按规则分组统计data.table数据，生成分教育水平的q维度统计表格

原数据集：

dt <- data.table(ID = c(1, 2, 3, 4),
                 q1= c(1, 2, 3, 5), 
                 q2= c(3, 5, 2, 4), 
                 q3= c(2, 3, 4, 3),
                 education = c("A", "B", "C", "D"))

统计需求：

• 对q1、q2、q3列按以下规则分组统计ID数量：
  • 数值为1、3、4的归入YES组
  • 数值为1、3的归入maybe组（允许ID被重复统计）
  • 数值为5、2的归入NO组
• 最终生成按education和q维度（q1/q2/q3）划分的统计表格，格式如下：

YES    maybe    NO
q1
q2
q3

步骤1：定义分组映射规则 #

用data.table存储数值与分组的对应关系（处理一个数值对应多个分组的情况）：

group_map <- data.table(
  value = c(1, 1, 3, 3, 4, 2, 5),
  group = c("YES", "maybe", "YES", "maybe", "YES", "NO", "NO")
)

步骤2：宽表转长表 #

将q1/q2/q3列转为长格式，统一处理所有q维度的数据：

dt_long <- melt(dt, id.vars = c("ID", "education"), 
                measure.vars = c("q1", "q2", "q3"),
                variable.name = "q_dim", value.name = "value")

步骤3：关联分组并统计计数 #

通过连接操作匹配分组规则，再按education、q_dim、group维度统计数量：

dt_count <- dt_long[group_map, on = "value"][
  , .(count = .N), by = .(education, q_dim, group)
]

步骤4：转换为目标宽格式 #

把统计结果转换为需求的宽表结构，缺失值用0填充：

result <- dcast(dt_count, education + q_dim ~ group, value.var = "count", fill = 0)

完整代码 #

library(data.table)

# 原数据集
dt <- data.table(ID = c(1, 2, 3, 4),
                 q1= c(1, 2, 3, 5), 
                 q2= c(3, 5, 2, 4), 
                 q3= c(2, 3, 4, 3),
                 education = c("A", "B", "C", "D"))

# 分组映射规则
group_map <- data.table(
  value = c(1, 1, 3, 3, 4, 2, 5),
  group = c("YES", "maybe", "YES", "maybe", "YES", "NO", "NO")
)

# 宽表转长表
dt_long <- melt(dt, id.vars = c("ID", "education"), 
                measure.vars = c("q1", "q2", "q3"),
                variable.name = "q_dim", value.name = "value")

# 关联分组并统计
dt_count <- dt_long[group_map, on = "value"][
  , .(count = .N), by = .(education, q_dim, group)
]

# 转换为目标宽表
result <- dcast(dt_count, education + q_dim ~ group, value.var = "count", fill = 0)

# 输出结果
print(result)

可选：仅按q维度统计 #

如果不需要区分education，修改dcast的公式即可：

result_by_q <- dcast(dt_count, q_dim ~ group, value.var = "count", fill = 0)
print(result_by_q)

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