An interactive Dashboard created by Gaurab Kundu
A major issue that has an impact on communities all across the world is road safety. In this project I have created a Road Accident Analytics Dashboard, a powerful tool designed to analyse and visualize road accident data for the years 2021 and 2022. This Excel-based dashboard empowers stakeholders with valuable insights to make data-driven decisions and pave the way towards safer roads.
- Data Cleaning, Data Transformation, Data Analysis
- Filtering, Sorting and Conditional function.
- Pivot Tables and Pivot Chart
- Data Visulisation and Dashboard creation
Clients want to create a road accident dashboard for the years 2021 and 2022 so that they can have insight on following aspects-
- Primary KPI - Total Casualties and Total Accident values for Current Year and YoY growth
- Primary KPI's-Total Casualties by Accident Severity for Current Year and YoY growth
- Secondary KPIs - Total Casualties concerning vehicle type for the Current Year
- Monthly trend showing a comparison of casualties for the Current Year and the Previous Year
- Casualties by Road Type for the Current Year
- Current Year Casualties by Area/ Location & by Day/Night
- Total Casualties and Total Accidents by Location
The dataset can be found here
This stage began with getting to know the dataset and checking for any data quality issues.
- Checking NULL values, blanks and Errors
- Removing duplicates, and irrelevant columns.
- Made sure data is consistent and clean with respect to data type, data format and values used.
- Creating 2 new attributes 'Year' and 'Month' were added for logical and easy interpretation of dataset.
Created a "Data Analysis" sheet to summarize all the pivot table Data for a new user or developer or client to ease their work and gathered information. On this sheet nine pivot tables were created to summarise the data and help identify trends in the dataset focusing on relationship between number of casualties and many factors such as- casualty type, vehicle type, road type, location etc. Monthly trends were also analysed for the years 2021 and 2022.
Below is a snippet of the Data Analysis Sheet:
Finally, the dashboard was created by inserting and customizing the pivot charts of corresponding pivot table. For user friendly and interactive experience slicers and timelines were incorporated. Hyperlinks and connections were also added to necessary icons for seamless navigation.
The Excel File can be Downloaded from here
Below is a snippet of the final dashboard in Excel:
📈 Total Casualties Analysis: The dashboard reveals that a staggering 417,883 casualties occurred after accidents during the two-year period.
📅 Peak Months: Overall the number of casulaties were slightly higher in 2021 than 2022. Maximum Casualties happened in the month of October and November in both the Years and minimum casualties take place in the month of January and February.
🚗 Casualties by Vehicle Type: Car accidents accounted for the highest number of casualties, contributing to 79.8% of the total. On the other hand, casualties were minimal in accidents involving other vehicle types.
🩸 Casualties by accident severity Slight severity form the bulk of casualties 84.1% whereas there are only 1.7% Fatal severity casualties.
🛣️ Road Type Analysis: Maximum Casualties by Single Carriageway road type (310.1K) and Minimum by Slip road(5.1K).
🌧️ Casualties Distribution by Road Surface: Highest Distribution of total casualties on Dry road surfaces (67%).
🏙️ Casualties Relation by Area/Location Urban areas form the majority of casualties after an accident (61%).
☀️ Casualties Distribution by light condtion: 73% of casulties take place in daylight condtion.
- By comparing casualty trends between the current and previous years on a monthly basis, the dashboard identifies critical periods of October and November. The road safety measure by the traffic police and other stakeholders must be active and focused during these high-risk periods.
- Car drivers form bulk of the casualties so they must be targeted for awareness camps and strict monitoring, periodic check-ups on safe driving.
- Extra safety measures should be placed Single Carriageway roads and they must be upgraded to double lane wherever possible.
- Understanding casualty distribution based on different road surface conditions helps pinpoint areas where road maintenance and surface improvements are essential.
- Urban areas should be targeted for interventions to improve road safety in specific and during daytime.
This Road Accident Analytics Dashboard opens the door to data-driven decision-making, enabling stakeholders to implement evidence-based interventions that enhance road safety. It serves as a valuable tool for policymakers, traffic authorities, and safety advocates alike.