Merge multiple tables with three keys

12 views (last 30 days)
DavidL88
DavidL88 on 3 Aug 2022
Answered: Lei Hou on 31 Aug 2022 at 3:32
I have three tables with the same categories of data. All three tables have these three keys - "Outcome","Range","ROI". There are 4 possible outcomes, 8 possible ranges and 68 possible ROIs (all ranges and all ROIs can be within any of the four outcomes). I want to merge them using these three keys. I tried outerjoin which works for two but not three simultaneously. I tried merging two first and then adding the third to the new table (of the first two joined together). The issue with this approach is that I have to choose which of the first two table keys I want to use in the new merged table. So if I choose the first table keys this is not a problem for rows where table 1 and 2 have the same data or table 3 and table 1 have the same data. But for rows where table 3 and table 2 have the same data and table 1 does not then the data here is not matched (because I used keys matching first and third table). Is the a way to do this? Possibly by making a master table where all possible variatons are included and then matching the three tables to this master key?
Example:
Outcome = ["11_right", "12_right"]';
Range = ["80ms,110ms" "110ms,140ms", "140ms,195ms"]';
ROI = ["Red", "Yellow", "Blue, "Green"];

Accepted Answer

Lei Hou
Lei Hou on 31 Aug 2022 at 3:32
Hi David,
I'm not sure whether I understand your problem correctly. Based on my understanding, outerjoin should help you with your use case. outerjoin will return the union of the unique combinations of the three keys from the input tables. Therefore, no matter whether the key combination of the right table exists in the left table or not, the output will contain all combinations with filling missing value for the rows that the combination was not found.
outcome1 = ["11_right"; "12_right"];
range1 = ["80ms,110ms";"110ms,140ms"];
ROI1 = ["Red"; "Yellow"];
Var1 = [1.1;1.2];
t1 = table(outcome1,range1,ROI1,Var1,'VariableNames',{'Outcome', 'Range', 'ROI', 'Var1'});
outcome2 = ["11_right"; "12_right"];
range2 = ["140ms,195ms";"110ms,140ms"];
ROI2 = ["Red"; "Yellow"];
Var2 = [2.1;2.2];
t2 = table(outcome2,range2,ROI2,Var2,'VariableNames',{'Outcome', 'Range', 'ROI', 'Var1'});
outcome3 = ["11_left"; "12_left"];
range3 = ["110ms,140ms";"140ms,195ms"];
ROI3 = ["Blue"; "Green"];
Var3 = [3.1;3.2];
t3 = table(outcome3,range3,ROI3,Var3,'VariableNames',{'Outcome', 'Range', 'ROI', 'Var1'});
>> t1
t1 =
2×4 table
Outcome Range ROI Var1
__________ _____________ ________ ____
"11_right" "80ms,110ms" "Red" 1.1
"12_right" "110ms,140ms" "Yellow" 1.2
>> t2
t2 =
2×4 table
Outcome Range ROI Var1
__________ _____________ ________ ____
"11_right" "140ms,195ms" "Red" 2.1
"12_right" "110ms,140ms" "Yellow" 2.2
>> t3
t3 =
2×4 table
Outcome Range ROI Var1
_________ _____________ _______ ____
"11_left" "110ms,140ms" "Blue" 3.1
"12_left" "140ms,195ms" "Green" 3.2
>> t_1 = outerjoin(t1,t2,'Keys',{'Outcome', 'Range', 'ROI'},'MergeKeys',true)
t_1 =
3×5 table
Outcome Range ROI Var1_t1 Var1_t2
__________ _____________ ________ _______ _______
"11_right" "140ms,195ms" "Red" NaN 2.1
"11_right" "80ms,110ms" "Red" 1.1 NaN
"12_right" "110ms,140ms" "Yellow" 1.2 2.2
>> t_2 = outerjoin(t_1,t3,'Keys',{'Outcome', 'Range', 'ROI'},'MergeKeys',true)
t_2 =
5×6 table
Outcome Range ROI Var1_t1 Var1_t2 Var1
__________ _____________ ________ _______ _______ ____
"11_left" "110ms,140ms" "Blue" NaN NaN 3.1
"11_right" "140ms,195ms" "Red" NaN 2.1 NaN
"11_right" "80ms,110ms" "Red" 1.1 NaN NaN
"12_left" "140ms,195ms" "Green" NaN NaN 3.2
"12_right" "110ms,140ms" "Yellow" 1.2 2.2 NaN

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!

Translated by