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)

options(knitr.kable.NA = '')
options(dplyr.summarise.inform = FALSE)

Background

Discards of groundfish species are used for several purposes throughout the year. Quota Monitoring requires these on a weekly basis. Discard rates for sector trips are shared with Sector managers. End of year ACL accounting also requires discard estimates from all trips.

This means a full accounting of groundfish discards occurs in several steps.

Steps for Groundfish Trips

Pull all merged trips from CAMS_OBS_CATCH. Here, it is a good idea to pull only a few years and not the whole table, which is ~12 years of data
Separate Groundfish trips from non-groundfish trips (Use CAMS GF column)
Set stratification variables for groundfish trips

# FULL Stratification variables

stratvars = c( 'SPECIES_STOCK'
                            # , 'GEARCODE'  # this is the SECGEAR_MAPPED variable
              , 'CAMS_GEAR_GROUP'
              , 'MESHGROUP'
              , 'SECTID'
              , 'EM'  # previously was four columns of EFP information.. 
              , "REDFISH_EXEMPTION"
              , "SNE_SMALLMESH_EXEMPTION"
              , "XLRG_GILLNET_EXEMPTION"
              )

- SPECIES_STOCK is taken from CAMS support table `MAPS.CAMS_STATAREA_STOCK`
- CAMS_GEAR_GROUP is derived from a support table (`MAPS.CAMS_GEARCODE_STRATA`)
- MESHGROUP is hardcoded for all trips according to decisions made by the mesh subgroup (see summary when available)
- SECTID comes from a CAMS matching table (`MAPS.MATCH_MULT_SECTID`)
- EFP and Exemptions are built in to `CAMS_LANDINGS`, the precurdor for `CAMS_OBS_CATCH`

Perform first pass of discaRd
- there are two sub-passes for year t and year t-1
Perform second pass of discaRd with discard rates rolled up for all Sectors
- Common Pool is distinguished from the rest of Sectors
- Simplified stratification is used:

# Assumed Stratification variables

stratvars = c('SPECIES_STOCK'
              , 'CAMS_GEAR_GROUP'
              , 'MESHGROUP'
                            , 'SECTOR_TYPE'
              )

The discaRd functions allow for an assumed rate to be calculated. This assumed rate is relative to the stratification used in the functions. Here, we utilize this feature to generate a broad stock rate. the stratification here is simply SPECIES_STOCK
For each pass, a transition rate is calculated between year t and year t-1. This rate determines how much, if any, information is used from previous years.
The two passes are joined in a hierarchical manner. Rates and DISCARD_SOURCE for groundfish estimates from groundfish trips are as follows (in parentheses):
(I) in season rate; >= 5 trips in Full Stratification
(T) Transition in season rate; < 5 trips in Full Stratification, year t, AND >= 5 trips in year t-1
(A) Assumed rate. This is the second pass rate that rolls up all Sectors. Common Pool trips are separated in this strata.
(B) Broad stock rate is used when other criteria are not met.
(EM) Electronic monitoring (EM) video based estimate. If the trip is reviewed and passed; and there was no human observer on board.
(VTR) VTR (self-reported) discard estimate on an EM trip that was reviewed and failed; and there was no human observer on board.
(DELTA) Delta model estimate of VTR discard on an EM trip that was reviewed and failed; and there was no human observer on board.
(O) Observed values used from observed trips; discard rate is NOT USED.

groundfish estimates from non-groundfish trips are as follows:

(I) in season rate; >= 5 trips in Full Stratification
(T) Transition in season rate; < 5 trips in Full Stratification, year t, AND >= 5 trips in year t-1
(GM) Assignment is from the second pass (e.g. Stock/Gear/Meshgroup). This is the rate when there were <5 trips in season and <5 in previous season at the full stratification level.
(G) Assignment is from the second pass (e.g. Stock/Gear). This rate uses two years of information (e.g. current year and previous year).
(N) No coverage in focal season and previous season; discard rate is not possible to estimate.
(O) Observed values used from observed rate trips; discard rate is NOT USED.
CV calculations are available for (I), (T), (GM), (G) and (A). (O) rates are not used and final discard values are not estimated.
Discard pounds per trip are calculated according to

    mutate(coalesce(DISC_MORT_RATIO, 1)) %>%
    mutate(CAMS_DISCARD = case_when(!is.na(LINK1) ~ DISC_MORT_RATIO*OBS_DISCARD
                                                         , is.na(LINK1) ~ DISC_MORT_RATIO*COAL_RATE*LIVE_POUNDS)

            # COAL_RATE is the final discard rate used. It is 'coalesced' from the (I), (A) and (B) rates

By assigning SPECIES_STOCK as a stratification variable, the computation time is reduced. Each subtrip may only occur in a single statistical area so it should never cross stock boundaries.

Once the full table (CAMS_OBS_CATCH) is loaded, each species takes ~12 seconds to process on the server. non-groundfish trips take ~1-2 minutes as there are many more trips and table rows.

Output tables are produced for each species and fishing year. These are then combined into a single View on MAPS and CAMS_GARFO

    MAPS.CAMS_DISCARD_ALL_YEARS
    CAMS_GARFO.CAMS_DISCARD_ALL_YEARS

    /* Get groundfish discards from groundfish trips for FY 2019 
       FY 2021 currently has EM values
    */

        select *
    from MAPS.CAMS_DISCARD_ALL_YEARS
     where GF = 1
    AND FED_OR_STATE = 'FED'
    AND FY = 2019
    AND SPECIES_ITIS in ('172877' ,
    '164712' ,
    '164732' ,
    '166774',
    '171341',
    '172873',
    '172909',
    '164727',
    '172933',
    '172746',
    '172905',
    '164744', 
    '630979')

Diagnostic Plots/Tables

# library(odbc)
library(ROracle)
library(dplyr, warn.conflicts = FALSE)
# library(dbplyr)
library(ggplot2)
# library(config)
library(stringr)
library(discaRd)
library(knitr)
library(kableExtra)
options(scipen = 999)


# condat <- config::get(value = "bgaluardi_cams_garfo", file = "~/config.yml")
# 
# ccon <- dbConnect(odbc::odbc(),
#                                   DSN = condat$dsn,
#                                   UID = condat$uid,
#                                   PWD = condat$pwd)


# mapsdat <- config::get(value = "maps", file = "~/config.yml")
# 
# con_maps <- ROracle::dbConnect(
#     drv = ROracle::Oracle(),
#     username = mapsdat$uid,
#     password = mapsdat$pwd,  
#     dbname = "NERO.world"
# )

con_maps = apsdFuns::roracle_login(key_name = 'apsd_ma', key_service = 'maps')

# con_maps <- dbConnect(odbc::odbc(), 
#                                   DSN = mapsdat$dsn, 
#                                   UID = mapsdat$uid, 
#                                   PWD = mapsdat$pwd)

db_example = tbl(con_maps, sql("

    select *
    from MAPS.CAMS_DISCARD_ALL_YEARS
     where GF = 1
    AND FED_OR_STATE = 'FED'
    AND FY = 2019
    AND ITIS_TSN in ('172877' ,
    '164712' ,
    '164732' ,
    '166774',
    '171341',
    '172873',
    '172909',
    '164727',
    '172933',
    '172746',
    '172905',
    '164744', 
    '630979')

")) %>% 
    collect()

# db_example %>% 
#   filter(DISCARD_SOURCE != 'O') %>% 
#   group_by(SPECIES_ITIS, DISCARD_SOURCE, STRATA_FULL, STRATA_ASSUMED) %>% 
#   slice(1) %>% 
#   ggplot()+
#   geom_bar(aes(x = SPECIES_STOCK, y = CAMS_DISCARD_RATE, fill = DISCARD_SOURCE), stat = 'identity', position = 'dodge')+
#   facet_wrap(~COMMON_NAME, scales = 'free')+
#   theme_light()

db_example %>% 
    # filter(DISCARD_SOURCE != 'O') %>% 
    group_by(ITIS_TSN, COMMON_NAME, DISCARD_SOURCE,SPECIES_STOCK) %>% 
    dplyr::summarise(DSUM = sum(CAMS_DISCARD, na.rm = T)) %>% 
    # slice(1) %>% 
    ggplot()+
    geom_bar(aes(x = SPECIES_STOCK, y = DSUM, fill = DISCARD_SOURCE), stat = 'identity', position = 'dodge')+
    facet_wrap(~COMMON_NAME, scales = 'free')+
    theme_light()

db_example %>% 
    group_by(COMMON_NAME
                     , SPECIES_STOCK) %>% 
    dplyr::summarise(nvtr = n_distinct(VTRSERNO)
                                     , KALL = sum(SUBTRIP_KALL, na.rm = T)
                                     , DISCARD = round(sum(CAMS_DISCARD, na.rm = T))) %>% 
    DT::datatable(caption = 'Discard Estimate by Stock, Species, Discard Source (using CAMS Stock ID)') %>% 
    DT::formatRound(c('nvtr','KALL','DISCARD'), digits = 0, interval = 3)
    # 
    # knitr::kable(format.args = list(big.mark = ",")) %>% 
    # kable_styling(bootstrap_options = c("striped", "hover", "responsive"))

match_stockid <- function(con_maps, species_itis){

    stock_dmis = tbl(con_maps, sql("
    select stock_id
    , comname
    , area
    , max(species_itis) as ITIS_TSN
    from fso.v_obSpeciesStockArea 
    where stock_id not like 'OTHER'
    and species_itis is not null
    group by stock_id, comname, species_itis, area
")
) %>%
    collect() %>% 
        mutate(ITIS_TSN = ifelse(COMNAME == 'OCEAN POUT', "630979", ITIS_TSN)) %>% 
        filter(ITIS_TSN == species_itis)


 t2 =   tbl(con_maps, sql('select * from MAPS.CFG_STATAREA_STOCK')) %>%
 filter(ITIS_TSN == species_itis) %>% 
    collect() %>%
    mutate('AREA' = as.character(AREA))


 t2 %>% 
    left_join(., stock_dmis, by = 'AREA') %>% 
    mutate(CAMS_STOCK_AREA = paste(COMMON_NAME, AREA_NAME, sep = '_')) %>% 
    dplyr::rename('DMIS_STOCK_ID' = STOCK_ID
                                , 'ITIS_TSN' = 'ITIS_TSN.x') %>% 
    dplyr::select(-ITIS_TSN.y)


}


species = tbl(con_maps, sql("
select distinct(b.species_itis)
    , COMNAME
    , a.nespp3
from fso.v_obSpeciesStockArea a
left join (select *  from APSD.CAMS_GEARCODE_STRATA) b on a.nespp3 = b.nespp3
where stock_id not like 'OTHER'
and b.species_itis is not null
")
) %>% 
    collect()

discard2019 = tbl(con_maps, sql("
select round(sum(POKGMASS_DISCARD)) POKGMASS
,round(sum(CODGMSS_DISCARD)) CODGMSS
,round(sum(CODGBE_DISCARD)) CODGBE
,round(sum(CODGBW_DISCARD)) CODGBW
,round(sum(FLDSNEMA_DISCARD)) FLDSNEMA
,round(sum(FLWGB_DISCARD)) FLWGB
,round(sum(FLWGMSS_DISCARD)) FLWGMSS
,round(sum(PLAGMMA_DISCARD)) PLAGMMA
,round(sum(YELCCGM_DISCARD)) YELCCGM
,round(sum(HADGBW_DISCARD)) HADGBW
,round(sum(WITGMMA_DISCARD)) WITGMMA
-- ,round(sum(FLWGMSS_DISCARD)) FLWGMSS
,round(sum(HALGMMA_DISCARD)) HALGMMA
,round(sum(YELGB_DISCARD)) YELGB
,round(sum(FLGMGBSS_DISCARD)) FLGMGBSS
,round(sum(HKWGMMA_DISCARD)) HKWGMMA
,round(sum(REDGMGBSS_DISCARD)) REDGMGBSS
-- ,round(sum(FLWGB_DISCARD)) FLWGB
,round(sum(HADGM_DISCARD)) HADGM
,round(sum(OPTGMMA_DISCARD)) OPTGMMA
,round(sum(WOLGMMA_DISCARD)) WOLGMMA
,round(sum(FLWSNEMA_DISCARD)) FLWSNEMA
,round(sum(HADGBE_DISCARD)) HADGBE
-- ,round(sum(CODGBW_DISCARD)) CODGBW
,round(sum(YELSNE_DISCARD)) YELSNE

from apsd.dmis_all_years
where fishing_year = 2019
"))  %>% 
  collect() %>% 
  t() %>% 
  as.data.frame() %>% 
  mutate(stock = row.names(.))

names(discard2019)[1] = 'DMIS_DISCARD'

discard2019$STOCK_ID = unlist(lapply(strsplit(discard2019$stock, split = '_'), function(x) x[[1]]))

discard2019 = discard2019 %>% dplyr::select(-stock)


# get CAMS estimates

comp_list = NULL

ii = 1

for(j in species$SPECIES_ITIS){

    comp_list[[ii]] <- db_example %>% 
        filter(ITIS_TSN == j)

    sid = match_stockid(con_maps, j) %>% 
        dplyr::select(DMIS_STOCK_ID, AREA, CAMS_STOCK_AREA)

    comp_list[[ii]] = comp_list[[ii]] %>% 
        left_join(sid, by = c('AREA')) %>% 
        group_by(COMMON_NAME, CAMS_STOCK_AREA, DMIS_STOCK_ID) %>% 
        dplyr::summarise(CAMS_DISCARD = round(sum(CAMS_DISCARD, na.rm = T))) %>% 
        filter(!is.na(COMMON_NAME))

    ii = ii+1

}

allgf_comp = do.call(rbind, comp_list) %>% 
    filter(!is.na(CAMS_STOCK_AREA))

# join to DMIS estimates

dmis_comp = allgf_comp %>% 
    group_by(COMMON_NAME, DMIS_STOCK_ID) %>% 
    dplyr::summarise(CAMS_DISCARD = round(sum(CAMS_DISCARD, na.rm = T))) %>% 
    left_join(., discard2019, by = c('DMIS_STOCK_ID' = 'STOCK_ID')) %>% 
    mutate(DMIS_CAMS_DIFF = DMIS_DISCARD - CAMS_DISCARD
                 , DMIS_CAMS_DIFF_PERC = (DMIS_DISCARD - CAMS_DISCARD)/DMIS_DISCARD)

dmis_comp %>% 
    DT::datatable(caption = 'DISCARD comparison, DMIS/CAMS (Uses DMIS Stock ID for summaries)') %>% 
    DT::formatPercentage('DMIS_CAMS_DIFF_PERC') %>% 
    DT::formatRound(names(dmis_comp)[3:5], digits = 0, interval = 3)

get_dmis_kall_stock <- function(con_maps, species_itis){

 t1 = tbl(con_maps, sql("
  select *
  from apsd.dmis_all_years
  where fishing_year = 2019
  and activity_code like 'NMS%'
   "
 )
)

 t2 =   tbl(con_maps, sql('select * from MAPS.CAMS_STATAREA_STOCK')) %>%
 filter(SPECIES_ITIS == species_itis)

stock_dmis = tbl(con_maps, sql("
    select stock_id
    , comname
    , area
    , max(species_itis) as species_itis
    from fso.v_obSpeciesStockArea 
    where stock_id not like 'OTHER'
    and species_itis is not null
    group by stock_id, comname, species_itis, area
")
) %>%
    filter(SPECIES_ITIS == species_itis)



t3 = tbl(con_maps, sql("select *
                                     from MAPS.CAMS_LANDINGS
                                     where activity_code_1 like 'NMS%'
                                     AND RECORD_LAND >= '01-MAY-19'
                                     AND RECORD_LAND < '01-MAY-20'
                                     "
                                     )) %>% 
    left_join(., t2, by = c('AREA' = 'STAT_AREA')) %>% 
    filter(!is.na(AREA_NAME)) %>% 
    group_by(AREA_NAME) %>% 
    dplyr::summarise(KALL_CAMS = sum(LIVLB, na.rm = T))

    # collect()

 t1 %>% 
    left_join(., t2, by= c('AREA' = 'STAT_AREA')) %>%
    left_join(., stock_dmis, by = c('AREA' = 'AREA')) %>% 
    filter(!is.na(STOCK_ID.y)) %>%   # don't use STOCK_ID.x... species specific stock to attribute catch 
    group_by(STOCK_ID.y, AREA_NAME) %>%
    # head() %>% 
    dplyr::summarise(KALL_DMIS = round(sum(POUNDS, na.rm = T))) %>%
    left_join(., t3, by = 'AREA_NAME') %>% 
    collect() %>% 
    mutate(SPECIES_ITIS = species_itis) %>% 
    dplyr::rename('STOCK_ID_DMIS' = 'STOCK_ID.y', 'STOCK_ID_CAMS' = 'AREA_NAME')

}

# make the table 

species = tbl(con_maps, sql("
select distinct(b.species_itis)
    , COMNAME
    , a.nespp3
from fso.v_obSpeciesStockArea a
left join (select *  from APSD.CAMS_GEARCODE_STRATA) b on a.nespp3 = b.nespp3
where stock_id not like 'OTHER'
and b.species_itis is not null
")
) %>% 
    collect()


species$SPECIES_ITIS %>% 
    as.list() %>%
lapply(., function(i) get_dmis_kall_stock(con_maps, i)) %>% 
    do.call(rbind, .) %>% 
    mutate(KALL_DIFF = KALL_DMIS - KALL_CAMS
                 , KALL_DIFF_PERC = round((KALL_DMIS - KALL_CAMS)/KALL_DMIS, 3)) %>% 
    DT::datatable(.
                                # , extensions = 'Buttons'
                             # , options = list(dom = 'frtipB', buttons = c('csv', 'excel'), autoWidth = TRUE)
                             , filter = "top"
                             , class = 'cell-border stripe'
                             , rownames = FALSE
                            , caption = 'KALL comparison, DMIS/CAMS') %>% 
    DT::formatPercentage('KALL_DIFF_PERC') %>% 
    DT::formatRound(c('KALL_DMIS','KALL_CAMS','KALL_DIFF'), digits = 0, interval = 3)

load('compare_gear_strata_gftrips19.Rdata')

gearcodes %>% 
        DT::datatable(caption = 'Gears used in OBS/CATCH merge (STG_SECGEAR_MAPPED)')
    # DT::formatRound(names(dmis_comp)[3:5], digits = 0, interval = 3)
    # knitr::kable(format.args = list(big.mark = ",")
    #                        , caption = "Gears used in OBS/CATCH merge (STG_SECGEAR_MAPPED)") %>% 
    #  kable_styling(bootstrap_options = c("striped", "hover", "responsive"))


CAMS_GEAR_STRATA %>% 
    DT::datatable(caption = 'Gear mapping for discaRd: Ocean Pout example')
    # knitr::kable(format.args = list(big.mark = ",")
    #                        , caption = "Gear mapping for discaRd: ocean pout example") %>% 
    #  kable_styling(bootstrap_options = c("striped", "hover", "responsive"))



# res_cams_gear %>%
    # replace_na(replace = list(' ')) %>% 
    #   knitr::kable(format.args = list(big.mark = ",")
    #                            , caption = "Groundfish discard estimates using CAMS_GEAR_GROUP") %>% 
    #  kable_styling(bootstrap_options = c("striped", "hover", "responsive"))

# res_cams_gearcode %>% 
#       knitr::kable(format.args = list(big.mark = ",")
#                                , caption = "Groundfish discard estimates using SECGEAR_MAPPED (OBS/Catch categories)") %>% 
#    kable_styling(bootstrap_options = c("striped", "hover", "responsive"))


bind_cols(res_cams_gear %>% dplyr::select(1,2), (res_cams_gear %>% dplyr::select(-1,-2)) - (res_cams_gearcode %>% dplyr::select(-1,-2))) %>% 
        knitr::kable(format.args = list(big.mark = ",")
                                 , caption = "Difference in estimates between using CAMS_GEAR_GROUP and SECGEAR_MAPPED gear groupings") %>% 
     kable_styling(bootstrap_options = c("striped", "hover", "responsive"))

Steps for estimation of groundfish discard on non-Groundfish Trips

same as above
same as above
Set stratification variables

# Full (First Pass) Stratification variables

stratvars = c('SPECIES_STOCK'
              ,'CAMS_GEAR_GROUP'
                            , 'MESHGROUP'
                          , 'TRIPCATEGORY'
                          , 'ACCESSAREA')

- SPECIES_STOCK is taken from CAMS support table `MAPS.CAMS_STATAREA_STOCK`
- CAMS_GEAR_GROUP is derived from a support table (`MAPS.CAMS_GEARCODE_STRATA`)
- MESHGROUP is hardcoded for all trips according to decisions made by the mesh subgroup (see summary when available)
- TRIPCATEGORY is specific to scallop trips and indicates Limited Access or General Category Fleets
- ACCESS AREA is specific to scallop trips and indicates Scallop Access Area

Run first pass of discaRd for full stratification
- there are sub-passes for year t and year t-1
Run a second pass using a coarsened stratification : 'SPECIES_STOCK', 'CAMS_GEAR_GROUP' and 'MESHGROUP'.

# Second Pass Stratification variables

stratvars = c('SPECIES_STOCK'
              , 'CAMS_GEAR_GROUP',
              ,'MESHGROUP')

Run a third pass using Stock and Gear only *Note: This pass uses data from the focal and previous year.

# Third PAss Stratification variables

stratvars = c('SPECIES_STOCK'
              , 'CAMS_GEAR_GROUP',
              ,'MESHGROUP')

Trips/Gear strata that are unobserved after these steps are not assigned rates as no observer coverage exists for these gear types (e.g. Menhaden purse seine)
Rates and DISCARD_SOURCE (in parentheses) are assigned for each trip according to:
(I) in season rate; >= 5 trips in Full Stratification
(T) Transition in season rate; < 5 trips in Full Stratification, year t, AND >= 5 trips in year t-1
(GM) Assignment is from the second pass (e.g. Stock/Gear/Meshgroup). This is the rate when there were <5 trips in season and <5 in previous season at the full stratification level.
(G) Assignment is from the second pass (e.g. Stock/Gear).
(N) No coverage in focal season and previous season; discard rate is not possible to estimate.
(O) Observed values used from observed rate trips; discard rate is NOT USED.
CV calculations are available for (I), (T), (G) and (GM).
Discard pounds per trip are calculated according to

    mutate(DISC_MORT_RATIO = coalesce(DISC_MORT_RATIO, 1)) %>%
    mutate(DISCARD = case_when(!is.na(LINK1) ~ DISC_MORT_RATIO*OBS_DISCARD
                                                         , is.na(LINK1) ~ DISC_MORT_RATIO*COAL_RATE*LIVE_POUNDS)

    # COAL_RATE is the final discard rate used. It is 'coalesced' from the (I), (GM) and (G) rates

non_gf = tbl(con_maps, sql("

    select ITIS_TSN,
    COMMON_NAME, 
    DISCARD_SOURCE,
    SPECIES_STOCK
    , sum(CAMS_DISCARD) as CAMS_DISCARD
    from MAPS.CAMS_DISCARD_ALL_YEARS
     where GF = 0
     AND FED_OR_STATE = 'FED'
    AND FY = 2019
    AND ITIS_TSN in ('172877' ,
    '164712' ,
    '164732' ,
    '166774',
    '171341',
    '172873',
    '172909',
    '164727',
    '172933',
    '172746',
    '172905',
    '164744', 
    '630979'
    )
     group by ITIS_TSN, COMMON_NAME, DISCARD_SOURCE,SPECIES_STOCK


")) %>% 
    collect()

    ggplot(non_gf)+
    geom_bar(aes(x = SPECIES_STOCK, y = CAMS_DISCARD, fill = DISCARD_SOURCE), stat = 'identity', position = 'dodge')+
    facet_wrap(~COMMON_NAME, scales = 'free')+
    theme_light()

    non_gf = tbl(con_maps, sql("

    select COMMON_NAME, SPECIES_STOCK, FED_OR_STATE
    , sum(CAMS_DISCARD) as CAMS_DISCARD
    , sum(subtrip_kall) as KALL
    , count(distinct(VTRSERNO)) as N_VTR
    from MAPS.CAMS_DISCARD_ALL_YEARS
     where GF = 0
     AND FED_OR_STATE = 'FED'
    AND FY = 2019
    AND ITIS_TSN in ('172877' ,
    '164712' ,
    '164732' ,
    '166774',
    '171341',
    '172873',
    '172909',
    '164727',
    '172933',
    '172746',
    '172905',
    '164744', 
    '630979'
    )
     group by COMMON_NAME,SPECIES_STOCK, FED_OR_STATE

")) %>% 
        collect()   


    non_gf %>% 
    DT::datatable(caption = 'Non-groundfish trips: Discard Estimate by Stock, Species, Discard Source (using CAMS Stock ID)') %>% 
    DT::formatRound(c('N_VTR','KALL','CAMS_DISCARD'), digits = 0, interval = 3)

    scal_trips = tbl(con_maps, sql("

    select COMMON_NAME, SPECIES_STOCK, SCALLOP_AREA, FY
    , sum(CAMS_DISCARD) as CAMS_DISCARD
    , sum(subtrip_kall) as KALL
    , count(distinct(CAMS_SUBTRIP)) as N_SUBTRIPS
    from MAPS.CAMS_DISCARD_ALL_YEARS
     where GF = 0
     AND FED_OR_STATE = 'FED'
    AND FY = 2019
    AND FY_TYPE = 'APRIL START'
    AND SCALLOP_AREA is not null
    AND ITIS_TSN in ('172877' ,
    '164712' ,
    '164732' ,
    '166774',
    '171341',
    '172873',
    '172909',
    '164727',
    '172933',
    '172746',
    '172905',
    '164744', 
    '630979'
    )
     group by COMMON_NAME,SPECIES_STOCK, SCALLOP_AREA, FY

")) %>% 
        collect()   


    scal_trips %>% 
    DT::datatable(caption = 'Scallop trips: Discard Estimate by Stock, Species, Scallop Area, Discard Source (using CAMS Stock ID)') %>% 
    DT::formatRound(c('N_SUBTRIPS','KALL','CAMS_DISCARD'), digits = 0, interval = 3)