R语言中使用summarise+n()时强制显示缺失组合的零值

要补全每个state-year组合下cat1和cat2的所有9种配对并填充0值，用tidyr::complete配合因子类型的分类变量就能实现，步骤如下：

1. 确保分类变量是因子类型
   先把cat1和cat2转为因子并指定所有可能的水平，这样complete能识别出所有应该存在的配对：

   library(dplyr)
   library(tidyr)
   
   # 替换成你的数据集名称
   your_data <- your_data %>%
     mutate(
       cat1 = factor(cat1, levels = c("A", "B", "C")),
       cat2 = factor(cat2, levels = c("X", "Y", "Z"))
     )
   

2. 分组统计初始计数
   按需求统计各组记录数，注意用.groups = "drop"取消分组状态，避免后续操作出错：

   count_result <- your_data %>%
     group_by(state, year, cat1, cat2) %>%
     summarise(count = n(), .groups = "drop")
   

3. 补全缺失配对并填充0
   使用complete，通过nesting(cat1, cat2)保留cat1和cat2的所有9种固定配对，再按state和year分组补全，缺失的count用0填充：

   final_result <- count_result %>%
     complete(state, year, nesting(cat1, cat2), fill = list(count = 0))
   

测试示例 #

如果需要验证，可以用模拟数据测试：

set.seed(123)
test_data <- tibble(
  state = rep(c("CA", "NY"), each = 10),
  year = rep(2020:2021, each = 5),
  cat1 = sample(c("A", "B", "C"), 20, replace = TRUE, prob = c(0.6, 0.3, 0.1)),
  cat2 = sample(c("X", "Y", "Z"), 20, replace = TRUE, prob = c(0.5, 0.4, 0.1))
)

# 执行上述步骤
test_data <- test_data %>%
  mutate(
    cat1 = factor(cat1, levels = c("A", "B", "C")),
    cat2 = factor(cat2, levels = c("X", "Y", "Z"))
  )

count_test <- test_data %>%
  group_by(state, year, cat1, cat2) %>%
  summarise(count = n(), .groups = "drop")

final_test <- count_test %>%
  complete(state, year, nesting(cat1, cat2), fill = list(count = 0))

# 查看结果
final_test

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