OpenAirDocs.org

NTSB findings

What NTSB found in 24,558 investigations, coded in a tree of 5 categories, 21 subcategories, 130 sections and 792 subsections. NTSB marks the findings that are part of the probable cause (a cause or contributing factor); the rest are other findings. NTSB publishes the codes’ names but not their definitions.

Accident and incident, 49 CFR 830.2NTSB coding manual, before 2008 (PDF)

By category

Cases with a finding in each category, per year: in green, those with one in the probable cause. Findings are coded with the final report, often a year or more after the event, so the latest years are incomplete.

Aircraft
20082026

19,176 cases · 18,374 in the probable cause

Personnel issues
20082026

18,967 cases · 18,419 in the probable cause

Environmental issues
20082026

10,285 cases · 5,684 in the probable cause

Not determined
20082026

2,247 cases · 2,176 in the probable cause

Organizational issues
20082026

504 cases · 388 in the probable cause

Query with SQL
duckdb -csv -c "SELECT left(f.\"findingCode\", 2) AS category, max(f.\"tier1Name\") AS name, year(c.\"eventDate\") AS year,
    count(DISTINCT f.case_mkey) AS cases,
    count(DISTINCT f.case_mkey) FILTER (WHERE f.\"inProbableCause\") AS in_probable_cause
  FROM 'https://openairdocs.org/data/latest/ntsb_findings.parquet' f JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = f.case_mkey
  WHERE c.\"eventDate\" >= DATE '2008-01-01' AND f.\"findingCode\" GLOB '[0-9][0-9]*'
  GROUP BY 1, 3
  ORDER BY 1, 3"

Prints the counts as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

Most often in the probable cause

All cases
Fatal cases
Query with SQL
duckdb -csv -c "-- The page charts the top 5 by in_probable_cause, and by fatal_cases
  SELECT left(f.\"findingCode\", 8) AS finding, max(f.\"tier4Name\") AS name, max(f.\"tier1Name\") AS category,
    count(DISTINCT f.case_mkey) AS cases,
    count(DISTINCT f.case_mkey) FILTER (WHERE f.\"inProbableCause\") AS in_probable_cause,
    count(DISTINCT f.case_mkey) FILTER (WHERE c.\"highestInjuryLevel\" = 'Fatal') AS fatal_cases
  FROM 'https://openairdocs.org/data/latest/ntsb_findings.parquet' f JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = f.case_mkey
  WHERE f.\"findingCode\" GLOB '[0-9][0-9]*'
  GROUP BY 1
  ORDER BY in_probable_cause DESC, cases DESC"

Prints the counts as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

Every finding

The whole tree, with the cases under each code. Open a level to see the one below.

5 categories, 21 subcategories, 130 sections and 792 subsections
Query with SQL
duckdb -csv -c "SELECT l.level, left(f.\"findingCode\", l.level * 2) AS code,
    max([f.\"tier1Name\", f.\"tier2Name\", f.\"tier3Name\", f.\"tier4Name\"][l.level]) AS name,
    count(DISTINCT f.case_mkey) AS cases,
    count(DISTINCT f.case_mkey) FILTER (WHERE f.\"inProbableCause\") AS in_probable_cause,
    count(DISTINCT f.case_mkey) FILTER (WHERE c.\"highestInjuryLevel\" = 'Fatal') AS fatal_cases
  FROM 'https://openairdocs.org/data/latest/ntsb_findings.parquet' f JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = f.case_mkey CROSS JOIN range(1, 5) AS l(level)
  WHERE f.\"findingCode\" GLOB '[0-9][0-9]*'
  GROUP BY 1, 2
  ORDER BY code"

Prints the counts as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

NTSB has coded findings and defining events since 2008. Earlier cases used its older occurrence and cause codes, so they aren’t counted here.