This project encompasses a comprehensive analysis of road traffic accident data in the United Kingdom for the years 2019 and 2022. Through the integration of Excel, SQL queries, Power BI visualizations, and Tableau dashboards, the aim is to provide actionable insights to enhance road safety and mitigate accidents.
This project utilizes open-source Road Accident Data from the United Kingdom spanning the years 2019 to 2022.
The United Kingdom faces persistent challenges related to road safety, with road accidents resulting in casualties and significant economic and social costs. To address this issue and make informed decisions, there is a need for a comprehensive Road Accident Dashboard that provides insights into accident data.
-
- Power BI Dashboard: Constructed a dynamic and interactive dashboard emphasizing data integration and visually appealing visualizations.
- Tableau Dashboard: Created a dashboard in Tableau with custom charts and insightful trend analyses.
- SQL: Utilized SQL queries for data processing and analysis, including data aggregation, filtering, and joining to extract key metrics.
- Excel Dashboard: Developed an interactive Excel dashboard incorporating pivot tables and visually compelling charts.
-
- Identified and calculated primary KPIs, including Total Casualties and Total Accidents, for 2021 and 2022. YoY growth percentages were computed to assess trends and improvements.
-
- Analyzed accident severity by categorizing casualties into severity levels (Fatal, Serious, Minor) for both years. YoY growth percentages for each severity level were calculated.
-
- Analyzed casualties based on vehicle types (Car, Truck, Motorcycle) for 2021 and 2022. YoY growth percentages were considered to identify trends.
-
- Conducted geospatial analysis to determine casualties and accidents by road type, location, and time of day for the UK in 2021 and 2022.
-
- Created a monthly trend analysis for a year-over-year comparison of casualties in 2021 and 2022.
| Insights | Descriptions |
|---|---|
| Stakeholder Understanding | Understanding the stakeholders and users of a dashboard is crucial for developing and designing an effective dashboard. |
| Time Intelligence in Power BI | Creating a date table is crucial for time intelligence functions in Power BI, ensuring accurate year-to-date calculations and other time-related analyses. |
| Custom DAX Functions in Power BI | Utilizing custom DAX functions in Power BI allows for more flexibility and control in calculating and displaying key performance indicators. |
| Positive Trend in Road Accidents | Fatal casualties have decreased by 33%, showing a positive trend in road accidents. |
| Tableau Performance Advantage | Tableau's ability to store data in compressed size and fire queries to high-profile data results in faster loading time and increased performance, making it a valuable tool for data analysis. |
| Insightful Visualization with Sparklines | The visualization of monthly accident trends using sparklines provides valuable insights into the patterns and changes over time. |
| Importance of Data Cleaning | Data cleaning is essential to ensure accuracy and consistency in the dataset, including checking for typos and missing values. |
| Discrepancies in Results Across Platforms | The SQL queries are used to find the current year casualties, and the results differ between Power BI, Tableau, and Excel dashboards due to the different filters and data sources used. |
| Tool | Description | Visualization |
|---|---|---|
| POWER BI | Dynamic and interactive dashboard | |
| TABLEAU | Custom charts and insightful trend analyses | |
| EXCEL | Interactive dashboard with pivot tables | |
| SQL | Utilized for data processing and queries |
For any inquiries or collaboration opportunities, please feel free to reach out via LinkedIn. Your feedback and suggestions are highly appreciated.
- Excel Dashboard: Drive Link
- Project Datasets: Drive Link
- Power BI Dashboard: Interactive Report