For loop to replace n/a with NaN in a table

I am trying to read a table that has a mixture of variable types. I need to change all of the n/a characters to NaN but my current code is not working so I was wondering if there is something wrong with my code or is there a better way to approach this?

My current code is:

data= readtable("Wetland_Water_Quality_data_1.csv");
[a,b]=size(data);
s1='n/a';
for x=1:a
    for y=1:b
    tf= strcmp(s1,data(x,y));
    if tf==1
        data(x,y)=NaN;
    end
    end
end

[Scott MacKenzie]

if the other data in the columns are numeric, then the 'n/a' entries should automatically convert to NaN, via readtable. Perhaps post the data file as well.

[JMG]

This is the csv I am working with. The columns past field pH and are the ones I am having issues with changing because I need them to be numeric values for graphing.

Answer 1

[dpb]

This is a case readtable needs some help -- use an import options object...

opt=detectImportOptions('Cape_Breton_Highlands_NP_Wetland_Water_Quality_2007-2016_data_1.csv','VariableNamingRule','preserve');
opt.VariableTypes(6)={'datetime'};
opt.VariableTypes(7:end-1)={'double'};
opt=setvaropts(opt,'Time','InputFormat','HH:mm','FillValue',datetime('00:00','InputFormat','HH:mm'));
data=readtable('Cape_Breton_Highlands_NP_Wetland_Water_Quality_2007-2016_data_1.csv',opt);
data.DateTime=datetime(data.Year,data.Month,data.Day,hour(data.Time),minute(data.Time),0);

Answer 2

[Jeremy Hughes]

What does this do?

data= readtable("Wetland_Water_Quality_data_1.csv","TreatAsMissing","n/a");

[JMG]

That worked! Thank you very much!

[dpb]

That's interesting -- I didn't see that it did here --

>> data=readtable('Cape_Breton_Highlands_NP_Wetland_Water_Quality_2007-2016_data_1.csv',"TreatAsMissing","n/a",'VariableNamingRule','preserve');
>> head(data)
>> ans(:,end-3:end)
ans =
  8×4 table
    Turbidity (NTU) – Lab measurement    Nitrate (mg/L) – Lab measurement    Ammonia nitrogen – Lab measurement     Comments 
    _________________________________    ________________________________    __________________________________    __________
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
                   NaN                              {0×0 char}                              NaN                    {0×0 char}
>> 

left the numeric data as char because it still read them as char, not double; just were empty instead of double NaN.

This is R2020b; you on a later release by any chance?

[JMG]

Oh you are right I didnt see that thank you. I used the code you wrote and it worked thanks!

[dpb]

Ah-so! Easy enough with so many columns and the bum ones off to the RHS...was wondering about that since I couldn't make it without setting the data type explicitly.

Answer 3

[Sean Brennan]

Here's an answer - not elegant or vectorized, but it should work. This uses the test file listed earlier as the input.

Given that many of the input columns are specific types, it might help with vectorization to change the import type using detectImportOptions. This might clean up the code below significantly.

data= readtable("Cape_Breton_Highlands_NP_Wetland_Water_Quality_2007-2016_data_1.csv");
[a,b]=size(data);

data_in_cell_format = table2cell(data); % Convert all data to cells, since string command not supported in tables (?!)
data_in_string_format = string(data_in_cell_format); % Convert again to strings (NOW we can convert cells to strings)
bad_indices = strcmpi(data_in_string_format,'n/a'); % These are the "bad" indices, 1 = N/A, 0 otherwise
for ith_column = 1:b
    for jth_row = 1:a
        if bad_indices(jth_row,ith_column)
            data(jth_row,ith_column) = {'nan'};
        end
    end
end