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

9 views (last 30 days)
JMG
JMG on 23 Jun 2021
Commented: dpb on 24 Jun 2021
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
  2 Comments
Scott MacKenzie
Scott MacKenzie on 23 Jun 2021
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
JMG on 23 Jun 2021
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.

Sign in to comment.

Accepted Answer

dpb
dpb on 23 Jun 2021
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);

More Answers (2)

Jeremy Hughes
Jeremy Hughes on 23 Jun 2021
What does this do?
data= readtable("Wetland_Water_Quality_data_1.csv","TreatAsMissing","n/a");
  4 Comments
JMG
JMG on 24 Jun 2021
Oh you are right I didnt see that thank you. I used the code you wrote and it worked thanks!
dpb
dpb on 24 Jun 2021
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.

Sign in to comment.


Sean Brennan
Sean Brennan on 23 Jun 2021
Edited: Sean Brennan on 23 Jun 2021
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

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!