how to convert hourly average into monthly??

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]

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]

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]

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.

Answer 1

[Pawel Jastrzebski]

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]

sir,it works.but it is taking random values between 10. how to solve this. please help

[dpb]

See either Steven Lord's alternate or my suggestion...don't need loops and this is the most difficult way to go about it in that you need a new variable for every average, not "the Matlab way"...

[Pawel Jastrzebski]

SD you have an actual data which I didn't have - you need to access the data and calculate the mean for it.

[SD]

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]

• 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:

• Access Data in a Table

---

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]

that's a great help. it works.

Answer 2

[Steven Lord]

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

Answer 3

[Razvan Carbunescu]

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'