How to take mean of rows of particular value?

I have an excel file with three columns (Year, Month, Temp). I wanted to find the monthly mean of Temp values from 1997 to 2019 i.e. Jan-97, Feb-97 and so on...

Now, the table (file atteached) has many values for Month 1, Month 2, etc. This is because I have extracted values from different grid points and hence is a little messy. It looks like this:

I used the sortrows function to order the table. However, I do not know how to proceed further. My main aim is to sort the data with respect to Month (Jan-1997, Feb-1997 ... Dec-2019) and then calculate the monthly mean of Temp values from Jan-1997 to Dec-2019. Looking forward to your assistance

[darova]

Everything looks correct. Sortred by rows. What is wrong?

Use mean to calculate mean value

[Keegan Carvalho]

If you sort the data, there are many 1997s (Year) 1s (Months):

So how would I group (take mean) all of the TEMP values for the Year 1997 and Month 1 and similarly Month 2 and so on? I am thinking maybe to loop the columns but not sure how to go about it.

Answer 1

[darova]

Sort your data and use for loop

schematic code (not tested)

s = 0;                  % sum of group data
k = 1;                  % index of group start
for i = 1:n-1
    s = s + a(i);
    if a(i) ~= a(i+1)
        a1(k:i) = s/(i-k+1);    % write mean inside a1
        s = 0;                  % zeros sum
        k = i;                  % new group start
    end
end

[Keegan Carvalho]

Apologies, I haven't quite understood the code. How would I have to get 's'? Also, would 'k' be assigned to index the years or months?

So far I 've got to this code:

data=xlsread('temp.xlsx');
b=sortrows(data);
a=b(:,1);
year=find(a==1997);             %  this is the index which corresponds to k in your code...

Is it possible if you could try the code on the file attached?

[darova]

Can you attach smaller file? Can't upload

[Keegan Carvalho]

I've attached a smaller file by removing some rows. Hope this one works. Thanks in advance!

[darova]

Here is how i understand your question

:Is it correct?

[Keegan Carvalho]

Not exactly. I wanted to group all 1s (Month) of 1997 (Year) and take the mean of Temp. Therefore, I would get the monthly Temp value for January 1997. Similarly, group all 2s (Month) of 1997 and take the mean. This would be monthly Temp value for February 1997 and so on till 2019.

Hope the above explanation helps!

[darova]

Run this

A = xlsread('temp.xlsx');
%%
A1 = sortrows(A);
A1(:,4) = 0;
A1(end+1,:) = 0;
s = 0;                           % sum of group data
k = 1;                           % index of group start
for i = 1:size(A1)-1
    s = s + A1(i,3);             % sum of group (year and month)
    if any( diff(A1(i:i+1,1:2)) )% if year or month is different
        A1(k:i+1,4) = s/(i-k+1); % write mean inside a1
        s = 0;                   % zeros sum
        k = i+1;                 % new group start
    end
end

[Keegan Carvalho]

Thank you. I managed to get the result, but for the sake of my understanding, I wanted to know the following:

So the A1 matrix is the result:

I wanted to know that the 4th column (11.0115) occurs for all 1s (January) of 1997. So do I take the mean of all 11.0115? Or is 11.0115 the monthly mean for January 1997?

[darova]

Yes, 4th column is mean value of the same month and year

[Keegan Carvalho]

Wonderful. Thank you very much darova.

Cheers!

[darova]

cheers!