如何按指定因子顺序导出TukeyHSD结果及描述统计至Excel

核心修改点 #

1. 重新排序描述统计结果：直接按照指定的因子顺序提取result$mean的行，确保导出顺序与预设一致。
2. 按指定顺序重组TukeyHSD比较结果：遍历预设因子顺序，依次提取每个因子的所有配对比较结果，每处理完一个因子的比较后插入空行，最后清理多余空行。

修改后的完整代码 #

# 生成模拟数据
main.data <- runif(256) 
main.factor <- c("1st control", "2nd control", "Control A", "Control B",
                 "Variant A", "Variant B", "Variant C",
                 "1st Variant", "2nd Variant", "3rd Variant",
                 "Treatment 1", "Treatment 2", "Treatment 3",
                 "1st Treatment", "2nd Treatment", "3rd Treatment")

df <- data.frame(experiment=rep(main.factor,each=16), Main.value=main.data)
# 预先指定因子水平顺序
df$experiment <- factor(df$experiment, levels = c("1st control", "2nd control", "Control A", "Control B",
                                                  "Variant A", "Variant B", "Variant C",
                                                  "1st Variant", "2nd Variant", "3rd Variant",
                                                  "Treatment 1", "Treatment 2", "Treatment 3",
                                                  "1st Treatment", "2nd Treatment", "3rd Treatment"))

# 单因素ANOVA与TukeyHSD检验
model.1 <- lm(Main.value~experiment, data=df)
anova(model.1)

library(agricolae)
result <- HSD.test(model.1, "experiment", group=F)

# 指定目标因子顺序（与预设一致）
factor.order <- c("1st control", "2nd control", "Control A", "Control B", 
                  "Variant A", "Variant B", "Variant C", 
                  "1st Variant", "2nd Variant", "3rd Variant",
                  "Treatment 1", "Treatment 2", "Treatment 3",
                  "1st Treatment", "2nd Treatment", "3rd Treatment")

# --------------------------
# 处理TukeyHSD比较结果：按指定顺序+插入空行
# --------------------------
# 定义函数：提取包含指定因子的所有配对
get_pairs_for_factor <- function(target_factor, comp_row_names) {
  grep(paste0("^", target_factor, "-|-(", target_factor, ")$"), comp_row_names, value = TRUE)
}

comp_rows <- rownames(result$comparison)
ordered_comp <- data.frame()

for (f in factor.order) {
  # 获取当前因子的所有配对比较
  f_pairs <- get_pairs_for_factor(f, comp_rows)
  if (length(f_pairs) > 0) {
    # 添加当前因子的比较结果
    ordered_comp <- rbind(ordered_comp, result$comparison[f_pairs, , drop = FALSE])
    # 添加空行分隔
    ordered_comp <- rbind(ordered_comp, rep(NA, ncol(result$comparison)))
  }
}

# 移除最后一行多余的空行
if (nrow(ordered_comp) > 0) {
  ordered_comp <- ordered_comp[-nrow(ordered_comp), ]
}
# 给空行设置空行名
empty_row_idx <- which(apply(ordered_comp, 1, function(x) all(is.na(x))))
rownames(ordered_comp)[empty_row_idx] <- ""

# --------------------------
# 处理描述统计结果：按指定顺序排序
# --------------------------
result.2 <- result$mean[factor.order, , drop = FALSE]

# --------------------------
# 导出到Excel
# --------------------------
library(xlsx)
# 导出TukeyHSD检验结果
write.xlsx(ordered_comp, file = "main.result.xlsx", 
           col.names = TRUE, row.names = TRUE, sheetName = "TukeyHSD_test")

# 追加导出描述统计结果
write.xlsx(result.2, file = "main.result.xlsx", 
           col.names = TRUE, row.names = TRUE, sheetName = "descriptive_statistics", append = TRUE)

代码说明 #

• 因子顺序控制：通过factor.order预设顺序，直接对result$mean按该顺序提取行，确保描述统计的输出顺序符合要求。
• TukeyHSD结果重组：使用自定义函数get_pairs_for_factor提取每个因子的所有配对比较，每一组比较后插入NA空行，最后清理多余空行并设置空行的行名为空，保证Excel中显示效果符合要求。
• Excel导出：保留原代码的xlsx包导出逻辑，修改后的数据框直接导出即可按指定顺序展示。

内容的提问来源于stack exchange，提问作者Baltazár Tivadar