如何简洁实现两组关联列的并行pivot_longer透视？

现有数据框包含well_short列，以及两组关联列（reporter1/reporter2、target1/target2），需要将这两组列并行执行pivot_longer操作，替代当前两步pivot_longer加过滤的繁琐代码。

数据示例 #

data <- structure(list(well_short = c("A1", "A2", "A3", "A4", "A5", "A6", 
"A7", "A8", "A9", "A10", "A11", "A12", "B1", "B2", "B3", "B4", 
"B5", "B6", "B7", "B8"), reporter1 = c("FAM", "FAM", "FAM", "FAM", 
"FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM", 
"FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM"), reporter2 = c("VIC", 
"VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", 
"VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC"), target1 = c(NA, NA, NA, NA, NA, "EIF4A2", "EIF4A2", "EIF4A2", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "EIF4A2", "EIF4A2", "EIF4A2"
), target2 = c("GAPDH", "GAPDH", "GAPDH", NA, NA, "ATP5B", "ATP5B", 
"ATP5B", NA, NA, NA, NA, "GAPDH", "GAPDH", "GAPDH", NA, NA, "ATP5B", 
"ATP5B", "ATP5B")), row.names = c(NA, -20L), class = "data.frame")

当前实现（需简化） #

data_long <- data %>%
  pivot_longer(cols = starts_with("reporter"),
               names_to = "reporter_n",
               names_prefix = "reporter",
               values_to = "reporter") %>%
  pivot_longer(cols = starts_with("target"),
               names_to = "target_n",
               names_prefix = "target",
               values_to = "target") %>%
  filter(reporter_n == target_n,
         !is.na(target)) %>%
  select(-c(reporter_n, target_n))

目标结果 #

> head(data_long)
# A tibble: 6 × 3
  well_short reporter target
  <chr>      <chr>    <chr> 
1 A1         VIC      GAPDH 
2 A2         VIC      GAPDH 
3 A3         VIC      GAPDH 
4 A6         FAM      EIF4A2
5 A6         VIC      ATP5B 
6 A7         FAM      EIF4A2

简化方案 #

方案：用pivot_longer的names_pattern一次性处理 #

利用正则表达式匹配列名的前缀与数字后缀，同时完成关联列的配对展开，一步到位：

library(tidyr)
library(dplyr)

data_long <- data %>%
  pivot_longer(
    cols = -well_short,
    names_pattern = "(reporter|target)(\\d)",
    names_to = c(".value", "group"),
    values_drop_na = TRUE
  ) %>%
  select(-group)

关键参数说明：

• names_pattern = "(reporter|target)(\\d)"：把列名拆分为两部分，第一部分是前缀（reporter或target），第二部分是数字组号
• names_to = c(".value", "group")：.value表示保留前缀作为新列名，group暂时存储数字组号（后续可直接删除）
• values_drop_na = TRUE：直接过滤掉target为NA的行，省去额外过滤步骤

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