Monthly Year over Year Growth Rate using dplyr

user_13303213 0
Viewed 507

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.

dplyr tidyverse r
1 Answers

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))
user_39629140
Related