Build a Frequency Table from a Dataset

Question

How can the Mathematica 11.3 module below be improved (it builds a frequency table)? The data source is a Dataset object (dbase) and the row/column variables are respectively rowDIN and colDIN. Here the table entries would give the grade distribution within subject.

Dataset's documentation has an example that suggests a solution (see the line titanic[GroupBy["sex"],GroupBy["class"],ratio,"survived"]). Although I've tweaked this to get the desired table, it works because the table has no zeros. When applied to another database whose table would have zeros, Mathematica doesn't return a table -- that's why I use Count below.

There must be an easier way, and hopefully someone here knows it. Thanks.

myTable[dbase_, rowDIN_, colDIN_] := Module[{
columns = Sort@Normal@DeleteDuplicates@dbase[[All, colDIN]],
group,
X = GroupBy[dbase, rowDIN]},   

X = (group = #; {group[[1, rowDIN]], 
    Count[Normal@group[[All, colDIN]], #] & /@ columns}) & /@ X;
X = X // Normal // Normal // Last /@ # &;
Grid@Prepend[Flatten /@ X, Prepend[columns, rowDIN]]];

myTable[schoolInfo, "Subject", "Grade"]

where

schoolInfo = Dataset@{<|"Subject" -> "XYZ", 
"Grade" -> "A"|>, <|"Subject" -> "XYZ", 
"Grade" -> "B"|>, <|"Subject" -> "XYZ", 
"Grade" -> "A"|>, <|"Subject" -> "ABC", 
"Grade" -> "A"|>, <|"Subject" -> "ABC", 
"Grade" -> "C"|>, <|"Subject" -> "ABC", 
"Grade" -> "C"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>}

(J.M., I'm sorry for posting the wrong dataset a moment ago. The above is correct.)

Answer 1

This is also a bit ugly, but at least it's short:

schoolInfo = Dataset @ {<|"Subject" -> "XYZ", "Grade" -> "A"|>,
                        <|"Subject" -> "XYZ", "Grade" -> "B"|>,
                        <|"Subject" -> "XYZ", "Grade" -> "A"|>,
                        <|"Subject" -> "ABC", "Grade" -> "A"|>,
                        <|"Subject" -> "ABC", "Grade" -> "C"|>,
                        <|"Subject" -> "ABC", "Grade" -> "C"|>,
                        <|"Subject" -> "ABC", "Grade" -> "A"|>};

grades = {"A", "B", "C", "D", "E", "F"};

schoolInfo[GroupBy["Subject"], 
           Lookup["Grade"] /* Counts /*
           (AssociationThread[grades, Lookup[#, grades, 0]] &)]

Answer 2

Transpose imputes Missing expressions so can commute around it with a numerical imputation using Replace:

transposeCommute[f_]:=Query[Transpose]/*f/*Query[Transpose[#1]&]

(note: as of 11.3 the slot lookup #1 is required to work around a bug in Dataset typesystem)

Then:

infoTable = schoolInfo[GroupBy["Subject"], GroupBy["Grade"], Length][
 transposeCommute[Query[All,KeySort, Replace[_Missing :> 0]]]]

If you want to add additional grades that don't appear anywhere in the input, another function is useful:

appendIfNoKey[key_->f_][as_Association]:=If[KeyExistsQ[as,key],as,Append[as,{key->f}]]

appendIfNoKey[ruleList_List][as_Association]:=Query[Apply[RightComposition][Map[appendIfNoKey][ruleList]]][as]

Then, to pad grades "A" through "F":

infoTable[All, appendIfNoKey[Thread[{"A", "B", "C", "E", "F"} -> 0]]]

So while there are gaps in the language, workarounds for such generic patterns are often 1-liners. These functions are from a API of mostly 1-liners to be bundled w/ a data science book I'm writing.

Answer 3

I am not sure is this a solution you are looking for, but you can use the package CrossTabulate.m:

schoolInfo = 
  Dataset@{<|"Subject" -> "XYZ", "Grade" -> "A"|>, <|
     "Subject" -> "XYZ", "Grade" -> "B"|>, <|"Subject" -> "XYZ", 
     "Grade" -> "A"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>, <|
     "Subject" -> "ABC", "Grade" -> "C"|>, <|"Subject" -> "ABC", 
     "Grade" -> "C"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>};

Import["https://raw.githubusercontent.com/antononcube/MathematicaForPrediction/master/CrossTabulate.m"]

MatrixForm[CrossTabulate[schoolInfo]]

I consider the making of contingency tables to be a fundamental operation. More detailed explanations are given in the blog post "Contingency tables creation examples".

Update (2018-04-05)

Because the answers of @alancalvitti and @J.M. provide extensions of the columns of the contingency table with all grades (from "A" to "F") here is code to do that extension for my answer above.

Import["https://raw.githubusercontent.com/antononcube/MathematicaForPrediction/master/SSparseMatrix.m"]

rct = ToSSparseMatrix[ct]
rct2 = ImposeColumnNames[rct, CharacterRange["A", "F"]]
MatrixForm[rct2]

(For details on SSparseMatrix see SSparseMatrix.pdf(GitHub) or this blog post.)

Answer 4

This might be ugly but here how I did.

schoolInfo = {<|"Subject" -> "XYZ", "Grade" -> "A"|>, <|
    "Subject" -> "XYZ", "Grade" -> "B"|>, <|"Subject" -> "XYZ", 
    "Grade" -> "A"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>, <|
    "Subject" -> "ABC", "Grade" -> "C"|>, <|"Subject" -> "ABC", 
    "Grade" -> "C"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>};

val = GatherBy[Values@schoolInfo, First];

Grid[Join @@ {{{"Subject", "A", "B", "C", "D", "E", "F"}}, 
   Table[Flatten@{val[[i, 1, 1]], 
      Map[Length, 
       Cases[val[[i]], #] & /@ {{_, "A"}, {_, "B"}, {_, "C"}, {_, 
          "D"}, {_, "E"}, {_, "F"}}, 2]}, {i, Length@val}]}, 
 Dividers -> {{2 -> Black}, {2 -> Black}}]

\begin{array}{c|cccccc} \text{Subject} & \text{A} & \text{B} & \text{C} & \text{D} & \text{E} & \text{F} \\ \hline \text{XYZ} & 2 & 1 & 0 & 0 & 0 & 0 \\ \text{ABC} & 2 & 0 & 2 & 0 & 0 & 0 \\ \end{array}

Edit Probably there is more fancy and easy way to do it. This is the first time I am working with Dataset

schoolInfo = {<|"Subject" -> "XYZ", "Grade" -> "A"|>, <|
    "Subject" -> "XYZ", "Grade" -> "B"|>, <|"Subject" -> "XYZ", 
    "Grade" -> "A"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>, <|
    "Subject" -> "ABC", "Grade" -> "C"|>, <|"Subject" -> "ABC", 
    "Grade" -> "C"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>};
val = GatherBy[Values@schoolInfo, First];
val2 = Flatten@{"Subject", 
    DeleteDuplicates@Flatten@val[[All, All, 2]]};

Grid[Join @@ {{val2}, 
   Table[Flatten@{val[[i, 1, 1]], 
      Map[Length, Cases[val[[i]], #] & /@ ({_, #} & /@ Rest@val2), 
       2]}, {i, Length@val}]}, 
 Dividers -> {{2 -> Black}, {2 -> Black}}]

\begin{array}{c|ccc} \text{Subject} & \text{A} & \text{B} & \text{C} \\ \hline \text{XYZ} & 2 & 1 & 0 \\ \text{ABC} & 2 & 0 & 2 \\ \end{array}

Here is different dataset:

schoolInfo = {<|"Subject" -> "XYZ", "Grade" -> "A"|>, <|
    "Subject" -> "XYZ", "Grade" -> "B"|>, <|"Subject" -> "XYZ", 
    "Grade" -> "A"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>, <|
    "Subject" -> "ABC", "Grade" -> "C"|>, <|"Subject" -> "ABC", 
    "Grade" -> "C"|>, <|"Subject" -> "ABC", "Grade" -> "A"|>, <|
    "Subject" -> "DEF", "Grade" -> "D"|>};

\begin{array}{c|cccc} \text{Subject} & \text{A} & \text{B} & \text{C} & \text{D} \\ \hline \text{XYZ} & 2 & 1 & 0 & 0 \\ \text{ABC} & 2 & 0 & 2 & 0 \\ \text{DEF} & 0 & 0 & 0 & 1 \\ \end{array}