# Data munging Mathematica Dataset object

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?

• 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. Commented May 4, 2022 at 3:33

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]


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

res[Key[20]]


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} &]]]]


Hope this helps.

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

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)