Remove duplicate elements in an Array based on one of the columns

Hi , I have a matrix 5000x4 in size. It has rows which have the identical elements in the 1st and 2nd column but different elements in the 4th column. I would like to be able to remove the elements where the 1st and 2nd columns are repeated , and Have a smaller value in the corresponding 4th column.

A =[1 2 3 4;
    1 2 4 6;
    3 5 7 2;
    3 5 4 5;
    3 5 3 7;]

In this example , i would like to remove the 1st row and the 3rd and 4th rows too. The 4h column of these rows are lesser than the competing rows' 4th column. I hope its not too confusing.

Thanks, Appreciate the help !

Answer 1

[Guillaume]

Here is one way to do it:

A =[1 2 3 4;
  1 2 4 6;
  3 5 7 2;
  3 5 4 5;
  3 5 3 7;]

[~, ~, rows] = unique(A(:, [1 2]), 'rows');
todelete = arrayfun(@(r) rows == r & A(:, 4) < max(A(rows == r, 4)), 1:max(rows), 'UniformOutput', false);
A(any(cell2mat(todelete), 2), :) = []

[Ask]

This works like Gold ! Just one thing though , I apologize , but could you perhaps explain how it works ? I apologize again , but I am terribly new to Matlab.

Thanks again !

[Guillaume]

the third return value of unique gives you an identical index (from 1 to ?) for each row with identical column 1 and 2.

arrayfun is equivalent to a for loop. It iterates from 1 to number_of_different_rows and for each of these numbers:

• find which of the indices returned by unique is equal to the iterator
• find which rows of A have the 4th column smaller than the max of the rows that are identical for the current iterator

So, for each unique index returned by unique, arrayfun returns a logical column vector the same size as the number of rows in A.

These logical vectors are concatenated by cell2mat into a matrix and any row in that matrix that has a one is deleted.

To understand the arrayfun, it may be better to rewrite as a loop. This is what it is doing:

[~, ~, rows] = unique(A(:, [1 2]), 'rows');
for i = 1:max(rows);
   identical_rows_for_current_value = rows == r;
   smaller_rows_for_current_value = A(:, 4) < max(A(identical_rows_for_current_value, 4);
   rows_to_delete_for_current_value = identical_rows_for_current_value & smaller_rows_for_current_value
   todelete{i} = rows_to_delete_for_current_value;
end
todelete = cell2mat(todelete{i}); %convert cell array into matrix 
todelete = any(todelete, 2); %any row with a 1 is to be deleted
A(todelete, :) = []; %do the deletion

If you step through this latest code, you'll see how it works.

[Akash Menon]

I have been searching for this solution for a day now! Many thanks! The 'arrayfun' does take a few tens of seconds to run on my PC but it works perfectly. I had a similar case where I had an array with two coloumns.

One was the kilometer points and the other depths. There were many duplicates of the kilometer points (after I had rounded down a bigger dataset) and I only wanted the unique values of those kilometer points but also their corresponding depths- there were a few depths to choose from but they were so close to each other I could choose the max or min value.

Thic code really helped! Hope the screenshots below help others too!

My original matrix (NewMat3):

Code adapted to my case: (i just used the max values as well into the new matrix)

New Matrix:

[Vijay]

Not sure, but this didn't work for me.

I found the following simple solution to the same problem.

https://www.mathworks.com/matlabcentral/answers/448432-remove-duplicate-rows-based-on-subset-of-row-elements