在R中利用正则表达式将宽格式DataFrame转为长格式

我有如下DataFrame：

d <- data.frame(
  sub = c(101, 102, 103),
  dmpfc_selfrefneg = c(1,2,3),
  dmpfc_selfimmneg = c(1,2,3),
  dmpfc_socrefneg = c(1,2,3),
  dmpfc_socimmneg = c(1,2,3),
  tpj_selfrefneg = c(1,2,3),
  tpj_selfimmneg = c(1,2,3),
  tpj_socrefneg = c(1,2,3),
  tpj_socimmneg = c(1,2,3)
)

期望得到的长格式输出如下：

d_ideal <- data.frame(
  sub = c(101, 101, 101, 101, 102, 102, 102, 102, 103, 103, 103, 103, 101, 101, 101, 101, 102, 102, 102, 102, 103, 103, 103, 103),
  roi = c('dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'dmpfc', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj', 'tpj'),
  condition = c('selfrefneg', 'selfimmneg', 'socrefneg', 'socimmneg', 'selfrefneg', 'selfimmneg', 'socrefneg', 'socimmneg', 'selfrefneg', 'selfimmneg', 'socrefneg', 'socimmneg', 'selfrefneg', 'selfimmneg', 'socrefneg', 'socimmneg', 'selfrefneg', 'selfimmneg', 'socrefneg', 'socimmneg', 'selfrefneg', 'selfimmneg', 'socrefneg', 'socimmneg'),
  value = c(1,1,1,1,2,2,2,2,3,3,3,3,1,1,1,1,2,2,2,2,3,3,3,3)
)

注：原DataFrame中除sub外的列名均为roi_condition格式，下划线分隔的两部分分别对应输出中的roi和condition列。已知可以用pivot_longer或gather转长格式，请问如何结合列名拆分实现智能转换？

用tidyverse包中的pivot_longer函数就能轻松实现，核心是利用参数拆分列名，有两种简单方法：

方法1：直接指定分隔符 #

如果列名都是用下划线固定分隔，直接用names_sep参数指定下划线为分隔符，把列名拆成两部分：

library(tidyverse)

d <- data.frame(
  sub = c(101, 102, 103),
  dmpfc_selfrefneg = c(1,2,3),
  dmpfc_selfimmneg = c(1,2,3),
  dmpfc_socrefneg = c(1,2,3),
  dmpfc_socimmneg = c(1,2,3),
  tpj_selfrefneg = c(1,2,3),
  tpj_selfimmneg = c(1,2,3),
  tpj_socrefneg = c(1,2,3),
  tpj_socimmneg = c(1,2,3)
)

d_ideal <- d %>%
  pivot_longer(
    cols = -sub,  # 排除sub列，处理其余所有列
    names_sep = "_",  # 用下划线拆分列名
    names_to = c("roi", "condition"),  # 拆分后的两部分分别对应roi和condition列
    values_to = "value"  # 原始列的值存入value列
  )

方法2：正则表达式匹配（适用于复杂分隔规则） #

如果列名的分隔规则更灵活，用names_pattern结合正则分组来拆分，比如匹配第一个下划线前的内容为roi，后面的为condition：

d_ideal <- d %>%
  pivot_longer(
    cols = -sub,
    names_pattern = "(.*)_(.*)",  # 正则分组：第一组匹配下划线前的所有字符，第二组匹配下划线后的所有字符
    names_to = c("roi", "condition"),
    values_to = "value"
  )

运行上述代码后，得到的结果和你期望的d_ideal完全一致。

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