Mean computation from accumulated row values while ignoring NaN in MATLAB

Question

I'm looking for suggestions on how to solve the following problem elegantly. Although performance isn't an issue in my specific case, I'd appreciate comments regarding good practices.

Thanks in advance!

The short version:

I'm trying to average matrix rows according to some logic, while ignoring NaN values. The code I currently have does not handle NaN values the way I want.

The long version:

My data is built in the following manner:

• A single (first) column of "bins". The amount of rows for every bin is not constant. The bins don't have to be integers. Rows are pre-sorted.
• A variable number of data columns, possibly including NaNs.

Here's an example:

DATA = [...
180     NaN     NaN     1.733
180     NaN     NaN     1.703
200     0.720   2.117   1.738
200     0.706   2.073   1.722
200     0.693   2.025   1.723
200     NaN     NaN     1.729
210     NaN     NaN     1.820
210     NaN     NaN     1.813
210     NaN     NaN     1.805
240     NaN     NaN     1.951
240     NaN     NaN     1.946
240     NaN     NaN     1.946
270     NaN     NaN     2.061
270     NaN     NaN     2.052
300     0.754   2.356   2.103
300     0.758   2.342   2.057
300     NaN     NaN     2.066
300     NaN     NaN     2.066 ];

The desired result is a matrix that contains the unique "bins" in the first column, and means "unspoiled by NaNs" in the rest, e.g.:

• If for a specific column+bin, there are only NaNs (in the above example: 1st data column+bin 210) - the result would be NaN.
• If for a specific column+bin there is a mix of NaNs and numbers, the result would be the mean of the valid numbers. In the above example: 1st data column+bin 200 should give (0.720+0.706+0.693)/3=0.7063 -- note the division by 3 (and not 4) for this column+bin.

Here's the desired result for the above example:

RES = [...
180     NaN     NaN     1.718
200     0.7063  2.072   1.728
210     NaN     NaN     1.812
240     NaN     NaN     1.948
270     NaN     NaN     2.056
300     0.756   2.349   2.074 ];

What I tried so far:

This is some code I managed to compile from several sources. It is working well for column+bin that contain NaNs or numbers only.

nDataCols=size(DATA,2)-1;
[u,m,n] = unique(DATA(:,1));
sz = size(m);
N=accumarray(n,1,sz);

RES(length(u),nDataCols) = 0; %Preallocation

for ind1 = 1:nDataCols
    RES(:,ind1)=accumarray(n,DATA(:,ind1+1),sz)./N;
end

RES= [u,RES];

Here's what I'm currently getting:

RES = [...
180     NaN     NaN     1.718
200     NaN     NaN     1.728
210     NaN     NaN     1.812
240     NaN     NaN     1.948
270     NaN     NaN     2.056
300     NaN     NaN     2.074 ];

p.s.

1. If by any chance this is easier to do using a spreadsheet software (such as MS Excel) - I'd love to hear ideas.
2. Doing the computation on a per-column basis is my current idea on how to handle this. I was just wondering if there's a way to generalize it to take the complete matrix right away.

Answer 1

One possible approach: find changes in first column (exploiting the fact that it's pre-sorted) and apply nanmean to each block of rows:

ind = find(diff([-inf; (DATA(:,1)); inf])~=0); %// value changed: start of block
r = arrayfun(@(n) nanmean(DATA(ind(n):ind(n+1)-1,:)), 1:numel(ind)-1, 'uni', 0);
RES = vertcat(r{:});

You can replace arrayfun by an explicit loop. That may be faster, and avoids the overhead introduced by cells:

ind = find(diff([-inf; (DATA(:,1)); inf])~=0); %// value changed: start of block
RES = zeros(numel(ind)-1, size(DATA,2)); %// preallocate
for n = 1:numel(ind)-1 %// loop over blocks
    RES(n,:) = nanmean(DATA(ind(n):ind(n+1)-1,:));
end

---

Your approach can be used as well. You only need to call accumarray with a handle to the nanmean function. This doesn't require the first column to be pre-sorted.

nDataCols = size(DATA,2)-1;
[u, ~, n] = unique(DATA(:,1));
RES = zeros(length(u), nDataCols); %// Preallocation
for ind1 = 1:nDataCols
    RES(:,ind1) = accumarray(n, DATA(:,ind1+1), [], @nanmean);
end
RES = [u, RES];

Answer 2

Here's another solution, though grossly inefficient. Also, the output array will set all of the NaN values to 0. Let's just say this is good for academic study. Here are the steps that I did:

1. For each ID you have in the first column, find a unique list.
2. For the other columns, split each column up into a cell array.
3. Create a new cell array where each column gets appended with the first column for each element in this cell array
4. Filter out those rows for each cell array that contain a NaN value
5. For each column of the filtered result, run accumarray with mean as a function handle.
6. Using the IDs in Step #1, index each accumarray result and transform back into a matrix

---

%// Step #1
num = unique(DATA(:,1));

%// Step #2
cells = mat2cell(DATA, size(DATA,1), ones(size(DATA,2),1));

%// Step #3
cellsAppend = cellfun(@(x) [DATA(:,1) x], cells(2:end), 'uni', false);

%// Step #4
cellsNonNaN = cellfun(@(x) x(~isnan(x(:,2)),:), cellsAppend , 'uni', false);

%// Step #5
cellsMean = cellfun(@(x) accumarray(x(:,1), x(:,2), [], @mean), cellsNonNaN, 'uni', false);

%// Step #6
selectCells = cellfun(@(x) x(num), append3, 'uni', false);
RES = [num cell2mat(selectCells)];

The result is:

RES = 

180.0000         0         0    1.7180
200.0000    0.7063    2.0717    1.7280
210.0000         0         0    1.8127
240.0000         0         0    1.9477
270.0000         0         0    2.0565
300.0000    0.7560    2.3490    2.0730

---

As you can see, pretty inefficient - especially with the amount of cellfun calls I made, but still an academic example I guess!