Merge tables with different columns and for matching columns update missing values in left table with values from right table

119 次查看（过去 30 天）
Maximilian Schwarz
Maximilian Schwarz 2022-12-22
编辑： Maria Battle 2024-4-25
I would like to merge two or more tables with different lenght, different and matching column names and one identical key T. Columns present only in the right table should be simply merged to the left table as with outerjoin. For columns present in both tables missing values in the left table should be replaced with nonmissing values from the right table. Is this possible in a single function? (see example below)
A = table([1;2;3;4;5], {'A', 'A', 'A', 'A','A'}', ...
'VariableNames',{'T', 'Var1'})
B = table([4 5 6 7]',{'B' 'B' 'B' 'B'}',...
'VariableNames',{'T' 'Var1'})
C = table([1 2 3]',{'C' 'C' 'C'}',...
'VariableNames',{'T' 'Var2'})
% Merge on T and final output should look like this
Final = table([1 2 3 4 5 6 7]',{'A', 'A', 'A', 'A','A', 'B', 'B'}', {'C' 'C' 'C' 'NaN' 'NaN' 'NaN' 'NaN'}', ...
'VariableNames',{'T','Var1', 'Var2'})

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

回答（2 个）

Eric Sofen
Eric Sofen 2022-12-27
Ran in:
This is a tricky combination, and not doable in a single join right now. As you say, the outerjoin of A and C to create a table with Var1 and Var2 is the easy part. However, we end up with rows with duplicate keys from the outerjoin of A and B. That requires some cleanup. I think the approach I outline below is general enough to tackle problems more involved than the example you gave.
A = table([1;2;3;4;5], {'A', 'A', 'A', 'A','A'}', ...
'VariableNames',{'T', 'Var1'});
B = table([4 5 6 7]',{'B' 'B' 'B' 'B'}',...
'VariableNames',{'T' 'Var1'});
C = table([1 2 3]',{'C' 'C' 'C'}',...
'VariableNames',{'T' 'Var2'});
D = outerjoin(A,B,MergeKeys=true)
D = 9×2 table
T Var1 _ _____ 1 {'A'} 2 {'A'} 3 {'A'} 4 {'A'} 4 {'B'} 5 {'A'} 5 {'B'} 6 {'B'} 7 {'B'}
% Clean up the rows with duplicate keys. Keep the "last" one to get the
% desired overwriting behavior.
[u,ia] = unique(D.T,"last");
D = D(ia,:)
D = 7×2 table
T Var1 _ _____ 1 {'A'} 2 {'A'} 3 {'A'} 4 {'B'} 5 {'B'} 6 {'B'} 7 {'B'}
Final = outerjoin(D,C,MergeKeys=true)
Final = 7×3 table
T Var1 Var2 _ _____ __________ 1 {'A'} {'C' } 2 {'A'} {'C' } 3 {'A'} {'C' } 4 {'B'} {0×0 char} 5 {'B'} {0×0 char} 6 {'B'} {0×0 char} 7 {'B'} {0×0 char}
Maria Battle
Maria Battle 2024-4-5
编辑：Maria Battle 2024-4-25
table_ab = joindata(A, B);
C.Var1 = string(zeros(height(C), 1)); % explanation below
all_tables = joindata(table_ab, C);
To use joindata(), "The first two columns of both frame tables contain the segment start and segment end points. The other columns in the tables contain data associated with the frame." Bascially you needed a Var1 in C to use joindata because joindata requires 2 columns to be the same vars across all tables being joined. See help page for more details.
Another approach: if your data have time or duration variables, synchronize() makes horizonal concatenation very easy. If you don't have time variables, you could possibly impute a dummy duration variable just to use synchronize.
Another example using joindata:
t1 = table([1;2;3], [1.1;2.1;3.1], [4;5;6], 'VariableNames', {'index', 'gas', 'electric'});
t2 = table([4;5;6;7], [1;2;3;3.5], [4;5;6;6.5], [7;8;9;9.5], 'VariableNames', {'index', 'gas', 'solar', 'wind'});
t3 = table([8;9], ["fee"; "fi"], ["me"; "you"], ["p1"; "p2"], 'VariableNames', {'index', 'gas', 'hood1', 'hood2'});
Ta = joindata(t1, t2);
Tb = joindata(Ta, t3);

类别

在 Help Center 和 File Exchange 中查找有关 Resizing and Reshaping Matrices 的更多信息

标签

产品


版本

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by