Identify duplicates in a matrix and keep the one with minimum value

Dear All Community members,

I have a matrix, examplified as "A" below, where I need to identify the duplicates in the first and second column and keep the one with minimum value in the third column. Values in the first and second column are considered duplicated with a 1 digit number (e.g. 15.61-> 15.6 equal to 15.56->15.6).

A=[1500 12 1; 1500 10 2; 1500 11 3;1500 15.61 6;1500 17 5;1500 15.56 4;2000 12.08 8;2000 13 8;2000 12.12 7;1300 14 10;1300 10 11;1300 11 12;900 14 14;900 14 13;900 14.04 15;900 11 16;900 12.23 17;900 12.24 18];

Example: Values in column 1 and 2 in row 4 and 6 are equal. I want to store row 6 since the value in the third column is lower compared to the value in row 4 (4 vs 6). The values in the first and second column are also equal in row 7 and 9. I want to keep row 9 since the value in the third column in row 9 is lower than in row 7. And so on...

The output matrix shall be:

B=[1500 10 2;1500 11 3;1500 12 1;1500 15.6 4;1500 17 5;2000 12.1 7;2000 13 8;1300 14 10;1300 10 11;1300 11 12;900 14 13;900 11 16;900 12.2 17];

Can anyone assist me?

Thanks in advance.

Answer 1

[Voss]

A = [1500  12  1; 1500  10  2; 1500  11  3;1500  15.61  6;1500  17  5;1500  15.56  4;2000  12.08  8;2000  13  8;2000  12.12  7;1300  14  10;1300  10  11;1300  11  12;900  14  14;900  14  13;900  14.04  15;900  11  16;900  12.23  17;900  12.24  18];

Ar = round(A,1);                                    % round to 1 decimal place
[uAr,~,jj] = unique(Ar(:,[1 2]),'rows','stable');   % get set of unique rows of first 2 columns of Ar
rows_to_keep = [];
for ii = 1:size(uAr,1)
    idx = find(jj == ii);
    if isscalar(idx)
        rows_to_keep(end+1) = idx;                  % only one -> keep it
        continue
    end
    [~,min_idx] = min(Ar(idx,3));                   % more than one -> use the min of column 3
    rows_to_keep(end+1) = idx(min_idx);
end
B = A(rows_to_keep,:);
disp(B); % (the order of rows of this B is different than your B; I'm not sure how you determine the order)

   1.0e+03 *

    1.5000    0.0120    0.0010
    1.5000    0.0100    0.0020
    1.5000    0.0110    0.0030
    1.5000    0.0156    0.0040
    1.5000    0.0170    0.0050
    2.0000    0.0121    0.0070
    2.0000    0.0130    0.0080
    1.3000    0.0140    0.0100
    1.3000    0.0100    0.0110
    1.3000    0.0110    0.0120
    0.9000    0.0140    0.0130
    0.9000    0.0110    0.0160
    0.9000    0.0122    0.0170

[Askeladden2]

Works perfect.

Thank you very much!

[Voss]

You're welcome!

Answer 2

[Stephen23]

Simple and efficient MATLAB approach using UNIQUE and ACCUMARRAY:

A = [1500,12,1; 1500,10,2; 1500,11,3; 1500,15.61,6; 1500,17,5; 1500,15.56,4; 2000,12.08,8; 2000,13,8; 2000,12.12,7;1300,14,10; 1300,10,11; 1300,11,12; 900,14,14; 900,14,13; 900,14.04,15; 900,11,16; 900,12.23,17; 900,12.24,18]

A = 18×3

1.0e+03 * 1.5000 0.0120 0.0010 1.5000 0.0100 0.0020 1.5000 0.0110 0.0030 1.5000 0.0156 0.0060 1.5000 0.0170 0.0050 1.5000 0.0156 0.0040 2.0000 0.0121 0.0080 2.0000 0.0130 0.0080 2.0000 0.0121 0.0070 1.3000 0.0140 0.0100

[~,X,Y] = unique([A(:,1),round(A(:,2),1)],'rows','stable');
B = [A(X,1:2),accumarray(Y,A(:,3),[],@min)]

B = 13×3

1.0e+03 * 1.5000 0.0120 0.0010 1.5000 0.0100 0.0020 1.5000 0.0110 0.0030 1.5000 0.0156 0.0040 1.5000 0.0170 0.0050 2.0000 0.0121 0.0070 2.0000 0.0130 0.0080 1.3000 0.0140 0.0100 1.3000 0.0100 0.0110 1.3000 0.0110 0.0120

[Askeladden2]

Thanks- simple and efficent.

Appreciate your contribution.