R语言：基于Group和Type分组查找序列中的下一个未来日期

原始数据集 #

df1 <- data_frame(date = c("2021-01-01", "2021-01-03", "2021-01-05", "2021-01-01", "2021-01-02", "2021-01-03", "2021-01-02", "2021-01-04", "2021-01-06"),
                 group = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
                 type = c("blue", "blue", "blue", "green", "green", "red", "yellow", "blue", "purple"))

需求说明 #

针对每个group与type的组合，为每行识别该组合是否存在当前日期之后的日期，新增future_date列：若存在后续日期，填入该组合的下一个日期；若无后续日期，填入NA，最终生成如下目标数据集：

df1 <- data_frame(date = c("2021-01-01", "2021-01-03", "2021-01-05", "2021-01-01", "2021-01-02", "2021-01-03", "2021-01-02", "2021-01-04", "2021-01-06"),
                 group = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
                 type = c("blue", "blue", "blue", "green", "green", "red", "yellow", "blue", "purple"),
                 future_date = c("2021-01-03", "2021-01-05", NA, "2021-01-02", NA, NA, NA, NA, NA))

解决方案 #

使用dplyr包的分组与lead()函数即可实现，步骤如下：

1. 将date列转换为日期格式，确保日期比较逻辑准确；
2. 按group和type分组，聚焦同一组合内的日期序列；
3. 用lead()函数提取每组内当前行的下一个日期，赋值给future_date。

完整代码：

library(dplyr)

# 生成目标数据集
df_result <- df1 %>%
  mutate(date = as.Date(date)) %>%
  group_by(group, type) %>%
  mutate(future_date = lead(date)) %>%
  ungroup() %>%
  # 将日期转回字符格式，与目标数据集格式匹配
  mutate(across(c(date, future_date), as.character))

# 查看结果
df_result

运行后得到的结果与目标数据集完全一致。

内容的提问来源于stack exchange，提问作者Gabriel Voelcker