# Average dataset with many repetitions?

I have a dataset where each item is of the form:

<|"p1" -> x1, "p2" -> x2, "r1" -> y1, "r2" -> y2|>


The interpretation is this: p1 and p2 are parameters, which can take on different values (in this case represented by x1 and x2), while r1, r2 are the results of some simulation carried out with some specific values for the parameters p1 and p2. For this particular item, the simulation was run with p1=x1, p2=x2, and the results were r1=y1, r2=y2. Now, given fixed values of the parameters p1, p2, there can be some variation in the results r1, r2. So my dataset, which is a List of items of the form above, has many repetitions for each particular pair of parameter values. Now what I want to do is transform this list, into another list, where the items are again of the form:

<|"p1" -> x1, "p2" -> x2, "r1" -> m1, "r2" -> m2|>


where now m1 is the mean of y1 for all simulations with parameter values p1=x1, p2=x2, and similarly for m2. So the new list has no repetitions: there is only one entry per pair of parameter values.

What's the simplest way of doing this?

My Mathematica version is 10.3.

Stealing Pickett's example:

Values @ GroupBy[res, Query[{"p1", "p2"}], Merge@Mean]

{
<|"p1" -> 1, "p2" -> 1, "r1" -> 64/5, "r2" -> 20|>,
<|"p1" -> 1, "p2" -> 2, "r1" -> 64/5, "r2" -> 156/5|>
}


And if m keys are required:

<|KeyDrop[#, {"r1", "r2"}], "m1" -> #r1, "m2" -> #r2|> & /@ %

• that is a bit overcomplicated. Values@GroupBy[res, #[[{"p1", "p2"}]] &, Mean] is all you need. Jan 14 '16 at 22:12
• @MikeHoneychurch this crashes my kernel :/
– Kuba
Jan 14 '16 at 23:23
• really! what are you running? I am on OS X 10.10.5 and Mma V10.3.1 and it works fine Jan 14 '16 at 23:50
• +1 Very nice and readable. And if I wanted the standard deviations of the results as well, I could use Values @ GroupBy[res, Query[{"p1", "p2"}], Merge@StandardDeviation]. Great! Jan 14 '16 at 23:50
• @MikeHoneychurch Mean alone triggers this bug. WRI claims it was broken in 10.3.0 and fixed in 10.3.1, but on Windows I still see the wrong result in 10.3.1 when Mean is applied to a list containing a single association (no crash though). Merge@Mean is the workaround. Jan 15 '16 at 0:24

One way:

gathered = GatherBy[res, {#p1, #p2} &]

<| "p1" -> First[#]["p1"],
"p2" -> First[#]["p2"],
Merge[#[[All, {Key["r1"], Key["r2"]}]], Mean]
|> & /@ gathered


Test:

res = {
<|"p1" -> 1, "p2" -> 1, "r1" -> 10, "r2" -> 20|>,
<|"p1" -> 1, "p2" -> 1, "r1" -> 15, "r2" -> 20|>,
<|"p1" -> 1, "p2" -> 1, "r1" -> 12, "r2" -> 20|>,
<|"p1" -> 1, "p2" -> 1, "r1" -> 13, "r2" -> 20|>,
<|"p1" -> 1, "p2" -> 1, "r1" -> 14, "r2" -> 20|>,
<|"p1" -> 1, "p2" -> 2, "r1" -> 10, "r2" -> 20|>,
<|"p1" -> 1, "p2" -> 2, "r1" -> 15, "r2" -> 30|>,
<|"p1" -> 1, "p2" -> 2, "r1" -> 12, "r2" -> 34|>,
<|"p1" -> 1, "p2" -> 2, "r1" -> 13, "r2" -> 32|>,
<|"p1" -> 1, "p2" -> 2, "r1" -> 14, "r2" -> 40|>
};

gathered = GatherBy[res, {#p1, #p2} &]
<| "p1" -> First[#]["p1"],
"p2" -> First[#]["p2"],
Merge[#[[All, {Key["r1"], Key["r2"]}]], Mean]
|> & /@ gathered

(* Out: {<|"p1" -> 1, "p2" -> 1, "r1" -> 64/5, "r2" -> 20|>, <|"p1" -> 1,
"p2" -> 2, "r1" -> 64/5, "r2" -> 156/5|>} *)


Update borrowing from Kuba.

Join @@@ Normal@GroupBy[dat, Query[{"p1", "p2"}],
KeyMap[StringReplace[#, "r" -> "m"] &]@*Mean@*Query[All, {"r1", "r2"}]]


Original post

Generate some data:

SeedRandom;
dat = Join[#, <|"r1" -> RandomReal, "r2" -> RandomReal|>] & /@
Flatten@ConstantArray[Table[<|"p1" -> i, "p2" -> j|>, {i, 2}, {j, 2}], 4];


Use GroupBy and a few Association related functions.

KeyValueMap[
Join[
AssociationThread[{"m1", "m2"} -> Mean@Query[All, {"r1", "r2"} /* Values]@#2]
] &
]@GroupBy[Lookup[{"p1", "p2"}]]@dat

(*
{<|"p1" -> 1, "p2" -> 1, "m1" -> 3.19274, "m2" -> 14.7372|>,
<|"p1" -> 1, "p2" -> 2, "m1" -> 4.41867, "m2" -> 11.8091|>,
<|"p1" -> 2, "p2" -> 1, "m1" -> 3.23234, "m2" -> 7.25984|>,
<|"p1" -> 2, "p2" -> 2, "m1" -> 6.18188, "m2" -> 6.79438|>}
*)


Hope this helps.

not an answer. i just wanted to paste output to show the bug has been fixed on Mac • @ilian why does the bug persist on Windows but not on Mac? Jan 16 '16 at 0:23