5
$\begingroup$

I often need to sum values in a list of Associations according to some columns in that Association. I have a way of doing it, which will be the main part of this question, but it seems awkward. I'm hoping that there's a simpler way to do this. Perhaps even one that would allow me to create a generic function I can use in a number of projects.

Can anyone suggest a less awkward way of doing this?

a1 = {
<|"L1" -> "abc", "L2" -> "wxy", "L3" -> "lmn", "V1" -> 0123, "V2" -> 0023, "V3" -> 0111|>,
<|"L1" -> "abc", "L2" -> "xyz", "L3" -> "lmn", "V1" -> 1023, "V2" -> 0043, "V3" -> 0411|>,
<|"L1" -> "abc", "L2" -> "www", "L3" -> "lmn", "V1" -> 1200, "V2" -> 0993, "V3" -> 1111|>,
<|"L1" -> "def", "L2" -> "wxy", "L3" -> "lmn", "V1" -> 1020, "V2" -> 0231, "V3" -> 2111|>,
<|"L1" -> "def", "L2" -> "wxy", "L3" -> "mno", "V1" -> 0120, "V2" -> 0123, "V3" -> 0011|>,
<|"L1" -> "ghi", "L2" -> "www", "L3" -> "lmn", "V1" -> 0088, "V2" -> 0523, "V3" -> 0001|>,
<|"L1" -> "jkl", "L2" -> "xyz", "L3" -> "lmn", "V1" -> 0808, "V2" -> 2713, "V3" -> 1001|>,
<|"L1" -> "jkl", "L2" -> "wxy", "L3" -> "lmn", "V1" -> 8080, "V2" -> 2123, "V3" -> 2002|>,
<|"L1" -> "abc", "L2" -> "wxy", "L3" -> "lmn", "V1" -> 0099, "V2" -> 0233, "V3" -> 0077|>,
<|"L1" -> "def", "L2" -> "www", "L3" -> "lmn", "V1" -> 0757, "V2" -> 0001, "V3" -> 9011|>,
<|"L1" -> "abc", "L2" -> "wxy", "L3" -> "mno", "V1" -> 1212, "V2" -> 0009, "V3" -> 0991|>,
<|"L1" -> "ghi", "L2" -> "www", "L3" -> "mno", "V1" -> 5512, "V2" -> 0111, "V3" -> 9091|>};

a2 = Dataset[a1]

dataset of input data

a3 = a2[GroupBy[{#L1, #L2} &], Total, {"V1", "V2"}];
a4 = Normal[Values[a3]];
a5 = Normal[Keys[a3]];
a6 = AssociationThread[{"L1", "L2"} -> #] & /@ a5;
a7 = MapThread[Join, {a6, a4}];
Dataset[a7]

result of 1st sum

a3 = a2[GroupBy[{#L1} &], Total, {"V2"}];
a4 = Normal[Values[a3]];
a5 = Normal[Keys[a3]];
a6 = AssociationThread[{"L1"} -> #] & /@ a5;
a7 = MapThread[Join, {a6, a4}];
Dataset[a7]

second summation

$\endgroup$
  • 1
    $\begingroup$ Is this your another account Mitchell Kaplan ? If so, why don't you merge it with your actual account? $\endgroup$ – Artes Jun 28 '17 at 18:56
  • $\begingroup$ Thanks - didn't realize it was there. I just submitted a request to merge them. $\endgroup$ – Mitchell Kaplan Jun 28 '17 at 19:35
  • 1
    $\begingroup$ Related: (4332) $\endgroup$ – Mr.Wizard Jul 1 '17 at 6:19
3
$\begingroup$

When the same aggregation function (e.g. Total) is to be applied to all columns, then we can use:

a2[
  GroupBy[KeyTake[{"L1", "L2"}] -> KeyTake[{"V1", "V2"}]] /* KeyValueMap[Join]
, Merge[Total]
]

dataset screenshot

a2[GroupBy[KeyTake["L1"] -> KeyTake["V2"]] /* KeyValueMap[Join], Merge[Total]]

dataset screenshot

If we want to apply a different aggregation function to each column, we can apply a more general technique (taken from Dataset collapsing/reducing):

a2[GroupBy[{#L1, #L2}&] /* Values
, <| "L1" -> Query[First, "L1"]
   , "L2" -> Query[First, "L2"]
   , "V1" -> Query[Total, "V1"]
   , "V2" -> Query[Min, "V2"]
   , "V3" -> Query[Max, "V3"]
   |>
]

dataset screenshot

$\endgroup$
  • $\begingroup$ I like the 2nd one better. Of course there's the additional flexibility, but it's also a little easier to follow. The only real downside is typing in the keys more times. $\endgroup$ – Mitchell Kaplan Jun 29 '17 at 16:02

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.