Loading in Data

mi_2023 <- read.csv("SAIPE_MI_2023.csv")
locations <- read.csv("../../location_countyNames copy.csv") |>
  select(Location, LocationId)

Cleaning Data

median_income_2023 <- mi_2023 |>
  filter(Name != "United States") |>
  select(Name, Year, Median.Household.Income.) |>
  mutate(Name = sub(" County", "", Name)) |>
  mutate(Median.Household.Income. = as.numeric(gsub(",", "", Median.Household.Income.))) |>
  mutate(LocationType = ifelse(Name == "Texas", "State", "County")) |>
  rename(Location = Name)

with_ids_2023 <- left_join(median_income_2023, locations, by = "Location")
final_2023_mi <- with_ids_2023 |>
  select(LocationId, Location, Median.Household.Income., LocationType, Year) |>
  mutate(DataFormat = "Currency") |>
  rename(Data = Median.Household.Income., 
         TimeFrame = Year) |>
  arrange(desc(LocationType), Location)

EXPORT

write.csv(file = "CLEANED_2.3_MedianIncome_2023.csv", final_2023_mi)