cumsum within a group

7 views (last 30 days)
Pete sherer
Pete sherer on 22 Jun 2016
Answered: Andrei Bobrov on 22 Jun 2016
Hi, Is there a faster way to do a cumulative sum within a group? data looks like
year val cumval
data=[1 10 10;
1 10 20;
1 10 30;
1 10 40;
2 10 10;
2 15 25;
2 20 45;
3 5 5;
4 10 10];
As you see cumVal is sumsum within a year only. Then what I want to do is to compute a new val that reflects this yearly cap. I create 2 more columns. cumval_w_annualCap reflects the annual cap value of 25 in this example. The val_cap is the re-compute val that reflects
cumval_w_annualCap val_cap
tcumsum=[10 10;
20 10;
25 5;
25 0;
10 10;
25 15;
25 0;
5 5;
10 10];
How I compute it seems to be very slow.
val_cap= data( :,2);
yearlyLim=25;
[ uniqYrs, indxYr] = unique( data(:,1));
nUniqYr = length(uniqYrs);
for runYr= 1: nUniqYr
if runYr< nUniqYr
tindx= [indxYr( runYr):indxYr( runYr+1)-1];
else
tindx= [indxYr( runYr):length( data(:,2))];
end %
yrVal = data(tindx,2);
tcumval= cumsum( yrVal, 1);
indxAggCap = find(tcumval >= yearlyLim, 1);
if indxAggCap == 1
yrVal( indxAggCap)= yearlyLim;
else
yrVal( indxAggCap)= yearlyLim - tcumval( indxAggCap-1);
end % if indxAggCap
yrVal( indxAggCap+1:end)= 0;
val_cap( tindx)= yrVal;
end %
Anyway to vectorize this algorithm?
Thanks,

Answers (2)

Thorsten
Thorsten on 22 Jun 2016
Edited: Thorsten on 22 Jun 2016
data=[1 10 10;
1 10 20;
1 10 30;
1 10 40;
2 10 10;
2 15 25;
2 20 45;
3 5 5;
4 10 10];
% first column of tcumsum
yearlyLim=25;
t1 = data(:,3);
t1(t1 > yearlyLim) = yearlyLim;
% second column of tcumsum
[~, b] = unique(data(:,1));
Nb = numel(b);
t2 = arrayfun(@(i) [t1(b(i)); diff(t1(b(i):b(min(i+1, Nb))-1))]', 1:Nb, 'Uni', 0);
t2 = cell2mat(t2)';
tcumsum = [t1 t2];
Since R2015b you can use split apply
t2 = cell2mat(splitapply(@(x) {[x(1); diff(x)]}, t1, data(:,1)));

Andrei Bobrov
Andrei Bobrov on 22 Jun 2016
data=[1 10
1 10
1 10
1 10
2 10
2 15
2 20
3 5
4 10];
[~,~,c] = unique(data(:,1));
d = data(:,2);
o1 = cell2mat(accumarray(c,d,[],@(x){cumsum(x)}))
val_cap = cell2mat(accumarray(c,min(o1,25),[],@(x){[x(1);diff(x)]}));

Categories

Find more on Programming in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!