修改R循环脚本：按分层变量合并输出并导出为Excel

将原循环生成单独统计输出的R脚本，修改为按age_group和sex分层合并统计结果，并导出为Excel文件（两个工作表分别对应两组分层统计）。

原始测试数据 #

# 生成模拟测试数据
set.seed(123)
test_data <- data.frame(
  id = 1:200,
  age_group = sample(c("18-29", "30-44", "45-59", "60+"), 200, replace = TRUE),
  sex = sample(c("Male", "Female"), 200, replace = TRUE),
  health_mental = sample(1:5, 200, replace = TRUE),
  overall_health = sample(1:5, 200, replace = TRUE),
  outlook_life = sample(1:5, 200, replace = TRUE)
)

原始脚本（循环生成单独输出） #

# 原始逻辑：循环输出每个变量的单独分层统计
vars_to_analyze <- c("health_mental", "overall_health", "outlook_life")
groups <- c("age_group", "sex")

for (group in groups) {
  for (var in vars_to_analyze) {
    stats <- aggregate(test_data[[var]] ~ test_data[[group]], data = test_data, 
                       FUN = function(x) c(mean = mean(x), median = median(x), sd = sd(x)))
    print(paste("分组依据:", group, "| 变量:", var))
    print(stats)
  }
}

修改后的脚本（合并结果并导出Excel） #

需依赖dplyr做数据处理，openxlsx操作Excel文件：

# 安装并加载依赖包
if (!require(dplyr)) install.packages("dplyr")
if (!require(openxlsx)) install.packages("openxlsx")
library(dplyr)
library(openxlsx)

# 定义统计计算函数
calculate_stats <- function(data, group_col, var_col) {
  data %>%
    group_by(!!sym(group_col)) %>%
    summarise(
      变量名 = var_col,
      均值 = mean(!!sym(var_col)),
      中位数 = median(!!sym(var_col)),
      标准差 = sd(!!sym(var_col)),
      .groups = "drop"
    )
}

# 指定需要分析的变量
target_vars <- c("health_mental", "overall_health", "outlook_life")

# 按age_group分层合并所有变量统计结果
age_group_combined <- bind_rows(lapply(target_vars, function(var) {
  calculate_stats(test_data, "age_group", var)
})) %>%
  relocate(变量名, .before = age_group)

# 按sex分层合并所有变量统计结果
sex_combined <- bind_rows(lapply(target_vars, function(var) {
  calculate_stats(test_data, "sex", var)
})) %>%
  relocate(变量名, .before = sex)

# 创建Excel工作簿并写入数据
wb <- createWorkbook()
addWorksheet(wb, sheetName = "按年龄分组统计")
writeData(wb, sheet = "按年龄分组统计", x = age_group_combined, rowNames = FALSE)
addWorksheet(wb, sheetName = "按性别分组统计")
writeData(wb, sheet = "按性别分组统计", x = sex_combined, rowNames = FALSE)

# 导出文件
saveWorkbook(wb, file = "合并统计结果.xlsx", overwrite = TRUE)

预期输出示例 #

工作表：按年龄分组统计 #

工作表：按性别分组统计 #

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