dimanche 4 mars 2018

Recent Four Weeks Average Sales based on a condition in R

I have dataset of promoted products during certain weeks, I want to compute the avg sales of products during promotion to non promotion sales for the past 4 non promo (indicated by flag)weeks. If the transaction is during non promo period , we should take the sale as it is we have to take avg of sales of recent non promo weeks sales, they may be non continous.

Please note that the

structure(list(Product_group = structure(c(1L, 1L, 1L, 1L, 1L, 
1L), .Label = "A", class = "factor"), Promo = structure(c(1L, 
1L, 2L, 1L, 1L, 2L), .Label = c("0", "1"), class = "factor"), 
    Week = structure(c(1L, 2L, 2L, 3L, 4L, 5L), .Label = c("2017-01-01", 
    "2017-01-02", "2017-01-04", "2017-01-05", "2017-01-06", "2017-01-08", 
    "2017-01-09"), class = "factor"), Sales = c(50, 50, 60, 70, 
    50, 50)), .Names = c("Product_group", "Promo", "Week", "Sales"
), row.names = c(NA, 6L), class = "data.frame")

head(df)
  Product_group Promo       Week Sales
1             A     0 2017-01-01    50
2             A     0 2017-01-02    50
3             A     1 2017-01-02    60
4             A     0 2017-01-04    70
5             A     0 2017-01-05    50
6             A     1 2017-01-06    50

I am looking for an output like

          Product_group Promo       Week Sales Avg Pre Promo Sales
    1             A     0 2017-01-01    50      50 # Since it is non promo
    2             A     0 2017-01-02    50      50 
    3             A     1 2017-01-02    60      50 # 100/2
    4             A     0 2017-01-04    70      70 
    5             A     0 2017-01-05    50      50
    6             A     1 2017-01-06    50      55 # (50 +70 + 50 + 50 )/4

Aucun commentaire:

Enregistrer un commentaire