About the Project
In 2022, the United Kingdom witnessed 91,199 road accidents, a stark reminder of the persistent challenges in ensuring road safety. This comprehensive analysis leverages data from the UK Government’s road safety dataset, examining various aspects of road accidents, including driver demographics, accident severity, common vehicle manoeuvres, and the impact of external factors such as lighting conditions. Through this analysis, we aim to uncover critical insights and propose actionable recommendations to enhance road safety.
Dataset
The dataset comprises information about 91,199 accidents in the UK during 2020, sourced from the UK government data portal. The dataset includes three primary tables: accident, vehicle, and casualty.
Tools Used
- MySQL: For data extraction and manipulation.
- Tableau: For data visualization.
- Python: For data preprocessing.
Importing the files
To import the data into MySQL, a custom Python script was used to create the tables and prepare the CSV files for loading. The tables were then populated using the LOAD DATA INFILE command.
Analysis
Age distribution of the drivers
To begin our analysis, we first examine the age distribution of drivers involved in accidents. This helps us understand who is most frequently involved in road accidents. We use the age_of_driver field from the vehicle table for this analysis.
SELECT age_of_driver, COUNT(*) as count
FROM `vehicle-2022`
WHERE age_of_driver >= 16
GROUP BY age_of_driver
ORDER BY age_of_driver
;
The average age of drivers involved in accidents is 34.5 years. Notably, there are spikes around multiples of 5 and 10, suggesting potential approximation errors. This indicates that middle-aged drivers are prominently involved in accidents, but further analysis is needed to understand the underlying reasons.
Accident severity
We then explore the overall severity of the accidents to understand the distribution of accident outcomes. This sets the stage for more detailed analysis of factors influencing accident severity.
SELECT accident_severity,
CASE
WHEN accident_severity = 3 THEN 'slight'
WHEN accident_severity = 2 THEN 'serious'
WHEN accident_severity = 1 THEN 'fatal'
END AS severity,
COUNT(*) AS count
FROM road_safety_analysis.`collision-2022`
GROUP BY accident_severity
ORDER BY accident_severity DESC
;
The vast majority of accidents (84.06%) are slight, while only 0.4% are fatal. This highlights the need to focus on preventing severe accidents, which, despite being less frequent, have significant consequences.
Severity by drivers age
Building on our understanding of accident severity, we analyze how severity correlates with the driver’s age. This helps us identify if certain age groups are more likely to be involved in serious or fatal accidents.
SELECT
v.age_of_driver,
c.accident_severity,
COUNT(*) AS count
FROM
`vehicle-2022` v
JOIN
`collision-2022` c ON v.accident_index = c.accident_index
WHERE
v.age_of_driver >= 16
GROUP BY
v.age_of_driver, c.accident_severity
ORDER BY
v.age_of_driver, c.accident_severity;
Younger drivers (<23) and older drivers (>50) are slightly more likely to be involved in serious or fatal accidents compared to middle-aged drivers. This suggests that targeted interventions for these age groups could help reduce the severity of accidents.
Common vehicle manoeuvres in accidents
Next, we examine the types of vehicle manoeuvres most commonly involved in accidents. This helps us identify driving behaviors that are particularly risky.
SELECT
CASE
WHEN vehicle_manoeuvre = 18 THEN 'Going ahead other'
WHEN vehicle_manoeuvre = 9 THEN 'Turning right'
WHEN vehicle_manoeuvre = 4 THEN 'Slowing or stopping'
WHEN vehicle_manoeuvre = 5 THEN 'Moving off'
WHEN vehicle_manoeuvre = 2 THEN 'Parked'
WHEN vehicle_manoeuvre = 3 THEN 'Waiting to go – held up'
WHEN vehicle_manoeuvre = 7 THEN 'Turning left'
ELSE 'Other'
END AS manoeuvre,
COUNT(*) AS count
FROM `vehicle-2022`
WHERE vehicle_manoeuvre BETWEEN 1 and 18
GROUP BY manoeuvre
ORDER BY count DESC;
“Going ahead other” is the most common manoeuvre involved in accidents, followed by “Other”. This highlights the need for educational campaigns focused on safe driving practices for these manoeuvres.
Location of the accidents
Visualizing the geographic distribution of accidents helps us understand where accidents are most likely to occur.
Accident by hour of day
We analyze the frequency of accidents by hour of the day to identify peak times for road accidents, which can inform targeted interventions.
SELECT
HOUR(STR_TO_DATE(time, '%H:%i')) AS hour_of_day,
COUNT(time) AS number_of_accidents
FROM `collision-2022`
GROUP BY hour_of_day
ORDER BY hour_of_day;
Most accidents occur around 17:00, with a noticeable spike around 08:00. These times correspond to rush hour traffic, suggesting the need for targeted measures during these periods to mitigate accident risks. This coincides with middle age people and the active workforce going to work.
Severity by lighting conditions
Finally, we explore how lighting conditions affect the severity of accidents, which can inform infrastructure improvements such as better street lighting.
SELECT
CASE
WHEN light_conditions = 1 THEN 'Daylight'
WHEN light_conditions = 4 THEN 'Darkness (lights lit)'
WHEN light_conditions = 5 THEN 'Darkness (lights unlit)'
WHEN light_conditions = 6 THEN 'Darkness (no lighting)'
END AS lighting,
AVG(CASE
WHEN accident_severity = 3 THEN 1
WHEN accident_severity = 2 THEN 10
WHEN accident_severity = 1 THEN 50
END) AS severity,
COUNT(*) AS number_of_accidents
FROM `collision-2022`
WHERE light_conditions IN (1, 4, 5, 6)
GROUP BY lighting;
Accidents in poor lighting conditions tend to be more severe. This underscores the importance of improving street lighting in areas prone to accidents, which could help reduce the severity of accidents occurring in the dark.
Outcomes and Recommendations
Based on the analysis, we propose the following recommendations to improve road safety in the UK:
- Targeted Awareness Campaigns: Focus on younger drivers (under 35) who are disproportionately involved in accidents. Educational campaigns could emphasize the risks associated with common dangerous manoeuvres like “Turning right.”
- Improved Road Lighting: Enhance lighting in areas with high accident rates, particularly where lighting conditions are currently poor. This could help reduce the severity of accidents that occur in the dark.
- Rush Hour Interventions: Implement measures to manage traffic during peak hours, such as staggered work hours or improved public transportation options, to reduce the number of accidents during rush hours.
- Road Design Improvements: Consider redesigning intersections and roadways to minimize high-risk manoeuvres. For example, providing dedicated lanes for turning right could help reduce accidents.
- Policy Adjustments: Evaluate and adjust policies related to driver’s license issuance and renewal, particularly for younger and older drivers, to ensure they are prepared for safe driving.
By implementing these recommendations, the UK can make significant strides in reducing the number and severity of road accidents, ultimately enhancing road safety for all users.
