ROAD ACCIDENT DATA ANALYSIS AND DASHBOARD REPORT
Background and Problem Explanation
The client requires a comprehensive Road Accident Interactive Dashboard for 2021 and 2022, focusing on primary and secondary Key Performance Indicators (KPIs) to provide insights into casualties, accident severity, and other contributing factors.
Primary KPIs:
- Total casualties post-accident
- Total casualties by accident severity and their percentage, and vehicle type maximum casualties.
Secondary KPIs:
- Total casualties categorized by vehicle type
- Monthly trend comparing casualties for the current and previous years
- Maximum casualties by road type
- Distribution of total casualties by road surface
- Correlation between casualties based on Area/Location and Day/Night.
Important Stakeholders:
Ministry of Transport, Road Transport Department, Police Force, Emergency Services Department, Road Safety Corps, Transport Operators, Traffic Management Agencies, Public, Media.
About the Data:
The dataset is in Excel (.xlsx) format with 307,974 rows and 21 columns. It contains accident details with fields like accident_index (unique for every data entry recorded), accident date, location details, accident_severity, number_of_casualties, road_type, vehicle_type, and the rest.
Data Cleaning Approach
- Data Exploration: The dataset underwent a thorough examination in Microsoft Excel to enhance comprehension and identify areas requiring cleaning. Notable issues included blank entries and spelling errors. Specifically, the ‘Accident_Severity’ and ‘Junction_Control’ columns revealed errors such as ‘Fetal’ instead of ‘Fatal’ and ‘Auto traffic sigl’ instead of ‘Auto traffic signal’.
- Data Cleaning: The raw data was duplicated into a new sheet, and labeled ‘Worksheet.’ The subsequent cleaning procedures were executed on this dedicated sheet, comprising:
- The Dataset columns were spaced to get a full view
- The cleaning process is tailored to the requested KPIs by the client
- Each column was checked for anomalies and any error
- All datasets containing blank were dropped.
- All errors were corrected using the Find and Replace function in Excel.
- At the end of the cleaning process, there are a total of 306,126 datasets ready for data analysis.
Data Processing
Part of the requested KPIs by the client is to showcase the dashboard in monthly and yearly insight, but from our data, we only have an Accident_Date column with the full date for each data entry.
Therefore, there is a need to create month and year columns to get the requested insight. The process of doing this is as follows;
- Create two blank columns, and name them ‘Month’ and ‘Year’ respectively
- Use the Excel Text function to extract the month and year from the Accident_Date column.
=TEXT(B2, “mmm”) for the newly created ‘Month’ column
=TEXT(B2, “yyyy”) for the newly created ‘Year’ column
Data Analysis
Primary Key Performance Indicators (KPIs)
- The Excel-Pivot Table was utilized to determine the total number of casualties after the accident in 2021 and 2022.
- The Accident_Severity is of 3 types- Fatal, Serious and Slight. PivotTable was utilized to determine the total number of casualties for each Accident_Severity type, and the Excel function was used to calculate the percentage of total casualties for each Accident_Severity type.
- The same PivotTable was used to determine the Vehicle_Type with maximum casualties.
As seen in the diagram above, for easier further analysis, the data was extracted out of the PivotTable
Secondary Key Performance Indicators (KPIs)
- Total Casualties by Vehicle Type: Categorized total casualties based on vehicle types using Excel PivotTable, and casualties for agricultural vehicles, cars, buses, vans, bikes, and others were identified. This breakdown facilitates a detailed understanding of the impact across various vehicle categories.
- Monthly Trend Analysis: Utilized Excel Pivot Table to compare monthly casualties for the current and previous years. This analysis helps identify patterns, seasonality, and potential contributing factors to accidents with time.
- Maximum Casualties by Road Type: Identified and analyzed the road types associated with the highest casualties. Excel Pivot Table was instrumental in summarizing and visualizing this data.
- Distribution of Total Casualties by Road Surface: Utilized Pivot Table to break down casualties based on road surface conditions. This analysis provides insights into the impact of road conditions on accident severity.
- Correlation Between Casualties based on Area/Location and Day/Night: Excel PivotTable was employed to analyze the correlation between casualties' location and time of day. Understanding the relationship between these variables contributes to targeted safety measures, especially in specific areas.
Excel Functions Utilized
- Cell Formatting for Thousands
Procedure:
Select all cells requiring formatting.
Press Ctrl+1 to open the Format Cells dialog box.
Navigate to the “Custom” category.
Edit the ‘Type’ from the default ‘General’ to ‘0.0,k’ and press OK.
Result:
Numbers in the thousands are now formatted with a ‘k’ for clean presentation.
- Categorizing Similar Data for Smooth Visualization
Procedure:
Click on the Pivot Table containing similar datasets.
Navigate to the PivotTable Analyze section at the top of the sheet.
Access Fields, Items, and Sets.
Choose “Insert Calculated Items.”
Assign a name to the category and aggregate similar data under this name.
Example: For Primary Key Performance Indicators (KPI) — ‘Vehicle Type and Number of Casualties - data for agricultural vehicles, cars, buses, vans, bikes, and others were grouped.
Similarly, for Secondary KPIs like ‘Casualty by Light Condition’ and ‘Casualty by Road Surface,’ similar data were aggregated under single headings, enhancing data clarity. This simplification aids in creating more comprehensible visualizations.
Visualization and Dashboard Overview
1. Visualization:
A dedicated ‘Data Analysis’ sheet was created for comprehensive data visualization. Excel’s ‘Insert Function’ feature was utilized to pick suitable charts for each table derived from PivotTable data analysis.
- Primary KPIs: Doughnut charts were employed to visualize and represent primary KPIs. The charts were formatted for clarity, ensuring a clean and easily interpretable presentation.
- Secondary KPIs: Different visualization tools, including Doughnut chart, Treemap, Line graph, and Bar chart, were utilized to convey insights from secondary KPIs.
2. Dashboard: An interactive dashboard with key features to enhance usability and understanding was developed.
This is the link to the dashboard:
https://d.docs.live.net/72c783e48b9c7cc3/Road%20Accident%20Data.xlsx
- Timeline Button: The timeline button was integrated to visualize road accidents in 2021 and 2022 separately or collectively. This enables a more detailed examination of trends and patterns over each year.
- Slicer: Slicer functionality was incorporated into the dashboard, using Rural/Urban categorization as a filter. This allows users to view the dashboard based on specific KPIs and choose between Urban, Rural, or overall perspectives for a targeted analysis.
- Linkages: The dashboard is intricately linked with the ‘Data Analysis’ sheet, providing seamless navigation to internet resources and email. This integration ensures easy mobility and quick access to related information. This can be interacted with with the use of icons on the left-hand side of the dashboard.
Conclusion
This visualization and dashboard setup not only allows for a visually appealing representation of the analyzed data but also provides an interactive platform for users to explore specific aspects of the data. The inclusion of timeline and slicer features enhances the flexibility and utility of the dashboard for stakeholders, facilitating a deeper understanding of road accident trends.
