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

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

[jonas]

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

[jonas]

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]

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

[Luke McLellan]

Thanks very much. I have altered slightly

[~,txtData]  = xlsread(...)
data = xlsread(...)
dates = datetime(datestr(txtData));

months = month(dates); unqMonths = unique(months);
years = year(dates);  unqYears = unique(years);

for k = 1:size(data,2)
    moAvg = zeros(length(unqMonths) * length(unqYears), k);
    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
end

The data file contains 8 columns and I am trying to apply code to loop for each of them. Any tips would be appreciated, over than that thanks once again.

L

[Adam Danz]

If you'd like to take the monthly average for each of the 8 columns, here's the changes you need to make from my version of the code. Only 2 lines have changed and I marked them on the right with an asterix.

Now moAvg will be a [n-by-8] matrix where rows are defined by month/year and columns are your means from you 8 columns. So, moAvg(4,:) are the 8 averages for the 4th month.

   % Loop through each month and do averaging 
   moAvg = zeros(length(unqMonths) * length(unqYears), size(data,2));  %*
   for j = 1:length(unqYears)
       for i = 1:length(unqMonths)
           monthyearIdx = months==unqMonths(i) & years == unqYears(j); 
           thisMonthData = data(monthyearIdx, :);
           moAvg(i,:) = mean(thisMonthData,1);                         %*
       end
   end

[Luke McLellan]

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

[Adam Danz]

help mean

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

Answer 2

[Peter Perkins]

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.

[Adam Danz]

@Luke, this method is faster but the results will be stored in a timetable rather than a matrix. I tested it and my modification below produces the same results as my answer above.

% Fake data
data = rand(364,8);
dates = datetime('01/01/2016') : datetime('12/29/2016');

tt = array2timetable(data, 'RowTimes', dates)
retime(tt,'monthly','mean')

%If you want the data in a matrix
m = tt{:,2:end};

[Luke McLellan]

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]

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.