在R DataFrame中添加新行、合并年龄组及计算总计的问题

问题详情 #

原始数据 #

df <- data.frame(Var=c("Var1", "Var1","Var1","Var1","Var1","Var1"),
             measure=c("M1","M1","M1","M1","M1","M1"),
             grType = c("Sex","Sex","Agegr","Agegr","Agegr","Agegr"),
             gr=c('Male', 'Female', '<20', '20-39', '40-69', '70+'),
             dem=c(39, 42, 11, 19, 22, 29),
             num=c(22, 19, 3, 11, 15, 12))

目标需求 #

需要添加总计行，并将原有年龄组重编码为<40和40+，最终输出如下：

df <- data.frame(Var=c("Var1", "Var1","Var1","Var1","Var1","Var1","Var1","Var1","Var1"),
             measure=c("M1","M1","M1","M1","M1","M1","M1","M1","M1"),
             grType = c("Sex","Sex","Agegr","Agegr","Agegr","Agegr","newAgegr","newAgegr", "Total"),
             gr=c('Male', 'Female', '<20', '20-39', '40-69', '70+', "<40", "40+", "Total"),
             dem=c(39, 42, 11, 19, 22, 29, 30, 49, 81),
             num=c(22, 19, 3, 11, 15, 12,14,27,41))

尝试代码及报错 #

尝试了以下代码但运行失败：

df <- rbind(df, c("Var1", "M1", "Agegr", "<40", sum(df[df$gr %in% c('<20', '20-39'), "dem"]), sum(df[df$gr %in% c('<20', '20-39'), "num"])))

df <- rbind(df, c("Var1", "M1", "newAgegr", "40+", df[df$grType == "Total", "dem"] - df[df$grType == "Agegr", "dem"], df[df$grType == "Total", "num"] - df[df$grType == "Agegr", "num"]))

df <- rbind(df, c("Var1", "M1", "Total", "Total", sum(df[df$grType %in% c("Sex", "newAgegr"), "dem"]), sum(df[df$grType %in% c("Sex", "newAgegr"), "num"])))

报错信息：

Error in sum(df[df$gr %in% c("<20", "20-39"), "dem"]) :
invalid 'type' (character) of argument

问题分析与解决方案 #

错误原因 #

1. 数据类型被强制转换：用c()构造行再执行rbind，会把所有元素转为字符型，导致dem和num列从数值型变成字符型，后续sum操作自然报错。
2. 逻辑顺序错误：计算40+行时依赖还未生成的Total行，逻辑闭环不成立。

方案1：使用dplyr包（推荐） #

dplyr的函数能更清晰地处理数据分组与合并，且不会破坏数据类型：

library(dplyr)

# 原始数据
df <- data.frame(Var=c("Var1", "Var1","Var1","Var1","Var1","Var1"),
                 measure=c("M1","M1","M1","M1","M1","M1"),
                 grType = c("Sex","Sex","Agegr","Agegr","Agegr","Agegr"),
                 gr=c('Male', 'Female', '<20', '20-39', '40-69', '70+'),
                 dem=c(39, 42, 11, 19, 22, 29),
                 num=c(22, 19, 3, 11, 15, 12))

# 生成新年龄组汇总行
new_age_rows <- df %>%
  filter(grType == "Agegr") %>%
  mutate(new_gr = case_when(
    gr %in% c("<20", "20-39") ~ "<40",
    gr %in% c("40-69", "70+") ~ "40+"
  )) %>%
  group_by(Var, measure, new_gr) %>%
  summarise(dem = sum(dem), num = sum(num), .groups = "drop") %>%
  rename(gr = new_gr) %>%
  mutate(grType = "newAgegr")

# 生成总计行
total_row <- df %>%
  summarise(
    Var = first(Var),
    measure = first(measure),
    grType = "Total",
    gr = "Total",
    dem = sum(dem),
    num = sum(num)
  )

# 合并所有数据
final_df <- bind_rows(df, new_age_rows, total_row)

# 查看结果
print(final_df)

方案2：基础R实现 #

如果不想加载额外包，可以用基础R构造数据框行，确保数据类型一致：

# 原始数据
df <- data.frame(Var=c("Var1", "Var1","Var1","Var1","Var1","Var1"),
                 measure=c("M1","M1","M1","M1","M1","M1"),
                 grType = c("Sex","Sex","Agegr","Agegr","Agegr","Agegr"),
                 gr=c('Male', 'Female', '<20', '20-39', '40-69', '70+'),
                 dem=c(39, 42, 11, 19, 22, 29),
                 num=c(22, 19, 3, 11, 15, 12),
                 stringsAsFactors = FALSE)

# 构造<40行
row_under40 <- data.frame(
  Var = "Var1",
  measure = "M1",
  grType = "newAgegr",
  gr = "<40",
  dem = sum(df$dem[df$gr %in% c("<20", "20-39")]),
  num = sum(df$num[df$gr %in% c("<20", "20-39")]),
  stringsAsFactors = FALSE
)

# 构造40+行
row_over40 <- data.frame(
  Var = "Var1",
  measure = "M1",
  grType = "newAgegr",
  gr = "40+",
  dem = sum(df$dem[df$gr %in% c("40-69", "70+")]),
  num = sum(df$num[df$gr %in% c("40-69", "70+")]),
  stringsAsFactors = FALSE
)

# 构造总计行
row_total <- data.frame(
  Var = "Var1",
  measure = "M1",
  grType = "Total",
  gr = "Total",
  dem = sum(df$dem),
  num = sum(df$num),
  stringsAsFactors = FALSE
)

# 合并数据
final_df <- rbind(df, row_under40, row_over40, row_total)

# 查看结果
print(final_df)

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