Grouping similar element based on common columns

4 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
Daniel M
Daniel M on 22 Oct 2019
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 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
Edited: 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)

Categories

Find more on Matrices and Arrays in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!