Project Overview
Our project analyzed a Connecticut statewide motorcycle crash data set covering the years 2015–2024.
The data set includes:
- Date and time
- Geographic coordinates
- Crash severity
- Weather conditions
- Roadway conditions
- Day of week
This level of detail allowed us to explore temporal trends, severity patterns, behavioral factors, and spatial clustering throughout the state.
When Did Most Motorcycle Crashes Happen?
(Time series using the crash date column)
Why This Question Is Interesting
Understanding when crashes occur most frequently helps transportation agencies anticipate high-risk periods, allocate patrol resources, and design targeted rider safety campaigns. Motorcycles are highly seasonal in many states, making timing a critical factor.
How We Used Excel
We imported the dataset into Excel, extracted the crash date column, and created a time-series line graph showing the total number of monthly crashes from 2015–2024. We used grouping, pivot tables, and smoothing to highlight seasonal cycles.
How We Developed the Visual
We tested weekly and monthly aggregations and determined that monthly counts created the cleanest visualization. We labeled summer peaks, minimized gridlines, and formatted the graph to emphasize year-to-year patterns.
What Is the Most Common Crash Severity?
(Bar chart using the CrashSeverity column)
Why This Question Is Interesting
Crash severity reveals how dangerous motorcycle collisions are in Connecticut. Identifying the most common severity category helps policymakers prioritize safety improvements, EMS preparedness, and roadway design.
How We Used Excel
We filtered the CrashSeverity column, counted occurrences of each category (Property Damage, Minor Injury, Serious Injury, Fatal), and built a bar chart to compare frequencies.
How We Developed the Visual
We chose a vertical bar chart for clarity, applied consistent colors for severity categories, and sorted bars from most to least frequent to avoid clutter.
What Is the Distribution of Crashes by Day of the Week?
(Histogram using Hour of the Day)
Why This Question Is Interesting
Motorcycle riding patterns often vary by time of day. It is important to know wether there are certain times of the day where riders are at an increased risk of being in a crash.
How We Used Excel
We grouped crashes by hour of the day, counted total crashes for each hour, and created a histogram showing the distribution.
How We Developed the Visual
We used evenly spaced bins for each day and applied a consistent color theme. We also ordered hours 1-24 to reflect the typical clock flow.
Is There a Geographic Pattern for Motorcycle Crashes?
(Scatter plot using Latitude × Longitude)
Why This Question Is Interesting
Mapping crashes helps identify hotspot areas where roadway design, intersections, or traffic volumes may increase crash risk.
How We Used Excel
We plotted every crash using latitude and longitude values, creating a two-dimensional scatter plot. Small point size helped reveal cluster density.
How We Developed the Visual
We removed gridlines, added state boundary outlines for context, and applied transparency so dense clusters were more visible.
Links to peers e-portfolios
https://nickcolosimo.quarto.pub/nicholas-colosimo/
https://srinidadi.quarto.pub/eport/
https://molly-lingenfelter27.quarto.pub/molly-lingenfelter/about.html