Calculate daily, monthly, annual averages from hourly data

I have hourly data, I want to be able to create daily, monthly and annual averages while taking into account the bisexile years.

These are hourly data from a netcdf file. So I have a temperature variable and a time variable

Data are available from January 1, 2003 to June 30, 2017

For temperature and time variables

    Format:
           classic
Dimensions:
           time = 127080
Variables:
    tas_sirta
           Size:       127080x1
           Dimensions: time
           Datatype:   single
           Attributes:
                       units         = '°C'
                       long_name     = 'air_temperature'
                       standard_name = 'air_temperature'
                       name          = 'tas'
                       dim           = 'time'

    Format:
           classic
Dimensions:
           time = 127080
Variables:
    time
           Size:       127080x1
           Dimensions: time
           Datatype:   single
           Attributes:
                       long_name     = 'date_time'
                       units         = 'hours since 1970-01-01'
                       actual_range  = '289272.0, 416351.0'
                       standard_name = 'time'
                       name          = 'time'
                       calendar      = 'standard'
                       dim           = 'time'
                       missing_value = NaN

I transform dates with datenum

timee = double(time)/24 + datenum('1970-01-01 00:00:00');
datestr(timee(1:7,1),'dd-mm-yyyy HH:MM:SS')

J'obtiens bien

    ans =
01-01-2003 00:00:00
01-01-2003 01:00:00
01-01-2003 02:00:00
01-01-2003 03:00:00
01-01-2003 04:00:00
01-01-2003 05:00:00
01-01-2003 06:00:00

but I do not know then to make the daily, monthly and annual averages taking into account all the specificities of a year (February with 28-29 days, month with 30, 31 days ...)

thank you

Answer 1

[Guillaume]

The simplest way to do what you want is to convert your data into a timetable:

hourlytemp = timetable(hours(time) + datetime(1970, 1, 1), tas_sirta, 'VariableNames', {'air_temperature'});

Then retime that timetable to monthly, daily, yearly or whatever period you want:

monthlyaverage = retime(hourlytemp, 'monthly', 'mean')

Done!

[Justine Ringard]

Why I have Undefined function 'datetime' and 'hours' ? Should not these be functions ? I have R2013b version. Another way if my version is too old

[per isakson]

retime was introduced in R2016b

[Guillaume]

datetime, and hours were introduced in R2014b

timetable and retime were introduced in R2016b

Another way to do this would be to use discretize which requires R2015a.

Really, that tells you it is time to upgrade!

In R2013b, the way to do it would involve converting to datevec and using histc or unique to discretise by whichever interval, then some accumarray magic. I'll have a go later if nobody beats me to it.

[Guillaume]

Ok, here is a version that should work for R2013b. No guarantee though:

timevector = datevec(double(time) / 24 + datenum('1970-01-01 00:00:00'));
[yearmonth, ~, id] = unique(timevector(:, [1 2]), 'rows');
monthlyaverage = accumarray(id, tas_sirta, [], @mean);
%each row of monthlyaverage corresponds to the same row of yearmonth

To get daily average:

[yearmonthday, ~, id] = unique(timevector(:, [1 2 3]), 'rows');
dailyaverage = accumarray(id, tas_sirta, [], @mean);
%each row of dailyaverage corresponds to the same row of yearmonthday

To get yearly, you only need to look at the first column of the date vector:

[year, ~, id] = unique(timevector(:, 1));  %don't need the row option as there's only one column passed to unique
yearlyaverage = accumarray(id, tas_sirta, [], @mean);
%each row of yearlyaverage corresponds to the same row of year

[Justine Ringard]

Thank you very much, this code gives exactly what I am trying to do.

[Abul Hasan Fahad]

Hello Guillaume, retime shows error for datatype "double". How do you solve this issue?

[Guillaume]

If you get an error you need to give us the full text of the error message, everything in red. Otherwise, we cannot guess.

Note that the first input of retime is a timetable

[Polina S]

Thank you, Guillaume! I have an extra question..

I have a range of three-year data measured twice a day. I need to get mean monthly temperature averaged over all given years. I've used retime to get monthly average. But how can I average them over the years?

t = datetime (year, month, day, hour, minute, second);
hourlytemp = timetable(t, TempSurf);
monthlyaverage = retime (hourlytemp, 'monthly', 'mean')

monthlyaverage has a dimension 96x1 now. I need to get 12x1

[Guillaume]

retime can't do this but groupsummary can:

monthofyearaverage = groupsummary(hourlytemp, 'Time', 'monthofyear', 'mean')

groupsummary is probably more appropriate for the original question as well, as it lets you calculate multiple statistics at once. However, groupsummary did not exist when the question was asked. It requires R2018a or later.