# How to calculate totals in datasets with GroupBy applied to Values

I have the following Dataset:

ExData = Import["Example.xlsx", {"Dataset", 1}, "HeaderLines" -> 1];


I would like to get the total of Stage1+Stage2 and Stage3+Stage4+Stage5 grouping by Supplier but I am not able to extend the instruction:

ExData[GroupBy[#[["Supplier"]] &], GroupBy[#[["Stage"]] &], Total, #[["Amount"]] &]


which gives:

to get GroupBy work in combination with values. Is there a clever way to use GroupBy for this purpose? Thanks !

• Provide sample data that can be copied and pasted. Aug 13, 2020 at 12:57

Using a variation of data from Alan's answer:

SeedRandom[1]
data = Dataset@
Table[Association["Sector" -> RandomChoice[{"Energy", "Env"}],
"Supplier" -> StringTemplate["N"]@RandomInteger[5],
"Stage" -> "Stage" <> ToString[RandomInteger[{1, 5}]],
"Amount" -> 1000*RandomInteger[200]], 100]


data[GroupBy["Supplier"],
GroupBy[Switch[#Stage, "Stage1" | "Stage2", "stages12", _, "stages345"] &],
Total, "Amount"]


Alternatively, use KeySort to reorganize the output:

data[GroupBy["Supplier"] /* KeySort,
GroupBy[Switch[#Stage, "Stage1" | "Stage2", "stages12", _, "stages345"] &] /* KeySort,
Total, "Amount"]


Edited in response to change in question added in a comment:

data = Dataset@Table[Association[
"Sector" -> RandomChoice[{"Energy", "Env"}],
"Supplier" -> StringTemplate["N"]@RandomInteger[5],
"Stage" -> StringTemplate["Stage"]@RandomInteger[5],
"Amount" -> 1000*RandomInteger[200]
], 100];
grp = GroupBy["Supplier"]
data[grp, Total, "Amount"]


Your comment says you only want to total over a Stage1 and Stage 2, so select for these.

sel = Select["Stage1" == #Stage || "Stage2" == #Stage &]
data[sel][grp, Total, "Amount"]

• Thank you, but you are not grouping by values here. What I am looking for is to get the sum of only Stage1 and Stage2, which are two values of "Stage". The subtle syntax you kindly suggested is equivalent to: ExData[GroupBy["Supplier"], Total, "Amount"]. Aug 13, 2020 at 13:35
• @Massolo Your comment does not match your question.
– Alan
Aug 13, 2020 at 13:56
• Sorry if I was not clear in the first place, it works, thanks a lot Aug 13, 2020 at 16:19