Creating a new date variable that is on the same day of the week, within the same month, and year as original date variable in r

Question

I need to create a new variable "controldates" from a date variable "casedates". This new variable is going to consist of dates that are on the same day of the week as the casedate, within the same month and year as the case date. For example if I have a case date on the 3rd Wednesday of July my control days will be the first 1st Wednesday of July, the second Wednesday of July, and the 4th Wednesday of July. Additionally, I would like to create an indicator variable for each group of dates that are created. I would like to do this using dplyr in r.

Starting data:

Casedate
 "01-03-2015"
 "08-27-2017"
 "10-23-2019"

This is how I would like it to look

Casedate          Controldate      Index
"01-03-2015"      "01-03-2015"       1
"01-03-2015"      "01-10-2015"       1
"01-03-2015"      "01-17-2015"       1
"01-03-2015"      "01-24-2015"       1
"01-03-2015"      "01-31-2015"       1
"08-12-2017"      "08-05-2017"       2
"08-12-2017"      "08-12-2017"       2
"08-12-2017"      "08-19-2017"       2
"08-12-2017"      "08-26-2017"       2
"10-23-2019"      "10-02-2019"       3
"10-23-2019"      "10-09-2019"       3
"10-23-2019"      "10-16-2019"       3
"10-23-2019"      "10-23-2019"       3
"10-23-2019"      "10-30-2019"       3

Answer 1

Here is an option with tidyverse. Convert the 'Casedate' to Date class with lubridate, then loop over the elements with map, create a sequence of dates in a list, unnest the list column

library(dplyr)
library(purrr)
library(lubridate)
df1 %>% 
   mutate(Index = row_number(), 
      Casedate = mdy(Casedate), 
     wd = wday(Casedate, label = TRUE), 
     Controldate = map2(floor_date(Casedate, 'month'), wd, ~ {
   x1 <- seq(.x, length.out = 7, by = '1 day')
    seq(x1[wday(x1, label = TRUE) == .y],
       ceiling_date(.x, 'month'), by = '7 day')})) %>% 
    unnest(c(Controldate)) %>%
    select(Casedate, Controldate, Index)

-output

# A tibble: 14 x 3
#   Casedate   Controldate Index
#   <date>     <date>      <int>
# 1 2015-01-03 2015-01-03      1
# 2 2015-01-03 2015-01-10      1
# 3 2015-01-03 2015-01-17      1
# 4 2015-01-03 2015-01-24      1
# 5 2015-01-03 2015-01-31      1
# 6 2017-08-27 2017-08-06      2
# 7 2017-08-27 2017-08-13      2
# 8 2017-08-27 2017-08-20      2
# 9 2017-08-27 2017-08-27      2
#10 2019-10-23 2019-10-02      3
#11 2019-10-23 2019-10-09      3
#12 2019-10-23 2019-10-16      3
#13 2019-10-23 2019-10-23      3
#14 2019-10-23 2019-10-30      3

data

df1 <- structure(list(Casedate = c("01-03-2015", "08-27-2017", "10-23-2019"
)), class = "data.frame", row.names = c(NA, -3L))