Joining two datasets if second is association of associations

Question

JoinAcross of two datasets does not work, because the second dataset is an association {key_i -> association_i} instead of a list of associations.

d = Dataset[{
   <| "id" -> 1, "a" -> 1, "b" -> 10 |>,
   <| "id" -> 1, "a" -> 2, "b" -> 11 |>,
   <| "id" -> 2, "a" -> 3, "b" -> 12 |>,
   <| "id" -> 3, "a" -> 4, "b" -> 13 |>,
   <| "id" -> 3, "a" -> 5, "b" -> 14 |>
   }
  ];

dReduced = d[GroupBy["id"], Total, 2 ;; 3][All, <|"a_total" -> "a", "b_total" -> "b"|>]

(* 
 dReduced = <|
              1 -> <|"a_total" -> 3, "b_total" -> 21|>, 
              2 -> <|"a_total" -> 3, "b_total" -> 12|>, 
              3 -> <|"a_total" -> 9, "b_total" -> 27|>
            |>;
*) 

JoinAcross[d, dReduced, Key["id"]]

The last line results in JoinAcross::ntable: Dataset at position 2 does not contain a valid list of associations.

I can't find a simple way to transform the second dataset to a list of associations and restore name 'id' for the key. More than that, I don't see any reason to do it: the second database is indexed by a proper key and the join operation could be optimal (for merge strategy of "join" no need to group the second dataset by a key).

Expected result:

Dataset[{
  <| "id" -> 1, "a" -> 1, "b" -> 10, "a_total" -> 3, "b_total" -> 21 |>,
  <| "id" -> 1, "a" -> 2, "b" -> 11, "a_total" -> 3, "b_total" -> 21 |>,
  <| "id" -> 2, "a" -> 3, "b" -> 12, "a_total" -> 3, "b_total" -> 12 |>,
  <| "id" -> 3, "a" -> 4, "b" -> 13, "a_total" -> 9, "b_total" -> 27 |>,
  <| "id" -> 3, "a" -> 5, "b" -> 14, "a_total" -> 9, "b_total" -> 27 |>
}]

Answer 1

I like this solution (without JoinAcross):

MapJoin[rightDataset_, record_, key_] := Join[record, rightDataset[Key[record[key]]]];

d[All, MapJoin[Normal[dReduced], #, "id"] &]
```

Answer 2

For those who wants to use JoinAcross, I have slightly more verbose solution similar to @kglr's:

FlattenAssociation[d_, keyName_] := MapThread[
   Join[<|keyName -> #1|>, #2] &, 
   {Keys[d] // Normal, Values[d] // Normal}
];

JoinAcross[Normal[d], FlattenAssociation[dReduced, "id"], Key["id"]] // Dataset;

Or more:

FlattenAssociation[d_, keyName_, valueName_] := MapThread[
   Join[<|keyName -> #1|>, <|valueName -> #2|>] &, 
   {Keys[d] // Normal, Values[d] // Normal}
];

JoinAcross[Normal[d], FlattenAssociation[dReduced, "id", "idSums"], Key["id"]] // Dataset

to get this answer:

{
<|"id" -> 1, 
   "a" -> 1, "b" -> 10, 
  "idSums" -> <|"a_total" -> 3, "b_total" -> 21|>|>,
<|"id" -> 1, 
  "a" -> 2, "b" -> 11, 
  "idSums" -> <|"a_total" -> 3, "b_total" -> 21|>|>, 
<|"id" -> 2, 
  "a" -> 3, "b" -> 12, 
  "idSums" -> <|"a_total" -> 3, "b_total" -> 12|>|>, 
<|"id" -> 3, 
  "a" -> 4, "b" -> 13, 
  "idSums" -> <|"a_total" -> 9, "b_total" -> 27|>|>,
 <|"id" -> 3, 
  "a" -> 5, "b" -> 14, 
  "idSums" -> <|"a_total" -> 9, "b_total" -> 27|>|>
}

Answer 3

You could alter the GroupBy to return the structure you desire.

dReduced = 
 d[
  GroupBy["id"]
   /* Values
  , Transpose
   /* Query[{"id" -> First, Sequence @@ Thread[Range[2, 3] -> Total]}]
   /* KeyMap[Replace[k_String?(# != "id" &) :> k <> "_total"]]
  ]

gives

{ 
   <|"id" -> 1, "a_total" -> 3, "b_total" -> 21|>
 , <|"id" -> 2, "a_total" -> 3, "b_total" -> 12|>
 , <|"id" -> 3, "a_total" -> 9, "b_total" -> 27|>
}

then

JoinAcross[d, dReduced, Key["id"]]

performs the join.