如何在R语言中按组填充数据框缺失值（含缺失月份补全）

我在补全数据框的缺失月份后尝试填充缺失值，这些缺失月份对关联其他数据框至关重要。当前代码无报错，但fill函数未按预期生效，无法补全新增月份对应的customer_id和cust_nm，也无法将缺失的cost1/cost2设为0。

原代码如下：

library(janitor)
library(tidyverse) # 原代码依赖但未显式加载，需补上

df.1 <- tribble(
~customer_id, ~cust_nm  ,~period, ~cost1, ~cost2 ,
'cust1', 'pizzaco', as_date('2022-01-01'), 5, 10,
'cust2', 'sodaco',as_date('2022-03-01'), 5, 10,
'cust1', 'pizzaco', as_date('2022-04-01'), 5, 10,
'cust2', 'sodaco', as_date('2022-05-01'), 5, 10,
)

# 定义需要覆盖的月份
reporting_months  <- data.frame(repmonths = seq.Date(from=ymd('2022-01-01'), ymd('2022-06-01'),by="1 month")) 

cust_list <- df.1 %>% 
  select(customer_id,cust_nm) %>% 
  unique()

for (i in 1:nrow(cust_list)) {
test <- df.1 %>% filter(customer_id == cust_list$customer_id[i]) %>% 
  group_by(customer_id, cust_nm, period) %>% 
  full_join(reporting_months, by = c('period' = 'repmonths')) %>% # 成功补全缺失月份
  fill(c(customer_id, cust_nm), .direction = "down") # 但此步骤无效果
  summarise(cost1 = sum(cost1, na.rm = T),
            cost2 = sum(cost2, na.rm = T),
            total = cost1 + cost2) %>% 
  adorn_totals(where = 'row') 
}

预期输出（以cust2为例）：

customer_id  cust_nm  period       cost1  cost2
cust2        sodaco   2022-01-01    0     0
cust2        sodaco   2022-02-01    0     0
cust2        sodaco   2022-03-01    5     10
cust2        sodaco   2022-04-01    0     0
cust2        sodaco   2022-05-01    5     10
cust2        sodaco   2022-06-01    0     0

• group_by时机错误：在full_join前就按period分组，导致每个分组仅包含单个月份，fill无法跨月份填充客户信息。
• full_join顺序问题：新增的月份行中customer_id和cust_nm为NA，且这些行可能出现在原数据行之前，down方向的fill无法覆盖前面的NA。
• 循环未保存结果：循环中每次生成的test变量会被覆盖，最终仅保留最后一次循环的结果，且未输出或保存。
• 缺失值处理逻辑缺失：sum(cost1, na.rm=T)会把NA转为0，但如果整行都是NA（比如新增的月份），未填充的customer_id会导致summarise丢失这些行。

方法1：用tidyverse管道替代循环（高效推荐） #

无需手动生成客户列表和循环，直接生成所有客户-月份的笛卡尔积，再左连原数据完成填充：

library(tidyverse)
library(janitor)

df.1 <- tribble(
~customer_id, ~cust_nm  ,~period, ~cost1, ~cost2 ,
'cust1', 'pizzaco', as_date('2022-01-01'), 5, 10,
'cust2', 'sodaco',as_date('2022-03-01'), 5, 10,
'cust1', 'pizzaco', as_date('2022-04-01'), 5, 10,
'cust2', 'sodaco', as_date('2022-05-01'), 5, 10,
)

reporting_months <- seq.Date(from=ymd('2022-01-01'), ymd('2022-06-01'), by="1 month")

# 生成所有客户-月份组合，左连原数据并填充缺失值
full_data <- df.1 %>%
  distinct(customer_id, cust_nm) %>%
  crossing(period = reporting_months) %>%
  left_join(df.1, by = c("customer_id", "cust_nm", "period")) %>%
  # 将cost的NA替换为0
  mutate(across(c(cost1, cost2), ~replace_na(.x, 0))) %>%
  # 可选：添加总计行
  adorn_totals(where = "row")

# 查看cust2的结果
full_data %>% filter(customer_id == "cust2" | customer_id == "Total")

方法2：修正原循环逻辑 #

如果必须保留循环，调整分组和填充顺序，并保存每一次循环的结果：

library(tidyverse)
library(janitor)

df.1 <- tribble(
~customer_id, ~cust_nm  ,~period, ~cost1, ~cost2 ,
'cust1', 'pizzaco', as_date('2022-01-01'), 5, 10,
'cust2', 'sodaco',as_date('2022-03-01'), 5, 10,
'cust1', 'pizzaco', as_date('2022-04-01'), 5, 10,
'cust2', 'sodaco', as_date('2022-05-01'), 5, 10,
)

reporting_months  <- data.frame(repmonths = seq.Date(from=ymd('2022-01-01'), ymd('2022-06-01'),by="1 month")) 

cust_list <- df.1 %>% 
  select(customer_id,cust_nm) %>% 
  unique()

# 创建空列表保存每个客户的结果
result_list <- list()

for (i in 1:nrow(cust_list)) {
  current_cust <- cust_list$customer_id[i]
  current_nm <- cust_list$cust_nm[i]
  
  test <- reporting_months %>%
    # 先给月份表绑定当前客户信息，避免NA
    mutate(customer_id = current_cust, cust_nm = current_nm) %>%
    left_join(df.1, by = c("customer_id", "cust_nm", "period" = "repmonths")) %>%
    # 替换cost的NA为0
    mutate(across(c(cost1, cost2), ~replace_na(.x, 0))) %>%
    summarise(cost1 = sum(cost1, na.rm = T),
              cost2 = sum(cost2, na.rm = T),
              total = cost1 + cost2,
              .by = c(customer_id, cust_nm, period)) %>%
    adorn_totals(where = 'row')
  
  result_list[[i]] <- test
}

# 合并所有客户的结果
final_result <- bind_rows(result_list)

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