📊 Crash Reporting & Incident Data Analysis (2019–2023) Data Science Minor Project – Excel-Based Analytics
A comprehensive analysis of U.S. crash incidents reported between 2019 and 2023, performed using Microsoft Excel. This project focuses on understanding trends, causes, severity patterns, and other insights related to road safety and crash behavior.
📁 Project Files File Description project.xlsx Cleaned dataset, pivot tables, charts, and dashboard DATA SCIENCE MINOR PROJECT REPORT.docx Full project documentation
🌐 Dataset Source Crash Reporting – Incidents Data 📌 https://catalog.data.gov/dataset/crash-reporting-incidents-data
The dataset contains 30,000+ official crash records collected through ACRS (Automated Crash Reporting System) by police departments in Montgomery County, Maryland.
🛠️ Tools & Technologies Used
Microsoft Excel
Pivot Tables & Pivot Charts
COUNTIFS, SUMIFS, IF Statements
Conditional Formatting
Excel Dashboard with Slicers
Data Cleaning and Pre-processing Techniques
🧹 Data Preprocessing Steps
The dataset was cleaned and prepared using the following methods:
Removed blank rows and unnecessary columns
Converted date fields and extracted:
Year
Month
Day
Standardized numeric fields
Created derived fields for:
Month-wise analysis
Injury severity categories
Filtered dataset for 2019–2023
📈 Analysis & Insights 1️⃣ Yearly Crash Trends
Crashes increased gradually and peaked in 2022
Slight decline in 2023
2️⃣ Injury Severity Breakdown
Most cases involve minor injuries
Fatal crashes account for < 5%
3️⃣ Major Contributing Causes
Speeding
Distracted driving
Failure to give right-of-way
4️⃣ Monthly Accident Patterns
Peaks observed in June and December
5️⃣ Hit-and-Run Incidents
Approximately 12% of all crashes
📊 Visualizations
The Excel workbook includes:
📉 Line Chart – Total yearly crashes
📊 Bar Chart – Weather conditions vs crash count
🥧 Pie Chart – Hit-and-run percentage
📌 Interactive Dashboard with slicers for:
Year
Severity
Cause
Weather
🪄 Dashboard Preview
(Add a screenshot here after you upload one.)
📚 Key Learnings
Techniques to clean and standardize datasets
Using Excel functions for analytical tasks
Creating dynamic dashboards
Finding real-world insights from large datasets
🚀 Future Enhancements
Build advanced visualizations using Power BI
Apply Python (Pandas, NumPy, Matplotlib) for deeper EDA
Develop ML models to predict crash probability
Combine crash data with weather and traffic datasets
🙌 Acknowledgements
Lovely Professional University
Guide: Anchal Kaundal
Dataset: U.S. Government Open Data Portal (data.gov)