1
$\begingroup$

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

$\endgroup$
1
  • $\begingroup$ Check out the dplyr package. Particularly the function 'left_join'. $\endgroup$
    – ralph
    May 15 at 20:56
1
$\begingroup$

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

```
$\endgroup$
2
  • $\begingroup$ Thank you so much! This is great! $\endgroup$ May 25 at 15:33
  • $\begingroup$ Thank you for sharing this. Although I am a big fan of data.table, I didn't know about this. $\endgroup$ Jul 14 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.