fast delete of rows in a table

5 views (last 30 days)
hi
I've got a table with 120k rows, and I need to delete some rows if there is a specific condition.
I wrote this:
for i=1:(size(db,1)-1)
if db.INTERVENTION(i)==1 && isequal(db.SETTLEMENTDATE(i-1),db.SETTLEMENTDATE(i)) && isequal(db.DUID(i-1),db.DUID(i))
db(i-1,:)=[];
end
end
This code takes a long time to execute.
Is there a faster way?
thanks!

Image Analyst on 27 May 2020
Try this:
numRows = size(db,1)-1;
rowsToDelete = false(numRows, 1);
for k = 2 : numRows % Has to start at 2, right? Since you're using k-1. Don't use i, the imaginary variable, as a loop index.
if db.INTERVENTION(k)==1 && isequal(db.SETTLEMENTDATE(k-1),db.SETTLEMENTDATE(k)) && isequal(db.DUID(k-1),db.DUID(k))
rowsToDelete(k) = true;
end
end
db(rowsToDelete, :) = []; % Remove these rows.
% Or equivalently
%db = db(~rowsToDelete, :); % Extract everything BUT the rows to delete.
3 CommentsShow 1 older commentHide 1 older comment
Image Analyst on 27 May 2020
I think the reason being is that when you removed a single row from the table, it had to rebuild the table each time. If you give it a vector, it has to rebuild the table only once.
yes, it makes a lot of sense ;)

darova on 27 May 2020
Write indices and delete outside the for loop
ind = logical(db*0);
for i=1:(size(db,1)-1)
if db.INTERVENTION(i)==1 && isequal(db.SETTLEMENTDATE(i-1),db.SETTLEMENTDATE(i)) && isequal(db.DUID(i-1),db.DUID(i))
ind(i,:) = true;
end
end
db(ind) = [];
seems ok, but I don't get the inizialization of ind. Line
ind = logical(db*0);
gives "Undefined operator '*' for input arguments of type 'table'"
Did I got wrong?
Thanks anyway!
darova on 27 May 2020
See Image Analyst answer below. It's correct

Categories

Find more on Logical in Help Center and File Exchange

R2019b

Community Treasure Hunt

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

Start Hunting!