How to delete rows in a table

27 views (last 30 days)
Christine Abou Nasr
Christine Abou Nasr on 29 Mar 2017
Commented: Rik on 31 Mar 2017
Hello, i have to analyse some data of sensors at different dates. I want to import the data and remove the entire row when there is a data missing = cell that contain 0 (and later NaN). After import my table looks like:
Date Info1 info2 info3... info26
NaN 30 NaN 40 ... NaN
734567 0 40 40 ... 40
785365 0 0 40 ... 0
789456 40 40 0 ... 40
789456 40 40 0 ... 40
785412 40 40 40... 40
778858 40 40 40 ... 40
In this case only the two last rows should be kept. I need the data of info1,2,3 to stay at the same date when i have all the data. When i import the data the table is in dataset, so i tried converting it to double, but it doesn't work.
This is my code:
%if true
T = importfile8('file.csv', 1, 16786);
T2=double(T(:,1:25));
i=1;
col=1;
for i=1:16782
for col=1:25
if T2(i,col)==0
T2(i,:)=[];
else col=col+1;
end
end
i=i+1;
end
There are 16786 rows and 25 columns and I get this error message: "Index exceeds matrix dimensions.
Error in ValuesFilter (line 11) if T2(i,col)==0" I just want to specify that i work with the R2013a version of matlab too. Thank you!
  1 Comment
KL
KL on 29 Mar 2017
Please format the question in a better way, this is clearly not readable.

Sign in to comment.

Accepted Answer

Rik
Rik on 29 Mar 2017
Edited: Rik on 29 Mar 2017
As KL mentioned, this code is unreadable. Press the {}Code button after selecting your code for proper formatting. However, I think I have an idea of what is going wrong.
You are removing rows, but then continuing to the next row. You should either loop backwards ( for i=100:-1:1 ), or better yet, generate a boolean vector which you can use to delete all rows in one go. The second option should be faster and easier to read. And it eliminates the necessity of a backwards loop.
Edit: after having a look at your formatted code, I would advise you something like this:
T = importfile8('file.csv', 1, 16786);
T2=double(T(:,1:25));
idx=prod(T2,2)==0;%the product value of the row will be 0 if any value is 0
T2(idx,:)=[];
Also, in Matlab you don't have to increment your loop-variable. Matlab will follow the vector you enter. In while-loops you need to do this, but in for-loops you don't.
  2 Comments
Christine Abou Nasr
Christine Abou Nasr on 29 Mar 2017
Thank you, that worked perfectly!
Rik
Rik on 31 Mar 2017
If you want to check for NaNs in the same way, you can use this (this uses the fact that prod([9 NaN 7 4]) will yield NaN_:
T = importfile8('file.csv', 1, 16786);
T2=double(T(:,1:25));
prods=prod(T2,2);%the product value of the row will be 0 if any value is 0
idx=prods==0 | isnan(prods);
T2(idx,:)=[];

Sign in to comment.

More Answers (1)

Peter Perkins
Peter Perkins on 31 Mar 2017
Edited: Peter Perkins on 31 Mar 2017
It's hard to tell what you actually have. You say both "table" and "dataset". The latter is a class in the Statistics Toolbox, the former is a newer class in core MATLAB that, unless you are using a pre-R2013b version of MATLAB, you probably want to be using instead, by calling readtable on your CSV file.
The first column of numbers look like datenums. Unless you're using pre-R2014b, you probably want to use datetimes. Given the timestamps, if you have R2016b or later, you probably want to look at timetables. And contrary to what you've said, it looks like you have 27 columns of numbers, one of datenums and 26 of other values.
If this is really a table, and you are using R2016b or later, use rmmissing (probably preceeded by standardizeMissing to deal with the "zeros as missing"). If not, then something similar to what Rik suggested, tailored to a table, does what you want:
i = any(t{:,:} == 0,2) | any(isnan(t{:,:},2);
t(i,:) = [];

Community Treasure Hunt

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

Start Hunting!