r - 在聚合时按最近的前一个日期合并两个数据帧

Question

我正在尝试按最近的前一个日期按组匹配两个数据集。
因此，在一个组中，当第一个数据集的日期是第二个数据集的日期或之前的最近日期时，我想将第二个数据集 (d2) 的变量添加到第一个 (d1) 的变量中。如果第二个数据集中的两行与第一个数据集中的一行匹配，我想添加较大的值。 (按组，d1 中的日期总是少于 d2 中的日期)

这是一个例子，希望能让它更清楚

 d1 = data.frame(id=c(1,1,1,2,2), 
                 ref=as.Date(c("2013-12-07", "2014-12-07", "2015-12-07", "2013-11-07", "2014-11-07" )))
 d1
#   id        ref
# 1  1 2013-12-07
# 2  1 2014-12-07
# 3  1 2015-12-07
# 4  2 2013-11-07
# 5  2 2014-11-07

 d2 = data.frame(id=c(1,1,2),                
                 date=as.Date(c("2014-05-07","2014-12-05", "2015-11-05")),
                 x1 = factor(c(1,2,2), ordered = TRUE), 
                 x2 = factor(c(2, NA ,2), ordered=TRUE))
 d2
#   id    date x1   x2
# 1  1 2014-05-07  1    2
# 2  1 2014-12-05  2 <NA>
# 3  2 2015-11-05  2    2

随着预期的结果

 output = data.frame(id=c(1,1,1,2,2),
                     ref=as.Date(c("2013-12-07", "2014-12-07", "2015-12-07", "2013-11-07", "2014-11-07" )),
                     x1 = c(2, NA, NA, NA, 2),
                     x2 = c(2, NA, NA, NA, 2))
 output
#   id        ref x1 x2
# 1  1 2013-12-07  2  2
# 2  1 2014-12-07 NA NA
# 3  1 2015-12-07 NA NA
# 4  2 2013-11-07 NA NA
# 5  2 2014-11-07  2  2

例如，d2 的前两个观察，id=1，日期 "2014-05-07","2014-12-05" , 匹配到较早的日期 "2013-12-07"在 d1。由于有两行与 d1 中的一行匹配，
然后选择最高级别。

我可以通过循环以下计算在基础 R 中做到这一点
每个小组，但我希望更有效的东西。
我很想看到 data.table 方法(但我仅限于 R v3.1 和 data.table v1.9.4)。谢谢

真实数据集:
d1:行 1M/100K 组
d2:行 11K/4K 组

# for one group
x = d1[d1$id==1, ]
y = d2[d2$id==1, ]
id = apply(outer(x$ref, y$date, "-"), 2, which.min)
temp = cbind(y, ref=x$ref[id])
# aggregate variables by ref
temp = merge(aggregate(x1 ~ ref, data=temp, max),
aggregate(x2 ~ ref, data=temp, max)
)
merge(x, temp, all=T)

ps:我看过 How to match by nearest date from two data frames?和 Join data.table on exact date or if not the case on the nearest less than date没有成功。

Answer 1

您可以使用 dplyr 执行此操作:

d2$ind <- 0
library(dplyr)
out <- d1 %>% full_join(d2,by=c("id","ref"="date")) %>%
              arrange(id,ref) %>%
              mutate(ind=cumsum(ifelse(is.na(ind),1,ind))) %>%
              group_by(ind) %>% 
              summarise(ref=min(ref),x1=max(x1,na.rm=TRUE),x2=max(x2,na.rm=TRUE))
### A tibble: 5 x 4
##    ind        ref     x1     x2
##  <dbl>     <date> <fctr> <fctr>
##1     1 2013-12-07      2      2
##2     2 2014-12-07     NA     NA
##3     3 2015-12-07     NA     NA
##4     4 2013-11-07     NA     NA
##5     5 2014-11-07      2      2

我们首先在 d2 中添加一列指标并将它们设置为零。然后，我们在 d1 之间执行全外连接。和 d2 . d1 中的那些行将有 ind的 NA .我们按 id 排序和 ref (即日期)，我们替换 NA ind 的条目与 1并执行 cumsum .这导致:

  id        ref   x1   x2 ind
1  1 2013-12-07 <NA> <NA>   1
2  1 2014-05-07    1    2   1
3  1 2014-12-05    2 <NA>   1
4  1 2014-12-07 <NA> <NA>   2
5  1 2015-12-07 <NA> <NA>   3
6  2 2013-11-07 <NA> <NA>   4
7  2 2014-11-07 <NA> <NA>   5
8  2 2015-11-05    2    2   5

由此我们可以很容易地看出，我们可以按 ind 分组。和 summarise适本地得到你的结果。