Calculate each overlapping date ranges from two independent databases in r

user_13538376 0
Viewed 160

I have two independent two databases, one contains followup data (start date and end date). As follows:

> data1 <- data.frame("ID" = c(1,1,1,1,2,2,2), "FUstart" = c("2019-01-01", "2019-04-01", "2019-07-01", "2019-10-01", "2019-04-01", "2019-07-01", "2019-10-01"), "FUend" = c("2019-03-31", "2019-06-30", "2019-09-30", "2019-12-31", "2019-06-30", "2019-09-30", "2019-12-31"))
> data1
  ID    FUstart      FUend
1  1 2019-01-01 2019-03-31
2  1 2019-04-01 2019-06-30
3  1 2019-07-01 2019-09-30
4  1 2019-10-01 2019-12-31
5  2 2019-04-01 2019-06-30
6  2 2019-07-01 2019-09-30
7  2 2019-10-01 2019-12-31

Another contains drug use data (also start date and end date). As follows:

> data2 <- data.frame("ID" = c(1,1,1,2), "Drugstart" = c("2019-01-11", "2019-03-26", "2019-06-26", "2019-03-20"), "Drugend" = c("2019-01-20", "2019-04-05", "2019-10-05", "2019-10-10"))
> data2
  ID  Drugstart    Drugend
1  1 2019-01-11 2019-01-20
2  1 2019-03-26 2019-04-05
3  1 2019-06-26 2019-10-05
4  2 2019-03-20 2019-10-10

The two databases are linked by "ID". The problem is that the rows for each ID may not be the same. I would like to calculate overlapping days and add it into the data1. I would expect to have the following results:

> data1
  ID    FUstart      FUend Overlapping.Days
1  1 2019-01-01 2019-03-31               16
2  1 2019-04-01 2019-06-30               10
3  1 2019-07-01 2019-09-30               92
4  1 2019-10-01 2019-12-31                5
5  2 2019-04-01 2019-06-30               91
6  2 2019-07-01 2019-09-30               92
7  2 2019-10-01 2019-12-31               10

Note that data1 is the basic database. And adds data2's overlapping days into data1. Many many thanks for helping~~

date-range r
1 Answers

An option using data.table::foverlaps:

foverlaps(data1, data2)[, 
    sum(1L + pmin(Drugend, FUend) - pmax(Drugstart, FUstart)), 
    .(ID, FUstart, FUend)]

output and I am also getting slightly diff numbers from OP's expected output:

   ID    FUstart      FUend V1
1:  1 2019-01-01 2019-03-31 16
2:  1 2019-04-01 2019-06-30 10
3:  1 2019-07-01 2019-09-30 92
4:  1 2019-10-01 2019-12-31  5
5:  2 2019-04-01 2019-06-30 91
6:  2 2019-07-01 2019-09-30 92
7:  2 2019-10-01 2019-12-31 10

data:

library(data.table)
setDT(data1)
cols <- paste0("FU", c("start","end"))
data1[, (cols) := lapply(.SD, as.IDate, format="%Y-%m-%d"), .SDcols=cols]
setkeyv(data1, c("ID", cols))

#too lazy to generalize and hence copy paste
setDT(data2)
cols <- paste0("Drug", c("start","end"))
data2[, (cols) := lapply(.SD, as.IDate, format="%Y-%m-%d"), .SDcols=cols]
setkeyv(data2, c("ID", cols))
user_19894800
Related