R语言按时间匹配合并动物收容所入所与出所数据集的方法

核心逻辑 #

所有出所记录都对应时间更早的同ID入所记录，同一只动物的多次出入记录按时间先后顺序一一对应，因此核心操作逻辑如下：

• 分别对入所表、出所表按Animal.ID分组，组内按时间升序排序后生成组内访问序号
• 以入所表为左表，按Animal.ID+组内访问序号为匹配键左连接出所表，即可得到目标结果

实现代码（tidyverse通用方案） #

library(dplyr)

# 示例数据集
intakes <- data.frame(
  Animal.ID = c("A1","A2","A1","A3","A4","A5"),
  Intake.Date = as.Date(c("2016-01-01","2017-01-01","2018-01-01","2019-01-01","2020-01-01","2021-01-01")),
  Intake.Type = c("Surrender","Stray","Surrender","Stray","Seized","Surrender")
)

outcomes <- data.frame(
  Animal.ID = c("A1","A2","A1","A3","A5"),
  Outcome.Date = as.Date(c("2016-06-30","2017-06-30","2018-06-30","2019-06-30","2021-06-30")),
  Outcome.Type = c("Adoption","Euthanasia","Transfer","Adoption","Transfer")
)

# 生成入所记录的组内序号
intakes_ordered <- intakes %>%
  group_by(Animal.ID) %>%
  arrange(Intake.Date, .by_group = TRUE) %>%
  mutate(visit_num = row_number()) %>%
  ungroup()

# 生成出所记录的组内序号
outcomes_ordered <- outcomes %>%
  group_by(Animal.ID) %>%
  arrange(Outcome.Date, .by_group = TRUE) %>%
  mutate(visit_num = row_number()) %>%
  ungroup()

# 合并得到结果
combined <- intakes_ordered %>%
  left_join(outcomes_ordered, by = c("Animal.ID", "visit_num")) %>%
  select(-visit_num) # 删除辅助生成的序号列

输出结果 #

打印combined即可得到要求的合并格式：

Animal.ID Intake.Date Intake.Type Outcome.Date Outcome.Type
1        A1  2016-01-01   Surrender   2016-06-30     Adoption
2        A1  2018-01-01   Surrender   2018-06-30     Transfer
3        A2  2017-01-01       Stray   2017-06-30   Euthanasia
4        A3  2019-01-01       Stray   2019-06-30     Adoption
5        A4  2020-01-01      Seized         <NA>         <NA>
6        A5  2021-01-01   Surrender   2021-06-30     Transfer

如果需要按入所时间升序排列结果，可在最后一步追加%>% arrange(Intake.Date)。

注意事项 #

• 该方案完全兼容日期时间格式的业务数据，仅需要将排序字段替换为对应的时间列即可，逻辑无需调整
• 运行前请确保数据无异常值（如出所时间早于同ID对应入所时间）

内容的提问来源于stack exchange，提问作者Alex C.