library(tidyverse)
(df<- tribble(~Qtr,~Category,~Value,
202001,1,10,
202002,1,40,
202003,1,100,
202004,1,20,
202001,2,80,
202002,2,90,
202003,2,4,
202004,2,7,
201901,1,8,
201902,1,9,
201903,1,20,
201904,1,30))
(df2 <- mutate(df,
yr=floor(Qtr/100)) )
(df3 <- group_by(df2,
Category,
yr) %>% mutate(vdiff = Value-lag(Value)))
# Groups: Category, yr [3]
# Qtr Category Value yr vdiff
# <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 202001 1 10 2020 NA
# 2 202002 1 40 2020 30
# 3 202003 1 100 2020 60
# 4 202004 1 20 2020 -80
# 5 202001 2 80 2020 NA
# 6 202002 2 90 2020 10
# 7 202003 2 4 2020 -86
# 8 202004 2 7 2020 3
# 9 201901 1 8 2019 NA
# 10 201902 1 9 2019 1
# 11 201903 1 20 2019 11
# 12 201904 1 30 2019 10