Airline Fleet Age by Route Dashboard

Defining Question

Inspiration: When flying from Atlanta to Honolulu in 2021, I noticed that my flight seemed a little bit “aged”. While other routes had upgraded their aircraft, it seemed as if the aircraft I was taking was at least 20 years old. Therefore, I was wondering if other airlines used an aging fleet to fly on tourist-focused flights, leaving the updated aircraft for business routes.

The dashboard was created to look at all of the flight routes in 2022 and provide information about which airlines fly which routes. However, my focus was more about the average of each airline’s (or carrier’s) fleet.

Collecting Data

There are three sources of data that is needed for this project. 

The first set of data is the actual flight data of flight (in this case, we are looking at flights in 2022). This provides all of the flight information for each flight by the major carriers in 2022. What’s important with this data is that it also lists the actual aircraft used for each flight. Here is a link to the data.

The second set of data, called the Aircraft Registry, connects the tail number (or “n” number) for an aircraft to both the owner and year that the aircraft was build. This will identify each aircraft that is use in 2022, and give us an idea of the age of each aircraft. Here is a link to the data.

The third set of data will identify the type of aircraft used, as well as the amount of passengers an aircraft can carry. In the Aircraft Registry data, aircraft capacity and type is not given. Instead, a code is given for each type of aircraft. This data set will connect the aircraft to the type. Here is a link to the data.  

Data Wrangling

While most of the data that is used for this set is clean for what it is needed, there is still work that needs to be done. When downloading flight data, it is only available for each month. Therefore, to get all of 2022’s flights into one dataset, a union needed to be conducted with each month. 

In addition, a left join was done with the flight data and aircraft registry data, as well as the aircraft registry and aircraft type data. The GitHub link to the left to get details about the joins of each of the datasets, or click here.

Analyze and Interpret Data

While the main question was about the fleet age was to determine the age of the flights that go from Atlanta to Honolulu, it can also be used to determine the age of the fleet from other routes as well. In the case of the ATL-HNL flight (with the data shown below), the flight that I took (operated by Delta) had an average fleet age of nearly 19 years old. However, it was not the oldest fleet, as that would be United Airlines. Still, the fleet of Southwest, Hawaiian, and American Airlines were much newer.

In addition, the total segments flying from each origin, and to each destination, as well as available seats, gives a better idea of which airline can provide more capacity, as well as a younger fleet.