MATLAB: Grouping similar element based on common columns

matricesmatrix arraymatrix manipulation

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?

Best Answer

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.
Related Question