Monthly average from yearly data missing days with dates in a cell
2 views (last 30 days)
Show older comments
Hi there,
I have gotten myself lost. I was able to achieve what I am about to ask using a lot of code but was wondering if there was a way to automate it. I have a large matrix of 364x8 that contains data point (m) over each day of the year (missing 2 days) over 8 time intervals. I have the dates in a cell in the format 01 January 2016 for example. Is there a way to get the averages for each month, for each time interval, automatically? I have been trying to search for 3 characters (i.e Jan) and averages all rows correspondingly, and so on, but have failed miserably.
Previously, I built smaller matrices over the desired time intervals (each month, by indexing) and averaged that way; then recombined the matrices. But, I will be looking to expand my data over multiple years so automation is ideal. Any help would be appreciated.
Thanks
L
2 Comments
jonas
on 2 Aug 2018
I think this would be an ideal situation to either use a timetable or findgroups/splitapply if you want to avoid loops. Just something to consider.
Accepted Answer
Adam Danz
on 1 Aug 2018
Edited: Adam Danz
on 1 Aug 2018
Here I create fake data that follow your descriptions. Then I group the datetime vector by month and year and loop through each month to average all the data for that month for each year. In my example, all data are from 2016 but if your data span >1 year this strategy will not combine months from multiple years.
% Fake data
data = rand(364,8);
dates = datetime('01/01/2016') : datetime('12/29/2016');
% Group by month
months = month(dates);
unqMonths = unique(months);
% Group by year
years = year(dates);
unqYears = unique(years);
% Loop through each month and do averaging
moAvg = zeros(length(unqMonths) * length(unqYears), 1);
for j = 1:length(unqYears)
for i = 1:length(unqMonths)
monthyearIdx = months==unqMonths(i) & years == unqYears(j);
thisMonthData = data(monthyearIdx, :);
moAvg(i) = mean(thisMonthData(:));
end
end
4 Comments
Adam Danz
on 2 Aug 2018
help mean
The 2nd input (DIM) specifies which dimension to average along.
More Answers (1)
Peter Perkins
on 3 Aug 2018
Don't use a loop. Use a timetable and retime:
>> data = array2timetable(rand(366,3),'RowTimes',datetime(2016,1,1:366));
>> data(randperm(366,5),:) = []; % remove a few days at random
>> retime(data,'monthly','mean')
ans =
12×3 timetable
Time Var1 Var2 Var3
___________ _______ _______ _______
01-Jan-2016 0.54266 0.39816 0.51137
01-Feb-2016 0.49972 0.50024 0.54141
01-Mar-2016 0.53285 0.51973 0.52028
01-Apr-2016 0.52634 0.41247 0.55255
01-May-2016 0.5884 0.54725 0.49745
01-Jun-2016 0.45233 0.51843 0.54474
01-Jul-2016 0.48747 0.49736 0.59025
01-Aug-2016 0.4588 0.45544 0.57767
01-Sep-2016 0.45116 0.46 0.47083
01-Oct-2016 0.49162 0.52662 0.54638
01-Nov-2016 0.49158 0.52979 0.61813
01-Dec-2016 0.4624 0.52723 0.47345
I'm not sure what you mean by "8 time intervals". Maybe that's what your eight columns represent, or maybe something else. In any case, the above can be extended.
3 Comments
See Also
Categories
Find more on Time Series Events 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!