Monthly average from yearly data missing days with dates in a cell

Question

Luke McLellan 2018-8-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/413105-monthly-average-from-yearly-data-missing-days-with-dates-in-a-cell

编辑： jonas 2018-8-7

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 个评论
显示无隐藏无

jonas 2018-8-1

编辑：jonas 2018-8-1

Could you upload some sample data to work with as well as the desired output?

jonas 2018-8-2

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.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Adam Danz 2018-8-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/413105-monthly-average-from-yearly-data-missing-days-with-dates-in-a-cell#answer_331232

编辑：Adam Danz 2018-8-1

在 MATLAB Online 中打开

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 个评论
显示 2更早的评论隐藏 2更早的评论

Luke McLellan 2018-8-2

编辑：Luke McLellan 2018-8-2

I could genuinely cry now haha. Thank you so much! I never got/understood the ,1 in the mean.

Adam Danz 2018-8-2

在 MATLAB Online 中打开

help mean

The 2nd input (DIM) specifies which dimension to average along.

请先登录，再进行评论。

Answer 2

Peter Perkins 2018-8-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/413105-monthly-average-from-yearly-data-missing-days-with-dates-in-a-cell#answer_331514

在 MATLAB Online 中打开

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 个评论
显示 1更早的评论隐藏 1更早的评论

Luke McLellan 2018-8-7

Hi Adam, this is something I previously looked into. This issue is that the data set can be missing random days throughout the year; where here I believe the last 2 days of the year are omitted. That is why I was initially trying to use a character search to filter through the months and average that way. But as mentioned before, I am not the greatest coder. My Matlab before was typically modelling from equations and not manipulating data sets.

Thank you very much for your help though :)

jonas 2018-8-7

编辑：jonas 2018-8-7

It doesn't matter if data is missing (as demonstrated by Peter Perkins). As I also suggested in the comments, both timetable and findgroups/splitapply are ideal for averaging over time series with missing data.

请先登录，再进行评论。