writetable replace NaN with blanks in Matlab

Question

Given a Matlab table that contains many NaN, how can I write this table as an excel or csv files where the NaN are replaced by blanks?

I use the following function:

T = table(NaN(5,2),'VariableNames',{'A','C'})

writetable(T, filename)

I do not want to replace it with zeros. I want that the output file:

has blanks for NaN and
that the variable names are included in the output.

Possible duplicate of [Replace NaN's in table with 0](https://stackoverflow.com/questions/36315985/replace-nans-in-table-with-0) — Flynn, Aug 18 '17 at 13:17
Look here. It looks like you can convert it to a matrix, or do a loop. https://stackoverflow.com/questions/36315985/replace-nans-in-table-with-0 — Flynn, Aug 18 '17 at 13:18

Sardar Usama · Accepted Answer · 2017-08-21T22:27:19.007

You just need xlswrite for that. It replaces NaNs with blanks itself. Use table2cell or the combination of table2array and num2cell to convert your table to a cell array first. Use the VariableNames property of the table to retrieve the variable names and pad them with the cell array.

data= [T.Properties.VariableNames; table2cell(T)];
%or data= [T.Properties.VariableNames; num2cell(table2array(T))];
xlswrite('output',data);

Sample run for:

T = table([1;2;3],[NaN; 410; 6],[31; NaN; 27],'VariableNames',{'One' 'Two' 'Three'})

T =

  3×3 table

    One    Two    Three
    ___    ___    _____

    1      NaN     31  
    2      410    NaN  
    3        6     27

yields:

Although the above solution is simpler in my opinion but if you really want to use writetable then:

tmp = table2cell(T);             %Converting the table to a cell array
tmp(isnan(T.Variables)) = {[]};  %Replacing the NaN entries with []
T = array2table(tmp,'VariableNames',T.Properties.VariableNames); %Converting back to table
writetable(T,'output.csv');      %Writing to a csv file

gnovice · Answer 2 · 2017-08-21T21:09:23.130

I honestly think the most straight-forward way to output the data in the format you describe is to use xlswrite as Sardar did in his answer. However, if you really want to use writetable, the only option I can think of is to encapsulate every value in the table in a cell array and replace the nan entries with empty cells. Starting with this sample table T with random data and nan values:

T = table(rand(5,1), [nan; rand(3,1); nan], 'VariableNames', {'A', 'C'});

T = 
            A                    C        
    _________________    _________________

    0.337719409821377                  NaN
    0.900053846417662    0.389738836961253
    0.369246781120215    0.241691285913833
    0.111202755293787    0.403912145588115
    0.780252068321138                  NaN

Here's a general way to do the conversion:

for name = T.Properties.VariableNames  % Loop over variable names
  temp = num2cell(T.(name{1}));        % Convert numeric array to cell array
  temp(cellfun(@isnan, temp)) = {[]};  % Set cells with NaN to empty
  T.(name{1}) = temp;                  % Place back into table
end

And here's what the table T ends up looking like:

T = 
             A                      C         
    ___________________    ___________________

    [0.337719409821377]    []                 
    [0.900053846417662]    [0.389738836961253]
    [0.369246781120215]    [0.241691285913833]
    [0.111202755293787]    [0.403912145588115]
    [0.780252068321138]    []

And now you can output it to a file with writetable:

writetable(T, 'sample.csv');

I actually thought of a similar approach until I realised that `xlswrite` does that itself. Do you mind if I include that in my answer? Although I avoided the use of loop and `cellfun` but not sure if it would be any better/faster — Sardar Usama, Aug 21 '17 at 21:16
@SardarUsama: If you have another variant for a solution, I'd go ahead and add it. I don't mind. :) — gnovice, Aug 21 '17 at 21:19

writetable replace NaN with blanks in Matlab

2 Answers2

Linked