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

user_11465261 0
Viewed 52

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 enter image description here

dataframe r date
2 Answers

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
user_134606020

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
user_37322710
Related