Identify duplicates in a matrix and keep the one with minimum value
4 views (last 30 days)
Show older comments
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.
0 Comments
Accepted Answer
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)
More Answers (1)
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]
[~,X,Y] = unique([A(:,1),round(A(:,2),1)],'rows','stable');
B = [A(X,1:2),accumarray(Y,A(:,3),[],@min)]
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!