In this project, I am attempting to complete the project using PostgreSQL. The dataset used is the accident_2021_database <<< — Download dataset.

I have placed all the queries I used on the following GitHub repository.

[GitHub Repository Link]

PROBLEM

The primary goal of this project is to analyze traffic safety data for 2021 to provide valuable recommendations to the National Highway Traffic Safety Administration. This analysis will provide in-depth insight into the factors that cause accidents, incident patterns, as well as critical areas that require more attention in efforts to develop new, more effective regulations.

OBJECTIVE

The main objective of this project is to carry out an in-depth analysis of accident data that occurred throughout 2021 and develop concrete and effective recommendations to reduce the number of accidents on the road. By carefully analyzing the data, this project aims to provide guidance to NHTSA in designing new regulations that are more effective and focused on traffic safety. Some specific goals that can be achieved through this project include:

1. Identify the risk factors that most contribute to road accidents, such as excessive speed, extreme weather, vehicle type and road conditions.
2. Determine the ten (10) states with the highest accident rates, so that NHTSA can direct prevention efforts more efficiently.
3. Analyze accident patterns by daily hours to identify critical time periods that require closer monitoring.
4. Measuring the percentage of accidents caused by drivers under the influence of alcohol or drugs with the aim of developing alcohol-related prevention strategies.
5. Comparing the percentage of accidents in rural and urban areas to adjust prevention efforts according to regional characteristics.
6. Identifies accident patterns by day of the week, aiding in more effective resource allocation.

By formulating these goals, the “Traffic Safety Project 2021” project will provide strong guidance and recommendations to NHTSA to design new regulations that have the potential to significantly reduce the number of road accidents

FINDINGS

Data Cleaning Limitations

Weather and light conditions at each hour of the accident and their relationship to data abnormalities

Summary

Curve Description

• Dark is a combination of dark-lighted, dark-not lighted, and dark-unknown lighted light conditions
• The light conditions Dusk, Reported as Unknown, Dawn, Not Reported are not displayed to simplify the parameters because the amount of data is 5.11% of the total data
• The weather displayed is the top 3 weather totaling 90.24% of the total data
• The second curve is a curve to narrow down the region (CST time zone) and the day of the accident (in this case it is 07 — July)

Weather conditions at the time of the accident

• The weather at the time of the accident was generally sunny every hour of the accident

Light Conditions at the time of the accident

• Accidents occur in almost balanced conditions between daylight and darkness, and the peak occurrence of accidents occurs during dark conditions at 13 o’clock
• The majority of accidents in light conditions occur at 01–13 hours, while in dark conditions they occur at 14–00 hours

Data anomalies

• Daylight occurs every hour, as well as dark conditions occurring almost every hour except 06 o’clock when the majority of weather conditions are sunny
• Accidents at 12–13 o’clock are almost balanced between dark and light in mostly sunny weather conditions
• By narrowing down the area and day of the accident (in this case the month) we have eliminated seasonal factors and the location of the sun and still found data on incidents during the day and night at the same time.
• Darkness and daylight simultaneously last from 11–15 with a peak at 14 with the number of accidents in the dark and in the light in a ratio of 2:1 (11 accidents in the light and 21 accidents in the dark)

Conclusion

We did not carry out the cleaning process for abnormalities in the data because based on the existing data we could not find patterns regarding the hours of the day and night in certain seasons, and time constraints

Here are some insights that we managed to find:

1. Analyze condition factors that increase the risk of accidents

Atmospheric Conditions

• Clear Atmospheric Conditions: These are the atmospheric conditions where the most accidents occur. This could indicate that other factors such as driver behavior or traffic volume may have contributed more to the crash than the weather conditions themselves.
• Foggy and Cloudy Atmospheric Conditions (Fog, Cloudy): Accidents in these conditions are less common, but they can occur due to reduced visibility.
• Rain and Snow Conditions (Raining, Snowing): These conditions are usually considered dangerous for driving, but the number of accidents in these conditions is lower compared to sunny weather.

Lighting Conditions

• Daylight: Most accidents occur in daylight conditions. This may reflect higher traffic volumes during these hours.
• Dark-Not Lighted: There has been a significant increase in the number of accidents in this condition, indicating that a lack of lighting may be a risk factor.
• Other Conditions (Dusk, Dawn, Dark-Lighted): Crashes in these conditions are less common.

Highlights

Dominance of Accidents in Sunny and Daytime Weather: The highest number of accidents occurred in sunny and daytime conditions. This is an important finding that suggests other factors may be more influential in causing accidents.

2. Top ten states where the most accidents occur

Summary

• Of the 35,451 accidents that occurred in 1 year, the top 10 states experienced more than 1000 accidents.
• The state of Texas is ranked 1st for the most accidents, followed by California and Florida as the top three states contributing to the most accidents at 28.5% of the total accidents.
• Of the 10 states with the largest number of accidents, it contributed 51.90% of the total accidents that occurred during 1 year.

Highlights

Of the 35,451 accident cases that occurred, the top 10 states accounted for 51.9%. The most accident cases were in the state of Texas, followed by California and Florida which accounted for 28.5% of the total accidents that occurred.

3. Average number of accidents per day based on the time the accident occurred

Summary

• The peak of the accident occurred at 13 o’clock
• On average, more than 5.5 accidents occur between 11–15 hours
• The lowest accident was at 03 Hours
• Accidents rarely occur at night but have a high potential if the driver is drunk, accounting for 9.83% of the 25.78% of incidents. See data here
• Accidents at night often occur between 23–02 hours with the peak being at 01 hours

Highlights

Community mobility which results in accidents increases from 06 to 13 and begins to decline from 14 to 22 with a high number of accidents, especially at 11–16 with the peak occurring at 13. There needs to be increased prevention balanced with increased preparedness. accident handling

4. Percentage of accidents caused by drunk drivers

Persentase Kecelakaan

• Within a period of one year, namely in 2021, out of a total of 100% of incidents, 25.78% of accident cases occurred due to drunk drivers and 74.22% of accident cases occurred due to non-drunk drivers.
• The percentage calculation is obtained as follows:
• Percentage of accidents due to drunk drivers:

f: =countif(numberof drunk driver ,”>0")/count(totalkecelakaan)*100

f: 9141/35451*100

• Percentage of accidents caused by non-drunk drivers:

f: =countif(numberof drunk driver,”=0")/count(totalkecelakaan)*100

f: 26310/35451 * 100

• The total number of accidents that occurred in 2021 was 35,451 accident cases

The percentage of accidents due to drunkenness is influenced by light conditions

Summary

The following is detailed data on the percentage of accidents due to drunkenness or soberness which are influenced by light conditions

• As many as 9.83% of the 25.78% number of accidents that occurred due to drunk drivers were affected by dark conditions without any lighting (dark-not lighted), followed by the second occurring during the day (daylight). The figure 9.83% is the percentage of 9141 incidents due to drunk drivers.
• In contrast to drunk drivers, the highest number of accidents caused by non-drunk drivers actually occurred during the day (daylight), namely 38.09% of the 74.44% of incidents. Details refer to number 8

The number of accident fatalities in 2021 based on the category of drunk or not drunk drivers

Summary

• Based on the chart of the number of accident victims, there were 9141 accidents, the number of fatalities due to accidents caused by drunk drivers was 10138 out of a total of 38491 fatalities.
• Meanwhile, as many as 26,310 accidents occurred, there were 28,353 fatalities out of a total of 38,491 fatalities.

Highlights

In 2021, the number of accidents caused by drunk drivers accounted for 25.78% of the total 100% of incidents. This means that there are still many vehicle drivers who do not comply with safety rules while driving so that this action causes harm both to themselves and to others. In fact, 10,138 people died as a result of this incident.

The incident that occurs most often is when it is dark without any street lighting and the driver is drunk. These two things have a big potential to cause traffic accidents because the driver’s concentration decreases and their response slows down.

In fact, this incident could have been avoided if the driver had full awareness of the dangers of consuming alcohol or other prohibited substances which could cause a loss of consciousness. Therefore, it is important to avoid driving after consuming alcohol and always look for safe alternatives such as using public transportation, taxis, or relying on friends or family to take you home. The safety of yourself and others must always be a top priority.

5. Accidents in rural and urban areas

Summary

• Comparison of the number of accidents

Based on existing data, it shows that 14,845 accidents occurred in villages and 20,123 accidents occurred in cities. So it can be concluded that the number of accidents in urban areas is higher than the number of accidents in rural areas

• Percentage of accidents

From the percentage data presented, 57.5% of total accidents occurred in cities, while 42.5% occurred in rural areas. From this percentage we can see the comparison of the percentage level of accidents in each location which is related to the total number of accidents.

• Analysis of accidents in the city

With a higher number of accidents and a larger percentage of accidents in the city, this shows that the accident problem in this area is more severe. It is very important to take appropriate and effective preventive measures based on an analysis of the causes and variables that influence the frequency of accidents in the city.

• Analysis of safety in the village

Even though accidents in villages are lower than in cities, it is very important to maintain and monitor safety in these areas. By analyzing the factors that cause accidents in villages, it can help prevent an increase in accidents in the future.

• Limitation

Other areas (Not Reported, Unknown, Traffic Not in State Inventory) were ignored due to the small number (483 accidents out of a total of 35,451 or 1.36%)

Highlight

The highest total accidents were in urban areas with a total of 20123 accidents with a percentage rate of 57.5%, while the lowest accident rate was in rural areas with a total of 14845 accidents with a percentage of 42.5%.

6. Number of accidents by day

Summary Chart

• Number of accidents by day:

Based on existing data, it shows that the highest number of accidents by day occurred on Sunday (Sunday) with 6327 cases and the second on Saturday (Saturday) with 5812 cases, so it can be concluded that the highest number of accidents occurred on weekends/holidays.

• For weekdays / working days from Monday to Friday the number of accidents is below 5000 cases and Tuesday is the day where the lowest number of accident cases occurs, namely 4357 cases

Highlight

The highest number of accidents occur on weekends (Saturday-Sunday), this is because many people use private vehicles to go on holiday or visit relatives/friends, so the traffic volume becomes busy, which can increase the risk of accidents occurring.

7. Intersection factors on driver conditions at each hour

Summary Chart

• The condition of drunk drivers is not the majority of conditions when accidents occur
• Accidents occur on average on straight roads (without intersections) every hour
• Between 18–20 hours the causes of accidents are balanced between drunk and not drunk conditions

Highlight

With high levels of drunk driving occurring at 15–18 with a peak occurring at 17, and almost equal accidents occurring between drunk and not drunk drivers at 18–20, there needs to be more massive prevention against drunk drivers at that time.

CONCLUSION

Based on the findings in the accident data for 2021, we are making recommendations to the National Highway Traffic Safety Administration

1. Re-evaluate Light Condition Categories

There is a need for comprehensive training and communication with field personnel regarding the definition and limitations of light conditions (such as Dark — Not Lighted, Dark — Lighted, Dusk, Dawn, Dark — Unknown Lighting, Reported as Unknown, Not Reported, Other). This will ensure better and more accurate analysis of accident data.

2. Driver Education and Awareness Campaigns

Given that clear weather and daytime lighting conditions are associated with the highest number of accidents, the NHTSA can develop driver awareness campaigns that specifically focus on safe driving habits during favorable weather and lighting conditions. Education on maintaining safe following distances, using headlights during the daytime, and the dangers of excessive speed in clear weather can help reduce accidents.

3. Infrastructure Lighting

Considering the increased accidents in poorly lit dark conditions, the NHTSA can collaborate with relevant agencies to enhance lighting on roads with inadequate illumination. Sufficient lighting can improve visibility and reduce the risk of accidents.

4. Avoid Driving Under the Influence

The use of dangerous additives or consuming alcoholic beverages can impair concentration and significantly increase the risk of accidents, posing serious threats to the safety of the driver, passengers, and other road users. In 2021, NHTSA data recorded 10,138 fatalities caused by drunk drivers. This alarming figure urges the NHTSA to launch extensive public awareness campaigns and education programs using mass media, social media, advertising, or educational initiatives to raise awareness about the dangers of impaired driving.