Loading and Tranformation

Introduction

This analysis uses the U.S. Census Bureau’s 2024 American Community Survey 5-Year Estimates, Table S2704: Public Health Insurance Coverage by Type and Selected Characteristics for New York’s 15th Congressional District. The dataset was selected because upcoming Medicaid eligibility changes make it useful to examine public health insurance coverage among working-age residents in the district.

Loading and Inspecting the Data

The original ACS dataset is loaded directly from the GitHub repository to ensure that the analysis is reproducible. The dimensions, column names, and initial observations are reviewed before transforming the data.

library(readr)
library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(stringr)

medicaid_raw <-read_csv("https://github.com/jmald1987/DATA607_Loading_and_Transformation/raw/refs/heads/main/ACSST5Y2024.S2704-2026-09-07T020103.csv"
)

## Rows: 32 Columns: 7

## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (6): Label (Grouping), Congressional District 15 (119th Congress), New Y...
## num (1): Congressional District 15 (119th Congress), New York!!Public Covera...
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

medicaid_raw

Data Transformation

The original ACS table contains several columns, including margins of error and lengthy Census column names. The dataset is transformed by selecting the variables relevant to this analysis, assigning more meaningful column names, and converting the selected values into appropriate numeric formats.

medicaid_clean <- medicaid_raw |> rename(
    population_group = `Label (Grouping)`,
    total_population = `Congressional District 15 (119th Congress), New York!!Total!!Estimate`,
    public_coverage = `Congressional District 15 (119th Congress), New York!!Public Coverage!!Estimate`,
    public_coverage_percent = `Congressional District 15 (119th Congress), New York!!Percent Public Coverage!!Estimate`
  )

medicaid_clean <- medicaid_clean |>
  select(
    population_group,
    total_population,
    public_coverage,
    public_coverage_percent
  )

medicaid_clean <- medicaid_clean |>
  mutate(
    population_group = str_squish(population_group)
  )

medicaid_clean <- medicaid_clean |>
  mutate(
    total_population = parse_number(as.character(total_population)),
    public_coverage = parse_number(as.character(public_coverage)),
    public_coverage_percent = parse_number(as.character(public_coverage_percent))
  )

## Warning: There was 1 warning in `mutate()`.
## ℹ In argument: `total_population =
##   parse_number(as.character(total_population))`.
## Caused by warning:
## ! 7 parsing failures.
## row col expected actual
##   3  -- a number    (X)
##   7  -- a number    (X)
##  11  -- a number    (X)
##  29  -- a number    (X)
##  30  -- a number    (X)
## ... ... ........ ......
## See problems(...) for more details.

medicaid_clean

Medicaid Coverage by Age Group

The transformed data is further reduced to examine Medicaid or means-tested public coverage across age groups. Particular attention is given to residents ages 19 to 64 because this working-age population is relevant to the upcoming Medicaid eligibility changes.

medicaid_age <- medicaid_clean |>
  slice(8:10)

medicaid_age <- medicaid_age |>
  mutate(
    population_group = recode(
      population_group,
      "Under 19" = "Under 19",
      "19 to 64 years" = "19-64",
      "65 years and over" = "65+"
    )
  )
medicaid_age

Conclusion

The transformed data shows differences in Medicaid or means-tested public coverage across age groups in New York’s 15th Congressional District. Approximately 49% of residents ages 19 to 64 have Medicaid or means-tested public coverage, indicating that changes to Medicaid eligibility may be particularly relevant to this district.

This analysis does not determine how many residents will actually be affected by the upcoming requirements. Future analysis could incorporate employment, income, disability, exemptions, and other eligibility characteristics to better identify populations that may be vulnerable to changes in Medicaid coverage.