R语言将含被试内/被试间变量的宽DataFrame转为长表

算力阿峰

2026-9-26

问题背景

我正尝试从Excel表格构建长结构数据集，包含2个被试间变量和2个被试内变量，当前数据集结构如下：

> str(Subset_0)         
'data.frame':   54 obs. of  11 variables:
 $ Subject : num  1 2 3 4 5 6 7 8 9 10 ...
 $ BETWEEN1: num  1 1 1 2 2 2 2 1 1 2 ...
 $ BETWEEN2: num  1 1 2 2 2 2 1 1 1 1 ...
 $ A_x1    : num  5 1 3 1 0 6 1 2 7 1 ...
 $ B_x2    : num  5 1 3 0 3 0 0 2 6 1 ...
 $ C_y1    : num  6 9 9 2 2 4 2 2 6 0 ...
 $ D_y2    : num  6 15 4 1 2 4 3 1 3 0 ...
 $ K_x1    : num  5 1 3 1 0 6 1 2 7 1 ...
 $ L_x2    : num  5 1 3 0 3 0 0 2 6 1 ...
 $ M_y1    : num  6 9 9 2 2 4 2 2 6 14 ...
 $ N_y2    : num  3 1 0 4 0 5 6 5 17 21 ...

dput导出的数据集结构如下：

structure(list(Subject = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 
12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55), BETWEEN1 = c(1, 
1, 1, 2, 2, 2, 2, 1, 1, 2, 2, 2, 2, 1, 2, 1, 2, 1, 1, 2, 2, 1, 
2, 1, 2, 1, 1, 2, 1, 1, 2, 1, 1, 2, 1, 2, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), BETWEEN2 = c(1, 
1, 2, 2, 2, 2, 1, 1, 1, 1, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 
1, 1, 2, 2, 1, 1, 2, 2, 1, 1, 2, 2, 2, 2, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), A_x1 = c(5, 
1, 3, 1, 0, 6, 1, 2, 7, 1, 1, 0, 0, 2, 0, 8, NA, NA, NA, NA, 
14, 23, 19, 10, 9, 10, 11, 14, 16, 8, 24, 17, 8, 22, 14, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA), B_x2 = c(5, 1, 3, 0, 3, 0, 0, 2, 6, 1, 0, 0, 0, 0, 1, 
7, 14, 23, 19, 10, 14, 29, 15, 7, 13, 16, 7, 9, 17, 6, 7, 16, 
6, 11, 13, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), C_y1 = c(6, 9, 9, 2, 2, 4, 2, 2, 6, 
0, 6, 0, 1, 10, 3, 8, 14, 29, 15, 7, 17, 21, 24, 7, 32, 31, 31, 
21, 27, 29, 18, 27, 33, 23, 28, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), D_y2 = c(6, 15, 
4, 1, 2, 4, 3, 1, 3, 0, 0, 0, 2, 2, 2, 5, 17, 21, 24, 7, 24, 
16, 28, 7, 28, 23, 25, 25, 24, 28, 33, 27, 31, 33, 21, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA), K_x1 = c(5, 1, 3, 1, 0, 6, 1, 2, 7, 1, 1, 0, 0, 2, 0, 8, 
24, 16, 28, 7, 24, 31, 31, 13, 32, 35, 32, 22, 29, 32, 32, 29, 
34, 32, 34, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), L_x2 = c(5, 1, 3, 0, 3, 0, 0, 2, 6, 
1, 0, 0, 0, 0, 1, 7, 24, 31, 31, 13, 30, 30, 34, 12, 31, 27, 
23, 25, 33, 28, 31, 29, 30, 36, 24, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), M_y1 = c(6, 
9, 9, 2, 2, 4, 2, 2, 6, 14, 23, 19, 10, 9, 10, 11, 14, 16, 8, 
24, 17, 8, 22, 14, 33, 28, 31, 14, 23, 19, 10, 9, 10, 11, 14, 
16, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA), N_y2 = c(3, 1, 0, 4, 0, 5, 6, 5, 17, 21, 24, 7, 
32, 31, 31, 21, NA, NA, NA, NA, 27, 29, 18, 27, NA, NA, 17, 21, 
24, 7, 32, 31, 31, 21, 27, 17, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, 
-54L))

转换需求

需要按被试和条件拆分：A、B、C、D的值合并为一列命名为First，K、L、M、N的值合并为另一列命名为Second；变量名中的x、y对应被试内因子，存入新列Within1，_1、_2对应另一被试内因子，存入新列Within2，同时保留被试间因子列Between1和Between2，目标结构如下：

Subject First  Second  Within1  Within2 Between1 Between2
1         Ai     Ki        x        1          1        1 
1         Bi     Li        x        2          1        1 
1         Ci     Mi        y        1          1        1      
1         Di     Ni        y        2          1        1 
2         Ai     Ki        x        1          1        1 
2         Bi     Li        x        2          1        1 
2         Ci     Mi        y        1          1        1 
2         Di     Ni        y        2          1        1 
...

已完成操作与问题

我已经调用reshape函数完成了First列的合并和被试内变量的拆分，代码如下：

Subset_1 <-reshape(Subset_0, 
                   varying = c("A_x1", " B_x2", "C_y1", "D_y2"),
                   v.names = "First",
                   timevar = "Within1",
                   times =  c("A_x1", " B_x2", "C_y1", "D_y2"),
                   direction = "long")

# Next_Trial_Choice column
Subset_1$Within1[Subset_1$Within1== "A_x1"] <- "x"
Subset_1$Within1[Subset_1$Within1== "B_x2"] <- "x"
Subset_1$Within1[Subset_1$Within1== "C_y1"] <- "y"
Subset_1$Within1[Subset_1$Within1== "D_y2"] <- "y"

#cleaning the names - opponent column
Subset_1$Within2[Subset_1$Within2== "A_x1"] <- "1"
Subset_1$Within2[Subset_1$Within2== "B_x2"] <- "2"
Subset_1$Within2[Subset_1$Within2== "C_y1"] <- "1"
Subset_1$Within2[Subset_1$Within2== "D_y2"] <- "2"

现在我需要对Second列做同样的转换，对Subset_1再次调用reshape函数无法得到预期结果，请问有什么方法可以实现需求？

解决方法

方案1：基础R单次reshape完成转换

你不需要分两次调用reshape，这个函数本身支持同时处理多组测量值列，直接指定两组varying参数即可完成First和Second列的同步转换，避免多次reshape的行列匹配问题：

# 按顺序对应First和Second的原始列
Subset_final <- reshape(Subset_0,
                 varying = list(
                   c("A_x1", "B_x2", "C_y1", "D_y2"),
                   c("K_x1", "L_x2", "M_y1", "N_y2")
                 ),
                 v.names = c("First", "Second"),
                 timevar = "origin_col",
                 times = c("A_x1", "B_x2", "C_y1", "D_y2"),
                 direction = "long")

# 批量提取被试内因子，不需要逐行匹配赋值
Subset_final$Within1 <- ifelse(grepl("x", Subset_final$origin_col), "x", "y")
Subset_final$Within2 <- ifelse(grepl("_1", Subset_final$origin_col), "1", "2")

# 按需保留列、按被试排序
Subset_final <- Subset_final[,c("Subject", "First", "Second", "Within1", "Within2", "BETWEEN1", "BETWEEN2")]
Subset_final <- Subset_final[order(Subset_final$Subject),]

方案2：tidyverse方案（代码更简洁易维护）

如果允许使用第三方包，用pivot_longer实现逻辑更清晰，不需要记忆reshape的复杂参数：

library(tidyr)
library(dplyr)
library(stringr)

Subset_final <- Subset_0 %>%
  # 保留ID列和被试间变量，其余列全部转长
  pivot_longer(cols = -c(Subject, BETWEEN1, BETWEEN2),
               names_to = "col_name",
               values_to = "val") %>%
  # 拆分列名提取分组信息
  mutate(group = substr(col_name, 1, 1),
         Within1 = str_extract(col_name, "[xy]"),
         Within2 = str_extract(col_name, "[12]")) %>%
  # 把ABCD和KLMN组的值分别转成First和Second列
  pivot_wider(names_from = group, values_from = val) %>%
  rename(First = A, Second = K) %>%
  # 保留需要的列、排序
  select(Subject, First, Second, Within1, Within2, BETWEEN1, BETWEEN2) %>%
  arrange(Subject)

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

R语言将含被试内/被试间变量的宽DataFrame转为长表

转换需求 #

已完成操作与问题 #

方案1：基础R单次reshape完成转换 #

方案2：tidyverse方案（代码更简洁易维护） #

转换需求

已完成操作与问题

方案1：基础R单次reshape完成转换

方案2：tidyverse方案（代码更简洁易维护）