How to apply an 'AggregationFunction' with two variables in unstack ?

17 views (last 30 days)
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: https://www.mathworks.com/help/matlab/ref/unstack.html
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');
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);
The types of errors I get:
%Not enough input arguments.
%Or: Index in position 1 exceeds array bounds (must not exceed 1)
(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 containing real positive integers, a logical array (...)

Campion Loong on 3 Feb 2021
>> t_groupsum = groupsummary(t,'group','sum',{'var1','var2'});
>> t_groupsum.Properties.RowNames = t_groupsum.group; % I like the group names as RowLabels
% Ratio of var1 sum between groups
>> t_groupsum.sum_var1("group1")/t_groupsum.sum_var1("group2")
ans =
0.4286
% Ratio of var2 sum between groups
>> t_groupsum.sum_var2("group1")/t_groupsum.sum_var2("group2")
ans =
0.2857

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by