Effacer les filtres
Effacer les filtres

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

7 vues (au cours des 30 derniers jours)
John
John le 11 Jan 2017
Commenté : John le 12 Jan 2017
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:

Réponse acceptée

Guillaume
Guillaume le 11 Jan 2017
Modifié(e) : Guillaume le 11 Jan 2017
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 commentaires
Guillaume
Guillaume le 12 Jan 2017
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.
John
John le 12 Jan 2017
brilliant, thanks a lot, Guillaume!!

Connectez-vous pour commenter.

Plus de réponses (1)

Peter Perkins
Peter Perkins le 12 Jan 2017
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 commentaire
John
John le 12 Jan 2017
Thanks a lot for your answer, Peter! The "workaround" solution (save as .csv / .txt and read it as such) seems to work.

Connectez-vous pour commenter.

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!

Translated by