I have multiple rows with the same ID, and date ranges for each row. Sometimes, these date ranges overlap. I need to identify rows where they overlap.
EG Dataset:
eg_data <- data.frame(
id = c(1,1,1, 2,2, 3,3,3,3,3,3, 4,4, 5,5,5,5),
start_dt = c("01/01/2016", "12/02/2016", "03/12/2017", "02/01/2016",
"08/12/2016", "01/01/2016", "03/05/2016", "05/07/2016", "07/01/2016",
"09/04/2016", "10/10/2016", "01/01/2016", "05/28/2016", "01/01/2016",
"06/05/2016", "08/25/2016", "11/01/2016"),
end_dt = c("12/01/2016", "03/14/2017", "05/15/2017", "05/15/2016",
"12/29/2016", "03/02/2016", "04/29/2016", "06/29/2016", "08/31/2016",
"09/25/2016", "11/29/2016", "05/31/2016", "08/19/2016", "06/10/2016",
"07/25/2016", "08/29/2016", "12/30/2016"))
eg_data$row_n <- 1:nrow(eg_data)
Run the eg data above, and you see that the
start date for row 3 overlaps the end date of row 2 for ID #1; start date for row 13 overlaps the end date of row 12 for ID #4; and start date for row 15 overlaps the end date of row 14 for ID #5.
I need to be able to identify when this type of overlaps happens, for a single ID number.
First convert the dates to Date class. Then a self join on id and the intersection criteria will join all relevant overlapping rows. overlap is 1 if that row has an overlap and 0 otherwise. overlaps lists the row numbers of the overlaps for that row. We used row numbers rowid but we could replace each occurrence of it in the code below with row_n if desired.
library(sqldf)
fmt <- "%m/%d/%Y"
eg2 <- transform(eg_data,
start_dt = as.Date(start_dt, fmt),
end_dt = as.Date(end_dt, fmt))
sqldf("select
a.*,
count(b.rowid) > 0 as overlap,
coalesce(group_concat(b.rowid), '') as overlaps
from eg2 a
left join eg2 b on a.id = b.id and
not a.rowid = b.rowid and
((a.start_dt between b.start_dt and b.end_dt) or
(b.start_dt between a.start_dt and a.end_dt))
group by a.rowid
order by a.rowid")
giving:
id start_dt end_dt row_n overlap overlaps
1 1 2016-01-01 2016-12-01 1 0
2 1 2016-12-02 2017-03-14 2 1 3
3 1 2017-03-12 2017-05-15 3 1 2
4 2 2016-02-01 2016-05-15 4 0
5 2 2016-08-12 2016-12-29 5 0
6 3 2016-01-01 2016-03-02 6 0
7 3 2016-03-05 2016-04-29 7 0
8 3 2016-05-07 2016-06-29 8 0
9 3 2016-07-01 2016-08-31 9 0
10 3 2016-09-04 2016-09-25 10 0
11 3 2016-10-10 2016-11-29 11 0
12 4 2016-01-01 2016-05-31 12 1 13
13 4 2016-05-28 2016-08-19 13 1 12
14 5 2016-01-01 2016-06-10 14 1 15
15 5 2016-06-05 2016-07-25 15 1 14
16 5 2016-08-25 2016-08-29 16 0
17 5 2016-11-01 2016-12-30 17 0