R语言：如何将聚合后的行结果转换为列

问题背景 #

原始数据框df：

age category
12  A
15  B
12  A
13  C
14  B
14  D

核心需求：

1. 用aggregate函数按age统计各分类{A,B,C,D}的出现次数，并将分类转为列，期望输出：

age A B C D
12  2 0 0 0
13  0 0 1 0
14  0 1 0 1
15  0 1 0 0

2. 若原始数据包含其他列（如y、z），需保留这些列的原数值，仅生成计数列x，同时完成上述分类转列的统计。

用户尝试的代码：

agdf <- aggregate(df, by=list(df$age, df$category), FUN=length)

但结果不符合预期，且会将其他列也转为计数值，无法满足需求。

解决方案 #

情况1：仅处理age和category列，生成分类列统计 #

aggregate本身只负责统计分组计数，转宽格式需要配合基础R的reshape或结果拆分操作：

1. 先统计每个age-category组合的计数，同时指定所有分类水平确保0值存在：

# 统计每个年龄下各分类的出现次数，用factor强制包含所有分类水平
count_df <- aggregate(category ~ age, data = df, 
                      FUN = function(x) table(factor(x, levels = c("A","B","C","D"))))

# 将table类型的结果拆分为列，合并到age列
result_df <- cbind(count_df$age, do.call(rbind, count_df$category))
colnames(result_df) <- c("age", "A", "B", "C", "D")

或者用reshape转宽格式的方式：

# 先生成每个age-category的计数
temp_df <- aggregate(list(x = df$category), by = list(age = df$age, category = df$category), FUN = length)
# 转宽格式，fill=0填充缺失的分类计数
result_df <- reshape(temp_df, idvar = "age", timevar = "category", direction = "wide", fill = 0)
# 清理列名并补充缺失分类列
colnames(result_df) <- gsub("x\\.", "", colnames(result_df))
for (cat in c("A","B","C","D")) {
  if (!cat %in% colnames(result_df)) result_df[[cat]] <- 0
}
# 调整列顺序为需求格式
result_df <- result_df[, c("age", "A", "B", "C", "D")]

情况2：原始数据包含其他列（如y、z），需保留原数值 #

假设原始数据框为：

age category y  z
12  A        10 20
15  B        15 25
12  A        10 20
13  C        12 22
14  B        14 24
14  D        14 24

处理步骤：

1. 按情况1的方式生成年龄-分类计数的宽格式数据：

count_temp <- aggregate(list(x = df$category), by = list(age = df$age, category = df$category), FUN = length)
count_wide <- reshape(count_temp, idvar = "age", timevar = "category", direction = "wide", fill = 0)
colnames(count_wide) <- gsub("x\\.", "", colnames(count_wide))
for (cat in c("A","B","C","D")) {
  if (!cat %in% colnames(count_wide)) count_wide[[cat]] <- 0
}

2. 提取每个年龄对应的唯一y、z值（假设同年龄下y、z值一致）：

other_cols <- unique(df[, c("age", "y", "z")])

3. 合并计数列和其他列：

final_df <- merge(other_cols, count_wide, by = "age")
final_df <- final_df[, c("age", "y", "z", "A", "B", "C", "D")]

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