R语言tidyr：如何按指定规则实现多值数据的宽表转换

需求说明 #

现有长格式数据集，需转换为指定宽格式：

• 行：以VarX_CondY为标识（如VarA_Cond1）
• 列：以Test项(统计量)为命名（如VVV(Mean(SD))、VVV(Median(IQR))）

原始数据 #

数据预览 #

> head(data)
# A tibble: 6 x 3
  variable       `Mean (SD)`    `Median (IQR)`
  <chr>          <glue>         <glue>        
1 VarA_VVV_Cond1 268.59 (80.6)  276 (86)      
2 VarA_WWW_Cond1 149.07 (39.79) 155 (40.5)    
3 VarA_XXX_Cond1 147.71 (39.65) 155 (41)      
4 VarA_YYY_Cond1 18.85 (10.76)  18 (15.5)     
5 VarA_ZZZ_Cond1 20.98 (11.34)  20 (14)       
6 VarA_VVV_Cond2 228.49 (83.77) 241 (116)  

数据集dput #

> dput(data)
structure(list(variable = c("VarA_VVV_Cond1", "VarA_WWW_Cond1", 
"VarA_XXX_Cond1", "VarA_YYY_Cond1", "VarA_ZZZ_Cond1", "VarA_VVV_Cond2", 
"VarA_WWW_Cond2", "VarA_XXX_Cond2", "VarA_YYY_Cond2", "VarA_ZZZ_Cond2", 
"VarB_VVV_Cond1", "VarB_WWW_Cond1", "VarB_XXX_Cond1", "VarB_YYY_Cond1", 
"VarB_ZZZ_Cond1", "VarB_VVV_Cond2", "VarB_WWW_Cond2", "VarB_XXX_Cond2", 
"VarB_YYY_Cond2", "VarB_ZZZ_Cond2"), `Mean (SD)` = structure(c("268.59 (80.6)", 
"149.07 (39.79)", "147.71 (39.65)", "18.85 (10.76)", "20.98 (11.34)", 
"228.49 (83.77)", "113.66 (35.91)", "112.64 (35.75)", "24.07 (15.79)", 
"26.36 (16.51)", "250.72 (61.53)", "140.71 (30.52)", "138.93 (30.37)", 
"21.02 (10.46)", "22.72 (11.05)", "225.98 (81.32)", "112.43 (36.09)", 
"111.1 (36.41)", "24.71 (16.77)", "26.59 (17.49)"), class = c("glue", 
"character")), `Median (IQR)` = structure(c("276 (86)", "155 (40.5)", 
"155 (41)", "18 (15.5)", "20 (14)", "241 (116)", "116 (51)", 
"116 (48)", "23 (21.5)", "24 (22.5)", "259 (60)", "142 (36)", 
"142 (34)", "21 (15)", "21 (15)", "244.5 (93.5)", "107.5 (51.5)", 
"107 (50.75)", "24 (20.75)", "24.5 (21.75)"), class = c("glue", 
"character"))), row.names = c(NA, -20L), class = c("tbl_df", 
"tbl", "data.frame"))

期望输出结构 #

VVV(Mean(SD)) VVV(Median(IQR)) XXX(Mean(SD)) XXX(Median(IQR))... 
VarA_Cond1
VarA_Cond2
VarB_Cond1
VarB_Cond2

用户尝试的代码（未解决） #

data1 <- data %>% 
   tidyr::pivot_wider(.,
                      names_from = "variable",
                      values_from = c("Mean (SD)", "Median (IQR)")) %>% 
   pivot_longer(cols = 1:40,
   names_to = c("Names"),
   values_to = c("Mean_SD", "Median_IQR"))

解决方案 #

核心思路：先拆分variable列提取分组信息，再通过pivot_wider构建目标宽表。

完整代码 #

library(tidyverse)

# 处理数据
result <- data %>%
  # 拆分variable列为Var、Test、Cond三部分
  separate(variable, into = c("Var", "Test", "Cond"), sep = "_") %>%
  # 合并Var和Cond作为行标识
  mutate(row_id = paste(Var, Cond, sep = "_")) %>%
  # 透视宽表：行是row_id，列由Test和统计量组合，值为对应内容
  pivot_wider(
    id_cols = row_id,
    names_from = Test,
    values_from = c(`Mean (SD)`, `Median (IQR)`),
    names_glue = "{Test}({str_remove(.value, ' \\(.*\\)')}(SD))"
  ) %>%
  # 调整列名格式，修正Median的统计量名称
  rename_with(~ str_replace(., "Mean(SD)", "Mean(SD)") %>% str_replace("Median(SD)", "Median(IQR)")) %>%
  # 设置row_id为行名
  column_to_rownames("row_id")

# 查看结果
print(result)

代码解释 #

1. 拆分variable列：用separate按_拆分，提取出变量(Var)、测试项(Test)、条件(Cond)三个维度。
2. 构建行标识：合并Var和Cond得到row_id（如VarA_Cond1），作为宽表的行。
3. 透视宽表：pivot_wider中通过names_glue自定义列名格式，将Test和统计量类型组合。
4. 调整列名：修正Median对应的统计量名称，匹配期望格式。
5. 设置行名：把row_id列转为行名，匹配输出结构。

输出示例 #

VVV(Mean(SD)) VVV(Median(IQR)) WWW(Mean(SD)) WWW(Median(IQR)) XXX(Mean(SD)) XXX(Median(IQR)) YYY(Mean(SD)) YYY(Median(IQR)) ZZZ(Mean(SD)) ZZZ(Median(IQR))
VarA_Cond1   268.59 (80.6)        276 (86)    149.07 (39.79)       155 (40.5)    147.71 (39.65)       155 (41)     18.85 (10.76)        18 (15.5)     20.98 (11.34)        20 (14)
VarA_Cond2   228.49 (83.77)       241 (116)    113.66 (35.91)       116 (51)     112.64 (35.75)       116 (48)     24.07 (15.79)        23 (21.5)     26.36 (16.51)       24 (22.5)
VarB_Cond1   250.72 (61.53)       259 (60)     140.71 (30.52)       142 (36)     138.93 (30.37)       142 (34)     21.02 (10.46)        21 (15)      22.72 (11.05)        21 (15)
VarB_Cond2   225.98 (81.32)       244.5 (93.5)  112.43 (36.09)      107.5 (51.5)   111.1 (36.41)      107 (50.75)   24.71 (16.77)       24 (20.75)    26.59 (17.49)      24.5 (21.75)

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