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

MATLABreadtable

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!

Best Answer

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'}
Related Question