如何在dplyr管道中基于缺失值为dataframe补充行？

原始数据 #

数据表格 #

数据结构 #

structure(list(date = structure(c(1738195200, 1738281600, 1738368000, 
1739404800, 1741219200, 1741305600, 1743638400, 1743724800, 1743811200
), tzone = "UTC", class = c("POSIXct", "POSIXt")), fishery = c("Lake Conroe", 
"Lake Conroe", "Lake Conroe", "Harris Chain", "Lake Murray", 
"Lake Murray", "Lake Guntersville", "Lake Guntersville", "Lake Guntersville"
), tournament_day = c(1, 2, 3, 1, 1, 2, 1, 2, 3), angler = c("Martin Villa", 
"Martin Villa", "Martin Villa", "Martin Villa", "Martin Villa", 
"Martin Villa", "Martin Villa", "Martin Villa", "Martin Villa"
), ffs_period = c("P1", "P2", "P1", "P3", "P1", "P1", "P3", "P2", 
"P1"), used_ffs = c(TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE)), row.names = c(NA, -9L), class = c("tbl_df", "tbl", 
"data.frame"))

需求说明 #

每个唯一的date、fishery、tournament_day、angler组合需要对应P1、P2、P3三个ffs_period记录，当前数据仅保留了used_ffs为TRUE的行，需补充缺失的两个ffs_period行，并将补充行的used_ffs设为FALSE。

实现方案 #

使用tidyverse工具包可以轻松实现，步骤清晰并不复杂：

• 提取分组键的唯一组合
• 生成所有ffs_period的完整列表
• 交叉连接得到所有可能的组合
• 左连接原始数据并填充缺失的used_ffs值

代码实现 #

library(tidyverse)

# 加载原始数据（替换为你的数据对象）
df <- structure(...)

# 执行补全操作
complete_df <- df %>%
  distinct(date, fishery, tournament_day, angler) %>%
  crossing(ffs_period = c("P1", "P2", "P3")) %>%
  left_join(df, by = c("date", "fishery", "tournament_day", "angler", "ffs_period")) %>%
  mutate(used_ffs = replace_na(used_ffs, FALSE))

# 查看结果
print(complete_df)

最终结果示例 #

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