For loop to replace n/a with NaN in a table
8 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
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 Commenti
Scott MacKenzie
il 23 Giu 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.
Risposta accettata
dpb
il 23 Giu 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 Commenti
Più risposte (2)
Jeremy Hughes
il 23 Giu 2021
What does this do?
data= readtable("Wetland_Water_Quality_data_1.csv","TreatAsMissing","n/a");
4 Commenti
dpb
il 24 Giu 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
il 23 Giu 2021
Modificato: Sean Brennan
il 23 Giu 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 Commenti
Vedere anche
Categorie
Scopri di più su Logical in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!