# GroupBy and MaximalBy to create a new Dataset

I'm looking for a neater way to achieve this, which to me looks awkward and suggests that I am missing something...

Given a Dataset...

ds = Dataset[{
<|"a"->1,"b"->1,"c"->3|>,
<|"a"->1,"b"->2,"c"->4|>,
<|"a"->2,"b"->3,"c"->5|>}];


Return another Dataset given by the grouping by one column and the maximal by another...

ds[GroupBy[#a &], MaximalBy[#b &]] // Values // Flatten


To return...

Dataset[{<|"a" -> 1, "b" -> 2, "c" -> 4|>, <|"a" -> 2, "b" -> 3, "c" -> 5|>}


The GroupBy and MaximalBy return a list of Associations of row number to a List of Associations (of which there is only one) which then needs (something like) //Values//Flatten to retrieve the Dataset I want.

Is there a more Dataset-eque way of doing this?

Since the question states that we need not worry about the case where there are multiple maxima in a group, the following expression eliminates the need for Flatten:

ds[GroupBy[#a &] /* Values, MaximalBy[#b &] /* First]


This arbitrarily chooses the First from among potential multiple maxima. The expression also does its work within the confines of the query expression without having specify additional post-processing functions. In principle, the Query optimizer can do a better job when it can see the complete operation.

If desired, we can (notionally) reduce the size of intermediate data structures by supplying a reduction function as the third argument to MaximalBy:

ds[GroupBy[#, #a &, MaximalBy[#b &] /* First] & /* Values]


We can see the difference between the plans of the two queries:

DatasetShowPlan[GroupBy[#a&] /* Values, MaximalBy[#b&] /* First]
(* GroupBy[#a&] /* Values /* Map[MaximalBy[#b&] /* First] *)

DatasetShowPlan[GroupBy[#, #a &, MaximalBy[#b&] /* First]& /* Values]
(* GroupBy[#1, #a&, MaximalBy[#b&] /* First]& /* Values *)


Note how the second plan does not contain the Map operator that is present in the first plan. Instead, GroupBy determines the single maximum for each group directly. Whether this second query results in a material performance difference is something that would need to be determined by benchmarking with real data.

Typically as soon as I post this occurs to me...

ds[GroupBy[#a &], MaximalBy[#b &]][Values, Merge[First]]


Since Values is a descending operator.

• If MaximalBy gives multiple values, then this gives a different result to that using Values and Flatten. For example, if ds = Dataset[{<|"a" -> 1, "b" -> 1, "c" -> 3|>, <|"a" -> 1, "b" -> 2, "c" -> 4|>, <|"a" -> 1, "b" -> 2, "c" -> 6|>, <|"a" -> 2, "b" -> 3, "c" -> 5|>}]; Is ds[GroupBy[#a &], MaximalBy[#b &]][Values][Flatten] more Dataset-eque? Commented Oct 23, 2014 at 7:47
• Thank you for that suggestion. In the case where MaximalBy returned more than one result, in my particular case, I'd have to choose one of them by some other criteria so would require some kind of tie breaker. Commented Oct 27, 2014 at 8:57
• OK, makes sense. You can also use MaximalBy to break the tie and failing that, select the first. For example: ds[GroupBy[#a &], MaximalBy[#, {Key["b"], Key["c"]}, 1] &][Values][Flatten] Commented Oct 28, 2014 at 0:26