How to calculate averaged values from 5-min interval values

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.

Answer 1

[David K.]

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*

[Daphne PARLIARI]

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.]

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]

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...

[Andrei Bobrov]

Please see my answer.

[Daphne PARLIARI]

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]

Please attach your data with which the mentioned errors appeared and indicate the version of your MATLAB.

[Daphne PARLIARI]

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."

[Andrei Bobrov]

Hi Daphne! I am fix my code. Please try him.

[Daphne PARLIARI]

I tried and :

Error using regexp

Invalid option for regexp: once

[Andrei Bobrov]

version of your MATLAB ?

[Daphne PARLIARI]

It's R2016a

[David K.]

Changed my answer to be more broad.

[Andrei Bobrov]

Daphne! I am fix my answer for R2016a.

[Daphne PARLIARI]

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.]

Ah right, it looks like if you just set size2 to 24 it should work since that one does not need to change.

[Daphne PARLIARI]

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.]

I believe so, the first for loop is taking the mean of every hour in the data set. AKA, sub1 is the length of the total number of hours in the data set and each is the mean temperature of those hours. Then, since there are 24 hours in a day, the second loop is taking the mean of the first hour in every day, then the second, then onwards to the 24th hour across every day.

[Daphne PARLIARI]

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?

[David K.]

Ah I figured it out, when calculating hourly vals the mean should be of the values in sub1 not x. There are still some weird looking values but I think that might have to do with some outliers that say the temperature/ humidity was like -7000.

[Daphne PARLIARI]

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.]

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]

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.]

Since it was a big group of values that needed to be replaced I looked at the 2 good values around them and just did an interpolation so x=0 and x=10 were the known values and it just went between them.

[Daphne PARLIARI]

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.]

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]

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.]

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]

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.

Answer 2

[Andrei Bobrov]

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');

[Daphne PARLIARI]

Thank you!!

I run it and this message appears

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

[Andrei Bobrov]

Yes! MATLAB modified of names variables in your table.

[Daphne PARLIARI]

Your help was crucial! Thank you a million times!

[Daphne PARLIARI]

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?