Comparing dates across different data frames and return output

Question

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)

Merijn van Tilborg · Accepted Answer · 2022-02-08 16:14:35Z

1

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)

answered Feb 8, 2022 at 16:14

Merijn van Tilborg

5,9801 gold badge10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparing dates across different data frames and return output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related