For loop to replace n/a with NaN in a table
5 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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
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.
Réponse acceptée
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);
0 commentaires
Plus de réponses (2)
Jeremy Hughes
le 23 Juin 2021
What does this do?
data= readtable("Wetland_Water_Quality_data_1.csv","TreatAsMissing","n/a");
4 commentaires
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.
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
0 commentaires
Voir également
Catégories
En savoir plus sur Logical dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!