在R中为未观测到生物的站点访问日期填充0值

需求说明 #

现有两个R数据框：

• mid_clean_up：记录各站点、日期下3个目标物种的实际计数（无生物观测的记录直接缺失）
• raw_visits：包含所有站点的全部访问日期

需要为每个物种补全已访问但未观测到的站点-日期组合的计数，将这些位置的计数填充为0。例如admin_pond站点共4次访问，某物种仅3次有计数，需将第4次访问的计数补为0。

原始数据 #

已观测生物数据 #

mid_clean_up <- structure(list(date = structure(c(19116, 19116, 19116, 19117, 
19117, 19117, 19123, 19123, 19123, 19124, 19124, 19130, 19130, 
19130, 19131, 19131, 19132, 19138, 19138, 19139, 19139, 19146, 
19146, 19147, 19147, 19150, 19150, 19151, 19151, 19157, 19157, 
19158, 19158, 19166, 19170, 19170, 19171, 19171, 19184, 19184, 
19185, 19185, 19191, 19191, 19192, 19192, 19206, 19244, 19244, 
19245, 19265, 19265), class = "Date"), site = c("wood_lab_pond", 
"wood_lab_pond", "wood_lab_pond", "phelps_pond", "phelps_pond", 
"phelps_pond", "admin_pond", "admin_pond", "admin_pond", "rv_pond", 
"rv_pond", "admin_pond", "admin_pond", "admin_pond", "admin_pond", 
"admin_pond", "admin_pond", "wood_lab_pond", "wood_lab_pond", 
"wood_lab_pond", "wood_lab_pond", "phelps_pond", "phelps_pond", 
"phelps_pond", "phelps_pond", "rv_pond", "rv_pond", "rv_pond", 
"rv_pond", "tuttle_pond", "tuttle_pond", "tuttle_pond", "tuttle_pond", 
"tryon_weber", "vorisek_pond", "vorisek_pond", "vorisek_pond", 
"vorisek_pond", "rv_pond", "rv_pond", "rv_pond", "rv_pond", "tuttle_pond", 
"tuttle_pond", "tuttle_pond", "tuttle_pond", "tryon_weber", "tuttle_pond", 
"tuttle_pond", "rv_pond", "tuttle_pond", "tuttle_pond"), species_capture = c("pseudacris_crucifer", 
"rana_catesbeiana", "rana_clamitans", "pseudacris_crucifer", 
"rana_catesbeiana", "rana_clamitans", "pseudacris_crucifer", 
"rana_catesbeiana", "rana_clamitans", "pseudacris_crucifer", 
"rana_catesbeiana", "pseudacris_crucifer", "rana_catesbeiana", 
"rana_clamitans", "pseudacris_crucifer", "rana_catesbeiana", 
"pseudacris_crucifer", "rana_catesbeiana", "rana_clamitans", 
"rana_catesbeiana", "rana_clamitans", "rana_catesbeiana", "rana_clamitans", 
"rana_catesbeiana", "rana_clamitans", "rana_catesbeiana", "rana_clamitans", 
"rana_catesbeiana", "rana_clamitans", "rana_catesbeiana", "rana_clamitans", 
"rana_catesbeiana", "rana_clamitans", "rana_clamitans", "rana_catesbeiana", 
"rana_clamitans", "rana_catesbeiana", "rana_clamitans", "rana_catesbeiana", 
"rana_clamitans", "rana_catesbeiana", "rana_clamitans", "rana_catesbeiana", 
"rana_clamitans", "rana_catesbeiana", "rana_clamitans", "rana_clamitans", 
"rana_catesbeiana", "rana_clamitans", "rana_catesbeiana", "rana_catesbeiana", 
"rana_clamitans"), n = c(2L, 2L, 1L, 4L, 4L, 7L, 4L, 9L, 5L, 
16L, 1L, 2L, 15L, 3L, 3L, 20L, 1L, 4L, 22L, 3L, 3L, 3L, 10L, 
6L, 16L, 7L, 2L, 5L, 1L, 15L, 19L, 22L, 3L, 1L, 14L, 8L, 13L, 
1L, 13L, 7L, 29L, 3L, 39L, 3L, 31L, 2L, 2L, 29L, 1L, 11L, 20L, 
2L)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-52L))

用户尝试的单个物种处理代码：

# 单个物种的访问数据透视表
bull_frog_visits <- mid_clean_up %>% 
  select(site, date, species_capture, n) %>% 
  filter(species_capture == "rana_catesbeiana") %>% 
  select(!species_capture) %>% 
  group_by(site) %>% 
  mutate(n_visit = match(date, unique(date)),
         n_visit = paste0("visit_", n_visit, sep = "")) %>% 
  select(!date) %>% 
  ungroup() %>%
  pivot_wider(names_from = c("n_visit"), values_from = c("n"))%>% 
  group_by(site) %>% 
  mutate(across(contains("visit"), 
                ~ifelse(is.na(.) &
                          !is.na(lag(.)), 0, .)))

所有站点访问数据 #

raw_visits <- structure(list(site = c("wood_lab_pond", "phelps_pond", "admin_pond", 
"rv_pond", "admin_pond", "admin_pond", "admin_pond", "wood_lab_pond", 
"wood_lab_pond", "wood_lab_pond", "phelps_pond", "phelps_pond", 
"phelps_pond", "rv_pond", "rv_pond", "tuttle_pond", "tuttle_pond", 
"tuttle_pond", "tryon_weber", "tryon_weber", "tryon_weber", "vorisek_pond", 
"vorisek_pond", "rv_pond", "rv_pond", "tuttle_pond", "tuttle_pond", 
"tryon_weber", "tuttle_pond", "rv_pond", "tuttle_pond"), date = structure(c(19116, 
19117, 19123, 19124, 19130, 19131, 19132, 19138, 19139, 19140, 
19146, 19147, 19148, 19150, 19151, 19157, 19158, 19159, 19165, 
19166, 19166, 19170, 19171, 19184, 19185, 19191, 19192, 19206, 
19244, 19245, 19265), class = "Date")), class = "data.frame", row.names = c(NA, 
-31L))

通用解决方案 #

下面是可以一次性处理所有物种的补全方法，基于tidyverse工具链：

library(tidyverse)

# 获取所有目标物种列表
target_species <- unique(mid_clean_up$species_capture)

# 生成所有站点-日期-物种的完整组合（确保没有遗漏任何访问记录）
full_combinations <- raw_visits %>%
  distinct(site, date) %>%  # 去重得到唯一的站点-访问日期组合
  crossing(species_capture = target_species)  # 与所有物种交叉，生成全量组合

# 左连接现有观测数据，将缺失的计数填充为0
filled_data <- full_combinations %>%
  left_join(mid_clean_up, by = c("site", "date", "species_capture")) %>%
  mutate(n = replace_na(n, 0))  # 把未观测的NA替换为0

# 查看admin_pond站点的补全结果示例
filled_data %>% filter(site == "admin_pond")

代码逻辑说明 #

1. 生成全量组合：用distinct提取raw_visits中唯一的站点-日期对，再通过crossing和所有物种组合，得到所有可能的观测场景。
2. 合并数据并补0：左连接现有观测数据后，未观测的记录会出现NA，用replace_na直接将这些NA替换为0，完成补全。

这个方法既保留了原始的长格式数据结构，又能一次性处理所有物种，后续无论要转换为宽格式还是做进一步分析都更灵活。

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