Hi everyone! My name is Bruna Guglielmi, and I’m part of Cohort 26 at the Data School Australia. This week marks our 14th week of training, and we’re diving into Dashboard Week, a challenging week where we create a dashboard each day and document the entire process.
Today, on Day 1, our dataset focused on U.S. boat accidents, a complex topic with data spanning multiple years and tables. It required quite a bit of cleaning and wrangling before we could even start visualizing!
Diving Into the Data
This dataset came with four different tables, separated into three date ranges: 2009-2013, 2014-2022, and 2023. Right off the bat, I noticed some columns were either named differently across tables or simply didn’t exist in some years, so getting a union to work smoothly took some attention. I used Alteryx to handle this cleanup, renaming columns and standardizing fields across tables. (An image of the Alteryx workflow here would help show this process visually.)
To keep everything in one place, I added a row ID to each table. This allowed me to handle some tricky fields without creating additional tables for causes, events, and other detailed columns. There were about 4 or 5 columns that dealt with the same subject but in varying levels of detail—like “Accident Cause 1” through “Accident Cause 4.” So, adding a unique row ID let me avoid the hassle of splitting these into separate tables and kept the data structure.
Building the Foundation in Tableau
Once cleaned up, I imported the dataset into Tableau and set up relationships using a unique accident identifier called BARDID, which was consistent across all tables. This key connected all the accident details seamlessly and set up a good base for analyzing everything in one place.
With the foundational structure in place, I started sketching out the key metrics I wanted to display: accident overviews, injury counts, death statistics, and the number of vessels involved in each accident. My goal was to make this dashboard as interactive as possible, so users could explore seasonal trends, accident types, and their impact (deaths and injuries) without needing multiple views.
Key Insights and Dashboard Availability
Since California and Oregon didn’t provide data, all insights exclude these states. There are also a few states with limited data past 2013, so users should keep this in mind when exploring trends over time. Another limitation is that we lack a unique vessel ID, so the vessel count might have duplicates if a single boat was involved in multiple accidents. This just means the “vessel number” represents instances of boats in different accidents, not unique vessels.
The final interactive dashboard is on Tableau Public, where you can check out all the insights and explore accident trends by time, season, and type.
Thanks for reading, and I hope you enjoy the final dashboard!
