Find rows of a matrix that belong to another one

Question

I haven't been able to find existing sources about what i ask, though i think others have had that problem before, so feel free to mark as duplicate if it is and i'll delete this question.

I have two matrices :

A a Mx5 matrix whose 4 first columns are integers and whose 5th column is filled with doubles.

B a Nx4 matrix whose 4 columns are the same than the first 4 columns in A but in a different order and with some rows that don't belong to A(we always have M<N).

What I want to do is :

Get the indexes (with respect to the bigger matrix B), of the rows in A in which the 4 first values fit a row in B
Fill a Nx1 vector with the values in the 5th column of A at the indexes where there was a matching row and zeros elsewhere.

I do have some working code here, but it's pretty slow as N will be at least 2 millions rows (Approx 7s, which is too big as the process will be repeated). Plus in order to do what i want, i for now have to sort the rows in both matrices with respect to the first 4 columns, thus taking even more time.

Working code :

N=size(B,1);
A=sortrows(A,1:4);
[B,ind]=sortrows(B);
[~,index]=sort(ind)
FinValues=zeros(N,1);
FinValues(ismember(B,A(:,1:4),'rows'))=A(:,5);
FinValues=FinValues(index);

Toy example :

A= [134 3 16998 2 2.0234e-2;...
    134 2 15557 1 3.0234e-3];

B=[17778 1 16559 4;...
   134 2 15557 1;...
   134 3 16998 2;...
   15554 2 16998 3];

Desired output :

FinValues=

0
3.0234e-3  
2.0234e-2
0

I hope this is clear enough, thanks in advance!

Your working code is missing the definition of N and for your toy example outputs FinValues = [0.0030 0.0202 0 0]; - different order. — IKavanagh
check this post... this might help stackoverflow.com/questions/33316919/… — horseshoe
@IKavanagh : I added the definition of N in the code. The reason you have this output is that you redefined N but didn't clear the variables so A and B had been sorted. Clear variables and run again it should work ^^ — BillBokeey

IKavanagh IKavanagh · Accepted Answer · 2015-10-29T10:50:26

This should be quicker, as it completely removes the need for any sorting. We can use the second output argument of ismember to find the indexes of the rows immediately and then use these to directly index F filling it with A.

>> [~, idx] = ismember(A(:, 1:4), B, 'rows');
>> F = zeros(N, 1);
>> F(idx) = A(:, 5)
F =
         0
    0.0030
    0.0202
         0

Find rows of a matrix that belong to another one

1 Answers