Replace NaN's in table with zero

Hello, I have a 1501x7 table called 'x' and there appears to be NaN's in the fourth and sixth column called "Age" and "height". I would like a way to replace NaN's with zeros. Take note, that I have already tried:

k = find(isnan(x))'; 
x(k) = 0;
% and
x(isnan(x)) = 0;

Yet, neither work because I am using a table, not a matrix. I have also tried converting my table into a cell array, and using these same functions, but they still do not work. They return:"Undefined function 'isnan' for input arguments of type 'cell'" ALSO, please note that the table has columns full of text. So, cell2mat does not work.

[AFiorillo]

When indexing into a table or cell, parentheses and curly braces have very different meanings. Using parenthesis result in a slice of that table/cell, so the result is also a table or a cell. Using curly braces yields the contents of those slices. See here for a more comprehensive explanation on accessing data in tables.

For your code, try

k = find(isnan(x))'; 
x{k} = 0;
% and
x{isnan(x)} = 0;

[xander fong]

I get this: "Undefined function 'isnan' for input arguments of type 'table'.

[hager fouda]

Thank you so much. it works.

Answer 1

[Peter Perkins]

There's a function called standardizeMissing that would replace a non-NaN value with NaN, but normally, replacing NaN with a constant value (as opposed to, for example, some sort estimated value) would be kind of a funny thing to do. I'll assume you have a good reason.

Either of the following should work:

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

>> t = table({'smith';'jones';'doe'},[20;NaN;40],[NaN;72;66],[120;130;140],'VariableNames',{'Name' 'Age' 'Height' 'Weight'});
>> [~,vars] = ismember({'Age' 'Height'},t.Properties.VariableNames)
vars =
     2     3
>> for i=vars, t.(i)(isnan(t.(i))) = 0; end

Hope this helps.

[Chris Hooper]

NaN does not work as desired in unique function:

https://www.mathworks.com/matlabcentral/answers/42561-treating-nan-as-a-unique-value-instead-of-as-a-distinct

An option in the unique fuction to treat NaN as a distinct would negate one reason for replacing NaN.

Answer 2

[Akira Agata]

If you have R2016a or later version, you can use ismissing function and make it much easier.

For example:

 % Make a sample table 'T' and replace 'NaN' with 0
 T = table({'smith';'jones';'doe'},[20;NaN;40],[NaN;72;66],[120;130;140],'VariableNames',{'Name' 'Age' 'Height' 'Weight'});
 idx = ismissing(T(:,{'Age','Height'}));
 T{:,{'Age','Height'}}(idx) = 0;

[Mohamed]

ismissing func. served the purpose.

Answer 3

[Yuting Mou]

I also run across the problem, but there seems to be an easier way:

x.age(isnan(x.age)) = 0;

This is OK in my case

[Dooyoung Kim]

This works for me too! Thanks for the suggestion.

Answer 4

[Steven Lord]

I would use the fillmissing function introduced in release R2016b. See the "Table with Multiple Data Types" example on that documentation page for a demonstration of how to replace NaN values with 0.

[Nurullah]

Thank you,

Answer 5

[J.M. Verduijn]

for i= 1: width(T)
    T.(i)(isnan(T.(i))) = 0;
end    

Works for me, replaces all NaN values in table T with 0

[Lautaro Parada]

This is so simple and accurate! thanks!

Answer 6

[Zachary Smith]

If you are using readtable() to load the table from a file, then you can add the name-value pair argument 'EmptyValue',0 to do this automatically.

Answer 7

[Aron Magesh]

Just use fillmissing function if the data is in a table or timetable.

https://www.mathworks.com/help/matlab/ref/fillmissing.html

[Walter Roberson]

That is what Steven Lord suggested in https://www.mathworks.com/matlabcentral/answers/231096-replace-nan-s-in-table-with-zero#answer_329967

Answer 8

[Andy]

so, in other words:

mainTTable{:,:}(ismissing(mainTTable)) = 0;

Answer 9

[carolina franco]

Hi,

Another simple way to understand what's going on .

For me, it works well in R2014a. You only need to enter the matrix with NaN values without specifying the columns where NaN values are.

%Input
m_data=C{1,1}; % Matrix with NaN values

%Code
        s1=size(m_data,1);
        for i= 1: s1
            msubs=m_data(i,1:end); % Save existing data in ith row of m_data
            msubs=msubs(isnan(m_data(i,1:end))==0); %Substitute matrix/ taking only non-NaN values
            m_data(i,1:end)=0; %Erase all existing values in ith row of m_data
            m_data(i,1:size(msubs,2))=msubs; %Substitute values without NaN
        end

[Stephen23]

Note that all the original question explicitly states that "..I am using a table, not a matrix", and all of the other answers work with tables, not numeric matrices. Tables are a container array type:

https://www.mathworks.com/help/matlab/tables.html

[carolina franco]

Right! Thanks

Answer 10

[Gabor]

T{:,2:4}(ismissing(T{:,2:4})) = 0;

2:4 are the columns which are containing NaN values.

[Walter Roberson]

Interesting, that does work.

T = table({'smith';'jones';'doe'},[20;NaN;40],[NaN;72;66],[120;130;140],'VariableNames',{'Name' 'Age' 'Height' 'Weight'})

T = 3x4 table
      Name       Age    Height    Weight
    _________    ___    ______    ______

    {'smith'}     20     NaN       120  
    {'jones'}    NaN      72       130  
    {'doe'  }     40      66       140  

T{:,2:4}(ismissing(T{:,2:4})) = 0

T = 3x4 table
      Name       Age    Height    Weight
    _________    ___    ______    ______

    {'smith'}    20        0       120  
    {'jones'}     0       72       130  
    {'doe'  }    40       66       140