Grouping similar element based on common columns

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?

[Poulomi Ganguli]

Can I use table properties to join these two matrices using outer/inner join?

[Daniel M]

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]

A and B are the two matrices, C is the desired output after merging matrices A and B.

Answer 1

[Sebastian Bomberg]

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.

[Poulomi Ganguli]

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")