How to delete rows in a table

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!

[KL]

Please format the question in a better way, this is clearly not readable.

Answer 1

[Rik]

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.

[Christine Abou Nasr]

Thank you, that worked perfectly!

[Rik]

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,:)=[];

Answer 2

[Peter Perkins]

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,:) = [];