如何进行data.table的滚动连接？

Question

我有两个数据表需要合并。一个是公司市场价值随时间的数据，另一个是公司股息历史随时间的数据。我想知道每个季度每家公司支付了多少钱，并将该值放在市场价值数据旁边。

library(magrittr)
library(data.table)
library(zoo)
library(lubridate)

set.seed(1337)
# data table of company  market values
companies <- 
    data.table(companyID = 1:10,
               Sedol = rep(c("91772E", "7A662B"), each = 5),
               Date = (as.Date("2005-04-01") + months(seq(0, 12, 3))) - days(1),
               MktCap = c(100 + cumsum(rnorm(5,5)),
                          50 + cumsum(rnorm(5,1,5)))) %>%
    setkey(Sedol, Date)

# data table of dividends
dividends <- 
    data.table(DivID = 1:7,
               Sedol = c(rep('91772E', each = 4), rep('7A662B', each = 3)),
               Date = as.Date(c('2004-11-19', '2005-01-13', '2005-01-29',
                                '2005-10-01', '2005-06-29', '2005-06-30',
                                '2006-04-17')),
               DivAmnt = rnorm(7, .8, .3)) %>%
    setkey(Sedol, Date)

我相信这是一个可以使用data.table滚动连接的情况，类似于：

dividends[companies, roll = "nearest"]

为了尝试获取类似于数据集的结果，请按照以下格式进行操作：

       DivID  Sedol       Date   DivAmnt companyID    MktCap
    1:    NA 7A662B       <NA>        NA         6  61.21061
    2:     5 7A662B 2005-06-29 0.7772631         7  66.92951
    3:     6 7A662B 2005-06-30 1.1815343         7  66.92951
    4:    NA 7A662B       <NA>        NA         8  78.33914
    5:    NA 7A662B       <NA>        NA         9  88.92473
    6:    NA 7A662B       <NA>        NA        10  87.85067
    7:     2 91772E 2005-01-13 0.2964291         1 105.19249
    8:     3 91772E 2005-01-29 0.8472649         1 105.19249
    9:    NA 91772E       <NA>        NA         2 108.74579
   10:     4 91772E 2005-10-01 1.2467408         3 113.42261
   11:    NA 91772E       <NA>        NA         4 120.04491
   12:    NA 91772E       <NA>        NA         5 124.35588

请注意，我已经按照季度精确匹配了股息与公司市值。(note that I've matched the dividends to the company market values by the exact quarter)

但我不确定如何执行它。 CRAN的pdf对roll是什么数字或应该是什么数字相当含混不清（您可以传递日期吗？数字是否量化向前推进的天数？ 观察次数的数量？）。而且改变rollends并不能让我得到想要的结果。

最终，我把股息日期映射到它们的季度结束，并在其上进行连接。这是一个好的解决方案，但如果我最终需要知道如何执行滚动连接，则没有用。在您的回答中，您能否描述需要滚动连接的情况，并帮助我理解如何执行它们？(In your answer, could you describe a situation where rolling joins are the only solution as well as help me understand how to perform them?)

- jks612

---

你能描述一下你正在尝试做什么吗？ - mtoto

你的代码输出的数据表有误，能否提供companies的dput()呢？ - Jaap

我忘记写上 library(lubridate) 的声明了。感谢你发现了这个问题。 - jks612

谢谢。现在已添加答案。希望有所帮助。 - Jaap

Answer 1

如果您不想使用滚动连接，可以使用data.table的foverlaps函数进行重叠连接：

# create an interval in the 'companies' datatable
companies[, `:=` (start = compDate - days(90), end = compDate + days(15))]
# create a second date in the 'dividends' datatable
dividends[, Date2 := divDate]

# set the keys for the two datatable
setkey(companies, Sedol, start, end)
setkey(dividends, Sedol, divDate, Date2)

# create a vector of columnnames which can be removed afterwards
deletecols <- c("Date2","start","end")

# perform the overlap join and remove the helper columns
res <- foverlaps(companies, dividends)[, (deletecols) := NULL]

结果：

> res
     Sedol DivID    divDate   DivAmnt companyID   compDate    MktCap
 1: 7A662B    NA       <NA>        NA         6 2005-03-31  61.21061
 2: 7A662B     5 2005-06-29 0.7772631         7 2005-06-30  66.92951
 3: 7A662B     6 2005-06-30 1.1815343         7 2005-06-30  66.92951
 4: 7A662B    NA       <NA>        NA         8 2005-09-30  78.33914
 5: 7A662B    NA       <NA>        NA         9 2005-12-31  88.92473
 6: 7A662B    NA       <NA>        NA        10 2006-03-31  87.85067
 7: 91772E     2 2005-01-13 0.2964291         1 2005-03-31 105.19249
 8: 91772E     3 2005-01-29 0.8472649         1 2005-03-31 105.19249
 9: 91772E    NA       <NA>        NA         2 2005-06-30 108.74579
10: 91772E     4 2005-10-01 1.2467408         3 2005-09-30 113.42261
11: 91772E    NA       <NA>        NA         4 2005-12-31 120.04491
12: 91772E    NA       <NA>        NA         5 2006-03-31 124.35588

---

与此同时，data.table的作者们引入了非等连接（v1.9.8）。您也可以使用它来解决这个问题。使用非等连接，您只需要：

companies[, `:=` (start = compDate - days(90), end = compDate + days(15))]
dividends[companies, on = .(Sedol, divDate >= start, divDate <= end)]

获取预期结果。

---

使用的数据（与问题中相同，但不包括创建键）：

set.seed(1337)
companies <- data.table(companyID = 1:10, Sedol = rep(c("91772E", "7A662B"), each = 5),
                        compDate = (as.Date("2005-04-01") + months(seq(0, 12, 3))) - days(1),
                        MktCap = c(100 + cumsum(rnorm(5,5)), 50 + cumsum(rnorm(5,1,5))))
dividends <- data.table(DivID = 1:7, Sedol = c(rep('91772E', each = 4), rep('7A662B', each = 3)),
                        divDate = as.Date(c('2004-11-19','2005-01-13','2005-01-29','2005-10-01','2005-06-29','2005-06-30','2006-04-17')),
                        DivAmnt = rnorm(7, .8, .3))

- Jaap

---

滚动连接在什么情况下更为适用？文档似乎表明这就是滚动连接被创建的原因。 - jks612

@jks612 我会再次研究这个问题。我记得滚动连接没有给出期望的结果，但我会再次查看它。希望我能在这个周末解决它。 - Jaap

在 setkey(dividends, Sedol, dDate, Date2) 中，dDate 在你的回答中没有定义。我想你可能是指 divDate。建议你进行更正。 - FXQuantTrader

@FXQuantTrader 这是一个打字错误，应该是 divDate（已经修复）。谢谢！现在还添加了一种非等值连接的替代方法。 - Jaap

非等值连接是如此强大...感谢您添加示例。 - denis