5
$\begingroup$

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?

$\endgroup$
5
$\begingroup$

Starting with the dataset from the question:

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

input dataset

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

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

result dataset

This works as follows:

  1. <|"letter" -> #letter, #color -> 1|>& is used to set the count of each row's color to one.
  2. KeyUnion is used to introduce a count of zero for colors missing from each row.
  3. GroupBy is used to group the rows together by letter and Total the color counts.
$\endgroup$
4
$\begingroup$
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}$

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

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]

Mathematica graphics

Hope this helps.

$\endgroup$
0
$\begingroup$
defaultds = <|"blue" -> 0, "red" -> 0|>;

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

Blockquote

$\endgroup$

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.