如何用dplyr按组汇总并将缺失计数显示为0而非NA？

在过滤掉recruitment_strategy = "Strategy 1"且treatment = "Group1"的组合后，原有汇总代码会出现缺失分组计数显示为NA的情况，且直接在count()中添加.drop=FALSE参数无效，可通过以下两种方法解决：

方法一：将分类变量转为因子并保留所有水平 #

.drop=FALSE仅对因子类型的分组变量生效，因此先把treatment和recruitment_strategy转为包含所有原始水平的因子，再执行汇总并替换NA为0：

library(tidyverse)

# 构造并过滤数据
set.seed(500)
dat <- tibble(
  treatment = sample(c("Group1", "Group2", "Group3"), 100, replace = TRUE),
  recruitment_strategy = sample(c("Strategy 1", "Strategy 2", "Strategy 3", "Strategy 4", "Strategy 5"), 100, replace = TRUE),
  Variable_A = rnorm(100),
  Variable_B = rnorm(100),
  Variable_C = rnorm(100)
)

dat2 <- dat %>% 
  filter(!(recruitment_strategy == "Strategy 1" & treatment == "Group1"))

# 转为因子并保留所有原始水平
dat2_factored <- dat2 %>%
  mutate(
    treatment = factor(treatment, levels = c("Group1", "Group2", "Group3")),
    recruitment_strategy = factor(recruitment_strategy, levels = c("Strategy 1", "Strategy 2", "Strategy 3", "Strategy 4", "Strategy 5"))
  )

# 汇总并替换NA为0
dat2_factored %>%
  inner_join(
    x = count(., treatment, recruitment_strategy, .drop = FALSE) %>% 
      spread(treatment, n) %>%
      mutate(across(c(Group1, Group2, Group3), ~replace_na(., 0))),
    y = count(., recruitment_strategy, name = "Overall_dataset", .drop = FALSE),
    by = "recruitment_strategy"
  ) %>%
  mutate_at(
    .vars = vars(-recruitment_strategy),
    .funs = ~ str_glue("{.} ({scales::percent(. / sum(.), accuracy = 1)})")
  )

方法二：使用complete()补全所有分组组合 #

无需转换变量类型，直接用complete()补全treatment和recruitment_strategy的所有可能组合，并指定缺失值填充为0：

library(tidyverse)

# 构造并过滤数据
set.seed(500)
dat <- tibble(
  treatment = sample(c("Group1", "Group2", "Group3"), 100, replace = TRUE),
  recruitment_strategy = sample(c("Strategy 1", "Strategy 2", "Strategy 3", "Strategy 4", "Strategy 5"), 100, replace = TRUE),
  Variable_A = rnorm(100),
  Variable_B = rnorm(100),
  Variable_C = rnorm(100)
)

dat2 <- dat %>% 
  filter(!(recruitment_strategy == "Strategy 1" & treatment == "Group1"))

# 汇总并补全缺失分组
dat2 %>%
  inner_join(
    x = count(., treatment, recruitment_strategy) %>%
      complete(treatment, recruitment_strategy, fill = list(n = 0)) %>%
      spread(treatment, n),
    y = count(., recruitment_strategy, name = "Overall_dataset"),
    by = "recruitment_strategy"
  ) %>%
  mutate_at(
    .vars = vars(-recruitment_strategy),
    .funs = ~ str_glue("{.} ({scales::percent(. / sum(.), accuracy = 1)})")
  )

说明 #

• 方法一通过因子保留所有可能的分组水平，配合.drop=FALSE确保每个水平都被统计，再用replace_na()将缺失的计数转为0。
• 方法二通过complete()直接生成所有分组组合，fill=list(n=0)自动将缺失的计数设为0，无需额外替换步骤，更简洁。

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