MATLAB: Outerjoin tables with identical variable names and unique/non-unique keys

join;merge tablesouterjoin

Hi
I have been trying to merge tables that have identical variable names with unique and non-unique key variables. In the example both tables have both a 'key' and 'Var1'. I essentially would like to match key variables, and horizontally concatenate them, and otherwise if they do not match then add them to the table. Easier to explain with a code example.
a = table({'a';'b';'c';'d'},[1;2;3;4],'VariableNames',{'Key','Var1'});
b = table({'a';'c';'e'},[0.1;0.2;0.3],'VariableNames',{'Key','Var1'});
c = outerjoin(a,b);
d = outerjoin(a,b,'MergeKeys',true);
The result I would like is:
Key Var1_a Var1_b
___ ______ ______
'a' 1 0.1
'b' 2 NaN
'c' 3 0.2
'd' 4 NaN
'e' NaN 0.3
If I just use outerjoin like in table c above then I find that the tables merge, NaN is added to those that do not match up, and it correctly differentiates var1 between tables a and b. However, it does not merge keys that are identical.
Key_a Var1_a Key_b Var1_b
_____ ______ _____ ______
'' NaN 'a' 0.1
'a' 1 '' NaN
'b' 2 '' NaN
'' NaN 'c' 0.2
'c' 3 '' NaN
'd' 4 '' NaN
'' NaN 'e' 0.3
If I use outerjoin like in table d var1 from table b overwrites var1 from table a.
Key Var1
___ ____
'a' 0.1
'a' 1
'b' 2
'c' 0.2
'c' 3
'd' 4
'e' 0.3
Any thoughts? I can always go through and rename the variables for the tables but there are over 30 variables for each table and I have to merge 6 tables together total, so I was hoping to avoid that…
Thanks!

Best Answer

You need to tell outerjoin that only the Key variable is to be as key:
outerjoin(a, b, 'Keys', 'Key', 'MergeKeys', true)