Average dataset with many repetitions?

Question

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.

Answer 1

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|> & /@ %

Answer 2

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|>} *)

Answer 3

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[123];
dat = Join[#, <|"r1" -> RandomReal[10], "r2" -> RandomReal[20]|>] & /@ 
   Flatten@ConstantArray[Table[<|"p1" -> i, "p2" -> j|>, {i, 2}, {j, 2}], 4];

Use GroupBy and a few Association related functions.

KeyValueMap[
  Join[
    AssociationThread[{"p1", "p2"} -> #1],
    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.

Answer 4

not an answer. i just wanted to paste output to show the bug has been fixed on Mac