Help with lead or lag

ksingh19 · August 21, 2023, 2:33pm

Hello!

Need help to have correct lag or lead Columns.
Apparently it shows NAs with lag as well as lead. Example column here is Volume2

df <- data.frame(
  stringsAsFactors = FALSE,
              Date = c(202308,202308,202308,202309,
                       202309,202309,202310,202310,202310,202311,202311,
                       202311,202312,202312,202312,202308,202308,202308,
                       202309,202309,202309,202310,202310,202310,202311,
                       202311,202311,202312,202312,202312,202401,202401,
                       202401,202402,202402,202402,202403,202403,202403,
                       202404,202404,202404,202405,202405,202405,202406,
                       202406,202406,202407,202407,202407,202408,202408,202408,
                       202409,202409,202409,202410,202410,202410,202411,
                       202411,202411,202412,202412,202412),
             State = c("Canada","Canada","Canada",
                       "Canada","Canada","Canada","Canada","Canada","Canada",
                       "Canada","Canada","Canada","Canada","Canada",
                       "Canada","USA","USA","USA","USA","USA","USA","USA",
                       "USA","USA","USA","USA","USA","USA","USA","USA",
                       "USA","USA","USA","USA","USA","USA","USA","USA","USA",
                       "USA","USA","USA","USA","USA","USA","USA","USA",
                       "USA","USA","USA","USA","USA","USA","USA","USA",
                       "USA","USA","USA","USA","USA","USA","USA","USA",
                       "USA","USA","USA"),
          Category = c("Soft Goods","Hard Goods",
                       "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
                       "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
                       "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
                       "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
                       "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
                       "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
                       "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
                       "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
                       "Hard Goods","Hard Goods","Soft Goods","Hard Goods","Hard Goods",
                       "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
                       "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
                       "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
                       "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
                       "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
                       "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
                       "Soft Goods","Hard Goods","Hard Goods"),
              Type = c("Type A","Type B","Type C",
                       "Type A","Type B","Type C","Type A","Type B","Type C",
                       "Type A","Type B","Type C","Type A","Type B",
                       "Type C","Type A","Type B","Type C","Type A","Type B",
                       "Type C","Type A","Type B","Type C","Type A","Type B",
                       "Type C","Type A","Type B","Type C","Type A",
                       "Type B","Type C","Type A","Type B","Type C","Type A",
                       "Type B","Type C","Type A","Type B","Type C","Type A",
                       "Type B","Type C","Type A","Type B","Type C",
                       "Type A","Type B","Type C","Type A","Type B","Type C",
                       "Type A","Type B","Type C","Type A","Type B","Type C",
                       "Type A","Type B","Type C","Type A","Type B","Type C"),
           Product = c("Product A","Product B",
                       "Product C","Product A","Product B","Product C",
                       "Product A","Product B","Product C","Product A","Product B",
                       "Product C","Product A","Product B","Product C",
                       "Product A","Product B","Product C","Product A","Product B",
                       "Product C","Product A","Product B","Product C",
                       "Product A","Product B","Product C","Product A",
                       "Product B","Product C","Product A","Product B","Product C",
                       "Product A","Product B","Product C","Product A",
                       "Product B","Product C","Product A","Product B","Product C",
                       "Product A","Product B","Product C","Product A",
                       "Product B","Product C","Product A","Product B",
                       "Product C","Product A","Product B","Product C","Product A",
                       "Product B","Product C","Product A","Product B",
                       "Product C","Product A","Product B","Product C",
                       "Product A","Product B","Product C"),
            Volume = c(60,0,2,100,0,5,80,0,5,
                       75,0,0,100,0,4,15,0,1,0,0,1,10,0,1,0,0,1,
                       0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,6,0,0,0,
                       0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
                       0),
              Rate = c(0.0425,0,0.0185,0.0425,0,
                       0.0185,0.0425,0,0.0185,0.0425,0,0.0185,0.0425,0,
                       0.0185,0.0180833333333333,0,0.0141666666666667,
                       0.0180833333333333,0,0.0141666666666667,0.0180833333333333,0,
                       0.0141666666666667,0.0180833333333333,0,
                       0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
                       0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
                       0,0.0141666666666667,0.0180833333333333,0,
                       0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
                       0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
                       0,0.0141666666666667,0.0180833333333333,0,
                       0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
                       0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
                       0,0.0141666666666667,0.0180833333333333,0,
                       0.0141666666666667,0.0180833333333333,0,0.0141666666666667)
)

df1 <- df %>%
  mutate(Volume1 = Volume * (1-Rate)) %>%
  group_by(Date, State, Category, Type, Product) %>%
  mutate(Volume2 = lead(Volume1))

FJCC · August 21, 2023, 2:46pm

All of your groups consist of one row. There are no values for the lead() function to return. You can see the group size with this code

df1 <- df %>%
  mutate(Volume1 = Volume * (1-Rate)) %>%
  group_by(Date, State, Category, Type, Product) %>%
  mutate(N = n())
range(df1$N)

Can you explain more about what your goal is?

ksingh19 · August 21, 2023, 4:08pm

Thanks @FJCC !

That's a good point. I was looking to use lead because the calculation for Volume2 should be same as Volume1 , but from prior month. So, Volume1 of 57.45 from 202308, first row should be used for row 4, 202309. Each month's value by group should match to previous month's value of Volume1

Thanks!

FJCC · August 21, 2023, 5:55pm

I think you can use

df1 <- df %>%
  mutate(Volume1 = Volume * (1-Rate)) %>%
  group_by(State, Category, Type, Product) %>%
  mutate(Volume2 = lag(Volume1))

ksingh19 · August 21, 2023, 5:57pm

Yes, I tried that first. But even lag gives NAs for some reason

FJCC · August 21, 2023, 6:08pm

Did you notice I dropped Date from the group_by()?

df <- data.frame(
  stringsAsFactors = FALSE,
  Date = c(202308,202308,202308,202309,
           202309,202309,202310,202310,202310,202311,202311,
           202311,202312,202312,202312,202308,202308,202308,
           202309,202309,202309,202310,202310,202310,202311,
           202311,202311,202312,202312,202312,202401,202401,
           202401,202402,202402,202402,202403,202403,202403,
           202404,202404,202404,202405,202405,202405,202406,
           202406,202406,202407,202407,202407,202408,202408,202408,
           202409,202409,202409,202410,202410,202410,202411,
           202411,202411,202412,202412,202412),
  State = c("Canada","Canada","Canada",
            "Canada","Canada","Canada","Canada","Canada","Canada",
            "Canada","Canada","Canada","Canada","Canada",
            "Canada","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA"),
  Category = c("Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods"),
  Type = c("Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B",
           "Type C","Type A","Type B","Type C","Type A","Type B",
           "Type C","Type A","Type B","Type C","Type A","Type B",
           "Type C","Type A","Type B","Type C","Type A",
           "Type B","Type C","Type A","Type B","Type C","Type A",
           "Type B","Type C","Type A","Type B","Type C","Type A",
           "Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C"),
  Product = c("Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C"),
  Volume = c(60,0,2,100,0,5,80,0,5,
             75,0,0,100,0,4,15,0,1,0,0,1,10,0,1,0,0,1,
             0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,6,0,0,0,
             0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
             0),
  Rate = c(0.0425,0,0.0185,0.0425,0,
           0.0185,0.0425,0,0.0185,0.0425,0,0.0185,0.0425,0,
           0.0185,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
           0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
           0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
           0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667)
)
library(dplyr)

df1 <- df %>%
  mutate(Volume1 = Volume * (1-Rate)) %>%
  group_by(State, Category, Type, Product) %>%
  mutate(Volume2 = lag(Volume1))

head(df1)
#> # A tibble: 6 × 9
#> # Groups:   State, Category, Type, Product [3]
#>     Date State  Category   Type   Product   Volume   Rate Volume1 Volume2
#>    <dbl> <chr>  <chr>      <chr>  <chr>      <dbl>  <dbl>   <dbl>   <dbl>
#> 1 202308 Canada Soft Goods Type A Product A     60 0.0425   57.4    NA   
#> 2 202308 Canada Hard Goods Type B Product B      0 0         0      NA   
#> 3 202308 Canada Hard Goods Type C Product C      2 0.0185    1.96   NA   
#> 4 202309 Canada Soft Goods Type A Product A    100 0.0425   95.8    57.4 
#> 5 202309 Canada Hard Goods Type B Product B      0 0         0       0   
#> 6 202309 Canada Hard Goods Type C Product C      5 0.0185    4.91    1.96

^{Created on 2023-08-21 with reprex v2.0.2}

ksingh19 · August 21, 2023, 7:26pm

Thanks for letting me know on Date and it works fine now.
Can you please guide me as to why we are dropping Date or why it creates the issue

FJCC · August 21, 2023, 8:36pm

Including Date in your group_by(), along with State, Category, Type, and Product, results in each group having one member. For example, there is only one row with 202308, Canada, Soft Goods, Type A, Product A. But what you want is that Volume2 have the value of Volume1 from the previous month with the same values of State, Category, Type, and Product. If you group_by() State, Category, Type, and Product, each group will have all the dates that share the same values of the grouping categories. You can then use lag() to shift the Volume1 values by one row within the group.

ksingh19 · August 21, 2023, 8:44pm

Got It Thanks a bunch!

ksingh19 · August 22, 2023, 2:30pm

I noticed that, for the last year on each group produces NAs because we don't have any extra rows for that group where it can perform lag.

For above output, in 202312, for Canada, none of the values are getting picked up in Volume2 and same is true for any group.

I tried using

group_by(State) %>%
group_modify(~add_row(.x, .after = 0))

But that doesn't add extra row at the bottom of each group, instead adds it at the top.
How can we capture all data of Volume1 from previous month for Volume2 ?

Thanks again for the help!

FJCC · August 22, 2023, 4:10pm

I do not see NA values in Volume2 in the last year of each group.

 df1[10:15,]
# A tibble: 6 × 9
# Groups:   State, Category, Type, Product [3]
    Date State  Category   Type   Product   Volume   Rate Volume1 Volume2
   <dbl> <chr>  <chr>      <chr>  <chr>      <dbl>  <dbl>   <dbl>   <dbl>
1 202311 Canada Soft Goods Type A Product A     75 0.0425   71.8    76.6 
2 202311 Canada Hard Goods Type B Product B      0 0         0       0   
3 202311 Canada Hard Goods Type C Product C      0 0.0185    0       4.91
4 202312 Canada Soft Goods Type A Product A    100 0.0425   95.8    71.8 
5 202312 Canada Hard Goods Type B Product B      0 0         0       0   
6 202312 Canada Hard Goods Type C Product C      4 0.0185    3.93    0

tail(df1)
# A tibble: 6 × 9
# Groups:   State, Category, Type, Product [3]
    Date State Category   Type   Product   Volume   Rate Volume1 Volume2
   <dbl> <chr> <chr>      <chr>  <chr>      <dbl>  <dbl>   <dbl>   <dbl>
1 202411 USA   Soft Goods Type A Product A      0 0.0181       0       0
2 202411 USA   Hard Goods Type B Product B      0 0            0       0
3 202411 USA   Hard Goods Type C Product C      0 0.0142       0       0
4 202412 USA   Soft Goods Type A Product A      0 0.0181       0       0
5 202412 USA   Hard Goods Type B Product B      0 0            0       0
6 202412 USA   Hard Goods Type C Product C      0 0.0142       0       0

Please post what you are seeing.

ksingh19 · August 22, 2023, 5:34pm

Sorry! You are right, not getting NAs.
But am looking to capture values of all data from Volume1 to Volume2 in its next month. Issue is only in the last year for each group.
Next month value of Volume 2 is dependant on previous month's value of Volume1. Everything works fine here while lagging except for the last year in each group as there is no extra row for next month for Vomue2.

So, looking to capture highlighted numbers from Volume1 in Volume2 for 202401 in this case as thats the next month. That's why I was trying to insert extra rows using add_rows but was unsuccessful.

FJCC · August 22, 2023, 8:19pm

Would it be acceptable to increment the Date by one month and store it in a new column rather than append rows just to store the Volume2 value?

library(dplyr)
library(lubridate)
df1 <- df %>%
  mutate(Date = ym(as.character(Date)),
         Volume1 = Volume * (1-Rate),
         Vol2_Date = Date %m+% months(1))

The Volume1 column now serves the purpose of your original Volume1 when referenced to the Date column and it looks like your original Volume2 if referenced to the Vol2_Date column. Both Date columns are now actually Date values instead of numbers.

ksingh19 · August 22, 2023, 8:42pm

Thanks @FJCC !
This would definitely serve the purpose for time being, if there is no other solution. From this, I can create 2 dataframes and then combine. However, because I do need to create few more similar columns like Volume2 and these would be all based on newly created column, thus, its not going to be best practice for me.

But thanks a bunch to give an alternate solution. This is definitely helpful for the time being.

ksingh19 · August 27, 2023, 4:59pm

Hi All!

Really need help with using lagged figures here. In above example, I need to add new columns Volume 2 which depends on Volume1, Volume 3 which depends on Volume 2, Volume 4 depending on Volume3 and so on until Volume 6 depending on Volume5.

Also, all the values from previous column should be used for the new Column such as all the values should be used from Volume 1 to Volume 2 and so on until Volume 6 which uses all values from Volume 5.

Creating subsets using above method is very tedious and looking for better ways to resolve this problem.

Thanks again for all your help!

carlne · August 29, 2023, 11:46am

I'm curious if there's a method to automate this process for every row. Nevertheless, your explanation has provided me with a fresh perspective. I'm still in the process of seeking a solution, and your assistance is proving to be immensely valuable! teatv apk

FJCC · August 29, 2023, 3:59pm

Here is code that adds 4 months of rows to each group of State, Category, Type , and Product while putting NA in the Volume and Rate columns of the new months. Does adding those rows allow you to use lag()?

df <- data.frame(
  stringsAsFactors = FALSE,
  Date = c(202308,202308,202308,202309,
           202309,202309,202310,202310,202310,202311,202311,
           202311,202312,202312,202312,202308,202308,202308,
           202309,202309,202309,202310,202310,202310,202311,
           202311,202311,202312,202312,202312,202401,202401,
           202401,202402,202402,202402,202403,202403,202403,
           202404,202404,202404,202405,202405,202405,202406,
           202406,202406,202407,202407,202407,202408,202408,202408,
           202409,202409,202409,202410,202410,202410,202411,
           202411,202411,202412,202412,202412),
  State = c("Canada","Canada","Canada",
            "Canada","Canada","Canada","Canada","Canada","Canada",
            "Canada","Canada","Canada","Canada","Canada",
            "Canada","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA","USA","USA","USA","USA","USA",
            "USA","USA","USA"),
  Category = c("Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods","Soft Goods",
               "Hard Goods","Hard Goods","Soft Goods","Hard Goods",
               "Hard Goods","Soft Goods","Hard Goods","Hard Goods",
               "Soft Goods","Hard Goods","Hard Goods"),
  Type = c("Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B",
           "Type C","Type A","Type B","Type C","Type A","Type B",
           "Type C","Type A","Type B","Type C","Type A","Type B",
           "Type C","Type A","Type B","Type C","Type A",
           "Type B","Type C","Type A","Type B","Type C","Type A",
           "Type B","Type C","Type A","Type B","Type C","Type A",
           "Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C",
           "Type A","Type B","Type C","Type A","Type B","Type C"),
  Product = c("Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C","Product A",
              "Product B","Product C","Product A","Product B",
              "Product C","Product A","Product B","Product C",
              "Product A","Product B","Product C"),
  Volume = c(60,0,2,100,0,5,80,0,5,
             75,0,0,100,0,4,15,0,1,0,0,1,10,0,1,0,0,1,
             0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,6,0,0,0,
             0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
             0),
  Rate = c(0.0425,0,0.0185,0.0425,0,
           0.0185,0.0425,0,0.0185,0.0425,0,0.0185,0.0425,0,
           0.0185,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
           0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
           0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667,
           0.0180833333333333,0,0.0141666666666667,0.0180833333333333,
           0,0.0141666666666667,0.0180833333333333,0,
           0.0141666666666667,0.0180833333333333,0,0.0141666666666667)
)

library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
df <- df |> mutate(Date = ym(as.character(Date)))
AddMonths <- df |> group_by(State, Category, Type, Product) |> 
  summarize(DateMax = max(Date), .groups = "keep") |> 
  reframe(Date = DateMax %m+% months(1:4))
dfExpand <- bind_rows(df, AddMonths) |> arrange(State, Category, Type, Product,Date)
head(dfExpand,10)
#>          Date  State   Category   Type   Product Volume   Rate
#> 1  2023-08-01 Canada Hard Goods Type B Product B      0 0.0000
#> 2  2023-09-01 Canada Hard Goods Type B Product B      0 0.0000
#> 3  2023-10-01 Canada Hard Goods Type B Product B      0 0.0000
#> 4  2023-11-01 Canada Hard Goods Type B Product B      0 0.0000
#> 5  2023-12-01 Canada Hard Goods Type B Product B      0 0.0000
#> 6  2024-01-01 Canada Hard Goods Type B Product B     NA     NA
#> 7  2024-02-01 Canada Hard Goods Type B Product B     NA     NA
#> 8  2024-03-01 Canada Hard Goods Type B Product B     NA     NA
#> 9  2024-04-01 Canada Hard Goods Type B Product B     NA     NA
#> 10 2023-08-01 Canada Hard Goods Type C Product C      2 0.0185

^{Created on 2023-08-29 with reprex v2.0.2}

ksingh19 · September 1, 2023, 6:12pm

Thanks @FJCC !!
Yes, this works.

Thanks a bunch!

system · September 8, 2023, 6:13pm

This topic was automatically closed 7 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.