How to do a right join based on multiple columns as ID?

11 次查看（过去 30 天）
Clarisha Nijman
Clarisha Nijman 2018-11-5
评论： Jürgen 2021-12-31
Dear all,
I want to join set B from the right with set A, based on the first three columns. (The values of the three columns are the ID of both sets.) Sorry, I do not know the terminology in matlab).
All the explanations I do find are expressed in terms of keys variables, variable names and it is really confusing.
Can somebody give me pls some instructions/suggestions?
Given data set A A=[1 2 3 9 8; 1 2 3 3 12; 1 4 3 11 12; 2 3 4 3 12]
and data set B: B=[2 3 4 0 0; 1 2 3 9 8; 1 3 4 1 1; 1 2 3 6 7]
The key variables in each one of these sets are for each line the first three values.
The result should be: [2 3 4 0 0 3 12; 1 2 3 9 8 9 8; 1 2 3 9 8 3 12; 1 3 4 1 1 nAn nAn; 1 2 3 6 7 5 8; 1 2 3 6 7 3 12];
  2 个评论
Guillaume
Guillaume 2018-11-5
All the explanations I do find are expressed in terms of keys variables, variable names and it is really confusing
That is because the data is stored in tables, not matrices. Usually, when columns of a matrix represent different things, it's better to store the data in a table which allows for easier manipulation.
Jürgen
Jürgen 2021-12-31
If I am not wrong, there is a little confusing error (for the novice reader) in the last line of the problem descriptioin:
The result should be: [...; ...; ...; ...; 1 2 3 6 7 5 8; 1 2 3 6 7 3 12];
sould read:
The result should be: [...; ...; ...; ...; 1 2 3 6 7 9 8; 1 2 3 6 7 3 12];

请先登录，再进行评论。

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

采纳的回答

Star Strider
Star Strider 2018-11-5
编辑：Star Strider 2018-11-5
This comes close to doing what you want:
A=[1 2 3 9 8; 1 2 3 3 12; 1 4 3 11 12; 2 3 4 3 12];
B=[2 3 4 0 0; 1 2 3 9 8; 1 3 4 1 1; 1 2 3 6 7];
Ac = mat2cell(A, ones(size(A,1),1), [3 1 1]);
Bc = mat2cell(B, ones(size(A,1),1), [3 1 1]);
TA = cell2table(Ac , 'VariableNames',{'ID','Var1','Var2'});
TB = cell2table(Bc , 'VariableNames',{'ID','Var1','Var2'});
TJ = outerjoin(TA,TB, 'MergeKeys',1, 'Keys','ID', 'LeftVariables',{'ID','Var1','Var2'}, 'RightVariables',{'ID','Var1','Var2'})
Result:
TJ =
7×5 table
ID Var1_TA Var2_TA Var1_TB Var2_TB
___________ _______ _______ _______ _______
1 2 3 9 8 9 8
1 2 3 9 8 6 7
1 2 3 3 12 9 8
1 2 3 3 12 6 7
1 3 4 NaN NaN 1 1
1 4 3 11 12 NaN NaN
2 3 4 3 12 0 0
EDIT — Corrected typographical error in the explanation. Code unchanged.
  10 个评论
显示 8更早的评论
Guillaume
Guillaume 2018-11-7
array2table documentation.
Obviously, the cell array that you give for the 'VariableNames' optional argument must have as many elements that there are columns in your matrix. My [compose('ID%d', 1:3), {'Var1', 'Var2'}] generates 5 variable names, if your actual array has more columns, you'll have to adjust that bit.

请先登录，再进行评论。

更多回答（0 个）

类别

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

标签

Community Treasure Hunt

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

Start Hunting!

Translated by