MATLAB Answers


Merging matrices by comparison of values of several columns

Asked by Joanna Smietanska on 4 Oct 2019
Latest activity Commented on by Joanna Smietanska on 4 Oct 2019
I have 2 matrices of different size. Matrix A is 1000x18, while B has 950x10 size. The values in columns 2,3 and 4 can be common for both data sets. My goal is to merge them into single matrix consisting of shared values from columns 2:4 and corresponding to them values from column 5 in A and columns 6 and 8 from B. I tried to use this code, but it fails in generating combined matrix C:
[j] = intersect(out1(:,2:4),out2(:,2:4),'rows');
C = [out2(j,5) out1(j,[6 8])];
I would appreciate any help.


Sign in to comment.

1 Answer

Answer by Guillaume
on 4 Oct 2019
 Accepted Answer

Nearly right
[~, whichArows, whichBrows] = intersect(out1(:, 2:4), out2(:, 2:4), 'rows');
C = [out2(whichBrows, 5), out1(whichArows, [6 8])]; %you may also want to include columns 2 to 4
Note that if the keys (columns 2:4) are present more than once in either input you'll only get one of them in the output.
Another option is to convert your matrices to tables. Then you can perform an innerjoin, outerjoin or a plain join:
tout1 = array2table(out1);
tout2 = array2table(out2);
joined = innerjoin(tout1, tout2, 'Keys', 2:4, 'LeftVariables', 5, 'RightVariables', [6 8]);

  1 Comment

It works great, thank you very much

Sign in to comment.