Comparing dates across different data frames and return output

user_17639640 0
Viewed 53

I have two data frames with different data for the same people. Data frame 1 (dfx) has unique ids and dates people had appointments and data frame 2 has unique ids and a start and end date.

It looks something like below:

c1 <- c("1", "1", "1", "1", "1", "2", "2", "2", "2", "2")
d1 <- c("2017", "2018", "2019", "2020", "2021", "2019", "2019", "2019", "2020", "2021")

dfx <- data.frame(c1,d1)

c2 <- c("1", "1", "2")
ds <- c("2017", "2020", "2017")
de <- c("2018", "2021", "2018")

dfy <- data.frame(c2,ds,de)

I'm working with data frame 2 and I want to know if dates in data frame 1 is within the start and end dates in data frame 2. I am trying to get an output in dfy saying TRUE or FALSE for overlap.

For this example, the output should return TRUE, TRUE, FALSE.

I've tried working with this on dplyr and not getting the result I'm looking. I'll appreciate any help. Thanks.

dplyr code:

overlap <- dfy %>%
  group_by(c2) %>%
  mutate (on_hold = any(mapply(function(id, start, end) any(id == dfx$c1 & dfx$d1 > start & dfx$d1 < end), c2, ds, de))) %>%
  arrange(c2, ds, de, on_hold)
dplyr if-statement r date
1 Answers

solution

ranges <- dfx %>%
  group_by(c1) %>%
  summarise(range = list(unique(d1)))

left_join(dfy, ranges, by = c("c2" = "c1")) %>%
  rowwise() %>%
  mutate(in_range = ds %in% range & de %in% range)

output

# A tibble: 3 x 5
# Rowwise: 
  c2    ds    de    range     in_range
  <fct> <fct> <fct> <list>    <lgl>   
1 1     2017  2018  <fct [5]> TRUE    
2 1     2020  2021  <fct [5]> TRUE    
3 2     2017  2018  <fct [3]> FALSE

data as provided by OP

c1 <- c("1", "1", "1", "1", "1", "2", "2", "2", "2", "2")
d1 <- c("2017", "2018", "2019", "2020", "2021", "2019", "2019", "2019", "2020", "2021")

dfx <- data.frame(c1,d1)

c2 <- c("1", "1", "2")
ds <- c("2017", "2020", "2017")
de <- c("2018", "2021", "2018")

dfy <- data.frame(c2,ds,de)
user_104157490
Related