Removing rows - duplicates based on a condition

조회 수: 12 (최근 30일)
Nick
Nick 2019년 12월 1일
댓글: Turlough Hughes 2019년 12월 1일
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
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
Nick 2019년 12월 1일
Hi Rik,
It is a prety big data set. 3million rows x 50 columns.

댓글을 달려면 로그인하십시오.

채택된 답변

dpb
dpb 2019년 12월 1일
편집: dpb 2019년 12월 1일
[~,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
Turlough Hughes
Turlough Hughes 2019년 12월 1일
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];
Then you just sub A2 in for A into dbp's solution.

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Shifting and Sorting Matrices에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by