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 color scale shows the SNAP Coverage of the Poverty Population, which I calculate by dividing the Number of People receiving SNAP benefits (from the USDA) by the Estimated Number of People living in Poverty (from the SAIPE dataset at Census.gov). Note that the color scale is divided into equal 25 point increments from a minimum of 25% to a maximum of 150%.

• Red is a rate between 25% and 50%
• Orange is a rate between 50% and 75%
• Yellow is a rate between 75% and 100%
• Light green is a rate between 100% and 125%
• Dark green is a rate between 125% and 150%

Hover

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

1. The percentage of the county's population living in poverty
2. The percentage of the county's population receiving SNAP benefits
3. The SNAP Coverage of the Poverty Population
4. A historical trend chart showing the comparison of #1 and #2 from 2003 to 2023

Highlight

To find any county in the U.S., start typing the county's name in the "Highlight County" box.

Filter

Last, there are three filters you can use.

To see Hawaii or Alaska, use the "Continental U.S. / Alaska / Hawaii" dropdown filter.

To change the visualization to display SNAP Coverage of the Poverty Population rates for different years, use the "Select Year" slider filter.

To zoom in on one state, use the "Choose State" 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 ."

Join poverty and SNAP data

1. Join the SAIPE poverty DB with the SNAP DB on "County FIPS Code," "State FIPS Code," 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. Make sure "5 digit FIPS code" is a geographic County data type
3. Drag Longitude to columns and Latitude to rows
4. Drag "5 digit FIPS code" to detail card
5. Drag "SNAP Percent of Poverty Population" to the color card. Choose your color scheme. I used temperature diverging.
6. Drag "Year" to the Filter pane. Make sure it is discrete data type (not continuous)
7. Create a group using "State Name" with the 48 contiguous states grouped. Add this group to the Filter pane.
8. Create a new worksheet. Drag "Year" to columns (make sure it is continuous data type). Drag "Poverty Percent" to Rows. Change chart type to line chart. Drag "SNAP Percent" over the y-axis to add to chart. Format as you wish.
9. Go back to the heat map. Click on the tooltip card. Add the data and text, and also the new historical trend chart created in Step 8.
10. Add some flair! Add your logo, change color scheme... make it yours!