In FRAMverse/framr: R for working with the Fishery Regulation Assessment Model (FRAM)

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

This vignette demonstrates the construction of lookup tables that are available as framr::chin_stock_lu and framr::chin_fishery_lu.

library(tidyverse)
library(framr)

lu_chin_stock

The following is modified from *chin_valid_2020.Rmd".

The "BackwardsFRAM" table includes 116 BKFRAM stocks which are indexed differently than for forward FRAM due to the inclusion of "totals" over marked and unmarked components. However, the project database tables do not currently include a lookup that cross-references the BKFRAM ids to those in the (forward) "Stock" table, so it was necessary to read and clean information in the "FRAMEscapeV2" sheet of an older FRAM stock compilation Excel workbook.

#grab current forward stock IDs and names
db_con <- DBI::dbConnect(
  drv = odbc::odbc(),
  .connection_string = paste0(
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",
    "O:/valid_2020/r711/Valid2020_Round_7.1.1.mdb", #"O:/code/chin_fram_validation_r62_1992_2016/Valid2018_NewBP_Round6_10.29.2018_Complete_cmpct.mdb",
    ";"))

db_stock <- tbl(db_con, "Stock") |> 
  filter(Species == "Chinook") |> 
  arrange(StockID) |> 
  collect()

#not used: db_bkfram <- tbl(db_con, "BackwardsFRAM") |> collect() |> rename(bkfram_id = StockID)

DBI::dbDisconnect(db_con)


#Note WhiteSp are 65/66, but Valid2018 sheet FRAMEscapeV2 incorrectly has ORMidCoastFalls Stock# colK assigned this rather than 77/78
framescapev2 <- bind_cols(
  readxl::read_excel("O:/valid_2020/Valid2018/Valid2018_FRAM_StockData_10.30.2018.xlsm",
                     range = "FRAMEscapeV2!A2:B117", col_names = c("stock_name", "stock_abbr")),
  readxl::read_excel("O:/valid_2020/Valid2018/Valid2018_FRAM_StockData_10.30.2018.xlsm",
                     range = "FRAMEscapeV2!K2:L117", col_names = c("stock_id", "bkfram_id"))
  ) |> 
  mutate(
    #fix the ORMidCoast misassignment
    stock_id = if_else(bkfram_id == 115, 77, stock_id),
    stock_id = if_else(bkfram_id == 116, 78, stock_id),
    #also differs from the "Stock" abbreviation
    stock_abbr = if_else(bkfram_id == 115, "U-MidORCst", stock_abbr),
    stock_abbr = if_else(bkfram_id == 116, "M-MidORCst", stock_abbr),
    #and the WhiteSp have the wrong strings
    stock_name = if_else(bkfram_id == 55, "UnMarked White Sp Year", stock_name),
    stock_name = if_else(bkfram_id == 56, "Marked White Sp Year", stock_name),
    #mapping bkframids to per-stock totals 
    bkfram_id_tot = c(rep(1,3), rep(4, 5), rep(seq(9, 115, by = 3), each = 3)),
    #cleanup
    stock_name = stock_name %>% str_remove("-----") %>% str_trim(),
    stock_abbr = stock_abbr %>% str_remove("--") %>% str_trim(),
    bk_run_def = case_when(
      bkfram_id %in% 1:8 ~ "TRS; includes 7B-D", #Nook/Sam
      bkfram_id %in% 9:17 ~ "TRS; includes Area 8 Net", #Skag
      bkfram_id %in% 18:23 ~ "ETRS; includes FW sport, no FW net", #Sno
      bkfram_id %in% 24:26 ~ "ETRS", #Stilly FF
      bkfram_id %in% 27:29 ~ "TRS; includes 8D catch (excludes 8A)", #Tulalip FF
      bkfram_id %in% 30:32 ~ "TRS; includes 10A, 10E, 11A", #Mid PS FF
      bkfram_id %in% 33:35 ~ "ETRS", #UW Accel
      bkfram_id %in% 36:38 ~ "TRS; includes 13A, 13C, and 13D-K", #SPS FF
      bkfram_id %in% 39:41 ~ "TRS", #SPS FY
      bkfram_id %in% 42:44 ~ "ETRS; includes FW net (FW spt assumed 0)", #White Spr F
      bkfram_id %in% 45:50 ~ "TRS; incl FW net, FW sport, 12H, HC net", #HC FF/FY
      bkfram_id %in% 51:53 ~ "ETRS; includes 6D", #JDF F
      bkfram_id %in% 54:56 ~ "ETRS; includes FW net (FW spt assumed 0)", #White Spr Y
      bkfram_id %in% 57:59 ~ "ETRS; esc only, no FW fishery" #Hoko
      ),
    #matching the levels in tamm_regions
    region = case_when(
      bkfram_id %in% 1:8 ~ "NookSam",
      bkfram_id %in% 9:17 ~ "Skagit",
      bkfram_id %in% 18:29 ~ "StSno",
      bkfram_id %in% c(30:35, 42:44, 54:56) ~ "MPS",
      bkfram_id %in% 36:41 ~ "SPS",
      bkfram_id %in% 45:50 ~ "HC",
      bkfram_id %in% c(51:53, 57:59) ~ "JDF",
      bkfram_id %in% c(60:86, 102:104) ~ "ColR",
      bkfram_id %in% c(87:89, 105:116) ~ "WA_NCoast_OR_CA",
      bkfram_id %in% c(90:101) ~ "Canada"
      )
  )

lu_chin_stock <- full_join(
  db_stock,
  framescapev2 |> select(StockID = stock_id, bkfram_id:region),
  by = "StockID"
  ) |> 
  arrange(bkfram_id)

#modified existing/prior description in "R/data-chin_stock_lu.R"
save(lu_chin_stock, file = "data/lu_chin_stock.rda")

lu_chin_fishery

The following reads a Fishery table from a current Chinook project database and appends several fields of metadata and catch database identifiers. Note that additional TAMM fisheries are required for a complete depiction of impacts (i.e., including terminal and freshwater mortality).

db_con <- DBI::dbConnect(
  drv = odbc::odbc(),
  .connection_string = paste0(
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",
    "O:/valid_2020/r711/Valid2020_Round_7.1.1.mdb",
    ";"))

db_fishery <- tbl(db_con, "Fishery") |> 
  filter(Species == "CHINOOK") |>  
  arrange(FisheryID) |> 
  collect()

DBI::dbDisconnect(db_con)

lu_chin_fishery <- db_fishery |> 
    mutate(
      catch_source = case_when(
        FisheryID < 16 ~ "PSC/CTC",
        FisheryID %in% c(16:18, 20:22, 26:27, 30:35) ~ "PFMC/STT",
        FisheryID %in% c(36,42,45,48,53,54,56,57,60,62,64,67) ~ "CRC/PSSP",
        FisheryID %in% c(23,24,25, 37:41, 43:44, 46,47, 49:52, 55, 58,59, 61, 63, 65,66, 68:71) ~ "FishTicket/TOCAS"
      ),
      region = case_when(
        FisheryID %in% c(37:40)  ~ "NookSam",
        FisheryID %in% c(46, 47) ~ "Skagit",
        FisheryID %in% c(49:52) ~ "StSno",
        FisheryID %in% c(58, 59, 61, 63) ~ "MPS",
        FisheryID %in% c(68, 69, 70, 71) ~ "SPS",
        FisheryID %in% c(65, 66) ~ "HC",
        FisheryID %in% c(17, 41, 43, 44, 55) ~ "JDF"
        # ~ "ColR",
        # ~ "WA_NCoast_OR_CA",
        # ~ "Canada"
      ),
      #CRC identifiers for marine recreational
      areacode = case_when(
        #FisheryID == 18 ~ "3,4", #taken from PFMC table
        #FisheryID == 22 ~ "2", #taken from PFMC table
        #FisheryID == 27 ~ "1", #taken from PFMC table
        FisheryID == 36 ~ "7",
        FisheryID == 42 ~ "5",
        FisheryID == 45 ~ "81,82", #winter
        FisheryID == 48 ~ "81,82", #summer, fishery 8D: area 8 is always closed in summer, so any summer catch is from 8D
        FisheryID == 53 ~ "9",
        FisheryID == 54 ~ "6",
        FisheryID == 56 ~ "10",
        FisheryID == 57 ~ "11",
        FisheryID == 60 ~ NA_character_, #10A
        FisheryID == 62 ~ NA_character_, #10E
        FisheryID == 64 ~ "12",
        FisheryID == 67 ~ "13"
      ),
      #WDFW FishTix identifiers for marine commercial
      #Note that treaty catches have recently come directly from TOCAS queries by O Miler
      #and that STT fisheries have come from PFMC STT rather than direct query of FT
      CatchAreaCode = case_when(
        FisheryID == 16 ~ "03,04,04B", #as backup, precedence to STT tables; Area 3:4:4B Troll
        FisheryID == 17 ~ "03,04,04B", #as backup, precedence to STT tables; Area 3:4:4B Troll
        #FisheryID == 19 ~ "04A", #No Wash. Coastal Net,
        FisheryID == 20 ~ "02", #as backup, precedence to STT tables; Area 2 Troll
        FisheryID == 21 ~ "02", #as backup, precedence to STT tables; Area 2 Troll
        FisheryID == 23 ~ "02A,02B,02C,02D", #G. Harbor Net
        FisheryID == 24 ~ "02A,02B,02C,02D", #G. Harbor Net
        FisheryID == 25 ~ "02G,02H,02J,02K,02M,02N,02P,02R,02T,02U", #Willapa Bay Net
        FisheryID == 26 ~ "01", #as backup, precedence to STT tables; Area 1 Troll
        FisheryID == 37 ~ "06A,07,07A", #6A:7:7A Net
        FisheryID == 38 ~ "06A,07,07A", #6A:7:7A Net
        FisheryID == 39 ~ "07B,07C,07D", #Area 7B-7D Net
        FisheryID == 40 ~ "07B,07C,07D", #Area 7B-7D Net
        FisheryID == 41 ~ "05,06C", #Tr JDF Troll
        FisheryID == 43 ~ "04B,05,06,06C", #NT JDF Net
        FisheryID == 44 ~ "04B,05,06,06C", #TR JDF Net
        FisheryID == 46 ~ "08", #Skagit Net
        FisheryID == 47 ~ "08", #Skagit Net
        FisheryID == 49 ~ "08A", #St/Snohomish Net
        FisheryID == 50 ~ "08A", #St/Snohomish Net
        FisheryID == 51 ~ "08D", #Tulalip Bay Net
        FisheryID == 52 ~ "08D", #Tulalip Bay Net
        FisheryID == 55 ~ "06B,09", #Area 6B:9 Net
        FisheryID == 58 ~ "10,11", #Area 10:11 Net
        FisheryID == 59 ~ "10,11", #Area 10:11 Net
        FisheryID == 61 ~ "10A", #Tr Area 10A Net
        FisheryID == 63 ~ "10E", #Tr Area 10E Net
        FisheryID == 65 ~ "12,12B,12C,12D", #NT Hood Canal Net
        FisheryID == 66 ~ "12,12B,12C,12D", #Tr Hood Canal Net
        FisheryID == 68 ~ "13,13B,13D,13E,13F,13G,13H,13I,13J,13K", #SPS Net
        FisheryID == 69 ~ "13,13B,13D,13E,13F,13G,13H,13I,13J,13K", #SPS Net
        FisheryID == 70 ~ "13A", #Area 13A Net
        FisheryID == 71 ~ "13A" #Area 13A Net
      ),
      FisherTypeDescription = if_else(grepl("^Tr", FisheryName), "Treaty", "Non-Treaty"),
      gear = if_else(grepl("Net", FisheryTitle), "net", "line")
    )



use_data(lu_chin_fishery)

FRAMverse/framr documentation built on Jan. 29, 2023, 1:55 a.m.