ROAD ACCIDENT ANALYSIS: MS EXCEL
Project Overview
The goal of this Excel project is to develop a comprehensive Road Accident Dashboard for the years 2021 and 2022. The aim is to offer meaningful insights to clients using a dataset comprising 21 fields and 307,000 records. The insights will be categorized into two types of Key Performance Indicators (KPIs): Primary KPIs and Secondary KPIs, providing a holistic view of the road accident data and its significant trends.
Primary KPIs involve:
- Aggregate casualties occurring post-accident.
- Total casualties and the percentage relative to accident severity, along with maximum casualties categorized by vehicle type.
Secondary KPIs involve:
- Total casualties categorized by vehicle type.
- Monthly trends comparing casualties between the Current Year and the Previous Year.
- Identification of maximum casualties by Road Type.
- Distribution of total casualties based on Road Surface.
- Examination of the relationship between casualties, Area/Location, and Day/Night conditions.
These KPIs will be addressed by considering all major stakeholders in order to effectively give comprehensive and meaningful information to the client. These stakeholders include:
- Ministry of Transport
- Road Transport Department
- Police Force
- Emergency Service Department
- Road Safety Corps
- Transport Operators
- Traffic Management Agencies
- Public
- Media
Developing the Comprehensive Road Accident Dashboard will provide the customer with valuable insights and trends in accident data spanning 2021 and 2022. These findings will enable informed decision-making, facilitate the implementation of impactful road safety measures, and ultimately contribute to the reduction of casualties on the road.
Project Framework
These are used to build the final Dashboard/Report:
- 307k with 21 fields of Data File .xlsx
- KPIs
- Monthly Trend
- Road Type
- Road Surface
- Road Light Type
- Data Analysis
- Dashboard
Step-by-step process for Data Analysis:
Data Gathering: In this phase, the project focused on defining objectives with stakeholders such as the Ministry of Transport, Road Transport Department, Police Force, and others. Key performance indicators (KPIs) were considered to align with project requirements and constraints. The dataset is a demo dataset acquired from Kaggle.com, https://tinyurl.com/4ad88cz
Data Cleaning: I’ve enhanced data accuracy by removing duplicates and added “Month” and “Year” columns derived from “Accident Date” for better temporal analysis in the dashboard.
Pivot Tables and Visualizations: Leveraging pivot tables as a foundation, I’ve designed KPIs, charts, and graphs for effective data communication in the dashboard, ensuring a user-friendly and informative experience.
Building the Dashboard: Integrating interactive elements, I’ve customized the layout for an engaging user experience, facilitating efficient data exploration, and supporting data-driven decision-making.
Buttons, Filters, and Slicers: Implemented interactive features like Buttons, Filters, and Slicers for user-friendly exploration. Filters based on accident date, urban and rural areas, and dynamic buttons enhance the dashboard’s interactivity and functionality.
Results:
- The most common type of vehicle involved in road accidents is a car.
- Casualties have declined in 2022 as compared to the previous year.
- The most common road type for road accidents is a single carriageway.
- The most common weather condition for road accidents is dry.
- The most common Area for road accident is Urban.
- The most common Light condition for road accident is Daylight.
Summary and Output Highlights:
Dashboard:
The project entailed the analysis of a dataset comprising more than 307,000 records, utilizing advanced features in Excel. Through thorough data cleaning and processing, meaningful insights were extracted. Trends and patterns were visually emphasized using Excel’s charts and visualizations. The outcomes were effectively communicated through an interactive dashboard and a detailed report. Stakeholders gained valuable insights, empowering informed decision-making and fostering improvements in road safety.
You can check out my project files here: https://github.com/raisinora21/Road-Accident-