Road Accident Dashboard

This project transforms raw accident data into actionable insights, identifying high-risk zones, vehicle impact, and temporal trends to enhance road safety and inform data-driven policy decisions.

Road Accident Dashboard Preview

Summary

Goal

Traffic accidents impose enormous costs — not just economically, but also in human lives. This project aims to harness the power of data to transform raw road accident records into actionable insights. By analysing accident severity, vehicle types, environmental conditions, and temporal trends, the dashboard provides a clear picture of high-risk areas and periods, enabling targeted interventions to improve road safety.

Process

The project begins with a deep dive into the raw data, which includes over 307,000 rows across 21 fields sourced from Kaggle. The data is meticulously scrubbed and transformed — errors are corrected, dates are standardised, and new columns (such as Month and Year) are derived.

Detailed PivotTables are then used to compute key metrics, and individual visualisations (Donut Charts, Line Charts, Treemaps) are built on dedicated worksheets. These visualisations are ultimately integrated into an interactive, comprehensive dashboard that allows users to drill down from annual trends to granular insights.

Insights

  1. Accident Severity: A substantial number of accidents result in high casualty volumes, with fatal and serious cases marking critical safety concerns.
  2. Temporal Trends: Certain months, such as November, exhibit significantly higher accident rates compared to quieter periods like January or February.
  3. Vehicle Impact: Cars, including taxis, are the predominant vehicle types involved in accidents.
  4. Environmental Factors: Specific road conditions — particularly single carriageway roads and certain surface conditions (dry vs. wet) — correlate strongly with accident frequency.

Interactive Demo

Watch the dashboard in action — demonstrating real-time filtering and responsive visualisation capabilities:

Project Overview

Project Description

▼

Road Accident Dashboard: Overall Project Overview

Tool:Microsoft Excel
Project Type:Data Analysis & Data Visualisation

The Road Accident Dashboard is a data visualisation project built to analyse and interpret synthetic road accident data for the years 2021 and 2022. Using an artificial dataset sourced from Kaggle, this project processes over 307,000 rows (handling up to 3.07 million records) across 21 fields, offering a comprehensive view into traffic incidents. The dashboard is designed to reveal patterns in accident severity, vehicle involvement, road and environmental conditions, and temporal trends, transforming raw data into actionable insights for stakeholders.

Objectives & Significance

▼

Primary Objectives:

  • Uncover Key Trends: Identify high-risk periods and critical factors contributing to road accidents.
  • Segmented Analysis: Break down data by accident severity (fatal, serious, slight), vehicle types, and environmental conditions (road surface, light conditions, urban vs. rural).
  • Interactive Decision Support: Develop an interactive dashboard that allows stakeholders to explore the data dynamically using slicers, timelines, and drill-down features.

Why It Matters:

Traffic accidents have profound social and economic impacts. By converting complex datasets into clear visual narratives, this dashboard equips key stakeholders — including government agencies, law enforcement, emergency services, and the general public — with the insights needed to drive policy changes, improve road safety, and ultimately reduce the number of accidents and casualties.

Project Environment & Specifications

▼

File & Data Specs:

  • Format: .xlsx (Excel Workbook)
  • Size: 307,000+ rows; handles 3.07 million records
  • Columns: 21 fields (e.g., Accident_Index, Date, Day_Of_Week, Accident_Severity, Vehicle_Type, Road_Surface, Light_Conditions, etc.)
  • Source: Kaggle (artificial dataset for practice)
  • Memory Management: Optimised for high-volume records without crashing

Stakeholder Ecosystem:

  • Primary: Ministry of Transport, Road Transport Department
  • Supporting: Police (Traffic Division), Emergency Medical Services, Road Safety Corps, Traffic Management Agencies, General Public, Media Organisations

Technical Parameters:

  • Excel Version: Office 365 (required for modern visuals)
  • Pivot Cache: Multiple caches for different analysis views
  • Calculation Mode: Automatic

Scope & Project Steps

▼

Phase 1: Data Scrubbing

  • Null Check: Column-by-column validation for completeness.
  • Error Correction:
    • Fix typos (e.g., replace "fetal" with "fatal" in 49 instances using Ctrl+H).
  • Format Standardisation:
    • Date fields formatted as DD/MM/YYYY.
    • Text fields converted to proper case.

Phase 2: Data Transformation

  • Derived Columns:
    • Month Extraction: =TEXT(B2,"mmm") — Converts date to Jan–Dec
    • Year Extraction: =TEXT(B2,"yyyy") — Extracts year, ensuring only 2021–2022 appear
  • Data Validation: Verify 12 unique months and that the year falls within 2021–2022.

Phase 3: Analytical Processing

  • Construct 7 base PivotTables (one per analysis dimension).
  • Vehicle Grouping: Cars = Car + Taxi; Buses = Bus + Minibus; Vans aggregated using nested IFs.
  • Time Grouping: Organise months into quarters and fiscal years.

Phase 4: Visual Engineering

  • Donut Charts: Inner radius set to 70%, no borders, labels positioned at the outside end.
  • Combo Charts: Primary axis uses line charts; secondary axis uses column charts with 150% gap width.
  • Conditional Formatting: Data Bars for KPIs; Color Scales for matrix tables.

Project Assets & Downloads

▼

Dashboard Files

Download the complete Excel workbook with interactive dashboard.

Get Dashboard

Raw Dataset

Download the processed dataset used in this analysis.

Get Dataset

For implementation details and technical documentation:

View Full Repository

Data Sources & Quality Control

▼

Dataset Schema & Provenance:

  • Includes 21 fields such as Accident_Index (unique key), Date, Day_Of_Week, Accident_Severity, Vehicle_Type, etc.
  • Downloaded as CSV from Kaggle, then converted to .xlsx.
  • Synthetic data with no real PII; balanced distribution over an artificial time range.

Quality Control Metrics:

  • Completeness: Verify row count and check null percentages.
  • Consistency: Cross-check severity categories, validate date ranges.
  • Accuracy: Compare sums from PivotTables with raw data; run statistical measures (mean, mode, standard deviation, IQR, correlation tests).

Worksheet Construction & Dashboard Engineering

▼

Pivot Table Blueprints:

  • KPI Master Table: Rows: Severity; Values: Sum of casualties; Filters: Year and Urban/Rural.
  • Vehicle Matrix: Rows: Grouped Vehicle Types; Columns: Severity; Values: Count of incidents.
  • Temporal Analysis: Rows: Month; Columns: Year; Values: Running totals.

Formula Repository:

  • Dynamic references using GETPIVOTDATA(), e.g., =GETPIVOTDATA("Sum of Casualties", $A$3, "Severity", "Fatal")
  • Conditional aggregation with SUMIFS(), and quick lookups using XLOOKUP().

Dashboard Components:

  • Header Block: Dimensions: 35cm × 1.75cm; Fill color: #303B4A; Font: Lato Black 28pt.
  • KPI Tiles: Fixed size 8.3cm × 3cm; Rounded corners (0.5cm radius); Drop shadow effects.
  • Interactivity: Slicer connections across timeline and location; Cross-filtering between Vehicle and Severity, Road Type and Surface; Drill-down from Year to Quarter, Urban to District.

Narrative Architecture:

  • Data Story Flow: Macro to micro (Annual → Monthly → Daily) and general to specific (all vehicles → focus on cars).
  • Annotations: Dynamic labels using concatenation formulas and tooltips indicating year-over-year change and percentage breakdowns.

Get in Touch

Thank you for exploring my work! Feel free to reach out for collaborations or inquiries.