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

4 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 (...)

Answers (1)

Campion Loong
Campion Loong on 3 Feb 2021
How about this?
>> 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

Categories

Find more on Tables in Help Center and File Exchange

Products


Release

R2019b

Community Treasure Hunt

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

Start Hunting!