如何在pivot_longer中编写正确的names_pattern正则表达式

需求说明 #

现有数据列名格式为[RATER_PREFIX]_[MEASURE]_[OTHER]_[SUFFIX]（如CRIS_CLAU_ENG_O、LARI_TUNITS_WRI_O），需要通过pivot_longer实现：

• 单独处理CLAU类列时，输出结构：ID | CLAU_VALUE | RATER，其中RATER取值为CRIS_O、LARI_O
• 同时处理CLAU和TUNITS类列时，输出结构可选：
  1. ID | CLAU_VALUE | TUNITS_VALUE | RATER，RATER取值为CRIS_O、CRIS_WRI、LARI_O、LARI_WRI
  2. ID | RATER | TYPE | CLAU_VALUE | TUNITS_VALUE，新增TYPE列存储O/WRI，RATER取值为CRIS、LARI

问题分析 #

原代码中names_pattern = "^([^_]+)([^_]+)"未正确捕获列名的分段逻辑，导致拆分后的字段错位，无法得到预期的RATER和值列。

分步解决方案 #

1. 单独处理CLAU类列 #

通过正则捕获RATER_PREFIX、MEASURE（CLAU）、RATER_SUFFIX，再合并为RATER字段：

# 加载数据
data1 <- structure(list(ID = c("A", "B", "C", "D", "E", "F", "G", "H", 
"I", "J", "K", "L", "M", "N", "O", "P"), CRIS_CLAU_ENG_O = c(6, 
5, 6, 7, 6, 3, 5, 5, 6, 6, 7, 9, 8, 6, 6, 6), CRIS_TUNITS_WRI_O = c(5, 
5, 4, 5, 5, 3, 5, 5, 4, 4, 7, 7, 7, 6, 6, 5), LARI_CLAU_ENG_O = c(6, 
5, 5, 7, 7, 3, 5, 5, 6, 6, 9, 9, 8, 8, 6, 6), LARI_TUNITS_WRI_O = c(5, 
3, 4, 6, 5, 3, 2, 5, 4, 4, 7, 8, 7, 6, 6, 5)), row.names = c(NA, 
-16L), spec = structure(list(cols = list(ALUNO = structure(list(), class = c("collector_character", 
"collector")), CRIS_CLAU_ENG_O = structure(list(), class = c("collector_double", 
"collector")), CRIS_TUNITS_WRI_O = structure(list(), class = c("collector_double", 
"collector")), LARI_CLAU_ENG_O = structure(list(), class = c("collector_double", 
"collector")), LARI_TUNITS_WRI_O = structure(list(), class = c("collector_double", 
"collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), delim = ","), class = "col_spec"),  class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"))

# 处理CLAU列
data1 %>% 
  select(ID, contains("CLAU")) %>% 
  pivot_longer(
    cols = -ID,
    names_to = c("RATER_PREFIX", ".value", "RATER_SUFFIX"),
    names_pattern = "^([^_]+)_([^_]+)_.*_([^_]+)$"
  ) %>% 
  mutate(RATER = paste(RATER_PREFIX, RATER_SUFFIX, sep = "_")) %>% 
  select(ID, CLAU_VALUE = CLAU, RATER)

输出示例：

# A tibble: 32 × 3
   ID    CLAU_VALUE RATER
   <chr>      <dbl> <chr>
 1 A              6 CRIS_O
 2 A              6 LARI_O
 3 B              5 CRIS_O
 4 B              5 LARI_O
...

2. 同时处理CLAU和TUNITS类列 #

方案一：生成带完整RATER标识的宽格式

data1 %>% 
  pivot_longer(
    cols = -ID,
    names_to = c("RATER_PREFIX", "MEASURE", "RATER_SUFFIX"),
    names_pattern = "^([^_]+)_([^_]+)_.*_([^_]+)$"
  ) %>% 
  pivot_wider(
    names_from = MEASURE,
    values_from = value,
    names_glue = "{MEASURE}_VALUE"
  ) %>% 
  mutate(RATER = paste(RATER_PREFIX, RATER_SUFFIX, sep = "_")) %>% 
  select(ID, CLAU_VALUE, TUNITS_VALUE, RATER)

输出示例：

# A tibble: 32 × 4
   ID    CLAU_VALUE TUNITS_VALUE RATER
   <chr>      <dbl>        <dbl> <chr>
 1 A              6            5 CRIS_O
 2 A              6            5 CRIS_WRI
 3 A              6            5 LARI_O
 4 A              6            5 LARI_WRI
...

方案二：拆分出TYPE字段的宽格式

data1 %>% 
  pivot_longer(
    cols = -ID,
    names_to = c("RATER", "MEASURE", "TYPE"),
    names_pattern = "^([^_]+)_([^_]+)_.*_([^_]+)$"
  ) %>% 
  pivot_wider(
    names_from = MEASURE,
    values_from = value,
    names_glue = "{MEASURE}_VALUE"
  ) %>% 
  select(ID, RATER, TYPE, CLAU_VALUE, TUNITS_VALUE)

输出示例：

# A tibble: 32 × 5
   ID    RATER TYPE  CLAU_VALUE TUNITS_VALUE
   <chr> <chr> <chr>      <dbl>        <dbl>
 1 A     CRIS  O              6            5
 2 A     CRIS  WRI            6            5
 3 A     LARI  O              6            5
 4 A     LARI  WRI            6            5
...

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