CleaningCPI

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.2     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.3     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(janitor)

Attaching package: 'janitor'

The following objects are masked from 'package:stats':

    chisq.test, fisher.test

library(readxl)

historical_cpi <- read_xlsx("data-raw/historical-cpi-u-202502.xlsx", 
                            skip = 3, 
                            col_names = TRUE,
                            col_types = c("guess", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric", "numeric"))

Warning: Coercing text to numeric in B6 / R6C2: '1913'

Warning: Coercing text to numeric in B7 / R7C2: '1914'

Warning: Coercing text to numeric in B8 / R8C2: '1915'

Warning: Coercing text to numeric in B9 / R9C2: '1916'

Warning: Coercing text to numeric in B10 / R10C2: '1917'

Warning: Coercing text to numeric in B11 / R11C2: '1918'

Warning: Coercing text to numeric in B12 / R12C2: '1919'

Warning: Coercing text to numeric in B13 / R13C2: '1920'

Warning: Coercing text to numeric in B14 / R14C2: '1921'

Warning: Coercing text to numeric in B15 / R15C2: '1922'

Warning: Coercing text to numeric in B16 / R16C2: '1923'

Warning: Coercing text to numeric in B17 / R17C2: '1924'

Warning: Coercing text to numeric in B18 / R18C2: '1925'

Warning: Coercing text to numeric in B19 / R19C2: '1926'

Warning: Coercing text to numeric in B20 / R20C2: '1927'

Warning: Coercing text to numeric in B21 / R21C2: '1928'

Warning: Coercing text to numeric in B22 / R22C2: '1929'

Warning: Coercing text to numeric in B23 / R23C2: '1930'

Warning: Coercing text to numeric in B24 / R24C2: '1931'

Warning: Coercing text to numeric in B25 / R25C2: '1932'

Warning: Coercing text to numeric in B26 / R26C2: '1933'

Warning: Coercing text to numeric in B27 / R27C2: '1934'

Warning: Coercing text to numeric in B28 / R28C2: '1935'

Warning: Coercing text to numeric in B29 / R29C2: '1936'

Warning: Coercing text to numeric in B30 / R30C2: '1937'

Warning: Coercing text to numeric in B31 / R31C2: '1938'

Warning: Coercing text to numeric in B32 / R32C2: '1939'

Warning: Coercing text to numeric in B33 / R33C2: '1940'

Warning: Coercing text to numeric in B34 / R34C2: '1941'

Warning: Coercing text to numeric in B35 / R35C2: '1942'

Warning: Coercing text to numeric in B36 / R36C2: '1943'

Warning: Coercing text to numeric in B37 / R37C2: '1944'

Warning: Coercing text to numeric in B38 / R38C2: '1945'

Warning: Coercing text to numeric in B39 / R39C2: '1946'

Warning: Coercing text to numeric in B40 / R40C2: '1947'

Warning: Coercing text to numeric in B41 / R41C2: '1948'

Warning: Coercing text to numeric in B42 / R42C2: '1949'

Warning: Coercing text to numeric in B43 / R43C2: '1950'

Warning: Coercing text to numeric in B44 / R44C2: '1951'

Warning: Coercing text to numeric in B45 / R45C2: '1952'

Warning: Coercing text to numeric in B46 / R46C2: '1953'

Warning: Coercing text to numeric in B47 / R47C2: '1954'

Warning: Coercing text to numeric in B48 / R48C2: '1955'

Warning: Coercing text to numeric in B49 / R49C2: '1956'

Warning: Coercing text to numeric in B50 / R50C2: '1957'

Warning: Coercing text to numeric in B51 / R51C2: '1958'

Warning: Coercing text to numeric in B52 / R52C2: '1959'

Warning: Coercing text to numeric in B53 / R53C2: '1960'

Warning: Coercing text to numeric in B54 / R54C2: '1961'

Warning: Coercing text to numeric in B55 / R55C2: '1962'

Warning: Coercing text to numeric in B56 / R56C2: '1963'

Warning: Coercing text to numeric in B57 / R57C2: '1964'

Warning: Coercing text to numeric in B58 / R58C2: '1965'

Warning: Coercing text to numeric in B59 / R59C2: '1966'

Warning: Coercing text to numeric in B60 / R60C2: '1967'

Warning: Coercing text to numeric in B61 / R61C2: '1968'

Warning: Coercing text to numeric in B62 / R62C2: '1969'

Warning: Coercing text to numeric in B63 / R63C2: '1970'

Warning: Coercing text to numeric in B64 / R64C2: '1971'

Warning: Coercing text to numeric in B65 / R65C2: '1972'

Warning: Coercing text to numeric in B66 / R66C2: '1973'

Warning: Coercing text to numeric in B67 / R67C2: '1974'

Warning: Coercing text to numeric in B68 / R68C2: '1975'

Warning: Coercing text to numeric in B69 / R69C2: '1976'

Warning: Coercing text to numeric in B70 / R70C2: '1977'

Warning: Coercing text to numeric in B71 / R71C2: '1978'

Warning: Coercing text to numeric in B72 / R72C2: '1979'

Warning: Coercing text to numeric in B73 / R73C2: '1980'

Warning: Coercing text to numeric in B74 / R74C2: '1981'

Warning: Coercing text to numeric in B75 / R75C2: '1982'

Warning: Coercing text to numeric in B76 / R76C2: '1983'

Warning: Coercing text to numeric in B77 / R77C2: '1984'

Warning: Coercing text to numeric in B78 / R78C2: '1985'

Warning: Coercing text to numeric in B79 / R79C2: '1986'

Warning: Coercing text to numeric in B80 / R80C2: '1987'

Warning: Coercing text to numeric in B81 / R81C2: '1988'

Warning: Coercing text to numeric in B82 / R82C2: '1989'

Warning: Coercing text to numeric in B83 / R83C2: '1990'

Warning: Coercing text to numeric in B84 / R84C2: '1991'

Warning: Coercing text to numeric in B85 / R85C2: '1992'

Warning: Coercing text to numeric in B86 / R86C2: '1993'

Warning: Coercing text to numeric in B87 / R87C2: '1994'

Warning: Coercing text to numeric in B88 / R88C2: '1995'

Warning: Coercing text to numeric in B89 / R89C2: '1996'

Warning: Coercing text to numeric in B90 / R90C2: '1997'

Warning: Coercing text to numeric in B91 / R91C2: '1998'

Warning: Coercing text to numeric in B92 / R92C2: '1999'

Warning: Coercing text to numeric in B93 / R93C2: '2000'

Warning: Coercing text to numeric in B94 / R94C2: '2001'

Warning: Coercing text to numeric in B95 / R95C2: '2002'

Warning: Coercing text to numeric in B96 / R96C2: '2003'

Warning: Coercing text to numeric in B97 / R97C2: '2004'

Warning: Coercing text to numeric in B98 / R98C2: '2005'

Warning: Coercing text to numeric in B99 / R99C2: '2006'

Warning: Coercing text to numeric in B100 / R100C2: '2007'

Warning: Coercing text to numeric in B101 / R101C2: '2008'

Warning: Coercing text to numeric in B102 / R102C2: '2009'

Warning: Coercing text to numeric in B103 / R103C2: '2010'

Warning: Coercing text to numeric in B104 / R104C2: '2011'

Warning: Coercing text to numeric in B105 / R105C2: '2012'

Warning: Coercing text to numeric in B106 / R106C2: '2013'

Warning: Coercing text to numeric in B107 / R107C2: '2014'

Warning: Coercing text to numeric in B108 / R108C2: '2015'

Warning: Coercing text to numeric in B109 / R109C2: '2016'

Warning: Coercing text to numeric in B110 / R110C2: '2017'

Warning: Coercing text to numeric in B111 / R111C2: '2018'

Warning: Coercing text to numeric in B112 / R112C2: '2019'

Warning: Coercing text to numeric in B113 / R113C2: '2020'

Warning: Coercing text to numeric in B114 / R114C2: '2021'

Warning: Coercing text to numeric in B115 / R115C2: '2022'

Warning: Coercing text to numeric in B116 / R116C2: '2023'

Warning: Coercing text to numeric in B117 / R117C2: '2024'

Warning: Coercing text to numeric in B118 / R118C2: '2025'

Warning: Expecting numeric in E118 / R118C5: got '–'

Warning: Expecting numeric in F118 / R118C6: got '–'

Warning: Expecting numeric in G118 / R118C7: got '–'

Warning: Expecting numeric in H118 / R118C8: got '–'

Warning: Expecting numeric in I118 / R118C9: got '–'

Warning: Expecting numeric in J118 / R118C10: got '–'

Warning: Expecting numeric in K118 / R118C11: got '–'

Warning: Expecting numeric in L118 / R118C12: got '–'

Warning: Expecting numeric in M118 / R118C13: got '–'

Warning: Expecting numeric in N118 / R118C14: got '–'

historical_cpi

historical_cpi_clean <- historical_cpi |> 
  clean_names() |> 
  select(-indent_level) |> 
  drop_na(year)

historical_cpi_clean

recent_cpi <- historical_cpi_clean |> filter(year > 2013)
recent_cpi

recent_cpi_pivoted <- recent_cpi |> pivot_longer(!year, names_to = "month", values_to = "cpi")

recent_cpi_pivoted

cpi_cleaned <- recent_cpi_pivoted |> mutate(
  year_month = my(paste(month, year))
) |> select(year_month,
            cpi,
            -year,
            -month)

cpi_cleaned

cpi_cleaned |> write_rds("data-processed/cpi_201401_202502")

CPI for Texas

There is not data from the government on cost of living or consumer price index by county in Texas. But here’s the statewide consumer price index.

ggplot(cpi_cleaned, aes(x = year_month, y = cpi)) +
  geom_line()

Warning: Removed 10 rows containing missing values (`geom_line()`).

alice <- read_xlsx("data-raw/2024 ALICE - Texas Data Sheet.xlsx",
                   sheet = "County 2010-2022") |> clean_names()

alice 

household_budget <- alice |> select(
  -geo_id2,
  -geo_display_label,
  -state_abbr,
  -source_american_community_survey
)

household_budget

household_budget_simple <- household_budget |> 
  mutate(county = paste(toupper(county), "COUNTY"),
         end_year = as.character(year)) |> 
  select(end_year, county, alice_threshold_hh_under_65)

household_budget_simple

household_budget_simple |> write_rds("data-processed/alice_household_budget.rds")