使用dpplyr按多分组汇总N_count与A_count的总比例

解决方案代码 #

library(dplyr)

# 示例数据
df <- structure(list(P_Common = c("B", "B", "C", "C", "D", "E", "E", 
"F", "G", "G", "B", "G", "E", "D", "F", "C"), Number_7 = c(1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 1L, 3L, 1L, 2L, 1L, 1L), 
    N_count = c(0L, 4L, 22L, NA, 7L, 0L, 44L, 16L, NA, NA, NA, 
    NA, NA, NA, NA, NA), A_count = c(NA, NA, NA, NA, NA, NA, 
    NA, NA, 0L, 4L, 7L, NA, 23L, 4L, 7L, 17L)), class = "data.frame", row.names = c(NA, 
-16L))

# 分组计算比例
result <- df %>%
  group_by(P_Common, Number_7) %>%
  summarise(
    total_N = sum(N_count, na.rm = TRUE),
    total_A = sum(A_count, na.rm = TRUE),
    Propo = total_N / total_A,
    .groups = "drop"
  )

print(result)

代码说明 #

• group_by(P_Common, Number_7)：指定按两个字段进行分组
• summarise()：对每个分组执行汇总操作：
  • total_N = sum(N_count, na.rm = TRUE)：计算每组N_count的总和，自动忽略NA值
  • total_A = sum(A_count, na.rm = TRUE)：计算每组A_count的总和，自动忽略NA值
  • Propo = total_N / total_A：用两组总和计算比例，0、NA、Inf等特殊值会按运算规则保留
• .groups = "drop"：取消分组状态，返回普通数据框格式

输出验证 #

运行代码后得到的结果与预期一致：

# A tibble: 8 × 4
  P_Common Number_7 total_N total_A  Propo
  <chr>       <int>   <int>   <int>  <dbl>
1 B               1       4       7  0.571
2 C               1      22      17  1.29 
3 D               2       7       4  1.75 
4 E               1       0      23  0    
5 E               2      44       0 Inf   
6 F               1       0       7  0    
7 F               2      16       0 Inf   
8 G               3       0       4  0    

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