Project 2: Data Tidying

US Census Population Data

Introduction

Anastasiia Gmyrina suggested looking into the US Census population data, specifically, the 10 states that saw the largest positive and negative population changes from 2020-2022. The raw data was wide and not formatted for analysis, so before exploring the relationships, we first tidyed the data.

Import Data

Import data from github hosted .csv and glimpse what we are working with.

url <- "https://raw.githubusercontent.com/jocslater-code/DATA607/refs/heads/main/Project2/census_top10_states_2022_untidy.csv"
df_raw <- read.csv(url)
head(df_raw)

  Rank Geographic.Area April.1..2020..Estimates.Base. July.1..2021 July.1..2022
1    1      California                       39538245     39142991     39029342
2    2           Texas                       29145428     29558864     30029572
3    3         Florida                       21538226     21828069     22244823
4    4        New York                       20201230     19857492     19677151
5    5    Pennsylvania                       13002689     13012059     12972008
6    6        Illinois                       12812545     12686469     12582032

Tidy Data

The data needs to be formatted for analysis. I first clean up the column names using the Janitor library, covert from wide to long, extract clear years, and reorder the columns.

df_tidy <- df_raw %>%
  # Janitor to clean up the column names
  clean_names() %>%
  
  # Convert to long format
  pivot_longer(
    cols = starts_with(c("april", "july")),
    names_to = "estimate_period",
    values_to = "population"
  ) %>%
  
  # Clean up the period column text and extract clean numeric years
  mutate(
    # Format labels cleanly (e.g., "July 1 2021" or "April 1 2020 Estimates Base")
    estimate_period = str_replace_all(estimate_period, "_", " ") %>% str_to_title(),
    
    # Optional: extract explicit numeric year into a separate column
    year = as.numeric(str_extract(estimate_period, "\\d{4}"))
  ) %>%
  
  # Reorder columns logically
  select(rank, geographic_area, estimate_period, year, population)

head(df_tidy)

# A tibble: 6 × 5
   rank geographic_area estimate_period              year population
  <int> <chr>           <chr>                       <dbl>      <int>
1     1 California      April 1 2020 Estimates Base  2020   39538245
2     1 California      July 1 2021                  2021   39142991
3     1 California      July 1 2022                  2022   39029342
4     2 Texas           April 1 2020 Estimates Base  2020   29145428
5     2 Texas           July 1 2021                  2021   29558864
6     2 Texas           July 1 2022                  2022   30029572

Analysis

# Reorder data frame so it is in descending order by 2020 population
df_plot <- df_tidy %>%
  mutate(
    # Reorder factor levels descending by pop_2020
    pop_2020 = population[year == 2020][match(geographic_area, geographic_area[year == 2020])],
    geographic_area = fct_reorder(geographic_area, pop_2020, .desc = TRUE)
  )

# Make the plot
ggplot(df_plot, aes(x = year, y = population / 1e6, color = geographic_area, group = geographic_area)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 2.5) +
  scale_x_continuous(breaks = c(2020, 2021, 2022)) +
  labs(
    title = "U.S. State Populations (2020–2022)",
    x = "Year",
    y = "Population (Millions)",
    color = "State"
  ) +
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", hjust = 0.5),
    legend.position = "right"
  )

To calculate the percent changes, first we make a new data frame with just the start and ending years. Then we can calculate absolute change, percent change, and add a new column for gained/lost population status.

# Summary dataframe with percent changes
pop_change_summary <- df_tidy %>%
  filter(year %in% c(2020, 2022)) %>%
  pivot_wider(
    id_cols = c(rank, geographic_area),
    names_from = year,
    names_prefix = "pop_",
    values_from = population
  ) %>%
  mutate(
    abs_change = pop_2022 - pop_2020,
    pct_change = (pop_2022 - pop_2020) / pop_2020,
  ) %>%
  arrange(desc(pct_change))

# 2. Pass directly into gt
table_gt <- pop_change_summary %>%
  gt() %>%
  tab_header(
    title = md("**U.S. State Population Change Summary (2020–2022)**")) %>%
  tab_spanner(label = "Population", columns = c(pop_2020, pop_2022)) %>%
  tab_spanner(label = "Net Change", columns = c(abs_change, pct_change)) %>%
  cols_label(
    rank = "Rank",
    geographic_area = "State",
    pop_2020 = "2020",
    pop_2022 = "2022 Estimate",
    abs_change = "Absolute",
    pct_change = "Percentage"
  ) %>%
  fmt_number(columns = c(pop_2020, pop_2022, abs_change), decimals = 0) %>%
  fmt_percent(columns = pct_change, decimals = 2) %>%
  cols_align(align = "left", columns = geographic_area) %>%
  # Conditional text
  tab_style(
    style = cell_text(color = "#1b5e20", weight = "bold"),
    locations = cells_body(columns = pct_change, rows = pct_change > 0)
  ) %>%
  tab_style(
    style = cell_text(color = "#b71c1c", weight = "bold"),
    locations = cells_body(columns = pct_change, rows = pct_change < 0)
  ) %>%
  tab_options(
    heading.title.font.size = px(18),
    column_labels.font.weight = "bold",
    table.border.top.color = "transparent",
    table.border.bottom.color = "black"
  )

table_gt

U.S. State Population Change Summary (2020–2022)
Rank	State	Population		Net Change
		2020	2022 Estimate	Absolute	Percentage
3	Florida	21,538,226	22,244,823	706,597	3.28%
2	Texas	29,145,428	30,029,572	884,144	3.03%
9	North Carolina	10,439,414	10,698,973	259,559	2.49%
8	Georgia	10,711,937	10,912,876	200,939	1.88%
5	Pennsylvania	13,002,689	12,972,008	−30,681	−0.24%
7	Ohio	11,799,374	11,756,058	−43,316	−0.37%
10	Michigan	10,077,325	10,034,113	−43,212	−0.43%
1	California	39,538,245	39,029,342	−508,903	−1.29%
6	Illinois	12,812,545	12,582,032	−230,513	−1.80%
4	New York	20,201,230	19,677,151	−524,079	−2.59%

Discussion and Next Steps

Florida saw the highest percent change in population (3.28%), but Texas had the largest absolute state population change (884,144). New York had the highest percent change (-2.59%) while California had the highest absolute change in population (-508,908).

Next steps could be to break down demographic changes these states to investigate if there is a certain group that is primarily responsible for the population change.