How many active Cessna 172 aircraft (or any other make/model) are there in the United States?

Question

I define "active" in accordance with the FAA's definition: "An active aircraft is one that flies at least one hour during the year."

The Bureau of Transportation Statistics (BTS) provides a summary of aircraft registered based on aircraft configuration (fixed wing, rotorcraft, single/multi engine, turbo-prop, jet, etc.) and GAMA has a report on the annual shipments, but not the totals of active aircraft.

The FAA's downloadable aircraft registration database is cryptic to the point where I can't figure out what a specific entry means, and the online search interface returns data for one search at a time.

Is there a data source that provides the count of a given make and model across the active fleet of aircraft in the US?

(PS: If there is some trick to decrypting the FAA registration database that would be be great to know as well!)

Answer 1

The FAA database

In the zip-file you find a number of text files, and a PDF file called 'ardata.pdf'. This file is the key to the other documents. The 'master.txt' file contains all aircraft records and 'ACFTREF.txt' contains all aircraft types and their id's in the register.

The third column in 'master.txt' is 'MFR MDL CODE', which corresponds to the first column in 'ACFTREF.txt' called 'CODE'. By finding the right aircraft codes you can find the number of corresponding aircraft in the 'master.txt' file.

My procedure

I imported both text files in Excel and filtered the aircraft file to contain only the Reims and Cessna 172 variants. This resulted in a list of 54 variants. Then I filtered the master file using the codes I found, resulting in 20,789 aircraft. This number is the total number of Cessna 172s that have a registration assigned to them in the United States.

However, not all aircraft with an assigned registration are actually active. For example, N-numbers can be assigned to reserve a registration while the aircraft is being transferred/imported or may be cancelled while the N-number still is assigned. This is also included in the 'master.txt' file as the 'STATUS' column.

I decided to only look for valid registrations (indicated by the letter 'V'), which returned 18,914 results. So there are 18,914 civil Cessna 172s with a valid registration in the United States. The actual number of Cessna 172s in the US will be lower, since US-registered aircraft may also be operated in other countries. And some of the aircraft with a valid registration may not be active, but I assume that number is low, so a guess of 18,000-19,000 active C172s will be accurate.

Answer 2

I'm accepting Jordy's answer as it provided the key to getting this figured out.

Below is a first draft of the analysis I was trying to complete. There are some inconsistencies with how the FAA's registration database refers to manufacturer and model; the main data modification I had to make was to combine the different variants of a given model into a single label. For example: All the Cessna 182 variants (182N, 182RG, T182, T182T, etc.) are counted under the Cessna 182 label. Additionally, this only shows the aircraft with a valid registration ("V" in Status Code column).

Some additional cleaning remains to make sure the counts are exact, hence this only being a "draft".

@jamesqf: Your comment is well taken. The assumption here is that if you go through the trouble of maintaining your registration, you'd at least fly one hour a year.