Averaging monthly using accumarray for two arrays?

I realize this is a question asked often, but there doesnt appear to be a easy solution.

So I'm trying to average daily data to monthly, across a multitude of years. My data is in a 1000x5 array, with columns being year, month, day, datestr and catch. The number of rows dedicated to each day varies. I'd like to average monthly, and if possible keep the month/year columns.

so far i havent had much success:

data example:

xx(1,:) = 1993  1  26  727942  365

my code currently looks like:

[dontcare, m] = month(xx(:,4));
y = year(xx(:,4));
ii = strcat(numstr(y),m);

for YY = 1:size(xx(:,1)); % So running through the whole of the data set

        for jj = unique(ii, 'rows')
                month_av = accumarray(ii('rows'), xx(:,5),[],@mean); % This doesnt work obviously
        end
end

This doesnt work because, i know, the logic is wrong, but i'm unsure of how to fix it - i need to refernece the same rows in two arrays (ii and xx) to average xx for the unique values in ii.

Any ideas would be much appreciated.

Answer 1

[Peter Perkins]

If you have R2014b or newer, this combination of table and datetime makes such grouped calculations fairly succinct:

>> Date = datetime('today') + caldays(0:100)';
>> Year = Date.Year;
>> Month = Date.Month;
>> Day = Date.Day;
>> Catch = rand(size(Date));
>> t = table(Date,Year,Month,Day,Catch);

>> avgCatch = varfun(@mean,t,'GroupingVariables',{'Year' 'Month'},'InputVariables','Catch')
avgCatch = 
              Year    Month    GroupCount    mean_Catch
              ____    _____    __________    __________
    2015_3    2015    3        26            0.47783   
    2015_4    2015    4        30            0.50448   
    2015_5    2015    5        31            0.47544   
    2015_6    2015    6        14            0.57008

If you have R2013b or newer, you can still use a table, but you'd use datestr and/or datevec instead of datetime. If your version is older than that, them here's the way to do it with accumarray:

>> [ym,~,ymIndex] = unique([Year Month],'rows');
>> avgCatch = [ym accumarray(ymIndex,t.Catch,[],@mean)]
avgCatch =
         2015            3      0.47783
         2015            4      0.50448
         2015            5      0.47544
         2015            6      0.57008

Hope this helps.

[Kirsten du plessis]

Hi Peter,

Thanks so much - that looks like a good way to solve the problem! I havent tried it, as the version i'm using is so old, it doesnt have a table function. I've tried using printmat, but thats asking for row labels.

Working on a solution to that now :)

Thanks again

[SD]

Hi Peter,

I'm a little late to this...

avgTemp = varfun(@mean,t,'GroupingVariables',{'Year' 'Month'},'InputVariables','Temp')

How would you exclude NAN values from the original dataset. I've tried 'omitnan' after 'Temp' and when I was creating the variable.

Is this something you would deal with while importing the dateset?

Thank you,

Shonna

[SD]

Hi Peter,

Never mind. I figured out the solution by just adding nan between the @ and mean

Have a great day and Thank you!

Shonna