CODEWITHSUNDEEP
rdplyrtidyverse
SL42
SL42

Reputation: 221

Monthly Year over Year Growth Rate using dplyr

My goal is to get the Year-over-Year Growth. I have tried group by, arrange, and the mutate function, but the Year-over-Year Growth column is not producing the right result(That is why I am try to get to get a dataset where I have alternating years).

Data:

date         store  city   measurement
2010-01-01   a      LA     150
2010-01-10   b      NY     160
2010-01-20   c      SE     10
...
# There is no 2011-01-01
#2011-01-01   a     LA     900
2011-01-10   b      NY     170
2011-01-20   c      SE     20
...
2012-01-01   a      LA     900
2012-01-10   b      NY     170
2012-01-20   c      SE     20
What I am trying to get:

date         store  city   measurement   YOY
2010-01-01   a      LA     150           ?
2011-01-01   a      LA     900           0
2012-01-01   a      LA     900                    
2010-01-10   b      NY     160           0.0625          
2011-01-10   b      NY     170           0
2012-01-10   b      NY     170    
2010-01-20   c      SE     10            1
2011-01-20   c      SE     20            0         
2012-01-10   b      NY     20 
2010
2011
2012
...

Any help is greatly appreciated.

Upvotes: 3

Views: 1945

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

arrange the data by city and date and for each city calculate YOY by subtracting the previous value of measurement with the current value dividing by the previous value.

library(dplyr)

result <- df %>%
            mutate(date = as.Date(date)) %>%
            arrange(city, date) %>%
            group_by(city) %>%
            mutate(YearoverYear=(measurement-lag(measurement))/lag(measurement))

Upvotes: 2

Related Questions