在R语言中重排水质数据表格格式的技术咨询

需求说明 #

需要将现有水质数据（指标值以均值(标准差)格式存储，按季节、站点逐行排列）转换为按季节分组、指标分均值/标准差行、站点为列的表格格式。

实现步骤（R语言） #

1. 加载示例数据 #

# 加载用户提供的示例数据集
df <- structure(list(season = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L), levels = c("Winter", "Spring", "Summer", "Autumn"), class = "factor"), 
                     site = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 1L, 2L, 3L, 4L, 5L, 6L, 1L, 2L, 3L, 4L, 5L, 6L), levels = c("1", "2", "3", "4", "5", "6"), class = "factor"), 
                     Temp = c("7.2(1.56)", "7.05(1.91)", "6.3(1.7)", "6.25(2.33)", "6.2(2.4)", "5.4(2.4)", "11.77(2.75)", "12.5(4.62)", "11.6(3.68)", "11.13(3.81)", "11(3.67)", "13.57(4.15)", "13(1.51)", "15.13(1.65)", "14.4(0.75)", "14.93(1.19)", "14.97(1.29)", "21(3.24)"), 
                     pH = c("7.44(0.29)", "7.38(0.28)", "7.52(0.1)", "7.53(0.12)", "7.38(0.06)", "7.56(0.26)", "7.21(0.1)", "7.2(0.13)", "7.35(0.08)", "7.44(0.06)", "7.46(0.02)", "7.72(0.11)", "7.35(0.1)", "7.48(0.12)", "7.44(0.05)", "7.12(0.14)", "7.15(0.03)", "7.86(0.38)"), 
                     `DO` = c("9(0)", "9.1(0.42)", "8.25(0.07)", "8.85(0.49)", "9.25(0.64)", "9(0.42)", "8.73(1.32)", "8.13(2.85)", "7.37(1.16)", "8.3(1.5)", "8.47(1.21)", "9.2(0.79)", "7.43(1.21)", "5.63(3.33)", "7.07(1.12)", "4.77(2.5)", "5(1.1)", "7.87(1.07)"), 
                     `EC` = c("337.5(55.86)", "333(41.01)", "321.5(51.62)", "322(32.53)", "309(25.46)", "300.5(30.41)", "407.67(13.58)", "404(12.29)", "376.33(8.08)", "337.33(8.5)", "333.67(13.5)", "290.67(9.24)", "474(7.21)", "464.33(8.33)", "409(4.36)", "389.33(30.27)", "368.67(19.6)", "327.67(18.58)")), 
                row.names = c(NA, 18L), class = "data.frame")

2. 安装并加载处理工具包 #

使用tidyverse完成数据清洗与格式转换：

install.packages("tidyverse")
library(tidyverse)

3. 拆分指标的均值与标准差 #

将每个指标列中的均值(标准差)格式拆分独立列：

df_clean <- df %>%
  # 拆分所有指标列的均值和标准差
  mutate(across(Temp:EC, ~str_split_fixed(., "\\(|\\)", 3)[, c(1,2)])) %>%
  # 重命名列，区分均值和标准差
  rename_with(~paste0(., "_mean"), Temp:EC) %>%
  rename_with(~paste0(str_remove(., "_mean"), "_sd"), ends_with("_mean")) %>%
  # 转换为长格式，便于后续整理
  pivot_longer(cols = starts_with(c("Temp", "pH", "DO", "EC")),
               names_to = c("indicator", ".value"),
               names_sep = "_")

4. 整理为目标表格格式 #

按季节分组，将站点转为列，指标的均值/标准差转为行：

final_df <- df_clean %>%
  pivot_wider(names_from = site, values_from = c(mean, sd), names_sep = "_") %>%
  # 按季节顺序排序（冬→春→夏→秋）
  arrange(match(season, c("Winter", "Spring", "Summer", "Autumn")))

转换后效果 #

最终表格会呈现以下结构：

• 按季节分组展示
• 每行对应一个指标的均值或标准差
• 各站点作为列，对应显示对应季节下的数值

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