Find matrix rows that have 3 common values, store the rows and the values

Suppose I have a 500000x5 matrix and for each row I need to find rows that have 3 common elements within the first 4 elements of the row but different 5th element and store the common values and the sum of the 5th elements to a new matrix.

For example:

1 2 3 4 1

1 2 3 5 2

Would yield a row like this in a new matrix

1 2 3 3

Of course this could be done with 2 nested for loops and if statements but the calculation takes like 10 hours. I need something that takes a few minutes even with 500k rows.. Is this possible with Matlab?

[Matt J]

What happens if there are 4 common elements, differing in the 5th element, e.g.,

1 2 3 4 10
1 2 3 4 7

Would this generate multiple rows in the resulting matrix, one for every subset of 3 elements?

1 2 3 17
1 2 4 17
2 3 4 17

Also, what happens if the elements are in different order, e.g.,

1 2 3 4 10
4 3 2 1 7

Are these considered to be matching rows?

[Pseudoscientist]

There are no rows that have duplicate in terms of the 4 first elements

1 2 3 17

1 2 4 17

2 3 4 17

Would generate

Using 1st row:

1 2 17

2 3 17

Using 2nd row:

1 2 17

2 4 17

Using 3rd row:

2 3 17

2 4 17

and without duplicates:

1 2 17

2 3 17

2 4 17

There shouldnt be any rows that have 4 same elements in whatever order :)

Thank you very much!

[Matt J]

Fine, but what about the ordering within the rows? Does it matter? Since the following rows have {1,3,4} in common, do they form a match even though the {1,3,4} lie in different orders and in different columns?

1 6 3 4 10
3 4 8 1 7

If so, does it matter in what order the common elements appear in the output? By my reasoning, the above could produce the following result,

1 3 4 17

[Pseudoscientist]

Yes the following result is correct, displayig the result in ascending order is very good

[Matt J]

OK, see my answer then.

Answer 1

[Matt J]

I'll call your original 5 column matrix A.

 D=cell(4,1);
 a5=A(:,5);
 As=sort(A(:,1:4),2);  
 
 for i=1:4  %small loop
    
   Triangles{i}=  [As(:, setdiff(1:4,i)),a5];
   
 end
 Q=cell2mat(Triangles);
 result=consol(Q(:,1:3),Q(:,4));
 
    function out=consol(B,b)
    
       
       [C,~,j]=unique(B,'rows');
       nc=size(C,1);
       c=accumarray(j,b,[nc,1]);
       out=[C,c];

        keep=histcounts(j,1:nc+1)>1;
        out=out(keep,:);

    end

[Pseudoscientist]

Hey thank you for help, maybe I used it in correctly but it gave me this:

"Error using horzcat

Dimensions of matrices being concatenated are not consistent."

I have attached data_A.mat that is first 100 rows of the 500k row matrix

[Matt J]

I've edited the code, but see my comment above.

[Pseudoscientist]

This solution worked, thank you !

[Pseudoscientist]

Hello, I just noticed something is not right.

In the matrix "result" in the 4th column there are values 1, which should not be possible as the 4th column should contain sums of the 5th elements of rows that have 3 out of 4 common elements.

in data_A.mat, the 5th column contains only values 1 and 2, so the sum cant be 1.

data_A.mat contains points that define tetrahedrons, we are trying to look for triangles that are shared by two tetras and then give the triangle a 4th value that signifies which two types of tetras shared it

edit: I have attached the entire dataset (data_A_all.mat)

[Matt J]

OK. Try my latest modification.

[Pseudoscientist]

Seems to work by the looks of it!

[Matt J]

Seems to work by the looks of it!

No, I don't think it was. See my latest revision. Below also is some small test input and output so we can verify visually that it is correct.

A =

     1     2     3    21     2
     1     2     3    29     1
     1     2     4    12     2
     1     2     4    29     1
     1     2    12    21     2
     1     3     6     8     1
     1     3     6    29     1


result =

     1     2     3     3
     1     2     4     3
     1     2    21     4
     1     2    29     2
     1     3     6     2

[Pseudoscientist]

Hmm quite interesting, the logic seems to work but when the results are plotted, the result is not that great..

Imagine a sphere filled with a tetrahedral mesh, the tetras have values 1 and 2. Now where the tetras face each other, we should have a triangular surface of values 3.

The first image is what should be. The second image is what is calculated as triangles with values 3. The third image is the sphere.

The first image has about 43k triangles and

the second image

tri_3 = find(result(:,4)==4);

result1 = result(tri_3,:);

has about 3k

[Matt J]

OK. One more version (I think this is it).