16th Biggest City per US State

library(tidyverse)
library(crosstalk)
library(DT)

# The path to the local data file
# The file is in a subfolder called "data"
file_path <- "data/sub-est2024.csv"

# Read the data from the CSV file
# The 'tryCatch' block will show a helpful message if the file is not found.
local_data <- tryCatch({
  # Explicitly set the SUMLEV column to be read as character to preserve leading zeros
  # Added locale to handle "invalid UTF-8" warnings by specifying the file encoding.
  read_csv(file_path, 
           col_types = cols(SUMLEV = col_character()),
           locale = locale(encoding = "latin1"))
}, error = function(e) {
  message("Error reading file: ", e$message)
  message("\nPlease make sure '", file_path, "' exists and is a valid CSV file.")
  # Return an empty tibble or NULL if the file can't be read
  tibble() 
})

 local_data <- local_data %>%
    mutate(
      sumlev_desc = case_when(
        SUMLEV == "040" ~ "State",
        SUMLEV == "050" ~ "County",
        SUMLEV == "061" ~ "Minor Civil Division",
        SUMLEV == "071" ~ "Minor Civil Division place part",
        SUMLEV == "157" ~ "County place part",
        SUMLEV == "162" ~ "Incorporated place",
        SUMLEV == "170" ~ "Consolidated city",
        SUMLEV == "172" ~ "Consolidated city -- place within consolidated city",
        TRUE ~ "Other" # A default for any values not in the key
      )
    )

  ranked_states <- local_data %>%
    filter(SUMLEV == "040") %>%
    arrange(desc(ESTIMATESBASE2020)) %>%
    mutate(Rank = row_number()) %>%
    select(Rank, STNAME, ESTIMATESBASE2020)

  # Assign the 16th ranked state name to a variable
  state_16 <- ranked_states$STNAME[16]

  # Display the table
  knitr::kable(
    ranked_states,
    caption = "State Population for 2020 Census, in Descending Order",
    col.names = c("Rank", "State Name", "2020 Population Base"),
    format.args = list(big.mark = ","))

  ranked_cities_by_state_all <- local_data %>%
    distinct(STNAME, NAME, ESTIMATESBASE2020, .keep_all = TRUE) %>%
    filter(SUMLEV %in% c( "162", "61", "50")) %>%
    group_by(STNAME) %>%
    arrange(desc(ESTIMATESBASE2020)) %>%
    mutate(Rank = row_number()) %>%
    filter(Rank == 16 ) %>%
    ungroup() %>%
    select(STNAME, NAME, ESTIMATESBASE2020) %>%
    arrange(STNAME)

    ranked_cities_by_state_all %>%
      mutate(Order = row_number()) %>%
  relocate(Order) %>%
      
    knitr::kable(
    caption = "16th Largest City or Place by 2020 Population Within Each State order alphabetically by State Name",
    col.names = c("Order", "State Name", "Place Name", "2020 Census Population"),
    format.args = list(big.mark = ",")
  )

    ranked_cities_by_state_by_pop <-ranked_cities_by_state_all %>% 
      arrange(desc(ESTIMATESBASE2020)) %>% mutate(Order = row_number()) %>%
  relocate(Order) 
    

    knitr::kable(ranked_cities_by_state_by_pop ,
    caption = "16th Largest City or Place by 2020 Census Population Within Each State ordered by Population Size",
    col.names = c("Order", "State Name", "Place Name", "2020 Census Population"),
    format.args = list(big.mark = ",")
  )

  ranked_cities_by_iowa <- local_data %>%
  filter(STNAME == "Iowa") %>%
filter(SUMLEV %in% c( "162", "61", "50")) %>%
    arrange(desc(POPESTIMATE2024)) %>%
    mutate(Rank = row_number()) %>%
    filter(Rank <= 25 ) %>%
   
    select(
      NAME,
      Rank,
      ESTIMATESBASE2020,
    
      POPESTIMATE2021,
      POPESTIMATE2022,
      POPESTIMATE2023,
      POPESTIMATE2024
    ) %>%
  arrange(Rank) %>%
  
    knitr::kable(
      caption = "Population Data for Iowa",
       col.names = c("Place Name", "Rank", "2020 Pop. Est.", "2021 Pop. Est.", "2022 Pop. Est.", "2023 Pop. Est.", "2024 Pop. Est."),
      format.args = list(big.mark = ",")
    )

 ranked_cities_by_iowa

local_data %>%
    filter(SUMLEV %in% c("162", "61", "50")) %>%
    group_by(STNAME) %>%
    summarise(Count = n(), .groups = 'drop') %>% 
    arrange(Count) %>%
    knitr::kable(
      caption = "Count of Selected Places per State", 
      col.names = c("State Name", "Selected Places")
    )