Mutate flag for overlapping date periods in pipe dplyr & R

user_17256461 0
Viewed 32

I have two periods that I want to add a flag for:

  • 30th of January to 1st of June, 2019
  • 1st of May to 1st of July, 2019

However, when I used case when, it won't flag both periods where they overlap. I understand why, but how can I do this? I would like to simply duplicate the rows in the overlapping period and flag one as the first period, and one as the second.

Here's some dummy data with some wrangling:

dummy <- data.frame(
  dates = seq(as.Date("2019-01-01"), as.Date("2021-01-01"), by = "days"),
  people = runif(732, min = 100, max = 15000)
)


dummy %>% 
  mutate(
    periods = case_when(
      dates >= as.Date("2019-01-30") & dates <= as.Date("2019-06-01") ~ "Period_1",
      dates >= as.Date("2019-05-01") & dates <= as.Date("2019-07-01") ~ "Period_2",
      T ~ "No_period"
    )
  )
dplyr r
2 Answers

Make another case for overlapping periods then use separate_rows may helps

library(tidyr)
library(dplyr)
dummy <- data.frame(
  dates = seq(as.Date("2019-04-29"), as.Date("2019-06-02"), by = "days"),
  people = runif(35, min = 100, max = 15000)
)


dummy %>% 
  mutate(
    periods = case_when(
      dates >= as.Date("2019-01-30") & dates < as.Date("2019-05-01") ~ "Period_1",
      dates > as.Date("2019-06-01") & dates <= as.Date("2019-07-01") ~ "Period_2",
      dates >= as.Date("2019-05-01") & dates <= as.Date("2019-06-01") ~ "Period_1,Period_2",
      T ~ "No_period"
    )
  )  %>%
  separate_rows(periods, sep = ",")

   dates      people periods 
   <date>      <dbl> <chr>   
 1 2019-04-29 11719. Period_1
 2 2019-04-30  8064. Period_1
 3 2019-05-01 11154. Period_1
 4 2019-05-01 11154. Period_2
 5 2019-05-02  9699. Period_1
 6 2019-05-02  9699. Period_2
 7 2019-05-03  2650. Period_1
 8 2019-05-03  2650. Period_2
 9 2019-05-04 11504. Period_1
10 2019-05-04 11504. Period_2
user_167291750

a data.table approach

library(data.table)
# set dummy to data.table forfmat
setDT(dummy)
# create data.tale with periods
periods <- data.table(period = paste0("period_",1:2),
                      from  = as.Date(c("2019-01-30", "2019-05-01")),
                      to = as.Date(c("2019-06-01", "2019-07-01")))
# non-equi left join
final <- periods[dummy, on = .(from <= dates, to >= dates)]
user_63562780
Related