Skip to content

This is an Excel project on Road Accident Data Analysis in the form of an interactive Dashboard.

Notifications You must be signed in to change notification settings

GaurabKundu1/Road-Accident-Data-Analysis

Folders and files

NameName
Last commit message
Last commit date

Latest commit

ย 

History

12 Commits
ย 
ย 

Repository files navigation

Road Accident Data Analysis(Interactive Dashboard creation using Excel)

An interactive Dashboard created by Gaurab Kundu

Project Description

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.

Tools & Technology Used

Skills showcased in this Project

  • Data Cleaning, Data Transformation, Data Analysis
  • Filtering, Sorting and Conditional function.
  • Pivot Tables and Pivot Chart
  • Data Visulisation and Dashboard creation

Requirements and KPIs

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

Data used

The dataset can be found here

Data Cleaning and Transformation

This stage began with getting to know the dataset and checking for any data quality issues.

  1. Checking NULL values, blanks and Errors
  2. Removing duplicates, and irrelevant columns.
  3. Made sure data is consistent and clean with respect to data type, data format and values used.
  4. Creating 2 new attributes 'Year' and 'Month' were added for logical and easy interpretation of dataset.

Data Analysis

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:

Data Visualization

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:

Key Insights

๐Ÿ“ˆ 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.

Recommendations based on the Analysis

  1. 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.
  2. Car drivers form bulk of the casualties so they must be targeted for awareness camps and strict monitoring, periodic check-ups on safe driving.
  3. Extra safety measures should be placed Single Carriageway roads and they must be upgraded to double lane wherever possible.
  4. Understanding casualty distribution based on different road surface conditions helps pinpoint areas where road maintenance and surface improvements are essential.
  5. Urban areas should be targeted for interventions to improve road safety in specific and during daytime.

Conclusion

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.

About

This is an Excel project on Road Accident Data Analysis in the form of an interactive Dashboard.

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published