Finding Average Day for each hour from large dataset

조회 수: 11 (최근 30일)
Ieuan Price-davies
Ieuan Price-davies 2022년 2월 14일
편집: Seth Furman 2022년 2월 24일
Hi there,
I have a large data set of weather data containing hourly recordings over a year(roughly 9000 rows).
I'm trying to find what an average day for each month of the year would look like.
So far I have tried using retime() however these seems to either give me the average for each day or the average for the month.
I've included my code below - the readtable bit is all working, the DateTinme format was proing abit difficult so this was my solution for it.
The part im struggling with is using the retime function. I'd like for this code to output 12 days worth of data showing the average temps etc. in each month over the course of 24 hours (so 24*12 = 288 rows)however It only produces 12 rows showing all the hours over the day averaged to one single value.
I have also tried varfun() and splitapply() but these have also given me the same problem.
Any help anyone could have on this would be greatly appreciated.
Thanks!
%This sections reads it in
%Sets Date time format for when table is read
opts = detectImportOptions("Weather.xlsx");
opts = setvartype(opts,"Date","datetime");
opts = setvaropts(opts,"Date",'InputFormat','dd.MM. HH:mm');
%Names each column and reads table with desired options
opts.VariableNames = ["Index","Date","NormDirectIrradiation","DiffuseIrradiation","GlobalIrradiation","AirHumidity","LongWavelengthIrradiation","OutdoorTemp24h","WindSpeed","OutdoorTempAve"];
conditions = readtable("Weather.xlsx",opts);
%PArt producing wrong output here
T=table2timetable(data);
TT=retime(T,'monthly','mean')

이 질문에 답변하려면 로그인하십시오.

답변 (2개)

Tyler F
Tyler F 2022년 2월 14일
편집: Tyler F 2022년 2월 14일
Your first column is hours, you could use the reshape command to convert your data into a 24 x 365 matrix then do operations on the rows for your daily math (mean, etc.). Its a little more tricky to break it up into months because of the variable days/month but you could just take the first 31 for Jan, next 28 for feb, etc.
For example, x = reshape(conditions.NormDirectIrradiation,24,[]) will return a 24x365 matrix of hours x days. To get the average per day you can do mean(x) and to get the average per hour mean(x')
  댓글 수: 2
Ieuan Price-davies
Ieuan Price-davies 2022년 2월 14일
You're right this would work, however it seems quite a long workaround - I would end up with 10+ Matrices to work with , was hoping to do it in a more efficient way
Tyler F
Tyler F 2022년 2월 14일
Put it into a multidimensional array.
TableExtract = conditions{:,3:end};
MDimData = reshape(TableExtract,24,365,8);
Now you have a single data structure you can work with. For example, to compute the mean of each column:
MeanData = squeeze(mean(MDimData));
Each column is the daily mean of your columns so
MeanData(:,1)
Would be the the mean of NormDirectIrradiation

댓글을 달려면 로그인하십시오.

Seth Furman
Seth Furman 2022년 2월 14일
편집: Seth Furman 2022년 2월 24일
Ran in:
Read data
opts = detectImportOptions("https://www.mathworks.com/matlabcentral/answers/uploaded_files/894410/Weather.xlsx");
opts = setvartype(opts,"Date","datetime");
opts = setvaropts(opts,"Date",'InputFormat','dd.MM. HH:mm');
opts.VariableNames = ["Index","Date","NormDirectIrradiation","DiffuseIrradiation","GlobalIrradiation","AirHumidity","LongWavelengthIrradiation","OutdoorTemp24h","WindSpeed","OutdoorTempAve"];
conditions = readtable("https://www.mathworks.com/matlabcentral/answers/uploaded_files/894410/Weather.xlsx",opts);
tt = table2timetable(conditions);
Add variables Month and Hour for grouping
tt = addvars(tt, month(tt.Date), 'Before', 'Index', 'NewVariableNames', 'Month');
tt = addvars(tt, hour(tt.Date), 'Before', 'Index', 'NewVariableNames', 'Hour')
tt = 8760×11 timetable
Date Month Hour Index NormDirectIrradiation DiffuseIrradiation GlobalIrradiation AirHumidity LongWavelengthIrradiation OutdoorTemp24h WindSpeed OutdoorTempAve ____________ _____ ____ _____ _____________________ __________________ _________________ ___________ _________________________ ______________ _________ ______________ 01.01. 00:00 1 0 0 0 0 0 86.4 301 2 5 3.5 01.01. 01:00 1 1 1 0 0 0 87.6 301 1.9 4.6 3.3 01.01. 02:00 1 2 2 0 0 0 88.8 300 1.9 6.2 3.1 01.01. 03:00 1 3 3 0 0 0 90.1 300 1.9 6.6 2.8 01.01. 04:00 1 4 4 0 0 0 87.9 301 1.9 7.2 3.2 01.01. 05:00 1 5 5 0 0 0 88.2 300 1.9 5.9 3 01.01. 06:00 1 6 6 0 0 0 88.5 300 1.9 5.9 2.9 01.01. 07:00 1 7 7 0 0 0 87.1 300 1.9 5.9 3.1 01.01. 08:00 1 8 8 0 0 0 87.2 299 2 5.5 3 01.01. 09:00 1 9 9 0 2.7 2.7 86.2 299 2 5 3.2 01.01. 10:00 1 10 10 0 30.4 30.4 82.7 293 2 5.2 3.8 01.01. 11:00 1 11 11 293 76.9 125 74.5 275 2.1 5.5 5.4 01.01. 12:00 1 12 12 265 58.6 104 71.6 282 2.2 5 6 01.01. 13:00 1 13 13 276 48.2 87.1 70.4 282 2.3 5 6.2 01.01. 14:00 1 14 14 0 23 23 72.8 294 2.5 4.8 5.6 01.01. 15:00 1 15 15 0 0.03 0.03 73.9 292 2.6 5.9 5.3
Average all variables grouping by Month and Hour
varfun(@mean, tt, "GroupingVariables", ["Month", "Hour"])
ans = 288×12 timetable
Date Month Hour GroupCount mean_Index mean_NormDirectIrradiation mean_DiffuseIrradiation mean_GlobalIrradiation mean_AirHumidity mean_LongWavelengthIrradiation mean_OutdoorTemp24h mean_WindSpeed mean_OutdoorTempAve ____________ _____ ____ __________ __________ __________________________ _______________________ ______________________ ________________ ______________________________ ___________________ ______________ ___________________ 01.01. 00:00 1 0 31 360 0 0 0 85.452 290.9 4.3774 5.3968 3.6355 01.01. 01:00 1 1 31 361 0 0 0 86.729 290.58 4.3774 5.1355 3.3968 01.01. 02:00 1 2 31 362 0 0 0 87.987 290.16 4.371 5.2903 3.1548 01.01. 03:00 1 3 31 363 0 0 0 88.358 290.13 4.371 5.4355 3.0839 01.01. 04:00 1 4 31 364 0 0 0 87.584 290.52 4.371 5.4032 3.229 01.01. 05:00 1 5 31 365 0 0 0 88.313 290.23 4.3742 5.4774 3.1032 01.01. 06:00 1 6 31 366 0 0 0 88.503 290.42 4.3742 5.3387 3.071 01.01. 07:00 1 7 31 367 0 0 0 88.139 290.68 4.3645 5.5097 3.1548 01.01. 08:00 1 8 31 368 0 0.54194 0.54194 88.31 290.71 4.3613 5.6581 3.1258 01.01. 09:00 1 9 31 369 94.723 19.571 26.752 82.255 291.9 4.3613 5.5774 4.2968 01.01. 10:00 1 10 31 370 146.02 38.997 61.297 78.058 291.06 4.371 5.4484 5.1903 01.01. 11:00 1 11 31 371 183.84 50.677 85.832 74.723 290.71 4.3774 5.5516 5.9516 01.01. 12:00 1 12 31 372 175.06 54.023 89.839 72.926 291.74 4.3839 5.4516 6.4032 01.01. 13:00 1 13 31 373 175.87 53.274 85.632 71.906 291.03 4.3935 5.4806 6.6355 01.01. 14:00 1 14 31 374 172.86 36.684 58.606 72.394 290.97 4.4065 5.3968 6.5065 01.01. 15:00 1 15 31 375 33.548 10.851 12.965 74.981 294.52 4.4161 5.3839 5.8839

카테고리

Help Center 및 File Exchange에서 Calendar에 대해 자세히 알아보기

태그

제품


릴리스

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by