explore_db

1 Species

While working on + larvae into oceano app · Issue #23 · CalCOFI/apps, noticing some funkiness with species_codes and species_codes_new tables.

Code

librarian::shelf(
  calcofi/calcofi4r,
  dplyr, DT, glue, here, 
  quiet = T)

source(here("../apps_dev/libs/db.R")) # con: database connection 

1.1 species_codes

Code

d_spp <- tbl(con, "species_codes") |> 
  collect()
dim(d_spp)

[1] 1209    6

Code

table(d_spp$taxon_rank, useNA="ifany")

     Class     Family      Genus Infraclass    Kingdom       None      Order 
         2        153        262          1          1          6         15 
    Phylum    Species  Subfamily   Suborder  Subphylum Subspecies 
         2        743         17          5          1          1 

1.2 species_codes_new

Code

d_spp_new <- tbl(con, "species_codes_new") |> 
  collect()
dim(d_spp_new)

[1] 1150    6

Code

table(d_spp_new$taxon_rank, useNA="ifany")

     Class     Family      Genus Infraclass    Kingdom       None      Order 
         2        145        253          1          1          4         15 
    Phylum    Species  Subfamily   Suborder  Subphylum Subspecies       <NA> 
         2        672         15          4          1          1         34 

1.3 species_codes vs species_codes_new

Code

d_spp |> 
  left_join(d_spp_new, by="scientific_name") |>
  filter(is.na(taxon_rank.x) | is.na(taxon_rank.y)) |> 
  # names() |> paste(collapse=', ') |> cat()
  select(
    scientific_name, 
    id.x, id.y, 
    spccode.x, spccode.y, 
    itis_tsn.x, itis_tsn.y, 
    common_name.x, common_name.y,
    taxon_rank.x, taxon_rank.y) |> 
  datatable()

2 Questions

• Why is.na(taxon_rank) (n=34) in species_codes_new but not species_codes?
• What is taxon_rank == 'None'?

3 Rename suggestions

• Drop species_codes_new since missing taxon_rank for 34 rows that are populated in species_codes
• Rename species_codes to species
• Drop field id , since confusing with spccode
• Rename field spccode to sp_id since