基于community_id构建物种存在-缺失矩阵的技术求助

问题背景 #

基于灵长类行为的大型纵向数据集创建物种/关联表时，因分组操作产生了多余的variable列，需要按community_id分组生成存在-缺失矩阵：每个物种在对应community的Species或Association列中出现过（非NA）则标记为1，未出现则标记为0。

可复现数据集 #

data <- structure(list(Species = c("BABO", "BW", "RC", "BW", "RC", "SKS", "SKS", "RC", "RC", "SKS", "BW", "RC", "RC", "RC", "RC", "SKS", "RC", "SKS", "SKS", "RC"), Association = c(NA, "SKS", NA, "RC", "BW", "SKS", NA, NA, NA, "BW", "SKS", NA, "SKS", "BW", "SKS", NA, NA, "SKS", NA, "MANG"), variable = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "community_id", class = "factor"), community_id = c("2007-4-16.C3", "2007-4-16.C3", "2007-4-16.C3", "2007-4-17.Mwani", "2007-4-17.Mwani", "2007-4-17.Mwani", "2007-4-17.Mwani", "2007-4-18.Sanje", "2007-4-18.Sanje", "2007-4-18.Sanje", "2007-4-18.Sanje", "2007-5-8.C3", "2007-5-9.Mwani", "2007-5-9.Mwani", "2007-5-9.Mwani", "2007-5-10.Sanje", "2007-5-10.Sanje", "2007-6-6.C3", "2007-6-6.C3", "2007-6-6.C3")), row.names = c(NA, 20L), class = "data.frame")

当前数据样式 #

Species  Association  variable       community_id
   <chr>    <chr>        <chr>          <chr>
1   BABO    NA           community_id   2007-4-16.C3
2   BW      SKS          community_id   2007-4-16.C3
3   RC      NA           community_id   2007-4-16.C3
4   BW      RC           community_id   2007-4-17.Mwani
5   RC      BW           community_id   2007-4-17.Mwani
6   SKS     SKS          community_id   2007-4-17.Mwani
7   SKS     NA           community_id   2007-4-17.Mwani
8   RC      NA           community_id   2007-4-18.Sanje
9   RC      NA           community_id   2007-4-18.Sanje
10  SKS     BW           community_id   2007-4-18.Sanje
11  BW      SKS          community_id   2007-4-18.Sanje
12  RC      NA           community_id   2007-5-8.C3
13  RC      SKS          community_id   2007-5-9.Mwani
14  RC      BW           community_id   2007-5-9.Mwani
15  RC      SKS          community_id   2007-5-9.Mwani
16  SKS     NA           community_id   2007-5-10.Sanje
17  RC      NA           community_id   2007-5-10.Sanje
18  SKS     SKS          community_id   2007-6-6.C3
19  SKS     NA           community_id   2007-6-6.C3
20  RC      MANG         community_id   2007-6-6.C3

期望输出 #

community_id         BABO    BW     RC     SKS    Mang
<chr>                <chr>   <chr>  <chr>  <chr>  <chr>
2007-4-16.C3         1       1       1      1      0
2007-4-17.Mwani      0       1       1      1      0
2007-4-18.Sanje      0       1       1      1      0
2007-5-8.C3          0       0       1      0      0 
2007-5-9.Mwani       0       1       1      1      0
2007-5-10.Sanje      0       0       1      1      0
2007-6-6.C3          0       0       1      1      1 

解决方案 #

使用tidyverse工具包分步骤处理：

步骤1：清理数据并收集分组物种 #

先移除无用的variable列，再按community_id分组，合并每组内Species和Association的非NA物种：

library(tidyverse)

# 移除多余列
data_clean <- data %>% select(-variable)

# 分组收集所有出现的物种
species_by_community <- data_clean %>%
  group_by(community_id) %>%
  summarise(
    all_species = list(unique(c(na.omit(Species), na.omit(Association))))
  )

步骤2：提取全量物种列表 #

统一物种名称的大小写（匹配期望输出的Mang格式）：

all_species <- unique(c(na.omit(data_clean$Species), na.omit(data_clean$Association)))
all_species <- str_to_title(all_species)

步骤3：生成存在-缺失矩阵 #

对比每组物种与全量列表，标记1/0后转成宽格式：

result <- species_by_community %>%
  rowwise() %>%
  mutate(
    # 逐个判断物种是否存在于当前分组
    across(all_species, ~as.integer(str_to_title(.x) %in% all_species))
  ) %>%
  ungroup() %>%
  select(-all_species) # 移除临时列表列

# 查看结果
print(result, width = Inf)

最终输出 #

运行代码后将得到与期望一致的结果：

# A tibble: 7 × 6
  community_id   BABO    BW    RC   SKS  Mang
  <chr>         <int> <int> <int> <int> <int>
1 2007-4-16.C3      1     1     1     1     0
2 2007-4-17.Mwani   0     1     1     1     0
3 2007-4-18.Sanje   0     1     1     1     0
4 2007-5-8.C3       0     0     1     0     0
5 2007-5-9.Mwani    0     1     1     1     0
6 2007-5-10.Sanje   0     0     1     1     0
7 2007-6-6.C3       0     0     1     1     1

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