如何为各state添加sex不为unknown的numerator、denominator汇总行及计算值？

你可以通过以下两种常见方法实现需求：为每个state添加汇总行，展示非unknown性别对应的数值总和及比值。

首先确认你的数据结构（可直接复制用于测试）：

df <- structure(list(state = c("AL", "AL", "AL", "FL", "FL", "FL"), 
    sex = c(" male", " female", " unknown", " male", " female", 
    " unknown"), numerator = c(10L, 20L, 40L, 10L, 20L, 40L), 
    denominator = c(20L, 30L, 50L, 20L, 30L, 50L), num_divide_denom = c(0.5, 
    0.66, 0.8, 0.5, 0.66, 0.8)), class = "data.frame", row.names = c(NA, 
-6L))

方法1：使用dplyr（tidyverse风格） #

适合习惯tidyverse语法的用户，代码简洁直观：

# 若未安装dplyr，先执行：install.packages("dplyr")
library(dplyr)

# 生成每个state的非unknown汇总行
summary_rows <- df %>%
  filter(sex != " unknown") %>%  # 过滤掉unknown性别
  group_by(state) %>%            # 按state分组
  summarize(
    sex = "total_non_unknown",   # 自定义汇总行的sex标识
    numerator = sum(numerator),  # 求和numerator
    denominator = sum(denominator),  # 求和denominator
    num_divide_denom = numerator / denominator,  # 计算总和比值
    .groups = "drop"  # 取消分组
  )

# 合并原数据与汇总行，并按state排序
result_df <- bind_rows(df, summary_rows) %>%
  arrange(state)

# 查看结果
print(result_df)

方法2：使用Base R #

无需额外安装包，适合原生R用户：

# 过滤掉unknown性别的行
non_unknown_df <- df[df$sex != " unknown", ]

# 按state分组计算numerator和denominator的总和
summary_base <- aggregate(cbind(numerator, denominator) ~ state, data = non_unknown_df, sum)

# 补充sex列和比值列
summary_base$sex <- "total_non_unknown"
summary_base$num_divide_denom <- summary_base$numerator / summary_base$denominator

# 调整列顺序与原数据一致
summary_base <- summary_base[, colnames(df)]

# 合并原数据与汇总行，并按state排序
result_base <- rbind(df, summary_base)
result_base <- result_base[order(result_base$state), ]

# 查看结果
print(result_base)

最终输出示例 #

两种方法都会得到如下结果：

state                sex numerator denominator num_divide_denom
1    AL               male        10          20        0.5000000
2    AL             female        20          30        0.6600000
3    AL            unknown        40          50        0.8000000
4    AL total_non_unknown        30          50        0.6000000
5    FL               male        10          20        0.5000000
6    FL             female        20          30        0.6600000
7    FL            unknown        40          50        0.8000000
8    FL total_non_unknown        30          50        0.6000000

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