In fishsciences/ybt: Yolo Bypass Telemetry Project Analysis Package

knitr::opts_chunk$set(
  collapse = TRUE,
  eval = FALSE,
  echo = TRUE,
  comment = "#>"
)

General database workflow

Creating the database "from scratch"

1. The entire database can be re-created with the create_ybt_database.R script in the inst/ subfolder of the ybt package.
2. This action requires all the raw project data
3. We strongly recommended: backing up a copy of all raw data before running the script, and making sure you understand what all steps of the script are doing before you run them.

Functions for the database in the ybt package and in the RSQLite package

• The ybt::ybt_db_append function is used to append all tables except for new deployments (so, tags, chn, wst, and detections)
• The ybt::write_deployments function is used to add new deployment records to the deployment table.
  • It assumes your deployment data is in the same format as the template .csv (found in /inst/).
• Note that SQLite doesn't have an internal format for dates and times, so all dates and times are stored as characters in the database and need to be converted to date/time formats in R.
• Don't forget to close the database connection after you've queried what you need. This is done with dbDisconnect(connection).

Appending new data to the database

Tags

• Use the tags .csv template to prepare your new tag data
• Append the tag data to the appropriate table ("tags") with the ybt::ybt_db_append() function.
• If appending new Chinook Salmon metadata, use the chn template. Append the new chn data to the "chn" table with the ybt::ybt_db_append() function.

Deployments

• Use the deployments template to prepare your new deployment data
• Append the new deployment data to the "deployments" table of the database using the ybt::write_deployments() function.

Detections

The detections are (of course) the hardest part of this workflow, and so they get their own vignette, which you can bring up with vignette(topic = "Database-detections", package = "ybt").

Querying the database

The nice thing about doing all this in R is that if you're already familiar with the dplyr package, you can use it to construct and run database queries (please see this tutorial for more information). Some examples of common database queries are below, but all the queries you need for the Analysis workflow are pre-written into the scripts and reproducible report.

# database connection: requires >= RSQLite 2.1.1
db = RSQLite::dbConnect(RSQLite::SQLite(), "../yb_database.sqlite") # check relative path

# get general info about database
dbListTables(db)
tbls = dbListTables(db)

If you already know basic SQL, I think the best way to get what you need is to use the dbGetQuery() function from the RSQLite package:

# how many rows per table?
sapply(tbls, function(x) dbGetQuery(db, paste("SELECT COUNT(*) FROM" , x)))
# how many/what fields per table?
sapply(tbls, dbListFields, conn = db)

If you're more comfortable with the dplyr package and tidyverse workflows, you can access the database with the tbl() function and then pipe through:

library(dplyr)

# All tagging metadata (including both wst and chinook)
alltags = tbl(db, "tags") %>% 
  collect() %>% # this pulls the whole table
  mutate(DateTagged = as.Date(DateTagged)) # this gets the dates and times in the correct format

# just white sturgeon:
wst = tbl(db, "tags") %>% 
  filter(Sp == "wst") %>% 
  collect() 

# detections for just the white sturgeon tagged in 2012:
wst2012 <- tbl(db, "tags") %>% # first get their TagID info
  inner_join(tbl(db, "detections"), by = "TagID") %>% # join it with the detections table
  filter(TagGroup == "wst_2012") %>% # filter down to just the 2012 white sturgeon
  collect() # bring it into R as a data frame

fishsciences/ybt documentation built on March 11, 2021, 8:45 a.m.