replace nan values with the mean of other cell array

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

Answer 1

[Cedric]

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