如何在缺失ID的观测值中为Number列填充0

现有一个包含ID、Number、Location、Surveyor列的数据框：

• ID包含a-t的字母编码（实际数据为其他编码）
• Location 1包含所有ID编码，但其他Location（实际共25个）存在部分ID缺失
• 需要为每个Location中缺失的ID对应的Number列填充0，曾尝试用tidyverse::mutate结合case_when但未成功

示例数据的dput输出：

structure(list(ID = c("a", "b", "c", "d", "e", "f", "g", "h", 
"i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "b", 
"c", "d", "e", "f", "j", "k", "n", "m", "q", "r"), Number = c(1, 
2, 1, 3, 4, 1, 1, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 
1, 2, 1, 1, 1, 1, 1, 2, 2), Location = c(1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 
2, 2, 2), Surveyor = c("JKK", "JKK", "JKK", "JKK", "JKK", "JKK", 
"JKK", "JKK", "JKK", "JKK", "JKK", "JKK", "JKK", "JKK", "JKK", 
"JKK", "JKK", "JKK", "JKK", "JKK", "JKK", "JKK", "JKK", "JKK", 
"JKK", "JKK", "JKK", "JKK", "JKK", "JKK", "JKK")), row.names = c(NA, 
-31L), spec = structure(list(cols = list(ID = structure(list(), class = c("collector_character", 
"collector")), Number = structure(list(), class = c("collector_double", 
"collector")), Location = structure(list(), class = c("collector_double", 
"collector")), Surveyor = structure(list(), class = c("collector_character", 
"collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), delim = ","), class = "col_spec"), problems = <pointer: 0x00000253510611f0>, class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"))

使用tidyverse中的complete()函数可以直接生成所有Location与ID的组合，再填充缺失值，这比mutate+case_when更适合——后者只能修改现有行，无法生成缺失ID对应的新行。

步骤1：提取完整ID列表 #

从包含所有ID的Location 1中提取唯一ID：

library(tidyverse)

full_ids <- df %>% 
  filter(Location == 1) %>% 
  pull(ID) %>% 
  unique()

步骤2：补全所有Location-ID组合并填充0 #

用complete()扩展数据框，指定fill参数将缺失的Number设为0，再补全Surveyor列：

df_complete <- df %>%
  # 生成每个Location与所有ID的组合，缺失的Number填0
  complete(Location, ID = full_ids, fill = list(Number = 0)) %>%
  # 按Location分组后双向填充Surveyor（适配示例中同Location下Surveyor一致的情况）
  group_by(Location) %>%
  fill(Surveyor, .direction = "downup") %>%
  ungroup()

补充说明 #

如果实际数据中同一Location存在多个Surveyor，可先提取Location与Surveyor的对应关系，再通过left_join补全，而不是用fill()。

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