Download PDR G Report

Code

library(rvest)
library(tidyverse)
library(here)
library(glue)

Killed Euthenized tables

The urls to the killed/euthized tables are the following:

Code

killed_euthanized = list(
  "2023" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2023:KILLED_EUTH:0:k6Slc6m5armtqanTaYXJqp-Rrg==",
  "2022" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2022:KILLED_EUTH:0:k6Slcqm5armtqanTaYXJqp-Rrg==",
  "2021" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2021:KILLED_EUTH:0:k6Slcam5armtqanTaYXJqp-Rrg==",
  "2020" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2020:KILLED_EUTH:0:k6SlcKm5armtqanTaYXJqp-Rrg==",
  "2019" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2019:KILLED_EUTH:0:k6Skeam5armtqanTaYXJqp-Rrg==",
  "2018" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2018:KILLED_EUTH:0:k6SkeKm5armtqanTaYXJqp-Rrg==",
  "2017" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2017:KILLED_EUTH:0:k6Skd6m5armtqanTaYXJqp-Rrg==",
  "2016" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2016:KILLED_EUTH:0:k6Skdqm5armtqanTaYXJqp-Rrg==",
  "2015" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2015:KILLED_EUTH:0:k6Skdam5armtqanTaYXJqp-Rrg==",
  "2014" = "https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2014:KILLED_EUTH:0:k6SkdKm5armtqanTaYXJqp-Rrg=="
)

killed_euthanized

Download all of the tables and combine into one dataframe

Code

download_killed_report = function(item, type){
    
   
    url = item[1][[1]]

    page <- read_html(url)

    table <- page %>% 
        html_nodes("table") %>%  
        .[5] %>% 
        html_table(fill = TRUE)

    if (type == "overall"){
        data_frame <- as.data.frame(table) %>% 
            janitor::row_to_names(row_number = 1) %>% 
            janitor::clean_names() %>% 
            mutate(across(where(is.character), tolower)) %>% 
            filter(!str_detect(species, "species total"))

    } else if ( type == "state"){

        data_frame <- as.data.frame(table) %>% 
            janitor::row_to_names(row_number = 1) %>% 
            janitor::clean_names() %>% 
            mutate(across(where(is.character), tolower)) %>% 
            filter(capture_restraint_dispersal_method   != "totals")

    }

    return(data_frame)

}


all_killed_euthanized = killed_euthanized  %>%   
    purrr::map(\(x){download_killed_report(x, "overall")}, .progress = TRUE) %>% 
    purrr::list_rbind(names_to = "year")

Code

glimpse(all_killed_euthanized)
write_csv(all_killed_euthanized, here("data/processed/20240926--all_killed_euthanized_2014-2023.csv"))

State filtered

This is the format of a url: https://www.aphis.usda.gov/pdr/PDR-G_Report?p=2015:STATE:AK:k6SkdanBda61qZ-1b2o=

The code below creates all the urls for each state for each year

Code

# Found by using inspect elements on webpage
# ==========================================

year_codes = list("2023" = "k6Slc6nBda61qZ",
                  "2022" = "k6SlcqnBda61qZ",
                  "2021" = "k6SlcanBda61qZ",
                  "2020" = "k6SlcKnBda61qZ",
                  "2019" = "k6SkeanBda61qZ",
                  "2018" = "k6SkeKnBda61qZ",
                  "2017" = "k6Skd6nBda61qZ",
                  "2016" = "k6SkdqnBda61qZ",
                  "2015" = "k6SkdanBda61qZ",
                  "2014" = "k6SkdKnBda61qZ")

state_codes = list(
'AK' = '-1b2o=',
'AL' = '-1cGo=',
'AR' = '-1dmo=',
'AZ' = '-1fmo=',
'CA' = '-3ZWo=',
'CO' = '-3c2o=',
'CT' = '-3eGo=',
'DC' = '-4Z2o=',
'DD' = '-4aGo=',
'DE' = '-4aWo=',
'FL' = '-6cGo=',
'GA' = '-7ZWo=',
'GU' = '-7eWo=',
'HI' = '-8bWo=',
'IA' = '-9ZWo=',
'ID' = '-9aGo=',
'IL' = '-9cGo=',
'IN' = '-9cmo=',
'KS' = '-_d2o=',
'KY' = '-_fWo=',
'LA' = '_AZWo=',
'MA' = '_BZWo=',
'MD' = '_BaGo=',
'ME' = '_BaWo=',
'MI' = '_BbWo=',
'MN' = '_Bcmo=',
'MO' = '_Bc2o=',
'MS' = '_Bd2o=',
'MT' = '_BeGo=',
'NC' = '_CZ2o=',
'ND' = '_CaGo=',
'NE' = '_CaWo=',
'NH' = '_CbGo=',
'NJ' = '_Cbmo=',
'NM' = '_CcWo=',
'NV' = '_Cemo=',
'NY' = '_CfWo=',
'OH' = '_DbGo=',
'OK' = '_Db2o=',
'OR' = '_Ddmo=',
'PA' = '_EZWo=',
'PR' = '_Edmo=',
'RI' = '_GbWo=',
'SC' = '_HZ2o=',
'SD' = '_HaGo=',
'TN' = '_Icmo=',
'TX' = '_IfGo=',
'UT' = '_JeGo=',
'VA' = '_KZWo=',
#'None' = '_Jd4a-nA==',
'VT' = '_KeGo=',
'WA' = '_LZWo=',
'WI' = '_LbWo=',
'WV' = '_Lemo=',
'WY' = '_LfWo=')

# Create urls
# ============

year_state_combos <- expand.grid(year = names(year_codes), state = names(state_codes))

create_url = function(year, state){
    year_code = year_codes[[year]]
    state_code = state_codes[[state]]
    url = glue("https://www.aphis.usda.gov/pdr/PDR-G_Report?p={year}:STATE:{state}:{year_code}{state_code}")
    return(url)

}




state_urls <- setNames(
  map2(year_state_combos$year, year_state_combos$state, ~ create_url(.x, .y)),
  year_state_combos$year
)

Code

# Download state tables

all_states_df = state_urls %>%   
    purrr::map(\(x){download_killed_report(x, "state")}, .progress = TRUE) %>% 
    purrr::list_rbind(names_to = "year")

Code

glimpse(all_states_df)
write_csv(all_states_df, here("data/processed/20240926--all_states_2014-2023.csv"))

PDR C

https://www.aphis.usda.gov/pdr/PDR-C_Report?p=2023:STATE:TX:k6Slc6nBda61qZ_IfGo=

https://www.aphis.usda.gov/pdr/PDR-C_Report?p=2023:INDEX: