3
$\begingroup$

I would like to join two Datasets based on their row index. Unlike ordinary columns, a row index does not have a name.

Single Row Index

dataSetSingle1=Dataset[{Association["c1"->"a","c2"->1],Association["c1"->"a","c2"->2],Association["c1"->"b","c2"->3],Association["c1"->"b","c2"->4]}][GroupBy["c1"],KeyDrop["c1"]]

enter image description here

dataSetSingle2=Dataset[{Association["c1"->"a","c3"->5],Association["c1"->"a","c3"->6],Association["c1"->"b","c3"->7],Association["c1"->"b","c3"->8]}][GroupBy["c1"],KeyDrop["c1"]]

enter image description here

The output I expect is:

enter image description here

Multiple Row Indices

To complicate the matter, how would this work for multi-level row indices?

dataSetMultiple1=Dataset[{Association["c0"->"M","c1"->"a","c2"->1],Association["c0"->"M","c1"->"a","c2"->2],Association["c0"->"M","c1"->"b","c2"->3],Association["c0"->"M","c1"->"b","c2"->4],Association["c0"->"F","c1"->"x","c2"->5],Association["c0"->"F","c1"->"x","c2"->6],Association["c0"->"F","c1"->"y","c2"->7],Association["c0"->"F","c1"->"y","c2"->8]}][GroupBy["c0"],GroupBy["c1"],KeyDrop["c0"],KeyDrop["c1"]]

enter image description here

Joined with:

dataSetMultiple2=Dataset[{Association["c0"->"M","c1"->"a","c3"->9],Association["c0"->"M","c1"->"a","c3"->10],Association["c0"->"M","c1"->"b","c3"->11],Association["c0"->"M","c1"->"b","c3"->12],Association["c0"->"F","c1"->"x","c3"->13],Association["c0"->"F","c1"->"x","c3"->14],Association["c0"->"F","c1"->"y","c3"->15],Association["c0"->"F","c1"->"y","c3"->16]}][GroupBy["c0"],GroupBy["c1"],KeyDrop["c0"],KeyDrop["c1"]]

enter image description here

Note that Datasets are hierarchical Associations.

Normal[dataSetSingle1] (* <|a->{<|c2->1|>,<|c2->2|>},b->{<|c2->3|>,<|c2->4|>}|> *)
Normal[dataSetMultiple1] (* <|M-><|a->{<|c2->1|>,<|c2->2|>},b->{<|c2->3|>,<|c2->4|>}|>,F-><|x->{<|c2->5|>,<|c2->6|>},y->{<|c2->7|>,<|c2->8|>}|> *)
$\endgroup$
  • $\begingroup$ does this work: {ds1,ds2}=Map[KeyValueMap[<|"rows" -> #,#2|>&]][Transpose@#]&/@{myDataSet1,myDataSet2}; joined=Dataset@JoinAcross[Catenate[Normal@ds1],Catenate[Normal@ds2],"rows"]? $\endgroup$ – kglr Sep 5 '19 at 10:56
  • $\begingroup$ No, but I understand your idea: first make 'regular' columns from the indices, then join on those columns, then re-index the output. That's a hack I am trying to avoid (unless there is no more elegant way). $\endgroup$ – LBogaardt Sep 5 '19 at 10:58
4
$\begingroup$
ClearAll[joinDS]

joinDS = Join[##, Depth[Normal@#] - 1] &;

joinDS[dataSetSingle1, dataSetSingle2]

joinDS[dataSetMultiple1, dataSetMultiple2]

enter image description here

$\endgroup$
  • 1
    $\begingroup$ Nice, and elegant. Though I'm still disappointed Datasets's internal API cannot deal with joins. Especially once you move to the more general case of inner joins, outer joins, left join and right join. $\endgroup$ – LBogaardt Sep 5 '19 at 15:34
  • 1
    $\begingroup$ Note that joinDS = Join[##, Depth[Normal@#] - 2] &; works better when there is only a single value per multi-level index (so "M", "a", "c2" gives a single value "1", not a List of {"1", "2"} as in my example above). $\endgroup$ – LBogaardt Sep 6 '19 at 7:18

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.