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