How do replace NaN values and delete full zero's columns from matlab table?

Dear experiences ...

i have a data stored in excel file which contain some empty cells (no values)..

• when i read this data by use read table.. i need to replace first NaN table cells by 0.
• then .. eliminate columns that fully contain 0's values along with header names ..

thanks

Answer 1

[KSSV]

k = rand(10) ;  % some random data 
% put some Nan's
k(randsample(1:numel(k),10)) = NaN ;
% make a column zero
k(:,7) = 0 ;

% replace Nan's with zeros
k(isnan(k)) = 0 ;

k=k(:,any(k)) ;  % remove columns with zeros

[ahmed obaid]

 I got the following errors...  
  T = readtable('D:\data.xls');
  T(isnan(T)) = 0 ;
  T=T(:,any(T));
- Undefined function 'isnan' for input arguments of type 'table'.
- Undefined function 'any' for input arguments of type 'table'.
--------------------------------------------------example: 
  LastName = {'Smith';'Johnson';'Williams';'Jones';'Brown'};
  Age = [38;43;38;40;49];
  Height = [0;0;0;NaN;0];
  Weight = [176;NaN;131;NaN;119];
T = table(Age,Height,Weight,...
    'RowNames',LastName)

[ahmed obaid]

Perhaps result delete full columns tables.. that include zeros (in my example delete hight column and replace NaN with 0's ).

[KSSV]

That case run only:

k(isnan(k)) = 0 ;

[ahmed obaid]

idx = ismissing(T(:,:)); % replace NaN values with zeors for input type table 
 T{:,:}(idx) = 0;

T=T(:,any(T{:,:})) ; % delete columns fully contain zero values

[KSSV]

You don't want that?

Answer 2

[Peter Perkins]

It seems like you're going against the grain here. There are tools to identify and remove missing values, but they mostly work with NaN as the flag for that. It sounds like maybe you have other zeros in the data that you want to preserve? Still, it seems like you're trying to use 0 as the flag for a missing value, and that's going to be more awkward than using the standard thing.

In recent versions of MATLAB, you can use rmmissing to find and remove variables in a table based on various criteria. But it's looking for NaN. You can replace 0's in your table with NaNs, using standardizeMissing, and then use rmmissing.

You could use varfun like this (this assumes all the variables in t are numeric, easy to change if that's not true):

t = varfun(@(x) x(isnan(x)) = 0, t);
allZero = varfun(@(x)all(x==0), t, 'UniformOutput', true);
t = t(:,~allZero);