How to calculate weeks of cover for a product using R?-CodePudding

I want to calculate weeks of cover products by group. I am still a learner in R Below is the dataset

WK<-c('wk1','wk2','wk3','wk4','wk5','wk6','wk7','wk8','wk9','wk10','wk11','wk12')
Model<-c('AB','AB','AB','AB','AB','AB','AB','BC','BC','BC','BC','BC')
QTY<-c(100,200,300,400,500,600,800,500,230,500,600,900)
stock<-c(300,600,100,250,362,850,985,236,652,758,550,700)
df<-data.frame(WK,Model,QTY,stock)

'Cover week' is calculated based on Stock(current week)/average(QTY for next 4 weeks). Needs to be calculated for all weeks by model.

Expected result

CodePudding user response：

You can use zoo::rollmeanr with lead:

library(dplyr)
df %>% 
  group_by(Model) %>% 
  mutate(cover = stock / zoo::rollmeanr(lead(QTY, default = 0), k = 4))

output

   WK    Model   QTY stock cover
   <chr> <chr> <dbl> <dbl> <dbl>
 1 wk1   AB      100   300 0.857
 2 wk2   AB      200   600 1.33 
 3 wk3   AB      300   100 0.174
 4 wk4   AB      400   250 0.526
 5 wk5   AB      500   362 1.03 
 6 wk6   AB      600   850 1.89 
 7 wk7   AB      800   985 1.71 
 8 wk8   BC      500   236 0.423
 9 wk9   BC      230   652 1.30 
10 wk10  BC      500   758 1.36 
11 wk11  BC      600   550 1.1  
12 wk12  BC      900   700 1.26

CodePudding user response：

An option with slider

library(dplyr)
library(slider)
df %>% 
  mutate(cover = stock/lead(slide_dbl( QTY, .after = 3, .f = mean)))
 WK Model QTY stock     cover
1   wk1    AB 100   300 0.8571429
2   wk2    AB 200   600 1.3333333
3   wk3    AB 300   100 0.1739130
4   wk4    AB 400   250 0.4166667
5   wk5    AB 500   362 0.6798122
6   wk6    AB 600   850 1.6748768
7   wk7    AB 800   985 2.1530055
8   wk8    BC 500   236 0.4233184
9   wk9    BC 230   652 0.9780000
10 wk10    BC 500   758 1.0106667
11 wk11    BC 600   550 0.6111111
12 wk12    BC 900   700        NA