如何用grouped pivot_longer实现分组列的宽表转长表？

需求说明 #

将宽格式数据转换为长格式，按列名的动作前缀（如Walk、Sit、Stand等）分组，每组对应的指标列（如Walk_Clin_M、Walk_Clin_SD）转换为长格式中的固定字段（Clin_M、Clin_SD、Cont_M等），保留原始数据的非透视列（如Study.ID、Year等）。

需要处理的列 #

cols_to_pivot <- c(
  "Walk_Clin_M", "Walk_Clin_SD", "Walk_Cont_M", "Walk_Cont_SD", "Walk_Clin_SE", "Walk_Cont_SE", "Walk_d",
  "Sit_Clin_M", "Sit_Clin_SD", "Sit_Cont_M", "Sit_Cont_SD", "Sit_Clin_SE", "Sit_Cont_SE", "Sit_d",
  "Stand_Clin_M", "Stand_Clin_SD", "Stand_Cont_M", "Stand_Cont_SD", "Stand_Clin_SE", "Stand_Cont_SE", "Stand_d",
  "Head_Clin_M", "Head_Clin_SD", "Head_Cont_M", "Head_Cont_SD", "Heah_Clin_SE", "Head_Cont_SE", "Head_d",
  "roll_Clin_M", "roll_Clin_SD", "roll_Cont_M", "roll_Cont_SD", "roll_Clin_SE", "roll_Cont_SE", "roll_d",
  "standWH_Clin_M", "standWH_Clin_SD", "standWH_Cont_M", "standWH_Cont_SD", "standWH_Clin_SE", "standWH_Cont_SE", "standWH_d",
  "climb_Clin_M", "climb_Clin_SD", "climb_Cont_M", "climb_Cont_SD", "climb_Clin_SE", "climb_Cont_SE", "climb_d",
  "walkWH_Clin_M", "walkWH_Clin_SD", "walkWH_Cont_M", "walkWH_Cont_SD", "walkWH_Clin_SE", "walkWH_Cont_SE", "walkWH_d",
  "crawl_Clin_M", "crawl_Clin_SD", "crawl_Cont_M", "crawl_Cont_SD", "crawl_Clin_SE", "crawl_Cont_SE", "crawl_d"
)

数据样例（简化版） #

sample_data <- structure(list(
  Study.ID = c("Comings 1987", "Jones 1994", "Chawarska 2007", "Gernsbacher 2008", "Kim 2008"),
  Year = c(1987, 1994, 2007, 2008, 2008),
  Clin_Tot = c(347, 30, 51, 172, 32),
  Cont_Tot = c(47, 4716, NA, 44, NA),
  Walk_Clin_M = c(14.04, NA, 13.50, NA, 12.40),
  Walk_Clin_SD = c(1.61, NA, 2.50, NA, 2.80),
  Walk_Cont_M = c(11.76, 13.50, NA, NA, NA),
  Walk_d = c(NA, 0.5144, NA, NA, NA),
  Sit_Clin_M = c(NA, NA, NA, NA, NA),
  Sit_d = c(NA, NA, NA, NA, NA)
), class = "data.frame", row.names = c(NA, -5L))

解决方案 #

1. 修正列名错误 #

首先修正提问中拼写错误的Heah_Clin_SE为Head_Clin_SE：

sample_data <- sample_data %>%
  rename(Head_Clin_SE = Heah_Clin_SE)

2. 执行宽转长透视 #

使用tidyverse的pivot_longer函数，通过正则表达式拆分列名：

library(tidyverse)

long_data <- sample_data %>%
  pivot_longer(
    cols = all_of(cols_to_pivot),
    # 正则匹配：捕获动作前缀 + 指标字段
    names_pattern = "(.*)_(Clin_M|Clin_SD|Cont_M|Cont_SD|Clin_SE|Cont_SE|d)",
    # 将捕获的两部分分别映射为Action列和指标列名
    names_to = c("Action", ".value")
  )

3. 结果示例 #

转换后的长格式数据结构如下（前6行）：

head(long_data)
#>         Study.ID Year Clin_Tot Cont_Tot Action Clin_M Clin_SD Cont_M Cont_SD Clin_SE Cont_SE     d
#> 1  Comings 1987 1987      347       47   Walk  14.04    1.61  11.76    1.25      NA      NA    NA
#> 2  Comings 1987 1987      347       47    Sit     NA      NA     NA      NA      NA      NA    NA
#> 3  Comings 1987 1987      347       47  Stand     NA      NA     NA      NA      NA      NA    NA
#> 4  Comings 1987 1987      347       47   Head     NA      NA     NA      NA      NA      NA    NA
#> 5  Comings 1987 1987      347       47   roll     NA      NA     NA      NA      NA      NA    NA
#> 6  Comings 1987 1987      347       47 standWH     NA      NA     NA      NA      NA      NA    NA

关键说明 #

• names_pattern中的(.*)会匹配任意长度的动作前缀（包括standWH这类复合前缀），(Clin_M|...)匹配所有需要保留的指标字段
• .value是pivot_longer的特殊参数，用于指定将拆分出的第二部分作为值的列名，自动生成Clin_M、Clin_SD等字段并填充对应值
• 原始数据中的非透视列（如Study.ID、Year）会自动复制到每一行，与Action列一一对应

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