3
$\begingroup$

I have the following Dataset:

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

enter image description here

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:

enter image description here

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

$\endgroup$
1
  • 2
    $\begingroup$ Provide sample data that can be copied and pasted. $\endgroup$ – Bob Hanlon Aug 13 '20 at 12:57
2
$\begingroup$

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]

enter image description here

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

enter image description here

Alternatively, use KeySort to reorganize the output:

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

enter image description here

$\endgroup$
1
$\begingroup$

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"]
$\endgroup$
3
  • $\begingroup$ 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"]. $\endgroup$ – Massolo Aug 13 '20 at 13:35
  • $\begingroup$ @Massolo Your comment does not match your question. $\endgroup$ – Alan Aug 13 '20 at 13:56
  • $\begingroup$ Sorry if I was not clear in the first place, it works, thanks a lot $\endgroup$ – Massolo Aug 13 '20 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.