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

Question

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?

Answer 1

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]]

Answer 2

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]]

Answer 3

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

Answer 4

Here is another possibility:

sales[
  GroupBy[KeyTake[{"Country","Region","BU","Year"}]] /* Normal /* (Association@@@#&)
, <| "Sales" -> Query[Total, "Sales"] |>
]

This approach has the interesting property that it can be "scaled up" to perform multiple aggregations at the same time:

sales[
  GroupBy[KeyTake[{"Country","Region","BU"}]] /* Normal /* (Association@@@#&)
, <| "Sales" -> Query[Total, "Sales"]
   , "MinYear" -> Query[Min, "Year"]
   , "MaxYear" -> Query[Max, "Year"] |>
]

Answer 5

Its a bit late to add a comment, but I found that GeneralUtilities` has a some operators such as AssociationPairs and AssociationMapThread. I used them to adjust the internal Dataset format. Since a GroupBy leaves a single association in the Dataset whose keys are the grouping keys, you need to process what is essentially a single association and make each k-> v in that association a "row." I used

dsGroupByResult[AssociationPairs]

to fix the structure. However it loses the column names and the "rows" are now lists.

To add columns back in, I use AssociationMapThread to "add" the columns back in and restructured back into a list of associations. My GroupBys usually output an association of values (e.g. mean, min, max for a numerical leaf column) so I just use ##2 since it already has keys on the values.

dsGroupByResult[AssocationMapThread[<|"theGroupingKeyColumnName"->#1 (*or whatever *), ##2|>]&]

I think both of these functions should be included in the standard package.