obtn: Oregon by the Numbers

# Packages ----------------------------------------------------------------

library(devtools)
library(tidyverse)
library(here)
library(readxl)
library(janitor)
library(sf)
library(scales)
library(tigris)
library(tidycensus)
library(tidygeocoder)
library(lubridate)
library(albersusa) # remotes::install_github("hrbrmstr/albersusa")
library(assertr)

load_all()


# SETTINGS ----------------------------------------------------------------

# Turn off scientific notation

options(scipen = 999)

# Define years so I can import all data

obtn_year <- year(today()) # Change this if you want to manually set year

obtn_years <- c(2019:obtn_year)

# Get everything except first year (2019) because Alice and a few other measures didn't exist then
obtn_years_minus_2019 <- obtn_years[2:length(obtn_years)]


# SOURCE INFO -------------------------------------------------------------

import_source_info <- function(data_year) {
  read_excel(str_glue("data-raw/{data_year}-obtn-source-info.xlsx")) |>
    clean_names() |>
    select(measure, source_details_for_measure_page) |>
    rename(source = source_details_for_measure_page) |>
    mutate(year = data_year) |>
    relocate(year, .before = 1)
}

obtn_source_info <-
  map(2020:obtn_year, import_source_info) |>
  bind_rows() |>
  # Make measure names consistent with rest of data
  mutate(measure = str_to_title(measure)) |>
  mutate(measure = case_match(
    measure,
    # Goes: text in source info sheet ~ measure as written in obtn_data_choropleth_measures
    "3rd Grade Reading" ~ "3rd Grade ELA",
    "4-Year Degree Or Greater" ~ "4yr Degree or Greater",
    "9th Grade On Track" ~ "9th Grade on Track",
    "Childcare Slots" ~ "Childcare Availability",
    "Electric Vehicle Charging" ~ "EV",
    "Alice" ~ "Financial Hardship",
    "Foster Care Rate" ~ "Foster Care",
    "Good Or Better Health" ~ "Good Health",
    "High School Graduation Rate" ~ "Graduation Rate",
    "Labor Force Participation Rate" ~ "LFPR",
    "Life Expectancy M/F" ~ "Life Expectancy - Overall",
    "Net Migration" ~ "Migration",
    "Property Tax" ~ "Property Tax per Person",
    "Publicly Owned Lands" ~ "Public Lands",
    "Vmt (Per Capita)" ~ "VMT per capita",
    "Vaccination Rate" ~ "Vaccination Rate 2yr olds",
    "Developed Land" ~ "Developed or Cultivated Land",
    .default = measure
  )) |>
  full_join(
    obtn_data_choropleth_measures,
    join_by(measure, year)
  ) |>
  arrange(measure)

use_data(
  obtn_source_info,
  overwrite = TRUE
)

# MISC DATA ---------------------------------------------------------------

obtn_oregon_counties <- counties(state = "OR") %>%
  arrange(NAME) %>%
  pull(NAME)

use_data(
  obtn_oregon_counties,
  overwrite = TRUE
)


# DATA HIGHLIGHT ----------------------------------------------------------

pace_word_positivity_rural <-
  read_excel(
    "data-raw/Data Highlight graphs 1 and 2.xlsx",
    sheet = "Graph 2"
  ) |>
  rename(
    "word" = `...1`,
    "value" = "Rural"
  ) |>
  mutate(value = value / 100) |>
  mutate(word = fct_reorder(word, value))

use_data(
  pace_word_positivity_rural,
  overwrite = TRUE
)

pace_ideology_by_urbanicity <-
  read_excel(
    "data-raw/Data Highlight graphs 1 and 2.xlsx",
    sheet = "Graph 1"
  ) |>
  rename("geography" = `...1`) |>
  pivot_longer(
    cols = -geography,
    names_to = "ideology"
  ) |>
  mutate(value = value / 100) |>
  mutate(ideology = fct_inorder(ideology)) |>
  mutate(ideology = fct_rev(ideology))

use_data(
  pace_ideology_by_urbanicity,
  overwrite = TRUE
)


# Data pasted with datapasta from https://docs.google.com/spreadsheets/d/1fGZhKn3pQQQ81xzT5beKeKp6v_pYqqrrYf1wBt-NZWk/edit#gid=728565203

pace_word_positivity <-
  tibble::tribble(
    ~word,
    ~response,
    ~demos_urban,
    ~survey_2023,
    ~survey_2021,
    ~score_change,
    "Belonging",
    "Neither negative nor positive",
    "Unsure",
    76.1,
    43.9,
    32.2,
    "Belonging",
    "Positive",
    "Unsure",
    23.9,
    39,
    -15.1,
    "Belonging",
    "Negative",
    "Rural",
    4.1,
    4.8,
    -0.7,
    "Belonging",
    "Neither negative nor positive",
    "Rural",
    22.3,
    32.8,
    -10.5,
    "Belonging",
    "Not familiar with this word",
    "Rural",
    3,
    4.3,
    -1.3,
    "Belonging",
    "Positive",
    "Rural",
    70.6,
    58.1,
    12.5,
    "Belonging",
    "Negative",
    "Suburban",
    4.3,
    4.9,
    -0.6,
    "Belonging",
    "Neither negative nor positive",
    "Suburban",
    21,
    34.1,
    -13.1,
    "Belonging",
    "Not familiar with this word",
    "Suburban",
    1.8,
    1.3,
    0.5,
    "Belonging",
    "Positive",
    "Suburban",
    73,
    59.7,
    13.3,
    "Belonging",
    "Negative",
    "Urban",
    2.4,
    6.6,
    -4.2,
    "Belonging",
    "Neither negative nor positive",
    "Urban",
    26.2,
    38.9,
    -12.7,
    "Belonging",
    "Not familiar with this word",
    "Urban",
    1,
    4.5,
    -3.5,
    "Belonging",
    "Positive",
    "Urban",
    70.4,
    50.1,
    20.3,
    "Citizen",
    "Neither negative nor positive",
    "Unsure",
    56.5,
    21.8,
    34.7,
    "Citizen",
    "Positive",
    "Unsure",
    43.5,
    42,
    1.5,
    "Citizen",
    "Negative",
    "Rural",
    2.1,
    5.1,
    -3,
    "Citizen",
    "Neither negative nor positive",
    "Rural",
    14.3,
    29.5,
    -15.2,
    "Citizen",
    "Not familiar with this word",
    "Rural",
    0.3,
    1.2,
    -0.9,
    "Citizen",
    "Positive",
    "Rural",
    83.4,
    64.2,
    19.2,
    "Citizen",
    "Negative",
    "Suburban",
    2.7,
    1.9,
    0.8,
    "Citizen",
    "Neither negative nor positive",
    "Suburban",
    16.7,
    28.4,
    -11.7,
    "Citizen",
    "Not familiar with this word",
    "Suburban",
    0.7,
    3,
    -2.3,
    "Citizen",
    "Positive",
    "Suburban",
    79.9,
    66.6,
    13.3,
    "Citizen",
    "Negative",
    "Urban",
    3.6,
    3.5,
    0.1,
    "Citizen",
    "Neither negative nor positive",
    "Urban",
    17.8,
    34.1,
    -16.3,
    "Citizen",
    "Not familiar with this word",
    "Urban",
    1.8,
    3.3,
    -1.5,
    "Citizen",
    "Positive",
    "Urban",
    76.8,
    59.1,
    17.7,
    "Civic Engagement",
    "Neither negative nor positive",
    "Unsure",
    63.2,
    23.6,
    39.6,
    "Civic Engagement",
    "Positive",
    "Unsure",
    36.8,
    14.3,
    22.5,
    "Civic Engagement",
    "Negative",
    "Rural",
    5.4,
    6.5,
    -1.1,
    "Civic Engagement",
    "Neither negative nor positive",
    "Rural",
    42.7,
    48.4,
    -5.7,
    "Civic Engagement",
    "Not familiar with this word",
    "Rural",
    6.6,
    13.8,
    -7.2,
    "Civic Engagement",
    "Positive",
    "Rural",
    45.3,
    31.3,
    14,
    "Civic Engagement",
    "Negative",
    "Suburban",
    5.3,
    5.3,
    0,
    "Civic Engagement",
    "Neither negative nor positive",
    "Suburban",
    34,
    41,
    -7,
    "Civic Engagement",
    "Not familiar with this word",
    "Suburban",
    4.5,
    10.2,
    -5.7,
    "Civic Engagement",
    "Positive",
    "Suburban",
    56.2,
    43.6,
    12.6,
    "Civic Engagement",
    "Negative",
    "Urban",
    5.1,
    5.6,
    -0.5,
    "Civic Engagement",
    "Neither negative nor positive",
    "Urban",
    33.5,
    42.3,
    -8.8,
    "Civic Engagement",
    "Not familiar with this word",
    "Urban",
    3.6,
    8.2,
    -4.6,
    "Civic Engagement",
    "Positive",
    "Urban",
    57.7,
    43.9,
    13.8,
    "Civility",
    "Neither negative nor positive",
    "Unsure",
    58,
    24.4,
    33.6,
    "Civility",
    "Positive",
    "Unsure",
    42,
    38.3,
    3.7,
    "Civility",
    "Negative",
    "Rural",
    6.9,
    5.9,
    1,
    "Civility",
    "Neither negative nor positive",
    "Rural",
    22.5,
    34.5,
    -12,
    "Civility",
    "Not familiar with this word",
    "Rural",
    3.4,
    7.7,
    -4.3,
    "Civility",
    "Positive",
    "Rural",
    67.2,
    51.9,
    15.3,
    "Civility",
    "Negative",
    "Suburban",
    8.3,
    6.2,
    2.1,
    "Civility",
    "Neither negative nor positive",
    "Suburban",
    20,
    30.3,
    -10.3,
    "Civility",
    "Not familiar with this word",
    "Suburban",
    2.5,
    8.4,
    -5.9,
    "Civility",
    "Positive",
    "Suburban",
    69.2,
    55.2,
    14,
    "Civility",
    "Negative",
    "Urban",
    9,
    5,
    4,
    "Civility",
    "Neither negative nor positive",
    "Urban",
    23.9,
    31.3,
    -7.4,
    "Civility",
    "Not familiar with this word",
    "Urban",
    3.8,
    9.6,
    -5.8,
    "Civility",
    "Positive",
    "Urban",
    63.3,
    54,
    9.3,
    "Democracy",
    "Negative",
    "Unsure",
    29.3,
    13.8,
    15.5,
    "Democracy",
    "Neither negative nor positive",
    "Unsure",
    40.6,
    42.3,
    -1.7,
    "Democracy",
    "Positive",
    "Unsure",
    30,
    26.1,
    3.9,
    "Democracy",
    "Negative",
    "Rural",
    9.7,
    9.9,
    -0.2,
    "Democracy",
    "Neither negative nor positive",
    "Rural",
    19.4,
    29.2,
    -9.8,
    "Democracy",
    "Not familiar with this word",
    "Rural",
    0.5,
    6.7,
    -6.2,
    "Democracy",
    "Positive",
    "Rural",
    70.4,
    54.2,
    16.2,
    "Democracy",
    "Negative",
    "Suburban",
    8.9,
    6.3,
    2.6,
    "Democracy",
    "Neither negative nor positive",
    "Suburban",
    18.8,
    25.6,
    -6.8,
    "Democracy",
    "Not familiar with this word",
    "Suburban",
    0.5,
    1.9,
    -1.4,
    "Democracy",
    "Positive",
    "Suburban",
    71.8,
    66.2,
    5.6,
    "Democracy",
    "Negative",
    "Urban",
    9.6,
    7.5,
    2.1,
    "Democracy",
    "Neither negative nor positive",
    "Urban",
    20.9,
    26.7,
    -5.8,
    "Democracy",
    "Not familiar with this word",
    "Urban",
    0.7,
    4.4,
    -3.7,
    "Democracy",
    "Positive",
    "Urban",
    68.7,
    61.4,
    7.3,
    "Diversity",
    "Neither negative nor positive",
    "Unsure",
    36.6,
    21.7,
    14.9,
    "Diversity",
    "Positive",
    "Unsure",
    63.4,
    37.8,
    25.6,
    "Diversity",
    "Negative",
    "Rural",
    15.4,
    11.5,
    3.9,
    "Diversity",
    "Neither negative nor positive",
    "Rural",
    27.6,
    34.1,
    -6.5,
    "Diversity",
    "Not familiar with this word",
    "Rural",
    1,
    1.4,
    -0.4,
    "Diversity",
    "Positive",
    "Rural",
    56,
    53,
    3,
    "Diversity",
    "Negative",
    "Suburban",
    16.9,
    10.5,
    6.4,
    "Diversity",
    "Neither negative nor positive",
    "Suburban",
    20.2,
    28.4,
    -8.2,
    "Diversity",
    "Not familiar with this word",
    "Suburban",
    0.9,
    2.5,
    -1.6,
    "Diversity",
    "Positive",
    "Suburban",
    61.9,
    58.6,
    3.3,
    "Diversity",
    "Negative",
    "Urban",
    13.8,
    9.6,
    4.2,
    "Diversity",
    "Neither negative nor positive",
    "Urban",
    18.4,
    20.9,
    -2.5,
    "Diversity",
    "Not familiar with this word",
    "Urban",
    1.7,
    1.9,
    -0.2,
    "Diversity",
    "Positive",
    "Urban",
    66.2,
    67.6,
    -1.4,
    "Liberty",
    "Negative",
    "Unsure",
    23.5,
    1.1,
    22.4,
    "Liberty",
    "Neither negative nor positive",
    "Unsure",
    44.4,
    30.6,
    13.8,
    "Liberty",
    "Positive",
    "Unsure",
    32.1,
    39,
    -6.9,
    "Liberty",
    "Negative",
    "Rural",
    3.2,
    9.1,
    -5.9,
    "Liberty",
    "Neither negative nor positive",
    "Rural",
    9.3,
    21.6,
    -12.3,
    "Liberty",
    "Not familiar with this word",
    "Rural",
    0.6,
    1.6,
    -1,
    "Liberty",
    "Positive",
    "Rural",
    86.9,
    67.8,
    19.1,
    "Liberty",
    "Negative",
    "Suburban",
    4.9,
    4.4,
    0.5,
    "Liberty",
    "Neither negative nor positive",
    "Suburban",
    11.3,
    21.6,
    -10.3,
    "Liberty",
    "Not familiar with this word",
    "Suburban",
    1.6,
    4.4,
    -2.8,
    "Liberty",
    "Positive",
    "Suburban",
    82.2,
    69.5,
    12.7,
    "Liberty",
    "Negative",
    "Urban",
    3.9,
    7.5,
    -3.6,
    "Liberty",
    "Neither negative nor positive",
    "Urban",
    16.1,
    31.9,
    -15.8,
    "Liberty",
    "Not familiar with this word",
    "Urban",
    2.4,
    6.7,
    -4.3,
    "Liberty",
    "Positive",
    "Urban",
    77.6,
    54,
    23.6,
    "Patriotism",
    "Neither negative nor positive",
    "Unsure",
    24.9,
    25.5,
    -0.6,
    "Patriotism",
    "Not familiar with this word",
    "Unsure",
    27.7,
    36.2,
    -8.5,
    "Patriotism",
    "Positive",
    "Unsure",
    47.4,
    37,
    10.4,
    "Patriotism",
    "Negative",
    "Rural",
    7.9,
    11.5,
    -3.6,
    "Patriotism",
    "Neither negative nor positive",
    "Rural",
    17.2,
    27.1,
    -9.9,
    "Patriotism",
    "Not familiar with this word",
    "Rural",
    2.3,
    7.6,
    -5.3,
    "Patriotism",
    "Positive",
    "Rural",
    72.5,
    53.8,
    18.7,
    "Patriotism",
    "Negative",
    "Suburban",
    12.2,
    8.8,
    3.4,
    "Patriotism",
    "Neither negative nor positive",
    "Suburban",
    17.9,
    25.8,
    -7.9,
    "Patriotism",
    "Not familiar with this word",
    "Suburban",
    1.5,
    4.9,
    -3.4,
    "Patriotism",
    "Positive",
    "Suburban",
    68.4,
    60.6,
    7.8,
    "Patriotism",
    "Negative",
    "Urban",
    15.3,
    13.8,
    1.5,
    "Patriotism",
    "Neither negative nor positive",
    "Urban",
    23.1,
    30.9,
    -7.8,
    "Patriotism",
    "Not familiar with this word",
    "Urban",
    2.9,
    8.5,
    -5.6,
    "Patriotism",
    "Positive",
    "Urban",
    58.7,
    46.8,
    11.9,
    "Racial Equity",
    "Negative",
    "Unsure",
    21.4,
    14.3,
    7.1,
    "Racial Equity",
    "Neither negative nor positive",
    "Unsure",
    35,
    14.8,
    20.2,
    "Racial Equity",
    "Positive",
    "Unsure",
    43.6,
    46.3,
    -2.7,
    "Racial Equity",
    "Negative",
    "Rural",
    20.3,
    20,
    0.3,
    "Racial Equity",
    "Neither negative nor positive",
    "Rural",
    28.2,
    34.6,
    -6.4,
    "Racial Equity",
    "Not familiar with this word",
    "Rural",
    1.2,
    5.9,
    -4.7,
    "Racial Equity",
    "Positive",
    "Rural",
    50.2,
    39.4,
    10.8,
    "Racial Equity",
    "Negative",
    "Suburban",
    21,
    19.7,
    1.3,
    "Racial Equity",
    "Neither negative nor positive",
    "Suburban",
    23.4,
    29.2,
    -5.8,
    "Racial Equity",
    "Not familiar with this word",
    "Suburban",
    1.4,
    5,
    -3.6,
    "Racial Equity",
    "Positive",
    "Suburban",
    54.2,
    46.1,
    8.1,
    "Racial Equity",
    "Negative",
    "Urban",
    18.7,
    13.1,
    5.6,
    "Racial Equity",
    "Neither negative nor positive",
    "Urban",
    17.8,
    31.7,
    -13.9,
    "Racial Equity",
    "Not familiar with this word",
    "Urban",
    1.9,
    4.6,
    -2.7,
    "Racial Equity",
    "Positive",
    "Urban",
    61.6,
    50.5,
    11.1,
    "Social Justice",
    "Negative",
    "Unsure",
    13.4,
    23.2,
    -9.8,
    "Social Justice",
    "Neither negative nor positive",
    "Unsure",
    23.2,
    48.8,
    -25.6,
    "Social Justice",
    "Positive",
    "Unsure",
    63.4,
    22.9,
    40.5,
    "Social Justice",
    "Negative",
    "Rural",
    24.9,
    23.2,
    1.7,
    "Social Justice",
    "Neither negative nor positive",
    "Rural",
    27.4,
    37.5,
    -10.1,
    "Social Justice",
    "Not familiar with this word",
    "Rural",
    1,
    2.1,
    -1.1,
    "Social Justice",
    "Positive",
    "Rural",
    46.6,
    37.2,
    9.4,
    "Social Justice",
    "Negative",
    "Suburban",
    23.4,
    22.7,
    0.7,
    "Social Justice",
    "Neither negative nor positive",
    "Suburban",
    20.9,
    32.9,
    -12,
    "Social Justice",
    "Not familiar with this word",
    "Suburban",
    1,
    2.8,
    -1.8,
    "Social Justice",
    "Positive",
    "Suburban",
    54.7,
    41.6,
    13.1,
    "Social Justice",
    "Negative",
    "Urban",
    15.7,
    15.4,
    0.3,
    "Social Justice",
    "Neither negative nor positive",
    "Urban",
    20.4,
    33.9,
    -13.5,
    "Social Justice",
    "Not familiar with this word",
    "Urban",
    1.5,
    3,
    -1.5,
    "Social Justice",
    "Positive",
    "Urban",
    62.4,
    47.7,
    14.7,
    "Unity",
    "Negative",
    "Unsure",
    21.4,
    17.3,
    4.1,
    "Unity",
    "Neither negative nor positive",
    "Unsure",
    34.8,
    19.9,
    14.9,
    "Unity",
    "Positive",
    "Unsure",
    43.9,
    37.5,
    6.4,
    "Unity",
    "Negative",
    "Rural",
    7.2,
    4.6,
    2.6,
    "Unity",
    "Neither negative nor positive",
    "Rural",
    19.3,
    20.8,
    -1.5,
    "Unity",
    "Not familiar with this word",
    "Rural",
    1.3,
    1.9,
    -0.6,
    "Unity",
    "Positive",
    "Rural",
    72.2,
    72.6,
    -0.4,
    "Unity",
    "Negative",
    "Suburban",
    8.4,
    4.9,
    3.5,
    "Unity",
    "Neither negative nor positive",
    "Suburban",
    16,
    18.3,
    -2.3,
    "Unity",
    "Not familiar with this word",
    "Suburban",
    0.5,
    5.4,
    -4.9,
    "Unity",
    "Positive",
    "Suburban",
    75.2,
    71.5,
    3.7,
    "Unity",
    "Negative",
    "Urban",
    7.4,
    6.1,
    1.3,
    "Unity",
    "Neither negative nor positive",
    "Urban",
    18.5,
    18.6,
    -0.1,
    "Unity",
    "Not familiar with this word",
    "Urban",
    1.2,
    6.1,
    -4.9,
    "Unity",
    "Positive",
    "Urban",
    72.9,
    69.2,
    3.7,
    "Belonging",
    "Negative",
    "Unsure",
    0,
    2,
    -2,
    "Belonging",
    "Not familiar with this word",
    "Unsure",
    0,
    15.1,
    -15.1,
    "Citizen",
    "Negative",
    "Unsure",
    0,
    13,
    -13,
    "Citizen",
    "Not familiar with this word",
    "Unsure",
    0,
    23.2,
    -23.2,
    "Civic Engagement",
    "Negative",
    "Unsure",
    0,
    11.8,
    -11.8,
    "Civic Engagement",
    "Not familiar with this word",
    "Unsure",
    0,
    50.3,
    -50.3,
    "Civility",
    "Negative",
    "Unsure",
    0,
    0.9,
    -0.9,
    "Civility",
    "Not familiar with this word",
    "Unsure",
    0,
    36.4,
    -36.4,
    "Democracy",
    "Not familiar with this word",
    "Unsure",
    0,
    17.9,
    -17.9,
    "Diversity",
    "Negative",
    "Unsure",
    0,
    8.5,
    -8.5,
    "Diversity",
    "Not familiar with this word",
    "Unsure",
    0,
    32,
    -32,
    "Liberty",
    "Not familiar with this word",
    "Unsure",
    0,
    29.3,
    -29.3,
    "Patriotism",
    "Negative",
    "Unsure",
    0,
    1.3,
    -1.3,
    "Racial Equity",
    "Not familiar with this word",
    "Unsure",
    0,
    24.6,
    -24.6,
    "Social Justice",
    "Not familiar with this word",
    "Unsure",
    0,
    5,
    -5,
    "Unity",
    "Not familiar with this word",
    "Unsure",
    0,
    25.4,
    -25.4
  ) |>
  filter(demos_urban %in% c("Rural", "Suburban", "Urban")) |>
  select(-c(score_change)) |>
  pivot_longer(
    contains("survey"),
    names_to = "year"
  ) |>
  mutate(year = parse_number(year)) |>
  rename("geography" = "demos_urban") |>
  mutate(value = value / 100)


use_data(
  pace_word_positivity,
  overwrite = TRUE
)

# DATA BY COUNTY ----------------------------------------------------------


# ├ Median Income -----------------------------------------------------------

import_median_income_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Median Income"
  ) %>%
    clean_names() %>%
    mutate(geography = str_trim(geography)) %>%
    set_names(c("geography", "value")) %>%
    mutate(year = data_year) %>%
    drop_na(geography) %>%
    # check data - will stop the process and raise an error otherwise
    verify(nrow(.) > 35) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert(is_uniq, geography) %>%
    assert(within_bounds(30000, 125000), value)
}

obtn_data_median_income <-
  map_df(obtn_years, import_median_income_data)

use_data(
  obtn_data_median_income,
  overwrite = TRUE
)


# ├ ALICE Data --------------------------------------------------------------

import_alice_data <- function(data_year) {
  alice_data_raw <-
    read_excel(
      here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
      sheet = "ALICE"
    ) %>%
    clean_names() %>%
    select(-c(
      alice_threshold_hh_under_65,
      alice_threshold_hh_65_years_and_over
    )) %>%
    mutate(geography = str_trim(geography)) %>%
    pivot_longer(
      -geography,
      names_to = "level"
    ) %>%
    mutate(
      level = case_when(
        level == "poverty_hh" ~ "Below Poverty Level",
        level == "alice_hh" ~ "Below ALICE Threshold",
        level == "above_alice_hh" ~ "Above ALICE Threshold"
      )
    ) %>%
    mutate(level = factor(
      level,
      levels = c(
        "Below Poverty Level",
        "Below ALICE Threshold",
        "Above ALICE Threshold"
      )
    )) %>%
    mutate(level = fct_rev(level)) |>
    mutate(year = data_year) |>
    drop_na(geography)

  if (data_year == 2024) {
    alice_data_raw <-
      alice_data_raw |>
      mutate(value = value / sum(value), .by = geography)
  } else {
    alice_data_raw <-
      alice_data_raw |>
      mutate(value = value / 100)
  }

  alice_data_raw %>%
    # check data - will stop the process and raise an error otherwise
    verify(nrow(.) > 105) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert(
      in_set(
        c(
          "Below Poverty Level",
          "Below ALICE Threshold",
          "Above ALICE Threshold"
        )
      ),
      level
    ) %>%
    # check it's unique : https://stackoverflow.com/questions/58799999/assert-that-a-combination-of-columns-is-unique-using-assertr
    assert_rows(col_concat, is_uniq, geography, level) %>%
    assert(within_bounds(0, 1), value)
}

obtn_alice_data <- map_df(obtn_years_minus_2019, import_alice_data)

use_data(
  obtn_alice_data,
  overwrite = TRUE
)

# Thresholds

import_alice_data_thresholds <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "ALICE"
  ) %>%
    clean_names() %>%
    select(
      c(
        geography,
        alice_threshold_hh_under_65,
        alice_threshold_hh_65_years_and_over
      )
    ) %>%
    drop_na() %>%
    pivot_longer(
      -geography,
      names_to = "age"
    ) %>%
    mutate(
      age = case_when(
        age == "alice_threshold_hh_under_65" ~ "Under 65",
        age == "alice_threshold_hh_65_years_and_over" ~ "65 and older"
      )
    ) %>%
    mutate(year = data_year) %>%
    # check data - will stop the process and raise an error otherwise
    verify(nrow(.) > 70) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert(in_set(c("Under 65", "65 and older")), age) %>%
    assert_rows(col_concat, is_uniq, geography, age) %>%
    # change if thresholds move a lot
    assert(within_bounds(20000, 100000), value)
}

obtn_alice_data_thresholds <-
  map_df(obtn_years_minus_2019, import_alice_data_thresholds)


use_data(
  obtn_alice_data_thresholds,
  overwrite = TRUE
)



# ├ Race/Ethnicity ----------------------------------------------------------

import_race_ethnicity_data <- function(data_year, state = "Oregon") {
  if (state == "Oregon") {
    raw_data <- read_excel(
      here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
      sheet = "Race Ethnicity"
    )
  }

  if (state == "California") {
    raw_data <- read_excel(
      here("data-raw", "2023 OBTN Edition for Siskiyou.xlsx"),
      sheet = "Race Ethnicity"
    ) |>
      mutate(geography = "Siskiyou")
  }

  raw_data %>%
    clean_names() %>%
    gather("population", "pct", -geography) %>%
    mutate(
      population = case_when(
        population == "percentage_of_population_white_non_latino" ~ "White",
        population == "percentage_of_population_black_non_latino" ~ "Black/African American",
        population == "percentage_of_population_asian_non_latino" ~ "Asian",
        population == "percentage_of_population_american_indian_or_alaska_native_non_latino" ~ "American Indian/Alaska Native",
        population == "percentage_of_population_native_hawaiian_pacific_islander_non_latino" ~ "Native Hawaiian/Pacific Islander",
        population == "percentage_of_population_multi_racial_non_latino" ~ "Two or more races",
        population == "percentage_of_population_other_race_non_latino" ~ "Some other race",
        population == "percentage_of_population_latino" ~ "Hispanic/Latino",
      )
    ) %>%
    rename("value" = "pct") %>%
    # Siskiyou data is e.g. 87% so need to divide by 100
    mutate(value = case_when(
      state == "California" ~ value / 100,
      .default = value
    )) |>
    mutate(year = data_year) %>%
    # checks
    # verify(nrow(.) > 300) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon",
      "Siskiyou"
    )), geography) %>%
    assert(
      in_set(
        c(
          "White",
          "Black/African American",
          "Asian",
          "American Indian/Alaska Native",
          "Native Hawaiian/Pacific Islander",
          "Two or more races",
          "Some other race",
          "Hispanic/Latino"
        )
      ),
      population
    ) %>%
    assert_rows(col_concat, is_uniq, geography, population) %>%
    assert(within_bounds(0, 1), value)
}

ca_race_ethnicity <-
  import_race_ethnicity_data(2023, "California")

obtn_race_ethnicity <-
  map(obtn_years, import_race_ethnicity_data) %>%
  bind_rows() |>
  group_by(population) %>%
  mutate(tertile_numeric = ntile(value, 3)) %>%
  mutate(tertile_numeric = as.numeric(tertile_numeric)) %>%
  ungroup() %>%
  mutate(
    tertile_text = case_when(
      tertile_numeric == 3 ~ "Top third",
      tertile_numeric == 2 ~ "Middle third",
      tertile_numeric == 1 ~ "Bottom third"
    )
  ) %>%
  mutate(tertile_text = fct_rev(tertile_text)) %>%
  arrange(population) %>%
  mutate(population = as_factor(population)) |>
  bind_rows(ca_race_ethnicity)


use_data(
  obtn_race_ethnicity,
  overwrite = TRUE
)


# ├ Employment Industries -------------------------------------------------

import_employment_industries_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Employment Industries",
    range = "A1:D37"
  ) %>%
    clean_names() %>%
    mutate(geography = str_trim(geography)) %>%
    gather("ranking", "industry", -geography) %>%
    select(-ranking) %>%
    filter(geography %in% obtn_oregon_counties) %>%
    mutate(top_three_industry = "Y") %>%
    complete(geography, industry, fill = list(top_three_industry = "N")) %>%
    mutate(year = data_year) %>%
    # checks
    verify(nrow(.) > 700) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert(
      in_set(
        c(
          "Accommodation",
          "Administrative and support services",
          "Ambulatory health care services",
          "Animal production",
          "Computer and electronic product manufacturing",
          "Crop production",
          "Educational services",
          "Executive, legislative, & general government",
          "Food and beverage stores",
          "Food manufacturing",
          "Food services and drinking places",
          "Forestry and logging",
          "Gasoline stations",
          "General merchandise stores",
          "Hospitals",
          "Justice, public order, and safety activi",
          "Nursing and residential care facilities",
          "Primary metal manufacturing",
          "Professional and technical services",
          "Social assistance",
          "Truck transportation",
          "Waste management and remediation service",
          "Wood product manufacturing",
          "Administrative and Support Services",
          "Ambulatory Health Care Services",
          "Animal Production and Aquaculture",
          "Computer and Electronic Product Manufacturing",
          "Crop Production",
          "Educational Services",
          "Executive, Legislative, and Other General Government Support",
          "Food Manufacturing",
          "Food Services and Drinking Places",
          "Forestry and Logging",
          "Justice, Public Order, and Safety Activities",
          "Nursing and Residential Care Facilities",
          "Primary Metal Manufacturing",
          "Professional, Scientific, and Technical Services",
          "Social Assistance",
          "Truck Transportation",
          "Waste Management and Remediation Services",
          "Wood Product Manufacturing",
          "Agriculture & forestry support activity",
          "Computer and electronic product mfg",
          "Heavy and civil engineering construction",
          "Justice, public order, and safety activities",
          "Transportation equipment manufacturing",
          "Specialty trade contractors",
          "Postal service"
        )
      ),
      industry
    ) %>%
    assert_rows(col_concat, is_uniq, geography, industry)
}

obtn_top_employment_industries <-
  map_df(obtn_years, import_employment_industries_data)

use_data(
  obtn_top_employment_industries,
  overwrite = TRUE
)


# ├ Population Pyramid ----------------------------------------------------

import_population_pyramid_data <- function(data_year, state = "Oregon") {
  age_order <- c(
    "0-4",
    "5-9",
    "10-14",
    "15-19",
    "20-24",
    "25-29",
    "30-34",
    "35-39",
    "40-44",
    "45-49",
    "50-54",
    "55-59",
    "60-64",
    "65-69",
    "70-74",
    "75-79",
    "80-84",
    "85+"
  )

  if (state == "Oregon") {
    raw_data <- read_excel(
      path = here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
      sheet = "Gender Age"
    )
  }

  if (state == "California") {
    raw_data <- read_excel(
      path = here("data-raw", "2023 OBTN Edition for Siskiyou.xlsx"),
      sheet = "Pop Pyramid"
    ) |>
      mutate(geography = "Siskiyou")
  }

  raw_data %>%
    clean_names() %>%
    gather("age_gender", "pct", -geography) %>%
    # Add gender variable
    mutate(gender = case_when(
      str_detect(age_gender, "females") ~ "female",
      TRUE ~ "male"
    )) %>%
    mutate(age_gender = str_remove(age_gender, "females_")) %>%
    mutate(age_gender = str_remove(age_gender, "males_")) %>%
    mutate(age_gender = str_replace(age_gender, "_", "-")) %>%
    mutate(age_gender = str_replace(age_gender, "80-85", "80-84")) %>%
    mutate(age_gender = str_replace(age_gender, "85", "85+")) %>%
    mutate(gender = case_match(
      gender,
      "male" ~ "Men",
      "female" ~ "Women"
    )) |>
    # Since we've removed gender from the age_gender variable, let's rename it to age
    rename("age" = "age_gender") %>%
    # Just reorder to make things nice
    select(geography, age, gender, pct) %>%
    mutate(age = fct_relevel(age, age_order)) %>%
    rename("value" = "pct") %>%
    mutate(year = data_year) %>%
    # checks
    # verify(nrow(.) > 1400) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon",
      "Siskiyou"
    )), geography) %>%
    assert(
      in_set(
        c(
          "Men",
          "Women"
        )
      ),
      gender
    ) %>%
    assert(
      in_set(
        age_order
      ),
      age
    ) %>%
    assert_rows(col_concat, is_uniq, geography, age, gender) %>%
    assert(within_bounds(0, 1), value)
}

ca_pop_pyramid_data <-
  import_population_pyramid_data(
    data_year = 2023,
    state = "California"
  )

obtn_population_pyramid <-
  map_df(obtn_years, import_population_pyramid_data) |>
  bind_rows(ca_pop_pyramid_data)

use_data(
  obtn_population_pyramid,
  overwrite = TRUE
)


# ├ Total Population --------------------------------------------------------

import_total_population_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Total Population"
  ) %>%
    clean_names() %>%
    mutate(year = data_year) %>%
    # checks
    verify(nrow(.) == 39) %>%
    assert(is.numeric, population) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography)
}

obtn_total_population <-
  map_df(obtn_years, import_total_population_data)

use_data(
  obtn_total_population,
  overwrite = TRUE
)

# ├ Rural Population --------------------------------------------------------

import_rural_population_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Rural Population"
  ) %>%
    clean_names() %>%
    rename("value" = "percent_rural") %>%
    mutate(value = value / 100) %>%
    mutate(year = data_year) %>%
    # checks
    verify(nrow(.) > 35) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert(within_bounds(0, 1), value)
}

obtn_rural_population <-
  map_df(obtn_years, import_rural_population_data)

use_data(
  obtn_rural_population,
  overwrite = TRUE
)



# ├ Population by Census Tract ----------------------------------------------

oregon_census_tract_size <- tracts(
  state = "OR",
  year = obtn_year - 2
) %>%
  clean_names() %>%
  # ALAND and AWATER are square meters
  # https://stackoverflow.com/questions/31246602/shape-area-and-aland-awater-in-tiger-census-data
  mutate(square_meters = aland + awater) %>%
  # Convert to square miles
  # https://www.unitconverters.net/area/square-meter-to-square-mile.htm
  mutate(square_miles = square_meters * 3.8610215854245E-7) %>%
  select(geoid, square_miles) %>%
  st_drop_geometry()

import_population_by_census_tract <- function(obtn_year) {
  get_acs(
    state = "OR",
    year = obtn_year - 2,
    variables = "B01003_001",
    geography = "tract"
  ) %>%
    clean_names() %>%
    right_join(oregon_census_tract_size, by = "geoid") %>%
    rename(population = estimate) %>%
    mutate(population_per_square_mile = population / square_miles) %>%
    select(geoid, population_per_square_mile) %>%
    mutate(year = obtn_year) %>%
    mutate(data_year = str_glue("{obtn_year - 6}-{obtn_year - 2}")) %>%
    # checks
    verify(nrow(.) == 1001) %>%
    assert(is.numeric, population_per_square_mile) %>%
    assert_rows(col_concat, is_uniq, geoid)
}

obtn_population_by_census_tract <-
  map_df(obtn_years, import_population_by_census_tract)

use_data(
  obtn_population_by_census_tract,
  overwrite = TRUE
)

# ├ Net Migration --------------------------------------------------------

import_net_migration_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Net Migration"
  ) %>%
    clean_names() %>%
    mutate(year = data_year) %>%
    mutate(geography = str_remove(geography, " Oregon")) %>%
    # checks
    verify(nrow(.) > 35) %>%
    assert(is.numeric, net_migration) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert(within_bounds(-1000, 1000), net_migration)
}

obtn_net_migration <- map_df(obtn_years, import_net_migration_data)

use_data(
  obtn_net_migration,
  overwrite = TRUE
)


# ├ Life Expectancy -------------------------------------------------------

import_life_expectancy_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Life Expectancy"
  ) %>%
    clean_names() %>%
    pivot_longer(
      -geography,
      names_to = "gender"
    ) %>%
    mutate(gender = case_when(
      str_detect(gender, "_overall") ~ "Total",
      str_detect(gender, "_male") ~ "Men",
      str_detect(gender, "_female") ~ "Women"
    )) %>%
    mutate(year = data_year) %>%
    # checks
    verify(nrow(.) > 90) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert(
      in_set(
        c(
          "Total",
          "Men",
          "Women"
        )
      ),
      gender
    ) %>%
    assert_rows(col_concat, is_uniq, geography, gender) %>%
    assert(within_bounds(60, 100), value)
}

obtn_life_expectancy <-
  map_df(obtn_years_minus_2019, import_life_expectancy_data)

use_data(
  obtn_life_expectancy,
  overwrite = TRUE
)


# ├ Total Land Area ---------------------------------------------------------

import_total_land_area_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Total Land Area"
  ) %>%
    clean_names() %>%
    mutate(year = data_year) %>%
    mutate(geography = str_remove(geography, " Oregon")) %>%
    # checks
    verify(nrow(.) > 35) %>%
    assert(is.numeric, land_area) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert_rows(col_concat, is_uniq, geography)
}

obtn_total_land_area <-
  map_df(obtn_years, import_total_land_area_data)

use_data(
  obtn_total_land_area,
  overwrite = TRUE
)


# ├ Public Land ---------------------------------------------------------

import_public_land_area_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Public Lands"
  ) %>%
    clean_names() %>%
    rename("value" = "publicly_owned_lands") %>%
    mutate(value = value / 100) %>%
    mutate(year = data_year) %>%
    # checks
    verify(nrow(.) > 35) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert_rows(col_concat, is_uniq, geography) %>%
    assert(within_bounds(0, 1), value)
}

obtn_public_land <- map_df(obtn_years, import_public_land_area_data)

use_data(
  obtn_public_land,
  overwrite = TRUE
)


# ├ Notable Features --------------------------------------------------------

# Data pasted from 2018 OBTN, which is the last time we made this

# obtn_notable_features <- tibble::tribble(
#   ~geography,                                  ~notable_feature,
#   "Baker",                 "Wallowa Whitman National Forest",
#   "Baker",           "Hells Canyon National Recreation Area",
#   "Baker",                          "Anthony Lakes Ski Area",
#   "Benton",          "Corvallis Watershed Wild Animal Refuge",
#   "Benton",                                      "Marys Peak",
#   "Benton",                   "Alsea Falls Recreational Site",
#   "Clackamas",                         "Mt Hood National Forest",
#   "Clackamas",                                "Willamette Falls",
#   "Clackamas",                                   "Trillium Lake",
#   "Clatsop", "Lewis & Clark National & State Historical Parks",
#   "Clatsop",                                   "Haystack Rock",
#   "Clatsop",                         "Fort Stevens State Park",
#   "Columbia",              "Forest Grove District State Forest",
#   "Columbia",                                   "Collins Beach",
#   "Columbia",                               "Multnomah Channel",
#   "Coos",           "Oregon Dunes National Recreation Area",
#   "Coos",                           "Sunset Bay State Park",
#   "Coos",                          "Shore Acres State Park",
#   "Crook",                          "Ochoco National Forest",
#   "Crook",              "Prineville Reservoir Wildlife Area",
#   "Crook",                       "Ochoco Wayside State Park",
#   "Curry",            "Rogue River-Siskiyou National Forest",
#   "Curry",                          "Cape Blanco State Park",
#   "Curry",                  "Floras Lake State Natural Area",
#   "Deschutes",                         "Mount Bachelor Ski Area",
#   "Deschutes",                           "Smith Rock State Park",
#   "Deschutes",                                 "Lava River Cave",
#   "Douglas",           "Oregon Dunes National Recreation Area",
#   "Douglas",                                 "Wildlife Safari",
#   "Douglas",                          "Umpqua National Forest",
#   "Gilliam",               "Cottonwood Canyon Recreation Area",
#   "Gilliam",                           "J S Burres State Park",
#   "Gilliam",                        "Earl Snell Memorial Park",
#   "Grant",          "John Day Fossil Beds National Monument",
#   "Grant",                        "Umatilla National Forest",
#   "Grant",                         "Malheur National Forest",
#   "Harney",                                 "Steen Mountains",
#   "Harney",                "Malheur National Wildlife Refuge",
#   "Harney",                         "Malheur National Forest",
#   "Hood River",                      "Mount Hood National Forest",
#   "Hood River",                        "Mt Hood Meadows Ski Area",
#   "Hood River",                                     "Eagle Creek",
#   "Jackson",                             "Bear Creek Greenway",
#   "Jackson",              "Cascade-Siskiyou National Monument",
#   "Jackson",                                   "Oregon Vortex",
#   "Jefferson",                   "The Cove Palisades State Park",
#   "Jefferson",                                     "Black Butte",
#   "Jefferson",                              "Lake Billy Chinook",
#   "Josephine",    "Oregon Caves National Monument and Preserves",
#   "Josephine",                                "Indian Mary Park",
#   "Josephine",              "Rogue River-Siskiyou National Park",
#   "Klamath",                       "Crater Lake National Park",
#   "Klamath",                          "Winema National Forest",
#   "Klamath",                               "Lake of the Woods",
#   "Lake",                  "Fremont-Winema National Forest",
#   "Lake",          "Hart Mountain National Antelope Refuge",
#   "Lake",                                    "Derrick Cave",
#   "Lane",                                 "Row River Trail",
#   "Lane",                                  "Sea Lion Caves",
#   "Lane",                                   "Three Sisters",
#   "Lincoln",                           "Oregon Coast Aquarium",
#   "Lincoln",               "Yaquina Bay State Recreation Site",
#   "Lincoln",                   "D River State Recreation Site",
#   "Linn",                                "Mount Washington",
#   "Linn",                       "Middle Santiam Wilderness",
#   "Linn",                      "Willamette National Forest",
#   "Malheur",                          "Lake Owyhee State Park",
#   "Malheur",                       "Succor Creek Natural Area",
#   "Malheur",                   "Ontario State Recreation Site",
#   "Marion",                         "Silver Falls State Park",
#   "Marion",                                "Enchanted Forest",
#   "Marion",                            "Oregon State Capitol",
#   "Morrow",                        "Umatilla National Forest",
#   "Morrow",               "Umatilla National Wildlife Refuge",
#   "Morrow",                                "Willow Creek Dam",
#   "Multnomah",                                 "Multnomah Falls",
#   "Multnomah",                         "Mt Hood National Forest",
#   "Multnomah",                      "Mark O Hatfield Wilderness",
#   "Polk",          "Basket Slough National Wildlife Refuge",
#   "Polk",                            "Valley of the Giants",
#   "Polk",             "Sarah Helmick State Recreation Site",
#   "Sherman",           "Deschutes River State Recreation Area",
#   "Sherman",                    "Cottonwood Canyon State Park",
#   "Sherman",                                    "John Day Dam",
#   "Tillamook",                          "Tillamook State Forest",
#   "Tillamook",                          "Nehalem Bay State Park",
#   "Tillamook",            "Cape Meares National Wildlife Refuge",
#   "Umatilla",                        "Umatilla National Forest",
#   "Umatilla",                      "Bridge Creek Wildlife Area",
#   "Umatilla",                             "Hat Rock State Park",
#   "Union",                        "Umatilla National Forest",
#   "Union",                         "Eagle Cap Mountain Peak",
#   "Union",                     "Mount Emily Recreation Area",
#   "Wallowa",                         "Wallowa Lake State Park",
#   "Wallowa",                                 "Zumwalt Prairie",
#   "Wallowa",                 "Wallowa Whitman National Forest",
#   "Wasco",                         "Mt Hood National Forest",
#   "Wasco",                 "Deschutes River Recreation Site",
#   "Wasco",                                    "Celilo Falls",
#   "Washington",                      "LL Stub Stewart State Park",
#   "Washington",         "Tualatin River National Wildlife Refuge",
#   "Washington",                      "Tualatin Hills Nature Park",
#   "Wheeler",                          "Ochoco National Forest",
#   "Wheeler",                                   "Painted Hills",
#   "Wheeler",                        "Umatilla National Forest",
#   "Yamhill",                                         "Mt Hebo",
#   "Yamhill",                            "Pheasant Creek Falls",
#   "Yamhill",               "Bald Creek State Scenic Viewpoint"
# ) %>%
#   mutate(location_for_geocoding = str_glue("{notable_feature}, {geography} County, Oregon")) %>%
#   geocode(address = location_for_geocoding,
#           min_time = 1,
#           method = "iq")
#
# # Update ones that either didn't get geocoded or are wrong
# # Did this by hand checking on Google Maps
#
# obtn_notable_features <- obtn_notable_features %>%
#   mutate(notable_feature = str_replace(notable_feature,
#                                        "Rogue River-Siskiyou National Park",
#                                        "Rogue River-Siskiyou National Forest")) %>%
#   mutate(notable_feature = str_replace(notable_feature,
#                                        "Mount Hood",
#                                        "Mt Hood")) %>%
#   mutate(notable_feature = str_replace_all(notable_feature, "&", "and")) %>%
#   mutate(lat = case_when(
#     notable_feature == "Wallowa Whitman National Forest" & geography == "Baker" ~ 45.02345981728898,
#     notable_feature == "Hells Canyon National Recreation Area" ~ 45.055778458412036,
#     notable_feature == "Corvallis Watershed Wild Animal Refuge" ~ 44.5449210809429,
#     notable_feature == "Lewis and Clark National and State Historical Parks" ~ 46.13729936314177,
#     notable_feature == "Forest Grove District State Forest" ~ 45.93484357203124,
#     notable_feature == "Rogue River-Siskiyou National Forest" & geography == "Curry" ~ 42.06104346555411,
#     notable_feature == "Mount Bachelor Ski Area" ~ 44.00301256545782,
#     notable_feature == "Oregon Dunes National Recreation Area" & geography == "Douglas" ~ 43.71826346678317,
#     notable_feature == "Oregon Dunes National Recreation Area" & geography == "Coos" ~ 43.60059334869395,
#     notable_feature == "J S Burres State Park" ~ 45.47732444550658,
#     notable_feature == "Umatilla National Forest" & geography == "Grant" ~ 44.987624346220215,
#     notable_feature == "Rogue River-Siskiyou National Forest" & geography == "Josephine" ~ 42.47856975892546,
#     notable_feature == "Oregon Caves National Monument and Preserves" ~ 42.13706451153894,
#     notable_feature == "Fremont-Winema National Forest" ~ 42.91465050526599,
#     notable_feature == "Lake Owyhee State Park" ~ 43.810392697619605,
#     notable_feature == "Mt Hood National Forest" & geography == "Multnomah" ~ 45.48593824049954,
#     notable_feature == "Mark O Hatfield Wilderness" & geography == "Multnomah" ~ 45.61576531420703,
#     notable_feature == "Umatilla National Forest" & geography == "Union" ~ 45.194838193450224,
#     notable_feature == "Mt Hood National Forest" & geography == "Wasco" ~ 45.414443691410746,
#     notable_feature == "Umatilla National Forest" & geography == "Wheeler" ~ 44.970792461703645,
#     notable_feature == "Ochoco National Forest" & geography == "Wheeler" ~ 44.45246060485592,
#     TRUE ~ lat
#   )) %>%
#   mutate(long = case_when(
#     notable_feature == "Wallowa Whitman National Forest" & geography == "Baker" ~ -117.38744367528058,
#     notable_feature == "Hells Canyon National Recreation Area" ~ -116.88233133951687,
#     notable_feature == "Corvallis Watershed Wild Animal Refuge" ~ -123.53229217330212,
#     notable_feature == "Lewis and Clark National and State Historical Parks" ~ -123.88076888076866,
#     notable_feature == "Forest Grove District State Forest" ~ -123.07249979472094,
#     notable_feature == "Rogue River-Siskiyou National Forest" & geography == "Curry" ~ -123.87646576358873,
#     notable_feature == "Mount Bachelor Ski Area" ~ -121.6776227639425,
#     notable_feature == "Oregon Dunes National Recreation Area" & geography == "Douglas" ~ -124.1035409488964,
#     notable_feature == "Oregon Dunes National Recreation Area" & geography == "Coos" ~ -124.20136591141907,
#     notable_feature == "J S Burres State Park" ~ -120.46685084430216,
#     notable_feature == "Umatilla National Forest" & geography == "Grant" ~ -118.68421387398422,
#     notable_feature == "Rogue River-Siskiyou National Forest" & geography == "Josephine" ~ -123.731241646653,
#     notable_feature == "Oregon Caves National Monument and Preserves" ~ -123.41385085107241,
#     notable_feature == "Fremont-Winema National Forest" ~ -121.34693050932547,
#     notable_feature == "Lake Owyhee State Park" ~ -117.25451548937511,
#     notable_feature == "Mt Hood National Forest" & geography == "Multnomah" ~ -121.91335835985407,
#     notable_feature == "Mark O Hatfield Wilderness" & geography == "Multnomah" ~ -121.96142354185297,
#     notable_feature == "Umatilla National Forest" & geography == "Union" ~ -118.53691378784315,
#     notable_feature == "Mt Hood National Forest" & geography == "Wasco" ~ -121.46216316275797,
#     notable_feature == "Umatilla National Forest" & geography == "Wheeler" ~ -119.78464332067196,
#     notable_feature == "Ochoco National Forest" & geography == "Wheeler" ~ -120.05622006779049,
#     TRUE ~ long
#   ))
#
# obtn_notable_features %>%
#   filter(geography == "Coos")
#
# obtn_county_map(2022, "Coos")
#
#
#
# use_data(obtn_notable_features,
#          overwrite = TRUE)


# ├ Largest Community -------------------------------------------------------

# import_largest_community_data <- function(data_year) {
#   read_excel(
#     here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
#     sheet = "Largest Community"
#   ) %>%
#     clean_names() %>%
#     select(1:3) %>%
#     set_names(c("geography", "largest_community", "population")) %>%
#     mutate(
#       largest_community = case_when(
#         largest_community == "Prinville" ~ "Prineville",
#         TRUE ~ largest_community
#       )
#     ) %>%
#     mutate(city_county = str_glue("{largest_community}, {geography} County, Oregon")) %>%
#     geocode(address = city_county) %>%
#     mutate(year = data_year) %>%
#     # checks
#     verify(nrow(.) == 36) %>%
#     assert(is.numeric, population) %>%
#     assert(in_set(c(
#       obtn_oregon_counties
#     )), geography) %>%
#     assert_rows(col_concat, is_uniq, geography)
# }
#
# obtn_largest_community <-
#   map_df(obtn_years, import_largest_community_data)
#
# use_data(
#   obtn_largest_community,
#   overwrite = TRUE
# )

# ├ Largest 100 Communities -------------------------------------------------------

import_oregon_communities_population <- function(obtn_year) {
  tidycensus::get_acs(
    state = "OR",
    year = obtn_year - 2,
    geography = "place",
    geometry = TRUE,
    variables = "B01003_001"
  ) %>%
    janitor::clean_names() %>%
    sf::st_centroid() %>%
    mutate(year = obtn_year)
}

# no checks on this --> what to test ?

obtn_communities_population <-
  map_df(obtn_years, import_oregon_communities_population)

use_data(
  obtn_communities_population,
  overwrite = TRUE
)

# ├ County Seat -------------------------------------------------------

# Only exists in 2022 data

# county_seats <- read_excel(
#   here("data-raw", str_glue("2022-obtn-by-county.xlsx")),
#   sheet = "Largest Community"
# ) %>%
#   clean_names() %>%
#   select(1, 4:5) %>%
#   set_names("geography", "county_seat", "population")
#
#
# obtn_county_seats <- county_seats %>%
#   select(geography, county_seat) %>%
#   mutate(city_county = str_glue("{county_seat}, {geography} County, Oregon")) %>%
#   geocode(address = city_county) %>%
#   mutate(year = 2022)
#
# use_data(
#   obtn_county_seats,
#   overwrite = TRUE
# )
#
# obtn_unique_county_seats <- obtn_largest_community %>%
#   filter(year == 2022) %>%
#   left_join(county_seats, by = "geography") %>%
#   mutate(seat_largest_cmty_differ = case_when(
#     largest_community == county_seat ~ FALSE,
#     TRUE ~ TRUE
#   )) %>%
#   filter(seat_largest_cmty_differ == TRUE) %>%
#   select(geography, county_seat) %>%
#   mutate(city_county = str_glue("{county_seat}, {geography} County, Oregon")) %>%
#   geocode(address = city_county) %>%
#   mutate(year = 2022)
#
#
#
# use_data(
#   obtn_unique_county_seats,
#   overwrite = TRUE
# )


# DATA BY MEASURE ---------------------------------------------------------

# ├ Data for Choropleth Maps ----------------------------------------------


# ├ ├ Measures ----------------------------------------------------------------

# Create df of all sheets to use later on


get_single_year_measures <- function(data_year) {
  here("data-raw", str_glue("{data_year}-obtn-by-measure.xlsx")) %>%
    excel_sheets() %>%
    tibble() %>%
    set_names("measure") %>%
    mutate(year = data_year)
}

obtn_data_choropleth_measures <-
  map_df(obtn_years, get_single_year_measures)

obtn_data_choropleth_measures %>%
  count(measure)

# ├ ├ Measure Categories ----------------------------------------------------------------

# The categories are different things in different years
# Plus the names vary from year to year

choropleth_categories_2019 <- tribble(
  ~year,
  ~category,
  ~measure,
  2019,
  "Social",
  "Food Insecurity",
  2019,
  "Social",
  "Child Poverty",
  2019,
  "Social",
  "Child Abuse",
  2019,
  "Social",
  "Index Crime",
  2019,
  "Social",
  "Voter Participation",
  2019,
  "Education",
  "4yr Degree or Greater",
  2019,
  "Education",
  "2yr Degrees",
  2019,
  "Education",
  "Graduation Rate",
  2019,
  "Education",
  "Higher ed enrollment",
  2019,
  "Education",
  "Letter Sounds",
  2019,
  "Economy",
  "Unemployment Rate",
  2019,
  "Economy",
  "LFPR",
  2019,
  "Economy",
  "Job Growth",
  2019,
  "Economy",
  "Property Tax per Person",
  2019,
  "Economy",
  "Housing Cost Burden",
  2019,
  "Health",
  "Physically Active Adults",
  2019,
  "Health",
  "Adult Smoking",
  2019,
  "Health",
  "Healthy Diet",
  2019,
  "Health",
  "Vaccination Rate 2yr olds",
  2019,
  "Health",
  "Low Weight Births",
  2019,
  "Infrastructure",
  "Broadband Access",
  2019,
  "Infrastructure",
  "Transit Service",
  2019,
  "Infrastructure",
  "Vehicle Miles Traveled",
  2019,
  "Infrastructure",
  "Developed or Cultivated Land",
  2019,
  "Infrastructure",
  "Mobile Homes",
  2019,
  "Other",
  "Total Population",
  2019,
  "Other",
  "Rural Population",
  2019,
  "Other",
  "Migration",
  2019,
  "Other",
  "Median Income",
  2019,
  "Other",
  "Land Area",
  2019,
  "Other",
  "Public Lands",
  2019,
  "Other",
  "Life Expectancy - Overall",
  2019,
  "Other",
  "Above ALICE HH",
  2019,
  "Other",
  "Financial Hardship"
)

choropleth_categories_2019

create_2020_to_2022_categories <- function(data_year) {
  tribble(
    ~year,
    ~category,
    ~measure,
    data_year,
    "Community",
    "Food Insecurity",
    data_year,
    "Community",
    "Child Poverty",
    data_year,
    "Community",
    "Foster Care",
    data_year,
    "Community",
    "Index Crime",
    data_year,
    "Community",
    "Voter Participation",
    data_year,
    "Education",
    "4yr Degree or Greater",
    data_year,
    "Education",
    "Graduation Rate",
    data_year,
    "Education",
    "9th Grade on Track",
    data_year,
    "Education",
    "3rd Grade ELA",
    data_year,
    "Education",
    "Letter Sounds",
    data_year,
    "Economy",
    "Unemployment Rate",
    data_year,
    "Economy",
    "LFPR",
    data_year,
    "Economy",
    "Job Growth",
    data_year,
    "Economy",
    "Property Tax per Person",
    data_year,
    "Economy",
    "Rent Costs",
    data_year,
    "Health",
    "Tobacco Use",
    data_year,
    "Health",
    "Good Mental Health",
    data_year,
    "Health",
    "Good Physical Health",
    data_year,
    "Health",
    "Vaccination Rate 2yr olds",
    data_year,
    "Health",
    "Low Weight Births",
    data_year,
    "Infrastructure",
    "Broadband Access",
    data_year,
    "Infrastructure",
    "Transit Service",
    data_year,
    "Infrastructure",
    "VMT per capita",
    data_year,
    "Infrastructure",
    "Childcare Availability",
    data_year,
    "Infrastructure",
    "Mobile Homes",
    data_year,
    "Other",
    "Total Population",
    data_year,
    "Other",
    "Rural Population",
    data_year,
    "Other",
    "Migration",
    data_year,
    "Other",
    "Median Income",
    data_year,
    "Other",
    "Land Area",
    data_year,
    "Other",
    "Public Lands",
    data_year,
    "Other",
    "Life Expectancy - Overall",
    data_year,
    "Other",
    "Above ALICE HH",
    data_year,
    "Other",
    "Financial Hardship",
    data_year,
    "Other",
    "Vehicle Miles Traveled",
    data_year,
    "Other",
    "Developed or Cultivated Land"
  )
}

# A few measures were changed in 2023. This starts with the 2020 to 2022 categories and makes a few changes.

choropleth_categories_2023_2024 <-
  create_2020_to_2022_categories(2023) %>%
  bind_rows(create_2020_to_2022_categories(2024)) %>%
  mutate(
    measure = case_when(
      measure == "Letter Sounds" ~ "5th Grade Math",
      measure == "Good Mental Health" ~ "Mental Health Providers",
      measure == "Good Physical Health" ~ "Good Health",
      measure == "Transit Service" ~ "EV",
      TRUE ~ measure # .default = measure was sending an error --> ???
    )
  )

choropleth_categories <-
  map_df(c(2020, 2021, 2022), create_2020_to_2022_categories) %>%
  bind_rows(choropleth_categories_2019) %>%
  bind_rows(choropleth_categories_2023_2024) %>%
  arrange(year)


# ├ ├ Combine Measures and Categories ----------------------------------------------------------------

choropleth_measures_categories <- obtn_data_choropleth_measures %>%
  left_join(choropleth_categories, by = c("year", "measure"))

choropleth_measures_categories

# ├ Create function to get one measure for one year ------------------------------------------------------------------

import_single_measure_data <- function(row_number) {
  print(row_number)

  choropleth_measures_categories_filtered <-
    choropleth_measures_categories %>%
    slice(row_number)

  read_excel(
    here(
      "data-raw",
      str_glue(
        "{choropleth_measures_categories_filtered$year}-obtn-by-measure.xlsx"
      )
    ),
    sheet = choropleth_measures_categories_filtered$measure
  ) %>%
    clean_names() %>%
    mutate(year = choropleth_measures_categories_filtered$year) %>%
    mutate(measure = choropleth_measures_categories_filtered$measure) %>%
    mutate(category = choropleth_measures_categories_filtered$category) %>%
    rename(
      geography = county,
      value = numeric_only
    ) %>%
    mutate(value = as.character(value)) %>%
    # For Mental Health Providers in 2023, the data looks like 151:1
    # Need to manually remove the :1 portion
    mutate(value = case_when(
      str_detect(value, "\\:") ~ str_remove(value, "\\:1"),
      TRUE ~ value
    )) %>%
    # There are some non-numeric things (e.g. ID); get rid of them
    mutate(value = as.numeric(value)) %>%
    # This is the manual ranking OSU provides
    rename(drop_on_measure_tables = rank) %>%
    # Convert to character, otherwise we can't combine
    mutate(drop_on_measure_tables = as.character(drop_on_measure_tables)) %>%
    # Only flag those with ID
    mutate(drop_on_measure_tables = case_when(
      drop_on_measure_tables == "ID" ~ TRUE,
      TRUE ~ FALSE
    )) %>%
    # Manually replace just "Rural" or "Urban" with "Rural Oregon" and "Urban Oregon"
    mutate(geography = case_when(
      geography == "Rural" ~ "Rural Oregon",
      geography == "Urban" ~ "Urban Oregon",
      TRUE ~ geography
    )) %>%
    # Keep variables we need
    select(
      geography,
      measure,
      category,
      drop_on_measure_tables,
      value,
      year
    ) %>%
    # There are some asterisks; remove them
    mutate(geography = str_remove(geography, "\\*")) %>%
    # Make sure there are no blank spaces
    mutate(geography = str_trim(geography)) %>%
    # Round to one digit
    mutate(value = round_half_up(value, digits = 1)) %>%
    # checks
    verify(nrow(.) >= 35) %>%
    assert(is.numeric, value) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon",
      "Rural Oregon",
      "Urban Oregon"
    )), geography) %>%
    assert(
      in_set(
        choropleth_measures_categories_filtered$category
      ),
      category
    ) %>%
    assert_rows(col_concat, is_uniq, geography, measure, category, drop_on_measure_tables)
}

import_single_measure_data(34) %>%
  print(n = 40)

# ├ Get all data ------------------------------------------------------------------

all_measures_data <-
  map_df(
    1:nrow(choropleth_measures_categories),
    import_single_measure_data
  )

# ├ ├ Rank, tertile, etc ----------------------------------------------------------------

obtn_data_by_measure <- all_measures_data %>%
  mutate(geography_type = case_when(
    str_detect(geography, "Oregon") ~ "State",
    TRUE ~ "County"
  )) %>%
  # Don't include Oregon/rural/urban in rankings calculations
  # Turn it into NA for ranking purposes
  mutate(value_for_ranking = case_when(
    geography_type == "State" ~ NaN,
    TRUE ~ value
  )) %>%
  # If the item is flagged to be dropped on measure tables, turn the value into NA
  mutate(value_for_ranking = case_when(
    drop_on_measure_tables == TRUE ~ NaN,
    geography_type == "State" ~ NaN,
    TRUE ~ value
  )) %>%
  # Drop drop_on_measure_tables column
  # select(-drop_on_measure_tables) %>%

  group_by(year, measure) %>%
  mutate(rank = min_rank(-value_for_ranking)) %>%
  ungroup() %>%
  mutate(value_for_table = case_when(
    drop_on_measure_tables == TRUE ~ NaN,
    TRUE ~ value
  )) %>%
  # Start tertile stuff
  mutate(tertile_numeric = santoku::chop_equally(rank, 3)) %>%
  mutate(tertile_numeric = as.numeric(tertile_numeric)) %>%
  mutate(
    tertile_text = case_when(
      tertile_numeric == 1 ~ "Top third",
      tertile_numeric == 2 ~ "Middle third",
      tertile_numeric == 3 ~ "Bottom third"
    )
  ) %>%
  # Add ID for all missing tertile values for counties and convert it into a factor
  mutate(tertile_text = case_when(
    is.na(tertile_text) & geography_type == "County" ~ "ID",
    TRUE ~ tertile_text
  )) %>%
  mutate(tertile_text = factor(
    tertile_text,
    levels = c(
      "Top third",
      "Middle third",
      "Bottom third",
      "No college",
      "ID"
    )
  )) %>%
  arrange(measure, desc(value), geography) %>%
  select(
    year,
    geography,
    measure,
    category,
    value,
    value_for_table,
    rank,
    tertile_numeric,
    tertile_text
  )



# ├ ├ Save Data ----------------------------------------------------------------

use_data(
  obtn_data_by_measure,
  overwrite = TRUE
)


use_data(
  obtn_data_choropleth_measures,
  overwrite = TRUE
)

# ├ Tribes ------------------------------------------------------------------

import_tribes_data <- function(data_year) {
  read_excel(
    here("data-raw", str_glue("{data_year}-obtn-by-county.xlsx")),
    sheet = "Tribes"
  ) %>%
    clean_names() %>%
    pivot_longer(
      -geography,
      names_to = "tribe",
      values_to = "present"
    ) %>%
    drop_na(present) %>%
    mutate(present = "Y") %>%
    # complete(geography, tribe, fill = list(present = "N")) %>%
    mutate(year = data_year) %>%
    # checks
    verify(nrow(.) > 35) %>%
    assert(in_set(c(
      obtn_oregon_counties,
      "Rural",
      "Urban",
      "Oregon"
    )), geography) %>%
    assert_rows(col_concat, is_uniq, geography, tribe, present)
}

obtn_tribes <- map_df(obtn_years, import_tribes_data)

use_data(
  obtn_tribes,
  overwrite = TRUE
)


# TABLES DATA -------------------------------------------------------------
# measures -> exported for the measures table
comma_measures <- c(
  "Foster Care",
  "Index Crime",
  "Letter Sounds",
  "Job Growth",
  "Childcare Availability",
  "VMT per capita",
  "Total Population",
  # "Rural Population",
  "Migration",
  "Land Area",
  "Vehicle Miles Traveled",
  "VMT per capita",
  "Life Expectancy - Overall",
  "EV",
  "Mental Health Providers"
)

use_data(
  comma_measures,
  overwrite = TRUE
)

dollar_measures <- c(
  "Property Tax per Person",
  "Rent Costs",
  "Median Income"
)

use_data(
  dollar_measures,
  overwrite = TRUE
)

measure_order <- tribble(
  ~measure,
  "Food Insecurity",
  "Child Poverty",
  "Foster Care",
  "Index Crime",
  "Voter Participation",
  "3rd Grade ELA",
  "5th Grade Math",
  "9th Grade on Track",
  "Graduation Rate",
  "4yr Degree or Greater",
  "Unemployment Rate",
  "LFPR",
  "Job Growth",
  "Property Tax per Person",
  "Rent Costs",
  "Low Weight Births",
  "Vaccination Rate 2yr olds",
  "Good Health",
  "Mental Health Providers",
  "Tobacco Use",
  "Broadband Access",
  "Childcare Availability",
  "EV",
  "Mobile Homes",
  "VMT per capita"
) %>%
  mutate(measure_order = row_number())

use_data(
  measure_order,
  overwrite = TRUE
)

# create county table data for all years
county_table_data <- obtn_data_by_measure %>%
  filter(category != "Other") %>%
  mutate(
    value_formatted = case_when(
      measure %in% comma_measures ~ comma(value, accuracy = 0.1),
      measure %in% dollar_measures ~ dollar(value, accuracy = 1),
      TRUE ~ percent(value / 100, accuracy = 0.1)
    )
  ) %>%
  mutate(
    value_formatted = case_when(
      measure == "Letter Sounds" &
        !str_detect(geography, "Oregon") ~ as.character(str_glue("{value_formatted} of 26")),
      measure %in% c("VMT per capita", "EV", "Mental Health Providers") ~ comma(value, accuracy = 1),
      TRUE ~ value_formatted
    )
  ) %>%
  left_join(measure_order, by = "measure") %>%
  arrange(geography, measure_order) %>%
  select(-c(value, measure_order))

# filter on Oregon data
oregon_data <- county_table_data %>%
  filter(str_detect(geography, "Oregon")) %>%
  select(-category) %>%
  pivot_wider(
    id_cols = c(year, measure),
    names_from = "geography",
    values_from = "value_formatted"
  )

# all counties tables
tables_county_data <-
  county_table_data %>%
  filter(!str_detect(geography, "Oregon")) %>%
  left_join(oregon_data, by = join_by(year, measure)) %>%
  rename(value = value_formatted) %>%
  relocate(category, .before = 1) %>%
  select(-c(tertile_text, tertile_numeric, value_for_table)) |>
  mutate(measure = case_match(
    measure,
    "Food Insecurity" ~ "Food insecurity",
    "Child Poverty" ~ "Child poverty*",
    "Foster Care" ~ "Foster care rate (per 1,000 pop.)",
    "Index Crime" ~ "Index crime (per 1,000 pop.)",
    "Voter Participation" ~ "Voter participation",
    "3rd Grade ELA" ~ "3rd grade reading",
    "5th Grade Math" ~ "5th grade math",
    "9th Grade on Track" ~ "9th grade on track",
    "Graduation Rate" ~ "5-year high school graduation rate",
    "4yr Degree or Greater" ~ "4-year college degree or greater",
    "Unemployment Rate" ~ "Unemployment rate",
    "LFPR" ~ "Labor force participation rate",
    "Job Growth" ~ "Job growth (per 1,000 pop.)",
    "Property Tax per Person" ~ "Property tax (per person)",
    "Rent Costs" ~ "Rent costs (1 bedroom/1 bath)",
    "Low Weight Births" ~ "Low weight births",
    "Vaccination Rate 2yr olds" ~ "Vaccination rate, 2-year-olds",
    "Good Health" ~ "Good or better health",
    "Mental Health Providers" ~ "Mental health (pop. per provider)",
    "Tobacco Use" ~ "Tobacco use",
    "Broadband Access" ~ "Broadband availability",
    "Childcare Availability" ~ "Child care (slots per 100 children)",
    "EV" ~ "Electric vehicle charging stations",
    "Mobile Homes" ~ "Mobile homes",
    "VMT per capita" ~ "Vehicle miles traveled (per capita)"
  ))


use_data(
  tables_county_data,
  overwrite = TRUE
)

use_data(
  obtn_boundaries_oregon_state,
  overwrite = TRUE
)

obtn_boundaries_oregon_counties <-
  counties(cb = TRUE, class = "sf") %>%
  clean_names() %>%
  filter(statefp == 41) %>%
  select(name) %>%
  rename("geography" = "name") %>%
  st_transform(crs = "WGS84")


use_data(
  obtn_boundaries_oregon_counties,
  overwrite = TRUE
)


obtn_boundaries_oregon_census_tracts <- tracts(
  state = "OR",
  year = obtn_year - 2,
  cb = FALSE
) %>%
  clean_names() %>%
  select(geoid) %>%
  st_transform(crs = "WGS84")

use_data(
  obtn_boundaries_oregon_census_tracts,
  overwrite = TRUE
)





# COLORS ------------------------------------------------------------------

tfff_dark_green <- "#004f39"
tfff_light_green <- "#A9C27F"
tfff_orange <- "#e65100"
tfff_yellow <- "#FBC02D"
tfff_blue <- "#283593"
tfff_light_blue <- "#B3C0D6"
tfff_red <- "#B71C1C"
tfff_dark_gray <- "#545454"
tfff_medium_gray <- "#a8a8a8"
tfff_light_gray <- "#eeeeee"
tfff_middle_gray <- "#828282"

use_data(
  tfff_dark_green,
  overwrite = TRUE
)

use_data(
  tfff_light_green,
  overwrite = TRUE
)

use_data(
  tfff_orange,
  overwrite = TRUE
)

use_data(
  tfff_yellow,
  overwrite = TRUE
)

use_data(
  tfff_blue,
  overwrite = TRUE
)

use_data(
  tfff_light_blue,
  overwrite = TRUE
)

use_data(
  tfff_red,
  overwrite = TRUE
)

use_data(
  tfff_dark_gray,
  overwrite = TRUE
)

use_data(
  tfff_medium_gray,
  overwrite = TRUE
)

use_data(
  tfff_light_gray,
  overwrite = TRUE
)

use_data(
  tfff_middle_gray,
  overwrite = TRUE
)

obtn_choropleth_colors <- rev(c(
  tfff_light_gray,
  "#A9C27F",
  "#6e8f68",
  "#004f39"
))

use_data(
  obtn_choropleth_colors,
  overwrite = TRUE
)


# Beep --------------------------------------------------------------------

beepr::beep()