Merge two datasets based on date ranges. R

Question

My goal is to merge two datasets using date ranges. Dataset1 contains patients stays in a hospital overtime. Dataset2 contains room information overtime. My goal is to identify what type of room the stays were in my Dataset1. It can get complicated since room type in some hospitalizations can change. For example patient 101 second hospitalization was part ICU and part Emergency.

Dataset 1

  PatientID Hospital Room           StartDate             EndDate
1       101     ODCC  4SW 2020-06-04 16:21:47 2020-06-22 15:12:39
2       101     ODCC   1W 2020-06-22 15:12:40 2020-09-08 14:03:34
3       101     ODCC   1N 2020-09-08 14:03:35 2020-10-02 06:50:24
4       101     ODCC   1W 2020-10-02 06:50:25 2020-10-05 14:25:54 

Dataset 2

  Hospital Room      Type    StartDT      EndDT
1     ODCC  11A     Other 2020-01-01 2021-05-12
2     ODCC   1W       ICU 2020-06-01 2020-07-30
3     ODCC   1W Emergency 2020-08-01 2021-05-12
4     ODCC   1N Emergency 2020-11-05 2021-02-07

My goal

  Patient.ID Hospital Room           StartDate             EndDate      Type    StartDT      EndDT
1        101     ODCC  4SW 2020-06-04 16:21:47 2020-06-22 15:12:39      <NA>       <NA>       <NA>
2        101     ODCC   1W 2020-06-22 15:12:40 2020-09-08 14:03:34       ICU 2020-06-01 2020-07-30
3        102     ODCC   1W 2020-06-22 15:12:40 2020-09-08 14:03:34 Emergency 2020-08-01 2021-05-12
4        101     ODCC   1N 2020-09-08 14:03:35 2020-10-02 06:50:24      <NA>       <NA>       <NA>
5        101     ODCC   1W 2020-10-02 06:50:25 2020-10-05 14:25:54 Emergency 2020-08-01 2021-05-12

Below you can find codes to replicate my datasets.

stays <- structure(list(
  PatientID = c(101, 101, 101, 101),
  Hospital = c("ODCC", "ODCC", "ODCC", "ODCC"),
  Room = c("4SW", "1W", "1N", "1W"), 
  StartDate = structure(c(1591287707, 1592838760, 1599573815, 1601621425),
                        class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
  EndDate = structure(c(1592838759, 1599573814, 1601621424, 1601907954), 
                      class = c("POSIXct", "POSIXt"), tzone = "UTC")),
  class = "data.frame", row.names = c(NA, -4L))

type <- structure(list(
  Hospital = c("ODCC", "ODCC", "ODCC", "ODCC"), 
  Room = c("11A", "1W", "1W", "1N"), Type = c("Other", "ICU", "Emergency",
                                              "Emergency"), 
  StartDT = structure(c(1577836800, 1590969600, 1596240000, 1604534400), 
                      class = c("POSIXct", "POSIXt"), tzone = "UTC"),
  EndDT = structure(c(1620777600, 1596067200, 1620777600, 1612656000),
                    class = c("POSIXct","POSIXt"), tzone = "UTC")), 
  class = "data.frame", row.names = c(NA, -4L))

Thank you! Marvin

Answer 1

A fast method is using foverlaps from data.table package, see ?data.table::foverlaps.

library(data.table)
stays<-data.table(stays)
type<-data.table(type)
setkeyv(type, c("Hospital", "Room", "StartDT", "EndDT"))
foverlaps(stays, type, by.x=c("Hospital", "Room", "StartDate", "EndDate"))

   Hospital Room      Type    StartDT      EndDT PatientID           StartDate             EndDate
1:     ODCC  4SW      <NA>       <NA>       <NA>       101 2020-06-04 16:21:47 2020-06-22 15:12:39
2:     ODCC   1W       ICU 2020-06-01 2020-07-30       101 2020-06-22 15:12:40 2020-09-08 14:03:34
3:     ODCC   1W Emergency 2020-08-01 2021-05-12       101 2020-06-22 15:12:40 2020-09-08 14:03:34
4:     ODCC   1N      <NA>       <NA>       <NA>       101 2020-09-08 14:03:35 2020-10-02 06:50:24
5:     ODCC   1W Emergency 2020-08-01 2021-05-12       101 2020-10-02 06:50:25 2020-10-05 14:25:54

```