Visualizing the Collapse in Traffic Collisions During Stay-at-Home Orders

And how traffic data impacts excess deaths, one proxy for COVID-19 mortality

Over 35,000 people die in car crashes annually in the U.S. Another three million are injured. The closing of the U.S. economy in mid-March of 2020 dramatically reduced traffic across the United States. I created a visualization to document the accompanying reductions in traffic collisions in nine U.S. cities. The purpose was to assess the magnitude of these reductions, how they vary across cities, over time, and over geography within the cities.

What does the viz show?

Collisions drop a lot. Across nine cities, the average reduction in collisions for the period from mid March to late June in 2020 compared to the same period in prior years was about 50%, ranging from 69% in New York City to 41% in Cincinnati. A 50% drop in collisions may not correspond to a 50% drop in the number of deaths or injuries, particularly if collisions dropped in city centers where death and injury rates may be lower due to lower speeds. Still, cutting collisions in half is bound to have some effect on deaths and injuries. It is also worth noting that the drop in collisions is substantially larger than the discounts that national car insurance companies gave to customers during stay-at-home orders. For example, StateFarm gave an average discount of 11%; GEICO and Allstate 15%; Farmers gave back 25% in April and 15% in May; Progressive offers 20% discount. I suspect that these companies will have a profitable second quarter with drops in claims far outpacing the drops in revenue.

Collisions drop everywhere. The variation in collision reductions across cities is relatively modest. The reduction was the largest in New York City — by far the hardest hit city by the COVID-19 infections during March and April. However, even in cities such as Austin and Los Angeles where infections were relatively modest during those months, reductions in collisions were 49% and 42% respectively.

Collisions drop mostly in city centers. The comparison of densities plots of collisions between the same periods in 2020 and 2019 shows that collisions dropped mostly in the center of cities. While the 2019 densities have clear hot spots in city centers, the 2020 densities appear more uniform.

Collisions are on the rise. While collisions in most cities remain below their pre-2020 level, they have risen from their initial lows recorded in late March.

Where does the data come from?

I used the open data platforms of cities that report up-to-date information on traffic collisions. These include New York, Los Angeles, Chicago, Washington D.C., Seattle, Denver, Cincinnati, and Austin. These cities provide information on each collision within the city including date, time, location, and in some cases the number of injuries and fatalities. This data is generally reported the next day. I also included data from Boston though its collisions include only those that required “a response from a public safety agency,” and its data is updated only through May 31. Including Boston, I have information for nine cities with a combined population of about 20 million. There are other cities with open data platforms that post collisions data but are not updated regularly. For example, Philadelphia, Detroit, and San Jose report crash data with significant delays, and thus can not be used to assess the impact of the stay-at-home orders. Other cities such as San Diego and Dallas do not report detailed locations of each collision.

Each city reports the data in a slightly different format. The code that reads and combines the data for the nine cities is available here. When reading the data, the code links directly to cities’ open data platforms making updates easy. The output of the code are two datasets: daily crashes.csv which is the daily count of collisions and for some cities the daily count of fatalities. This data is used to generate the line graphs. The second data set is map.csv which includes one row for each collision for the period from March 15 until the maximum date in the data for years 2019 and 2020. This data is used to generate the collision density maps.

How did I make the viz in Tableau?

I created the visualization as a Tableau dashboard. You can download the workbook here. The line graphs on the left side of the visualization plot collisions in different years against the same date horizontal axis. This effect is achieved by creating a date field that uses the DATEADD() function to shift each year to 2020. Year of date is then added to the detail to generate one line for each year in the data. I used a table calculation to smooth the lines with a seven day moving average.

The density maps on the right side are made up of separate sheets for each city. The separate sheets ensure that each map is zoomed on its city. I used gray-scale WMS open street maps to avoid the default Mapbox logo repeated on each thumbnail sized map. I placed one OpenStreetMap copyright as a text box under all of the maps. Map backgrounds are 40% washed-out to highlight the changes in the densities.

The middle section is a city cross-tab of formatted labels. Throughout the dashboard I used vertical and horizontal containers to maintain control of where each object is located. For example, the midsection with headings is a horizontal container with the leftmost itself a vertical container containing a title (“Seven-day Moving…) and a legend. All sheets have grey backgrounds to blend nicely with light grey maps.

So what?

Changes in traffic collisions can help us understand the true number of deaths from COVID-19. Since many COVID-19 cases go undiagnosed, the CDC reports excess mortality from all causes as one possible measure of COVID-19 deaths. This would be a good measure if the only thing different this year compared to previous years was COVID-19. However, many things are different this year compared to previous years. The significant drop in traffic collisions documented in this visualization has the opposite effect of COVID-19. It lowers mortality, making the excess mortality from all causes statistic understate the mortality due to COVID-19.

More generally, the visualization shows that when cities make their data available to the public, we can better understand the rapidly changing world around us — whether it is the adequacy of car insurance discounts, or the true number of COVID-19 deaths.