Finding Average Day for each hour from large dataset
3 次查看(过去 30 天)
显示 更早的评论
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')
0 个评论
回答(2 个)
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 个评论
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
2022-2-14
编辑:Seth Furman
2022-2-24
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')
Average all variables grouping by Month and Hour
varfun(@mean, tt, "GroupingVariables", ["Month", "Hour"])
0 个评论
另请参阅
类别
在 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!