使用dplyr进行滚动连接

Question

最近有人在推特上说dplyr现在支持不等式连接（"滚动连接"），但CRAN上的版本没有提及。如有指引，请告知。

- Frank Harrell

---

我会询问推文作者。在dplyr的github上，我没有看到任何暗示表明这种情况。 - MrFlick

Answer 1

滚动连接现在在dplyr 1.1.0中通过join_by得到支持。

使用following和preceding来查找特定观测/日期之后或之前的观测。使用>=，>，<=，<来匹配多个观测（之前或之后）。

sales <- tibble(id = c(1L, 1L, 1L, 2L, 2L),
                sale_date = as.Date(c("2018-12-31", "2019-01-02", "2019-01-05", "2019-01-04", "2019-01-01")))

promos <- tibble(id = c(1L, 1L, 2L),
                 promo_date = as.Date(c("2019-01-01", "2019-01-05", "2019-01-02")))

left_join(sales, promos, join_by(id, following(sale_date, promo_date)))
#left_join(sales, promos, join_by(id, sale_date <= promo_date))
# A tibble: 5 × 3
     id sale_date  promo_date
  <int> <date>     <date>    
1     1 2018-12-31 2019-01-01
2     1 2019-01-02 2019-01-05
3     1 2019-01-05 2019-01-05
4     2 2019-01-04 NA        
5     2 2019-01-01 2019-01-02

left_join(sales, promos, join_by(id, sale_date <= promo_date))
# A tibble: 6 × 3
     id sale_date  promo_date
  <int> <date>     <date>    
1     1 2018-12-31 2019-01-01
2     1 2018-12-31 2019-01-05
3     1 2019-01-02 2019-01-05
4     1 2019-01-05 2019-01-05
5     2 2019-01-04 NA        
6     2 2019-01-01 2019-01-02

left_join(sales, promos, join_by(id, preceding(sale_date, promo_date)))
# A tibble: 5 × 3
     id sale_date  promo_date
  <int> <date>     <date>    
1     1 2018-12-31 NA        
2     1 2019-01-02 2019-01-01
3     1 2019-01-05 2019-01-05
4     2 2019-01-04 2019-01-02
5     2 2019-01-01 NA        

- Maël

---

与data.table相比，tidyverse仍需要发明大量单独的函数来完成任务。 - Frank Harrell

它据说提供更多的灵活性（例如，请参见我的编辑） - Maël

Answer 2

据我所知，在dplyr中没有这样的单个函数，但是假设您所指的是在SQL中显示的这种复杂连接，对于任何行i，它计算那些时间点在Time[i]-2和Time[i]之间的行的平均需求。

library(sqldf)
sqldf("select a.Time, a.demand demand, avg(b.demand) mean_demand 
       from BOD a join BOD b on b.Time between a.Time - 2 and a.Time
       group by a.Time")

如果要这样做，可以使用dplyr和tidyr（尽管它有一个显著的缺点，即形成完整的n x n交叉连接，然后将其过滤掉）。

library(dplyr)
library(tidyr)
BOD %>% 
    expand(., ., .) %>% 
    group_by(Time, demand) %>% 
    filter(Time1 <= Time & Time1 >= Time-2) %>% 
    summarize(mean_demand = mean(demand1)) %>%
    ungroup

我们也可以使用 zoo::rollapplyr 来实现。这里的 Avg 是一个函数，它接受行号向量 ix 并返回那些时间至少为 max(Time[ix])-2 的输入行的 demand 的平均值。我们使用该函数在行号上进行 rollapplyr。

library(zoo)
Avg <- function(ix) with(BOD[ix, ], mean(demand[Time >= max(Time) - 2]))
transform(BOD, Avg = rollapplyr(1:nrow(BOD), 3, Avg, partial = TRUE))

或者，如果您只想获取最近的3行，而不考虑它们的时间戳，那么这更容易：

transform(BOD, Avg = rollapplyr(demand, 3, mean, partial = TRUE))

另外，关于data.table中的滚动连接，请参见此链接。

- G. Grothendieck

---

对于非整洁数据方法，我更喜欢 data.table，我发现它在完整的滚动连接中既优雅又快速。例如：http://data.vanderbilt.edu/fh/attach/rolljoin.r - Frank Harrell

我发现在滚动连接方面，SQL方法更加清晰。例如，将您链接中的d代码的清晰度与此SQL代码进行比较：d <- sqldf("select d1.*, d2.pos from d2 left join d1 on d1.x = d2.x and d2.pos between d1.start and d1.end") 此外，SQL代码可以处理更大的输入，因为可以指定在内存之外执行计算。 - G. Grothendieck

很棒的答案。您能指出这里 expand(., ., .) 中的点是做什么用的吗？我尝试查找文档，但无法弄清楚。 - ashleych

点（dot）是指输入，此处为 BOD，因此与 expand(BOD, BOD, BOD) 相同。 - G. Grothendieck