如何提取各医院中group 0与group 1的重叠住院时段？

需求说明 #

给定数据框df，包含studyid、hospitalname、Date1、Date2、group字段，需按hospitalname分组，提取group=0与group=1中时间区间（Date1至Date2）存在重叠的所有记录组合。

示例数据 #

library(zoo)

# 创建示例数据
df <- data.frame(
  studyid = 1:5,
  Date1 = as.yearmon(c("2020-01", "2020-03", "2020-10", "2020-07", "2020-06")),
  Date2 = as.yearmon(c("2020-02", "2020-10", "2021-02", "2020-08", "2020-10")),
  hospitalname = c("Hospital A", "Hospital A", "Hospital A", "Hospital B", "Hospital B"),
  group = c(0, 1, 0, 1, 0)
)

解决方案 #

核心逻辑：拆分不同组的数据，按医院做交叉匹配，筛选出区间重叠的组合，最后整理为目标格式。

library(dplyr)
library(zoo)

# 拆分group=0和group=1的数据并重命名字段
group0 <- df %>% filter(group == 0) %>% rename_with(~paste0(., "_0"), c(studyid, Date1, Date2))
group1 <- df %>% filter(group == 1) %>% rename_with(~paste0(., "_1"), c(studyid, Date1, Date2))

# 按医院交叉连接，筛选区间重叠的组合
result <- group0 %>%
  inner_join(group1, by = "hospitalname") %>%
  # 时间区间重叠判断：0组的起始<=1组的结束，且0组的结束>=1组的起始
  filter(Date1_0 <= Date2_1 & Date2_0 >= Date1_1) %>%
  # 按期望顺序排列字段
  select(studyid.0, Date1_0, Date2_0, studyid.1, Date1_1, Date2_1, hospitalname)

# 查看结果
result

结果验证 #

运行代码后输出与期望结果一致：

#   studyid.0 Date1_0 Date2_0 studyid.1 Date1_1 Date2_1 hospitalname
# 1         3 Nov 20  Feb 21         2 Mar 20  Oct 20  Hospital A
# 2         5 Jul 20  Oct 20         4 Aug 20  Sep 20  Hospital B

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