Group and merge rows by ID when there are identical start and end date fields in R columns

I have a dataset with ID, Start_date and End_date fields. Some records in this dataset have an end date that matches the start date for the same ID, but in a different row. For instance, an ID could have dates 2002-01-20 to 2002-01-22 and another record on a different row with 2002-01-22 to 2002-01-23. I wish to merge those records together so I have only one record for that ID that has the start date from the first record (2002-01-20) and the end date from the second record (2002-01-23).

Example original data:

structure(list(ID = c(565, 898, 521, 522, 522, 323, 887, 887, 
                      522), Start_date = structure(c(12846, 19172, 19341, 19495, 19497, 
                                                     19495, 14194, 14204, 18786), class = "Date"), End_date = structure(c(12847, 
                                                                                                                          19174, 19347, 19497, 19499, 19497, 14203, 14206, 18798), class = "Date")), row.names = c(NA, 
                                                                                                                                                                                                                   -9L), class = c("tbl_df", "tbl", "data.frame"))

Example final data:

structure(list(ID = c(565, 898, 521, 522, 323, 887, 887, 522), 
               Start_date = structure(c(12846, 19172, 19341, 19495, 19495, 
                                        14194, 14204, 18786), class = "Date"), End_date = structure(c(12847, 
                                                                                                      19174, 19347, 19499, 19497, 14203, 14206, 18798), class = "Date")), row.names = c(NA, 
                                                                                                                                                                                        -8L), class = c("tbl_df", "tbl", "data.frame"))

There seem to be a number of sql solutions on stackoverflow for this problem, but I wasn't able to locate one using R. Thank you.

First, we can use an inner_join to join the dataset to itself, and keep only the rows with the same ID and matching Start/End dates:

library(tidyverse)

dat <- structure(list(ID = c(565, 898, 521, 522, 522, 323, 887, 887, 
                             522), Start_date = structure(c(12846, 19172, 19341, 19495, 19497, 
                                                            19495, 14194, 14204, 18786), class = "Date"), End_date = structure(c(12847, 
                                                                                                                                 19174, 19347, 19497, 19499, 19497, 14203, 14206, 18798), class = "Date")), row.names = c(NA, 
                                                                                                                                                                                                                          -9L), class = c("tbl_df", "tbl", "data.frame"))
processed <- structure(list(ID = c(565, 898, 521, 522, 323, 887, 887, 522), 
                            Start_date = structure(c(12846, 19172, 19341, 19495, 19495, 
                                                     14194, 14204, 18786), class = "Date"), End_date = structure(c(12847, 
                                                                                                                   19174, 19347, 19499, 19497, 14203, 14206, 18798), class = "Date")), row.names = c(NA, 
                                                                                                                                                                                                     -8L), class = c("tbl_df", "tbl", "data.frame"))


dat |>
  inner_join(dat,
            by = c("ID", "End_date" = "Start_date"))
#> # A tibble: 1 × 4
#>      ID Start_date End_date   End_date.y
#>   <dbl> <date>     <date>     <date>    
#> 1   522 2023-05-18 2023-05-20 2023-05-22

We rename the columns to keep the correct End_date only:

dat |>
  inner_join(dat,
            by = c("ID", "End_date" = "Start_date")) |>
  select(ID, Start_date, End_date = End_date.y)

Then we do the same operation, with an anti_join to remove these rows. Because we want to remove both rows (the one with the Start date, and the one with the End date), we need to do it twice:

dat |>
  anti_join(dat,
             by = c("ID", "End_date" = "Start_date")) |>
  anti_join(dat,
            by = c("ID", "Start_date" = "End_date"))
#> # A tibble: 8 × 3
#>      ID Start_date End_date  
#>   <dbl> <date>     <date>    
#> 1   565 2005-03-04 2005-03-05
#> 2   898 2022-06-29 2022-07-01
#> 3   521 2022-12-15 2022-12-21
#> 4   323 2023-05-18 2023-05-20
#> 5   887 2008-11-11 2008-11-20
#> 6   887 2008-11-21 2008-11-23
#> 7   522 2021-06-08 2021-06-20

And we're done, we can just combine these two:

combined <- dat |>
  inner_join(dat,
            by = c("ID", "End_date" = "Start_date")) |>
  select(ID, Start_date, End_date = End_date.y)


res <- dat |>
  anti_join(dat,
             by = c("ID", "End_date" = "Start_date")) |>
  anti_join(dat,
            by = c("ID", "Start_date" = "End_date")) |>
  bind_rows(combined)


all.equal(arrange(res, ID, Start_date),
          arrange(processed, ID, Start_date))
#> [1] TRUE

library(dplyr)

df |> 
  mutate(End_date = as.Date(ifelse(End_date %in% Start_date, max(End_date), End_date)),
         .by = ID) |>
  distinct(ID, End_date, .keep_all = T)

How it works

1. Within an ID (e.g., .by = ID), we find rows where the End_date is also the Start_date in another row.
2. When that condition is met then within that ID and for that row, we set End_date to be the max date. The max date returns the most recent chronological date. When this operation is done End_date is now duplicated in two rows for a given ID.
3. Lastly, we can simply remove that row using distinct which will keep the first row with unique values in ID and End_date.

Note: .by is experimental and new as of packageVersion("dplyr") 1.1.0. If you are using an older version of dplyr then you would do something like:

df |>
  group_by(ID) |>
  mutate(...) |> # ... same code as above
  ungroup() |>
  distinct(...) # ... same code as above

Output

     ID Start_date End_date  
  <dbl> <date>     <date>    
1   565 2005-03-04 2005-03-05
2   898 2022-06-29 2022-07-01
3   521 2022-12-15 2022-12-21
4   522 2023-05-18 2023-05-22
5   323 2023-05-18 2023-05-20
6   887 2008-11-11 2008-11-20
7   887 2008-11-21 2008-11-23
8   522 2021-06-08 2021-06-20

One warning is the following scenario where you have a matching Start_date and End_date within an ID, but an additional non-matching row with a more recent date (the third row):

df2 <- data.frame(ID = '522',
           Start_date = as.Date(c("2023-05-18", "2023-05-20", "2023-05-25")),
           End_date = as.Date(c("2023-05-20", "2023-05-22", "2023-05-28")))

You could then do something like:

df2 |> 
  mutate(End_date = as.Date(ifelse(End_date %in% Start_date, End_date[which(Start_date %in% End_date)], End_date)),
         .by = ID) |>
  distinct(ID, End_date, .keep_all = T)

I ended up solving this using the following code, which accounted for a few nuances in my data, including (1) wanting to retain additional variables from my original dataset associated with the records (2) being able to account for instances where there may be 3 or 4 records that I wished to link together; and (3) the fact that IDs can and do repeat in my dataset, some which I wanted merged, while others I wanted to remain separate because they didn't meet my criteria (same end/start dates).

raw_data <- raw_data %>%
  arrange(ID, START_DATE)

raw_data <- raw_data %>%
  group_by(ID) %>%
  mutate(Combined = ifelse(!is.na(lag(END_DATE)) & START_DATE == lag(END_DATE), TRUE, FALSE)) %>%
  ungroup()

multiples <- raw_data %>%  
  mutate(Group = cumsum(!Combined)) %>%
  group_by(Group) %>%
  filter(n() > 1) %>%
  ungroup()

singles <- raw_data %>%  
  mutate(Group = cumsum(!Combined)) %>%
  group_by(Group) %>%
  filter(n() == 1) %>%
  ungroup() %>%
  select(-Combined)

multiples <- multiples %>%  
  group_by(ID, Group) %>%
  summarize(START_DATE = first(START_DATE),
            END_DATE = last(END_DATE)) %>%
  ungroup()

raw_data <- raw_data %>%
  select(-START_DATE, -END_DATE, -Combined)

#This step is so I can reintroduce the additional variables of interest back into my dataset, after I combined the records I wanted combined.
multiples <- multiples %>%
  left_join(raw_data, by = "ID")

#This deduplicates my records after the left join, leaving only a single record for each
multiples <- setDT(multiples)[order(ID, Group), .SD[1], by=list(ID, Group)]

final_dataset <- rbind(multiples, singles)