使用rfishbase批量查询鱼类拉丁名对应通用名报错求助

问题场景与代码 #

有大量鱼类拉丁名，希望通过rfishbase包批量查询对应通用名并添加至新列，执行代码时报错，代码如下：

df_sample <- tibble(Latin_name = c("Sparus aurata", "Mullus barbatus", "Belone belone"))

# connect the fisbase database
fishbase_db <- fb_conn(server = c("fishbase", "sealifebase"), version = "latest")


df_sample <- df_sample %>%
  rowwise() %>%
  mutate(Common_name = {
    result <- common_names(Latin_name, db = fishbase_db)
    if (nrow(result) > 0) result$CommonName[1] else NA
  })

print(df_sample)

错误信息 #

Error in mutate():
ℹ In argument: Common_name = { ... }.
ℹ In row 1.
Caused by error in db_query_fields.DBIConnection():
! Can't query fields.
ℹ Using SQL: SELECT * FROM (FROM species) q03 WHERE (0 = 1)
Caused by error:
! rapi_prepare: Failed to prepare query SELECT *
FROM (FROM species) q03
WHERE (0 = 1)
Error: Catalog Error: Table with name species does not exist!
Did you mean "pg_views"?
LINE 2: FROM (FROM species) q03
^
Run rlang::last_trace() to see where the error occurred.

解决步骤与修正代码 #

核心问题 #

错误提示找不到species表，是因为手动调用fb_conn建立的远程数据库连接，其表结构与rfishbase函数预期不匹配，存在版本兼容性问题。

方案1：无需手动建立数据库连接（推荐） #

直接使用common_names()函数的批量处理能力，无需手动创建db连接，函数会自动处理数据加载：

library(tidyverse)
library(rfishbase)

df_sample <- tibble(Latin_name = c("Sparus aurata", "Mullus barbatus", "Belone belone"))

# 批量查询所有拉丁名的通用名
common_names_result <- common_names(df_sample$Latin_name)

# 合并结果到原数据框，取每个拉丁名的第一个通用名
df_sample <- df_sample %>%
  left_join(
    common_names_result %>%
      group_by(Species) %>%
      slice(1) %>%
      select(Species, CommonName),
    by = c("Latin_name" = "Species")
  )

print(df_sample)

方案2：加载本地数据库数据 #

如果需要使用本地数据库缓存，改用load_fishbase()加载数据，而不是fb_conn建立远程连接：

library(tidyverse)
library(rfishbase)

# 加载fishbase和sealifebase的本地数据集
load_fishbase(server = c("fishbase", "sealifebase"), version = "latest")

df_sample <- tibble(Latin_name = c("Sparus aurata", "Mullus barbatus", "Belone belone"))

df_sample <- df_sample %>%
  rowwise() %>%
  mutate(Common_name = {
    result <- common_names(Latin_name)
    if (nrow(result) > 0) result$CommonName[1] else NA
  })

print(df_sample)

额外优化 #

避免使用rowwise()，改用map()进一步提升批量处理效率：

df_sample <- df_sample %>%
  mutate(Common_name = map_chr(Latin_name, ~{
    res <- common_names(.x)
    if (nrow(res) > 0) res$CommonName[1] else NA_character_
  }))

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