R语言data.table场景下字符映射后按类别分组求和问题

解决方法（基于data.table实现） #

步骤1：完成字符到数值的映射替换 #

library(data.table)
# 原始数据集
DATA=data.table(STUDENT= c(1,2,3,4),
    DOG_1= c("a","e","a","c"),
    DOG_2= c("a","e","d","b"),
    DOG_3= c("a","d","b","c"),
    CAT_1= c("c","a","d","c"),
    CAT_2= c("c","d","a","b"),
    MOUSE_1= c("d","b","e","b"),
    MOUSE_2= c("c","a","b","e"),
    MOUSE_3= c("a","b","b","e"),
    MOUSE_4= c("b","c","a","d"))

# 构建映射向量
val_map <- c("a" = 1, "b" = 2, "c" = 3, "d" = 4, "e" = 5)
# 替换除STUDENT外所有列的字符为对应数值
cols <- setdiff(names(DATA), "STUDENT")
DATA[, (cols) := lapply(.SD, function(x) val_map[x]), .SDcols = cols]

步骤2：按列名前缀分组求和生成结果 #

# 按前缀计算每个学生的分类总分
DATA[, `:=`(
  DOG_SUM = rowSums(.SD[, grep("^DOG_", names(.SD)), with = FALSE]),
  CAT_SUM = rowSums(.SD[, grep("^CAT_", names(.SD)), with = FALSE]),
  MOUSE_SUM = rowSums(.SD[, grep("^MOUSE_", names(.SD)), with = FALSE])
), by = STUDENT]

# 提取最终结果
result <- DATA[, .(STUDENT, DOG_SUM, CAT_SUM, MOUSE_SUM)]

最终输出结果 #

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