This post includes an interactive data visualization, which is best viewed on a computer or tablet. However, if you must use your phone, at least turn it landscape.

Observe

First, just observe the visualization.

The y-axis is the SNAP Coverage of the Poverty Population while the x-axis is the state.

Each dot represents a county within that state.

The color of the dot corresponds to the state government trifecta party each year.

The size of the dot corresponds to the number of people living in poverty.

The dividing line set at 100% SNAP Coverage of the Poverty Population. Counties falling below this line (in red shading) had less people receiving SNAP than are living in poverty. Counties above this line (green shading) had more people receiving SNAP than are living in poverty.

Hover

Next, hover over any dot to bring up a tooltip showing several statistics on SNAP and poverty in that county. The tooltip will show:

1. The trifecta party
2. The SNAP Coverage of the Poverty Population
3. The estimated number of people living in poverty
4. People in poverty as a percent of total population
5. The average people receiving SNAP benefits over the year
6. The percentage of the state's population receiving SNAP benefits

Highlight

Start typing a county name into the "Highlight County" box to highlight a county in the visualization.

You can also click on the colors in the trifecta party color legend to highlight the counties/states in one trifecta party in the visualization.

Filter

Change the year displayed on the visualization by using the "Select Year" filter.

Prepare the SNAP data

1. Download county-level SNAP data from the SAIPE Model Input Data. Download the file named, "County SNAP Benefits Data.xls."
2. Note that county-level SNAP beneficiary counts are for the month of July in each year. I have used this one month as a proxy for the year in constructing this visualization. This is one limitation of the county level data analysis, as ideally I would average all months as I was able to do for the state data analysis. But county-level data for each month is not available, so this was the best that could be done. To better understand how much error this could add, I analyzed 20 years of state data, comparing July beneficiary counts to annual average beneficiary counts. I found the average absolute error across all 50 states and 20 years (1,050 data points) to be 1.3% and the median absolute error to be 0.6%. To get a sense of the upper end of the absolute error, I also calculated the 95% percentile and found it to be 4.7%. Based on this analysis, July beneficiaries appear to be a good proxy for the annual average beneficiaries, at least at the state level.
3. Given the analysis in Step 2, replace the header in the raw data file (which has "July <Year>") with just <Year>. For example, "July 2022" would be replaced with "2022."
4. Remove all numerical data fields from the file except "Participation Persons," as this is the only field we will use to create this visualization.
5. Pivot the data table on Year to end up with five data fields: "Year," "State FIPS Code," "County FIPS Code," "County Name," and, "People receiving SNAP."

Prepare the SAIPE poverty data

1. Download the SAIPE State and County Estimates. This link will get you to the 2024 page. When on the page, download the file called, "US and All States and Counties.xls."
2. Navigate the SAIPE datasets to get all years back to 2003. You can also use an API to save time.
3. Clean-up all files to remove blank rows and ensure headers are the same.
4. Calculate the field "Year" by extracting the two-digit year from the middle of the File Path field. Make sure it is in date format.
5. Remove all data with "County FIPS Code" = "000" to remove the state-level data
6. Remove extra numerical data fields that are not needed. The only fields I kept were, "Poverty Estimate, All Ages" and "Poverty Percent ."

Create the trifecta status data table

1. Create a spreadsheet with all 50 states in the rows and years in the columns
2. Go to Ballotpedia. Scroll down to the "Trifecta Status by State (1990-Present)" section. If the year has all "R" or all "D" it has a trifecta. Add this to the spreadsheet in the correct year/state cell. If it is mixed, add "Divided" to the correct year/state cell. Build out the entire data table manually this way. NOTE: I tried to use AI to do this for me and it continually returned many errors. The only way I found to guarantee it's correct is to do it manually.
3. Pivot the table to create a DB with three fields - Year, State, and Trifecta Status

Join poverty and SNAP data

1. Join the SAIPE poverty DB with the SNAP DB on "County FIPS Code," "State FIPS Code," and "Year."

Join Poverty/SNAP data with trifecta data

1. Join the combined poverty and SNAP database with the pivoted trifecta status data table on "State Name" and "Year."

Finalize database

1. Create a calculated field called "5 digit FIPS code which is a concatenation of "State FIPS code" and "County FIPS code."
2. Create a calculated field called "SNAP Coverage of Poverty Population" by dividing "SNAP Participation Persons" by "Poverty Estimate, All Ages"
3. Create a calculated field called "SNAP Percent of Population" and set equal to, "SNAP Participation Persons" / ("Poverty Estimate, All Ages" / "Poverty Percent")
4. Remove any other extraneous fields to keep the final database nice and tidy and then output the database

Create Tableau visualization

I'm assuming you know the basics of Tableau in these instructions

1. Connect Tableau desktop to the finished database
2. Drag "State" onto the Column field and "SNAP Percent of the Poverty Population" onto the Row field.
3. Drag "Trifecta Status" onto the color card
4. Drag "5 digit FIPS code" onto the detail card
5. Drag "Poverty Estimate, All Ages" onto the size card
6. Drag all the fields you would like to display on hover onto the tooltip card. Format the tooltip with text, etc. by clicking on the tooltip card.
7. Drag "Year" onto the Filters tray
8. Add some flair! Add your logo, change color scheme... make it yours!