How to calculate averaged values from 5-min interval values
18 次查看(过去 30 天)
显示 更早的评论
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.
采纳的回答
David K.
2019-9-19
编辑:David K.
2019-9-25
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 个评论
Daphne PARLIARI
2019-9-20
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.
2019-9-20
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
2019-9-23
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
2019-9-23
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
2019-9-23
编辑:Andrei Bobrov
2019-9-23
Please attach your data with which the mentioned errors appeared and indicate the version of your MATLAB.
Daphne PARLIARI
2019-9-23
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
2019-9-24
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.
2019-9-24
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
2019-9-24
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.
2019-9-24
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
2019-9-25
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
2019-9-26
编辑:Daphne PARLIARI
2019-9-26
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.
2019-9-26
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
2019-9-27
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.
2019-9-27
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
2019-9-27
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.
2019-9-30
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
2019-10-1
编辑:Daphne PARLIARI
2019-10-1
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.
2019-10-1
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
2019-10-2
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.
更多回答(1 个)
Andrei Bobrov
2019-10-1
编辑:Andrei Bobrov
2019-10-1
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 个评论
Daphne PARLIARI
2019-10-1
Thank you!!
I run it and this message appears
"Warning: Variable names were modified to make them valid MATLAB identifiers. "
Daphne PARLIARI
2019-10-31
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?
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!发生错误
由于页面发生更改,无法完成操作。请重新加载页面以查看其更新后的状态。
您也可以从以下列表中选择网站:
如何获得最佳网站性能
选择中国网站(中文或英文)以获得最佳网站性能。其他 MathWorks 国家/地区网站并未针对您所在位置的访问进行优化。
美洲
- América Latina (Español)
- Canada (English)
- United States (English)
欧洲
- 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)
亚太
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)