MATLAB Answers

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

9 ビュー (過去 30 日間)
JMG
JMG 2021 年 6 月 23 日
コメント済み: dpb 2021 年 6 月 24 日
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 件のコメント
JMG
JMG 2021 年 6 月 23 日
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.

サインインしてコメントする。

採用された回答

dpb
dpb 2021 年 6 月 23 日
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);

その他の回答 (2 件)

Jeremy Hughes
Jeremy Hughes 2021 年 6 月 23 日
What does this do?
data= readtable("Wetland_Water_Quality_data_1.csv","TreatAsMissing","n/a");
  4 件のコメント
dpb
dpb 2021 年 6 月 24 日
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
Sean Brennan 2021 年 6 月 23 日
編集済み: Sean Brennan 2021 年 6 月 23 日
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

製品


リリース

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by