使用pivot_longer处理多列数据的格式转换及异常解决

需求与问题 #

需要将两类列转换为长格式：

• 以crt_开头的列（如crt_ppara_01、crt_ppard_03）：拆分出基因名（gen列）、时间标识（time列），对应值存入Ct列
• 以peso_开头的列（如peso_1、peso_3）：拆分出时间标识（time列），对应值存入peso列

尝试两步pivot_longer或正则表达式方法均失败，处理数据库时还出现time列值为NA、行重复的异常。

示例数据 #

df <- structure(list(id = c(140102089, 70802013, 60901027, 60901020, 
120715021, 70802015, 140103026, 60901026, 140101097, 60901028
), crt_ppara_01 = c(21.456, 19.785, 20.889, NA, NA, 19.395, 18.783, 
18.802, 19.999, 20.455), crt_ppard_01 = c(18.085, 16.623, 18.037, 
16.744, 16.59, 17.335, 15.53, 17.096, 16.916, 18.512), crt_ppara_03 = c(19.518, 
20.76, 20.642, NA, 18.123, 20.799, 19.817, NA, 20.838, 20.251
), crt_ppard_03 = c(16.461, 17.755, 17.516, 17.176, 15.626, 18.172, 
15.795, 16.582, 17.571, 17.499), peso_1 = c(70, 78.2, 91.2, 84.3, 
76.2, 100, 81.4, 82.2, 65.1, 108), peso_3 = c(69.7, 71.7, 86.9, 
83.7, 76, 100, 80, 77, 64, 116.9)), row.names = c(NA, -10L), class = "data.frame")

正确解决方案 #

方法一：拆分处理后合并 #

分别处理两类列，再通过id和time匹配合并，避免笛卡尔积：

library(tidyr)
library(dplyr)

# 处理crt列，提取gen、time和Ct值
crt_long <- df %>%
  pivot_longer(
    cols = starts_with("crt_"),
    names_to = c("gen", "time"),
    names_pattern = "crt_(.+)_0?(\\d+)", # 兼容_01和_1格式的时间标识
    values_to = "Ct"
  ) %>%
  mutate(time = as.integer(time)) # 统一time为整数格式

# 处理peso列，提取time和peso值
peso_long <- df %>%
  pivot_longer(
    cols = starts_with("peso_"),
    names_to = "time",
    names_pattern = "peso_(\\d+)",
    values_to = "peso"
  ) %>%
  mutate(time = as.integer(time))

# 合并结果，保留需要的列顺序
final_df <- crt_long %>%
  inner_join(peso_long, by = c("id", "time")) %>%
  select(id, time, peso, gen, Ct)

方法二：单次pivot_longer结合重塑宽格式 #

通过正则兼容两类列的结构，再转换为目标格式：

df %>%
  pivot_longer(
    cols = -id,
    names_to = c("type", "gen", "time"),
    # 正则匹配：区分crt（带gen）和peso（无gen）的列结构
    names_pattern = "(crt|peso)(?:_(.+))?_0?(\\d+)",
    values_to = "value"
  ) %>%
  # 为peso列填充gen为NA，后续过滤时剔除多余行
  mutate(gen = ifelse(type == "peso", NA, gen)) %>%
  # 按type将value拆分为Ct和peso列
  pivot_wider(
    id_cols = c(id, time, gen),
    names_from = type,
    values_from = value
  ) %>%
  filter(!is.na(gen)) # 仅保留有基因信息的行

异常情况排查 #

使用genes %>% pivot_longer(!c(id, sexo, edad0, grup_int), names_pattern = "(.*)_0*([0-9]+)$")出现time列NA、行重复的原因：

• time列NA：部分列名不匹配正则模式（比如无末尾数字、格式不符合xxx_数字），导致时间标识无法被捕获
• 行重复：正则的捕获组错误匹配了列名中的其他下划线，导致同一列被多次拆分，或生成多余的分组

解决建议：

1. 先筛选目标列：cols = matches("^(crt|peso)_")，确保仅处理需要转换的列
2. 使用更精准的正则，区分不同列的结构（如方法二中的正则）
3. 转换后检查time列，用filter(!is.na(time))剔除无效行

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