Analyzing NTSB Data

Fans of *Mayday: Air Disasters* are more than used to seeing the NTSB step in whenever there is an incident in the United States or one involving an aircraft built in the United States (for example, a Boeing).

The National Transportation Safety Board is an independent federal agency whose mission is:

"To make transportation safer by conducting independent accident investigations, advocating for safety improvements, and deciding on certification appeals by pilots and mariners."

Although its scope of work covers the entire transportation system, it has been a key player in the development of the aviation industry.

On its website, https://www.ntsb.gov/about/pages/default.aspx, there are various open-data databases available for download. In particular, a few weeks ago I decided to download the database on aviation accidents.

Although the data is not real-time, it is fairly up-to-date and includes records dating back to 1962. The database contains civil aviation accidents and incidents investigated by the NTSB, and the data can be downloaded as XML or CSV: https://www.ntsb.gov/_layouts/ntsb.aviation/index.aspx

After downloading them as CSV files, I loaded them into a database created with MySQL using the "Data Import Wizard" in MySQL Workbench. The data quality was quite good, so I didn’t have to perform any major preprocessing. The most notable step in this process was adding a new field to handle the date as a Date type.

With the database now loaded, we can start experimenting with some interesting queries:

NUMBER OF INCIDENTS WITH MORE THAN 100 INJURED, BY MAKER AND MODEL

NUMBER OF INCIDENTS BY FLIGHT PHASE

 

What do you think of this data? What other queries would you find interesting to run?

See you next time!

 

The database, queries, and other resources are available at: https://github.com/sophiamaiolo/NTSB_Data

Tools used:

Awesome Table: https://awesome-table.com/

ChartBlocks: https://app.chartblocks.com