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

4 views (last 30 days)
Per Vatsvag on 22 Aug 2022
Commented: Voss on 23 Aug 2022
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?

Voss on 22 Aug 2022
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
Voss on 23 Aug 2022
You're welcome!

Stephen23 on 23 Aug 2022
Edited: Stephen23 on 23 Aug 2022
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
Per Vatsvag on 23 Aug 2022
Thanks- simple and efficent.

Categories

Find more on Logical in Help Center and File Exchange

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!