In Matlab, how to apply an 'AggregationFunction' with two variables in unstack?

478 Views Asked by At

Objective: I would like to get, for each period and group of a timetable, the result of a given function of var1 and var2 [i.e. the ratio of (the sum of var1 over the group) by (the sum of var2 over the group)] using unstack and a function handle:

Data

A = [1 2 3 4 2 4 6 8]';
B = [4 2 14 7 8 4 28 14]';
C=["group1";"group1";"group2";"group2";"group1";"group1";"group2";"group2"];
Year = [2010 2010 2010 2010 2020 2020 2020 2020]';
Year = datetime(string(Year), 'Format', 'yyyy');
t=table(Year,C,A,B,'VariableNames',{'Year' 'group' 'var1' 'var2'});
t=table2timetable(t,'RowTimes','Year');

Desired Output [EDIT]

A table with three columns: year, Ratio_group1, Ratio_group2. Where for instance: Ratio_group1 for 2010 = (1+2) / (4+2) =0.5.

Function

f = @(x,y) sum(x)./sum(y);   %or f = @(x) sum(x(1,:))./sum(x(2,:));
[Ratio,is] = unstack(t,{'var1','var2'},"group",'AggregationFunction',f);

Errors that I get:

%Not enough input arguments.
%Or: Index in position 1 exceeds array bounds (must not exceed 1)

Another failed test inspired from https://www.mathworks.com/help/matlab/ref/double.groupsummary.html (See Method Function Handle with Multiple Inputs)

[Ratio,is] = unstack(t,{["var1"],["var2"]},"group",'AggregationFunction',f);
%Error: A table variable subscript must be a numeric array continaing real positive integers, a logical array (...)
1

There are 1 best solutions below

0
On

This can be done using findgroups and splitapply (or equivalently accumarray):

result = splitapply(@(x,y) sum(x)/sum(y), t.var1, t.var2, findgroups(t.Year, t.group));