7
$\begingroup$

Many times when working with Dataset I have to GroupBy. If for example, I group by and aggregate on columns of the same type then the single aggregator function like the following is usually enough.

GroupBy[dataset,First->Rest,Total]

which is equivalent to

GroupBy[dataset,First->Rest,Merge[Total]]

If my Rest is all numeric columns then the Total works fine on them. But now let's say I am encountering a case where my Rest is columns of DateObject, Number and Entity among other things.

Now Total, when applied to columns of DateObject and Entities, don't produce meaningful results.

So I am looking for a way to use a Merge which can have different aggregators for different keys.

GroupBy[dataset,First->Rest,Merge[...]]

Is there a form of Merge or some other equivalent function that applies a different aggregator based on the keys? Like for example:

Merge[<|"DateColumn"->Max,"NumberColumn"->Total,"EntityColumn"->First|>]
$\endgroup$

2 Answers 2

4
$\begingroup$

Here are two ways:

data = AssociationThread[{"a", "b", "c"}, #] & /@ Tuples[{1, 2}, {3}]
(* {<|"a" -> 1, "b" -> 1, "c" -> 1|>, <|"a" -> 1, "b" -> 1, "c" -> 2|>,
    <|"a" -> 1, "b" -> 2, "c" -> 1|>, <|"a" -> 1, "b" -> 2, "c" -> 2|>,
    <|"a" -> 2, "b" -> 1, "c" -> 1|>, <|"a" -> 2, "b" -> 1, "c" -> 2|>,
    <|"a" -> 2, "b" -> 2, "c" -> 1|>, <|"a" -> 2, "b" -> 2, "c" -> 2|>} *)

GroupBy[data, First -> Rest, Merge[Apply@Construct]@*Prepend[<|"b" -> b, "c" -> c|>]]
(* <|1 -> <|"b" -> b[1, 1, 2, 2], "c" -> c[1, 2, 1, 2]|>,
     2 -> <|"b" -> b[1, 1, 2, 2], "c" -> c[1, 2, 1, 2]|>|> *)

GroupBy[data, First -> Rest, Query[{"b" -> b, "c" -> c}]@*Merge[Identity]]
(* <|1 -> <|"b" -> b[{1, 1, 2, 2}], "c" -> c[{1, 2, 1, 2}]|>,
     2 -> <|"b" -> b[{1, 1, 2, 2}], "c" -> c[{1, 2, 1, 2}]|>|> *)

The first one prepends an additional association to the associations to be merged. It then uses Construct to apply the function from the first association to the elements of the other associations.

The second approach merges the associations with Identity, and uses Query to apply the appropriate post-processing to each entry. In my opinion, this one is more readable. It also has the advantage that it doesn't break if the associations have keys not present in your "reduction list":

GroupBy[data, First -> Rest, Merge[Apply@Construct]@*Prepend[<|"b" -> b|>]]
(* <|1 -> <|"b" -> b[1, 1, 2, 2], "c" -> 1[2, 1, 2]|>,
     2 -> <|"b" -> b[1, 1, 2, 2], "c" -> 1[2, 1, 2]|>|> *)

GroupBy[data, First -> Rest, Query[{"b" -> b}]@*Merge[Identity]]
(* <|1 -> <|"b" -> b[{1, 1, 2, 2}], "c" -> {1, 2, 1, 2}|>,
     2 -> <|"b" -> b[{1, 1, 2, 2}], "c" -> {1, 2, 1, 2}|>|> *)

Notice how the first output contains expressions like 1[2, 1, 2] (since we didn't supply a function in the first association, Construct simply took the element from the first "proper" association for the head - in this case a 1). The second output simply contains {1, 2, 1, 2} in that place, since the output from Merge has just been left untouched.

$\endgroup$
3
  • $\begingroup$ Thank you Lukas for your answer. I was reusing this technique in another code today and thought if there was a way to instead of defining actual keys one could define patterns so could bundle them, like Query[{"a"|b"->f,"c"->g}]. But it is not working do you know if there is a way to do this? $\endgroup$
    – user13892
    Mar 17, 2020 at 21:27
  • $\begingroup$ @user13892 Unfortunately, I am not aware of a nice built-in way to achieve that. I can think of a few ugly ways, but maybe it's better if you ask a new question about this part specifically, chances are good that someone else can think of a nice solution $\endgroup$
    – Lukas Lang
    Mar 20, 2020 at 13:56
  • $\begingroup$ Thank you, I have asked this specific question here $\endgroup$
    – user13892
    Mar 25, 2020 at 11:44
7
$\begingroup$

I created a Wolfram repository function specifically for this. It allows you to define different functions for merging different keys. For example, you can merge numerical and categorical columns with different functions in the Titanic dataset:

data = Normal @ ExampleData[{"Dataset", "Titanic"}];
ResourceFunction["MergeByKey"][data,
  {"age" -> Histogram},
  BarChart[Counts[#], ChartLabels -> Automatic] &
]

enter image description here

MergeByKey is based on AssociationTranspose (from the GeneralUtilities package) and Query. It's also worth noting that it's significantly faster on large rectangular datasets like the one from the example:

merge1 = ResourceFunction["MergeByKey"][data, {}]; // RepeatedTiming
merge2 = Merge[data, Identity]; // RepeatedTiming
merge1 === merge2

{0.0054, Null}

{0.022, Null}

True

$\endgroup$
2
  • 2
    $\begingroup$ This function is awesome. Thanks Sjoerd! $\endgroup$ Aug 2, 2022 at 2:05
  • $\begingroup$ Many thanks for this useful function! $\endgroup$
    – Lacia
    Jan 20 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.