R语言重塑dataframe：将指定列值转换为列名完成格式转换

问题背景 #

现有如下结构的皮电分段统计数据框：

df <- structure(list(Segment.Number = c("Start Event", "Start Time", 
"End Event", "End Time", "Segment Duration", "Total SCRs", "ER-SCRs", 
"NS-SCRs", "Tonic SCL", "Mean SC", "Tonic Period"), X1 = c("time.txt:start pressed (F1):BL1", 
"60", "time.txt:start pressed (F1):BL2", "200", "140", "27", 
"0", "27", "16.877020827457564", "17.325101513639225", "80.45693848354793"
), X2 = c("time.txt:start pressed (F1):F1", "215", "time.txt:start pressed (F1):F2", 
"515", "300", "68", "1", "67", "18.507943774797333", "19.012163892375462", 
"165.33022014676453"), X3 = c("time.txt:start pressed (F1):W1", 
"2040", "time.txt:start pressed (F1):W2", "2940", "900", "155", 
"0", "155", "22.1224503921822", "22.600699854723032", "546.20937986219167"
), Path = c("Code1", "Code1", "Code1", "Code1", "Code1", "Code1", 
"Code1", "Code1", "Code1", "Code1", "Code1")), row.names = c(NA, 
-11L), class = "data.frame")

需要将Segment.Number列的行值转为新数据框的列名，其余观测值按对应位置匹配到正确列下。

转换失败原因 #

直接使用gather/spread（或新版tidyr的pivot_longer/pivot_wider）无法得到正确结果，核心原因是原始数据为横向转置存储结构：字段名全部存在第一列的行值中，X1/X2/X3是三个独立分段的观测值，Path为全表公共属性，不符合常规长宽表转换要求的“名值对存储”结构。

可行实现方法 #

方法1：基础R实现（无第三方依赖） #

核心逻辑是直接转置观测列，再设置列名、修正字段类型：

# 提取X开头的观测列做转置
obs_cols <- df[, startsWith(names(df), "X")]
df_new <- as.data.frame(t(obs_cols))
# 将Segment.Number的取值设为新表列名
colnames(df_new) <- df$Segment.Number
# 补充分段ID、公共Path字段
df_new$Segment_ID <- rownames(df_new)
df_new$Path <- unique(df$Path)
# 重置行名
rownames(df_new) <- NULL
# 将数值类字段从字符型转为数值型
num_vars <- c("Start Time", "End Time", "Segment Duration", "Total SCRs",
              "ER-SCRs", "NS-SCRs", "Tonic SCL", "Mean SC", "Tonic Period")
df_new[num_vars] <- lapply(df_new[num_vars], as.numeric)

方法2：tidyverse实现（可读性更强） #

通过两步长宽转换实现，逻辑更易维护：

library(tidyverse)
# 提取公共Path值
path_const <- unique(df$Path)
df_new <- df %>%
  # 先将X1/X2/X3三个观测列转成长表
  pivot_longer(cols = starts_with("X"), names_to = "Segment_ID", values_to = "val") %>%
  # 再将Segment.Number的取值展开为列
  pivot_wider(names_from = Segment.Number, values_from = val) %>%
  # 补充Path字段，转换数值类型
  mutate(
    Path = path_const,
    across(c(`Start Time`, `End Time`, `Segment Duration`, `Total SCRs`,
             `ER-SCRs`, `NS-SCRs`, `Tonic SCL`, `Mean SC`, `Tonic Period`),
           as.numeric)
  )

转换结果说明 #

转换后最终得到3行13列的规范数据框：

• 每一行对应原表的一个X列（即一个实验分段：BL/F/W阶段）
• 列名完全采用Segment.Number的所有取值，单元格取值和原表位置一一对应
• 公共字段Path正确保留为Code1，数值类字段已转换为可计算的数值类型

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