如何用pivot_longer将多组列转换为mean_stat等3个目标列？

需要对数据框执行pivot_longer操作，生成3个新列：

• mean_stat：数据来自mean_stat.x、mean_stat.y、mean_stat
• med_stat：数据来自med_stat.x、med_stat.y、med_stat
• sample：数据来自sample.x、sample.y、sample

尝试代码及报错 #

执行代码：

data_head %>% pivot_longer(col= !c("seqnames",
                            "start","end","strand",
                            "pos","mgi_symbol",
                            "ensembl_transcript_id_version",
                            "ensembl_gene_id_version"), 
                            names_to = c("mean_stat.x", "mean_stat.y","mean_stat",
                            "med_stat.x", "med_stat.y", "med_stat",
                            "sample.x","sample.y","sample"),
                            values_to=c("mean_stat","med_stat","sample"))

报错信息：

Error in `build_longer_spec()`:
! If you supply multiple names in `names_to` you must also supply one of `names_sep` or `names_pattern`.
Run `rlang::last_error()` to see where the error occurred.

数据结构 #

dput输出的数据结构：

structure(list(seqnames = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label = "chr6", class = "factor"), 
    start = c(6862382L, 6862382L, 6862383L, 6862383L, 6862427L, 
    6862427L), end = c(6862382L, 6862382L, 6862383L, 6862383L, 
    6862427L, 6862427L), strand = structure(c(1L, 1L, 2L, 2L, 
    1L, 1L), .Label = c("+", "-", "*"), class = "factor"), pos = c(6862382L, 
    6862382L, 6862383L, 6862383L, 6862427L, 6862427L), mean_stat.x = c(0.19792959917225, 
    0.19792959917225, 0.199321381221393, 0.199321381221393, 0.00406433693126574, 
    0.00406433693126574), med_stat.x = c(0.0165784049972236, 
    0.0165784049972236, 0.145539314543045, 0.145539314543045, 
    0.000704259313821343, 0.000704259313821343), sample.x = c("P_1", 
    "P_1", "P_1", "P_1", "P_1", "P_1"), mgi_symbol = c("Dlx6", 
    "Dlx6", "Dlx6", "Dlx6", "Dlx6", "Dlx6"), ensembl_transcript_id_version = c("ENSMUST00000160937.8", 
    "ENSMUST00000171311.7", "ENSMUST00000160937.8", "ENSMUST00000171311.7", 
    "ENSMUST00000160937.8", "ENSMUST00000171311.7"), ensembl_gene_id_version = c("ENSMUSG00000029754.13", 
    "ENSMUSG00000029754.13", "ENSMUSG00000029754.13", "ENSMUSG00000029754.13", 
    "ENSMUSG00000029754.13", "ENSMUSG00000029754.13"), mean_stat.y = c(0.000764852175246134, 
    0.000764852175246134, 0.394296327919949, 0.394296327919949, 
    0.00375821267875787, 0.00375821267875787), med_stat.y = c(0.000764852175246134, 
    0.000764852175246134, 0.394296327919949, 0.394296327919949, 
    0.00375821267875787, 0.00375821267875787), sample.y = c("P_2", 
    "P_2", "P_2", "P_2", "P_2", "P_2"), mean_stat = c(0.221987651899815, 
    0.221987651899815, 0.352667337885222, 0.352667337885222, 
    0.00892599647121971, 0.00892599647121971), med_stat = c(0.00799234148146299, 
    0.00799234148146299, 0.256276923247135, 0.256276923247135, 
    0.000752177507566287, 0.000752177507566287), sample = c("P_3", 
    "P_3", "P_3", "P_3", "P_3", "P_3")), row.names = c(NA, -6L
), class = c("tbl_df", "tbl", "data.frame"))

报错原因是误解了pivot_longer的参数逻辑：names_to用于指定拆分后的列名，而非直接罗列原列名；当需要将多组指标列按样本分组转长时，需通过names_sep或names_pattern拆分原列名中的指标和样本标识。

以下是三种可行实现方式，推荐第三种最简洁的方案：

方案一：正则匹配列名后转宽 #

library(tidyverse)

data_head %>%
  # 转长，拆分带后缀的列，无后缀列补充临时后缀.z
  pivot_longer(
    cols = !c(seqnames, start, end, strand, pos, mgi_symbol, 
              ensembl_transcript_id_version, ensembl_gene_id_version),
    names_to = c("stat_type", "sample_suffix"),
    names_pattern = "(.*)\\.(.*)",
    values_to = "value",
    names_repair = ~ ifelse(str_detect(., "\\."), ., paste0(., ".z"))
  ) %>%
  # 映射后缀到样本名
  mutate(sample = case_when(
    sample_suffix == "x" ~ "P_1",
    sample_suffix == "y" ~ "P_2",
    sample_suffix == "z" ~ "P_3"
  )) %>%
  # 转宽得到目标列
  pivot_wider(names_from = stat_type, values_from = value) %>%
  select(-sample_suffix)

方案二：重命名无后缀列后拆分 #

data_head %>%
  # 给无后缀的指标列添加.z后缀
  rename_with(
    ~ ifelse(str_detect(., "^(mean_stat|med_stat|sample)$"), paste0(., ".z"), .)
  ) %>%
  pivot_longer(
    cols = !c(seqnames, start, end, strand, pos, mgi_symbol, 
              ensembl_transcript_id_version, ensembl_gene_id_version),
    names_to = c("stat_type", "sample_suffix"),
    names_sep = "\\.",
    values_to = "value"
  ) %>%
  mutate(sample = case_when(
    sample_suffix == "x" ~ "P_1",
    sample_suffix == "y" ~ "P_2",
    sample_suffix == "z" ~ "P_3"
  )) %>%
  pivot_wider(names_from = stat_type, values_from = value) %>%
  select(-sample_suffix)

方案三：一步到位的正则匹配（推荐） #

利用.value参数直接将匹配到的指标名作为列名，同时捕获样本标识：

data_head %>%
  pivot_longer(
    cols = !c(seqnames, start, end, strand, pos, mgi_symbol, 
              ensembl_transcript_id_version, ensembl_gene_id_version),
    names_to = c(".value", "sample"),
    names_pattern = "(mean_stat|med_stat|sample)(?:\\.(x|y))?",
    names_transform = list(sample = ~ case_when(
      . == "x" ~ "P_1",
      . == "y" ~ "P_2",
      is.na(.) ~ "P_3"
    ))
  )

• .value：表示将正则匹配到的第一部分（指标名）作为新列名
• names_pattern：匹配带/不带后缀的指标列，捕获样本后缀x/y，无后缀则为NA
• names_transform：将后缀映射为对应的样本名，无后缀的对应P_3

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