How to plot a mean monthly data from a daily time series

Question

I have a series data of daily rainfall from Jan-01-1976 up to Dec-31-2001 for both predicted and observed. And what i want to plot them into a graph of mean monthly from Jan-Feb...Dec. Can you tell me how to do it in Excel or Matlab ? Thank you very much !

Daily data This photo represents daily of predicted and observed

And i want it to be like this:

A sample of mean monthly data

Do you have daily values? Are there two columns, one with date and one with rainfall? Can you provide a sample of the data set? Btw: How is it possible to predict negative precipitation? Seems like the meteorologist have been smoking something... =P — Stewie Griffin
Also, can you post a few lines of your data as an example? Assuming the dates are in the format you mention: In Excel, you can create a new "month" column containing the first three characters from your date (use the left command) and then create a pivot chart with your month column as the "axis" and your rainfall columns as the "values" (summarizing by average). — Jim Quirk
Data such as: drive.google.com/file/d/0By5tEg03EXCpRzN4OUVCMHgtWEE/… — Peter
I didn't see any dates in that file, only predicted and observed values — Jim Quirk
Sorry, I just modified. Please check it again, Thank for your support — Peter

VHarisop VHarisop · Accepted Answer · 2015-01-07T19:31:12

First you will need to find which month each date falls upon. Judging by your linked data, you can do something simple but (i think) slow:

for i = 1:last_index
    % use automatic date overflow of datevec() func
    ldate = datevec(datenum(1976, 1, i))
    month = ldate(2);

From that point you can assign observed and predicted data to months by associating each month with its respective index, i.e. January = 1, February = 2 etc.

After that, you can plot vertical bars for each month with the bar() function.

Edit: ok, here's some more code. I assume your data is in the form predicted observed, and resides in a vector called rainData.

% create matrix for per-month data
monthlyData = zeros(12, 2);
% create matrix for month occurences
months = zeros(12, 1);

for i = 1:length(rainData)
    % use automatic date overflow of datevec() func
    ldate = datevec(datenum(1976, 1, i))
    month = ldate(2);

    % update month occurences
    months(month) = months(month) + 1;

    % update observed sum 
    monthlyData(month, :) = monthData(month, :) + rainData(i, :);
end

% extract means for each month
% dividing the sum of rain values for each month with the month's occurences
for i = 1:12
    monthlyData(i, :) = monthlyData(i, :) / months(i);
end

At that point monthlyData is 12x2 array where each row corresponds to a month and has the form of [predictedValue observedValue]. Use bar(monthlyData) to plot a histogram-like figure.

Also, check out the Matrices and Arrays Tutorial if you have trouble understanding the indexing above.

How to plot a mean monthly data from a daily time series

1 Answers