Transforming Road Accident Data Into Actionable Insights: The Excel Road Accident Dashboard.
Hello everyone! I recently dived into the world of road accident analysis, and I’m excited to share what I’ve discovered. The main goal of this project was to create a Road Accident Dashboard covering the years 2021 and 2022, with a focus on examining traffic accident data. This dashboard was designed to offer important insights to decision-makers and influence policies aimed at decreasing accident-related injuries and fatalities. The key performance indicators (KPIs) we concentrated on, both primary and secondary, included:
Primary KPIs
- Total Casualties after the accident
- Casualties by accident severity and vehicle type
Secondary KPIs
- Monthly comparison of casualties between the current and previous year
- Casualties by vehicle type
- Maximum casualties by road type
- Distribution of total casualties by road surface
- Relation between casualties by area/location and by day/night
- Casualties by vehicle type
What’s the Ministry of Transport Looking For?
So, the Ministry of Transport and its stakeholders wanted to learn about road accidents for the years 2021 and 2022.
What I Discovered
To begin, I began with an XLSX dataset comprising 23 columns and 307,973 data rows DataFile. To streamline data handling, I transformed this dataset into a table format, simplifying tasks such as filtering and sorting. Ensuring data accuracy was a priority, involving checks for null entries and rectifying any typographical errors. Subsequently, pivot tables were employed to produce the essential Key Performance Indicators (KPIs).
In 2021, there were a lot of road accidents — a whopping 222,146 to be exact, and they led to casualties (that’s people getting hurt). That’s about 53.2% of all accidents. But in 2022, something interesting happened. The number of casualties dropped by 6.3%. This drop happened because the Road Ministry started a campaign to teach people how to drive safely.
Now, let’s look at the kinds of vehicles in accidents. Saloon cars were the main troublemakers, involved in about 80% of accidents. These are those everyday cars you see everywhere.
Also, most accidents, around 60%, happened in cities. This might be because cities have faster speed limits, and more people mean more chances for accidents.
Now, here’s a surprising one — about 73% of accidents took place on those narrow, single-carriageway roads. It’s harder to avoid accidents on these roads.
Lastly, dry roads saw about 67% of accidents, while wet and snowy roads had fewer. Maybe people feel safer on dry roads, so they drive faster and sometimes crash.
Recommendations
- To improve road safety, it is advisable for the Ministry for Transport to continue investing in initiatives such as enhancing road infrastructure, educating the public on safe driving practices, and enforcing traffic laws.
- Prioritize efforts to reduce accidents involving private vehicles, such as cars, by encouraging alternatives like public transportation, promoting carpooling, and implementing measures to deter driving under the influence.
- Enhance emergency services and ensure equitable access to safe and affordable medical care to minimize casualties resulting from accidents.
- Utilize advanced technologies, like intelligent transportation systems, to monitor traffic conditions and identify potential hazards, thereby improving road safety.
