How can one extract this vector from a matrix?

First, suppose I have a matrix M of 60 rows and 3 columns. Suppose each row is a month like January, February, ... so that every 12 months make a year.

Second, I now want to create a matrix Y of 5 rows and 2 columns where each row represents year and the second column is the yearly average of entries of the second column and the third columns of each year.

How can one write a program?

(Just to make sure, the actual case is not about months and year so it is not that one wants to use retime and other time related function.)

[Totanly]

Please clarify if your data has year information? i mean

year month data

2020 Jan 1 4

2020 Feb 5

. . .etc

[madhan ravi]

More clarification needed. Provide a short example of your data. Maybe it’s much easier than it seems?

[alpedhuez]

input matrix M

1  5 3
2  4  4
3  3  7
4  2  3
5  6   2
6  3   1
7   0  9
8  10  8
9 11  5
10  7   2
11  5  1
12  3 0 

output desired Y

1   4.33333

(average of above 24 numbers in Column 2 and 3)

program that I have been trying

years=size(M,1)/12;
for y=1:1:years
  x=M((years-1)*12+1:years*12,2:3)
  mean(sort(x(:)))
end
   

Answer 1

[Image Analyst]

Try splitapply():

% Get sample data:
M = rand(60, 3);
M(:, 1) = repmat([1:12]', 5, 1) % Make months repeating in the first columns.
% Get average of each column grouped by the month number.
Y = splitapply(@mean, M(:, 2:3), M(:, 1))
% That is the average over months but each average is in it's own column so far.
% Now average the two columns together so that's like 
% averaging columns 2 and 3 of the original matrix.
Y = mean(Y, 2)

[alpedhuez]

M(:, 1) = repmat([1:12]', 5, 1)

So this is the first colum of M to be repeat of 1:12. 5 means repeating 5 times (12*5=60).

Y = splitapply(@mean, M(:, 2:3), M(:, 1))

This is to split M(:, 2:3) into group specified by M(:, 1). Does this mean grouping for month 1, month 2,...?

[Image Analyst]

Yes, because you forgot to attach your actual M, I had to create one with the month in the first column, like you said. I thought I created what you want -- every row is a month, and because there are 60 rows, you have 5 years worth -- 5 times 1-12. If that's not what you have, then please attach your M in a .mat file.

By specifying M(:, 2:3) it will extract the 2nd and 3rd columns as a matrix, and group those according to M(:, 1) which is the first column and which represents the month. So it basically histograms columns 2 and 3 by month (which is the first column). Then you said you wanted the average so I averaged columns 2 and 3. If this is not what you want, then explain more precisely and attach your actual M with the expected results.

Note that splitapply will work for any number of rows - it does not need to be a multiple of 12 rows like Bruno's answer. If you have five years and 3 months, it will still work.

[Bruno Luong]

"it does not need to be a multiple of 12 rows like Bruno's answer"

Apple and orange: My answer gives average by year (as OP request: "second column is the yearly average of entries of the second column and the third columns of each year. ") not by month like yours. They do not achieve the same task.

[Image Analyst]

True. Since he had the month number 1-12 in the first column, and there are supposed to be 60 of them, I assumed there would be 5 years and that the "yearly average" would be like averaging month 1 (January) numbers in rows 1, 13, 25, 27, 39, and 51. In other words, compute the average January values over the 5 years (5 Januaries) in the array. So my code gives 12 averages - one for each month.

Your code averages over all 12 months for each year and so produces 5 averages for the 5 years. Actually looking at his example for only 12 rows where he gives just one average as the output, I think that you might be right.

Waiting for alpedhueze to clarify which way he wants it, and to attach his actual M and desired result.

[alpedhuez]

You are right. The word "yearly average' was confusing. In one interpretation, it is 'an average from January to December ofyear 2019'. In another interpretation, it is 'an average of January over the years.' I meant the first one. But I now realize that the wording allows the second interpretation. Thank you very much.

Answer 2

[Bruno Luong]

Avg = mean(reshape(M(:,2:3)',12*2,[]));
[(1:length(Avg)); Avg]'

[alpedhuez]

reshape(M(:,2:3)',12*2,[]) 

Does this mean that

"take the second and third column of M and put them into 24*x matrix"

?

[Bruno Luong]

Not exactly

"take the second and third column of M, transpose it as 2-row matrix, then put them into 24*x matrix"

[alpedhuez]

True. It has tranpose. But whey does one need to take a transpose?

[Bruno Luong]

Because one need to group columns 2&3 of the same year together so the reshape is correctly group them in 24-chunk..

[Bruno Luong]

I might expand my answer for the need of transpose. MATLAB uses column-major order scheme. If no transpose, in memory the data is stored in this order

12 month year 1 column (2)

12 month year 2 column (2)

...

12 month year 5 column (2)

12 month year 1 column (3)

...

12 month year 5 column (3)

If group the above by chunk of 24 we get

12 month year 1 column (2) + 12 month year 2 column (2)

...

12 month year 5 column (2) + 12 month year 1 column (3)

...

12 month year 4 column (3) + 12 month year 5 column (3)

This is NOT yearly mean of column 2+3 if we take the mean of each chunk.

With the transpose, the data will for be stored

month 1 year 1 column(2)

month 1 year 1 column(3)

month 2 year 1 column(2)

month 2 year 1 column(3)

...

month 12 year 5 column(2)

month 12 year 5 column(3)

This will give the right answer when taking the mean of chunk 24.