Merging two three column matrices: uniques in cols 1&2, maximum in col 3

Question

I have two matrices:

a = [ 1 10 20; 2 11 22; 3 12 34; 4 13 12];
b = [ 3 12 1;  4 13 25; 5 14 60; 6 15 9 ];

I want to merge them into a single matrix where the rows with the maximum in column 3 used where columns 1 and 2 are identical, i.e. the resulting matrix should look like this:

 c = [ 1 10 20; 2 11 22; 3 12 34; 4 13 25; 5 14 60; 6 15 9];

Any suggestions how to do this easily in MATLAB would be greatly appreciated. I've banged my head on a wall trying to use intersect but to no avail.

Answer 1

When merging the arrays you should make sure that they end up sorted (by col1 then col2 then col3). Fortunately, the union function does exactly that.

In your example, where the values in the 1st and 2nd columns are always unique, we can only observe the values in the 1st column to choose the correct rows. This happens when diff returns a nonzero value (which means this is the bottom row of a group):

a = [ 1 10 20; 2 11 22; 3 12 34; 4 13 12];
b = [ 3 12 1;  4 13 25; 5 14 60; 6 15 9];
c = [ 1 10 20; 2 11 22; 3 12 34; 4 13 25; 5 14 60; 6 15 9 ];

u = union(a,b,'rows');               % this merges and sorts the arrays
r = u(logical([diff(u(:,1)); 1]),:); % since the array is sorted, the last entry will have 
                                     % the maximum value in column 3
assert(isequal(r,c));

Answer 2

You can also use a mix between unique and accumarray.

• Use unique to create an index based on the 2 first columns
• Use accumarray to find the maximum value in the third column according to the index.

The code:

a = [ 1 10 20;2 11 22; 3 12 34; 4 13 12];
b = [3 12 1; 4 13 25; 5 14 60; 6 15 9];

M = [a;b];
[res,~,ind] = unique(M(:,1:2),'rows');
c  = [res,accumarray(ind,M(:,3),[],@max)]

Answer 3

Read about unique

a = [ 1 10 20;2 11 22; 3 12 34; 4 13 12];
b = [3 12 1; 4 13 25; 5 14 60; 6 15 9];

A = [a;b] ;
[c,ia,ib] = unique(A(:,1)) ;

C = A(ia,:)