About the Data:

An xlsx file was provided that contains information on all the traffic accidents in New York City from Jan. 1, 2022 up to Jan. 30, 2023 in a single table. Each record represented a vehicle collision/ accident. Other information included the date, time, location, zip code, vehicle type, contributing factor, and if injury or fatality occurred. Injuries and fatalities were split into PERSONS INJURED, PERSONS KILLED, PEDESTRIANS INJURED, PEDESTRIANS KILLED, CYCLIST INJURED, and CYCLIST KILLED.

Objective: Create an Excel report or dashboard to answer the following:

• Do traffic accidents show any patterns throughout the year?
• During which time of day and day of the week do traffic accidents occur most frequently?
• Are there any particular streets or intersections that carry a higher risk than others?
• What are the most common contributing factors for traffic accidents? What about the most fatal?

Deliverables: (1) Analysis using an Excel workbook, (2) Key insights and findings derived from the data (3) data and visualizations supporting my findings

How I approached the data set and questions:

I opened the file and visually explored the contents to review which information fields and data types were in the data set.

The accident date field was included, but to add additional date part fields, I used excel date functions to add additional columns for the day of the week name and day of week number. Similarly, I added the month name, month number, and year.

For all records that did not have the "Borough" listed, I replaced these empty cells with "No Borough Listed".

Other additional, minor data cleansing was performed.

Key Takeaways:

Focusing on the complete year of 2022, there were a total of 103,648 accidents reported, and>60% resulted in an injury or fatality, with a 1% fatality rate.

Do traffic accidents show any patterns throughout the year?

Accident frequency increased during the year when the weather becomes milder and then decreases again afterward.

During which time of day and day of the week do traffic accidents occur most frequently?

Friday was the day of the week when there were the most accidents. Regardless of the day of the week, the time when most accidents occurred was in the morning and evening rush hour commuting times.

Are there any particular streets or intersections that carry a higher risk than others?

Of the top 10 streets with the most accidents, Belt Parkway had the highest and Broadway had the second highest. In total, there were 36 fatalities reported among the top 10 streets.

Brooklyn has the highest percentage of accidents at 29%, compared to the other Boroughs, as well as 58 fatalities. Borough location was not indicated for 89 fatalities.

What are the most common contributing factors for traffic accidents? What about the most fatal?

Distracted drivers alone accounted for 25% of accidents.

Unsafe speed alone accounted for 22% of fatalities, while distracted drivers accounted for 11%.

Contributing factors were not specified for 28% of fatalities.

Recommendations:

-Focus on implementing policies (incentives and/or penalties) to reduce distracted driving.

-Consider adding age as a part of the accident report to determine if distracted drivers fall into a certain age group.

-Replace unspecified categories with non-ambiguous descriptors.

Tools used: Excel 2021