Car Collisions

Take a minute to write an introduction that is short, sweet, and to the point. If you sell something, use this space to describe it in detail and tell us why we should make a purchase. Tap into your creativity. You’ve got this.

Guiding Questions

  • What can we find out about car accidents in Travis County?

  • Do adverse weather or road conditions have a significant impact on car accidents?

  • Does the influence of alcohol have a significant impact on car accidents

  • What hours are subject to highest volume of accidents?

Data Exploration

  • This data was collected by running multiple querys on the Texas Department of Transportation Website.

  • First we remove the spacing in the header columns to make the data easier to work with in SQL

  • There is inconcsistent data types in many columns; some containing a mixture of numbers and ‘no data’.

  • There is multiple records for each car incident: Crash ID, this represents multiple people involved in a single incident, this will need to be accounted for.

Data Analysis

  • We determine fields such as longitude, latitude, vehiclemodelyear, personage contain nulls.

  • We use substring to remove the numbers in columns like surface condition.

  • We add leading zeros to crash time field so each value is of consistent length and we can actually convert them data of time or datetime in the visualization.

  • We recognize inconsistency between deathcount and crash severity fields with some entrys with a death of one actually being designated with crash severity of minor injury or less.

  • We will will use crash severity of fatal to actually denote deaths.

  • We were unable to join the tables in a manner that made sense for the analysis and will instead union the data from multiple years to create a comprehensive dataset for visualization.

  • This analysis was run using Microsoft Sql Server.

Visualization

  • We create the visualization using Tableau.

  • We extract the data rather than use a live import since this data is historical and not regularly updated; this will import load speeds in our visualization.

  • We use count of distinct crashid to find how many incidents there are rather than just count because there are usually multiple individuals involved in a single collision.

  • We created calculated fields and then use those calculated fields to visualize year over year change in number of minor accidents, severe accidents, fatal accidents, etc.

  • We create a set to group driver by BAC over the legal limit of .08 and under the limit of .08

  • We create filters allowing the user to drill down accidents by road conditions, time of day, under influence of alcohol vs not, etc.

Conclusions

  • The ratio of car invidents to county population seems consistent with national averages.

  • There is a minor increase in accidents of all types from 2021 to 2022.

  • Most accidents happen in clear weather with dry road conditions.

  • Most accidents happen during the evening traffic hours, however most accident involving intoxicated drivers occur around 2 am.

  • Many fatal collisions occured on Interstate 35.

Access Project Files