R语言数据框列合并报错：undefined columns selected问题求助

需要从R的data frame中提取特定列并合并为新数据框：POS1-POS12列存储1-12的数字，对应s1-s12列的数值，要将二者合并为12列，填入对应s列的实际值而非引用。此前可用的代码现在报错：

Error in h(simpleError(msg, call)) : error in evaluating the argument 'x' in selecting a method for function 'as.matrix': undefined columns selected

推测是R无法找到全部1-12列，但不清楚原因。以下是代码及可复现问题的数据子集：

data_seq <- data[ which(data$MODE=='sequential'),]

data_seq = data_seq[,c(1:25, 26,29,32,35,38,41,44,47,50,53,56,59)] #remove unnecessary columns

data_test1 = data_seq[,c(1,10, 11:23, 36, 37)] #remove unnecessary columns

data_test1 = data_test1[,c(3:17, 1:2)]

data_test1$VP = as.numeric(data_test1$VP)

#grep("POS",colnames(data_seq))

data_test_pos <- data_seq[,26:37]
data_test2    <- data_seq[,11:22]

xxx<-data.frame()

for (t in 1:nrow(data_test1)) {
  xxx[t,1:12]<-as.matrix(data_test2[t,as.matrix(data_test_pos[t,])])
}

data_test_recency <- cbind(data_seq,xxx)

可复现问题的数据子集：

structure(list(VP = structure(c(1L, 1L, 1L, 1L, 1L), levels = c("1", 
"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", 
"20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "3", 
"30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "4", 
"40", "41", "42", "43", "44", "45", "46", "5", "6", "7", "8", 
"9", "VP"), class = "factor"), BLOCK = c("2", "2", "2", "2", 
"2"), TRIAL = c("1", "2", "3", "4", "5"), SEX = c("1", "1", "0", 
"1", "1"), MODE = structure(c(2L, 2L, 2L, 2L, 2L), levels = c("parallel", 
"sequential"), class = "factor"), OUTL = structure(c(1L, 2L, 
1L, 1L, 1L), levels = c("no", "yes"), class = "factor"), PRS = c("2", 
"12", "5", "10", "7"), DUR = c("1200", "1200", "1200", "1200", 
"1200"), L_MEAN = c("-3", "18", "13", "-24", "23"), G_MEAN = c(-3, 
11, 13, -24, 23), s1 = c("-8", "18", "6", "-26", "28"), s2 = c("-5", 
"14", "20", "-17", "21"), s3 = c("3", "22", "10", "-23", "23"
), s4 = c("-2", "14", "18", "-28", "29"), s5 = c("-5", "18", 
"8", "-20", "23"), s6 = c("-6", "25", "19", "-19", "26"), s7 = c("2", 
"17", "13", "-28", "23"), s8 = c("0", "16", "11", "-30", "19"
), s9 = c("2", "18", "9", "-26", "17"), s10 = c("-6", "22", "15", 
"-25", "25"), s11 = c("-5", "-30", "20", "-16", "18"), s12 = c("-4", 
"-26", "6", "-25", "20"), RESP = c(-5, 17, 12, -24, 22), RT = c("1871", 
"2430", "2006", "3955", "3114"), ITI = c("500", "500", "500", 
"500", "500"), POS1 = c("5", "10", "3", "8", "6"), x1 = c("-23", 
"-23", "-29", "-10", "2"), y1 = c("18", "10", "-2", "-12", "11"
), POS2 = c("2", "11", "2", "11", "1"), x2 = c("2", "-22", "-9", 
"-23", "26"), y2 = c("22", "4", "12", "-20", "23"), POS3 = c("10", 
"12", "12", "5", "5"), x3 = c("22", "20", "-14", "21", "28"), 
    y3 = c("4", "6", "-28", "14", "-30"), POS4 = c("7", "3", 
    "11", "12", "3"), x4 = c("9", "7", "-16", "9", "24"), y4 = c("-21", 
    "-25", "-17", "-27", "-19"), POS5 = c("12", "4", "6", "1", 
    "11"), x5 = c("2", "-19", "22", "-20", "12"), y5 = c("-25", 
    "-17", "13", "-28", "-4"), POS6 = c("8", "1", "5", "3", "10"
    ), x6 = c("-19", "15", "5", "3", "2"), y6 = c("26", "-7", 
    "29", "-2", "4"), POS7 = c("6", "5", "1", "6", "4"), x7 = c("11", 
    "-12", "28", "-9", "21"), y7 = c("10", "-17", "13", "13", 
    "-15"), POS8 = c("9", "8", "7", "10", "9"), x8 = c("-6", 
    "-15", "16", "-18", "4"), y8 = c("16", "-29", "-12", "-4", 
    "-23"), POS9 = c("3", "9", "10", "7", "12"), x9 = c("-9", 
    "25", "-30", "-6", "28"), y9 = c("-19", "10", "17", "22", 
    "6"), POS10 = c("4", "2", "4", "4", "7"), x10 = c("15", "-9", 
    "-7", "-1", "21"), y10 = c("30", "0", "13", "7", "1"), POS11 = c("11", 
    "6", "8", "9", "8"), x11 = c("-26", "6", "7", "-27", "-8"
    ), y11 = c("0", "-29", "-17", "-21", "26"), POS12 = c("1", 
    "7", "9", "2", "2"), x12 = c("14", "-26", "-4", "-24", "3"
    ), y12 = c("0", "28", "-24", "-24", "18"), STRT_et = c("2326169", 
    "2332032", "2338451", "2344444", "2352388"), STRT_pr0 = c("429848", 
    "435711", "442129", "448122", "456065"), STRT_pr1 = c("429848", 
    "435711", "442129", "448122", "456065")), row.names = 41:45, class = "data.frame")

解决方案 #

错误原因 #

• POS列类型不匹配：POS1-POS12列存储的是字符型值（如"5"），用这些值索引data_test2时，R会尝试匹配列名而非列的位置索引，但data_test2的列名是s1-s12，不存在名为"5"的列，因此报错。
• 循环赋值效率低且易出错：逐行循环的方式不仅运行慢，还容易因类型转换问题导致赋值失败。

修正后的代码 #

# 保留原始筛选步骤
data_seq <- data[which(data$MODE == 'sequential'), ]
data_seq <- data_seq[, c(1:25, 26,29,32,35,38,41,44,47,50,53,56,59)]

# 精准提取POS列和s列，避免硬编码索引
data_test_pos <- data_seq[, grep("POS", colnames(data_seq))]
data_test2 <- data_seq[, grep("^s\\d+$", colnames(data_seq))]

# 将POS列转为数值型，确保索引时识别为列位置
data_test_pos <- apply(data_test_pos, 2, as.numeric)

# 构造矩阵索引，批量提取对应值
row_idx <- rep(1:nrow(data_test2), each = 12)
col_idx <- as.vector(data_test_pos)
xxx <- matrix(data_test2[cbind(row_idx, col_idx)], ncol = 12, byrow = TRUE)
xxx <- as.data.frame(xxx)
colnames(xxx) <- paste0("mapped_s", 1:12)  # 给新列命名

# 合并到原数据框
data_test_recency <- cbind(data_seq, xxx)

代码说明 #

• 用grep提取列：避免硬编码列索引，确保即使数据列顺序变化也能正确提取POS和s列。
• POS列转数值型：将字符型POS值转为数值，让R识别为列的位置索引（1-12），而非列名。
• 矩阵索引批量处理：通过构造行和列的索引矩阵，一次性提取所有对应值，比循环更高效稳定。
• 新列命名：给合并后的12列添加明确名称，方便后续数据识别与处理。

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