replacing NaN with dot in a table

I have table A where many numeric variables contain NAN values. How can i replace the NaN values with a dot?

Answer 1

[Guillaume]

Why would you want to do that? While it is possible to do it, it's going to make using that table much harder. Matlab knows what NaN means and can be told to ignore them in calculations, it knows nothing about the meaning of '.'. Furthermore, a NaN is numeric meaning that your columns can all be basic vectors, introducing dot characters means that all columns need to change to cell arrays, complicating access for all values.

If you really want to do it, assuming all the table columns are numeric:

Aascell = table2cell(A);
Aascell{cellfun(@isnan, Aascell)} = '.';
Awithdots = cell2table(Aascell, 'VariableNames', A.Properties.VariableNames)

[Danielle Leblance]

I am receiving an error: Expected one output from a curly brace or dot indexing expression, but there were 43677635 results.

[Walter Roberson]

There is probably a better way, but this works:

Aascell = table2cell(A);
mask = cellfun(@(C) any(isnan(C)), Aascell);
cols = find(any(mask,1));
subtab_as_str = arrayfun(@num2str, A{:,cols}, 'Uniform', 0);
subtab_as_str(mask(:,cols)) = {'.'};
newsubtab = array2table(subtab_as_str, 'VariableNames', A.Properties.VariableNames(cols));

AwithDots = A;
AwithDots(:,cols) = [];
AwithDots = [AwithDots, newsubtab];

Note: this will affect the order of the columns.

[Guillaume]

"I am receiving an error: Expected one output from a curly brace or dot indexing expression"

I can't reproduce this particular error with the various table formats that I've tried, so please post an example of the table you have.

However, while testing the code in my answer I've come across a bug in cell2table (reported to Mathworks and acknowledged) that fails to properly convert cell arrays containing a mixture of scalar numbers and exclusively single character char arrays. The only work around I can think of at the moment would be to use two dots instead of one:

Aascell = table2cell(A);
Aascell{cellfun(@isnan, Aascell)} = '..';
Awithdots = cell2table(Aascell, 'VariableNames', A.Properties.VariableNames)

[Walter Roberson]

I did hit "Expected one output from a curly brace or dot indexing expression" when I tried your code using the sample data KSSV posted.

[Guillaume]

Using R2016b with that sample data and my code, I get "Error using cellfun, Non-scalar in Uniform output, at index 1, output 1. Set 'UniformOutput' to false." I did say "assuming all columns are numeric" (and I should have added scalar)

However, I do get the expected one output error if I change the cellfun to cellfun(@(x) all(isnan, x), Aascell) to fix the above error.

Anyway, this would fix all:

Aascell = table2cell(A);
Aascell(cellfun(@(x) all(isnan(x)), Aascell)) = {'..'}; %can't be a single character due to a bug in cell2table
Awithdots = cell2table(Aascell, 'VariableNames', A.Properties.VariableNames)

Answer 2

[Peter Perkins]

As Guillaume already said, this is almost certainly a bad idea, and you should really ask yourself why you want to do it. You cannot store a dot in a numeric variable, you will have to convert each of those variables to a cell array, at which point they become useless for computation. NaN is the way to indicate missing data, I really, strongly suggest that you use NaN.

But if you really want to do this ...

1) Convert every variable in t to a cell:

>> t = array2table(randn(5));
>> t{2,1} = NaN; t{4,3} = NaN
t =
     Var1        Var2        Var3        Var4        Var5   
    _______    ________    ________    ________    _________
    -1.4557      1.3628      0.8534     -1.3116    -0.088259
        NaN     0.82098     -2.0728     0.65508       1.3722
    -1.1904    -0.53919     -1.2658    -0.52271      -1.8305
    0.12795      2.0646         NaN    -0.16537     -0.96146
    -1.1664      1.1394    -0.32769    -0.54855     -0.47091
>> c = table2cell(t);
>> c(ismissing(t)) = {'.'}
c =
  5×5 cell array
    [-1.4557]    [  1.3628]    [  0.8534]    [ -1.3116]    [-0.088259]
    '.'          [ 0.82098]    [ -2.0728]    [ 0.65508]    [   1.3722]
    [-1.1904]    [-0.53919]    [ -1.2658]    [-0.52271]    [  -1.8305]
    [0.12795]    [  2.0646]    '.'           [-0.16537]    [ -0.96146]
    [-1.1664]    [  1.1394]    [-0.32769]    [-0.54855]    [ -0.47091]

>> t = array2table(c,'VariableNames',t.Properties.VariableNames)
t =
      Var1          Var2          Var3          Var4          Var5    
    _________    __________    __________    __________    ___________
    [-1.4557]    [  1.3628]    [  0.8534]    [ -1.3116]    [-0.088259]
    '.'          [ 0.82098]    [ -2.0728]    [ 0.65508]    [   1.3722]
    [-1.1904]    [-0.53919]    [ -1.2658]    [-0.52271]    [  -1.8305]
    [0.12795]    [  2.0646]    '.'           [-0.16537]    [ -0.96146]
    [-1.1664]    [  1.1394]    [-0.32769]    [-0.54855]    [ -0.47091]

2) Convert only the ones you need to:

>> t = array2table(randn(5));
>> t{2,1} = NaN; t{4,3} = NaN
t =
     Var1        Var2        Var3       Var4        Var5   
    _______    ________    ________    _______    _________
     1.3769    -0.22714    -0.72136     1.8183      0.47225
        NaN     0.55456      1.2013    0.50796    0.0063762
    -1.1656     0.91397    0.051885    0.42044       1.7116
    -2.2186    -0.20018         NaN    0.33693     -0.23943
    0.13924     0.14879    -0.45745    0.45433     -0.84344

>> t2 = varfun(@NaN2Dot,t);
>> t2.Properties.VariableNames = t.Properties.VariableNames
t2 =
      Var1         Var2         Var3        Var4        Var5   
    _________    ________    __________    _______    _________
    [ 1.3769]    -0.22714    [-0.72136]     1.8183      0.47225
    '.'           0.55456    [  1.2013]    0.50796    0.0063762
    [-1.1656]     0.91397    [0.051885]    0.42044       1.7116
    [-2.2186]    -0.20018    '.'           0.33693     -0.23943
    [0.13924]     0.14879    [-0.45745]    0.45433     -0.84344

where NaN2Dot looks like this:

function x = NaN2Dot(x)
nans = isnan(x);
if any(nans)
    x = num2cell(x);
    x(nans) = {'.'};
end

Answer 3

[KSSV]

t = table({'smith';'jones';'doe'},[20;NaN;40],[NaN;72;66],[120;130;140],'VariableNames',{'Name' 'Age' 'Height' 'Weight'}) 
vars = {'Age' 'Height'};
t2 = t{:,vars};
t2(isnan(t2)) = 0;
t{:,vars} = t2

[Danielle Leblance]

no it is working terribly.there isn't only 1 problem to report but many.

[Jan]

@Danielle: Then please explain the problems such that KSSV and others can fix them. We want to assist you to solve your problem. So please help us.