Calculate average for each hour in day
Show older comments
I have a bunch of data in datetime format similar to this:
2010-Nov-30 18:00.00 0 8.7 17.05 61.38
I have summed all the data into hours, but I would like to calculate the average at every hour 00:00, 01:00, 02:00... 23:00 for every day in the year.
I have data for a whole year, so I'm interested in seeing the average values in a day, per hour, with the data from the whole year.
I've tried to use retime but I can't really get it to function correctly. Maybe it's the wrong function for this purpose.
3 Comments
Adam Danz
on 20 Jun 2020
retime is definitely the way to go. Why didn't it work for you?
Oliver Zacho
on 20 Jun 2020
Adam Danz
on 20 Jun 2020
Could you show us what you tried and describe the input variables used? It will be easier to help you tweek that instead of creating a different example out of our imaginations that may not even be suitable with your data.
Accepted Answer
dpb
on 20 Jun 2020
1 vote
IIUC, retime is not the tool for this; it bins data into desired time segments such as hourly but averages within those bins.
To compute an average of all observations in the dataset at each exact hour, use a grouping variable and varfun or groupsummary or grpsummary (latter requires Statistics Toolbox and has essentially been replaced by former in base MATLAB).
You will have to group by sufficiently-fine resolution to isolate the one actual top-oif-the-hour reading by hours(), minutes(), seconds(), ... depending upon the time-of-day readings in the dataset.
6 Comments
I may have misinterpretted the question.
If the goal is to compute the hourly avg across some time frame, let's say a year, so that there are 8760 mean values, one for each hour of the year, then you can use
% Create timetable
TT = timetable(datetime(2019,6,20)+minutes(linspace(0,525600,50000)'), rand(50000,1), 'VariableNames',{'Data'});
% Hourly avg
TT_hourly = retime(TT,'hourly', 'mean');
If the goal is to compute the average by hour-of-day combining all days, so that you have 24 mean values across the entire year,
% Create timetable
TT = timetable(datetime(2019,6,20)+minutes(linspace(0,525600,50000)'), rand(50000,1), 'VariableNames',{'Data'});
% Identify the hour of each datetime value. This shifts all datetime
% values to the *start* of the hour.
hourNumber = hour(dateshift(TT.Time,'Start','Hour'));
% If you have the Stats & ML toolbox,
hourlyMeans = grpstats(TT.Data,hourNumber);
T = table(unique(hourNumber), hourlyMeans, 'VariableNames', {'Hour', 'Mean_Data'})
% Same thing without S&ML toolbox
[hourIdx, hourGroups] = findgroups(hourNumber);
hourlyMeans = splitapply(@mean, TT.Data, hourIdx);
T = table(hourGroups, hourlyMeans, 'VariableNames', {'Hour', 'Mean_Data'})
% Result
% T =
% 24×2 table
% Hour Mean_Data
% ____ _________
% 0 0.49151
% 1 0.49628
% 2 0.49473
% 3 0.49118
% 4 0.51048
% 5 0.49791
% 6 0.49877
% 7 0.501
% 8 0.50227
% 9 0.50334
% 10 0.49643
% 11 0.50641
% 12 0.49736
% 13 0.50344
% 14 0.50906
% 15 0.50823
% 16 0.50117
% 17 0.49397
% 18 0.509
% 19 0.49521
% 20 0.49733
% 21 0.5005
% 22 0.49892
% 23 0.49418
Oliver Zacho
on 20 Jun 2020
That's 'cuz you grouped on the Time vector itself -- which is unique for each element -- NOT unique for each hour for the collection of days. With a datetime variable, even if the display format shows only the time, the date portion is still associated with the value itself, it's only the output format that was changed.
You can see this two ways -- first, indirectly by the observation that there's only a single value in each group returned from varfun; secondly, directly by setting the .Format property for the date back to 'default' which will show the full datetime value, not just the output format form requested.
dpb
on 20 Jun 2020
Since you have hourly observations, the above solution by AD will work -- I didn't check for sure, but I'm pretty sure if you had observations every 15 minutes, say, the use of hour(dateshift()) would have the effect of averaging all four of the observations at each hour (0, 15, 30, 45 min), not just the 0 minutes at top of the hour observation.
That might also be the desired way to treat the data, too, ... just noting a possible behavior if you were to have another set of data besides the specific shown.
Oliver Zacho
on 20 Jun 2020
dpb
on 20 Jun 2020
The better solution is probably to just use hour() of the real datetime -- that will return a double from 0-23 for each.
Since you have only the one hourly observation, you won't need the retime step.
More Answers (1)
Catriona Fyffe
on 22 Jul 2020
A very simple way to solve this if you have whole days starting at 00:00 is to use reshape. Its a bit old school but I like it! However I have come onto this page to solve this problem for data which doesn't start at 00:00 (not my choice!)
NHours=size(VarA,1);
NDays=NHours/24;
VarA_AH=nanmean(reshape(VarA,[24,NDays]),2);
Categories
Find more on Data Type Identification in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
