obtn: Oregon by the Numbers

# OREGON VOICES -----------------------------------------------------------



# ├ Overall Participation -------------------------------------------------

# obtn_oregon_voices_random <- read_excel("data-raw/Oregon Voices_County Distributions_6-17-22.xlsx", sheet = "6-17-22") %>%
#   clean_names() %>%
#   remove_empty() %>%
#   select(1:2) %>%
#   set_names("geography", "responses") %>%
#   mutate(type = "random")
#
# obtn_oregon_voices_engagement <-read_excel("data-raw/Oregon Voices_County Distributions_6-17-22.xlsx", sheet = "6-17-22") %>%
#   clean_names() %>%
#   remove_empty() %>%
#   select(3:4) %>%
#   set_names("geography", "responses") %>%
#   mutate(type = "engagement")
#
# obtn_oregon_voices_participation <- bind_rows(obtn_oregon_voices_random, obtn_oregon_voices_engagement) %>%
#   filter(geography != "Missing") %>%
#   filter(geography != "Total")
#
# use_data(obtn_oregon_voices_participation,
#          overwrite = TRUE)


# ├ Appreciate About Where They Live ----------------------------------------

# obtn_oregon_voices_appreciation <- read_excel("data-raw/Q5_Q3_Recode_Urban_Rural__group_1_rhh_data.xlsx") %>%
#   clean_names() %>%
#   select(-percent) %>%
#   set_names("response", "group", "group_total", "number") %>%
#   mutate(pct = number / group_total)
#
# use_data(obtn_oregon_voices_appreciation,
#          overwrite = TRUE)


# ├ Community Pride -------------------------------------------------------

# obtn_oregon_voices_community_pride <- read_excel("data-raw/Rural-Urban Comparison_6-18-22.xlsx",
#                                                  sheet = "Q14",
#                                                  range = "B2:E8") %>%
#   clean_names() %>%
#   select(-x4) %>%
#   set_names("response", "rural", "urban") %>%
#   filter(response != "Total") %>%
#   pivot_longer(cols = -response,
#                names_to = "group",
#                values_to = "pct") %>%
#   mutate(response = fct_inorder(response))
#
# use_data(obtn_oregon_voices_community_pride,
#          overwrite = TRUE)


# RURALITY ----------------------------------------------------------------


oregon_voices_rurality <- read_rds("data-raw/or_voices.rds") %>%
  select(County_Recode, Q3_recode) %>%
  set_names("geography", "rurality") %>%
  drop_na() %>%
  count(geography, rurality) %>%
  group_by(geography) %>%
  mutate(pct = n / sum(n)) %>%
  ungroup() %>%
  filter(rurality == "Rural")

use_data(oregon_voices_rurality ,
         overwrite = TRUE)



oregon_counties_rurality <- read_excel("data-raw/OBTN Rural Urban.xlsx",
                                       skip = 2) %>%
  set_names("geography", "geoid", "designation") %>%
  filter(str_length(geoid) == 5) %>%
  mutate(geography = str_remove(geography, " County, Oregon"))

obtn_counties_rurality <-
  obtn_boundaries_oregon_counties %>%
  left_join(oregon_counties_rurality)

use_data(obtn_counties_rurality,
         overwrite = TRUE)


oregon_census_tracts_rurality <-
  read_excel("data-raw/OBTN Rural Urban.xlsx",
             skip = 2) %>%
  set_names(c("geography", "geoid", "designation")) %>%
  select(-geography) %>%
  filter(str_length(geoid) == 11) %>%
  mutate(geoid = as.character(geoid))

oregon_census_tracts <- tracts(state = "OR",
                               year = obtn_year - 2) %>%
  clean_names() %>%
  select(geoid)

obtn_census_tracts_rurality <- oregon_census_tracts %>%
  left_join(oregon_census_tracts_rurality, by = "geoid")

use_data(obtn_census_tracts_rurality,
         overwrite = TRUE)

# Census blocks

# or_census_blocks <- blocks(state = "OR",
#                            year = obtn_year - 2)

# download.file("https://www2.census.gov/geo/tiger/TIGER_RD18/LAYER/TABBLOCK20/tl_rd22_41_tabblock20.zip",
#               destfile = "/Users/davidkeyes/Downloads/tl_rd22_41_tabblock20.zip")
#
# or_census_blocks_rurality <- read_sf("/Users/davidkeyes/Downloads/tl_rd22_41_tabblock20/tl_rd22_41_tabblock20.shp") %>%
#   clean_names() %>%
#   select(ur20) %>%
#   rename(urban_rural = ur20)
#
# use_data(or_census_blocks_rurality,
#          overwrite = TRUE)

or_voices_rurality <- read_rds("data-raw/or_voices.rds") %>%
  select(County_Recode, Q3_recode) %>%
  set_names(c("geography", "designation")) %>%
  count(geography, designation) %>%
  group_by(geography) %>%
  mutate(pct_rural = n / sum(n)) %>%
  ungroup() %>%
  filter(designation == "Rural") %>%
  select(geography, pct_rural)

obtn_or_voices_rurality <-
  obtn_boundaries_oregon_counties %>%
  left_join(or_voices_rurality)

use_data(obtn_or_voices_rurality,
         overwrite = TRUE)


# ├ Economic Mobility -------------------------------------------------------

# obtn_economic_mobility <-
#   read_excel(here(
#     "data-raw",
#     "Economic Mobility Data for Oregon Counties 2.10.20.xlsx"
#   ),
#   range = "A1:D37") %>%
#   clean_names() %>%
#   rename("geography" = "name") %>%
#   mutate(geography = str_remove(geography, " County, OR")) %>%
#   mutate(geography = str_trim(geography)) %>%
#   pivot_longer(-geography,
#                names_to = "family_income_percentile") %>%
#   mutate(
#     family_income_percentile = case_when(
#       str_detect(family_income_percentile, "75th") ~ "75th Percentile",
#       str_detect(family_income_percentile, "50th") ~ "50th Percentile",
#       str_detect(family_income_percentile, "25th") ~ "25th Percentile"
#     )
#   ) %>%
#   arrange(geography, family_income_percentile) %>%
#   # checks
#   verify(nrow(.) > 100) %>%
#   assert(is.numeric, value) %>%
#   assert(in_set(c(
#     obtn_oregon_counties, "Rural", "Urban", "Oregon"
#   )), geography) %>%
#   assert(in_set(c(
#     "25th Percentile", "50th Percentile", "75th Percentile"
#   )), family_income_percentile) %>%
#   assert_rows(col_concat, is_uniq, geography, family_income_percentile) %>%
#   assert(within_bounds(0, 1), value)
#
# use_data(obtn_economic_mobility,
#          overwrite = TRUE)


# OREGON POVERTY MEASURE --------------------------------------------------

obtn_data_orpm <-
  read_csv(here("data-raw/ChildPoverty_CovidProjections.csv")) %>%
  select(policy:qtr_childpov_meanpct) %>%
  set_names(c("policy", "quarter", "child_poverty_rate"))


use_data(
  obtn_data_orpm,
  overwrite = TRUE
)