Help with correct values of lags

tidyverse
1

Hello!

My data is grouped by each of the variable shown in the example table below (By Date, Country, ....until Product).

I am interested in performing lag of 6 months. It doesn't take grouped details into consideration and provides incorrect values. For example, In below example, as we can see 294 in 3rd row of Sales variable for Product "TR1521TW" and the lag Sales of 294 is shown for another Product instead "TR1614TW". What I would like to achieve is to see right value of lags for right product including its entire group by Date and such.

df <- data.frame(
  stringsAsFactors = FALSE,
                    Date = c("01/01/2018",
                             "01/01/2018","01/01/2018","01/01/2018","01/01/2018",
                             "01/01/2018","01/01/2018","01/01/2018",
                             "01/01/2018","01/01/2018","01/01/2018","02/01/2018",
                             "02/01/2018","02/01/2018","02/01/2018","02/01/2018",
                             "02/01/2018","02/01/2018","02/01/2018",
                             "02/01/2018","02/01/2018","02/01/2018","03/01/2018",
                             "03/01/2018","03/01/2018","03/01/2018","03/01/2018",
                             "03/01/2018","03/01/2018","03/01/2018",
                             "03/01/2018","03/01/2018","03/01/2018","03/01/2018",
                             "03/01/2018","04/01/2018","04/01/2018",
                             "04/01/2018","04/01/2018","04/01/2018","04/01/2018",
                             "04/01/2018","04/01/2018","04/01/2018","05/01/2018",
                             "05/01/2018","05/01/2018","05/01/2018",
                             "05/01/2018","05/01/2018","05/01/2018","05/01/2018",
                             "06/01/2018","06/01/2018","06/01/2018","06/01/2018",
                             "06/01/2018","06/01/2018","06/01/2018",
                             "07/01/2018","07/01/2018","07/01/2018","07/01/2018",
                             "07/01/2018","07/01/2018","07/01/2018","07/01/2018",
                             "07/01/2018","08/01/2018","08/01/2018",
                             "08/01/2018","08/01/2018","08/01/2018","08/01/2018",
                             "08/01/2018","09/01/2018","09/01/2018","09/01/2018",
                             "09/01/2018","09/01/2018","09/01/2018",
                             "10/01/2018","10/01/2018","10/01/2018","10/01/2018",
                             "10/01/2018","10/01/2018","10/01/2018",
                             "11/01/2018","11/01/2018","11/01/2018","11/01/2018",
                             "11/01/2018","11/01/2018","12/01/2018","12/01/2018",
                             "12/01/2018","12/01/2018","12/01/2018",
                             "01/01/2019","01/01/2019","01/01/2019","02/01/2019",
                             "02/01/2019","02/01/2019","02/01/2019","03/01/2019",
                             "03/01/2019","03/01/2019","03/01/2019",
                             "04/01/2019","04/01/2019","04/01/2019","05/01/2019",
                             "05/01/2019","05/01/2019","06/01/2019","06/01/2019",
                             "06/01/2019","07/01/2019","07/01/2019",
                             "08/01/2019","08/01/2019","08/01/2019","09/01/2019",
                             "10/01/2019","10/01/2019","11/01/2019","12/01/2019",
                             "01/01/2020","02/01/2020","03/01/2020",
                             "03/01/2020","04/01/2020","04/01/2020","04/01/2020",
                             "05/01/2020","05/01/2020","05/01/2020",
                             "06/01/2020","07/01/2020","07/01/2020","10/01/2020",
                             "10/01/2020","12/01/2020","02/01/2021"),
                 Country = c("Canada","Canada",
                             "Canada","Canada","Canada","Canada","USA",
                             "USA","USA","USA","USA","Canada","Canada",
                             "Canada","Canada","Canada","USA","USA","USA","USA",
                             "USA","USA","Canada","Canada","Canada",
                             "Canada","Canada","USA","USA","USA","USA","USA",
                             "USA","USA","USA","Canada","Canada","USA","USA",
                             "USA","USA","USA","USA","USA","Canada",
                             "Canada","Canada","USA","USA","USA","USA","USA",
                             "Canada","Canada","Canada","USA","USA","USA",
                             "USA","Canada","Canada","Canada","USA","USA",
                             "USA","USA","USA","USA","Canada","USA","USA",
                             "USA","USA","USA","USA","Canada","Canada",
                             "Canada","USA","USA","USA","Canada","Canada",
                             "USA","USA","USA","USA","USA","Canada","Canada",
                             "USA","USA","USA","USA","Canada","Canada",
                             "USA","USA","USA","Canada","Canada","USA",
                             "Canada","USA","USA","USA","Canada","Canada",
                             "Canada","USA","Canada","Canada","USA","Canada",
                             "Canada","USA","Canada","USA","USA","Canada",
                             "USA","Canada","Canada","USA","USA","USA",
                             "USA","USA","USA","USA","USA","Canada","USA",
                             "Canada","USA","USA","Canada","USA","USA","USA",
                             "Canada","USA","Canada","USA","USA","USA"),
          Market.Product = c("TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM","TM",
                             "TM","TM","TM","TM","TM","TM","TM"),
        Product.Category = c("FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT","FT",
                             "FT","FT","FT","FT","FT","FT","FT"),
            Product.Type = c("MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF","MTF","MTF",
                             "MTF","MTF","MTF","MTF","MTF"),
                 Product = c("TR1521QW",
                             "TR1521RW","TR1521TW","TR1621QB","TR1621RB","TR1621RW",
                             "TR1521TW","TR1614RW","TR1614TW","TR1621RB",
                             "TR1621RW","TR1521RW","TR1521TW","TR1621QB",
                             "TR1621RB","TR1621RW","TR1521TW","TR1614RW",
                             "TR1614TW","TR1619RB","TR1621RB","TR1621RW","TR1521QW",
                             "TR1521RW","TR1521TW","TR1621QB","TR1621RW",
                             "TR1521RW","TR1521TW","TR1614RW","TR1614TW",
                             "TR1619RS","TR1621QW","TR1621RB","TR1621RW",
                             "TR1521RW","TR1521TW","TR1521TW","TR1614RW","TR1614TW",
                             "TR1619RW","TR1621QW","TR1621RB","TR1621RW",
                             "TR1521RW","TR1521TW","TR1621RW","TR1521TW",
                             "TR1614QW","TR1614RW","TR1614TW","TR1621RB",
                             "TR1521RW","TR1521TW","TR1621RW","TR1521TW","TR1614TW",
                             "TR1621RB","TR1621RW","TR1521RW","TR1521TW",
                             "TR1621RW","TR1521RW","TR1521TW","TR1614RW",
                             "TR1614TW","TR1621RB","TR1621RW","TR1521TW",
                             "TR1521TW","TR1614RW","TR1614TW","TR1619RB","TR1621RB",
                             "TR1621RW","TR1521RW","TR1521TW","TR1621RW",
                             "TR1521TW","TR1614TW","TR1621RB","TR1521RW",
                             "TR1621RW","TR1521TW","TR1614RW","TR1614TW",
                             "TR1621RB","TR1621RW","TR1521TW","TR1621RW","TR1521TW",
                             "TR1614RW","TR1614TW","TR1621RB","TR1521TW",
                             "TR1621RW","TR1521TW","TR1614RW","TR1614TW",
                             "TR1521TW","TR1621RW","TR1614TW","TR1521TW",
                             "TR1521RW","TR1521TW","TR1614TW","TR1521TW","TR1621RB",
                             "TR1621RW","TR1614TW","TR1521TW","TR1621RW",
                             "TR1614TW","TR1614TW","TR1621RW","TR1614TW",
                             "TR1614TW","TR1614TW","TR1621RW","TR1621RW",
                             "TR1614TW","TR1521TW","TR1621RW","TR1614TW","TR1614TW",
                             "TR1614TW","TR1621RW","TR1614TW","TR1614TW",
                             "TR1614TW","TR1614TW","TR1614TW","TR1614TW",
                             "TR1614TW","TR1614TW","TR1621RW","TR1614TW",
                             "TR1614TW","TR1621RW","TR1614TW","TR1614TW","TR1614TW",
                             "TR1614TW","TR1614TW","TR1614TW","TR1614TW"),
                   Sales = c(0L,0L,294L,0L,
                             0L,0L,117L,10L,1573L,15L,2L,0L,355L,1L,0L,
                             0L,85L,2L,1493L,22L,51L,2L,0L,0L,356L,4L,
                             0L,0L,192L,8L,1826L,2L,1L,5L,2L,0L,175L,
                             87L,1L,1784L,1L,4L,4L,4L,0L,129L,0L,51L,
                             1L,1L,1896L,4L,0L,75L,0L,42L,2502L,9L,
                             1L,0L,11L,0L,0L,23L,-29L,2362L,4L,3L,4L,
                             6L,3L,2987L,1L,6L,0L,-1L,2L,0L,9L,2700L,
                             6L,1L,0L,4L,7L,2773L,16L,1L,1L,0L,2L,0L,
                             2326L,14L,0L,0L,0L,1L,1983L,0L,0L,1314L,
                             0L,1L,1L,1714L,0L,0L,0L,2164L,0L,0L,1535L,
                             1L,0L,1882L,1L,2190L,1L,0L,2371L,0L,0L,
                             2452L,1939L,2261L,1L,1666L,1375L,1148L,763L,
                             2L,253L,5L,16L,1L,4L,8L,1L,90L,24L,1L,2L,
                             -1L,1L,2L)
      )

df <- df%>%
  mutate(Date = mdy(Date))

# 6 Months Lag
df <- df%>%
  mutate("lag Sales" = lag(Sales, 6))

I was also trying to split data using group_split() so as to create a new list of mini groups where I can then apply lag to each group which can later be combined back. But it gives just one giant list instead and was not helpful.

Any help here would be appreciated.

Thanks!

2

Are you after something like this? The first few will be empty but the after six months they're lagged. You would want to get the grouping right.

library(tidyverse)
df %>% 
  group_by(Country, Product) %>%
  mutate(lag_sales = lag(Sales, 6))
3

Thanks @williaml !

This data was already grouped. I again grouped by as follows, but it shows all NAs for lag_sales

df %>% 
  group_by(Date, Country, `Market.Product`, `Product.Category`,  `Product.Type`, Product) %>%
  mutate(Sales = sum(Sales), lag_sales = lag(Sales, 6))

Thanks!

4

Remove Date from the group_by(). You are lagging by date. I think that will get you what you want.

5

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.