在R中处理DataFrame计算外科医生年均手术量的技术问询

开发者阿泽

2026-6-28

问题描述

在R环境中工作，需要基于每位外科医生的有效工作年份计算Annual_Cases列：

Years变量以每3年为一个时间区间统计
部分外科医生在多家医院任职
手动计算逻辑：Annual_Cases = 总病例数 / 有效工作总年份，期望输出如下：
Surgeon Total_Cases Total_Years Annual_Cases
A 646 9 71.78
B 163 17 9.59

原代码及数据集：

library(dplyr);library(tidyr)
df %>% drop_na(Cases) %>% group_by(Surgeon)%>% summarise(Annaul_Cases= sum(Cases))/n,  na.rm=T)-> df # I need to calculate n column (number of years the surgeon were in service)


structure(list(Serial.ID = c(215, 522, 903, 1210, 1591, 1898, 
2279, 2586, 2967, 3274, 3655, 3962, 4343, 4650, 5031, 5338, 330, 
1018, 1706, 2394, 3082, 3770, 4458, 5146), Surgeon = c("A", "A", 
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", 
"A", "B", "B", "B", "B", "B", "B", "B", "B"), Center = c("Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Maimonides Medical Center", 
"St. Vincents Hospital and Medical Center", "Mount Sinai Hospital", 
"Mount Sinai Hospital", "Mount Sinai Hospital", "Mount Sinai Hospital", 
"Mount Sinai Hospital", "Mount Sinai Hospital", "Mount Sinai Hospital", 
"Mount Sinai Hospital"), Years = c("1996-1998", "1996-1998", 
"1999-2001", "1999-2001", "2002-2004", "2002-2004", "2005-2007", 
"2005-2007", "2008-2010", "2008-2010", "2011-2013", "2011-2013", 
"2014-2016", "2014-2016", "2017-2019", "2017-2019", "1996-1998", 
"1999-2001", "2002-2004", "2005-2007", "2008-2010", "2011-2013", 
"2014-2016", "2017-2019"), Cases = c(377, 19, 223, NA, 27, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 110, 18, 13, 
17, 4, 1), Deaths = c("10", "1", "6", "NA", "0", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", 
"1", "0", "0", "0", "0", "0"), Annual_Cases = c("NA", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA")), row.names = c(NA, 
-24L), class = c("tbl_df", "tbl", "data.frame"))

修改方案

原代码存在语法错误，且未处理「同一区间多家医院重复统计年份」的问题，以下是修正后的代码：

library(dplyr)
library(tidyr)

df_processed <- df %>%
  # 拆分年份区间为起始、结束年份
  separate(Years, into = c("Start_Year", "End_Year"), sep = "-", convert = TRUE) %>%
  # 计算单个区间的年份跨度
  mutate(Interval_Years = End_Year - Start_Year + 1) %>%
  # 保留有病例记录的有效行
  drop_na(Cases) %>%
  # 去重：同一医生同一区间只统计一次年份
  distinct(Surgeon, Start_Year, End_Year, .keep_all = TRUE) %>%
  # 按医生分组计算核心指标
  group_by(Surgeon) %>%
  summarise(
    Total_Cases = sum(Cases, na.rm = TRUE),
    Total_Years = sum(Interval_Years, na.rm = TRUE),
    Annual_Cases = round(Total_Cases / Total_Years, 2)
  ) %>%
  ungroup()

print(df_processed)

代码说明

拆分年份区间：把Years拆分为起始和结束年份，计算每个区间的实际年份数（如1996-1998为3年）
去重处理：避免同一医生在同一区间的多家医院任职时，重复统计该区间的年份
分组计算：按医生汇总总病例数、总有效年份，最后计算年均病例数并保留两位小数

输出结果

# A tibble: 2 × 4
  Surgeon Total_Cases Total_Years Annual_Cases
  <chr>         <dbl>       <dbl>        <dbl>
1 A               646           9         71.78
2 B               163          17          9.59

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