CODEWITHSUNDEEP
rdatedplyr
TimL
TimL

Reputation: 231

Find if a date overlaps between multiple pairs of vectorised dates

I'm trying to find whether a date exists between multiple pairs of dates which are wide in my dataset - the length I've given here is just an example, the eventual number may be larger or smaller. Not sure if this is the most sensible option but working longwise didn't seem to work, this is also a very common way to work with overlapping dates and date pairs in SPSS, where you can have multiple variables numerised as the dates are here and it works through each numbered 'set' to give you a response.

Here is an example dataset:

  person   key_date 1_end_date 2_end_date 3_end_date 4_end_date 1_start_date 2_start_date 3_start_date 4_start_date
1      1 2019-09-30 2019-05-23 2019-09-30 2016-07-22       <NA>   2019-05-23   2019-09-30   2016-07-22         <NA>
2      2 2019-06-07 2019-05-16 2019-06-07       <NA>       <NA>   2019-05-16         <NA>         <NA>         <NA>
3      3 2020-03-09 2016-06-02 2019-08-09 2020-05-27 2020-02-12   2016-06-02   2019-08-09   2020-05-27   2020-03-09

test <- structure(list(person = 1:3, key_date = structure(c(18169, 18054,18330), class = "Date"), `1_end_date` = structure(c(18039, 18032,16954), class = "Date"), `2_end_date` = structure(c(18169, 18054,18117), class = "Date"), `3_end_date` = structure(c(17004, NA,18409), class = "Date"), `4_end_date` = structure(c(NA, NA, 18304), class = "Date"), `1_start_date` = structure(c(18039, 18032,16954), class = "Date"), `2_start_date` = structure(c(18169,NA, 18117), class = "Date"), `3_start_date` = structure(c(17004,NA, 18409), class = "Date"), `4_start_date` = structure(c(NA,NA, 18330), class = "Date")), row.names = c(NA, 3L), class = "data.frame")

The expected output would be just a binary flag to indicate that the key_date exists between any pair of start_date and end_date. In the example given, that would mean person 1 and 3. Any ideas how to do this? Is this really inefficient?

Upvotes: 2

Views: 89

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26238

tidyverse approach

library(tidyverse)
result <- test %>% mutate(across(ends_with("end_date"), ~ 
                         key_date <= . & key_date >= get(str_replace(cur_column(), "end", "start")),
                       .names = '{.col}_flag')) %>%
  rowwise() %>%
  mutate(Flag1 = sum(c_across(ends_with("flag")), na.rm = T)) %>%
  ungroup() %>%
  select(-ends_with("flag"))

> result$Flag1
[1] 1 0 0

Complete output will look like

> result
# A tibble: 3 x 11
  person key_date   `1_end_date` `2_end_date` `3_end_date` `4_end_date` `1_start_date` `2_start_date` `3_start_date` `4_start_date` Flag1
   <int> <date>     <date>       <date>       <date>       <date>       <date>         <date>         <date>         <date>         <dbl>
1      1 2019-09-30 2019-05-23   2019-09-30   2016-07-22   NA           2019-05-23     2019-09-30     2016-07-22     NA                 1
2      2 2019-06-07 2019-05-16   2019-06-07   NA           NA           2019-05-16     NA             NA             NA                 0
3      3 2020-03-09 2016-06-02   2019-08-09   2020-05-27   2020-02-12   2016-06-02     2019-08-09     2020-05-27     2020-03-09         0

Upvotes: 3

Related Questions