How to turn NaN values in only numerical columns into -999?

3 ビュー (過去 30 日間)
Leon
Leon 2019 年 10 月 17 日
コメント済み: Leon 2019 年 10 月 17 日
I have some data with both numerical and string columns. See attached for an example (aa.xlsx).
It has four columns like this:
Header1 Header2 Header3 Header4
1, 4, -9, ABC123
2, NaN, 0, NaN
5, 6, NaN, ABC789
My goal is to convert any NaN values that are in only numerical columns into -999, while leaving the NaN values in string columns intact. The end results should look like something like this:
Header1 Header2 Header3 Header4
1, 4, -9, ABC123
2, -999, 0, NaN
5, 6, -999, ABC789
Here is the code I know will work, if all of my columns are numerical:
%convert any NaN into -999
T1 = readtable ('aa.xlsx', 'PreserveVariableNames',true)
Ind_table = isnan(T1{:,:});
T1{:,:}(Ind_table) = -999;
How should I modify it so that it won't do the conversion for columns that are made up of strings?
Many thanks!

採用された回答

Adam Danz
Adam Danz 2019 年 10 月 17 日
編集済み: Adam Danz 2019 年 10 月 17 日
When you create your table, the missing values in the Header4 column will not appear as NaNs since that column contains character arrays. Instead, they will just be an empty char array. A very annoying thing with tables is that they do not support subscript indexing. So the solution below converts the table to a cell array, replace the NaN values in numeric columns, and then puts the cell array back into a table with matching properties as your original table.
T1 = readtable ('aa.xlsx', 'PreserveVariableNames',true);
T1cell = table2cell(T1);
isnum = varfun(@isnumeric,T1,'output','uniform'); % ID columns that are numeric
ismiss = ismissing(T1); % find missing values
T1cell(ismiss & isnum) = {-999};
T1New = cell2table(T1cell);
T1New.Properties = T1.Properties; % your new table with NaN replacement
Result
T1New =
3×4 table
Header1 Header2 Header3 Header4
_______ _______ _______ __________
1 4 -9 {'ABC123'}
2 -999 0 {0×0 char}
5 6 -999 {'ABC789'}
  2 件のコメント
Adam Danz
Adam Danz 2019 年 10 月 17 日
Glad I could help!

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

その他の回答 (2 件)

Sebastian Bomberg
Sebastian Bomberg 2019 年 10 月 17 日
You can have fillmissing apply only to the numeric variables directly:
fillmissing(T1,"constant",-999,"DataVariables",@isnumeric)
  1 件のコメント
Leon
Leon 2019 年 10 月 17 日
Very cool and it works!
Thank you very much!

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


Walter Roberson
Walter Roberson 2019 年 10 月 17 日
fillmissing(T1,'constant',{-999,-999,-999,'NaN'})
Note that this will use the character vector 'NaN' (three characters) in place of the numeric NaN entries in column 4, as it is not possible to have numeric entries in a column devoted to character vectors.
  5 件のコメント
Leon
Leon 2019 年 10 月 17 日
Weird that the code works for some of my files but not for all of them. I now get an error.
Here is the code I use:
% replacing any NaN values with -999 for numerical columns, and
% any NaN values with 'N/A' for string columns.
char_vars = varfun(@iscell, T1, 'OutputFormat', 'uniform'); % identify the columns that are not numerical (strings)
constants = num2cell(-999*ones(1,width(T1))); % create a one-row matrix of -999
constants(char_vars) = {'N/A'}; % replace -999 with 'NaN' for string columns
new_T1 = fillmissing(T1, 'constant', constants); % fill the empty cells within each respective columns
T1 = new_T1;
Here is the error:
Error using fillmissing/checkArrayType (line 548)
Invalid fill constant type.
Error in fillmissing/fillTableVar (line 182)
[intConstVj,extMethodVj] = checkArrayType(Avj,intMethod,intConstVj,extMethodVj,x,true);
Error in fillmissing/fillTable (line 160)
B.(vj) = fillTableVar(indVj,A.(vj),intMethod,intConst,extMethod,x,useJthFillConstant,useJthExtrapConstant,mavj);
Error in fillmissing (line 138)
B = fillTable(A,intM,intConstOrWinSize,extM,x,dataVars,ma);
Error in FunctionLoading (line 32)
new_T1 = fillmissing(T1, 'constant', constants); % fill the empty cells within each respective columns

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

カテゴリ

Find more on Tables in Help Center and File Exchange

タグ

製品


リリース

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by