R语言中基于动态列名替换Attribute类列值的实现问题

现有如下R数据框dt：

dt <- data.frame(
  session_id = c("17472631", "17472632", "17472633", "17472635", "17472636", "17472638"),
  Attractiveness_Real = c(-2, -2, 1, -1, 3, 1),
  Calmness_Real = c(-3, 2, 3, 2, 3, -2),
  Generosity_Real = rep(c(1, 3, 2), each = 2L),
  Arrogance_Real = c(-2, 1, -2, -3, 3, 2),
  Ambition_Real = c(-1, 1, -2, 2, 3, 1),
  Charisma_Real = c(-1, -3, -3, 2, 2, 2),
  Intelligence_Real = c(3, 2, 3, 2, 2, 2),
  Joyfulness_Real = c(3, -3, 1, 3, 2, -2),
  Friendliness_Real = c(-3, -1, -3, -2, -1, -3),
  Laziness_Real = c(-1, -3, NA, -2, NA, -3),
  Intelligence_Retreived = c(2, 1, NA, NA, NA, 3),
  Laziness_Retreived = rep(c(-3, NA), c(1L, 5L)),
  Attractiveness_Retreived = c(-2, NA, NA, NA, NA, 3),
  Generosity_Retreived = c(-2, 2, 3, 2, NA, 3),
  Friendliness_Retreived = c(NA, 2, -2, NA, -2, 2),
  Arrogance_Retreived = c(NA, NA, NA, -1, NA, 1),
  Calmness_Retreived = c(-2, NA, -1, NA, 1, 3),
  Charisma_Retreived = c(1, NA, NA, 3, -1, 3),
  Joyfulness_Retreived = c(NA, NA, NA, 3, NA, 3),
  Ambition_Retreived = c(NA, 1, 2, NA, 2, 2),
  RealAttribute_1 = c(
    "Attractiveness", "Charisma", "Friendliness", "Joyfulness", "Joyfulness",
    "Friendliness"
  ),
  RealAttribute_2 = c("Calmness", "Joyfulness", "Generosity", "Charisma", "Laziness", "Arrogance"),
  RealAttribute_3 = c("Generosity", "Attractiveness", "Arrogance", "Calmness", "Charisma", "Charisma"),
  RealAttribute_4 = c("Arrogance", "Generosity", "Charisma", "Friendliness", "Friendliness", "Ambition"),
  RealAttribute_5 = c("Ambition", "Calmness", "Ambition", "Laziness", "Arrogance", "Intelligence"),
  RealAttribute_6 = c("Charisma", "Ambition", "Calmness", "Ambition", "Generosity", "Generosity"),
  RealAttribute_7 = c(
    "Intelligence", "Friendliness", "Intelligence", "Attractiveness",
    "Attractiveness", "Laziness"
  ),
  RealAttribute_8 = c("Joyfulness", "Arrogance", "Joyfulness", "Arrogance", "Ambition", "Attractiveness"),
  RealAttribute_9 = c(
    "Friendliness", "Laziness", "Attractiveness", "Generosity", "Intelligence",
    "Joyfulness"
  ),
  RealAttribute_10 = c("Laziness", "Intelligence", "Laziness", "Intelligence", "Calmness", "Calmness"),
  RetrievedAttribute_1 = c(
    "Intelligence", "Intelligence", "Generosity", "Joyfulness", "Laziness",
    "Intelligence"
  ),
  RetrievedAttribute_2 = c("Laziness", "Joyfulness", "Ambition", "Arrogance", "Intelligence", "Ambition"),
  RetrievedAttribute_3 = c("Attractiveness", "Laziness", "Arrogance", "Charisma", "Calmness", "Generosity"),
  RetrievedAttribute_4 = rep(c("Generosity", "Arrogance", "Friendliness"), 2),
  RetrievedAttribute_5 = c("Friendliness", "Calmness", "Arrogance", NA, "Laziness", "Laziness"),
  RetrievedAttribute_6 = c("Arrogance", "Arrogance", "Calmness", NA, "Arrogance", "Charisma"),
  RetrievedAttribute_7 = c("Calmness", "Calmness", NA, NA, "Intelligence", "Attractiveness"),
  RetrievedAttribute_8 = c("Charisma", "Ambition", NA, NA, "Ambition", "Arrogance"),
  RetrievedAttribute_9 = c("Arrogance", "Friendliness", NA, NA, "Friendliness", "Joyfulness"),
  RetrievedAttribute_10 = c("Friendliness", "Generosity", NA, NA, "Charisma", "Calmness"),
  RankedAttribute_1 = c(
    "Intelligence", "Intelligence", "Arrogance", "Generosity", "Laziness",
    "Intelligence"
  ),
  RankedAttribute_2 = c("Laziness", "Generosity", "Friendliness", "Intelligence", "Charisma", "Calmness"),
  RankedAttribute_3 = c("Charisma", "Laziness", NA, "Gloomy/Joyful", "Intelligence", "Laziness"),
  RankedAttribute_4 = c("Friendliness", "Calmness", NA, "Charisma", "Attractiveness", "Charisma"),
  RankedAttribute_5 = c("Ambition", "Friendliness", NA, NA, "Friendliness", "Attractiveness"),
  RankedAttribute_6 = c("Arrogance", "Attractiveness", NA, NA, NA, "Generosity"),
  RankedAttribute_7 = c(NA, "Arrogance", NA, NA, NA, NA),
  RankedAttribute_8 = rep(NA_character_, 6L),
  RankedAttribute_9 = rep(NA_character_, 6L),
  RankedAttribute_10 = rep(NA_character_, 6L),
  target = c("Andy", "Daniel", "Brandon", "Jack", "Daniel", "Tyler"),
  rating_target = c("8", "5", "5", "10", "10", "11 - <i>extremely like</i>")
)

需求 #

对所有包含Attribute_的列（RealAttribute_*、RetrievedAttribute_*、RankedAttribute_*）进行重编码：将这些列的每行值替换为以该值开头、以_Real结尾的列对应的行数值。例如，若RetrievedAttribute_1某行的值为"Intelligence"，则将该行替换为Intelligence_Real列的对应行数值。

尝试过的错误方法 #

方法1：使用mutate_at #

dt <- dt %>% mutate_at(vars(contains("Attribute_")), funs(. = !!paste0(., "_Real")))

错误信息：

Error in call2():! .fn must be a string, a symbol, a call, or a function

方法2：循环方法 #

columns_to_recode <- grep("Attribute_", colnames(dt), value = TRUE)
for (col in columns_to_recode) {
  att<-dt[[col]]
  col_name <- paste0(att, "_Real")
  dt[[col]] <- dt[[col_name]]
}

错误信息：

Error in .subset2(x, i, exact = exact) : recursive indexing failed at level 2

可行解决方案 #

方案1：使用dplyr的rowwise+mutate #

逐行处理每个Attribute_列，匹配对应的_Real列值，同时处理不存在的属性名称（如"Gloomy/Joyful"）：

library(dplyr)

# 获取所有需要处理的列
attr_cols <- grep("Attribute_", colnames(dt), value = TRUE)

dt <- dt %>%
  rowwise() %>%
  mutate(across(all_of(attr_cols), ~ {
    if (is.na(.x)) return(NA)
    real_col <- paste0(.x, "_Real")
    # 列存在则取值，否则返回NA
    if (real_col %in% colnames(dt)) pull(cur_data(), all_of(real_col)) else NA
  })) %>%
  ungroup()

方案2：修正循环方法 #

逐行遍历每个元素，避免向量索引的错误：

columns_to_recode <- grep("Attribute_", colnames(dt), value = TRUE)

for (col in columns_to_recode) {
  for (i in seq_len(nrow(dt))) {
    att_val <- dt[i, col]
    if (!is.na(att_val)) {
      real_col <- paste0(att_val, "_Real")
      if (real_col %in% colnames(dt)) {
        dt[i, col] <- dt[i, real_col]
      } else {
        dt[i, col] <- NA
      }
    }
  }
}

方案3：使用purrr向量化处理 #

用purrr工具包实现批量列处理：

library(purrr)

attr_cols <- grep("Attribute_", colnames(dt), value = TRUE)

dt[attr_cols] <- map_dfc(dt[attr_cols], function(col) {
  imap_dbl(col, function(val, idx) {
    if (is.na(val)) NA else {
      real_col <- paste0(val, "_Real")
      if (real_col %in% colnames(dt)) dt[idx, real_col] else NA
    }
  })
})

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