replace nan values with the mean of other cell array

8 views (last 30 days)
Consider the following example:
Jday = datenum('2007-01-01 00:00','yyyy-mm-dd HH:MM'):1:...
datenum('2009-12-31 23:00','yyyy-mm-dd HH:MM');
Jday2 = datenum('2008-01-01 00:00','yyyy-mm-dd HH:MM'):1:...
datenum('2010-12-31 23:00','yyyy-mm-dd HH:MM');
Data{1} = [Jday;1+(length(Jday)-1).*rand(1,length(Jday))]';
Data{2} = [Jday2;1+(length(Jday)-1).*rand(1,length(Jday))]';
Data{3} = [Jday;1+(length(Jday)-1).*rand(1,length(Jday))]';
for i = 1:length(Data);
d_nan{i} = floor(1+(length(Jday)-1).*rand(1,100));
for i = 1:length(Data);
a(i) = length(d_nan{i});
for ii = 1:a(i);
Data{i}(d_nan{i}(ii),2) = nan;
In this example I have a cell array 'Data' which contains three cell arrays, the first column of each cell array contains the Julian date of the value in the second column. There are numerous nan values in each cell array. I would like to replace the nan values in each cell array with the average of the values in the other cells for that specific julian date. My problem is complicated due to the three cells not showing data at the same times (although Data{1} and Data{3} have the same days.) If the julian date were identical between the three cells this problem could be solved by using isnan to find the location of the nans and then replace those rows by the nanmean of the other rows (in the other cells).
Could anyone suggest an effective way of tackling this problem? Thank you for your time

Accepted Answer

Cedric Wannaz
Cedric Wannaz on 29 Jan 2013
Edited: Cedric Wannaz on 29 Jan 2013
You could build a solution based on the following:
% -- Build a lookup table that associates to each JDay the mean of data
% over all other same JDays.
stacked = vertcat(Data{:}) ;
flag_nan = isnan(stacked(:,2)) ;
buffer = stacked(~flag_nan,:) ;
I = buffer(:,1) ; J = ones(size(buffer,1),1) ; n = max(stacked(:,1)) ;
spmean = sparse(I, J, buffer(:,2), n, 1) .* ...
spfun(@(x) 1./x, sparse(I, J, 1, n, 1)) ;
% -- Update NaN data with relevant means.
for ii = 1:length(Data)
flag_nan = isnan(Data{ii}(:,2)) ;
Data{ii}(flag_nan,2) = spmean(Data{ii}(flag_nan,1), 1) ;
Or, if you can afford looping over NaN entries, you can go for simpler:
stacked = vertcat(Data{:}) ;
isnan_stacked = isnan(stacked(:,2)) ;
for ii = 1:length(Data)
isnan_data = isnan(Data{ii}(:,2)) ;
idx_nan = find(isnan_data) ;
for jj = 1:numel(idx_nan)
isvalid = stacked(:,1) == Data{ii}(idx_nan(jj),1) & ~isnan_stacked ;
Data{ii}(idx_nan(jj),2) = mean(stacked(isvalid,2)) ;

More Answers (0)


Community Treasure Hunt

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

Start Hunting!

Translated by