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

8 vues (au cours des 30 derniers jours)
JMG
JMG le 23 Juin 2021
Commenté : dpb le 24 Juin 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 commentaires
Scott MacKenzie
Scott MacKenzie le 23 Juin 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 le 23 Juin 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.

Connectez-vous pour commenter.

Réponse acceptée

dpb
dpb le 23 Juin 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);

Plus de réponses (2)

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

Connectez-vous pour commenter.


Sean Brennan
Sean Brennan le 23 Juin 2021
Modifié(e) : Sean Brennan le 23 Juin 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

Catégories

En savoir plus sur Logical dans Help Center et File Exchange

Produits


Version

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by