With the Republican Party set on slashing Medicaid, I wanted to examine the data and determine who would be most affected. The Centers for Medicare & Medicaid Services (CMS) offer various datasets. I selected one that provided the share of Medicaid enrollees in managed care, which included enrollment numbers for all 50 states, the District of Columbia, and US territories.
I needed to clean the data to isolate state and total Medicaid enrollee columns. Using Python, specifically Jupyter Notebook and Pandas, I approached this task systematically. The Pandas library is particularly useful for handling structured data like tables in Excel or CSV files.
My process included:
1. Loading the data into a Pandas DataFrame
2. Creating a new DataFrame with only the State, Total Medicaid Enrollees, and Year columns
3. Converting the Total Medicaid Enrollees data from string to numeric format
4. Using value_counts() on the State column to check for duplicates
5. Keeping only the most recent data from each state
6. Creating a variable for US territories since I wanted only the 50 states and DC
7. Using regex to filter out territories and instances where state names contained numbers
With clean state and enrollment data in hand, I applied statistics to derive meaningful insights. If Medicaid were dismantled, what would be the immediate impacts? Emergency room visits are notably expensive, so I used CDC data indicating 42.7 ER visits per year per 100 people, along with UnitedHealthcare's estimate of $1,700 per ER visit. These figures allowed me to calculate how much each state would need to absorb in emergency room costs annually without Medicaid. I created a new column, “Estimated ER Cost Without Medicaid,” by multiplying each state's total enrollees by 0.427 and then by $1,700. After completing my analysis, I saved the data as a CSV file and pushed it to GitHub.
For visualization, I created a treemap in Power BI. After experimenting with different chart types, I found that bar charts and scatter plots didn't capture the eye or leave an impact like a treemap. The treemap's squares represent each state's Medicaid enrollment relative to other states. The dollar amount inside each square shows the annual emergency room costs that state would face if Medicaid were eliminated.
The figures are alarming and suggest serious cascading effects. Many individuals would become uninsured, facing substantial medical bills they likely couldn't afford, leading to debt and financial hardship. Hospitals, legally obligated to provide care regardless of ability to pay, would experience increased uncompensated care costs that would likely be shifted to paying patients through higher charges. This situation would also create enormous strain on state and local government resources.
As Congress deliberates Medicaid's future, it's evident that eliminating the program would negatively impact both those who rely on it and those who don't.
https://github.com/koller-m/medicaid-data