Assigning an index to each row based on column values in a matrix.

Hi, I have a matrix like this.

a= [ 1  2  8

1  2  2

1  3  1

1  2  5

2  4  3

3  5  4

4  6  2

4  7  1

4  6  5

4  6  3

5  7  3

6  8  3

6  9  1

6  9  1

6  9  5

7  6  4

7  8  5

9  8  2]

I want to assign an index to each row based on the following criterion - If the value in the first column and second column is same, I want to compare the third column value and assign an index 1 if it is the minimum among all rows with the same first and second column values and 0 otherwise. So for example, comparing first and second rows, they have the same corresponding first and second column values and second row has the minimum third column value among this. So the program should assign 0 for the first row and 1 for the second row.

Also, If all the three column values are same, I want to assign 1 to any one row and 0 to all other such rows.

So the ideal index vector I want for this is as follows

[0

1

1

0

1

1

1

1

0

0

1

1

1

0

0

1

1

1]

I wrote the following code to do this, but I am unable to get the required output.

    minindex = ones(1,size(a,1))
    for j=1:size(a,1)
        for i = 1:size(a,1)
            if ((a(j,1) == a(i,1)) && (a(j,2) == a(i,2)))
                if (j~=i)
                    minindex(j) = (a(i,3) >  a(j,3));
                end
            end
        end
    end
    minindex'

Can any one help me on how to achieve this?

Thanks in advance!

Answer 1

[Matt Fig]

[As,I] = sortrows(a);
As = any([1 1;diff(As(:,1:2))],2);
I = As(I)

.

.

EDIT The above will not work always, but this should.

[As,I] = sortrows(a);
As = any([1 1;diff(As(:,1:2))],2);
Z = false(size(As,1),1);
Z(I(As)) = true

[Oleg Komarov]

The cleverest...+1

[Jan]

Faster than the FOR approach, easier to understand than the ACCUMARRAY method. +1

[Manu]

Thanks a lot! Very nice and very clever.

[Manu]

Hi Matt, I had an issue when I tried to run this on a = [1 2 947.5; 1 2 20; 1 2 25] and [ 1 2 4; 1 2 2; 1 2 2]. Here I got I = [ 0 0 1] instead of [0 1 0] as it should have been. Can you tell me what seems to be the issue?

[Matt Fig]

Hmmm, you are correct. It looks like I needed another variable in there, but coincidentally it worked on your original data. I have changed the post to a more correct formulation. Thanks for catching that!

Answer 2

[Jan]

Please try this (I cannot run it, because I have no access to Matalb currently):

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

[ua, ia, ja] = unique(a(:, 1:2), 'rows');
n = max(ja);  % Number of different rows
minindex = zeros(size(a, 1), 1);
for i = 1:n
  index = find(ja == i);
  [dummy, q] = min(a(index, 3));
  minindex(index(q)) = 1;
end

I assume there is a shorter way using ACCUMARRAY, but I cannot figure it out.

[Oleg Komarov]

The accumarray solution:

A = [1 2 8; 1 2 2; 1 3 1; 1 2 5; 2 4 3; 3 5 4

4 6 2; 4 7 1; 4 6 5; 4 6 3; 5 7 3; 6 8 3

6 9 1; 6 9 1; 6 9 5; 7 6 4; 7 8 5; 9 8 2];

C = [0;1;1;0;1;1;1;1;0;0;1;1;1;0;0;1;1;1];

[a,b,idx] = unique(A(:,1:2),'rows');

[B loc] = ismember(A,[a accumarray(idx,A(:,3),[],@min)],'rows');

B([false; diff(loc)==0],1) = false;

isequal(B,C)

[Jan]

The ACCUMARRAY is shorter, but not really nicer. In addition UNIQUE and ISMEMBER perform a time-consuming sorting. Therefore I'd prefer the FOR method - but Matt's easier SORTROWS approach rules.

[Manu]

Hi Jan,

I had an issue when I tried to run Matt's code on a = [1 2 947.5; 1 2 20; 1 2 25] and [ 1 2 4; 1 2 2; 1 2 2]. Here I got I = [ 0 0 1] instead of [0 1 0] as it should have been. It looks like since sort rows doesn't sort from top down, the 'As' in the second step is [2 3 1] instead of [2 1 3]. Is there any way to correct this?

Thanks!