MATLAB Answers

Replace NaN's in table with zero

828 views (last 30 days)
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.

  2 Comments

AFiorillo
AFiorillo on 24 Jul 2015
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
xander fong on 27 Jul 2015
I get this: "Undefined function 'isnan' for input arguments of type 'table'.

Sign in to comment.

Accepted Answer

Peter Perkins
Peter Perkins on 26 Jul 2015
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.

  0 Comments

Sign in to comment.

More Answers (7)

Yuting Mou
Yuting Mou on 29 Jul 2016
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

  1 Comment

Dooyoung Kim
Dooyoung Kim on 25 Jun 2018
This works for me too! Thanks for the suggestion.

Sign in to comment.


Akira Agata
Akira Agata on 17 Feb 2017
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;

  0 Comments

Sign in to comment.


Steven Lord
Steven Lord on 23 Jul 2018
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.

  0 Comments

Sign in to comment.


J.M. Verduijn
J.M. Verduijn on 8 Feb 2019
for i= 1: width(T)
T.(i)(isnan(T.(i))) = 0;
end
Works for me, replaces all NaN values in table T with 0

  1 Comment

Lautaro Parada
Lautaro Parada on 2 Jul 2019
This is so simple and accurate! thanks!

Sign in to comment.


Andrei
Andrei on 23 Jul 2018
so, in other words:
mainTTable{:,:}(ismissing(mainTTable)) = 0;

  0 Comments

Sign in to comment.


carolina franco
carolina franco on 28 Jan 2020
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

  2 Comments

Stephen Cobeldick
Stephen Cobeldick on 28 Jan 2020
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:

Sign in to comment.


Zachary Smith
Zachary Smith on 19 Mar 2020
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.

  0 Comments

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by