Welcome back to my Dashboard Week at the Data School blog series. For day 4, we were challenged to web scrape this text file housing alcohol consumption statistics across all states in the U.S. The data comes from the latest consumption surveillance report by the National Institute on Alcohol Abuse and Alcoholism. The data spans all states from 1970 to 2022, detailing population metrics and consumption metrics for 3 major alcoholic beverages (spirits, beer, and wine).
Approach
Initially, I thought this would be straightforward. I started with the web scraping in Alteryx, did some data cleaning, then chucked the clean data into Power Query for my dashboard. Unfortunately, I later found myself getting stuck trying to come up with a good story. I initially tried to understand whether some states relied more heavily on alcohol during Covid-19. However, I frequently experienced issues with DAX calculations and mental blocks concerning how I would frame this type of story. Because of this, I pivoted my focus, supplementing my data with violent crime rates from the FBI Crime Data Explorer. This data worked well with the alcohol consumption dataset since it was also organised by year and state. From here, I connected these tables in my data model with a state table.
Insights
Exploring the connection between alcohol consumption and violent crime was still tricky. The data showed no clear correlation between alcohol consumption and violent crime rates by state. This was certainly surprising and even a bit counter-intuitive. I even had to do some research simply confirming that the link between alcohol consumption and violent crime wasn’t just a fictional bias I somehow made up during my lifetime. For reference, the link is well researched and very real (Sontate et al., 2021). Upon reflection, I believe the contradiction between my dashboard and this research demonstrates the complexity of violent crime. Whilst alcohol consumption may influence an individual’s propensity to commit violent crimes, complex socioeconomic factors will also play a role. This would explain vastly differing crime rates between states with similar alcohol consumption.
The above scatterplot is one of my dashboard’s main charts. As can be seen, District of Columbia (red) and New Hampshire (green) both have high alcohol consumption but significantly different crime rates. This likely has to do with many factors, but most prominently:
- That there is no sales tax and extremely low liquor tax in New Hampshire, and
- District of Columbia’s complicated legal system of overlapping agencies and sociopolitical tensions.
Hence, my dashboard’s final analysis finishes somewhat open-ended, emphasising that violent crime is a complex issue but alcohol’s influence should still not be understated.
Thanks for reading. Feel free to check out my dashboard on NovyPro. If you missed the last blog and want to keep up-to-date with Dashboard Week, check out my blog for Day 3 here.