Values changed when joining in data.table

Question

I have the following two dataframes (dput below):

> df1
  group       date
1     A 2023-01-10
2     A 2023-01-15
3     B 2023-01-09
4     B 2023-01-12
> df2
  group      date1      date2 value
1     A 2023-01-09 2023-01-11     2
2     B 2023-01-11 2023-01-14     3

I would like to join df2 to df1 based on if date falls between date1 and date2 using data.table. When I run the following code I get the following output:

library(data.table)
setDT(df1)
setDT(df2)
df2[df1, 
    .(group, date, date1, date2, value),
    on = .(group, date1 <= date, date2 >= date)]
#>    group       date      date1      date2 value
#> 1:     A 2023-01-10 2023-01-10 2023-01-10     2
#> 2:     A 2023-01-15 2023-01-15 2023-01-15    NA
#> 3:     B 2023-01-09 2023-01-09 2023-01-09    NA
#> 4:     B 2023-01-12 2023-01-12 2023-01-12     3

^{Created on 2023-01-11 with reprex v2.0.2}

This is almost the desired output, but the values of date1 and date2 are now the same as date while I would like them to be the same as the were in df2. So the desired output should look like this:

#>    group       date      date1      date2 value
#> 1:     A 2023-01-10 2023-01-09 2023-01-11     2
#> 2:     A 2023-01-15         NA         NA    NA
#> 3:     B 2023-01-09         NA         NA    NA
#> 4:     B 2023-01-12 2023-01-11 2023-01-14     3

So I was wondering if anyone knows how to keep the origin values of df2 when joining in data.table?

dput data:

df1 <- structure(list(group = c("A", "A", "B", "B"), date = structure(c(1673305200, 
1673737200, 1673218800, 1673478000), class = c("POSIXct", "POSIXt"
), tzone = "")), class = "data.frame", row.names = c(NA, -4L))

df2 <- structure(list(group = c("A", "B"), date1 = structure(c(1673218800, 
1673391600), class = c("POSIXct", "POSIXt"), tzone = ""), date2 = structure(c(1673391600, 
1673650800), class = c("POSIXct", "POSIXt"), tzone = ""), value = c(2, 
3)), class = "data.frame", row.names = c(NA, -2L))

Probably the canonical PR where the background of your issue is discussed: [SQL-like column return for non-equi and rolling joins](https://github.com/Rdatatable/data.table/pull/2706) (The PR is marked as closed, but I reckon it's "just" because it's transferred to [Both columns for rolling and non-equi joins](https://github.com/Rdatatable/data.table/pull/3093)). — Henrik, Jan 11 '23 at 17:30

score 1 · Answer 1 · answered Jan 11 '23 at 13:30

Not a direct answer, but I prefer to use foverlaps for range merges:

#add start, end dates same as dates in df2
df1[, c("date1", "date2") := date]

#key both datasets
setkey(df1, group, date1, date2)
setkey(df2, group, date1, date2)

foverlaps(df1, df2)[, .(group, date, date1, date2, value) ]
#    group                date               date1               date2 value
# 1:     A 2023-01-09 23:00:00 2023-01-08 23:00:00 2023-01-10 23:00:00     2
# 2:     A 2023-01-14 23:00:00                <NA>                <NA>    NA
# 3:     B 2023-01-08 23:00:00                <NA>                <NA>    NA
# 4:     B 2023-01-11 23:00:00 2023-01-10 23:00:00 2023-01-13 23:00:00     3

If we remove the columns subset bit, it might explain whey we get date1, date2 filled in from df1:

foverlaps(df1, df2)
#    group               date1               date2 value                date
# 1:     A 2023-01-08 23:00:00 2023-01-10 23:00:00     2 2023-01-09 23:00:00
# 2:     A                <NA>                <NA>    NA 2023-01-14 23:00:00
# 3:     B                <NA>                <NA>    NA 2023-01-08 23:00:00
# 4:     B 2023-01-10 23:00:00 2023-01-13 23:00:00     3 2023-01-11 23:00:00
#                i.date1             i.date2
# 1: 2023-01-09 23:00:00 2023-01-09 23:00:00
# 2: 2023-01-14 23:00:00 2023-01-14 23:00:00
# 3: 2023-01-08 23:00:00 2023-01-08 23:00:00
# 4: 2023-01-11 23:00:00 2023-01-11 23:00:00

Waldi · Accepted Answer · 2023-01-11T19:07:35.893

are you simply looking for x.date1?

Columns of x can be referred to using the prefix x. and is particularly useful during joining to refer to x's join columns as they are otherwise masked by i's. For example, X[Y, .(x.a-i.a, b), on="a"]

df2[df1, 
    .(group, date, x.date1, x.date2, value),
    on = .(group, date1 <= date, date2 >= date)]

    group       date    x.date1    x.date2 value
   <char>     <POSc>     <POSc>     <POSc> <num>
1:      A 2023-01-10 2023-01-09 2023-01-11     2
2:      A 2023-01-15       <NA>       <NA>    NA
3:      B 2023-01-09       <NA>       <NA>    NA
4:      B 2023-01-12 2023-01-11 2023-01-14     3

Values changed when joining in data.table

2 Answers2