r - 在 R 中按最接近的日期连接两个数据框

Question

我有两个不同长度的数据框。两个数据框都包含日期。我想在不过去的情况下加入最近的日期列。例如“2019-01-05”为日期，两个选项分别为“2019-01-06”和“2019-01-02”，则与“2019-01-02”相连。我尝试使用 data.table 包中的 roll "inf"但我没有得到想要的结果。这是我想按日期加入的 2 个玩具数据框。

我还想按代码列对连接进行分组。在 tidyverse 中，代码类似于:

df1 %>% group_by(ticker) %>% inner_join(df2, by = "Date") #but choose the closest date without going over.


df1 = data.frame(ticker = c("AAPL", "AAPL", "MSFT", "MSFT"), date = c("2019-01-06", "2019-02-06", "2019-01-06", "2019-05-02"))

df2 = data.frame(ticker = c("AAPL", "AAPL", "AAPL", "MSFT", "MSFT", "MSFT"), date = c("2019-01-03", "2019-01-07" , "2019-02-06", "2019-01-05", "2019-01-07", "2019-05-02"), randomVar = rnorm(6))

print(df1)
 ticker       date
1   AAPL 2019-01-06
2   AAPL 2019-02-06
3   MSFT 2019-01-06
4   MSFT 2019-05-02


print(df2)
 ticker       date  randomVar
1   AAPL 2019-01-03 -0.5321493
2   AAPL 2019-01-07 -0.7909461
3   AAPL 2019-02-06  0.2121993
4   MSFT 2019-01-05  1.2336315
5   MSFT 2019-01-07 -0.2729354
6   MSFT 2019-05-02 -0.5349596

我想创建 df3，它是 df1 和 df2 之间的连接。日期键列只能是 df1 日期列中的那些。

在这种情况下，我们的新 df3 应该看起来完全像这样。

 print(df3)

 ticker       date   randomVar
1   AAPL 2019-01-06  -0.5321493
2   AAPL 2019-02-06   0.2121993
3   MSFT 2019-01-06   1.2336315
4   MSFT 2019-05-02   -0.5349596

Answer 1

这可以在 SQL 中完成，默认的 SQLite 后端在 ticker 和 df2 日期小于或等于 df1 日期时使用左连接，然后对 df1 进行分组，并从 df2 中获取连接到 df1 的最大日期。

library(sqldf)
sqldf("select df1.*, max(df2.date), df2.randomVar from df1 
  left join df2 on df1.ticker = df2.ticker and df1.date >= df2.date
  group by df1.rowid
  order by df1.rowid")[-3]

给予:

  ticker       date  randomVar
1   AAPL 2019-01-06 -0.5321493
2   AAPL 2019-02-06  0.2121993
3   MSFT 2019-01-06  1.2336315
4   MSFT 2019-05-02 -0.5349596

注意事项

以可重现的形式输入:

Lines1 <- "ticker       date
1   AAPL 2019-01-06
2   AAPL 2019-02-06
3   MSFT 2019-01-06
4   MSFT 2019-05-02"


Lines2 <- "ticker       date  randomVar
1   AAPL 2019-01-03 -0.5321493
2   AAPL 2019-01-07 -0.7909461
3   AAPL 2019-02-06  0.2121993
4   MSFT 2019-01-05  1.2336315
5   MSFT 2019-01-07 -0.2729354
6   MSFT 2019-05-02 -0.5349596"

df1 <- read.table(text = Lines1, as.is = TRUE)
df2 <- read.table(text = Lines2, as.is = TRUE)