Join tables by date range

Question

I am looking for simple method to join two tables by date range. 1 table contains exact date, another table contains two variables identifying beginning and ending of the time period. I need to join tables if date in first table is withing range from second table.

data1 <- data.table(date = c('2010-01-21', '2010-01-25', '2010-02-02', '2010-02-09'),
                name = c('id1','id2','id3','id4'))


data2 <- data.table(beginning=c('2010-01-15', '2010-01-23', '2010-01-30', '2010-02-05'), 
                ending = c('2010-01-22','2010-01-29','2010-02-04','2010-02-13'),
                class = c(1,2,3,4))

result <- data.table(date = c('2010-01-21', '2010-01-25', '2010-02-02', '2010-02-09'),
                 beginning=c('2010-01-15', '2010-01-23', '2010-01-30', '2010-02-05'), 
                 ending = c('2010-01-22','2010-01-29','2010-02-04','2010-02-13'),
                 name = c('id1','id2','id3','id4'),
                 class = c(1,2,3,4))

Any help please? I found few difficult examples but they don't even work on my data because of formats. I need something like:

select * from data1
left join
select * from data2
where data2.beginning <= data1.date <= data2.ending

Thanks

did you try the `sqldf` package? – rrs May 30 '14 at 16:32 — rrs, May 30 '14 at 16:32

score 10 · Accepted Answer · edited Jun 02 '20 at 10:37

I know the following looks horrible in base, but here's what I came up with. It's better to use the 'sqldf' package (see below).

library(data.table)
data1 <- data.table(date = c('2010-01-21', '2010-01-25', '2010-02-02', '2010-02-09'),
                    name = c('id1','id2','id3','id4'))


data2 <- data.table(beginning=c('2010-01-15', '2010-01-23', '2010-01-30', '2010-02-05'), 
                    ending = c('2010-01-22','2010-01-29','2010-02-04','2010-02-13'),
                    class = c(1,2,3,4))

result <- cbind(data1,"beginning"=sapply(1:nrow(data2),function(x) data2$beginning[data2$beginning[x]<data1$date & data2$ending[x]>data1$date]),
            "ending"=sapply(1:nrow(data2),function(x) data2$ending[data2$beginning[x]<data1$date & data2$ending[x]>data1$date]),
            "class"=sapply(1:nrow(data2),function(x) data2$class[data2$beginning[x]<data1$date & data2$ending[x]>data1$date]))

Using the package sqldf:

library(sqldf)
result = sqldf("select * from data1
                left join data2
                on data1.date between data2.beginning and data2.ending")

Using data.table this is simply

data1[data2, on = .(date >= beginning, date <= ending)]

Thanks for this. Is there a way to avoid writing `.` when performing range join using `data.table` approach? `on = .(date >= beginning, date <= ending)` `devtools::check` gets annoyed with this dot and I cannot bypass it. — Death Metal, Aug 06 '20 at 19:32
For the `data.table` example, the result will rename `beginning` and `ending` to `date` and `date.1`. How do I keep the correct column names, while also including the original `date` column from `data1` ? — Olsgaard, Apr 19 '22 at 08:58

Join tables by date range

1 Answers1

Linked