Road Accident Analysis

Steps completed during analysis:

1. Data cleaning - As a first step, we have cleaned the data removing duplicates, spelling error, handling nulls etc.

2. Data processing -applied formulas, built customized columns for the visualization

3. Data analysis - we calculated aggregated data, built pivot table , some charts to analyze the available data

4. Data visualization - built the dashboard with analysis we have done for the end users

Requirement -

Primary KPIs :

1. Total casualities have taken place after the accident

2. Total casualities and percentage of total based on accident severity

3. Maximum casualities based on type of vehicles

Secondary KPIs:

1. Total casualities based on vehicle types

2. Monthly trend of casualities and comparison between current year and LY

3. Max casualities by road type

4. Distribution of total casualities by road surface

5. Relation of casualities by day/night, urban/rural

Major stakeholders -

1. ministry of transport

2. road transport dept

3. Police,

4. Emergency services

5. Road safety corps

6. Transport operators

7. Traffic management agencies

8. Public, media

Metadata :

3 lakhs records from excel with 21 columns

Technology used: Microsoft excel

Key insights:

1. Most of the accidents happened with car (almost 80%)

2. Most accidents happened with single road.

3. Most casualities happened on dry road surfaces.

4. Urban location has more contributions to accidents compared to rural location

5. Most accidents happened in daylight compared to dark.

6. In current and previous years , November has highest accidents compared to other months.