Please click here to take a look at my data set. As you can see, it includes

  • All civil and commercial aviation accidents of scheduled and non-scheduled passenger airliners worldwide, which resulted in a fatality (including all U.S. Part 121 and Part 135 fatal accidents)
  • All cargo, positioning, ferry and test flight fatal accidents.
  • All military transport accidents with 10 or more fatalities.
  • All commercial and military helicopter accidents with greater than 10 fatalities.
  • All civil and military airship accidents involving fatalities.
  • Aviation accidents or incidents of noteworthy interest.

All together, I have almost 85 years of aviation accidents. You have to agree it is a lot of data. As expected, the tool I am using , Carto DB, does not accept the format the data is provided in on Github. First I almost pushed the panic button while imagining the amount of time needed to transfer it to Excel, but luckily, Hannah Aizenman suggested I download and debug it, and then see if any manual cleaning is required. Downloading took a long time. Unfortunately, I do not know how to debug it yet. Therefore, for my presentation I have entered eight years of data into Excel spreadsheet manually.

Screen Shot 2015-11-29 at 11.52.49 PM

The categories I am working with are

Date:  Date of accident,  in the format – January 01, 2001
Time:  Local time, in 24 hr. format unless otherwise specified
Airline/Op:  Airline or operator of the aircraft
Flight #:  Flight number assigned by the aircraft operator
Route:  Complete or partial route flown prior to the accident
AC Type:  Aircraft type
Reg:  ICAO registration of the aircraft
cn / ln:  Construction or serial number / Line or fuselage number
Aboard:  Total aboard (passengers / crew)
Fatalities:  Total fatalities aboard (passengers / crew)
Ground:  Total killed on the ground
Summary:  Brief description of the accident and cause if known

At this moment, I do not have enough data to make any conclusions, but questions have already started to arise. For example, is there a connection between the aircraft type and the number of accidents? De Havilland, Fokker, and KLM seem the most popular for now.

To better understand my data, I chose Carto DB as my tool. In the near future I should be able to see in what part of the world the most airplane accidents happened. For now, my map looks like this

Screen Shot 2015-11-29 at 10.49.39 PM

When clicked on a dot info about the accident appears

Screen Shot 2015-11-29 at 10.57.34 PMI

Ideally, I would like to have every ten years appear in different colors on the map. This kind of visualization should provide a deeper insight in my data.

Screen Shot 2015-11-29 at 11.08.16 PM

Problems Encountered

I knew that Carto DB mostly recognized large cities. Surprisingly, it is sometimes able to find towns, too. So how do you georeference something that did not appear on the map? You need to go to the map’s data and look for what exactly did not show up. Then, go back to the map and search for the town you were looking for. At the bottom of the menu there is an “add” feature. Click it and click twice at the location you need. You can enter data manually. This is how I dealt with the little towns situation. When a plane accident over Gibraltar had to be georeferenced, I simply clicked twice on the map at the approximate location of the catastrophe. A new dot appeared and I entered the details manually.

Sometimes Carto DB is wrong. Spring Rocks Wyoming is in the USA, not Jamaica. A few other dots proved wrong, too. Do not know how to deal with this yet. Will ask digital fellows for advise.

Only three lawyers are available on Carto DB. It appears you have to pay for the rest. Good Destry told us about the two free tryout weeks:).

I have 85 years of data. In the first 8 years out of 122 accidents 24 did not get georeferenced. The amount of those in 85 years will be overwhelming. To enter all that data manually would take forever. I wonder if I could leave my map a little imperfect… Something worth asking our professors.

In general, it was rather interesting to research aviation accidents data. Now I know most tragedies happen due to bad weather. Conclusion: never complain about plane delays in winter!

May we stay safe, and may there be less tragedies in the world.