In noaa-garfo/discaRd: Cochran bycatch estimation
knitr::opts_chunk$set(echo=FALSE, warning = FALSE,
message = FALSE, cache = FALSE,
progress = TRUE, verbose = FALSE, comment = F
, error = FALSE, dev = 'png', dpi = 200)
t1 = Sys.time()
# setwd("C:/Users/benjamin.galuardi/Documents/GitHub/discaRd/CAMS/")
library(tidyverse)
library(odbc)
library(ROracle)
library(dplyr, warn.conflicts = FALSE)
# library(dbplyr)
library(ggplot2)
# library(config)
library(stringr)
#library(discaRd)
devtools::load_all()
options(scipen = 999)
# local run
#dw_maps <- config::get(value = "maps", file = "~/config.yml")
# # if on server..
# dw_maps <- config::get(value = "maps", file = "~/config.yml")
dw_maps <- config::get(config = "maps", file = "~/config_group.yml")
#
# bcon <- dbConnect(odbc::odbc(),
# DSN = dw_maps$dsn,
# UID = dw_maps$uid,
# PWD = dw_maps$pwd)
# Connect to database - move this to config file in the future - quick addition for server
connectString <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", dw_maps$host, ")(PORT=", dw_maps$port, "))",
"(CONNECT_DATA=(SERVICE_NAME=",dw_maps$svc, ")))",
sep = ""
)
# Connect to oracle each loop in case of timeouts
bcon <- ROracle::dbConnect(
drv = ROracle::Oracle(),
username = dw_maps$uid,
password = dw_maps$pwd,
dbname = connectString
)
'%!in%' <- function(x,y)!('%in%'(x,y))
source('~/discaRd/CAMS/R/cams_discard_functions.R')
FY <- 2020
FY_TYPE = 'MAY START'
#--------------------------------------------------------------------------#
# group of species ITIS codes..
# SMB, river herring, bluefish, summer flounder,
# black seabass and scup.
#Not sure what else is needed for herring and shad catch cap.
itis <- c('164499',
'160617',
'564139',
'160855',
'564136',
'564130',
'564151',
'564149',
'564145',
'164793',
'164730',
'164791')
#itis <- itis
itis_num <- as.numeric(itis)
species = tbl(bcon, sql("select *
from MAPS.CAMS_DISCARD_MORTALITY_STOCK")) %>%
collect() %>%
filter(SPECIES_ITIS %in% itis_num) %>% group_by(SPECIES_ITIS) %>%
slice(1)
species$ITIS_TSN <- stringr::str_sort(itis)
#species$ITIS_TSN <- as.numeric(species$SPECIES_ITIS)
# species$ITIS_TSN <- as.character(species$SPECIES_ITIS)
#--------------------------------------------------------------------------#
# a sumamry table for comaprison
# final_discard_table = data.frame(YEAR = FY, SPECIES_ITIS = species$ITIS_TSN, COMNAME = species$COMMON_NAME, DISCARD = NA)
#--------------------------------------------------------------------------#
# get catch and matched obs data together
c_o_dat2 <- tbl(bcon, sql(
" with obs_cams as (
select year
, month
, case when month in (5,6,7,8,9,10) then 1
when month in (11,12,1,2,3,4) then 2
end as halfofyear
-- , carea
,region
, AREA
, vtrserno
, link1
, docid
, CAMSID
, nespp3
, itis_tsn as SPECIES_ITIS
, itis_group1
, SECGEAR_MAPPED as GEARCODE
, NEGEAR
, GEARTYPE
, MESHGROUP
, SECTID
, case when PERMIT = '000000' then 'STATE'
else 'FED' end as trip_type
, tripcategory
, accessarea
, activity_code_1
-- , permit_EFP_1
-- , permit_EFP_2
-- , permit_EFP_3
-- , permit_EFP_4
-- , redfish_exemption
-- , closed_area_exemption
-- , sne_smallmesh_exemption
-- , xlrg_gillnet_exemption
, NVL(sum(discard),0) as discard
, NVL(round(max(subtrip_kall)),0) as subtrip_kall
, NVL(round(max(obs_kall)),0) as obs_kall
, NVL(sum(discard)/nullif(round(max(obs_kall)), 0), 0) as dk
from MAPS.CAMS_OBS_CATCH
-- where nespp3 is not null
group by year
-- , carea
,region
, AREA
, vtrserno
, link1
, docid
, nespp3
, itis_tsn
, itis_group1
, SECGEAR_MAPPED
, NEGEAR
, GEARTYPE
, MESHGROUP
, SECTID
, case when PERMIT = '000000' then 'STATE'
else 'FED' end
, CAMSID
, month
, halfofyear
, tripcategory
, accessarea
, activity_code_1
-- , permit_EFP_1
-- , permit_EFP_2
-- , permit_EFP_3
-- , permit_EFP_4
-- , redfish_exemption
-- , closed_area_exemption
-- , sne_smallmesh_exemption
-- , xlrg_gillnet_exemption
order by vtrserno asc
)
select case when MONTH in (1,2,3,4) then YEAR-1 else YEAR end as GF_YEAR
, case when MONTH in (1,2,3) then YEAR-1 else YEAR end as SCAL_YEAR
, o.*
, c.match_nespp3
, coalesce(c.match_nespp3, o.nespp3) as nespp3_final
from obs_cams o
left join apsd.s_nespp3_match_conv c on o.nespp3 = c.nespp3
"
)
) %>%
collect()
state_trips = c_o_dat2 %>% filter(TRIP_TYPE == 'STATE')
fed_trips = c_o_dat2 %>% filter(TRIP_TYPE == 'FED')
fed_trips = fed_trips %>%
mutate(ROWID = 1:nrow(fed_trips)) %>%
relocate(ROWID)
# filter out link1 that are doubled on VTR
multilink = fed_trips %>%
filter(!is.na(LINK1)) %>%
group_by(VTRSERNO) %>%
dplyr::summarise(nlink1 = n_distinct(LINK1)) %>%
arrange(desc(nlink1)) %>%
filter(nlink1>1)
remove_links = fed_trips %>%
filter(is.na(SPECIES_ITIS) & !is.na(LINK1) & VTRSERNO %in% multilink$VTRSERNO) %>%
dplyr::select(LINK1) %>%
distinct()
remove_id = fed_trips %>%
filter(is.na(SPECIES_ITIS) & !is.na(LINK1) & VTRSERNO %in% multilink$VTRSERNO) %>%
distinct(ROWID)
fed_trips =
fed_trips %>%
filter(ROWID %!in% remove_id$ROWID)
non_gf_dat = fed_trips%>%
#filter(substr(ACTIVITY_CODE_1, 1,3) != 'NMS') %>%
bind_rows(., state_trips)
c_o_dat2 <- non_gf_dat
# Stratification variables
stratvars = c('SPECIES_STOCK'
,'CAMS_GEAR_GROUP'
, 'MESHGROUP'
# , 'HALFOFYEAR'
# , 'REGION'
, 'TRIPCATEGORY'
, 'ACCESSAREA')
# Begin loop
for(i in 1:length(species$ITIS)){
print(paste0('Running ', species$COMMON_NAME[i]))
# species_nespp3 = species$NESPP3[i]
#species_itis = species$ITIS_TSN[i]
species_itis <- as.character(species$ITIS_TSN[i])
species_itis_srce = as.character(as.numeric(species$ITIS_TSN[i]))
#--------------------------------------------------------------------------#
# Support table import by species
#GEAR TABLE
CAMS_GEAR_STRATA = tbl(bcon, sql(' select * from MAPS.CAMS_GEARCODE_STRATA')) %>%
collect() %>%
dplyr::rename(GEARCODE = VTR_GEAR_CODE) %>%
# filter(NESPP3 == species_nespp3) %>%
filter(SPECIES_ITIS == species_itis) %>%
dplyr::select(-NESPP3, -SPECIES_ITIS)
#Used Alewife stratificatino in support tables
CAMS_GEAR_STRATA <- read.csv("~/discaRd/CAMS/MODULES/CALENDAR/SBRM_CAMS_GEAR_GROUP.csv")
CAMS_GEAR_STRATA$CAMS_GEAR_GROUP <- as.character(CAMS_GEAR_STRATA$CAMS_GEAR_GROUP)
# # Stat areas table
# unique stat areas for stock ID if needed
STOCK_AREAS = tbl(bcon, sql('select * from MAPS.CAMS_STATAREA_STOCK')) %>%
# filter(NESPP3 == species_nespp3) %>% # removed & AREA_NAME == species_stock
dplyr::filter(SPECIES_ITIS == species_itis) %>%
collect() %>%
group_by(AREA_NAME) %>%
distinct(STAT_AREA) %>%
mutate(AREA = as.character(STAT_AREA)
, SPECIES_STOCK = AREA_NAME) %>%
ungroup() #%>%
#dplyr::select(SPECIES_STOCK, AREA)
#Used Alewife stratificatino in support tables
STOCK_AREAS <- read.csv("~/discaRd/CAMS/MODULES/CALENDAR/SBRM_STAT_AREA.csv")
STOCK_AREAS$AREA <- as.character(STOCK_AREAS$AREA)
STOCK_AREAS$STAT_AREA<- as.character(STOCK_AREAS$STAT_AREA)
# #Mortality table
CAMS_DISCARD_MORTALITY_STOCK = tbl(bcon, sql("select * from MAPS.CAMS_DISCARD_MORTALITY_STOCK")) %>%
collect() %>%
mutate(SPECIES_STOCK = AREA_NAME
, GEARCODE = CAMS_GEAR_GROUP) %>%
select(-AREA_NAME) %>%
mutate(CAMS_GEAR_GROUP = as.character(CAMS_GEAR_GROUP)) %>%
# filter(NESPP3 == species_nespp3) %>%
filter(SPECIES_ITIS == species_itis_srce) %>%
# dplyr::select(-NESPP3, -SPECIES_ITIS) %>%
dplyr::rename(DISC_MORT_RATIO = Discard_Mortality_Ratio)
#Used Alewife stratificatino in support tables
CAMS_DISCARD_MORTALITY_STOCK <- read.csv("~/discaRd/CAMS/MODULES/CALENDAR/SBRM_CAMS_DISCARD_MORTALITY_STOCK.csv")
CAMS_DISCARD_MORTALITY_STOCK$CAMS_GEAR_GROUP <- as.character(CAMS_DISCARD_MORTALITY_STOCK$CAMS_GEAR_GROUP)
CAMS_DISCARD_MORTALITY_STOCK$GEARCODE <- as.character(CAMS_DISCARD_MORTALITY_STOCK$GEARCODE)
#--------------------------------------------------------------------------------#
# make tables
ddat_focal <- c_o_dat2 %>%
filter(YEAR == FY) %>% ## time element is here!!
filter(AREA %in% STOCK_AREAS$AREA) %>%
mutate(LIVE_POUNDS = SUBTRIP_KALL
,SEADAYS = 0
, NESPP3 = NESPP3_FINAL) %>%
left_join(., y = STOCK_AREAS, by = 'AREA') %>%
left_join(., y = CAMS_GEAR_STRATA, by = 'GEARCODE') %>%
left_join(., y = CAMS_DISCARD_MORTALITY_STOCK
, by = c('SPECIES_STOCK', 'CAMS_GEAR_GROUP')
) %>%
dplyr::select(-SPECIES_ITIS.y, -GEARCODE.y) %>%
dplyr::rename(SPECIES_ITIS = 'SPECIES_ITIS.x', GEARCODE = 'GEARCODE.x') %>%
relocate('COMMON_NAME','SPECIES_ITIS','NESPP3','SPECIES_STOCK','CAMS_GEAR_GROUP','DISC_MORT_RATIO')
ddat_prev <- c_o_dat2 %>%
filter(YEAR == FY-1) %>% ## time element is here!!
filter(AREA %in% STOCK_AREAS$AREA) %>%
mutate(LIVE_POUNDS = SUBTRIP_KALL
,SEADAYS = 0
, NESPP3 = NESPP3_FINAL) %>%
left_join(., y = STOCK_AREAS, by = 'AREA') %>%
left_join(., y = CAMS_GEAR_STRATA, by = 'GEARCODE') %>%
left_join(., y = CAMS_DISCARD_MORTALITY_STOCK
, by = c('SPECIES_STOCK', 'CAMS_GEAR_GROUP')
) %>%
dplyr::select(-SPECIES_ITIS.y, -GEARCODE.y) %>%
dplyr::rename(SPECIES_ITIS = 'SPECIES_ITIS.x', GEARCODE = 'GEARCODE.x') %>%
relocate('COMMON_NAME','SPECIES_ITIS','NESPP3','SPECIES_STOCK','CAMS_GEAR_GROUP','DISC_MORT_RATIO')
# need to slice the first record for each observed trip.. these trips are multi rowed while unobs trips are single row..
ddat_focal_cy = ddat_focal %>%
filter(!is.na(LINK1)) %>%
mutate(SPECIES_EVAL_DISCARD = case_when(SPECIES_ITIS == species_itis ~ DISCARD
)) %>%
mutate(SPECIES_EVAL_DISCARD = coalesce(SPECIES_EVAL_DISCARD, 0)) %>%
group_by(LINK1, VTRSERNO) %>%
arrange(desc(SPECIES_EVAL_DISCARD)) %>%
slice(1) %>%
ungroup()
# and join to the unobserved trips
ddat_focal_cy = ddat_focal_cy %>%
union_all(ddat_focal %>%
filter(is.na(LINK1)) %>%
group_by(VTRSERNO, CAMSID) %>%
slice(1) %>%
ungroup()
)
# if using the combined catch/obs table, which seems necessary for groundfish.. need to roll your own table to use with run_discard function
# DO NOT NEED TO FILTER SPECIES HERE. NEED TO RETAIN ALL TRIPS. THE MAKE_BDAT_FOCAL.R FUNCTION TAKES CARE OF THIS.
bdat_cy = ddat_focal %>%
filter(!is.na(LINK1)) %>%
mutate(DISCARD_PRORATE = DISCARD
, OBS_AREA = AREA
, OBS_HAUL_KALL_TRIP = OBS_KALL
, PRORATE = 1)
# set up trips table for previous year
ddat_prev_cy = ddat_prev %>%
filter(!is.na(LINK1)) %>%
mutate(SPECIES_EVAL_DISCARD = case_when(SPECIES_ITIS == species_itis ~ DISCARD
)) %>%
mutate(SPECIES_EVAL_DISCARD = coalesce(SPECIES_EVAL_DISCARD, 0)) %>%
group_by(LINK1, VTRSERNO) %>%
arrange(desc(SPECIES_EVAL_DISCARD)) %>%
slice(1) %>%
ungroup()
ddat_prev_cy = ddat_prev_cy %>%
union_all(ddat_prev %>%
filter(is.na(LINK1)) %>%
group_by(VTRSERNO, CAMSID) %>%
slice(1) %>%
ungroup()
)
# previous year observer data needed..
bdat_prev_cy = ddat_prev %>%
filter(!is.na(LINK1)) %>%
mutate(DISCARD_PRORATE = DISCARD
, OBS_AREA = AREA
, OBS_HAUL_KALL_TRIP = OBS_KALL
, PRORATE = 1)
# Run the discaRd functions on previous year
d_prev = run_discard(bdat = bdat_prev_cy
, ddat = ddat_prev_cy
, c_o_tab = ddat_prev
# , year = 2018
# , species_nespp3 = species_nespp3
, species_itis = species_itis
, stratvars = stratvars
, aidx = c(1,2) #maybe this should be bumped up?
)
# Run the discaRd functions on current year
d_focal = run_discard(bdat = bdat_cy
, ddat = ddat_focal_cy
, c_o_tab = ddat_focal
# , year = 2019
# , species_nespp3 = '081' # haddock...
# , species_nespp3 = species_nespp3 #'081' #cod...
, species_itis = species_itis
, stratvars = stratvars
, aidx = c(1,2) #maybe this should be bumped up?
)
# summarize each result for convenience
dest_strata_p = d_prev$allest$C %>% summarise(STRATA = STRATA
, N = N
, n = n
, orate = round(n/N, 2)
, drate = RE_mean
, KALL = K, disc_est = round(D)
, CV = round(RE_rse, 2)
)
dest_strata_f = d_focal$allest$C %>% summarise(STRATA = STRATA
, N = N
, n = n
, orate = round(n/N, 2)
, drate = RE_mean
, KALL = K, disc_est = round(D)
, CV = round(RE_rse, 2)
)
# substitute transition rates where needed
trans_rate_df = dest_strata_f %>%
left_join(., dest_strata_p, by = 'STRATA') %>%
mutate(n.y = coalesce(n.y, 0)) %>%
mutate(STRATA = STRATA
, n_obs_trips = n.x
, in_season_rate = drate.x
, previous_season_rate = drate.y
, trans_rate = get.trans.rate(l_observed_trips = n_obs_trips
, l_assumed_rate = previous_season_rate
, l_inseason_rate = in_season_rate
)
)
#Fit model for an alternative to the assumed rate
#
# m_data <- d_focal$res %>% filter(!is.na(LINK1))
#
# mod <- glm(OBS_DISCARD ~ offset(log(m_data$OBS_KALL+1))+
# SPECIES_STOCK +
# CAMS_GEAR_GROUP +
# MESHGROUP +
# HALFOFYEAR +
# REGION,
# family = poisson(),
# data = m_data)
#
# data_focal <- d_focal$res
#
# data_focal$OBS_KALL <- log(data_focal$OBS_KALL+1)
#
# # Predict
# data_focal <- data.frame(data_focal, D_MODEL = predict(mod, data_focal, type = "response")) #*DISC_MORT_RATIO
#Then join to trans_rate_df
final_table = trans_rate_df %>%
right_join(., y = d_focal$res, by = 'STRATA') %>%
as_tibble() %>%
mutate(SPECIES_ITIS_EVAL = species_itis
, COMNAME_EVAL = species$COMMON_NAME[i]
, FISHING_YEAR = FY
, FY_TYPE = FY_TYPE) %>%
mutate(FINAL_RATE = case_when(n_obs_trips >= 5 ~ in_season_rate # this is an in season rate
,n_obs_trips < 5 & n.y >=5 ~ trans_rate
,n_obs_trips < 5 & n.y < 5 ~ ARATE)
) %>%
mutate(DISCARD_SOURCE = case_when(!is.na(LINK1) ~ 'O'
, is.na(LINK1) & n_obs_trips >= 5 ~ 'I'
, is.na(LINK1) & n_obs_trips < 5 & n.y>=5 ~ 'T'
, is.na(LINK1) & n_obs_trips < 5 &
n.y < 5 ~ 'A'))
final_table = final_table %>%
mutate(DISC_MORT_RATIO = coalesce(DISC_MORT_RATIO, 1)) %>%
mutate(CV = case_when(DISCARD_SOURCE == 'O' ~ 0
, DISCARD_SOURCE == 'E' ~ CV.x
, DISCARD_SOURCE == 'T' ~ CV.x
, DISCARD_SOURCE == 'A' ~ CV),
DISCARD = case_when(!is.na(LINK1) ~ DISC_MORT_RATIO*OBS_DISCARD
, is.na(LINK1) ~ DISC_MORT_RATIO*FINAL_RATE*SUBTRIP_KALL),
STRATA_USED = case_when(DISCARD_SOURCE == 'E' ~ STRATA
,DISCARD_SOURCE == 'T' ~ STRATA
,DISCARD_SOURCE == 'A' ~ STRATA_ASSUMED))%>%
rename('ACTIVITY_CODE'='ACTIVITY_CODE_1') %>% select(-SPECIES_ITIS)
final_table %>%
dplyr::group_by(TRIP_TYPE) %>%
dplyr::summarise(Discard_total = sum(DISCARD, na.rm=TRUE),
Kall_total = sum(SUBTRIP_KALL, na.rm=TRUE))
final_table$FINAL_RATE[is.na(final_table$FINAL_RATE)]<-0
final_table$COMMON_NAME <- species$COMMON_NAME[i]
sum(final_table$DISCARD, na.rm=TRUE)
species$COMMON_NAME[i]
#add subtrip kall after CV
cy_discard_example = final_table %>%
# mutate(GF_STOCK_DEF = paste0(COMNAME_EVAL, '-', SPECIES_STOCK)) %>%
dplyr::rename('DISCARD_RATE' = 'FINAL_RATE'
,'N_OBS_TRIPS_F' = 'n_obs_trips'
,'STRATA_FULL' = 'STRATA'
,'CAMS_DISCARD_RATE' = 'FINAL_RATE'
,'SPECIES_ITIS' = 'SPECIES_ITIS_EVAL') %>%
mutate(DATE_RUN = as.character(lubridate::today())
, FY = as.integer(FY)) %>%
dplyr::select(
DATE_RUN,
FY,
YEAR,
MONTH,
SPECIES_ITIS,
COMMON_NAME,
FY_TYPE,
ACTIVITY_CODE,
GEARCODE,
NEGEAR,
VTRSERNO,
CAMSID,
TRIP_TYPE,
AREA,
LINK1,
N_OBS_TRIPS_F,
# STRATA_USED,
STRATA_FULL,
STRATA_ASSUMED,
DISCARD_SOURCE,
OBS_DISCARD,
SUBTRIP_KALL,
CAMS_DISCARD_RATE,
DISC_MORT_RATIO,
DISCARD,
CV,
eval(stratvars)
)
unique(cy_discard_example$CV)
cy_discard_example$CV[is.nan(cy_discard_example$CV)]<-NA
# cy_discard_example$DISCARD_RATE <- #round(cy_discard_example$DISCARD_RATE, 5)
names(cy_discard_example) = toupper(names(cy_discard_example))
species$COMMON_NAME[i]
#db_drop_table(con = bcon, table = 'MAPS.CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', force = F)
dbWriteTable(bcon, name = 'CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', value = cy_discard_example, overwrite = T)
# save trip by trip info to RDS
#saveRDS(final_table, file = paste0('discard_est_', species_itis, '.RDS'))
# saveRDS(final_table, file = paste0('discard_est_', species_itis, '.RDS'))
#---------------------------------------------------------------------#
# End loop
#Need to modify this loop so it produces the oracle tables on each loop.
t2 = Sys.time()
print(paste(species_itis, ' RAN IN ', t2-t1, ' SECONDS', sep = ''))
}
# trips that were not observed
check <- final_table %>%
filter(is.na(LINK1)) %>%
filter(is.na(FINAL_RATE))
# See if any trips do not have a discard source
final_table %>%
filter(is.na(DISCARD_SOURCE))
# see what trips have CV and which do not
final_table %>%
group_by(DISCARD_SOURCE) %>%
dplyr::summarise(mean(CV, na.rm = F))
# examine strata that have NaN for DISC_EST
dest_strata_f %>%
View()
db_drop_table(con = bcon, table = 'MAPS.CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', force = F)
dbWriteTable(bcon, name = 'CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', value = cy_discard_example, overwrite = T)
dbWriteTable(bcon
, cy_discard_example
, name ='CAMS_DISCARD_EXAMPLE_CY_butterfish_19'
, temporary = FALSE
, batch.rows = nrow(cy_discard_example)
,overwrite=TRUE
# , append = TRUE)
,field.types = c(
DATE_RUN = "VARCHAR2(30)"
,FY = "NUMBER(38,0)"
,SPECIES_ITIS_EVAL = "VARCHAR2(30)"
,COMMON_NAME = "VARCHAR2(30)"
,FY_TYPE = "VARCHAR2(30)"
,ACTIVITY_CODE_1 = "VARCHAR2(30)"
,VTSERNO = "VARCHAR2(30)"
,LINK1 = "VARCHAR2(30)"
,N_OBS_TRIPS_F = "NUMBER"
,STRATA_FULL = "VARCHAR2(30)"
,STRATA_ASSUMED = "VARCHAR2(30)"
,DISCARD_RATE = "NUMBER"
,DISCARD_SOURCE = "VARCHAR2(30)"
,OBS_DISCARD = "NUMBER"
,DISC_MORT_RATIO = "NUMBER"
,DISCARD = "NUMBER"
,CV = "NUMBER"
,SUBTRIP_KALL = "NUMBER"
,SPECIES_STOCK = "VARCHAR2(30)"
,CAMS_GEAR_GROUP = "VARCHAR2(30)"
,MESH_GROUP = "VARCHAR2(30)"
,HALFOFYEAR = "NUMBER"
,REGION = "VARCHAR2(30)"
,TRIPCATEGORY = "VARCHAR2(30)"
,ACCESSAREA = "VARCHAR2(30)"
,TRIP_TYPE = "VARCHAR2(30)")
)
#connect to cams_garfo
dw_maps <- config::get(config = "cams_garfo", file = "~/config_group.yml")
# Connect to database - move this to config file in the future - quick addition for server
connectString <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", dw_maps$host, ")(PORT=", dw_maps$port, "))",
"(CONNECT_DATA=(SERVICE_NAME=",dw_maps$svc, ")))",
sep = ""
)
# Connect to oracle each loop in case of timeouts
acon <- ROracle::dbConnect(
drv = ROracle::Oracle(),
username = dw_maps$uid,
password = dw_maps$pwd,
dbname = connectString
)
# create example table on MAPS
# grant table to cams_garfo
# dbSendQuery(bcon, statement = "GRANT SELECT ON MAPS.CAMS_DISCARD_EXAMPLE_GF19 TO CAMS_GARFO")
# create table ins cams_garfo
db_drop_table(ccon, "CAMS_DISCARD_EXAMPLE_CY_stripedbass_19")
dbWriteTable(ccon, name = 'CAMS_DISCARD_EXAMPLE_CY_stripedbass_19', value = cy_discard_example, overwrite = T)
# grant the cams_garfo table to cams_garfo_nefsc
dbSendQuery(ccon, statement = "GRANT SELECT ON CAMS_GARFO.CAMS_DISCARD_EXAMPLE_CY_stripedbass_19 TO CAMS_GARFO_FOR_NEFSC")
# check it worked!
tbl(bcon, sql("select * from MAPS.CAMS_DISCARD_EXAMPLE_CY_stripedbass_19"))
tbl(ccon, sql("select * from CAMS_GARFO.CAMS_DISCARD_EXAMPLE_CY_stripedbass_19"))
noaa-garfo/discaRd documentation built on April 17, 2025, 10:32 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
knitr::opts_chunk$set(echo=FALSE, warning = FALSE, message = FALSE, cache = FALSE, progress = TRUE, verbose = FALSE, comment = F , error = FALSE, dev = 'png', dpi = 200)
t1 = Sys.time() # setwd("C:/Users/benjamin.galuardi/Documents/GitHub/discaRd/CAMS/") library(tidyverse) library(odbc) library(ROracle) library(dplyr, warn.conflicts = FALSE) # library(dbplyr) library(ggplot2) # library(config) library(stringr) #library(discaRd) devtools::load_all() options(scipen = 999) # local run #dw_maps <- config::get(value = "maps", file = "~/config.yml") # # if on server.. # dw_maps <- config::get(value = "maps", file = "~/config.yml") dw_maps <- config::get(config = "maps", file = "~/config_group.yml") # # bcon <- dbConnect(odbc::odbc(), # DSN = dw_maps$dsn, # UID = dw_maps$uid, # PWD = dw_maps$pwd) # Connect to database - move this to config file in the future - quick addition for server connectString <- paste( "(DESCRIPTION=", "(ADDRESS=(PROTOCOL=tcp)(HOST=", dw_maps$host, ")(PORT=", dw_maps$port, "))", "(CONNECT_DATA=(SERVICE_NAME=",dw_maps$svc, ")))", sep = "" ) # Connect to oracle each loop in case of timeouts bcon <- ROracle::dbConnect( drv = ROracle::Oracle(), username = dw_maps$uid, password = dw_maps$pwd, dbname = connectString ) '%!in%' <- function(x,y)!('%in%'(x,y)) source('~/discaRd/CAMS/R/cams_discard_functions.R')
FY <- 2020 FY_TYPE = 'MAY START' #--------------------------------------------------------------------------# # group of species ITIS codes.. # SMB, river herring, bluefish, summer flounder, # black seabass and scup. #Not sure what else is needed for herring and shad catch cap. itis <- c('164499', '160617', '564139', '160855', '564136', '564130', '564151', '564149', '564145', '164793', '164730', '164791') #itis <- itis itis_num <- as.numeric(itis) species = tbl(bcon, sql("select * from MAPS.CAMS_DISCARD_MORTALITY_STOCK")) %>% collect() %>% filter(SPECIES_ITIS %in% itis_num) %>% group_by(SPECIES_ITIS) %>% slice(1) species$ITIS_TSN <- stringr::str_sort(itis) #species$ITIS_TSN <- as.numeric(species$SPECIES_ITIS) # species$ITIS_TSN <- as.character(species$SPECIES_ITIS) #--------------------------------------------------------------------------# # a sumamry table for comaprison # final_discard_table = data.frame(YEAR = FY, SPECIES_ITIS = species$ITIS_TSN, COMNAME = species$COMMON_NAME, DISCARD = NA) #--------------------------------------------------------------------------#
# get catch and matched obs data together c_o_dat2 <- tbl(bcon, sql( " with obs_cams as ( select year , month , case when month in (5,6,7,8,9,10) then 1 when month in (11,12,1,2,3,4) then 2 end as halfofyear -- , carea ,region , AREA , vtrserno , link1 , docid , CAMSID , nespp3 , itis_tsn as SPECIES_ITIS , itis_group1 , SECGEAR_MAPPED as GEARCODE , NEGEAR , GEARTYPE , MESHGROUP , SECTID , case when PERMIT = '000000' then 'STATE' else 'FED' end as trip_type , tripcategory , accessarea , activity_code_1 -- , permit_EFP_1 -- , permit_EFP_2 -- , permit_EFP_3 -- , permit_EFP_4 -- , redfish_exemption -- , closed_area_exemption -- , sne_smallmesh_exemption -- , xlrg_gillnet_exemption , NVL(sum(discard),0) as discard , NVL(round(max(subtrip_kall)),0) as subtrip_kall , NVL(round(max(obs_kall)),0) as obs_kall , NVL(sum(discard)/nullif(round(max(obs_kall)), 0), 0) as dk from MAPS.CAMS_OBS_CATCH -- where nespp3 is not null group by year -- , carea ,region , AREA , vtrserno , link1 , docid , nespp3 , itis_tsn , itis_group1 , SECGEAR_MAPPED , NEGEAR , GEARTYPE , MESHGROUP , SECTID , case when PERMIT = '000000' then 'STATE' else 'FED' end , CAMSID , month , halfofyear , tripcategory , accessarea , activity_code_1 -- , permit_EFP_1 -- , permit_EFP_2 -- , permit_EFP_3 -- , permit_EFP_4 -- , redfish_exemption -- , closed_area_exemption -- , sne_smallmesh_exemption -- , xlrg_gillnet_exemption order by vtrserno asc ) select case when MONTH in (1,2,3,4) then YEAR-1 else YEAR end as GF_YEAR , case when MONTH in (1,2,3) then YEAR-1 else YEAR end as SCAL_YEAR , o.* , c.match_nespp3 , coalesce(c.match_nespp3, o.nespp3) as nespp3_final from obs_cams o left join apsd.s_nespp3_match_conv c on o.nespp3 = c.nespp3 " ) ) %>% collect() state_trips = c_o_dat2 %>% filter(TRIP_TYPE == 'STATE') fed_trips = c_o_dat2 %>% filter(TRIP_TYPE == 'FED') fed_trips = fed_trips %>% mutate(ROWID = 1:nrow(fed_trips)) %>% relocate(ROWID) # filter out link1 that are doubled on VTR multilink = fed_trips %>% filter(!is.na(LINK1)) %>% group_by(VTRSERNO) %>% dplyr::summarise(nlink1 = n_distinct(LINK1)) %>% arrange(desc(nlink1)) %>% filter(nlink1>1) remove_links = fed_trips %>% filter(is.na(SPECIES_ITIS) & !is.na(LINK1) & VTRSERNO %in% multilink$VTRSERNO) %>% dplyr::select(LINK1) %>% distinct() remove_id = fed_trips %>% filter(is.na(SPECIES_ITIS) & !is.na(LINK1) & VTRSERNO %in% multilink$VTRSERNO) %>% distinct(ROWID) fed_trips = fed_trips %>% filter(ROWID %!in% remove_id$ROWID) non_gf_dat = fed_trips%>% #filter(substr(ACTIVITY_CODE_1, 1,3) != 'NMS') %>% bind_rows(., state_trips) c_o_dat2 <- non_gf_dat
# Stratification variables stratvars = c('SPECIES_STOCK' ,'CAMS_GEAR_GROUP' , 'MESHGROUP' # , 'HALFOFYEAR' # , 'REGION' , 'TRIPCATEGORY' , 'ACCESSAREA') # Begin loop for(i in 1:length(species$ITIS)){ print(paste0('Running ', species$COMMON_NAME[i])) # species_nespp3 = species$NESPP3[i] #species_itis = species$ITIS_TSN[i] species_itis <- as.character(species$ITIS_TSN[i]) species_itis_srce = as.character(as.numeric(species$ITIS_TSN[i])) #--------------------------------------------------------------------------# # Support table import by species #GEAR TABLE CAMS_GEAR_STRATA = tbl(bcon, sql(' select * from MAPS.CAMS_GEARCODE_STRATA')) %>% collect() %>% dplyr::rename(GEARCODE = VTR_GEAR_CODE) %>% # filter(NESPP3 == species_nespp3) %>% filter(SPECIES_ITIS == species_itis) %>% dplyr::select(-NESPP3, -SPECIES_ITIS) #Used Alewife stratificatino in support tables CAMS_GEAR_STRATA <- read.csv("~/discaRd/CAMS/MODULES/CALENDAR/SBRM_CAMS_GEAR_GROUP.csv") CAMS_GEAR_STRATA$CAMS_GEAR_GROUP <- as.character(CAMS_GEAR_STRATA$CAMS_GEAR_GROUP) # # Stat areas table # unique stat areas for stock ID if needed STOCK_AREAS = tbl(bcon, sql('select * from MAPS.CAMS_STATAREA_STOCK')) %>% # filter(NESPP3 == species_nespp3) %>% # removed & AREA_NAME == species_stock dplyr::filter(SPECIES_ITIS == species_itis) %>% collect() %>% group_by(AREA_NAME) %>% distinct(STAT_AREA) %>% mutate(AREA = as.character(STAT_AREA) , SPECIES_STOCK = AREA_NAME) %>% ungroup() #%>% #dplyr::select(SPECIES_STOCK, AREA) #Used Alewife stratificatino in support tables STOCK_AREAS <- read.csv("~/discaRd/CAMS/MODULES/CALENDAR/SBRM_STAT_AREA.csv") STOCK_AREAS$AREA <- as.character(STOCK_AREAS$AREA) STOCK_AREAS$STAT_AREA<- as.character(STOCK_AREAS$STAT_AREA) # #Mortality table CAMS_DISCARD_MORTALITY_STOCK = tbl(bcon, sql("select * from MAPS.CAMS_DISCARD_MORTALITY_STOCK")) %>% collect() %>% mutate(SPECIES_STOCK = AREA_NAME , GEARCODE = CAMS_GEAR_GROUP) %>% select(-AREA_NAME) %>% mutate(CAMS_GEAR_GROUP = as.character(CAMS_GEAR_GROUP)) %>% # filter(NESPP3 == species_nespp3) %>% filter(SPECIES_ITIS == species_itis_srce) %>% # dplyr::select(-NESPP3, -SPECIES_ITIS) %>% dplyr::rename(DISC_MORT_RATIO = Discard_Mortality_Ratio) #Used Alewife stratificatino in support tables CAMS_DISCARD_MORTALITY_STOCK <- read.csv("~/discaRd/CAMS/MODULES/CALENDAR/SBRM_CAMS_DISCARD_MORTALITY_STOCK.csv") CAMS_DISCARD_MORTALITY_STOCK$CAMS_GEAR_GROUP <- as.character(CAMS_DISCARD_MORTALITY_STOCK$CAMS_GEAR_GROUP) CAMS_DISCARD_MORTALITY_STOCK$GEARCODE <- as.character(CAMS_DISCARD_MORTALITY_STOCK$GEARCODE) #--------------------------------------------------------------------------------# # make tables ddat_focal <- c_o_dat2 %>% filter(YEAR == FY) %>% ## time element is here!! filter(AREA %in% STOCK_AREAS$AREA) %>% mutate(LIVE_POUNDS = SUBTRIP_KALL ,SEADAYS = 0 , NESPP3 = NESPP3_FINAL) %>% left_join(., y = STOCK_AREAS, by = 'AREA') %>% left_join(., y = CAMS_GEAR_STRATA, by = 'GEARCODE') %>% left_join(., y = CAMS_DISCARD_MORTALITY_STOCK , by = c('SPECIES_STOCK', 'CAMS_GEAR_GROUP') ) %>% dplyr::select(-SPECIES_ITIS.y, -GEARCODE.y) %>% dplyr::rename(SPECIES_ITIS = 'SPECIES_ITIS.x', GEARCODE = 'GEARCODE.x') %>% relocate('COMMON_NAME','SPECIES_ITIS','NESPP3','SPECIES_STOCK','CAMS_GEAR_GROUP','DISC_MORT_RATIO') ddat_prev <- c_o_dat2 %>% filter(YEAR == FY-1) %>% ## time element is here!! filter(AREA %in% STOCK_AREAS$AREA) %>% mutate(LIVE_POUNDS = SUBTRIP_KALL ,SEADAYS = 0 , NESPP3 = NESPP3_FINAL) %>% left_join(., y = STOCK_AREAS, by = 'AREA') %>% left_join(., y = CAMS_GEAR_STRATA, by = 'GEARCODE') %>% left_join(., y = CAMS_DISCARD_MORTALITY_STOCK , by = c('SPECIES_STOCK', 'CAMS_GEAR_GROUP') ) %>% dplyr::select(-SPECIES_ITIS.y, -GEARCODE.y) %>% dplyr::rename(SPECIES_ITIS = 'SPECIES_ITIS.x', GEARCODE = 'GEARCODE.x') %>% relocate('COMMON_NAME','SPECIES_ITIS','NESPP3','SPECIES_STOCK','CAMS_GEAR_GROUP','DISC_MORT_RATIO') # need to slice the first record for each observed trip.. these trips are multi rowed while unobs trips are single row.. ddat_focal_cy = ddat_focal %>% filter(!is.na(LINK1)) %>% mutate(SPECIES_EVAL_DISCARD = case_when(SPECIES_ITIS == species_itis ~ DISCARD )) %>% mutate(SPECIES_EVAL_DISCARD = coalesce(SPECIES_EVAL_DISCARD, 0)) %>% group_by(LINK1, VTRSERNO) %>% arrange(desc(SPECIES_EVAL_DISCARD)) %>% slice(1) %>% ungroup() # and join to the unobserved trips ddat_focal_cy = ddat_focal_cy %>% union_all(ddat_focal %>% filter(is.na(LINK1)) %>% group_by(VTRSERNO, CAMSID) %>% slice(1) %>% ungroup() ) # if using the combined catch/obs table, which seems necessary for groundfish.. need to roll your own table to use with run_discard function # DO NOT NEED TO FILTER SPECIES HERE. NEED TO RETAIN ALL TRIPS. THE MAKE_BDAT_FOCAL.R FUNCTION TAKES CARE OF THIS. bdat_cy = ddat_focal %>% filter(!is.na(LINK1)) %>% mutate(DISCARD_PRORATE = DISCARD , OBS_AREA = AREA , OBS_HAUL_KALL_TRIP = OBS_KALL , PRORATE = 1) # set up trips table for previous year ddat_prev_cy = ddat_prev %>% filter(!is.na(LINK1)) %>% mutate(SPECIES_EVAL_DISCARD = case_when(SPECIES_ITIS == species_itis ~ DISCARD )) %>% mutate(SPECIES_EVAL_DISCARD = coalesce(SPECIES_EVAL_DISCARD, 0)) %>% group_by(LINK1, VTRSERNO) %>% arrange(desc(SPECIES_EVAL_DISCARD)) %>% slice(1) %>% ungroup() ddat_prev_cy = ddat_prev_cy %>% union_all(ddat_prev %>% filter(is.na(LINK1)) %>% group_by(VTRSERNO, CAMSID) %>% slice(1) %>% ungroup() ) # previous year observer data needed.. bdat_prev_cy = ddat_prev %>% filter(!is.na(LINK1)) %>% mutate(DISCARD_PRORATE = DISCARD , OBS_AREA = AREA , OBS_HAUL_KALL_TRIP = OBS_KALL , PRORATE = 1) # Run the discaRd functions on previous year d_prev = run_discard(bdat = bdat_prev_cy , ddat = ddat_prev_cy , c_o_tab = ddat_prev # , year = 2018 # , species_nespp3 = species_nespp3 , species_itis = species_itis , stratvars = stratvars , aidx = c(1,2) #maybe this should be bumped up? ) # Run the discaRd functions on current year d_focal = run_discard(bdat = bdat_cy , ddat = ddat_focal_cy , c_o_tab = ddat_focal # , year = 2019 # , species_nespp3 = '081' # haddock... # , species_nespp3 = species_nespp3 #'081' #cod... , species_itis = species_itis , stratvars = stratvars , aidx = c(1,2) #maybe this should be bumped up? ) # summarize each result for convenience dest_strata_p = d_prev$allest$C %>% summarise(STRATA = STRATA , N = N , n = n , orate = round(n/N, 2) , drate = RE_mean , KALL = K, disc_est = round(D) , CV = round(RE_rse, 2) ) dest_strata_f = d_focal$allest$C %>% summarise(STRATA = STRATA , N = N , n = n , orate = round(n/N, 2) , drate = RE_mean , KALL = K, disc_est = round(D) , CV = round(RE_rse, 2) ) # substitute transition rates where needed trans_rate_df = dest_strata_f %>% left_join(., dest_strata_p, by = 'STRATA') %>% mutate(n.y = coalesce(n.y, 0)) %>% mutate(STRATA = STRATA , n_obs_trips = n.x , in_season_rate = drate.x , previous_season_rate = drate.y , trans_rate = get.trans.rate(l_observed_trips = n_obs_trips , l_assumed_rate = previous_season_rate , l_inseason_rate = in_season_rate ) ) #Fit model for an alternative to the assumed rate # # m_data <- d_focal$res %>% filter(!is.na(LINK1)) # # mod <- glm(OBS_DISCARD ~ offset(log(m_data$OBS_KALL+1))+ # SPECIES_STOCK + # CAMS_GEAR_GROUP + # MESHGROUP + # HALFOFYEAR + # REGION, # family = poisson(), # data = m_data) # # data_focal <- d_focal$res # # data_focal$OBS_KALL <- log(data_focal$OBS_KALL+1) # # # Predict # data_focal <- data.frame(data_focal, D_MODEL = predict(mod, data_focal, type = "response")) #*DISC_MORT_RATIO #Then join to trans_rate_df final_table = trans_rate_df %>% right_join(., y = d_focal$res, by = 'STRATA') %>% as_tibble() %>% mutate(SPECIES_ITIS_EVAL = species_itis , COMNAME_EVAL = species$COMMON_NAME[i] , FISHING_YEAR = FY , FY_TYPE = FY_TYPE) %>% mutate(FINAL_RATE = case_when(n_obs_trips >= 5 ~ in_season_rate # this is an in season rate ,n_obs_trips < 5 & n.y >=5 ~ trans_rate ,n_obs_trips < 5 & n.y < 5 ~ ARATE) ) %>% mutate(DISCARD_SOURCE = case_when(!is.na(LINK1) ~ 'O' , is.na(LINK1) & n_obs_trips >= 5 ~ 'I' , is.na(LINK1) & n_obs_trips < 5 & n.y>=5 ~ 'T' , is.na(LINK1) & n_obs_trips < 5 & n.y < 5 ~ 'A')) final_table = final_table %>% mutate(DISC_MORT_RATIO = coalesce(DISC_MORT_RATIO, 1)) %>% mutate(CV = case_when(DISCARD_SOURCE == 'O' ~ 0 , DISCARD_SOURCE == 'E' ~ CV.x , DISCARD_SOURCE == 'T' ~ CV.x , DISCARD_SOURCE == 'A' ~ CV), DISCARD = case_when(!is.na(LINK1) ~ DISC_MORT_RATIO*OBS_DISCARD , is.na(LINK1) ~ DISC_MORT_RATIO*FINAL_RATE*SUBTRIP_KALL), STRATA_USED = case_when(DISCARD_SOURCE == 'E' ~ STRATA ,DISCARD_SOURCE == 'T' ~ STRATA ,DISCARD_SOURCE == 'A' ~ STRATA_ASSUMED))%>% rename('ACTIVITY_CODE'='ACTIVITY_CODE_1') %>% select(-SPECIES_ITIS) final_table %>% dplyr::group_by(TRIP_TYPE) %>% dplyr::summarise(Discard_total = sum(DISCARD, na.rm=TRUE), Kall_total = sum(SUBTRIP_KALL, na.rm=TRUE)) final_table$FINAL_RATE[is.na(final_table$FINAL_RATE)]<-0 final_table$COMMON_NAME <- species$COMMON_NAME[i] sum(final_table$DISCARD, na.rm=TRUE) species$COMMON_NAME[i] #add subtrip kall after CV cy_discard_example = final_table %>% # mutate(GF_STOCK_DEF = paste0(COMNAME_EVAL, '-', SPECIES_STOCK)) %>% dplyr::rename('DISCARD_RATE' = 'FINAL_RATE' ,'N_OBS_TRIPS_F' = 'n_obs_trips' ,'STRATA_FULL' = 'STRATA' ,'CAMS_DISCARD_RATE' = 'FINAL_RATE' ,'SPECIES_ITIS' = 'SPECIES_ITIS_EVAL') %>% mutate(DATE_RUN = as.character(lubridate::today()) , FY = as.integer(FY)) %>% dplyr::select( DATE_RUN, FY, YEAR, MONTH, SPECIES_ITIS, COMMON_NAME, FY_TYPE, ACTIVITY_CODE, GEARCODE, NEGEAR, VTRSERNO, CAMSID, TRIP_TYPE, AREA, LINK1, N_OBS_TRIPS_F, # STRATA_USED, STRATA_FULL, STRATA_ASSUMED, DISCARD_SOURCE, OBS_DISCARD, SUBTRIP_KALL, CAMS_DISCARD_RATE, DISC_MORT_RATIO, DISCARD, CV, eval(stratvars) ) unique(cy_discard_example$CV) cy_discard_example$CV[is.nan(cy_discard_example$CV)]<-NA # cy_discard_example$DISCARD_RATE <- #round(cy_discard_example$DISCARD_RATE, 5) names(cy_discard_example) = toupper(names(cy_discard_example)) species$COMMON_NAME[i] #db_drop_table(con = bcon, table = 'MAPS.CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', force = F) dbWriteTable(bcon, name = 'CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', value = cy_discard_example, overwrite = T) # save trip by trip info to RDS #saveRDS(final_table, file = paste0('discard_est_', species_itis, '.RDS')) # saveRDS(final_table, file = paste0('discard_est_', species_itis, '.RDS')) #---------------------------------------------------------------------# # End loop #Need to modify this loop so it produces the oracle tables on each loop. t2 = Sys.time() print(paste(species_itis, ' RAN IN ', t2-t1, ' SECONDS', sep = '')) }
# trips that were not observed check <- final_table %>% filter(is.na(LINK1)) %>% filter(is.na(FINAL_RATE)) # See if any trips do not have a discard source final_table %>% filter(is.na(DISCARD_SOURCE)) # see what trips have CV and which do not final_table %>% group_by(DISCARD_SOURCE) %>% dplyr::summarise(mean(CV, na.rm = F)) # examine strata that have NaN for DISC_EST dest_strata_f %>% View()
db_drop_table(con = bcon, table = 'MAPS.CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', force = F) dbWriteTable(bcon, name = 'CAMS_DISCARD_EXAMPLE_CY_BLACKSEABASS_19', value = cy_discard_example, overwrite = T) dbWriteTable(bcon , cy_discard_example , name ='CAMS_DISCARD_EXAMPLE_CY_butterfish_19' , temporary = FALSE , batch.rows = nrow(cy_discard_example) ,overwrite=TRUE # , append = TRUE) ,field.types = c( DATE_RUN = "VARCHAR2(30)" ,FY = "NUMBER(38,0)" ,SPECIES_ITIS_EVAL = "VARCHAR2(30)" ,COMMON_NAME = "VARCHAR2(30)" ,FY_TYPE = "VARCHAR2(30)" ,ACTIVITY_CODE_1 = "VARCHAR2(30)" ,VTSERNO = "VARCHAR2(30)" ,LINK1 = "VARCHAR2(30)" ,N_OBS_TRIPS_F = "NUMBER" ,STRATA_FULL = "VARCHAR2(30)" ,STRATA_ASSUMED = "VARCHAR2(30)" ,DISCARD_RATE = "NUMBER" ,DISCARD_SOURCE = "VARCHAR2(30)" ,OBS_DISCARD = "NUMBER" ,DISC_MORT_RATIO = "NUMBER" ,DISCARD = "NUMBER" ,CV = "NUMBER" ,SUBTRIP_KALL = "NUMBER" ,SPECIES_STOCK = "VARCHAR2(30)" ,CAMS_GEAR_GROUP = "VARCHAR2(30)" ,MESH_GROUP = "VARCHAR2(30)" ,HALFOFYEAR = "NUMBER" ,REGION = "VARCHAR2(30)" ,TRIPCATEGORY = "VARCHAR2(30)" ,ACCESSAREA = "VARCHAR2(30)" ,TRIP_TYPE = "VARCHAR2(30)") ) #connect to cams_garfo dw_maps <- config::get(config = "cams_garfo", file = "~/config_group.yml") # Connect to database - move this to config file in the future - quick addition for server connectString <- paste( "(DESCRIPTION=", "(ADDRESS=(PROTOCOL=tcp)(HOST=", dw_maps$host, ")(PORT=", dw_maps$port, "))", "(CONNECT_DATA=(SERVICE_NAME=",dw_maps$svc, ")))", sep = "" ) # Connect to oracle each loop in case of timeouts acon <- ROracle::dbConnect( drv = ROracle::Oracle(), username = dw_maps$uid, password = dw_maps$pwd, dbname = connectString ) # create example table on MAPS # grant table to cams_garfo # dbSendQuery(bcon, statement = "GRANT SELECT ON MAPS.CAMS_DISCARD_EXAMPLE_GF19 TO CAMS_GARFO") # create table ins cams_garfo db_drop_table(ccon, "CAMS_DISCARD_EXAMPLE_CY_stripedbass_19") dbWriteTable(ccon, name = 'CAMS_DISCARD_EXAMPLE_CY_stripedbass_19', value = cy_discard_example, overwrite = T) # grant the cams_garfo table to cams_garfo_nefsc dbSendQuery(ccon, statement = "GRANT SELECT ON CAMS_GARFO.CAMS_DISCARD_EXAMPLE_CY_stripedbass_19 TO CAMS_GARFO_FOR_NEFSC") # check it worked! tbl(bcon, sql("select * from MAPS.CAMS_DISCARD_EXAMPLE_CY_stripedbass_19")) tbl(ccon, sql("select * from CAMS_GARFO.CAMS_DISCARD_EXAMPLE_CY_stripedbass_19"))
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.