Grouping similar element based on common columns

2 次查看（过去 30 天）
Poulomi Ganguli
Poulomi Ganguli 2019-10-22
编辑： Poulomi Ganguli 2019-10-23
Hello:
I have two matrices, which I wish to group together based on common first three column elements. If no common column element found, it should be filled by 'NaN' element. Here is the two matrices A and B and resulting matrix C after the operation:
A = [6119200 44.55 -0.87 0.91
6123400 47.05 0.54 0.68
6122141 49.55 2.99 0.39
6421500 50.87 5.72 0.54
6607851 51.25 0.45 0.72]
B = [6123400 47.05 0.54 1.14
6122141 49.55 2.99 0.75
6421500 50.87 5.72 0.73
6607851 51.25 0.45 0.70
6607851 51.25 0.45 0.26
6607650 51.41 0.31 0.47]
C = [6119200 44.55 -0.87 0.91 NaN
6123400 47.05 0.54 0.68 1.14
6122141 49.55 2.99 0.39 0.75
6421500 50.87 5.72 0.54 0.73
6607851 51.25 0.45 0.72 0.26
6607650 51.41 0.31 0.47 NaN]
Any help how should I do so?
  3 个评论
显示 1更早的评论
Daniel M
Daniel M 2019-10-22
What about the 4th and 5th row in B? One of them is dropped.
6607851 51.25 0.45 0.70
6607851 51.25 0.45 0.26
Poulomi Ganguli
Poulomi Ganguli 2019-10-23
A and B are the two matrices, C is the desired output after merging matrices A and B.

请先登录，再进行评论。

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

采纳的回答

Sebastian Bomberg
Sebastian Bomberg 2019-10-23
You can use outerjoin:
% Convert matrices A and B to tables
TA = array2table(A,"VariableNames",["Key"+(1:3) "A"])
TB = array2table(B,"VariableNames",["Key"+(1:3) "B"])
% Perform outerjoin with respect to columns 1 to 3 as key variables
TC = outerjoin(TA,TB,"Keys",1:3,"MergeKeys",true,"Type","full")
% Convert result back to matrix C
C = table2array(TC)
Note that in your C matrix, the 2nd NaN will appear in the second to last column with the values originally in matrix A.
Also, as Daniel M pointed out, both rows with identical keys will show up in the joined table.
  1 个评论
Poulomi Ganguli
Poulomi Ganguli 2019-10-23
编辑：Poulomi Ganguli 2019-10-23
TA = array2table(A,'VariableNames',{'Key1','Key2','Key3','A'});
>> TB = array2table(B,'VariableNames',{'Key1','Key2','Key3','B'});
>> TC = outerjoin(TA,TB,"Keys",1:3,"MergeKeys",true,"Type","full")

请先登录，再进行评论。

更多回答（0 个）

类别

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

标签

Community Treasure Hunt

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

Start Hunting!

Translated by