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

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:

Answer 1

[Guillaume]

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.

[John]

Thanks, @Guillaume! I would like to fix the problem at the creation, but it simply doesn't work! I have the following file (.xlsx format, see screenshot above). Import data > import as table imports my table, not with NaN (as I need it for calculation), but as 'NaN', which is very annoying because similar files with less NaN (on my .xlsx file) get imported as NaN.

[Guillaume]

Attach the file rather than a screenshot, so we can try importing it ourselves.

Have you tried:

t = readtable('PFR24weeks.xlsx', 'TreatAsEmpty', 'NaN');

[John]

Yes, I tried this one, it generates the " Error using readtable (line 197) TREATASEMPTY must not contain numeric literals." .txt file attached now

[Guillaume]

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]

brilliant, thanks a lot, Guillaume!!

Answer 2

[Peter Perkins]

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.

[John]

Thanks a lot for your answer, Peter! The "workaround" solution (save as .csv / .txt and read it as such) seems to work.