如何用dplyr按reference分组匹配Sent状态act_id的前置有效状态creation_date

用dplyr可以通过分组标记+向下填充的方式高效实现需求，步骤清晰且性能不错，适合处理大规模数据：

library(dplyr)
library(tidyr) # 需要用到fill函数

result <- data %>%
  # 按reference分组，组内务必按creation_date排序（保证时间顺序正确）
  group_by(reference) %>%
  arrange(creation_date, .by_group = TRUE) %>%
  # 只给符合条件的行保留creation_date，其他行设为NA
  mutate(prev_valid_date = ifelse(status %in% c("Automatic", "Received"), creation_date, NA)) %>%
  # 向下填充NA，让每个Sent行继承前面最近的有效日期
  fill(prev_valid_date, .direction = "down") %>%
  # 只保留status为Sent的行（如果需要保留所有行可去掉这一步）
  filter(status == "Sent") %>%
  ungroup()

print(result)

代码解释 #

• arrange(creation_date, .by_group = TRUE)：确保每个分组内的记录严格按时间先后排序，这是找"之前"记录的核心前提
• mutate(prev_valid_date = ...)：标记出所有有效状态的日期，非有效状态的行暂时设为NA
• fill(prev_valid_date, .direction = "down")：把前面的有效日期向下填充到后续的NA行，这样每个Sent行就自动拿到了最近的前置有效日期
• 最后用filter筛选出Sent记录，得到目标结果

验证结果 #

运行代码后输出如下：

# A tibble: 2 × 6
  creation_date       end_date           status reference act_id prev_valid_date     
  <dttm>              <dttm>             <chr>  <chr>      <dbl> <dttm>             
1 2023-05-03 00:00:00 2023-05-08 00:00:00 Sent   A              3 2023-05-02 00:00:00
2 2023-05-04 00:00:00 2023-05-09 00:00:00 Sent   B              4 NA                 

• reference为A的Sent记录（act_id=3），拿到了前面最近的Received状态日期2023-05-02
• reference为B的Sent记录（act_id=4），因前面无有效状态记录，prev_valid_date为NA，符合逻辑

关于效率 #

这个方法用的都是dplyr的矢量化操作，比sapply这类循环型操作效率高很多，即使处理十万级数据也不会有明显卡顿。

内容的提问来源于stack exchange，提问作者Antonio Y