Removing rows - duplicates based on a condition

Question

Nick 2019-12-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/494161-removing-rows-duplicates-based-on-a-condition

评论： Turlough Hughes 2019-12-1

采纳的回答： dpb

在 MATLAB Online 中打开

Hi,

I have a table with a number of columns and would like to delete some rows based on some conditions.

Here is an example:

A = table([1;2;3;3;3;3;4;4;4], [ 0;0;0;0;1;1;1;1;1], [1999;1999;1999;2000;2001;2002;2000;2001;2004]);
A.Properties.VariableNames = {'ID' 'Size' 'Date'};

The condidions are:

If ID is the same and within that ID the size = 1 then if there are more than one rows, keep the row that contains the earliest Date for that ID.

So in this case the new table would look like this:

B = table([1;2;3;3;3;4], [ 0;0;0;0;1;1], [1999;1999;1999;2000;2001;2000]);
B.Properties.VariableNames = {'ID' 'Size' 'Date'};

Thanks!

2 个评论
显示无隐藏无

Rik 2019-12-1

I can't come up with anything other than a solution involving a nested loop, which will have horrible performance on larger sets of data. How large do you expect your real data to be?

Nick 2019-12-1

Hi Rik,

It is a prety big data set. 3million rows x 50 columns.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

dpb 2019-12-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/494161-removing-rows-duplicates-based-on-a-condition#answer_404121

编辑：dpb 2019-12-1

在 MATLAB Online 中打开

[~,ia]=unique(A(A.Size==1,1),'first');
B=[A(A.Size==0,:);A([ia+find(A.Size==0,1,'last')],:)];

returns

>> B
B =
  6×3 table
    ID    Size    Date
    __    ____    ____
    1     0       1999
    2     0       1999
    3     0       1999
    3     0       2000
    3     1       2001
    4     1       2000
>>

I'm guessing the selection on Size==1 is only artificial given no duplicates by inspection. Logic works more simply without having to not subset that grouping (the mess about the complicated indexing expression for B)

The solution relies on the Date field being sorted so unique returns the first/lowest date...if possibly not, then sort first.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Turlough Hughes 2019-12-1

在 MATLAB Online 中打开

The above also assumes that A.Size is sorted, if this is not the case in your data you should first sort the Size and then the Date where Size=1 as follows:

A = table([1;2;3;3;3;3;4;4;4;5;5;5;5], [ 0;0;0;0;1;1;1;1;1;0;0;1;1], ...
[1999;1999;1999;2000;2001;2002;2000;2001;2004;2000;2005;2005;2001], ...
'VariableNames',{'ID','Size','Date'}) % sample data to illustrate
T=sortrows(A,2); % Sort the size first.
T2=sortrows(T(find(T.Size),:),3) % then where Size=1, sort Date
A2=[A(A.Size==0,:);T2];