0

I have two dataframes:

df1 <- data.frame("id"=c(1,1,1,2,2,2),
                  "date"=c(10, 15, 20, 5, 10, 15))

df2 <- data.frame("id"=c(1, 1, 2),
                  "startDate"=c(9, 14, 9),
                  "endDate"=c(10, 15, 10),
                  "value"=c(99, 100, 10))

I would like to merge df2 on to df1 on the id-column and the date-column such that a given date in df1 is larger than startDate or less than or equal to endDate. If the date in df1 isn't in the interval dictated by df2 for that particular id, then it should just be a NA. The resulting dataframe should be

df <- data.frame("id"=c(1,1,1,2,2,2),
                 "date"=c(10, 15, 20, 5, 10, 15),
                 "value"=c(99, 100, NA, NA, 10, NA))

In essence it is a left-join I want on id and date, but where the date of df2 is an interval.

Tyler D
  • 323
  • 1
  • 12
  • 1
    Try this non-equi join solution using `data.table` https://stackoverflow.com/a/38394201 – Ronak Shah Jan 06 '21 at 08:28
  • 1
    You can join in any order. This gives your expected output after you turn the two dataframe to datatable `df2[df1, on=.(id, startDate <= date, endDate >= date)]` – Ronak Shah Jan 06 '21 at 08:42

0 Answers0