# replace nan values with the mean of other cell array

8 views (last 30 days)
Richard on 29 Jan 2013
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));
end
for i = 1:length(Data);
a(i) = length(d_nan{i});
for ii = 1:a(i);
Data{i}(d_nan{i}(ii),2) = nan;
end
end
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

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) ;
end
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)) ;
end
end
Cheers,
Cedric