如何用pivot_longer将带问题与权重的宽表转为规范长表？

现有一份以调研受访者唯一标识（uniqueID）为索引的宽表x，表中包含各调研问题的结果列（后缀为bin）及对应权重列（后缀为weight）。希望用pivot_longer一步将其转换为包含type、questions、scores、weights列的长表，无需拆分后再合并。

输入宽表代码 #

x <- tibble::tribble(
  ~uniqueID, ~type, ~name_of_question_bin, ~name_of_question_weight, ~name_of_second_question_bin, ~name_of_second_question_weight,
     12345L,  "AA",                    1L,                      0.3,                           0L,                             0.5,
     67891L,  "AA",                    0L,                      0.7,                           1L,                             0.9,
     23456L,  "BB",                    1L,                     0.95,                           1L,                             0.6,
     78910L,  "BB",                    0L,                     0.98,                           1L,                             0.1
  )

期望输出长表代码 #

tibble::tribble(
  ~type,                    ~questions, ~scores, ~weights,
   "AA",        "name_of_question_bin",      1L,      0.3,
   "AA",        "name_of_question_bin",      0L,      0.7,
   "BB",        "name_of_question_bin",      1L,     0.95,
   "BB",        "name_of_question_bin",      0L,     0.98,
   "AA", "name_of_second_question_bin",      0L,      0.5,
   "AA", "name_of_second_question_bin",      1L,      0.9,
   "BB", "name_of_second_question_bin",      1L,      0.6,
   "BB", "name_of_second_question_bin",      1L,      0.1
  )

已尝试的代码 #

x %>% 
    dplyr::select(ends_with("bin"), type) %>% 
    pivot_longer(!type, names_to = "question", values_to = "scores")

解决方案 #

利用pivot_longer的names_pattern参数，通过正则表达式匹配列名结构，实现一步拆分转换：

library(dplyr)
library(tidyr)

x %>%
  select(-uniqueID) %>%  # 移除不需要的唯一标识列
  pivot_longer(
    cols = -type,
    names_to = c("questions", ".value"),
    names_pattern = "(.*)_(bin|weight)"
  ) %>%
  rename(scores = bin, weights = weight)

关键逻辑说明 #

• names_pattern = "(.*)_(bin|weight)"：将列名拆分为两部分，第一部分是问题的核心名称，第二部分是列类型（bin或weight）；
• names_to = c("questions", ".value")：第一部分存入questions列，第二部分作为新的列名（自动生成bin和weight列）；
• rename步骤将bin和weight重命名为期望的scores和weights。

运行上述代码即可直接得到目标长表，无需拆分后合并操作。

内容的提问来源于stack exchange，提问作者C.Robin