How to conditionally insert rows into a data frame with R?

library(dplyr, warn.conflicts = FALSE)

df = read.table(text = 'ID  Product MaxDate Day
100 candy   2022-01-18  Tue
100 chips   2022-01-18  Tue
101 candy   2022-01-18  Tue
101 chips   2022-01-18  Tue
102 candy   2022-01-18  Tue
103 candy   2022-01-13  Wed
103 chips   2022-01-13  Tue', header = T) %>%
  mutate(MaxDate = as.Date(MaxDate))


df %>% 
  left_join(tibble(Day = 'Tue', lagged_days = 2:0)) %>% 
  mutate(MaxDate = MaxDate - coalesce(lagged_days, 0),
         lagged_days = NULL)
#> Joining, by = "Day"
#>     ID Product    MaxDate Day
#> 1  100   candy 2022-01-16 Tue
#> 2  100   candy 2022-01-17 Tue
#> 3  100   candy 2022-01-18 Tue
#> 4  100   chips 2022-01-16 Tue
#> 5  100   chips 2022-01-17 Tue
#> 6  100   chips 2022-01-18 Tue
#> 7  101   candy 2022-01-16 Tue
#> 8  101   candy 2022-01-17 Tue
#> 9  101   candy 2022-01-18 Tue
#> 10 101   chips 2022-01-16 Tue
#> 11 101   chips 2022-01-17 Tue
#> 12 101   chips 2022-01-18 Tue
#> 13 102   candy 2022-01-16 Tue
#> 14 102   candy 2022-01-17 Tue
#> 15 102   candy 2022-01-18 Tue
#> 16 103   candy 2022-01-13 Wed
#> 17 103   chips 2022-01-11 Tue
#> 18 103   chips 2022-01-12 Tue
#> 19 103   chips 2022-01-13 Tue

^{Created on 2022-01-18 by the reprex package (v2.0.1)}

There's probably a more elegant way if you needed to generalize this, but this is quick and gets the job done:

bind_rows(
    df,
    df %>% filter(Day == "Tue") %>% mutate(MaxDate = MaxDate - 1),
    df %>% filter(Day == "Tue") %>% mutate(MaxDate = MaxDate - 2)
  ) %>%
  arrange(ID, MaxDate, Product)
#     ID Product    MaxDate Day
# 1  100   candy 2022-01-16 Tue
# 2  100   chips 2022-01-16 Tue
# 3  100   candy 2022-01-17 Tue
# 4  100   chips 2022-01-17 Tue
# 5  100   candy 2022-01-18 Tue
# 6  100   chips 2022-01-18 Tue
# 7  101   candy 2022-01-16 Tue
# 8  101   chips 2022-01-16 Tue
# 9  101   candy 2022-01-17 Tue
# 10 101   chips 2022-01-17 Tue
# 11 101   candy 2022-01-18 Tue
# 12 101   chips 2022-01-18 Tue
# 13 102   candy 2022-01-16 Tue
# 14 102   candy 2022-01-17 Tue
# 15 102   candy 2022-01-18 Tue
# 16 103   candy 2022-01-11 Tue
# 17 103   chips 2022-01-11 Tue
# 18 103   candy 2022-01-12 Tue
# 19 103   chips 2022-01-12 Tue
# 20 103   candy 2022-01-13 Tue
# 21 103   chips 2022-01-13 Tue

Using this reproducible data:

df = read.table(text = 'ID  Product MaxDate Day
100 candy   2022-01-18  Tue
100 chips   2022-01-18  Tue
101 candy   2022-01-18  Tue
101 chips   2022-01-18  Tue
102 candy   2022-01-18  Tue
103 candy   2022-01-13  Tue
103 chips   2022-01-13  Tue', header = T) %>%
  mutate(MaxDate = as.Date(MaxDate))

How to conditionally insert rows into a data frame with R?

Related

Recent Posts