Data munging Mathematica Dataset object

Question

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?

Answer 1

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.

Answer 2

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