5
$\begingroup$

I struggle to use Mathematica's Dataset object. I like the descending and ascending query operators construct and I try to manipulate my simple tabular data using this approach. However, I am not a very good programmer and I inevitably fallback to extracting the values into List structures.

The file I am using is available at https://www.dropbox.com/s/mfr00g4eq9uvmk6/wais.csv?dl=0 which I import into a Dataset using

ds = Import["D:\\Statistics\\Regression\\Logistic Regression\\wais.csv","Dataset",HeaderLines -> 1];

The data is a list of scores (ranging from 4 thru 20), each associated with a binary indicator (0 or 1) according to whether the score is associated with an asymptomatic (0) or symptomatic (1) condition. Most scores have both but a few have only one or the other indicator. I want to reduce the the data into a 3-by-17 matrix with the first column being the score Id (4 to 20), and the second and third columns holding, respectively, the total number of symptomatic and asymptomatic cases (for each score ID). In the end, I have extracted the data List formatted data using

data = Normal@Values@ds[All, {"wais", "sym"}];
data = {{9,1}, {13,1}, {6,1}, {8,1}, {10,1}, {4,1}, {14,1}, {8,1}, {11,1}, {7,1}, {9,1}, {7,1}, {5,1}, {14,1}, {13,0}, {16,0}, {10,0}, {12,0}, {11,0}, {14,0}, {15,0}, {18,0}, {7,0}, {16,0}, {9,0}, {9,0}, {11,0}, {13,0}, {15,0}, {13,0}, {10,0}, {11,0}, {6,0}, {17,0}, {14,0}, {19,0}, {9,0}, {11,0}, {14,0}, {10,0}, {16,0}, {10,0}, {16,0}, {14,0}, {13,0}, {13, 0}, {9,0}, {15,0}, {10,0}, {11,0}, {12,0}, {4,0}, {14,0}, {20,0}}

Then, I use the following to get my 17-by-3 table

waisScore = Range[4, 20];
asymptomatic = Length[#] & /@ Table[Select[data, #[[1]] == i && #[[2]] == 0 &], {i, 4, 20}];
symptomatic = Length[#] & /@ Table[Select[data, #[[1]] == i && #[[2]] == 1 &], {i, 4, 20}];
TableForm[m = {waisScore, symptomatic, asymptomatic}\[Transpose]]

However, I would like to be able to do this from the Dataset object without extracting the values into a list. I can get part way there with

s0 = ds[SortBy["wais"]/*Select[#sym == 0 &]/*GroupBy["wais"], Length,"sym"];
s1 = ds[SortBy["wais"]/*Select[#sym == 1 &]/*GroupBy["wais"], Length,"sym"];

But the columns are discontinuous in the scoreID.

Is there a way to have all score ids represented, even when a symptom is missing?

$\endgroup$
1
  • $\begingroup$ I feel your pain. The descending and ascending query operators are really cool, but I am only ever able to chain more than 2 of them together after hours of monte carlo programming, and invariably revert to unpacking them to lists, as you describe. I saw Wolfram also commenting on their opacity in one of the live CEO-ing episodes on Yutube. $\endgroup$ May 4 at 3:33

2 Answers 2

2
$\begingroup$

To propagate missing columns you can use Merge with an Association of column default values.

With ds as in OP

res =
  ds[GroupBy[{#"wais" &, #"sym" &}]/*
    KeySort
   , Merge[{<|Thread[{0, 1} -> 0]|>, #}, Last] &
   , Length
   ];
Dataset[res, MaxItems -> 3]

enter image description here

In the case where a "wais" has only one type of response both responses are present; e.g.

res[Key[20]]

enter image description here

Key is needed since your keys are integers which are interpreted as Part instead of a key.

The matrix can be extracted by

MatrixForm[m = Normal[res[KeyValueMap[Flatten@{#, Values@#2} &]]]]

enter image description here

Hope this helps.

$\endgroup$
2
  • $\begingroup$ Awesome! I am continuously taken aback by what can be accomplished by a master of their craft. Thank you. $\endgroup$ May 9 at 11:57
  • $\begingroup$ @GeorgeEllis Don't forget to accept (checkbox under the vote buttons) if you are proceeding with this solution. $\endgroup$
    – Edmund
    May 9 at 23:28
-1
$\begingroup$

Here is a simple approach x = Import["C:\Users\Lenovo\Downloads\wais.csv"];

y = x[[2 ;; 18]]; (*to select only the necessary part of the data)

iD = Range[4, 20] (to generate the id number you wanted

v = Thread[{iD, y}]; (*connect the values with the ids)

z = Flatten[v, 3]; (* the datasets)

Partition[z, 3] // Dataset (*you get a formatted dataset U wanted)

Bons, if U want you can add header for your dataset

$\endgroup$
2
  • 1
    $\begingroup$ Could you actually apply your code to the OP's dataset or to a dataset you create? Doing so would be very helpful in evaluating your answer. $\endgroup$
    – bbgodfrey
    May 2 at 21:21
  • $\begingroup$ Thank you for your input. However, I was hoping to stay within the Query Dataset paradigm where the descending operator(s) get the data and ascending operator(s) restructure the selected data into the form I get when I do it in the List paradigm (but, of course, as a new Dataset object). But, thank you. $\endgroup$ May 3 at 23:49

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.