基于R语言计算各College及Department年度招生增长率的问题

原始数据集 #

df <- structure(list(year = c(2017, 2017, 2017, 2017, 2018, 2018, 2018, 
2018, 2019, 2019, 2019, 2019, 2017, 2017, 2018, 2018, 2019, 2019, 
2017, 2017, 2017, 2017, 2018, 2018, 2018, 2018, 2019, 2019, 2019, 
2019), College = c("College1", "College1", "College1", "College1", 
"College1", "College1", "College1", "College1", "College1", "College1", 
"College1", "College1", "College2", "College2", "College2", "College2", 
"College2", "College2", "College3", "College3", "College3", "College3", 
"College3", "College3", "College3", "College3", "College3", "College3", 
"College3", "College3"), Department = c("Department 1", "Department 2", 
"Department 3", "Department 4", "Department 1", "Department 2", 
"Department 3", "Department 4", "Department 1", "Department 2", 
"Department 3", "Department 4", "Department 1", "Department 2", 
"Department 1", "Department 2", "Department 1", "Department 2", "Department 1", 
"Department 2", "Department 3", "Department 4", "Department 1", "Department 2", 
"Department 3", "Department 4", "Department 1", "Department 2", "Department 3", 
"Department 4"), Enrollment = c(51L, 322L, 251L, 106L, 468L, 205L, 718L, 200L, 
344L, 256L, 434L, 38L, 487L, 503L, 14L, 448L, 489L, 437L, 695L, 833L, 941L, 
299L, 864L, 888L, 531L, 335L, 47L, 753L, 319L, 986L)), row.names = c(NA, -30L), class = c("tbl_df", "tbl", "data.frame"
))

需求与问题 #

需要新增RateChange_College（学院年度总招生相对上年的增长率）和RateChange_Department（系部年度招生相对上年的增长率）两个字段。原代码因分组逻辑错误，未按年度+学院/系部正确分组计算，导致结果不符合预期。

正确解决方案 #

以下代码通过分步骤计算学院和系部的增长率，最终得到符合要求的输出：

library(dplyr)

# 1. 计算学院年度总招生及增长率
college_growth <- df %>%
  group_by(College, year) %>%
  summarise(total_enroll = sum(Enrollment), .groups = "drop") %>%
  arrange(College, year) %>%
  group_by(College) %>%
  mutate(RateChange_College = (total_enroll - lag(total_enroll)) / lag(total_enroll)) %>%
  select(-total_enroll)

# 2. 计算系部年度招生增长率
department_growth <- df %>%
  arrange(College, Department, year) %>%
  group_by(College, Department) %>%
  mutate(RateChange_Department = (Enrollment - lag(Enrollment)) / lag(Enrollment))

# 3. 合并结果并调整格式
final_df <- department_growth %>%
  left_join(college_growth, by = c("College", "year")) %>%
  arrange(match(row.names(.), row.names(df))) %>%
  mutate(X = row_number()) %>%
  select(X, year, College, Department, Enrollment, RateChange_College, RateChange_Department)

# 查看最终结果
print(final_df)

代码逻辑说明 #

• 学院级增长率：先按学院+年份汇总总招生人数，再按学院分组，计算当年总招生相对上一年的变化率，公式为(当年总招生 - 上年总招生) / 上年总招生，2017年作为起始年，增长率为NA。
• 系部级增长率：按学院+系部分组，按年份排序后，计算该系部当年招生相对上年的变化率，起始年2017年增长率为NA。
• 最后合并两个结果，补充X列并调整列顺序，与期望输出结构一致。

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