多数据库物种信息合并：按指定条件合并行并拼接列值问题

我需要合并全球不同地点多物种状态的18个数据库信息，但仅合并同一taxonID和locationID下establishmentMeans同时为introduced和uncertain的行，并拼接这些行中剩余列的数值。尝试多种方案后，要么丢失部分值，要么新增了不存在的值。

示例数据 #

df1 <- data.frame(
  taxonID = c(1, 1, 1, 1, 2, 2, 2),
  locationID = c(1, 1, 1, 2, 3, 3, 4),
  establishmentMeans = c("introduced", "uncertain", "vagrant", "uncertain", "introduced", "uncertain", "introduced"),
  degreeOfEstablishment = c("established", "reproducing", NA, NA, "invasive", "failing", NA),
  pathway = c("releasedForUse", "otherEscape", NA, NA, "unaided", "unaided", NA),
  source = c("x", "y", "y", "x", "x", "x", "y"),
  stringsAsFactors = FALSE 
)
df2 <- data.frame(
  taxonID = c(1, 1, 2, 2),
  locationID = c(1, 2, 3, 5),
  establishmentMeans = c("native", "native", "native", "native"),
  source = c("z", "z", "z", "z"),
  stringsAsFactors = FALSE
)

现有尝试代码 #

# merge data 
dat <- merge(df1, df2, by = c("locationID", "taxonID","establishmentMeans"), all = TRUE)

# merge rows where a taxon is reported as introduced and uncertain in the same location
dat2 <- dat |> 
  group_by(locationID, taxonID) |> 
  mutate(
    establishmentMeans = if ("introduced" %in% establishmentMeans & "uncertain" %in% establishmentMeans) {
      "introduced; uncertain"
    } else {
      establishmentMeans
    }
  )

# merge the remaining information corresponding to the status of a species when introduced and uncertain
dat3 <- dat2 |> 
  group_by(locationID, taxonID, establishmentMeans) |> 
  mutate(
    across(
      c(starts_with("degreeOfEstablishment"), starts_with("pathway"), starts_with("source")), 
      ~ paste(unique(na.omit(.)), collapse = "; "),
      .names = "{.col}"
    )
  )|> 
  ungroup()

期望输出 #

out <- data.frame(
  taxonID = c(1, 1, 1, 1, 1, 2, 2, 2, 2),
  locationID = c(1, 1, 1, 2, 2, 3, 3, 4, 5),
  establishmentMeans = c("introduced; uncertain", "native", "vagrant", "uncertain", "native", "introduced; uncertain", "native", "introduced", "native"),
  degreeOfEstablishment = c("established; reproducing", NA, NA, NA, NA, "invasive; failing", NA, NA, NA),
  pathway = c("releasedForUse; otherEscape", NA, NA, NA, NA, "unaided", NA, NA, NA),
  source = c("x; y", "z", "y", "z", "x", "x", "z", "y", "z"),
  stringsAsFactors = FALSE
)

当前遇到的问题 #

• 合并指定行时出现数据丢失；
• 需拼接所有剩余列而非特定列，不确定mutate(across(everything))是否适配列差异的数据库。

注：已修正期望输出，保留taxonID=1、locationID=2、establishmentMeans="uncertain"的行。

解决方案 #

以下代码解决了数据丢失和列适配问题，适配多数据库列差异的场景：

library(dplyr)

# 合并所有数据库（18个库可扩展为bind_rows(df1, df2, df3, ...)）
combined_dat <- bind_rows(df1, df2)

# 识别需要合并的组：同时存在introduced和uncertain的(taxonID, locationID)
target_groups <- combined_dat %>%
  group_by(taxonID, locationID) %>%
  filter(all(c("introduced", "uncertain") %in% establishmentMeans)) %>%
  distinct(taxonID, locationID)

# 处理需合并的行：拼接对应列值
merged_rows <- combined_dat %>%
  inner_join(target_groups, by = c("taxonID", "locationID")) %>%
  filter(establishmentMeans %in% c("introduced", "uncertain")) %>%
  group_by(taxonID, locationID) %>%
  summarise(
    establishmentMeans = "introduced; uncertain",
    across(everything(), ~ paste(unique(na.omit(.)), collapse = "; ")),
    .groups = "drop"
  )

# 处理无需合并的行：保留原数据（排除已合并的introduced/uncertain行）
remaining_rows <- combined_dat %>%
  anti_join(target_groups, by = c("taxonID", "locationID")) %>%
  bind_rows(
    combined_dat %>%
      semi_join(target_groups, by = c("taxonID", "locationID")) %>%
      filter(!establishmentMeans %in% c("introduced", "uncertain"))
  )

# 合并结果并调整顺序，匹配期望输出
final_out <- bind_rows(merged_rows, remaining_rows) %>%
  arrange(taxonID, locationID, establishmentMeans) %>%
  select(taxonID, locationID, establishmentMeans, degreeOfEstablishment, pathway, source)

# 输出结果
print(final_out)

方案优势 #

1. 用bind_rows替代merge，完美适配不同数据库的列差异，避免列丢失或冗余；
2. 精准定位需要合并的组，仅处理同时包含introduced和uncertain的组合，避免误操作；
3. across(everything())自动处理所有剩余列，无需手动指定列名，适配18个数据库的列变化；
4. 分离处理合并行与剩余行，确保原数据无丢失，符合期望输出要求。

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