3
$\begingroup$

enter image description here

Above is a .csv file. I am trying to construct two lists of male and female math test scores such that only districts which include info for both male and female students are included i.e. neither male nor female math scores are left blank (if a district includes male data, but not female data, I dont want it to show up in my list). The strings are to be of the form {distname, Math}.

My attempt is to join the raw data for male and female math scores, and then using the Select[ ] command -- but i return nothing but { } or errors when i try this. Further, I'm not sure how to decouple the two lists once i weeded out blanks.

My lame attempt: femalescores=Import["file.csv"][[5 ;; ;; 12, {3, 7}]]; malescores=Import["file.csv"][[6;; ;; 12, {3, 7}]]; MFscores=Join[femalescores, malescore]; Select[MFscores,#>0&] which just returns { }

` Also, i don't think my 'solution' really meets the criteria of the string. Help or points in the right direction is much appreciated.

$\endgroup$
4
$\begingroup$

Import the data and chose the columns that contain relevant information:

In:

data = Import["file.csv"]〚All, {1, 3, 7}〛

Out:

{{"Female", "A", 1}, {"x", "A", 2}, {"Male", "A", 3},
 {"y", "B", 4}, {"Female", "B", 5}, {"Male", "B", 6},
 {"Female","C",7}, {"Male", "C", Null}}

Select those entries that start with "Female" or "Male" and wich also have a numerical value:

In:

data2 = Select[data, 
StringMatchQ[First@#, "Female" | "Male", IgnoreCase -> True] &&
NumericQ[Last@#] &]

Out:

{{"Female", "A", 1}, {"Male", "A", 3}, {"Female", "B", 5},
 {"Male", "B", 6}, {"Female", "C", 7}}

Gather female and male data by "districts" (second entry), select data with length of 2 (male and female data available)

In:

data3 = Select[GatherBy[data2, #〚2〛 &], Length[#] == 2 &]

Out:

{{{"Female", "A", 1}, {"Male", "A", 3}},
 {{"Female", "B", 5}, {"Male","B", 6}}}

Visualization

TableForm[data3〚All, All, -1〛, 
TableHeadings -> {data3〚All, 1, 2〛, {"Female", "Male"}}]

enter image description here

$\endgroup$
3
$\begingroup$
imp = Import["file.csv"] (* fake data *)

Mathematica graphics

scores = imp[[Join @@ Range[{5, 6}, Length@imp, 12], {3, 7}]];
MFscores = GatherBy[scores, First]

{{{"Cayuga ISD", 494}, {"Cayuga ISD", 489}},
{{"Elkhart ISD", 513}, {"Elkhart ISD", 455}},
{{"Westwood ISD", 519}, {"Westwood ISD", 451}},
{{"LaPoynor ISD", ""}, {"LaPoynor ISD", 451}}}

You can use Pick, Select, DeleteCases, Cases etc..

res1 = Pick[MFscores, FreeQ[#[[All, 2]], ""] & /@ MFscores];

res2 = Select[MFscores, FreeQ[#[[All, 2]], ""] &];

res3 = DeleteCases[MFscores, {{_, ""}, {__}} | {{__}, {_, ""}}];
(* or  res3 = DeleteCases[MFscores, _?(! FreeQ[#, ""] &)] *)

res4 = Cases[MFscores, Except[{{_, ""}, {__}} | {{__}, {_, ""}}]];
(* or res4 = Cases[MFscores, _?(FreeQ[#, ""] &)] *)

Equal @@ {res1, res2, res3, res4}
(* True *)

You can reorganize the output so that each row is in the form {distname, femalemathscore, malemathscore}

res = {#[[1, 1]], ## & @@ #[[All, 2]]} & /@ res1

{{"Cayuga ISD", 494, 489}, {"Elkhart ISD", 513, 455}, {"Westwood ISD", 519, 451}}

Panel[TableForm[res,TableHeadings -> {None, {"distname", "Female", "Male"}}], 
     Style["Math scores", 16], {{Top, Left}}]

Mathematica graphics

$\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.