How to sort array matrix, finding means of output for matching months and reading stations?

Hi forum,

first post here - hope you guys can help me out:

I have a 4515 x 3 array sorted like this:

In the first column are the IDs for 8 different stations, already sorted (9002, 9002, .... , 9002, 9004, 9004 .... , 9004, 9011, ... and so forth, in the second column are the months of measuring (1,2,3,4,5,6,7,8,9,10,11,12,1,2,3,4 etc) and in the third column are all the individual readings for each station for each month. The data set is for several years, so there will be several loops of all 12 months for each station.

What I need to do is compute the mean of all the readings from the same month for the same station, so I can get a matrix like this:

          Jan Feb Mar ...
9002   X
9004
...

so with X being the mean of all the readings from all the Januaries for station 9002.

I'm relatively new to MATLAB, and I don't really have any idea of how to approach this problem... Hope you can help me!

Cheers, Simon

Answer 1

[Oleg Komarov]

You can use my Pivot/unPivot which I wrote specifically for this task:

Pivot(A(:,[2 1 3]),@mean)

Note that the first column becomes the column header and the second column the row header. Since you want the months on top I reshaped your A.

[Simon Funder]

Hi Oleg,

thanks for this very nice function! Although I'm getting all NaN values in my outputs:

Can I not have NaN values in my 'input' matrix A?

ans =

Columns 1 through 7

NaN 1 2 3 4 5 6

9002 NaN NaN NaN NaN NaN NaN

9004 NaN NaN NaN NaN NaN NaN

9005 NaN NaN NaN NaN NaN NaN

9011 NaN NaN NaN NaN NaN NaN

9012 NaN NaN NaN NaN NaN NaN

9017 NaN NaN NaN NaN NaN NaN

9025 NaN NaN NaN NaN NaN NaN

9027 NaN NaN NaN NaN NaN NaN

9029 NaN NaN NaN NaN NaN NaN

Columns 8 through 13

7 8 9 10 11 12

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

NaN NaN NaN NaN NaN NaN

[Oleg Komarov]

I suspect you have NaNs in you data which propagates.

If you have the stats toolbox use @nanmean.

Answer 2

[Andrei Bobrov]

A = sortrows(data);
[a1,~,n] = unique(A(:,1));
out = [0:12;a1 accumarray([n A(:,2)],A(:,3),[n(end) 12],@mean)];

Answer 3

[Simon Funder]

Thank you both!

I just had to use nanmean, as my array contained NaN values.

[Oleg Komarov]

Ah sorry commented too late but happy you found the solution to the NaNs.