This vignette demonstrates the construction of lookup tables that are
available as framr::chin_stock_lu and
framr::chin_fishery_lu.
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")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)