**You are now following this question**

- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.

# How to calculate averaged values from 5-min interval values

38 views (last 30 days)

Show older comments

Hello guys.

I have the following question : I have observations for temperature in 5-minute interval, from 1/1/2015 to 31/1/2015 (8928 rows in total). From these data I must extract the hourly average value for temperature, which means that eventually I must have 24 hourly averaged values.

I would appreciate any ideas on the matter!

PS. I have attached the excel file I'm working on. Please note that the format of time appears diferently on Matlab.

##### 0 Comments

### Accepted Answer

David K.
on 19 Sep 2019

Edited: David K.
on 25 Sep 2019

Using for loops is a very straightforward way that this can be done:

x = yourData; %your data

size1 = length(x)/12;

size2 = 24;

sub1 = ones(size1,1); % intermediate matrix for averaging every hour

for n = 1:size1 % 8928/12 = 744

sub1(n) = mean(x(12*(n-1)+1 : (12*n))); % averages every 12 values to average every hour

end

hourlyVals = ones(size2 , 1); % 744/31 = 24

for n = 1:size2

hourlyVals(n) = mean(sub1(n:24:size1)); % calculate average of each hour across every day.

end

*edited to fix issue brought up in comments*

##### 28 Comments

Daphne PARLIARI
on 20 Sep 2019

Thank you so much for your help!

I think it runs until

sub1(n) = mean(x(12*(n-1)+1 : x(12*n))).

It prints the error "Warning: Integer operands are required for colon operator when used as index". I tried FIX, ROUND, CEIL for the problematic n but still the same warning...

David K.
on 20 Sep 2019

Ah yes that is because I made a typo. It should actually be

sub1(n) = mean(x(12*(n-1)+1 : (12*n)));

The x in front of (12*n) was wrong. Fixed in the answer:

Daphne PARLIARI
on 23 Sep 2019

Great, thank you!!

I have to expand my time-series to include all 12 months of 2015. How can I avoid making a mistake with index n, in order for it to remain integer all the time? I could calculate every month individually and then combine the results but I guess there must be a more efficient way...

Daphne PARLIARI
on 23 Sep 2019

Done that, but:

Warning: Usage of DATEVEC with empty date strings is not supported.

Results may change in future versions.

In datevec (line 89)

Error using duration (line 223)

Numeric input data must be a matrix with three columns, or else three or four separate numeric arrays. You can also create durations from a single

numeric array using the YEARS, DAYS, HOURS, MINUTES, or SECONDS functions.

Andrei Bobrov
on 23 Sep 2019

Edited: Andrei Bobrov
on 23 Sep 2019

Daphne PARLIARI
on 23 Sep 2019

These are tha data only for January (I have 11 similar xlsx files for the entire 2015).

I copied your code and the message is "Warning: Usage of DATEVEC with empty date strings is not supported.

Results may change in future versions.

> In datevec (line 89)

Error using duration (line 223)

Numeric input data must be a matrix with three columns, or else three or four separate numeric arrays. You can also create durations from a single numeric

array using the YEARS, DAYS, HOURS, MINUTES, or SECONDS functions."

Daphne PARLIARI
on 24 Sep 2019

Hi David, thank you for your assistance.

I tried your code but an error appeared : "Error using ones. Size inputs must be integers"

Yet, size1 appears to be of integer size but not size2! And it makes sense because size2=size1/31, but not all months have 31 days. Some have 30 and one has 28.

David K.
on 24 Sep 2019

Daphne PARLIARI
on 24 Sep 2019

It worked indeed but I am not sure about the validity of the graph I produce, which should be diurnal varation of temperature.

size2 = 24 does not endanger the hourly means, right?

David K.
on 24 Sep 2019

Daphne PARLIARI
on 25 Sep 2019

Dear David I am most grateful for your help.

I attach the code as I have run it, plus the respective excel file. Although the hourly values for RH (relative humidity) seem reasonable, the hourly values for T (temperature) are completely wrong. When I plot diurnal T both the shape of the curve and the 24 values themselves, are all over the place.

What could have possibly gone wrong?

Daphne PARLIARI
on 26 Sep 2019

Edited: Daphne PARLIARI
on 26 Sep 2019

Dear David, thank you so much.

How can I alter the code in order to exclude values =-6999, which are obviously wrong? (and not mess with size1 = length(T)/12 which must be integer at all times...)

David K.
on 26 Sep 2019

To test if it made a difference I used find(T<-100) to find the indices of all the bad values then just manually looked at the values around them and set them to an in between value. The values I did were

RH(10457) = 82.5;

T(10547) = 13.7;

T(39547:39555) = .067*(1:9)+18.37; % a lot of bad values together so I used a line

Basically just assuming that the missing values could be linearly interpolated from the nearest complete values

Daphne PARLIARI
on 27 Sep 2019

Worked like a charm! Thank you so much!

Could I ask, how did you decide to choose this line

.067*(1:9)+18.37

and not a different one?

David K.
on 27 Sep 2019

Daphne PARLIARI
on 27 Sep 2019

Completely clear, thank you!

I tried to alter the code to read the attached xlsx (which contains hourly data from 1/1/2015 ot 16/5/2015). But it seems that hourlyValsT are too low, I must have made a mistake somewhere...

format short g

B=xlsread(filename);

columnD = xlsread(filename,'D:D');

columnE = xlsread(filename,'E:E');

T = columnD;

RH=columnE;

size2 = 24;

size1 = length(T)/12;

hourlyValsT = ones(size2 , 1);

for n = 1:size2

hourlyValsT(n) = mean(T(n:24:size1)); % calculate average of each hour across every day.

end

David K.
on 30 Sep 2019

The issue is in

mean(T(n:24:size1));

you want to take the mean of every hour in the entire data set. But size1 is 1/12 of the size of total size. So, if you change it to

mean(T(n:24:length(T)));

or

size1 = length(T);

it should work fine

Daphne PARLIARI
on 1 Oct 2019

Edited: Daphne PARLIARI
on 1 Oct 2019

Thank you, I tried the correction you proposed but the hourly values of temperature display a completely reverse behavior than normal. I attach plotTcorr.tif: maximum T is during night which is completely irrational.

Before the correction of the code, shape of diurnal T was normal but the values were too low. Now the values seem more normal but the shape of the curve is reversed!

Plus if I use:

size2 = 24;

size3=length(RH);

hourlyValsRH = ones(size2 , 1);

for n = 1:size2

hourlyValsRH(n) = mean(RH(n:24:size3)); % calculate average of each hour across every day.

end

the hourlyValsRH table is missing the value at 13.00...

David K.
on 1 Oct 2019

So I looked at the excel table itself to calculate the mean and it had the same flipped situation. Since it appears that if we only look at the first few days it is as expected then I think it is possible there is something wrong with the data. Your data stops at the 11th hour instead of the full 24 for the last day. perhaps at some point the data got off. I do not know how you would figure it out but I think that is up to you with your data.

To fix the RH I would do the same thing as last time where you just find the places it is missing and fix it manually.

Daphne PARLIARI
on 2 Oct 2019

If I delete the last 12 values in order to have all the days with 24 hourly values, then the shape of the diurnal variation of T is correct but the values are too low.

If I add again the last 12 values, the curve loses its shape but the hourly values are correct. It seems that I can't have it both ways.

### More Answers (1)

Andrei Bobrov
on 1 Oct 2019

Edited: Andrei Bobrov
on 1 Oct 2019

T = readtable('parko_hourly_1_1_2015_eos_16_5_2015_T_RH MINE.xlsx','sheet','Φύλλο1');

T = T(~isnan(T.minute),[1:5,7:8]);

T.YEAR = str2double(T.YEAR);

T{:,{'AIR_TEMP_DegC','RH'}} = str2double(T{:,6:7});

T = T(:,[1:5,8:9]);

T_hour = varfun(@(x)mean(x,'omitnan'),T,'InputVariables',{'AIR_TEMP_DegC','RH'},'GroupingVariables','HOUR');

##### 4 Comments

Daphne PARLIARI
on 1 Oct 2019

Thank you!!

I run it and this message appears

"Warning: Variable names were modified to make them valid MATLAB identifiers. "

Daphne PARLIARI
on 31 Oct 2019

Dear Andrei, I would like to ask a further question on your code which has worked dreamily.

Is there a way to create an intermediate matrix (before extracting the 24 hourly values of T) which will contain one hourly value of temperature for every hour of each day? With this, I need to have a temperature at 00.00 on 1/1/2015, temperature at 01.00 on 1/1/2015 and so on until 23.00 on 31/5/2015.

Is this applicable?

### See Also

### Community Treasure Hunt

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

Start Hunting!**An Error Occurred**

Unable to complete the action because of changes made to the page. Reload the page to see its updated state.

Select a Web Site

Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .

You can also select a web site from the following list:

## How to Get Best Site Performance

Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.

### Americas

- América Latina (Español)
- Canada (English)
- United States (English)

### Europe

- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)

- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)

### Asia Pacific

- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)