Merge multiple tables with three keys

12 次查看（过去 30 天）
DavidL88
DavidL88 2022-8-3
回答： Lei Hou 2022-8-31
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"];

请先登录，再回答此问题。

采纳的回答

Lei Hou
Lei Hou 2022-8-31
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

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Tables 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by