基于不同起始日期关联宽表并筛选后续30个事件的实现方法

问题说明 #

现有两个数据库：

1. BaseDB：包含字段id、Main_event_date，数据如下：

id  Main_event_date
1   01/01/2017
2   01/07/2018
3   01/11/2017

2. DB_2：包含字段id、event_date，同一id对应多条事件记录，数据如下：

id  event_date
1   01/02/2017
1   19/12/2017
1   19/01/2018
2   01/10/2018
2   10/01/2019

已通过reshape将DB_2转换为宽表DB_2_wide，包含字段id、event_date0至event_date100。需要将BaseDB与DB_2_wide关联，仅保留每个id在Main_event_date之后的前30个事件，最终生成包含id、t1至t30的Final_Table。直接用left_join或merge会混入Main_event_date之前的事件列（值为NA），需解决该问题。

解决方案 #

方法一：先处理长表再转宽表（推荐） #

优先在长表阶段完成过滤和筛选，再转换为宽表，流程更高效：

library(dplyr)
library(tidyr)

# 统一转换日期格式为日期类型（避免字符串比较误差）
BaseDB <- BaseDB %>% mutate(Main_event_date = as.Date(Main_event_date, format = "%d/%m/%Y"))
DB_2 <- DB_2 %>% mutate(event_date = as.Date(event_date, format = "%d/%m/%Y"))

# 关联两张表，过滤出主事件日期后的记录，按id分组排序后取前30条
filtered_events <- DB_2 %>%
  left_join(BaseDB, by = "id") %>%
  filter(event_date > Main_event_date) %>%
  group_by(id) %>%
  arrange(event_date, .by_group = TRUE) %>%
  slice(1:30) %>%
  mutate(event_num = paste0("t", row_number())) %>% # 生成t1-t30的列名标识
  ungroup()

# 转换为宽表，同时保留BaseDB中所有id（无符合条件事件的id对应列值为NA）
Final_Table <- filtered_events %>%
  select(id, event_num, event_date) %>%
  pivot_wider(names_from = event_num, values_from = event_date) %>%
  right_join(BaseDB %>% select(id), by = "id")

方法二：基于已生成的DB_2_wide处理 #

若已存在宽表DB_2_wide，可先转回长表处理，再重新转宽：

library(dplyr)
library(tidyr)

# 转换日期格式
BaseDB <- BaseDB %>% mutate(Main_event_date = as.Date(Main_event_date, format = "%d/%m/%Y"))
DB_2_wide <- DB_2_wide %>%
  mutate(across(starts_with("event_date"), ~as.Date(., format = "%d/%m/%Y")))

# 将宽表转回长表，去除空值记录
DB_2_long <- DB_2_wide %>%
  pivot_longer(cols = starts_with("event_date"), names_to = "event_col", values_to = "event_date") %>%
  filter(!is.na(event_date))

# 过滤、筛选前30条并转宽
filtered_events <- DB_2_long %>%
  left_join(BaseDB, by = "id") %>%
  filter(event_date > Main_event_date) %>%
  group_by(id) %>%
  arrange(event_date, .by_group = TRUE) %>%
  slice(1:30) %>%
  mutate(event_num = paste0("t", row_number())) %>%
  ungroup()

Final_Table <- filtered_events %>%
  select(id, event_num, event_date) %>%
  pivot_wider(names_from = event_num, values_from = event_date) %>%
  right_join(BaseDB %>% select(id), by = "id")

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