Finding subtotals in Datasets

Questions
1

Hello all,

I have found that it seems to be very difficult to find subtotals in a Dataset structure. I am talking about simple Mathematica datasets with named columns, consisting of a list of associations.

To give a simple example, suppose a dataset has a column for “City”, another for “Year”, another for “Month”, another for “Date” (within the month in question) and another for amount of “Rain” (in the day in question).

I want to find the city, year, and month that has brough the most total rain. I agree that it should be simple. We would GroupBy (or GatherBy) {city, year, month} and then subtotal rain within those subsets and find the maximum.

I have tried many different ways, devised by myself using the documentation or suggested by AI coding agents. Things like:

winner = mydataset\[GroupBy\[#\[\[{"A", "B", "C"}\]\] &, Total\[#\[All, "D"\]\] &\] /\* MaximalBy\[Identity\] /\* Keys /\* First\]

or

topGroup = mydataset\[GroupBy\[{#A,#B,#C} &\] /\* MaximalBy\[Total\[#\[All, "D"\]\] &\] /\* First\]

when these approaches do not result in some error message, they identify the wrong case as the winner (which is worse).

The only way I have found to get the right answer is to extract subsets of the dataset by the subgroups (not just group the dataset, but actually extract the subset), total the variable in question in the extracted subset, do that for each subset and then find the maximum. This is slow because of the need to extract each subset to analyze it. In a sense, that is what any solution to this problem will do, but it should be able to do it without actually extracting the subset out of the dataset.

I have found other solutions, but they can be said to be essentially the same. Essentially one gives up on the idea of using purely dataset functions and starts using the true and tried list functionality.

Any suggestions would be much appreciated.

Best,

Otto Linsuain

2

I’m not confident that I understand what you’re asking for, but here is my best guess. First, I created some dummy data:

SeedRandom[17];
rainData = 
  Flatten[Table[<|"city" -> city, "year" -> year, "month" -> month, 
     "day" -> day, "rain" -> RandomReal[10]|>, {year, 2024, 
     2026}, {month, 1, 12}, {day, 1, 
     20}, {city, {"baltimore", "austin", "tampa"}}]];
rainDataset = Dataset[rainData]

Here is how we can get the total rain for {city, year, month} tuples:

rainDataset[GroupBy[{#city, #year, #month} &], Total@*Map[#rain &]]

We can sort it largest to smallest:

rainDataset[GroupBy[{#city, #year, #month} &], Total@*Map[#rain &]] // ReverseSort

We can extract the maximal one:

rainDataset[GroupBy[{#city, #year, #month} &], Total@*Map[#rain &]][MaximalBy[Identity]]

Hmm, there are some display issues related to “#”, but it’s late for me and I don’t want to spend time figuring out this editor.

3

Thanks a lot Eric. I have spent a lot of time on this.
The key seems to be how you use (Map) the Total: Total@*Map[#rain &]

I would have never guessed it. It worked the first time.

Thanks again,

Otto Linsuain