README.md

In PopulationEcologyDivision/andesmerge: andesmerge: Maritimes tools for getting data out of ANDES and into the Maritimes groundfish database

andesmerge - a utility for reformatting andes data into the ESE format

This package exists to convert the csv files output by andes into the older format used by the ESE.

Description

Each time a new survey returns, the data must be QC'd and loaded into the groundfish schema. Previously, the ESE (Ecosystems Survey Entry) system output files in a specific format and loaded them into ESE_* tables within Oracle. All of the QC and loading procedures are dependent on the structure created by the ESE. This package is built to

1. convert the andes data into the same format used by the ESE; and
2. load the converted data into Oracle

Future effort will be made to incorporate existing (and improved) QC checks directly in this package, and further, load the data directly into the appropriate tables in groundfish (forgoing the step of generating ESE-specific tables altogether)

Usage

By pointing a function (i.e. matchAndesToESE() at a folder of andes-generated csv files, the package will output a list object containing the following objects:

• ESE_MISSIONS
• ESE_SETS
• ESE_CATCHES
• ESE_BASKETS
• ESE_SPECIMENS
• ESE_LV1_OBSERVATIONS

With appropriate Oracle credentials, another function (i.e. replaceESEData()) can then be run to load the generated objects into the groundfish schema

Installation:

# install.packages("devtools")
devtools::install_github("PopulationEcologyDivision/andesmerge", dependencies = TRUE)

To generate a list object containing all of the various ESE_* objects, one would run the following script, where the path points to a folder containing the following files:

• tmp_basket_data.csv
• tmp_catch_data.csv
• tmp_cruise_data.csv
• tmp_obs_types_data.csv
• tmp_set_data.csv
• tmp_specimen_data.csv

library(andesmerge)
thedata <- matchAndesToESE(dataPath = "c:/<some_path_to_the_andes_csv_files>/")

The data can then be loaded into temporary tables within the groundfish schema using the following commands. This will delete any existing content within the target tables, and replace it with the new information drawn from the csv files/

cxn <- Mar.utils::make_oracle_cxn(fn.oracle.username = groundfish.username,
                                  fn.oracle.password = groundfish.password, 
                                  fn.oracle.dsn = "PTRAN", 
                                  usepkg = 'roracle')
loadESEData(cxnObj = cxn, source_df = tt$ESE_MISSIONS, target_table = "ANDESE_MISSIONS")
loadESEData(cxnObj = cxn, source_df = tt$ESE_SETS, target_table = "ANDESE_SETS")
loadESEData(cxnObj = cxn, source_df = tt$ESE_BASKETS, target_table = "ANDESE_BASKETS")
loadESEData(cxnObj = cxn, source_df = tt$ESE_CATCHES, target_table = "ANDESE_CATCHES")
loadESEData(cxnObj = cxn, source_df = tt$ESE_SPECIMENS, target_table = "ANDESE_SPECIMENS")
loadESEData(cxnObj = cxn, source_df = tt$ESE_LV1_OBSERVATIONS, target_table = "ANDESE_LV1_OBSERVATIONS")

Once all QC is done, and we are ready to load the new data into production, we can do the following:

loadESEData(cxnObj = cxn, source_df = tt$ESE_MISSIONS, target_table = "ESE_MISSIONS", confirmOverwrite = T)
loadESEData(cxnObj = cxn, source_df = tt$ESE_SETS, target_table = "ESE_SETS", confirmOverwrite = T)
loadESEData(cxnObj = cxn, source_df = tt$ESE_BASKETS, target_table = "ESE_BASKETS", confirmOverwrite = T)
loadESEData(cxnObj = cxn, source_df = tt$ESE_CATCHES, target_table = "ESE_CATCHES", confirmOverwrite = T)
loadESEData(cxnObj = cxn, source_df = tt$ESE_SPECIMENS, target_table = "ESE_SPECIMENS", confirmOverwrite = T)
loadESEData(cxnObj = cxn, source_df = tt$ESE_LV1_OBSERVATIONS, target_table = "ESE_LV1_OBSERVATIONS", confirmOverwrite = T)

After that step, the Groundfish loader tool can be run, loading the ESER tables to production like normal.

As of 2022.05.17, we do QC within these new ANDESE_* objects inside of Oracle. Any QC issues are relayed back to this repository as github 'issues', and are corrected using the tweaks.R file. When convenient, the functions above are re-run, with the tweaks fixing the issues. Once satisfied, the data will be loaded into the production tables in the same way it would have been done for the original ESE output.

PopulationEcologyDivision/andesmerge documentation built on April 12, 2024, 6:28 a.m.