0
$\begingroup$

I need to create a list from a general Excel database that requires only data from "ALL STUDENTS" rows on columns 2 and 7. Excel Data

The List most be of the form {DISTRICT, MATH} where the MATH score is only from the ALL STUDENTS group.

I'm not really sure how to go about doing this, whether i can just import specific stuff only from the file, or if I can just compose a list with specific entries. My mathematica skills are very limited and I don't find the mathematica more info option very helpful.

Any help is much appreciated

Edit: Import["SAT_District_Data_class_2011.csv", {"Sheets", 1, All, {2, 7}}][[2 ;; ;; 12]]

returns the errors:

import::noelem: The Import element "Sheets " is not present when importing as CSV Part::take: "Cannot take positions 2 through -1 in $Failed. "

|improve this question
$\endgroup$
  • $\begingroup$ Welcome to Mathematica.SE! I suggest that: 1) You take the introductory Tour now! 2) When you see good questions and answers, vote them up by clicking the gray triangles, because the credibility of the system is based on the reputation gained by users sharing their knowledge. Also, please remember to accept the answer, if any, that solves your problem, by clicking the checkmark sign! 3) As you receive help, try to give it too, by answering questions in your area of expertise. $\endgroup$ – bbgodfrey Apr 30 '15 at 21:29
  • $\begingroup$ Please include the code you already have developed. $\endgroup$ – bbgodfrey Apr 30 '15 at 21:29
  • $\begingroup$ Does this work: Import["yourexcelfile.xlsx", {"Sheets", 1, All, {2, 7}}][[2;;;;12]]? $\endgroup$ – kglr Apr 30 '15 at 21:36
  • 1
    $\begingroup$ ... or, for the csv file, you can try Import["SAT_District_Data_class_2011.csv"][[ 2 ;; ;; 12, {2,7}]] $\endgroup$ – kglr Apr 30 '15 at 23:00
  • 1
    $\begingroup$ reference.wolfram.com/language/ref/Span.html and reference.wolfram.com/language/ref/Part.html and perhaps reference.wolfram.com/language/tutorial/… There are often two forms of a Mathematica function, a spelled out name followed by [ arguments ] and a shorthand abbreviation that is often only a couple of punctuation characters. Sometimes the documentation is not careful to show how both forms are used. $\endgroup$ – Bill May 1 '15 at 3:01
0
$\begingroup$

Just for completeness the syntax to "directly" import specified rows/columns is like this:

  Import[ "test.xls", {"Data", 1, {2, 25, 71}, {32, 44}}]

which says take sheet 1 , rows 2,25&71 and columns 32,44.

For comparison this expression produces the same result by importing everything and extracting the desired parts:

  Import["test.xls"][[1, {2, 25, 71}, {32, 44}]]

Ultimately there is little if any reason to prefer the first form, even though it might appear to be more efficient. In practice it seems to perform the same or even worse for large files. Additionally the direct form can only take explicit lists of row/col numbers (or All ), but not ranges etc.

|improve this answer
$\endgroup$
2
$\begingroup$

With a sheet containing

Group Distric DistName Cityname regname region Math Critrd
All Students 1. s w g 1. 451. x
female       5  n t e 7   12. x
male         2. r y k 4.   5. x
All Students 7. g m m 9. 456. x

and the spreadsheet file named Book.xlsx and in a place where Mathematica can find it then this

data = First[Import["Book.xlsx"]];
Cases[data, {"All Students", dist_, _,_,_,_, math_, __} :> {dist,math}]

gives you

{{1., 451.}, {7., 456.}}
|improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.