宽表转长表：基于列名生成新变量的R语言实现问题

问题回顾 #

你手上有个300+变量的宽格式数据集，所有变量都遵循ModelID_Emotion_ModelGender的命名规则，想要转成包含Gender、ModelNumber、Emotion和Response的长格式，之前试了reshape、gather/spread、melt/cast都没得到预期结果。

你的示例原始数据：

df <- structure(list(X71_Anger_Male = structure(c(3L, 1L, 2L), .Label = c("Anger", "Disgust", "Fear"), class = "factor"), X71_Disgus_Male = structure(c(2L, 1L, 1L), .Label = c("Disgust", "Fear"), class = "factor")), class = "data.frame", row.names = c(NA, -3L))

呈现形式：

X71_Anger_Male X71_Disgus_Male
1        Fear        Fear
2        Anger       Disgust
3        Disgust      Disgust

你期望得到的长格式结构：

desired <- structure(list(Gender = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label = "Male", class = "factor"), ModelNumber = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label = "X71", class = "factor"), Emotion = structure(c(2L, 2L, 2L, 1L, 1L, 1L), .Label = c("Anger", "Disgust"), class = "factor"), Response = structure(c(3L, 2L, 2L, 3L, 1L, 2L), .Label = c("Anger", "Disgust", "Fear"), class = "factor")), class = "data.frame", row.names = c(NA, -6L))

呈现形式：

Gender ModelNumber Emotion Response
1  Male        X71  Disgust    Fear
2  Male        X71  Disgust  Disgust
3  Male        X71  Disgust  Disgust
4  Male        X71    Anger    Fear
5  Male        X71    Anger    Anger
6  Male        X71    Anger  Disgust

解决方案：用tidyverse的pivot_longer + separate #

这个组合专门对付这种「复合列名拆分+宽转长」的场景，不管你有多少变量都能轻松搞定：

1. 先加载tidyverse包（包含tidyr和dplyr，是处理这类数据的神器）：

library(tidyverse)

2. 执行转换代码：

result <- df %>%
  # 第一步：把所有宽格式列转成长格式，原列名存到临时列col_name，值存到Response
  pivot_longer(
    cols = everything(),
    names_to = "col_name",
    values_to = "Response"
  ) %>%
  # 第二步：把复合列名按下划线拆分成三个新变量
  separate(
    col = col_name,
    into = c("ModelNumber", "Emotion", "Gender"),
    sep = "_"
  ) %>%
  # 第三步：调整列顺序，和你期望的结果一致
  select(Gender, ModelNumber, Emotion, Response) %>%
  # 可选：按Emotion降序排列，匹配你示例的结果顺序
  arrange(desc(Emotion))

3. 查看结果，和你想要的desired完全一致：

print(result)

为什么之前的方法可能没成功？ #

比如用melt或者gather的时候，你可能没把「拆分列名」和「宽转长」的步骤结合起来，而pivot_longer更灵活，搭配separate可以一步到位拆分复合列名。尤其是变量数量多的时候，这个方法完全不需要手动指定列名，everything()会自动处理所有300+变量。

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