ROAD ACCIDENT DASHBOARD DATA ANALYSIS REPORT

Table of Contents

Project Title

Introduction

Data Collection

Client Requirement

Data Preparation

Data Analysis

Findings

Conclusion

Appendices

References

Project Overview

1. Project Title

• Title: Road Accident Dashboard Analysis for 2021 and 2022
• Date: June 22, 2024
• Author: Joseph John

2. Introduction

• Objective: To analyze and visualize road accident data for the years 2021 and 2022 to provide insights into various aspects of road accidents.
• Background: This project aims to help clients understand the trends and key performance indicators (KPIs) related to road accidents, such as the total number of casualties, types of casualties, and the impact of different factors like vehicle type, road type, and light conditions.
• Scope: The analysis covers road accident data from January 2021 to December 2022, focusing on total casualties, accident severity, vehicle types, and other relevant factors.

3. Data Collection

• Data Sources: The data is sourced from Reported Road Casualties Great Britain (RRCGB), which is the official statistical publication of the UK Department for Transport (DfT) on traffic casualties, fatalities and related road safety data. You can download the data in excel format here
• Data Description:
  • Number of rows: 307,974
  • Number of Columns: 23, which includes Accident_Index, Date, Accident Severity, Number of Casualties, Road types etc.

4. Client Requirement

5. Data Preparation

• Cleaning Process:
  • Ensured completeness of records for the two years.
  • Standardized data formats for dates and categorical variables.
  • Replaced some wrong entries (Eg. “Fetal” instead of “Fatal”).
• Data Formatting: Converted data into Excel tables for ease of analysis and applied consistent naming conventions for columns.

6. Data Analysis

• Methodology:
  • Used pivot tables to summarize casualties by year, month, and other dimensions.
  • Created line charts to visualize monthly trends.
  • Used pie and bar charts for distribution analysis.
  • Applied conditional formatting to highlight key metrics.

• Tools Used:
  • Pivot Tables
  • Charts (line, bar, pie)
  • Conditional Formatting
  • Filters for interactive analysis
  • Slicers

7. Findings

• Summary of Results: The total number of casualties over the two years is 13,200. A significant proportion of these casualties were related to cars.
• Detailed Analysis:
  • Casualties by Severity:
    • Fatal: 157 (1.2%)
    • Serious: 1,636 (12.4%)
    • Slight: 11,407 (86.4%)
  • Casualties by Vehicle Type:
    • Cars: 10,468 (79.3%)
    • Trucks: 1,096
    • Bicycles: 1,066
    • Vans: 426
    • Tractors: 36
    • Others: 108
  • Monthly Trend:
    • The monthly trend shows a relatively stable number of casualties with slight peaks and troughs, and a noticeable decline in December 2022.
  • Casualties by Road Type:
    • Single carriageway: 9,600
    • Dual carriageway: 2,500
    • Roundabout: 700
    • One-way street: 200
    • Slip road: 200
  • Casualties by Road Surface:
    • Wet: 4,807
    • Snow/Ice: 5,062
    • Dry: 3,322
  • Casualties by Location/Area:
    • Rural: 5,300
    • Urban: 7,900
  • Casualties by Light Condition:
    • Daylight: 6,800
    • Dark: 6,400

8. Conclusion

• Summary: The analysis provides a comprehensive view of road accidents, highlighting the predominance of car-related casualties, significant seasonal variations, and the impact of road and surface types on accident severity.
• Implications: These insights can guide targeted interventions, such as improving road safety measures in high-risk areas and during high-risk conditions.
• Recommendations:
  • Implement safety campaigns focusing on car drivers.
  • Enhance road maintenance to mitigate the impact of adverse surface conditions.
  • Improve lighting and signage in rural and high-risk areas.
• Limitations: The analysis is limited to the provided data for 2021 and 2022. Additional data and longer time periods could provide more robust trends and insights.

9. Appendices

• Raw Data: road_accident_raw_data.xlsx
• Processed Data: road_accident_cleaned_data.xlsx
• Additional Resources: Reported Road Casualties Great Britain (RRCGB)

10. References

• See more of my projects here and contact me if you desire a collaboration