GroupBy dataset values and length

Question

I have a dataset, and I want to group by one of my columns, and then show the Length of another set of columns for each group. For example:

letter color
 a     red
 a     red
 a     blue
 b     blue

I want my new dataset to show:

    red blue
a    2   1
b    0   1

where red and blue are now column headers.

What code can I use to do this?

related: Creating a contingency table – kglr Nov 25 '17 at 02:24 — kglr, Nov 25 '17 at 02:24

score 7 · Answer 1 · answered Nov 25 '17 at 04:54

Starting with the dataset from the question:

dataset =
  {{"a", "red"}, {"a", "red"}, {"a", "blue"}, {"b", "blue"}} //
    Query[Dataset, AssociationThread[{"letter", "color"} -> #]&]

... then we can get the desired result as follows:

dataset[
  KeyUnion[#, 0&]& /* (GroupBy[#, Key["letter"] -> KeyDrop["letter"], Merge[Total]]&)
, <|"letter" -> #letter, #color -> 1|>&
]

This works as follows:

<|"letter" -> #letter, #color -> 1|>& is used to set the count of each row's color to one.
KeyUnion is used to introduce a count of zero for colors missing from each row.
GroupBy is used to group the rows together by letter and Total the color counts.

score 4 · Answer 2 · answered Nov 25 '17 at 02:34

4

dt = {{letter, color}, {a, red}, {a, red}, {a , blue}, {b, blue}}; 
TableForm[Outer[Count[dt[[2 ;;]], {##}] &, ## & @@ #], 
    TableHeadings -> #] &[DeleteDuplicates /@ Transpose[dt[[2 ;;]]]] // TeXForm

$\begin{array}{ccc} & \text{red} & \text{blue} \\ a & 2 & 1 \\ b & 0 & 1 \\ \end{array}$

answered Nov 25 '17 at 02:34

kglr

394,356
18
477
896

Thanks - Is there any way to do this in dataset form as well, or does it have to be in list and table form? – mathematicanewbie Nov 25 '17 at 03:19
@mathematicanewbie, could you add the Dataset input form of the example to you post? – kglr Nov 25 '17 at 04:09

score 2 · Answer 3 · answered Nov 25 '17 at 18:20

You may use GroupBy and Transpose.

Taking @WReach Dataset definition.

dataset =
  {{"a", "red"}, {"a", "red"}, {"a", "blue"}, {"b", "blue"}} //
    Query[Dataset, AssociationThread[{"letter", "color"} -> #]&]

Then

dataset[GroupBy[{#["color"] &, #["letter"] &}] /*
  Transpose /*
  ReplaceAll[_Missing -> 0],
 All,
 Length]

Hope this helps.

SquareOne · Answer 4 · 2017-11-26T11:23:55.307

0

defaultds = <|"blue" -> 0, "red" -> 0|>;

dataset[GroupBy[#, Key@"letter" -> Key@"color", 
   (Join[defaultds, #] &)@GroupBy[#, Identity, Length] &] &]

edited Nov 26 '17 at 11:23

answered Nov 26 '17 at 11:14

SquareOne

7,575
1
15
34

GroupBy dataset values and length

4 Answers4