# GroupBy several keys while keeping the Dataset as a Table (List of Associations)

Assume you import data from a Table source of the following format.

<< GeneralUtilities; fields = {"Country", "Region", "BU", "Year",
"Date", "Sales"};
organization = {{"Argentina", "LATAM", "Americas"}, {"SouthAfrica",
"Africa", "EAME"}, {"Brazil", "LATAM", "Americas"}, {"Japan",
"Japan", "APAC"}, {"Australia", "ASEAN", "APAC"}, {"Germany",
"Europe", "EAME"}};
SeedRandom[0];
list = Flatten[
Table[Join[
organization[[i]], {year, DateObject[{year, month, 1}],
RandomInteger[{100, 1000}]}], {i, 6}, {year, 2004,
2013}, {month, 1, 6, 5}], 2];
sales = Dataset[AssociationThread[fields, #] & /@ list]


I would like to summarize the data at the year level. If working with a database, an SQL command of the following format would allow you to create a dataset that is still flat.

SELECT sales.Country, sales.Region, sales.BU, sales.Year, Sum(sales.Sales) AS SumOfSales FROM sales GROUP BY sales.Country, sales.Region, sales.BU, sales.Year;

Using

sales[GroupBy@Key["Country"], GroupBy@Key["Region"],
GroupBy@Key["BU"], GroupBy[Key["Year"]], Total, "Sales"]


Creates a multilevel hierarchical data structure, which is not as simple to operate as a table type of dataset.

Is there a way to operate (total,mean, median,etc) on a dataset by grouping on several keys of interest while keeping the dataset flat the same way as done with the SQL procedure?

-
PatoCriollo: the GeneralUtilities package does not seem to be documented in V. 10. Could you please describe what is it used for and how you found it? –  magma Aug 9 at 8:46
@magma See here. I believe that package was first introduced in that answer by Taliesin. –  RunnyKine Aug 9 at 12:24

Probably far from ideal, but this works:

sales[
GroupBy[{#Country, #Region, #BU, #Year} & -> Key["Sales"]]
][Normal, Total
][All, Apply[Append]
]


(Thanks to WReach for the tip on the unusual but useful linebreak pattern.)

Update

This works too, and preserves the keys. Now if only I could specify Normal to be descending here ...

sales[
GroupBy[#, KeyTake[{"Country", "Region", "BU", "Year"}] -> KeyTake["Sales"], Total] &
][Normal
][All, Apply[Join]]


-
Brilliant! Dataset[AssociationThread[Drop[fields, {5}], #] & /@sales[ GroupBy[{#Country, #Region, #BU, #Year} & -> Key["Sales"]] ][Normal, Total ][All, Apply[Append]]//Normal] puts the keys back again. ] –  PatoCriollo Aug 9 at 4:15
@PatoCriollo That came out really wordy though, I'm hoping for a shorter solution! It would be good to have something like Merge that doesn't merge each key using the same function. –  Szabolcs Aug 9 at 4:20
@Szabolcs I'm using your answer for a similar problem that I have, but I don't really understand the syntax. I was hoping you could help. How are [Normal][All,Apply[Join]] applied to the result from Total? –  Mitchell Kaplan Aug 20 at 20:17
@MitchellKaplan This dataset contains an association, the keys of which are also associations. Normal will convert this outer association, which has the form <| <|...|> -> <| "Sales" -> 1 |>, <|...|> -> <| "Sales" -> 2 |>, ... |> to a simple list of rules, { <|...|> -> <| "Sales" -> 1 |>, <|...|> -> <| "Sales" -> 2 |>, ... }. You can see e.g. the first element of this list using ...[Normal][1]. Now what we want to do is join the left-hand-side of -> together with its right-hand-side into a single association. Instead of having <| "a" -> 1 |> -> <| "b" -> 2 |> we want ... –  Szabolcs Aug 20 at 20:26
... <| "a" -> 1, "b" -> 2 |>. This is what Apply is good for: change the head Rule (i.e. ->) into something else. What we need here is to change it to Join. Apply[Join][...] is equivalent to Apply[Join, ...] so we can use the form Apply[Join]. I hope this clears it up a bit. Regarding this method, while it works, it's probably not efficient and I am not really happy with it. I find it too convoluted. I am hoping for something simpler ... I'm not yet experienced with Dataset, and even though my answer got accepted, I'm not too confident about it. –  Szabolcs Aug 20 at 20:28

Here is an alternative:

 Query[Map[Total] /* Normal /* Map[Apply@Append]]@
sales[GroupBy[{#Country, #Region, #BU, #Year} & -> Key["Sales"]]]


OR

sales[GroupBy[{#Country, #Region, #BU, #Year} & -> Key["Sales"]]][
Map[Total] /* Normal /* Map[Apply@Append]]

-

A possibility

sales[
GroupBy[KeyTake[{"Country", "Region", "BU", "Year"}] -> KeyDrop["Date"]] /* Values,
merge[{"Sales" -> Total}, First]
]


merge is an operator such that you can specify a merging function for particular keys, and a default one

merge[r : {__Rule}, def_] := Merge[Identity] /* Query[{
Query[KeyDrop@Keys@r, def],
Query[KeyTake[#], #2] & @@@ r} // Flatten] /*
Merge[First]


or something among these lines

groupBy2D[groupby_, newCols : {__Rule}] :=
With[{tr = Transpose[#, AllowedHeads -> All] &},
Query[
GroupBy[KeyTake[groupby]] /* Values,
Query[{First, tr /* Query[<|newCols|>]}] /* Merge[First] /*
KeyTake[groupby]]
]


so that

sales[
groupBy2D[
{"Country", "Region", "BU", "Year"},
{"SumOfSales" -> (Total@#Sales &)}
]
]
`

These are probably not too efficient

-