R(dplyr)：基于医师专科筛选指定时间范围的关联住院记录

数据集说明 #

• 住院-医师数据集：每行以「住院id-医师id」为唯一标识，包含入院日期（date_start）、出院日期（date_end）及所属医院（hospital_id）。单条住院记录可关联多名医师，一名医师可在多家医院执业。
• 医师专科数据集：记录医师的专科信息，一名医师可拥有多个专科。

需求 #

针对每一行「住院-医师」记录，找出该住院入院前30天内，同一医院中，由与该医师至少共享一个专科的其他医师参与的所有住院id。

现有代码问题 #

已基于dplyr编写代码，可筛选同一医院、指定时间范围内其他医师的住院记录，但未纳入「医师共享专科」的关联逻辑，核心难点是医师可能拥有多个专科，无法通过单一变量直接筛选。

现有住院-医师数据集及处理代码 #

df <- data.frame(hospitalization_id = c(1, 2, 3,
                                        1, 2, 3,
                                        4, 5, 
                                        6, 7, 8),
                 hospital_id = c("A", "A", "A", 
                                 "A", "A", "A", 
                                 "A", "A",
                                 "B", "B", "B"),
                 physician_id = c(1, 1, 1, 
                                  2, 2, 2,
                                  3, 3, 
                                  2, 2, 2),
                 date_start = as.Date(c("2000-01-01", "2000-01-12", "2000-01-20",
                                        "2000-01-01", "2000-01-12", "2000-01-20",
                                        "2000-01-12", "2000-01-20",
                                        "2000-02-10", "2000-02-11", "2000-02-12")),
                 date_end = as.Date(c("2000-01-03", "2000-01-18", "2000-01-22",
                                      "2000-01-03", "2000-01-18", "2000-01-22",
                                      "2000-01-18", "2000-01-22",
                                      "2000-02-11", "2000-02-14", "2000-02-17")))

df2 <- df %>%
  mutate(
    # 生成当前住院入院前30天的时间区间
    date_range1 = date_start - 30,
    date_range2 = date_start - 1,
    # 筛选同一医院、时间区间内的所有住院id
    hospid_all = pmap(list(date_range1, date_range2, hospital_id),
                      function(x, y, z) filter(df,
                                               date_end >= x & date_end <= y,
                                               hospital_id == z)$hospitalization_id),
    hospid_all = lapply(hospid_all, unique),
    # 筛选同一医院、时间区间内当前医师参与的住院id
    hospid_ego = pmap(list(date_range1, date_range2, hospital_id, physician_id),
                      function(x, y, z, p) filter(df,
                                                  date_end >= x & date_end <= y,
                                                  hospital_id == z,
                                                  physician_id == p)$hospitalization_id),
    # 计算排除当前医师后的其他医师住院id
    hospid_peer = future_map2(hospid_all, hospid_ego, ~ .x[!(.x %in% .y)])) %>%
  select(-starts_with('date_'), -hospid_all, -hospid_ego) %>% # 仅保留其他医师的住院id列表
  rename('ego'='physician_id')

df3 <- df2 %>%
  select(hospitalization_id, hospital_id, ego, hospid_peer) %>%
  unnest(hospid_peer, keep_empty = TRUE)

df4 <- df3 %>%
  left_join(select(df, hospitalization_id, physician_id), 
            by=c('hospid_peer'='hospitalization_id')) %>%
  rename(alter = physician_id)

医师专科数据集代码 #

physician_spec <- data.frame(physician_id = c(1, 2, 2, 3),
                      specialty_code = c(100, 100, 200, 200))

核心思路：先构建「医师-共享专科医师」映射表，再结合时间、医院维度筛选符合条件的住院记录。

library(dplyr)
library(purrr)
library(furrr) # 使用future_map2需加载

# 1. 构建医师-共享专科医师映射表
physician_specialty_matches <- physician_spec %>%
  # 按专科分组，提取同一专科下的所有医师
  group_by(specialty_code) %>%
  mutate(peer_physicians = list(unique(physician_id))) %>%
  ungroup() %>%
  # 合并每个医师的所有共享专科医师，去重并排除自身
  group_by(physician_id) %>%
  summarise(peer_physicians = list(unique(unlist(peer_physicians)[unlist(peer_physicians) != physician_id]))) %>%
  ungroup()

# 2. 加载原始数据集
df <- data.frame(hospitalization_id = c(1, 2, 3,
                                        1, 2, 3,
                                        4, 5, 
                                        6, 7, 8),
                 hospital_id = c("A", "A", "A", 
                                 "A", "A", "A", 
                                 "A", "A",
                                 "B", "B", "B"),
                 physician_id = c(1, 1, 1, 
                                  2, 2, 2,
                                  3, 3, 
                                  2, 2, 2),
                 date_start = as.Date(c("2000-01-01", "2000-01-12", "2000-01-20",
                                        "2000-01-01", "2000-01-12", "2000-01-20",
                                        "2000-01-12", "2000-01-20",
                                        "2000-02-10", "2000-02-11", "2000-02-12")),
                 date_end = as.Date(c("2000-01-03", "2000-01-18", "2000-01-22",
                                      "2000-01-03", "2000-01-18", "2000-01-22",
                                      "2000-01-18", "2000-01-22",
                                      "2000-02-11", "2000-02-14", "2000-02-17")))

physician_spec <- data.frame(physician_id = c(1, 2, 2, 3),
                      specialty_code = c(100, 100, 200, 200))

# 3. 整合映射表并筛选符合条件的住院id
df_with_peers <- df %>%
  # 关联当前医师的共享专科医师列表
  left_join(physician_specialty_matches, by = "physician_id") %>%
  mutate(
    # 生成入院前30天的时间区间
    date_range1 = date_start - 30,
    date_range2 = date_start - 1,
    # 筛选同一医院、时间区间内，由共享专科医师参与的住院id
    matched_hosp_ids = pmap(list(date_range1, date_range2, hospital_id, peer_physicians),
                            function(x, y, z, peers) {
                              df %>%
                                filter(
                                  hospital_id == z,
                                  date_end >= x & date_end <= y,
                                  physician_id %in% peers
                                ) %>%
                                pull(hospitalization_id) %>%
                                unique()
                            })
  ) %>%
  # 保留核心字段
  select(hospitalization_id, physician_id, hospital_id, date_start, matched_hosp_ids)

# 可选：将列表格式的结果展开为每行对应一个匹配住院id
df_with_peers_expanded <- df_with_peers %>%
  unnest(matched_hosp_ids, keep_empty = TRUE)

# 查看结果
print(df_with_peers)
print(df_with_peers_expanded)

代码说明 #

• 第一步构建physician_specialty_matches：通过专科分组，解决多医师多专科的匹配问题，为每个医师生成所有共享专科的其他医师集合。
• 第二步新增physician_id %in% peers筛选条件，确保仅保留符合专科关联要求的住院记录。
• 最终结果提供两种格式：列表格式（保留每条住院-医师记录的匹配住院id集合）、展开格式（每行对应一个匹配关系），可按需选择。

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