⚙️ How-To Aircraft registrations Joins & relationships Notebook By · Updated

How to Join FAA Aircraft Records to Their Make and Model

On this page

Problem statement

The FAA registry's main file says what an aircraft is only as a seven-character code, MFR MDL CODE. Manufacturer, model, seats, weight class and engine count are in a second file, ACFTREF.txt, and the join between them is straightforward, but the names it returns are not: one manufacturer appears under several spellings, and one model family under many model strings. This page joins the two with the 16 October 2026 files, checks the join, and turns model strings into families and seat counts.

Quick answer

Read both with faa() so the codes keep their leading zeros, and merge with a many-to-one check:

v = reg[reg["STATUS CODE"] == "V"]
m = v.merge(ref, left_on="MFR MDL CODE", right_on="CODE", how="left", validate="many_to_one", indicator=True)
print(m._merge.value_counts().to_string())
print(m.groupby(["MFR", "MODEL"]).size().nlargest(6).to_string())
_merge
both          310844
left_only          0
right_only         0
MFR                 MODEL    
PIPER               PA-28-140    4155
CIRRUS DESIGN CORP  SR22         3927
CESSNA              172M         3534
PIPER               PA-28-180    3451
                    J3C-65       3149
CESSNA              172N         3098

All 310,844 valid registrations find their code in ACFTREF, and validate="many_to_one" confirms each code appears there once. The most common single model string is the Piper PA-28-140, then the Cirrus SR22 and Cessna 172M.

Horizontal bars of the twelve most common model strings after the join, led by the Piper PA-28-140, Cirrus SR22 and Cessna 172M, and including the Zipline P2 delivery drone. Horizontal bars of the twelve most common model strings after the join, led by the Piper PA-28-140, Cirrus SR22 and Cessna 172M, and including the Zipline P2 delivery drone.
The most common model strings among valid registrations.

The code, the names and the families

1. What the seven characters hold

print(ref[["CODE", "MFR", "MODEL", "NO-SEATS", "AC-WEIGHT"]].sample(4, random_state=3).to_string(index=False))
print(f"codes: {ref.CODE.nunique():,} unique of {len(ref):,} rows; manufacturer part (first 3): {ref.CODE.str[:3].nunique():,}")
   CODE             MFR          MODEL NO-SEATS AC-WEIGHT
05676LB DROUIN REJEAN J PANTHER 2 PLUS      002   CLASS 1
0560301 HOLMES MILTON N          EAGLE      002   CLASS 1
060002M  AVIADESIGN INC            A16      002   CLASS 1
05648XR KAISER HOWARD R           KR 2      002   CLASS 1
codes: 94,115 unique of 94,115 rows; manufacturer part (first 3): 559

ACFTREF has 94,115 codes, one row each. The first three characters identify the manufacturer, 559 of them, and the rest the model; amateur builders get their own codes, which is why individuals' names appear as manufacturers. NO-SEATS is text with leading zeros, so convert it before comparing.

2. One maker, several names

cess = m[m.MFR.str.contains("CESSNA", na=False)]
print(cess.MFR.value_counts().to_string())
MFR
CESSNA                            71734
CESSNA AIRCRAFT CO                  381
CESSNA ECTOR                         20
CESSNA/AIR REPAIR INC                 8
CESSNA WREN                           4
CESSNA CLAASSEN                       2
CESSNA ALLARDICE                      1
CESSNA/RAVEN                          1
CESSNA CONROY                         1
CESSNA-MACONE                         1
CESSNA/INLAND VALLEY AVIA             1
CESSNA-MORSE                          1
CESSNA-LOOMIS-BUEHN                   1
CESSNA-VANSANT INTERPRISES-INC        1
CESSNA/WHITEHOUSE                     1
CESSNA/WEAVER                         1

Cessna's 71,734 valid aircraft are under CESSNA, but 381 more are under CESSNA AIRCRAFT CO and a tail of modified or rebuilt aircraft under names such as CESSNA/AIR REPAIR INC. Grouping on the exact string splits the manufacturer; match on a prefix and decide case by case about the rebuilds.

Horizontal bars of the 19 Cessna 172 model strings, from 172M with 3,534 and 172N with 3,098 down to 172Q with 13. Horizontal bars of the 19 Cessna 172 model strings, from 172M with 3,534 and 172N with 3,098 down to 172Q with 13.
One family, 19 model strings: the Cessna 172, 19,191 aircraft.

3. Model families, not model strings

m["family"] = m.MODEL.str.extract(r"^(\d{3}|[A-Z]+-?\d+)", expand=False)
c172 = m[m.MFR.str.contains("CESSNA", na=False) & (m.family == "172")]
print(f"Cessna 172 rows: {len(c172):,} across {c172.MODEL.nunique()} model strings; top: {c172.MODEL.value_counts().head(4).to_dict()}")
Cessna 172 rows: 19,191 across 19 model strings; top: {'172M': 3534, '172N': 3098, '172S': 1909, '172': 1777}

The Cessna 172, the most-produced light aircraft, is 19,191 registrations across 19 model strings, led by the 172M, 172N, 172S and plain 172. As one family it outnumbers the Piper PA-28-140 more than four times.

Seats and weight classes

The data used on this page

The FAA's Releasable Aircraft Database, public domain, downloaded on 16 October 2026 and saved on this site, because the FAA replaces it every day. The saved copy leaves out the owners' names and street addresses and keeps five-digit ZIP codes; every other column is as published. With it come the Census Bureau's Vintage 2025 county population estimates, 2024 county boundaries and 2024 ZCTA gazetteer.

from pathlib import Path
import zipfile

import geopandas as gpd
import numpy as np
import pandas as pd
import requests

Path("data").mkdir(exist_ok=True)
# FAA Releasable Aircraft Database, 16 October 2026: MASTER.txt without the NAME, STREET, STREET2 and OTHER NAMES
# columns and with five-digit ZIP codes (MASTER-no-names.txt), ACFTREF.txt and ENGINE.txt as published; Census
# Vintage 2025 county estimates, 2024 cartographic boundary counties and 2024 ZCTA gazetteer. Saved here unchanged
# otherwise, because the FAA replaces the file daily.
# Live: https://registry.faa.gov/database/ReleasableAircraft.zip (needs a browser User-Agent),
# https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2024_Gazetteer/2024_Gaz_zcta_national.zip
ARCHIVE = "faa-aircraft-registry-2026-10-16.zip"
if not Path(f"data/{ARCHIVE}").exists():
    Path(f"data/{ARCHIVE}").write_bytes(requests.get(f"https://data.spatialworkflow.io/{ARCHIVE}", timeout=600).content)
Z = zipfile.ZipFile(f"data/{ARCHIVE}")


def faa(name):
    """One registry file: comma-separated, every field padded with spaces, a byte-order mark and a trailing comma."""
    d = pd.read_csv(Z.open(name), dtype=str, encoding="utf-8-sig", keep_default_na=False)
    d.columns = d.columns.str.strip()
    d = d.loc[:, [c for c in d.columns if c and not c.startswith("Unnamed")]]
    return d.apply(lambda c: c.str.strip())


reg = faa("MASTER-no-names.txt")
ref = faa("ACFTREF.txt")
TYPE_AIRCRAFT = {"1": "glider", "2": "balloon", "3": "blimp", "4": "fixed wing single engine", "5": "fixed wing multi engine",
                 "6": "rotorcraft", "7": "weight-shift", "8": "powered parachute", "9": "gyroplane", "H": "hybrid lift", "O": "other"}
REGISTRANT = {"1": "individual", "2": "partnership", "3": "corporation", "4": "co-owned", "5": "government", "7": "LLC",
              "8": "non-citizen corporation", "9": "non-citizen co-owned", "": "blank"}
Path("data/cb_2024_us_county_20m.zip").write_bytes(Z.read("cb_2024_us_county_20m.zip"))
counties = gpd.read_file("data/cb_2024_us_county_20m.zip")
STATEFP = counties.drop_duplicates("STUSPS").set_index("STUSPS").STATEFP
pop = pd.read_csv(Z.open("co-est2025-alldata.csv"), encoding="latin-1", dtype={"STATE": str, "COUNTY": str})
pop = pop[pop.SUMLEV == 50].assign(GEOID=lambda p: p.STATE + p.COUNTY)
print(f"FAA registry, 16 October 2026: {len(reg):,} aircraft, {len(ref):,} make-and-model codes")
FAA registry, 16 October 2026: 317,604 aircraft, 94,115 make-and-model codes

Example 1: seats

seats = pd.to_numeric(m["NO-SEATS"], errors="coerce")
print(pd.cut(seats, [0, 1, 2, 4, 6, 10, 20, 1000], labels=["1", "2", "3-4", "5-6", "7-10", "11-20", "21+"]).value_counts(sort=False).to_string())
NO-SEATS
1         17340
2         85906
3-4      104618
5-6       36864
7-10      21656
11-20     13292
21+       13498

Most registered aircraft have three or four seats (104,618) or two (85,906). 13,498 have 21 or more, the airliners.

Example 2: weight class by kind of aircraft

m["kind"] = m["TYPE AIRCRAFT"].map(TYPE_AIRCRAFT)
print(pd.crosstab(m.kind, m["AC-WEIGHT"]).loc[["fixed wing single engine", "fixed wing multi engine", "rotorcraft"]].to_string())
AC-WEIGHT                 CLASS 1  CLASS 2  CLASS 3  CLASS 4
kind                                                        
fixed wing single engine   216427      569      654      228
fixed wing multi engine     22446     8428    17798       10
rotorcraft                  22887     1298      518     2834
Bar chart of registered aircraft by number of seats: 104,618 with three or four, 85,906 with two, and 13,498 with 21 or more. Bar chart of registered aircraft by number of seats: 104,618 with three or four, 85,906 with two, and 13,498 with 21 or more.
Registered aircraft by number of seats from ACFTREF.

Weight class 1, the lightest, covers 216,427 of the single-engine aeroplanes; multi-engine aeroplanes spread across classes 1 to 3, and 2,834 rotorcraft fall in class 4, which the FAA's documentation defines as unmanned aircraft up to 55 pounds: drones again.