CODEWITHSUNDEEP
rdata.table
Rob Richmond
Rob Richmond

Reputation: 875

data.table roll join not working correctly

I'm trying to roll join two data.table's. Here's an example:

library(data.table)

tmp1 <- data.table(structure(list(Code = c("AED", "AED", "AED", "AED", "AED"),
                                  Date = structure(c(97286400, 97372800, 97459200, 97545600, 97632000),
                                                   class = c("POSIXct", "POSIXt"), tzone = "UTC")),
                             .Names = c("Code", "Date"), row.names = c(NA, -5L), class = "data.frame"))

tmp2 <- data.table(structure(list(Date = structure(c(97286400, 99705600, 102297600), tzone = "UTC",
                                                   class = c("POSIXct", "POSIXt")),
                                  Val = c(4.39, 3.96, 3.9474), Code = c("AED", "AED", "AED")),
                             .Names = c("Date", "Val", "Code"), row.names = c(NA, -3L), class = "data.frame"))

> tmp1
   Code       Date
1:  AED 1973-01-31
2:  AED 1973-02-01
3:  AED 1973-02-02
4:  AED 1973-02-03
5:  AED 1973-02-04

> tmp2
         Date    Val Code
1: 1973-01-31 4.3900  AED
2: 1973-02-28 3.9600  AED
3: 1973-03-30 3.9474  AED

> setkey(tmp1,Code,Date)

> setkey(tmp2,Code,Date)

> tmp2[tmp1,roll=TRUE]
         Date  Val Code
1: 1973-01-31 4.39  AED
2: 1973-02-01 4.39  AED
3: 1973-02-02 4.39  AED
4: 1973-02-03 4.39  AED
5: 1973-02-04 4.39  AED

> tmp2[tmp1,roll=2]
         Date  Val Code
1: 1973-01-31 4.39  AED
2: 1973-02-01   NA  AED
3: 1973-02-02   NA  AED
4: 1973-02-03   NA  AED
5: 1973-02-04   NA  AED

The first roll works correctly. In the second example, I would expect 4.39 to be carried forward to 1973-02-02, as per the documentation: "When roll is a positive number, this limits how far values are carried forward." I'd expect to see:

> tmp2[tmp1,roll=2]
         Date  Val Code
1: 1973-01-31 4.39  AED
2: 1973-02-01 4.39  AED
3: 1973-02-02 4.39  AED
4: 1973-02-03   NA  AED
5: 1973-02-04   NA  AED

Is this a bug or am I misinterpreting the functionality?

Upvotes: 1

Views: 295

Answers (1)

mathematical.coffee
mathematical.coffee

Reputation: 56905

You're interpreting it fine. The reason is that your date is POSIXct so the roll number is in seconds, not days. Set your roll to 2 days, in seconds:

class(tmp1$Date)
> class(tmp1$Date)
[1] "POSIXct" "POSIXt"
> tmp2[tmp1, roll=2*3600*24]
         Date  Val Code
1: 1973-01-31 4.39  AED
2: 1973-02-01 4.39  AED
3: 1973-02-02 4.39  AED
4: 1973-02-03   NA  AED
5: 1973-02-04   NA  AED

Or coerce your Date via Date:=as.Date(Date) and use roll=2, depending on your preference.

Upvotes: 3

Related Questions