Basic Analysis on Flights Dataset
This is the first of three blog posts from this summer internship project showing how to answer questions concerning big datasets stored in MongoDB using MongoDB's frameworks and connectors.
The first dataset explored was a domestic flights dataset. The Bureau of Transportation Statistics provides information for every commercial flight from 1987, but we narrowed down our project to focus on the most recent available data for the past year (April 2012-March 2013).
We were particularly attracted to this dataset because it contains a lot of fields that are well suited for manipulation using the MongoDB aggregation framework.
To get started, we wanted to answer a few basic questions concerning the dataset:
- When is the best time of day/day of week/time of year to fly to minimize delays?
- What types of planes suffer the most delays? How old are these planes?
- How often does a delay cascade into other flight delays?
- What was the effect of Hurricane Sandy on air transportation in New York? How quickly did the state return to normal?
The first order of business was uploading the dataset into MongoDB. The downloaded data sets are in monthly csv files of 150MB each. Many fields such as diversions or cancellations have blank values. mongoimport would include a lot of these blank field values. To avoid field sparsity, we used an upload Python script to weed out the unnecessary fields and incomplete entries. This means that all fields which are blank or zero will not be included.
After deliberation, we decided to keep most of the flight information aside from the diverting airports (we'd come to realize that it was better to include more fields than few). The fields we chose to keep can be found in this gist. One important distinction that must be made is the difference between fields of depDelay, the difference in minutes between the scheduled departure time and actual departure time, versus arrDelay, the difference in minutes between the scheduled arrival time and the actual arrival time. Some of the other more important fields of our schema are:
| Field | Description | Sample |
|---|---|---|
| date | Flight Date | ISODate("2012-08-10T00:00:00Z") |
| carrierId | Unique Carrier Code | "EV" |
| tailNum | Unique Tail Number of Aircraft | "N607LR" |
| origAirportId | Origin Airport | "IAH" |
| origStateId | Origin State | "TX" |
| destAirportId | Destination Airport | "DTW" |
| destStateId | Destination State | "MI" |
| crsDeptTime | Scheduled Departure Time | ISODate("2012-08-10T14:55:00Z") |
| depDelay | Minutes Delayed for Departure | 421 |
| depTime | Time of Departure | ISODate("2012-08-10T21:56:00Z") |
| crsArrTime | Scheduled Arrival Time | ISODate("2012-08-10T18:46:00Z") |
| arrDelay | Minutes Delayed for Arrival | 398 |
| arrTime | Time of Arrival | ISODate("2012-08-11T01:24:00Z") |
| cancellationCode | Reason for Cancellation (0 for not cancelled, 1 for carrier, 2 for weather, 3 for national air system, 4 for security) | 0 |
In total, we imported 6,155,752 recorded trips into the flying database's flights collection.. It took 2 hours to upload this dataset using 1TB of space and 7GB of RAM with again with individual document insertions.
In order to find out the ages of the aircrafts that caused the most delays, we had to download a different dataset (MASTER.txt --131MB). MASTER.txt contained information about the MFR-Year of an aircraft, the year the aircraft was created.
We wrote a script, masterNNumbers.py, with the function addAge that adds an age field into the documents for which a corresponding tailNum (also known as the n-number) exists in the current flights collection in the flying database. Since a single aircraft can have different departure and arrival times, an aircraft with a specific tail number must be updated in several documents. We used the multi flag to enable multiple updates per aircraft.
So what can we do with this data? We started by answering the questions above with simple aggregations and queries. Then we moved to more investigations, such as how Hurricane Sandy affected flights going in and leaving New York? Finally, we used the D3 JavaScript library to visualize some of our results.
Let us assume that the delays mentioned in this question are the arrival delays, as that would matter more to the traveler.
Using the following query, where field can be replaced by hour, week, month, or any combination therefore:
db.flights.aggregate(
{"$group" : {"_id" : <field>, "delays" : {"$avg" : "$arrDelay"}}}
, {"$sort" : {"delays" : 1}}
);
The best time to leave during the day with the least average arrival delays is in the morning:
| Hour of Day | Average arrDelay in Minutes |
|---|---|
| 5 | -2.954266867847939 |
| 6 | -2.900575701429328 |
| 2 | -2.3468085106382977 |
| 7 | -2.024416791254782 |
| 4 | -0.5765124555160143 |
This advice to catch flights in the morning to avoid delays is also supported when we graphed the delays vs time of week. The least delays (white) occurred on Wednesday and Thursday mornings with arrival 7 minutes early on average, while the most delays (blue) happened the evenings of Thursdays, Fridays, and Sundays with arrivals being 10 minutes late on average.
Additionally, if we look at the delays over the entire year of our data set from April 2012 to March 2013, we notice that the summer and Christmas have the most delays (red). This is probably due to the heavier flights traffic during those times.
We used this query to obtain the n most delayed aircrafts (with ages):
db.flights.find({"age" : {"$exists" : true}},
{"_id" : 1
, "arrDelay" : 1
, "depDelay" : 1
, "carrier" : 1
, "origCity" : 1
, "destCity" : 1
, "age" : 1}).sort({"arrDelay" : -1, "depDelay" : -1}).limit(n);
In order to make this query run faster, we built a compound index on both attributes arrDelay and depDelay, {arrDelay : -1, depDelay : -1}. See the indexing paragraph summary for more information.
Another statistic we sought after was the number of cascading delays caused by an initial delay, as calculated in cascadingDelays.py. For every flight which started at least on time and arrived late, the Python script finds all the flights scheduled after that particular flight's scheduled arrival and searches for the number of immediate, consecutive delays. One consecutive delay for aircraft 123 for flight ABC is determined to be the scheduled flight immediately after flight ABC also on the aircraft 123, where the delay reason is specified as a late aircraft arrival, and this next flight both departs and arrives late.
Initially, for every consecutive delay, the program recursively searched for the next scheduled flight to determine whether it was delayed. If it was, it would search for the next one, and so on. Unfortunately this reached Python's maximum recursion limit, and it caused too many queries, which also slowed down the performance. Instead, we used another strategy: we first found all the aircraft that were delayed; then for each delayed aircraft, we queried the next 10 scheduled flights, and iterated through these results to find the number of consecutive flights delayed. Essentially, we wrote an iterative version of the initial recursive solution. This proved to be more efficient. See cascadingDelays.py for more documentation on the function.
Interestingly, each starting flight causes on average only 0.189 consecutive trip delays for the same aircraft.
Hurricane Sandy devastated much of New England last October, and New York City experienced one of its worst natural disasters.
Up to this point, our calculations mostly considered scheduled flights. Thus, we looked at the number of scheduled flights throughout the period around Hurricane Sandy. However, flights are scheduled months in advance, obviously without the knowledge of Sandy, so this measurement of scheduled flights was incorrect.
Our data set documented which flights were cancelled due to weather, and the number of flights cancelled due to weather would be the measurement for Hurricane Sandy's effects.
The following aggregation returned the total number of NY flights cancelled due to weather for dates around which Hurricane Sandy occurred.
db.flights.aggregate(
{"$match" : {"cancelCode" : 2, "month" : 10, "$or" : [{"origStateId" : "NY"}, {"destStateId" : "NY"}]}}
, {"$group" : {"_id" : "$dayOfMonth", "sum" : {"$sum" : 1}}}
, {"$sort" : {"_id" : 1}}
);
We can tell that the dates during Hurricane Sandy (October 25 - October 31) have significantly higher weather delays (over 1000x times). The only caveat to this data is that there's another super storm that a second super storm hit on November 4th, which caused numerous cancellations on its own. Therefore, the data is not isolated and wholly controlled.
| Date | Number of Cancelled Flights In or Out of NY |
|---|---|
| October 25 | 0 |
| October 26 | 0 |
| October 27 | 0 |
| October 28 | 246 |
| October 29 | 1316 |
| October 30 | 1239 |
| October 31 | 951 |
| November 1 | 361 |
| November 2 | 70 |
| November 3 | 2 |
| November 4 | 505 |
| November 5 | 143 |
| November 6 | 13 |
The following query finds 10 states with the most departing flights:
db.flights.aggregate({"$group" : {"_id" : "$origState" , "flew" : {"$sum" : 1 }}}
, {"$sort" : {"flew" : -1 }}
, {"$limit" : 10}
);
| State | Number of Flights Leaving from an Airport in this State |
|---|---|
| California | 760113 |
| Texas | 725487 |
| Florida | 446024 |
| Georgia | 411166 |
| Illinois | 410492 |
| New York | 268986 |
| Colorado | 260408 |
| Arizona | 206408 |
| North Carolina | 204447 |
| Virginia | 188618 |
A similar query with $destState replacing $origState will show the 10 states sorted by the by most arriving flights.
The results of these queries have been mapped in D3. The color key denotes yellow to have fewer flights and green states have more flights in the past year. There was no record of any flights to or from Delaware in the past year. The first map is solely of flights originating from a state and the second map is of flights destined for a state.
On a similar note, we also want to discover which states experience the worst delays. The following query finds the states with the highest average delays, which is also represented in the graph below:
db.flights.aggregate(
{"$group" : {"_id" : "$origStateId" , delay : {"$avg": "$depDelay"}}}
, {"$sort" : {"delay" : -1}}
, {"$limit" : 10}
);
Yellow in the maps represents no delay while red means extreme delay. The first map summarizes the states for departure delay while the second map summarizes the states for arrival delay.
A particular point of concern for the data was the presentation of departure and arrival times. In the given data set, they were shown in military time, which was not convenient for our calculations. Instead, the script changed them to BSON date values. Next day arrivals, or delays causing next day departures, caused problems converting from time-of-day to a datetime. The problem was rectified in rectified by the correctDays() function in inputMongo.py.
MongoDB stresses the importance of indexing, which we experienced first-hand. As our calculations gradually migrated toward delays and aggregations, it became evident that certain fields needed indexes. In the end, we ended up with indexes for the following fields:
- date
- arrDelay
- origStateId
- destStateId
- arrDelay, depDelay
On average, each index took around 2 minutes on average to create. But their sizes ranged from 120MB to 200MB. This is a worthwhile trade-off since all our queries ran much faster after creating the indexes.
Note: We created a compound index on the fields, arrDelay and depDelay ({arrDelay : -1, depDelay : -1}). Furthermore, we created an index on depDelay alone but not on arrDelay because the prefixes of the compound index can satisfy queries on any prefix of its fields. See this page for more details.
Depending on the size of the machine and the relevant indexes, the query times differed significantly. Consider the query for all flights which depart on time but arrive late:
db.flights.aggregate(
{"$match" : {"arrDelay" : {"$gt" : 0}, "depDelay" : {"$lte" : 0}}}
, {"$group" : {"_id" : "$origStateId", "numFlights" : {"$sum" : 1}}}
, {"$sort" : {"numFlights" : -1}}
, {"$limit" : 5}
);
This query would take about 20 minutes on a medium EC2 instance. Creating the compound index took 2 minutes and 20 seconds. However, with the index, the query finished in less than 5 seconds indexed.
- Make the field names shorter — saves a significant amount of document storage space. But the caveat is that shorter field names are less descriptive than longer ones. It would also not affect any of the queries we created.