Function similar to MS Excel "subtotal"? (for 2 conditions)

Hi,

I have a similar question to this one: http://www.mathworks.com/matlabcentral/answers/20132-function-similar-to-ms-excel-subtotal

but the difference is that I need to use the unique function for 2 separete conditions. I'll explain

I have a matrix which is 9x8036 size, where the first 6 rows are data and the last 3 rows are year, month and day from which the data came from. The columns are in order from the first day (01-01-1979) until the last day i have the data (31-12-2000)

A little crop from my matrix, which shows the first 5 days from my chosen period:

I'd like the mean of January for each year (i.e. 22 results), then the mean of February for each year (again, 22 results..), and like that for each month and for each year.

I tried the unique function, like they respond in the other post, but i only get the mean of all January's days for all years (1 result only).

Any idea?

BTW, I'm a beginner in Matlab and all i have done so far is modify previews scripts to match them doing what i need, but this is really new for me. I would love if the response (if any) could come in detail for dumb people :)

Thanks in advance!

Answer 1

[the cyclist]

Here is some code adapted from Titus' code in the prior question:

x = [2012 1 23;
     2012 1 25;
     2012 2 38;
     2012 2 38;
     2012 2 44;
     2012 3 41;
     2012 3 41;
     2012 4 30;
     2012 4  8;
     2012 4 36;
     2012 4 11;
     2013 1 12;
     2013 1 13;
     2013 1 38;
     2013 2 29;
     2013 2 43;
     2013 3 22]; 
% find the different indices: 
[vals vec id] = unique(x(:,[1 2]),'rows'); 
subTotals = zeros(size(vals,1),1); 
for i=1:numel(id) 
  subTotals(i) = mean(x(id==id(i), 3));
end 

subTotalReplicatedAllRows = [x(:,[1 2]),subTotals]

subTotalUniqueRows = unique(subTotalReplicatedAllRows,'rows')

This code looks at the first two columns (year,month) for unique pairs, and averages the third column for those entries.

There are two outputs:

subTotalReplicatedAllRows has the same number of rows as there were in x, with the mean value appearing multiple times. (For example, [2012 1] appears twice, so the mean value of 24 appears twice as well.)

subTotalUniqueRows has only one row per unique month.

Answer 2

[Sean de Wolski]

Just use accumarray (same as cyclist's only cleaner):

x = [2012 1 23;
   2012 1 25;
   2012 2 38;
   2012 2 38;
   2012 2 44;
   2012 3 41;
   2012 3 41;
   2012 4 30;
   2012 4  8;
   2012 4 36;
   2012 4 11;
   2013 1 12;
   2013 1 13;
   2013 1 38;
   2013 2 29;
   2013 2 43;
   2013 3 22]; 
[vals vec id] = unique(x(:,[1 2]),'rows');

Cyclist's way

% find the different indices: 
subTotals = zeros(size(vals,1),1); 
for i=1:numel(id) 
  subTotals(i) = mean(x(id==id(i), 3));
end 
subTotalReplicatedAllRows = [x(:,[1 2]),subTotals]
subTotalUniqueRows = unique(subTotalReplicatedAllRows,'rows')

My Way:

mm = accumarray(id,x(:,3),[],@mean);
xmm = [x(vec,[1 2]) mm]

[the cyclist]

@lightworks, I'm really glad that Sean posted the accumarray solution, as that is definitely the preferred method. accumarray() is sometimes difficult to understand for beginners, though. So I suggest you refer to my solution to understand what accumarray is effectively doing "under the hood", but definitely use accumarray in your code!

[lightworks]

Thanks a lot!

I don't get how to properly use accumerray, but the other worked great. I'll keep that in mind for improves on the script, though.