How to combine two different datasets to create a unified dataset

Question

I have two different datasets : dsDat1 and dsDat2:

ClearAll[dat1, dat2, col1, col2, row1, row2, dsDat1, dsDat2];
SeedRandom[123];
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.

Answer 1

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

We can put this in one function:

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

Consolidate[dsDat1, dsDat2]

(* output like above *)

Answer 2

It's cumbersome:

a = Normal @ dsDat1 // KeySort;

b = Normal @ dsDat2 // KeySort;

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

"sfin"

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

"sdummy"

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

aa = Values @ KeyTake[k] @ a;

bb = Values @ KeyTake[k] @ b;

Join[
  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