I have two different datasets : dsDat1 and dsDat2:

ClearAll[dat1, dat2, col1, col2, row1, row2, dsDat1, dsDat2];
dat1 = RandomInteger[5, {10, 5}];
col1 = {"BOS", "labor", "revenue", "worker", "share"};
row1 = {"agr", "min", "ma1", "ma3", "ma5", "soth", "sbus", "stel", 
   "segw", "sfin"};
dsDat1 = 
 Dataset@AssociationThread[row1, AssociationThread[col1, #] & /@ dat1]

dat2 = RandomInteger[5, {10, 5}];
col2 = {"Numb1", "Tot", "Rev", "Per", "Share"};
row2 = {"min", "agr", "ma5", "ma3", "ma1", "stel", "soth", "sbus", 
   "segw", "sdummy"};
dsDat2 = 
 Dataset@AssociationThread[row2, AssociationThread[col2, #] & /@ dat2]

I want to create a single unified dataset by combining the two different datasets using the row keys in dsDat1 as a reference. There are some complications though. The second dataset dsDat2 is not perfectly matching dsDat1 in terms of row keys.

Here are the places they do not match:

  1. The order of the row keys in dsDat2 is mixed compared to those in dsDat1;
  2. Some row keys in dsDat2 do not exist in dsDat1, and some row keys in dsDat1 do not exist in dsDat2;

I like to handle those cases in items 1 and 2 as follows:

  1. Consider the row keys in dsDat1 as fixed, and create a new row at the end of the unified dataset for each row key that does not exist in dsDat1 but in dsDat2;
  2. For the missing row keys in dsDat2 (compared to dsDat1), place MissingValue[] in the unified dataset;

Basically, I just want to create a unified dataset by combining two different datasets.


2 Answers 2


I found a better way:

KeyUp[ds_] := KeyValueMap[<|"key" -> #1, #2|> &, Normal @ ds]

up = KeyUp /@ {dsDat1, dsDat2};

join = JoinAcross[Sequence @@ up, "key", "Outer"];

KeySort /@ GroupBy[join, First -> Rest, Last] // Dataset

enter image description here

We can put this in one function:

Consolidate[a_, b_] :=
 Dataset @ Map[KeySort] @
    JoinAcross[Sequence @@ Map[KeyUp, {a, b}], "key", "Outer"],
    First -> Rest,

Consolidate[dsDat1, dsDat2]

(* output like above *)

  • $\begingroup$ Thank you very much for your answers. The updated version is much more compact and works for me. There is one tiny issue you might want to consider to revise the code. The keys of the first dataset should be used as reference and hence be placed at the top in the final dataset and the keys of the 2nd dataset at the bottom. This means that the key sdummy in the final dataset should follow the key sfin. $\endgroup$ Commented Feb 17 at 17:41
  • 1
    $\begingroup$ Thank you very much for acceptance. The tiny problem can be easily solved by swapping the arguments: Consolidate[dsDat2, dsDat1] $\endgroup$
    – eldo
    Commented Feb 17 at 17:52
  • 1
    $\begingroup$ Thank you for your effort and time... $\endgroup$ Commented Feb 17 at 18:47
  • $\begingroup$ It's always a pleasure working with you :) $\endgroup$
    – eldo
    Commented Feb 17 at 19:04

It's cumbersome:

a = Normal @ dsDat1 // KeySort;

b = Normal @ dsDat2 // KeySort;

ab = First @ Complement[Keys @ a, Keys @ b]


ba = First @ Complement[Keys @ b, Keys @ a]


k = Intersection[Keys @ a, Keys @ b];

aa = Values @ KeyTake[k] @ a;

bb = Values @ KeyTake[k] @ b;

  KeySort /@ AssociationThread[k -> Table[Join[aa[[i]], bb[[i]]], {i, Length @ aa}]],
  <|"sfin" -> KeySort @ Association @ KeyUnion[{a[ab], b[ba]}]|>,
  <|"sdummy" -> KeySort @ Association @ KeyUnion[{b[ba], a[ab]}]|>] // Dataset

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.