Skip to main content

Analyzing United States County Data Trends with SandDance for Data Storytelling

Headshot of article author Jeff Lumpkin

What do you get when you combine a passion for data and Microsoft Power BI with an interest in U.S. politics, social economic and cultural trends? You receive compelling reports covering election data, obesity and drug overdose rates, along with income per capita levels within the United States over the past 10+ years.

Passion + Data = Insight Discovery

My name is Jeff Lumpkin and I’m a Program Manager with Power BI. I’m also an avid follower of political elections, economic trends, societal developments, etc. Driven by this interest, I have collected over 100 columns of data about the U.S., going down to the county level. This data comes from a wide variety of public sources, such as the U.S. Census, Center for Disease Control and the Bureau of Labor Statistics. As I built out the dataset, I began to collect political data as well, and now have data from the past 3 presidential elections: 2008, 2012 and 2016. Currently, I have a table with over 100 columns of data for each of the 3,100+ counties across the country.

Taken in aggregate, this data has allowed for some very interesting analyses, which includes obesity rates across time, deaths from drug overdoses from 1999 to 2014, the relationship between education and per capita income/poverty rate, as well as some very interesting observations about the 2016 presidential election.

A Deeper Look Through SandDance

Using Microsoft SandDance, a Power BI custom visual that offers rich modeling, interactivity, and animated transitions, I created visualizations and trend analysis across the U.S. counties. I chose SandDance as I felt it was the best medium to spin through large amounts of data very rapidly. I’ve found SandDance to be particularly useful when there are many different entities (counties) and many different metrics (columns). One of the most interesting explorations was to compare election results across U.S. counties for the past 3 elections.

  1. County Election Trends from 2008 to 2016
    I analyzed county election results from 2008 to 2016, however my main focus was on the last two presidential elections in 2012 and 2016. In the 2016 election over 200 counties shifted from Democrat to Republican. The visual to the left is from the 2012 election and the darker blue color represents the higher the percentage of Democratic voters whereas the dark red color represents Republican voters. The visual to the right is the same data analysis for the 2016 election. As you can see from this visual, the center of the country became significantly more Republican in the 2016 election.
    image
  2. Drug Fatality Results from 1999 to 2014
    I then looked at the county data from the Center for Disease Control to analyze drug fatality rates. The visual on the left is from 1999, and shows that the highest fatality rates were centered in West Virginia and Kentucky (dark blue counties). By 2014, on the right shows that drug related fatality rates had spread almost everywhere across the country.
    image
  3. Obesity Results 2004 to 2013
    Next, I analyzed the obesity rates across U.S. counties from 2004 to 2013. Looking first at the light to dark blue in the visual to the left, the 2004 obesity rates were focused on the deep south primarily in Alabama, Mississippi, Louisiana, and North and South Carolina. By 2013 as you can see in the graph to the right, the higher concentration of blue representing obesity rates had spread to a large percentage of the country.
    image
  4. College Graduation Correlation to Income and Poverty Levels
    Finally, I analyzed the relationship between education and income levels. In the first graph below to the left, the correlation of higher income with education is clearly outlined with the lighter blue dots in the graph. Not surprisingly, higher college graduation rates equate to higher income per capita. In the graph on the right, the counties are colored by the percentage of firms in a county that are owned by women. Very clearly, this shows a strong correlation between higher income and a greater percentage of firms owned by women – the conclusion is that the more that women can be integrated into an economy, the more prosperous that economy is.
    image

 

Your Turn to Play!

Enclosed below are the data sets I used to create this analysis, as well as my report. I encourage you to explore the data with SandDance and Power BI to make your own discoveries. If you haven’t already done so, download SandDance, followed by the Excel and PBIX files. Once you’ve had a chance to play with the data, please comment below with your own insights! I’m going to continue adding new columns to this data set to make it even richer. If you have data you’d like to contribute, that would be very welcome.

For more information and to obtain the data see additional resources below: