# how to convert hourly average into monthly??

13 views (last 30 days)
SD on 23 Mar 2018
i have data for hourly. i need the monthly average of the each hour of the day.
eg: date time data
01/03/18 1:00:00 2.365
02/03/18 1:00:00 3.235
03/03/18 1:00:00 10.2655
..............
..............
29/03/18 1:00:00 45.6552
30/03/18 1:00:00 32.2552
31/03/18 1:00:00 15.3565
in this way i need all 24 hours average for a month.
this kind of data is for 4 years.
is there any possible program related to this problem,
if anyone can solve thanks in advance.
this is the data available with me.
but as i mentioned above i need the average of the each hour of the month.
i.e, 01/01/2012 1:00:00+02/01/2012 1:00:00+......31/01/2012 1:00:00.
like this 24 hours for a month and 12 months for a year.
i want to do mean of the first hour values of a month,then second hour values for amonth and so on last hour i.e,23 rd hour mean for a month. i hope you understood my problem,if not i will explain you clearly one more time.

Pawel Jastrzebski on 23 Mar 2018
You mentioned that you have an 'hourly average' of your data and want to turn them into 'monthly average'.
But in the snippet of data you've provided I can only see 'daily values'.
In order to be able to offer an adequate help, could you provide the actual data you'll be dealing with or at least a portion of it but in the original format?
SD on 23 Mar 2018
this is the data available with me.but as i mentioned above i need the average of the each hour of the month.i.e, 01/01/2012 1:00:00+02/01/2012 1:00:00+......31/01/2012 1:00:00.like this 24 hours for a month and 12 months for a year.
i want to do mean of the first hour values of a month,then second hour values for amonth and so on last hour i.e,23 rd hour mean for a month. i hope you understood my problem,if not i will explain you clearly one more time.
dpb on 23 Mar 2018
Attach the data itself (representative sample) instead of image; we can't do anything with that.
See <splitapply> for how to group data, though, should get you going.

Pawel Jastrzebski on 23 Mar 2018
Use the logical vectors for conditioning your data as in the example below. You'll need some loops to go over every hour:
% example data
% date generation
t1 = datetime(2018,01,01,0,0,0);
t2 = datetime(2018,03,31,0,0,0);
t = (t1:hours(1):t2)';
% hourly value generation
hourlyVal = randi(10,length(t),1);
% store all in a table
T = table(t,hourlyVal)
%---CALCULATE---
% average for hour 00:00 in January 2018
% CONDITION
condition = ...
year(T.t) == 2018 & month(T.t) == 1 & hour(T.t) == 0;
% VALUE
aveJan18_00 = mean(T.hourlyVal(condition))

SD on 27 Mar 2018
yes,sir.definetely.upto what you have suggested i got the result.but not the random numbers i need.so, can you suggest me what to replaced in the place of randi for accessing of the whole data without changing the values.
Pawel Jastrzebski on 27 Mar 2018
• How do you store your data in MATALB? Is it in a table?
If so you will access your data using the actual variables:
aveJan18_00 = mean(...
TableName.VariableName(condition))
If you imported the table and no name was assigned to its variables (columns), it will hold a generic names for the variables:
Then your code would look more like this:
aveJan18_00 = mean(...
TableName.Var3(condition))
Or alternatively, use column indexing to access i.e. column no 3. More on that:
---
The example I have provided earlier was a quick fix - but the example was to show you how you can use vectors to extract the data from you set and calculate the average. Naturally, it requires some work on your side to understand the example and improve the code so it is more efficient. I'm not claiming this is the most efficient way, but I disagree with dpd 's statement that this is not the Matlab way.
To expand on my example, I've created a table of conditions, to show you that you:
• Can collect all of your conditions in one place
• Use only one loop to carry out the calculation
• Don't need to create a new variable for every average you want to calculate
% EXAMPLE DATA
% date generation
t1 = datetime(2017,010,01,0,0,0);
t2 = datetime(2018,03,31,0,0,0);
t = (t1:hours(1):t2)';
% random hourly value generation
hourlyVal = randi(10,length(t),1);
% store all in a table
T = table(t,hourlyVal)
clear t1 t2 t hourlyVal;
% CREATE CONDITIONS TO CHECK
% Create a list of of all of the conditions for you to check
% And store them in a table for convenience
y = (2017:2018)'; % year span
m = (1:12)';
h = (0:23)';
h1 = repmat(h,numel(m)*numel(y),1);
m1 = repelem(m,numel(h));
m1 = repmat(m1,numel(y),1);
y1 = repelem(y,numel(m)*numel(h));
% create table of the 'date' condtions to check
T_cond = table(y1, m1, h1);
% add column for the average that will be calculated from the data
T_cond.Ave(:) = 0;
clear y m h y1 h1 m1;
% CALCULATE AND STORE THE REQUIRED AVERAGE
% One loop to go over evry condition from 'T_cond'
% And if the condition is met, evaluate the avetage for the DATA
for i = 1:size(T_cond,1)
condition = ...
year(T.t) == T_cond.y1(i) & ...
month(T.t) == T_cond.m1(i) & ...
hour(T.t) == T_cond.h1(i);
T_cond.Ave(i) = mean(T.hourlyVal(condition));
end
In this example you'll see that:
• Your fake date span ranges from 10.2017 to 03.2018
• Conditions to calculate the average for are created easily with this code:
y = (2017:2018)'; % year span
m = (1:12)';
h = (0:23)';
h1 = repmat(h,numel(m)*numel(y),1);
m1 = repelem(m,numel(h));
m1 = repmat(m1,numel(y),1);
y1 = repelem(y,numel(m)*numel(h));
% create table of the 'date' condtions to check
T_cond = table(y1, m1, h1);
• Lastly, code calculates the average where data is available and conditions are met:
Last thoughts:
• Yes, Matlab philosophy is not to use loops if you don't have to
• But at the same time it's not a crime to use them, use whatever method and programming skills you're comfortable with
• There's always room for improvement
SD on 28 Mar 2018
that's a great help. it works.

Steven Lord on 23 Mar 2018
If you're using release R2016b or later, store your data in a timetable and call retime on that timetable.

Razvan Carbunescu on 1 Apr 2018
If using R2018a and the data lives in a table T with variables 'Time' and 'Data' can accomplish this by using the new groupsummary command:
>> groupsummary(T,{'Time', 'Time'},{'monthname','hourofday'},'mean','Data'}
The above will group both Januarys of 2012 and 2013 together. If you want them separate would use 'month' instead of 'monthofyear'

### Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by