Find matching rows based on specific column values

Hi there, I have a matrix of 3 columns by N rows, e.g:

1 2 5

4 5 7

1 2 9

6 3 2

4 5 1

7 1 3

I want to select all rows where they have the same value in the first and second columns, so for the example above I want:

1 2 5

4 5 7

1 2 9

4 5 1

Any help is appreciated, thanks.

Answer 1

[Constantino Carlos Reyes-Aldasoro]

This is rather easy if you know how to address matrices properly. Take your matrix:

>> a=[1 2 5
4 5 7
1 2 9
6 3 2
4 5 1
7 1 3];

Then you need to define the rows, take the first

>> a(1,1:2)
ans =
     1     2

Then compare

>> (a(:,1:2)==(a(1,1:2)))
ans =
  6×2 logical array
   1   1
   0   0
   1   1
   0   0
   0   0
   0   0

Similarly for the second row

>> (a(:,1:2)==(a(2,1:2)))

ans =

  6×2 logical array

   0   0
   1   1
   0   0
   0   0
   1   1
   0   0

And now use an or

>> (a(:,1:2)==(a(1,1:2)))|(a(:,1:2)==(a(2,1:2)))

ans =

  6×2 logical array

   1   1
   1   1
   1   1
   0   0
   1   1
   0   0

Finally, you only need one location per row, so use any and then pass that as the address of the rows:

>> a(any((a(:,1:2)==(a(1,1:2)))|(a(:,1:2)==(a(2,1:2))),2),:)

ans =

     1     2     5
     4     5     7
     1     2     9
     4     5     1

>> 

And your problem is solved!

If it does not, let me know. If it does, please accept the answer

[Martin Grant]

Thanks very much!

[Constantino Carlos Reyes-Aldasoro]

My pleasure. Addressing a matrix in Matlab is one of the most powerful and effective techniques to solve problems.

[In-chan Kim]

Hi Constantino, thanks for your answer!

I have a question that extends on this.

First, let me see if I understand what you've suggested correctly.

I take it your solution works when we know that we are looking for matches of the first 2 columns in the first 2 rows in all the other rows, in this case specifically [1 2 X] and [4 5 X].

Is this correct?

How could I approach this where I have a lot more rows, and I don't know specifically how they will match, and with what? With the pure objective of wanting to find all matching rows.

That is, there may be [9 5 X], and [3 8 X] and so on and on, and there are lots of different combinations, too many to manually do (a(:,1:2)==(a(1,1:2))) for.

[Constantino Carlos Reyes-Aldasoro]

Hello

I take it your solution works when we know that we are looking for matches of the first 2 columns in the first 2 rows in all the other rows, in this case specifically [1 2 X] and [4 5 X].

Is this correct?

Correct, you are addressing the matrix for those locations, not for the values 1 2 4 5 but for whatever is in those locations.

How could I approach this where I have a lot more rows, and I don't know specifically how they will match, and with what? With the pure objective of wanting to find all matching rows.

That is, there may be [9 5 X], and [3 8 X] and so on and on, and there are lots of different combinations, too many to manually do (a(:,1:2)==(a(1,1:2))) for.

The key now is how you define your problem so you want to find if row 79 has the first two values as row 99? If understand correctly, then you have to loop over and repeat, i.e.

for currentRow=1:numRows
       ... a(:,1:2)==(a(currentRow,1:2) ....
end 

so that you test for all rows.

Hope that helps.