Find nearest preceding and following dates between data frames

Question

I have the following two data frames:

df1 <- data.frame(ID = c("A","A","B","B","C","D","D","D","E"),
             Date = as.POSIXct(c("2018-04-12 08:56:00","2018-04-13 11:03:00","2018-04-14 14:30:00","2018-04-15 03:10:00","2018-04-16 07:28:00","2018-04-17 11:17:00","2018-04-17 14:21:00","2018-04-18 09:56:00","2018-05-02 07:49:00")))

df2 <- data.frame(ID = c("A","A","A","B","C","D","D","D","D","D","E"),
              Date = as.POSIXct(c("2018-04-10 07:11:00","2018-04-11 18:59:00","2018-04-12 12:37:00","2018-04-15 01:43:00","2018-04-21 09:52:00","2018-04-15 20:25:00","2018-04-17 12:33:00","2018-04-17 14:21:00","2018-04-18 10:59:00","2018-04-20 14:11:00","2018-05-01 09:50:00")))

For df1, I would like to do 2 things: First, I want to find the nearest preceding date, by ID, from df2. Second, I want to find the nearest following date, by ID, from df2, again without repeating values. In both cases, I do not want dates from df2 to be repeated in df1.

Using the roll = Inf feature from the data.table package I am able to merge in the preceding dates by ID.

setDT(df1)
setDT(df2)

setkey(df1, ID, Date)
setkey(df2, ID, Date)[, PrecedingDate:=Date]

result <- df2[df1, roll=Inf]

I'm unsure of how I can pull the nearest following date from df2 into df1, and how I can ensure that dates are not repeated.

The result should be as follows:

result <- data.frame(ID = c("A","A","B","B","C","D","D","D","E"),
                     Date = as.POSIXct(c("2018-04-12 08:56:00","2018-04-13 11:03:00","2018-04-14 14:30:00","2018-04-15 03:10:00","2018-04-16 07:28:00","2018-04-17 11:17:00","2018-04-17 14:21:00","2018-04-18 09:56:00","2018-05-02 07:49:00")),
                     PrecedingDate = as.POSIXct(c("2018-04-11 18:59:00","2018-04-12 02:37:00",NA,"2018-04-15 01:43:00",NA,"2018-04-15 20:25:00","2018-04-17 14:21:00",NA,"2018-05-01 09:50:00")),
                     FollowingDate = as.POSIXct(c("2018-04-12 02:37:00",NA,"2018-04-15 01:43:00",NA,"2018-04-21 09:52:00","2018-04-17 12:33:00","2018-04-17 14:21:00","2018-04-18 10:59:00",NA)))

Any help here would be most appreciated.

what happens if df2 has the same date as df1? Is it classified as preceding or following or ignored? — Shree
In those instances, it should be classified as only preceding. — Dfeld
The 2nd PrecedingDate and the 1st FollowingDate in result are incorrect imo. They should be both 2018-04-12 12:37:00. I've corrected that in my answer. — Jaap

Shree Shree · Accepted Answer · 2018-10-13T22:34:21

Here's a solution using dplyr. You might get some warnings for min max functions but you can safely ignore or suppress them.

library(dplyr)

closest_to_zero <- function(x) {
  neg <- which(x == max(x[x < 0]))
  pos <- which(x == min(x[x > 0]))
  c(previous = neg, following = pos)
}

result <- left_join(df1, df2, by = "ID") %>%
  group_by(ID, Date.x) %>%
  mutate(
    time_diff = Date.y - Date.x,
    Preceding = Date.y[closest_to_zero(time_diff)["previous"]],
    Following = Date.y[closest_to_zero(time_diff)["following"]]
  ) %>%
  distinct(ID, Date.x, Preceding, Following)

# A tibble: 9 x 4
# Groups:   ID, Date.x [9]
  ID    Date.x              Preceding           Following          
  <fct> <dttm>              <dttm>              <dttm>             
1 A     2018-04-12 08:56:00 2018-04-11 18:59:00 2018-04-12 12:37:00
2 A     2018-04-13 11:03:00 2018-04-12 12:37:00 NA                 
3 B     2018-04-14 14:30:00 NA                  2018-04-15 01:43:00
4 B     2018-04-15 03:10:00 2018-04-15 01:43:00 NA                 
5 C     2018-04-16 07:28:00 NA                  2018-04-21 09:52:00
6 D     2018-04-17 11:17:00 2018-04-15 20:25:00 2018-04-17 12:33:00
7 D     2018-04-17 14:21:00 2018-04-17 12:33:00 2018-04-18 10:59:00
8 D     2018-04-18 09:56:00 2018-04-17 14:21:00 2018-04-18 10:59:00
9 E     2018-05-02 07:49:00 2018-05-01 09:50:00 NA

Find nearest preceding and following dates between data frames

2 Answers