4
$\begingroup$

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 |>
}]

Error, and what I want

$\endgroup$
0
1
$\begingroup$

I like this solution (without JoinAcross):

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

d[All, MapJoin[Normal[dReduced], #, "id"] &]
```
$\endgroup$
0
$\begingroup$

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|>|>
}
$\endgroup$
0
$\begingroup$

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.

$\endgroup$

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.