user36176 user36176 - 25 days ago 11
R Question

Apply a function rowwise for calculating percentage changes in R

I have yearly time series data in a long format at a Group-State-Brand level. I want to apply a function that would calculate the growth rates YOY for every level.

basically the (currentvalue/previous value) -1

Find below an extract of the data:

Grp Sta Brnd Yr Sls
A AL Ben's 2012 29770
A AL Ben's 2013 23357
A AL Ben's 2014 22442
A AL Ben's 2015 21848
A AL Ben's 2016 13799
B CA Scott's 2012 1079
B CA Scott's 2013 11178
B CA Scott's 2014 14778
B CA Scott's 2015 15241
B CA Scott's 2016 10569
C TX Joey's 2012 1673
C TX Joey's 2013 1290
C TX Joey's 2014 899
C TX Joey's 2015 732
C TX Joey's 2016 294


Basically, each unique level of grp-state-brand is 5 rows.

Grp Sta Brnd Yr Sls Grwth
A AL Ben's 2012 29770
A AL Ben's 2013 23357 -22%
A AL Ben's 2014 22442 -4%
A AL Ben's 2015 21848 -3%
A AL Ben's 2016 13799 -37%
B CA Scott's 2012 1079
B CA Scott's 2013 11178 936%
B CA Scott's 2014 14778 32%
B CA Scott's 2015 15241 3%
B CA Scott's 2016 10569 -23%
C TX Joey's 2012 1673
C TX Joey's 2013 1290 -23%
C TX Joey's 2014 899 -30%
C TX Joey's 2015 732 -19%
C TX Joey's 2016 294 -60%

Answer
df=data.frame(Grp = c(rep("A",5),rep("B",5),rep("C",5)), Sta = c(rep("AL",5),rep("CA",5),rep("TX",5)), 
          Brnd = c(rep("Ben's",5),rep("Scott's",5),rep("Joey's",5)), 
          Yr=rep(c(2012,2013,2014,2015,2016),3), 
          Sls = c(29770,23357,22442,21848,13799,1079,11178,14778,15241,10569,1673,1290,899,732,294))


ddply(df, .(Grp,Sta,Brnd),mutate, y = sprintf("%.2f%%",c(NA,100*diff(Sls)/Sls[-length(Sls)])))


   Grp Sta    Brnd   Yr   Sls       y
1    A  AL   Ben's 2012 29770     NA%
2    A  AL   Ben's 2013 23357 -21.54%
3    A  AL   Ben's 2014 22442  -3.92%
4    A  AL   Ben's 2015 21848  -2.65%
5    A  AL   Ben's 2016 13799 -36.84%
6    B  CA Scott's 2012  1079     NA%
7    B  CA Scott's 2013 11178 935.96%
8    B  CA Scott's 2014 14778  32.21%
9    B  CA Scott's 2015 15241   3.13%
10   B  CA Scott's 2016 10569 -30.65%
11   C  TX  Joey's 2012  1673     NA%
12   C  TX  Joey's 2013  1290 -22.89%
13   C  TX  Joey's 2014   899 -30.31%
14   C  TX  Joey's 2015   732 -18.58%
15   C  TX  Joey's 2016   294 -59.84%
Comments