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
11 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.
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
Please attach your data with which the mentioned errors appeared and indicate the version of your MATLAB.
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
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
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
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
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
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
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
Categories
Find more on Time Series Objects 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!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 (한국어)