Grouping similar element based on common columns

2 views (last 30 days)
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 Comments
Poulomi Ganguli
Poulomi Ganguli on 23 Oct 2019
A and B are the two matrices, C is the desired output after merging matrices A and B.

Sign in to comment.

Accepted Answer

Sebastian Bomberg
Sebastian Bomberg on 23 Oct 2019
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 Comment
Poulomi Ganguli
Poulomi Ganguli on 23 Oct 2019
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")

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by