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

19 views (last 30 days)
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!

Accepted Answer

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

More Answers (0)

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!