Subtract One Category from Another by Quarter in R

I want to subtract sales for each category by quarter with the first quarter being skipped from generating an output and not calculating year-over-year (evaluate 2019 and 2020 separately)

df

       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
20194       1             30

Expected Results:

        Qtr    Name         Value 
202001      1                 
202002      1             30    
202003      1             60    
202004      1             -80    
202001      2                 
202002      2             10    
202003      2             -86    
202004      2             3    
201901      1             
201902      1             1
201903      1             11
20194       1             10
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
1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.