How to overwrite 'NaN' strings in a table using the for-loop

5 次查看（过去 30 天）
John
John 2017-1-11
评论： John 2017-1-12
I want to replace the strings 'NaN' with NaN in my table using a loop / or any other strategy that returns NaN, leaving all else unchanged. Here my code:
for k = 1:height(Z_24TimeSteps)
if isnan(Z_24TimeSteps{k})
Z_24TimeSteps{k} = nan;
end
end
Table:
Another strategy I tried:
Z_24TimeSteps(cellfun(@isnan,Z_24TimeSteps))=nan;
How can I use this rationale for the "table" format?
Screenshot of .xlsx file to be imported:

请先登录，再回答此问题。

采纳的回答

Guillaume
Guillaume 2017-1-11
编辑：Guillaume 2017-1-11
It's puzzling why you think that isnan is going to return true on a string (even if that string is 'NAN'). To find if a string, or a cell array of strings is the string 'NAN':
strcmp(stringorcellarrayofstring, 'NAN')
It's also puzzling why you think that you can index tables with just one index. Z_24TimeSteps{k} is invalid syntax. Or why you think that cellfun works with tables.
Anyway, there's an additional complication in that your columns are a blend of strings and numbers. Thankfully, strcmp doesn't mind being passed numbers, so you could do:
isnanstring = strcmp(Z_24TimeSteps.x1COVGY, 'NAN');
Z_24TimeSteps.x1COVGY{isnanstring} = nan;
Note that the underlying container for the column will still be a cell array. To change that column to a matrix, assuming that it only contains scalar numerics, no strings, no matrices:
Z_24TimeSteps.x1COVGY = cell2mat(Z_24TimeSteps.x1COVGY);
edit: forgot to say. rather than trying to fix the mess afterward, it would be better to fix the creation / import in the first place. If the table has been imported with readtable, then the 'TreatAsEmpty' option can be used to directly change defined strings into numeric nan.
  5 个评论
显示 3更早的评论
Guillaume
Guillaume 2017-1-12
Right, you don't need the 'TreatAsEmpty' since matlab already recognises the string 'NaN' as a numeric NaN.
The main issue is that your file is encoded in 16-bit unicode (UTF16-LE), which matlab does not support. It can still read your files (in R2016b at least), but you get a bunch of warnings. Reading the file is as simple as:
t = readtable('PFR24weeksTXT.txt', 'FileEncoding', 'UTF16-LE');
If you can, change the settings of whatever is generating the files so that they're in format that matlab supports. UTF8 is probably the best.

请先登录，再进行评论。

更多回答（1 个）

Peter Perkins
Peter Perkins 2017-1-12
You almost certainly don't want to the specific thing you've stated, i.e. "replace the strings 'NaN' with NaN". You'd end up still having a cell array, which isn't usually a good way to store numbers. I think you want to address the root cause.
Excel does not have any notion of NaN. So I'm guessing those those NaNs in your screenshot are text fields in the spreadsheet. That may be contributing to this. It's hard to say exactly how you got to where you are because there are different ways you could call readtable, and readtable itself has seen improvements for interpreting spreadsheets over the last few releases. It is probably possible to read things into MATLAB in the right form, but I think you're going to have to provide a small example that's exactly like what you have and what you've done.
One work-around might be to save the .xlsx as a .csv and read it as a text file. Another would be to loop over the vars in the table and convert them to numeric. Something like
for i = indicesOfTheVarsToConvert name = t.Properties.VariableNames{i}; t.(name) = convertToNumeric(t.(name)); end
where convertToNumeric is a function you'll write to convert those cell arrays to numeric. It may be something along these lines:
>> c = {'NaN' 'NaN' 1 2 3}
c =
1×5 cell array
'NaN' 'NaN' [1] [2] [3]
>> j = strcmp(c,'NaN')
j =
1×5 logical array
1 1 0 0 0
>> c(j) = {NaN}
c =
1×5 cell array
[NaN] [NaN] [1] [2] [3]
>> cell2mat(c)
ans =
NaN NaN 1 2 3
Hope this helps.
  1 个评论
John
John 2017-1-12
Thanks a lot for your answer, Peter! The "workaround" solution (save as .csv / .txt and read it as such) seems to work.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Logical 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by