removing all rows with duplicate information in two columns

Hi there,

I have a large matrix with 5 columns. As an example ,imagine a=[1 2 3 4 5; 1 3 4 5 6; 1 2 3 5 5] is just a small sample of the larger matrix. I am trying to remove the rows where columns 1 and columns 5 match. So for my example matrix a, I would need to remove BOTH rows 1 and 3 since they have the same values in column 1 AND column 5. I know that the unique function can get me to the point where one of the rows is removed but I need both of them removed. I have been at this all day and am stumped (and it is probably a simple answer!)

Any help is much appreciated!

Answer 1

[Star Strider]

Try this:

a=[1 2 3 4 5; 1 3 4 5 6; 1 2 3 5 5];
[~,~,idx] = unique(a(:,[1 5]),'rows');
tally = accumarray(idx,(1:numel(idx)).',[],@(x){idx(x)});
notsame = a(cellfun(@(x)numel(x)==1,tally),:)

producing:

notsame =
     1     3     4     5     6

I also tried it on a larger version. It took a bit of time for me to prove that this works with the larger matrix.

[Erika Wells]

Thanks for the suggestion!

When I ran with bigger mtarix, it still keeps one of the duplicate rows.

I am trying to play around with your code to see if I can get rid of both rows.

[Star Strider]

It did not when I ran it with a larger random matrix.

[Star Strider]

I tested this rigorously and it appears to work with every random ‘a’ matrix I give it:

[~,~,idx] = unique(a(:,[1 5]),'rows');
rn = (1:numel(idx)).';
tally = accumarray(idx,rn,[],@(x){cumsum(x)});
notsameidx = tally(cellfun(@(x)numel(x)==1,tally))
notsame = a([notsameidx{:}],:)

It produces the result you want (as ‘notsame’) with your matrix.

[Erika Wells]

Thank you so much!! That works!! I struggled with it all night.

Very much appreciated.

[Star Strider]

As always, my pleasure!

(I obviously struggled with it as well!)

Answer 2

[Erika Wells]

Here is what I get with this matrix

a=[ 1 13 1 2 4; 1 1 1 2 18; 1 21 1 2 21;

1 40 1 1 21;1 10 1 1 29;1 27 1 1 41;2 12 1 1 29; 2 16 1 2 29];

[~,~,idx] = unique(a(:,[1 5]),'rows');

tally = accumarray(idx,(1:numel(idx)).',[],@(x){idx(x )});

notsame = a(cellfun(@(x)numel(x)==1,tally ),:);

notsame=

1 13 1 2 4

1 1 1 2 18

1 40 1 1 21

1 10 1 1 29

But what I want is:

1 13 1 2 4

1 1 1 2 18

1 10 1 1 29

1 27 1 1 41