How to find NaN in a cell of type table then delete the entire row

Hi there,

If anyone can help me out please that would be greatly appreciated. I have this huge data that consist of 20k rows and 75 columns in excel (.csv). But I created a small working file which looks like this.

As seen, there are NaN values in this data table. So what I want to do is:

• go through the data and locate all the NaN values.
• if a row contains at least 1 NaN in the cell, I want to store the whole row to another list.
• once its gone through the list, I want to create a function that deletes the listed rows at the same time.

At the end, Im hoping to not see rows that contains any NaN values in any cell. is this possible? I have been trying to figure it out for the past couple weeks. tried bits and pieces from different solutions. nothing just seems to work. it would be greatly appreciated if anyone out there knows. PLEASE.

[madhan ravi]

attach your data as .mat file or .xlsx file

[Andrea Del Rosario]

my raw data comes out as (.csv) file so I have attached the excel file here.

Answer 1

[madhan ravi]

Try the below example:

Age = [38;43;38;40;49];
Smoker = logical([1;0;1;0;1]);
Height = [71;69;64;67;64];
Weight = [176;163;131;133;119];
BloodPressure = [124 93; 109 77; 125 83; 117 NaN; 122 80];
T = table(Age,Smoker,Height,Weight,BloodPressure);
T=T{:,:};   % adapt this to your data
T(~any(isnan(T),2),:) % adapt this to your data

[Andrea Del Rosario]

T = readtable('testfile3.csv');  
T = T{:,:};   
T(~any(isnan(T),2),:) 

-----------------------------------------------------------------------

Is this what you mean? I tried doing this but have error:

Cannot concatenate the table variables 'PcbTestID' and 'TestTime', because their types are double and cell.

[madhan ravi]

https://www.mathworks.com/help/matlab/ref/rmmissing.html - this is exactly what you need (released in 2016b)

T = readtable('testfile3.csv');
T=rmmissing(T)

Gives:

T =

  4×16 table

    PcbTestID        TestTime          PcbID       Verify12VInput    Verify12VInputPassed    VerifyCurrentDraw    VerifyCurrentDrawPassed    Rail12VConditioned    Rail12VConditionedPassed    Rail5V    Rail5VPassed    RailNve5V    RailNve5VPassed    Rail1V8    Rail1V8Passed    Rail3V3
    _________    ________________    __________    ______________    ____________________    _________________    _______________________    __________________    ________________________    ______    ____________    _________    _______________    _______    _____________    _______

        1        27/06/2018 10:20    'G3000044'        11.962               'TRUE'                0.3321                  'TRUE'                   11.954                   'TRUE'             4.9425       'TRUE'         -5.021         'TRUE'         1.8014        'TRUE'        3.3138 
        2        02/07/2018 17:47    'G3000042'        11.953               'TRUE'                0.3344                  'TRUE'                   11.945                   'TRUE'              4.938       'TRUE'        -5.0233         'TRUE'         1.7997        'TRUE'        3.3116 
        3        03/07/2018 10:35    'G3000042'        11.966               'TRUE'                0.3312                  'TRUE'                   11.958                   'TRUE'             4.9392       'TRUE'        -5.0216         'TRUE'         1.8012        'TRUE'        3.3132 
        8        03/07/2018 10:44    'G3000045'        11.966               'TRUE'                0.3208                  'TRUE'                   11.959                   'TRUE'              4.954       'TRUE'          -4.96         'TRUE'         1.7986        'TRUE'        3.3157

[Andrea Del Rosario]

Currently, I only have MATLAB2016a for my licensing, and tried using the rmmissing and it said : Undefined function or variable 'rmmissing'. :(

[madhan ravi]

Ahhhh... knew it! , try using trial version perhaps ,..

[Andrea Del Rosario]

I tried my online licensing and it worked! Thank you!

My problem after this is that, the data has a lot of duplication. If you take a look at ('PcbID') column, the ID have some dupplication (there are couple of 'G3000042 . Is there a way to remove this duplication and retain the unique data?

[madhan ravi]

Anytime :) ,if it answered your original question ( "How to find NaN in a cell of type table then delete the entire row?" ) make sure to accept the answer , post a separate question about that.

[Andrea Del Rosario]

Thank you so much again for helping this question! Ill post another question instead! :)

[Luna]

unique function may help you on this but how will you decide which duplicate you want to remove or keep?

[Andrea Del Rosario]

I was thinking as you can see there is a 'TestTime' in my set of data. the last time stamp determines that it's the unique row I want to keep! Would you be able to guide me on this?