How to get monthly average from daily data?

So i have this Matrix, consisting of 4 columns: Column 1: Year Column 2: Month Column 3: day (1 to 30) Column 4: Some Value

I want to make monthly average of "some value" without breaking the matrix into some new matrix and calculate mean for each moths matrix seperately then rejoin them. I want to make an average matrix using for loop and find function for each myear seperately. So I dont like to have an average from all the first months of all the years together. Does anyone have an Idea?

I have sth like this, but i donno why it is not working:

k=1
  %Calculating Monthly Average
  for i=1961:1990                                     %Years Looping
      for j=1:12                                      %Months Looping
          idx=find((date(:,1)==i) & (date(:,2)==j));  %Find all indeces for the specific month
          meandata1=mean(date(idx,4),1);
          Monthly_Ave(k,:)=[i,j,meandata1;];          %Store in a new matrix the mean values per month
          k=k+1;
      end
  end

[jonas]

When you say monthly average, do you mean over several years or for each single year separately?

Do you want 12 values or 12 times the number of years?

[frankovaT]

yeah you are right, i ll modify the question

[jonas]

If I interpret your addendum correctly, you want 12 values per year. I have modified the answer accordingly, however I'd strongly recommend using a timetable and retime. If you upload some data I'd be happy to demonstrate.

[frankovaT]

Dear jonas I truly appreciate your help i attached an Excel file of my data.

[jonas]

and answer updated as promised!

Answer 1

[jonas]

Not sure what you mean by "without breaking the matrix into some new matrix", but here we go:

x is column with months,

y is data

[G,name] = findgroups(x)
out = splitapply(@mean,y,G)

This will give you max 12 values, one for each month of the year. If you have several years, and you want 12 values per year, then you can do the following trick.

x1 is months (double)

x2 is years (double)

x=cellstr(num2str([x1, x2]))
[G,name] = findgroups(x)
out = splitapply(@mean,y,G)

Better yet, put your data in a timetable and use retime to calculate the monthly average. Here's an example using your data:

data=xlsread('data.xlsx')
t=datetime(data(:,1:3))
T=timetable(t,data(:,4))
T2=retime(T,'monthly','mean')

[frankovaT]

   Hey
I added my time series and my code

[frankovaT]

wow i truly appreciate, while i am trying to understand your code would you do me favor and tell me what s wrong with my own code?

[jonas]

Note that there was a small error when I first posted the solution with timetable. It's been fixed now.

[frankovaT]

thanks again for working on this. actually i thought that what i was making with my own code is quite the right thing:)))) and now i donnno where in my code it is wrong. i am trying to work on your code

[jonas]

There's a typo here:

 Monthly_Ave(k,:)=[i,j,meandate1;];

should be meandata1. Also date is a built in function, so don't name your variable that! Both methods give the same results, so that's reassuring :)

[frankovaT]

Oh god! you are an angle! I was so unsure about my code that i didnt even tried to look for typos:))) My first for loop!!! thanks :)

Answer 2

[KSSV]

Let M be your month column, extracted and A be your data.

B = zeros(12,1) ;   % means 
for i = 1:12
    B(i) = mean(A(M==i)) ;
end

[frankovaT]

maybe I should have mentioned that i have 50 years of data, so if I ll have like 50, Marches, and this is going to make an average from all the Marches for instance. but i want to make an average form each daily vaule in month of a year seperately.