How to extract new matrix from existing one

I have a large number of entries arranged in three columns. Sample of the data 开发者_如何学Pythonis:

A=[1 3 2 3 5 4 1 5 ; 
   22 25 27 20 22 21 23 27; 
   17 15 15 17 12 19 11 18]'

I want the first column (hours) to control the entire matrix to create new matrix as follows:

Anew=[1 2 3 4 5 ; 22.5 27 22.5 21 24.5; 14 15 16 19 15]'

Where the 2nd column of Anew is the average value of each corresponding hour for example:

from matrix A: at hour 1, we have 2 values in 2nd column correspond to hour 1 which are 22 and 23 so the average is 22.5

Also the 3rd column: at hour 1 we have 17 and 11 and the average is 14 and this continues to the hour 5 I am using Matlab

You can use ACCUMARRAY for this:

Anew = [unique(A(:,1)),...
        cell2mat(accumarray(A(:,1),1:size(A,1),[],@(x){mean(A(x,2:3),2)}))]

This uses the first column A(:,1) as indices (x) to pick the values in columns 2 and 3 for averaging (mean(A(x,2:3),1)). The curly brackets and the call to cell2mat allow you to work on both columns at once. Otherwise, you could do each column individually, like this

Anew = [unique(A(:,1)), ...
        accumarray(A(:,1),A(:,2),[],@mean), ...
        accumarray(A(:,1),A(:,3),[],@mean)]

which may actually be a bit more readable.

EDIT

The above assumes that there's no missing entry for any of the hours. It will result in an error otherwise. Thus, a more robust way to calculate Anew is to allow for missing values. For easy identification of the missing values, we use the fillval input argument to accumarray and set it to NaN.

Anew = [(1:max(A(:,1)))', ...
        accumarray(A(:,1),A(:,2),[],@mean,NaN), ...
        accumarray(A(:,1),A(:,3),[],@mean,NaN)]

You can use consolidator to do the work for you.

[Afinal(:,1),Afinal(:,2:3)] = consolidator(A(:,1),A(:,2:3),@mean);

Afinal
Afinal =
            1         22.5           14
            2           27           15
            3         22.5           16
            4           21           19
            5         24.5           15