📌 Project Name: Road Accident Analysis
👥 Team Members: Amit Porwal(Self Created Project)
🎓 Mentored by: Ajay Kumar Gupta
🔧 skills required: Advance Excel, EDA, Dashboard, Power Query
📅 Project start Date: 28/07/2023
📅 Project end Date: 30/07/2023
Our mission is to create a comprehensive UK Road Accident Excel Dashboard with the aim of providing actionable insights into road safety. Through this project, we aim to analyze and visualize road accident data, enabling stakeholders to make informed decisions for improving road safety measures and reducing accidents. The achieved outcome will be a user-friendly and informative Excel dashboard that empowers users to explore accident data, identify trends, and take proactive steps towards a safer road network in the UK.
- Performed data cleaning tasks to enhance data quality and consistency.
- Addressed missing values, duplicates, and outliers in the scraped data to prevent skewed analysis.
- Calculated fields in Excel pivot tables allow you to create custom formulas and expressions using existing columns, making it possible to derive new insights or perform specific calculations based on data from columns like "light condition," "road type," or "road surface."
- Leveraged Excel's Text() function to extract data from other columns, creating new derived features.
- Data transformation, particularly the extraction of Accident year and Accident month information from the Accident Date column to enable deeper analysis based on their different parameter.
Finally, we leveraged Excel to create an interactive and visually informative dashboard:
- Developing Excel worksheets to present data insights in a user-friendly format.
- Utilizing charts, graphs, and pivot tables to visualize trends and patterns.
- Incorporating slicers and filters for user interaction, enabling dynamic exploration of data.
- Ensuring real-time data updates through data connections, maintaining the dashboard's relevance.
Here are the insights from the UK road accident dashboard:
- When considering road types, the highest number of accidents occurred on single carriageways and dual carriageways.
- Analyzing road surface conditions, the order of the highest accident frequency was dry roads, followed by wet roads and then snowy roads.
- In the years 2020 and 2021, a total of 417,883 casualties were reported due to road accidents.
- Among these casualties, 61% occurred in urban areas, while 39% took place in rural areas.
- A majority of road accidents, 73%, happened during daylight hours, with the remaining 27% occurring in the dark.
- Casualties categorized by vehicle type show that 333,485 casualties were in cars, 33,472 in vans, 12,798 in buses, 33,672 on bicycles, and 4,456 in other types of vehicles.
- Helps to quickly identify patterns, such as casualty counts, accident locations, and road conditions.
- Shows the impact of light conditions on accidents, facilitating safety measures and road infrastructure improvements.
- Empowers authorities and stakeholders to make informed decisions, enhancing road safety and accident prevention.
- Promotes transparency in sharing accident statistics with the public, increasing awareness and safety awareness.
- Provides a user-friendly interface for both experts and non-experts to work with the data effectively.
- Dealing with messy and inconsistent data.
- Handling missing values, duplicates, and data formatting issues.
- Merging and integrating data from multiple sources.
- Managing and analyzing large volumes of accident data.
- Optimizing Excel workbook performance.
- Using tools like pivot tables and Power Query for efficient data handling.
- Selecting appropriate charts and graphs.
- Ensuring a user-friendly and understandable dashboard layout.
- Balancing aesthetics with clear labels and interactive features.