Technical writing

FHWA National Bridge Inventory: The Federal Database Behind 620,000 US Bridge Inspections

· AI Analytics
FHWABridgesInfrastructureTransportationFederal Data

The Federal Highway Administration National Bridge Inventory collects biennial condition ratings for every highway bridge in the United States—620,000 bridges covering structural sufficiency, deck ratings, superstructure, substructure, and channel conditions—and is the primary evidence base for federal bridge rehabilitation funding.

What the NBI is and why it exists

The National Bridge Inventory is a federal database maintained by the Federal Highway Administration under 23 CFR Part 650, Subpart C. It collects inspection condition ratings and inventory data for every public highway bridge in the United States that spans more than 20 feet. Coverage encompasses approximately 620,000 structures: highway bridges over rivers, ravines, and other roads; grade separation structures; and culverts meeting the length threshold. The data is collected by state departments of transportation under federally mandated inspection standards, submitted to FHWA annually, and published as a public flat-file dataset.

The legal authority for the NBI is the National Bridge Inspection Standards (NBIS), codified at 23 CFR Part 650. The NBIS emerged directly from a catastrophic bridge failure. On December 15, 1967, the Silver Bridge spanning the Ohio River between Point Pleasant, West Virginia, and Gallipolis, Ohio, collapsed during rush hour. Forty-six people died; 31 vehicles plunged into the river. The investigation found that the collapse originated from a stress-corrosion crack in an eyebar link—a defect that a routine inspection could have identified. Congress passed the Federal Aid Highway Act of 1968, which directed FHWA to establish a national bridge inspection program. The first NBIS regulations took effect in 1971.

The NBIS requires states to inspect all public highway bridges every 24 months under routine inspection protocols. Fracture-critical bridges—those where the failure of a single member would cause a partial or total collapse—require inspection at the same 24-month interval or more frequently if conditions warrant. A separate “in-depth inspection” cycle applies to bridges with identified concerns that require closer examination, potentially involving underwater inspection of submerged components, nondestructive testing, or load rating analysis. States must employ or contract qualified bridge inspection personnel who meet FHWA's qualification requirements.

As of the most recent full NBI cycle, approximately 46,000 bridges—roughly 7.5 percent of the inventory—are classified as being in poor condition based on FHWA's derived overall condition methodology. That figure, which receives significant attention in infrastructure policy debates, reflects a substantial improvement from the early 1990s, when more than 22 percent of bridges were classified as structurally deficient. The improvement reflects decades of targeted federal rehabilitation funding, though the remaining population of poor-condition bridges still represents structures that carry hundreds of millions of vehicle crossings annually.

The 0-9 condition rating scale

The core of the NBI data is the condition rating system. Inspectors assign numerical ratings on a 0-to-9 scale to four primary bridge elements: the deck, the superstructure, the substructure, and the channel and channel protection. The ratings are defined in the Recording and Coding Guide for the Structure Inventory and Appraisal of the Nation's Bridges (FHWA-PD-96-001), the technical reference document that governs NBI data collection.

The rating scale runs from 9 (“excellent condition”) down to 0 (“failed condition”). A rating of 9 describes a newly constructed element in perfect condition. Ratings of 7 and above indicate good condition, with minor deterioration that does not affect structural performance. A rating of 6 is “satisfactory condition”—minor defects but structural elements are functioning as designed. A rating of 5 is “fair condition” with minor section loss, cracking, spalling, or scour. A rating of 4, “poor condition,” is the critical threshold: primary structural elements have advanced section loss, deterioration, spalling, or scour. A rating of 3 is “serious condition,” with loss of section threatening structural integrity and requiring close monitoring. A rating of 2, “critical condition,” means the bridge has advanced deterioration of primary structural elements with imminent failure possible and requiring immediate remediation or closure. Ratings of 1 and 0 indicate failed or collapsed conditions.

The four rated elements capture distinct structural zones. The deck is the riding surface of the bridge—the concrete or steel plate that vehicles travel on. Deck deterioration manifests as cracking, spalling of concrete, delamination, corrosion of embedded rebar, and potholing. The superstructure encompasses the structural elements that support the deck and span between supports: steel girders, concrete beams, trusses, arches, and cables. Superstructure deterioration includes section loss from corrosion, fatigue cracking, and loss of composite action between structural members. The substructure covers the piers and abutments that transfer loads from the superstructure to the foundation: concrete piers, steel pipe piles, masonry abutments, and spread footings. Substructure deterioration includes concrete spalling, settlement, undermining from scour, and corrosion of steel elements. The channel and channel protection rating documents the condition of the streambed and banks beneath and adjacent to the bridge, including evidence of scour erosion, bank protection failure, and channel migration that could undermine foundations.

FHWA's derived overall condition classification—“good,” “fair,” or “poor”—is determined by the minimum rating among the deck, superstructure, and substructure for conventional bridges, or the culvert condition rating for culvert structures. A bridge is classified as poor if any of its primary structural element ratings falls to 4 or below. Critically, a bridge is classified as poor on the basis of a single low-rated element even if the other elements are in good condition. This methodology is deliberately conservative: a bridge with a badly deteriorated deck but an intact superstructure and substructure is still “poor” under the FHWA definition.

The Sufficiency Rating

Alongside the element condition ratings, the NBI includes a composite Sufficiency Rating for each bridge, expressed on a 0-to-100 scale. The SR is a formula-based metric that combines three factors: structural adequacy and safety (weighted at 55 percent of the total score), serviceability and functional obsolescence (weighted at 30 percent), and essentiality for public use (weighted at 15 percent). The structural adequacy component draws on the element condition ratings and load capacity. The serviceability component captures geometric deficiencies: lane widths narrower than current standards, insufficient vertical clearance for modern truck traffic, and approach roadway width that creates a bottleneck compared to the connecting roadway network. The essentiality component reflects traffic volume and the availability of alternative routes—a bridge carrying high traffic with no detour option scores higher on essentiality, which raises its SR.

The SR has direct regulatory significance for federal funding eligibility. A bridge with an SR below 50 is eligible for federal replacement funding under the Bridge Formula Program. A bridge with an SR below 80 is eligible for federal rehabilitation funding. These thresholds have driven the targeting of federal bridge expenditures for decades: state DOTs seeking federal reimbursement for bridge work must demonstrate SR thresholds or poor-condition ratings that qualify the structure under the applicable program.

Functional obsolescence is a concept distinct from structural deficiency that the SR captures in its serviceability component. A functionally obsolete bridge is structurally sound—its deck, superstructure, and substructure may all rate 6 or above—but it fails to meet current geometric or load standards. Common obsolescence conditions include lane widths of 10 or 11 feet where current standards call for 12-foot lanes, vertical clearance of 13'6” or less that legally excludes many truck configurations, load postings below the legal limit weight that require trucks to detour, and bridge widths too narrow for a bridge on a road that has been widened since the structure was built. Functionally obsolete bridges are not categorized as “poor” under the FHWA condition system, but they are candidates for rehabilitation or replacement funding under the sufficiency rating framework.

Notable bridge failures

Bridge failures in the United States are rare in absolute terms, but the ones that occur under NBI oversight have shaped the inspection and funding framework in lasting ways.

The collapse of the I-35W bridge in Minneapolis on August 1, 2007, killed 13 people and injured 145 when a 1,000-foot section of the eight-lane bridge over the Mississippi River fell during evening rush hour. The NTSB investigation found the probable cause was an undersized gusset plate in the original 1967 design— a design error that had existed since construction—combined with the additional dead load of construction equipment and materials staged on the bridge deck at the time of the collapse. The bridge had carried a Sufficiency Rating of 50 at the time of its last inspection and had been flagged with known fatigue cracks in its steel truss members. It was classified as structurally deficient. The Minnesota DOT had scheduled the bridge for replacement, but the collapse occurred before work began. The I-35W failure drove immediate reinspection of thousands of similar fracture-critical non-redundant steel deck trusses nationally and produced lasting changes to how inspectors assess gusset plate capacity.

The Fern Hollow Bridge in Pittsburgh collapsed on January 28, 2022, the morning before President Biden arrived in the city to promote the Infrastructure Investment and Jobs Act. The bridge, which carried Forbes Avenue over Fern Hollow Creek in Frick Park, had been rated in poor condition for more than three years at the time of its collapse. Its most recent NBI inspection had assigned it a substructure condition rating of 3—serious condition, meaning loss of section threatening structural integrity. Ten people were injured; no fatalities occurred. The collapse occurred without any vehicle collision or external impact; the bridge failed under its own deteriorated condition. The Fern Hollow collapse illustrated that poor-condition ratings in the NBI represent real and present risk, not merely administrative classifications, and it provided a vivid backdrop for the federal bridge funding debate that was ongoing at the moment of its failure.

The Francis Scott Key Bridge in Baltimore collapsed on March 26, 2024, killing six construction workers when a container ship, the Dali, lost power and struck one of the bridge's support piers. The Key Bridge collapse was not a condition-related failure: the bridge had NBI inspection ratings consistent with fair-to-good condition, and the cause was an extraordinary external impact event that no inspection program is designed to prevent. The collapse nonetheless drew intense scrutiny of the NBI and bridge safety systems, and it focused national attention on the vulnerability of large bridges to vessel strikes—a risk category that the NBI's scour criticality ratings address for waterway erosion but that does not have an analogous rating field for ship collision risk. The incident prompted FHWA to examine vessel collision protection standards for major water crossings.

The Infrastructure Investment and Jobs Act and bridge funding

The Infrastructure Investment and Jobs Act of 2021 (IIJA, also called the Bipartisan Infrastructure Law) included the largest dedicated federal bridge investment in US history. The law directed approximately $550 billion in new federal infrastructure spending across surface transportation, water, broadband, and energy. Within surface transportation, bridges received several distinct funding streams.

The Bridge Formula Program provides $27.5 billion over five years (fiscal years 2022 through 2026), distributed to states by formula based on their share of the national population of poor-condition bridges weighted by deck area. The BFP represents the largest dedicated bridge formula funding in US history, nearly doubling the prior level of dedicated bridge formula funding. States must use BFP funds for bridges on or off the federal highway system— including county roads and local roads that are not part of the National Highway System—and must prioritize bridges in poor condition and bridges that are fracture-critical.

The National Highway Performance Program also provides substantial bridge funding for structures on the National Highway System. NHPP funds can be used for both construction of new bridges and rehabilitation of existing NHS bridges. The NHS carries roughly 55 percent of all vehicle miles traveled in the United States, and NHS bridges tend to be larger and carry higher traffic than off-system bridges, making NHS bridge condition a significant component of the national infrastructure picture.

The Bridge Investment Program is a new competitive grant program established by the IIJA, providing $12.5 billion over five years for large-scale bridge projects. The BIP targets bridges with replacement or rehabilitation costs exceeding $100 million and prioritizes projects that improve condition, reduce vulnerability, improve geometry to current standards, and provide economic development benefits. Unlike the formula-driven BFP and NHPP, the BIP operates on a merit-based competitive application process administered by FHWA.

A specific priority in both the BFP and IIJA bridge policy guidance is the population of off-system bridges—structures on local and county roads that are not part of the federal-aid highway system. Off-system bridges were historically ineligible for or deprioritized in federal bridge programs, creating a funding gap that left many rural county bridges in deteriorated condition. The IIJA explicitly directed that a portion of BFP funds be dedicated to off-system bridges and required states to give priority to poor-condition off-system bridges in their BFP project selection.

NBI data fields

The NBI flat file includes approximately 120 data fields per bridge record, defined in the FHWA Recording and Coding Guide (FHWA-PD-96-001). The fields span inventory identification, location, physical characteristics, condition ratings, appraisals, and traffic data. Key fields in the dataset include:

FieldDescription
STRUCTURE_NUMBER_008State-assigned unique bridge identifier (up to 15 characters)
STATE_CODE_001FIPS state code (2-digit)
COUNTY_CODE_003FIPS county code (3-digit)
PLACE_CODE_004Census place code for urban/rural location
LATITUDE_016 / LONGITUDE_017Decimal latitude and longitude of bridge
YEAR_BUILT_027Year of original construction
YEAR_RECONSTRUCTED_106Year of most recent major reconstruction (blank if none)
MAIN_UNIT_SPANS_045Number of spans in the main unit
MAX_SPAN_LEN_MT_048Length of maximum span in meters
STRUCTURE_LEN_MT_049Total bridge length in meters
DECK_WIDTH_MT_052Out-to-out deck width in meters
APPR_WIDTH_MT_032Approach roadway width in meters
DECK_COND_058Deck condition rating (0-9 or N)
SUPERSTRUC_COND_059Superstructure condition rating (0-9 or N)
SUBSTRUC_COND_060Substructure condition rating (0-9 or N)
CHANNEL_COND_061Channel and channel protection condition (0-9 or N)
CULVERT_COND_062Culvert condition rating for culvert structures (0-9 or N)
BRIDGE_CONDITIONFHWA-derived overall condition: G (good), F (fair), P (poor)
SCOUR_CRITICAL_113Scour criticality rating (0-9 scale plus U for unknown)
FRACTURE_CRITICALFlag indicating fracture-critical member(s) present
SUFFICIENCY_RATINGComposite 0-100 sufficiency score
ADT_029Average daily traffic (vehicles per day)
ADTTRUCK_109Truck traffic as percentage of ADT
BRIDGE_STATUSOpen, closed, or restricted status
SERVICE_ON_042AType of service carried on the bridge (highway, railroad, pedestrian)
MATERIAL_TYPEPrimary structural material (concrete, steel, prestressed concrete, timber)
DESIGN_LOAD_031Design load standard (MS 18, MS 18+, legal loads)
FEDERAL_LANDS_105Federal lands code if bridge is on federal property

Data access

FHWA publishes the NBI as an annual flat-file download at https://www.fhwa.dot.gov/bridge/nbi/ascii.cfm. The files are pipe-delimited ASCII text, available as a national file combining all states and territories or as individual state-level files. The national file for a given year contains approximately 620,000 records and runs to roughly 175 MB uncompressed. Prior years are available in the same format going back to the early 1990s, enabling longitudinal analysis of condition trends.

The FHWA Bridge Program data portal at https://www.fhwa.dot.gov/bridge/nbi/ provides context, documentation, and links to the annual downloads. The portal also hosts the Recording and Coding Guide (FHWA-PD-96-001), the essential reference for interpreting field codes, rating definitions, and data element specifications. The coding guide is the primary source of truth for any analytical work with the NBI.

The Bureau of Transportation Statistics makes NBI data available through the data.transportation.gov Socrata platform at https://data.transportation.gov/api/views/vjbh-mfr3/rows.json. The Socrata interface supports filtered queries and JSON responses via the SODA API, which is useful for ad hoc queries against subsets of the data without downloading the full national flat file. For analysis spanning the full 620,000-bridge inventory, the bulk flat-file download is substantially more efficient than the Socrata API, which is subject to row-count limits and rate constraints on unfiltered queries.

There is no official REST API for the NBI comparable to, for example, the FRA Safety Data API for railroad accidents. FHWA's primary distribution mechanism is the annual bulk flat file. State DOTs sometimes publish their own bridge condition data through state open-data portals, occasionally with more frequent updates than the annual federal submission cycle, but these are not standardized across states.

Python: analyzing NBI condition data from the flat file

The following Python script demonstrates the core NBI analysis workflow: downloading the annual national flat file, parsing the pipe-delimited format, computing poor-condition rates by state, summarizing the Sufficiency Rating distribution, examining the age distribution of the bridge inventory, and quantifying average daily traffic crossing poor-condition bridges by state. The script handles the “N” not-applicable codes that appear in condition rating fields for inapplicable element types and applies FHWA's official poor-condition definition using the minimum of the three primary element ratings.

import requests
import pandas as pd
import io
import matplotlib.pyplot as plt
from collections import defaultdict

# ---------------------------------------------------------------------------
# FHWA National Bridge Inventory (NBI) Analysis
#
# Primary data source:
#   FHWA NBI ASCII flat files at:
#   https://www.fhwa.dot.gov/bridge/nbi/ascii.cfm
#
#   Files are pipe-delimited ("|") ASCII, one row per bridge structure.
#   Field definitions: Recording and Coding Guide for the Structure Inventory
#   and Appraisal of the Nation\'s Bridges (FHWA-PD-96-001).
#
#   National file (all 50 states + territories): ~620,000 rows, ~175 MB.
#   State files: individual ASCII files per state, same schema.
#
# Alternative access:
#   Bureau of Transportation Statistics / data.transportation.gov Socrata:
#   https://data.transportation.gov/api/views/vjbh-mfr3/rows.json
#   Useful for ad-hoc queries; slower than bulk flat file for full-dataset work.
# ---------------------------------------------------------------------------

# --- Step 0: Download the national NBI flat file ---
# The FHWA publishes annual NBI files at stable URLs by year.
# Replace the year below to access prior years (data available back to 1992).
NBI_YEAR = 2023
NBI_URL = f"https://www.fhwa.dot.gov/bridge/nbi/{NBI_YEAR}/delimited/NBI{NBI_YEAR}.txt"

print(f"Downloading NBI national file for {NBI_YEAR}...")
resp = requests.get(NBI_URL, timeout=120)
resp.raise_for_status()

# The file uses "|" as delimiter and has a one-row header.
df = pd.read_csv(
    io.StringIO(resp.text),
    sep="|",
    dtype=str,          # read all columns as strings; cast numeric fields explicitly
    low_memory=False,
)
print(f"Loaded {len(df):,} bridge records with {len(df.columns)} columns.")

# ---------------------------------------------------------------------------
# Key NBI fields used in this analysis
# (column names match the FHWA coding guide field numbers / header labels)
#
#   STATE_CODE_001        : FIPS state code (2-digit string, e.g. "48" = Texas)
#   STRUCTURE_NUMBER_008  : unique bridge identifier within state
#   YEAR_BUILT_027        : year of original construction
#   YEAR_RECONSTRUCTED_106: year of most recent major reconstruction (blank if none)
#   ADT_029               : average daily traffic (vehicles per day)
#   ADTTRUCK_109          : trucks as percentage of ADT
#   DECK_COND_058         : deck condition rating (0-9 scale, "N" = not applicable)
#   SUPERSTRUC_COND_059   : superstructure condition rating (0-9 scale)
#   SUBSTRUC_COND_060     : substructure condition rating (0-9 scale)
#   CHANNEL_COND_061      : channel and channel protection condition (0-9, "N" = N/A)
#   CULVERT_COND_062      : culvert condition (0-9, "N" = N/A for non-culverts)
#   SUFFICIENCY_RATING    : composite 0-100 sufficiency score
#   SCOUR_CRITICAL_113    : scour criticality code (0-9 scale; U = unknown)
#   FRACTURE_CRITICAL     : fracture critical flag
#   BRIDGE_CONDITION      : FHWA-derived overall condition ("G"=good, "F"=fair, "P"=poor)
#   STRUCTURAL_EVAL_067   : structural evaluation appraisal rating
#   LATITUDE_016          : decimal latitude
#   LONGITUDE_017         : decimal longitude
# ---------------------------------------------------------------------------

# Normalize column names: strip whitespace and upper-case for consistency.
df.columns = [c.strip().upper() for c in df.columns]

# Helper: convert NBI 0-9 rating columns to numeric, treating "N" as NaN.
def nbi_rating(series: pd.Series) -> pd.Series:
    return pd.to_numeric(series.replace("N", pd.NA), errors="coerce")

df["DECK_NUM"]   = nbi_rating(df["DECK_COND_058"])
df["SUPER_NUM"]  = nbi_rating(df["SUPERSTRUC_COND_059"])
df["SUB_NUM"]    = nbi_rating(df["SUBSTRUC_COND_060"])
df["CULVERT_NUM"]= nbi_rating(df["CULVERT_COND_062"])
df["SR_NUM"]     = pd.to_numeric(df["SUFFICIENCY_RATING"], errors="coerce")
df["ADT_NUM"]    = pd.to_numeric(df["ADT_029"], errors="coerce")
df["YEAR_BUILT_NUM"] = pd.to_numeric(df["YEAR_BUILT_027"], errors="coerce")

# FHWA definition of "poor" condition: any of deck, superstructure, or substructure
# rated 4 or below (for non-culvert bridges), OR culvert rated 4 or below.
# Bridges rated "N" on all three main elements are culverts; apply culvert rating.
is_culvert = df["DECK_NUM"].isna() & df["SUPER_NUM"].isna() & df["SUB_NUM"].isna()

df["POOR"] = (
    (~is_culvert & (
        (df["DECK_NUM"]  <= 4) |
        (df["SUPER_NUM"] <= 4) |
        (df["SUB_NUM"]   <= 4)
    )) |
    (is_culvert & (df["CULVERT_NUM"] <= 4))
)

# --- Step 1: Poor-condition rate by state ---
state_totals = df.groupby("STATE_CODE_001").size().rename("total")
state_poor   = df[df["POOR"]].groupby("STATE_CODE_001").size().rename("poor")
state_summary = pd.concat([state_totals, state_poor], axis=1).fillna(0)
state_summary["poor_pct"] = 100 * state_summary["poor"] / state_summary["total"]

# FIPS-to-abbreviation lookup for the 50 states + DC.
fips_to_abbr = {
    "01":"AL","02":"AK","04":"AZ","05":"AR","06":"CA","08":"CO","09":"CT",
    "10":"DE","11":"DC","12":"FL","13":"GA","15":"HI","16":"ID","17":"IL",
    "18":"IN","19":"IA","20":"KS","21":"KY","22":"LA","23":"ME","24":"MD",
    "25":"MA","26":"MI","27":"MN","28":"MS","29":"MO","30":"MT","31":"NE",
    "32":"NV","33":"NH","34":"NJ","35":"NM","36":"NY","37":"NC","38":"ND",
    "39":"OH","40":"OK","41":"OR","42":"PA","44":"RI","45":"SC","46":"SD",
    "47":"TN","48":"TX","49":"UT","50":"VT","51":"VA","53":"WA","54":"WV",
    "55":"WI","56":"WY",
}
state_summary["abbr"] = state_summary.index.map(fips_to_abbr).fillna("OTHER")

top10_pct   = state_summary.nlargest(10, "poor_pct")[["abbr","poor","total","poor_pct"]]
top10_count = state_summary.nlargest(10, "poor")[["abbr","poor","total","poor_pct"]]

print("\nTop 10 States by Share of Bridges in Poor Condition")
print(f"{'State':<6}  {'Poor':>7}  {'Total':>8}  {'Pct':>6}")
print("-" * 32)
for _, row in top10_pct.iterrows():
    print(f"{row['abbr']:<6}  {int(row['poor']):>7,}  {int(row['total']):>8,}  {row['poor_pct']:>5.1f}%")

print("\nTop 10 States by Count of Bridges in Poor Condition")
print(f"{'State':<6}  {'Poor':>7}  {'Total':>8}  {'Pct':>6}")
print("-" * 32)
for _, row in top10_count.iterrows():
    print(f"{row['abbr']:<6}  {int(row['poor']):>7,}  {int(row['total']):>8,}  {row['poor_pct']:>5.1f}%")

# --- Step 2: Sufficiency Rating distribution histogram ---
sr_bins = [0, 25, 50, 75, 100]
sr_labels = ["0-25", "25-50", "50-75", "75-100"]
df["SR_BIN"] = pd.cut(df["SR_NUM"], bins=sr_bins, labels=sr_labels, right=True, include_lowest=True)
sr_dist = df["SR_BIN"].value_counts().sort_index()

print("\nSufficiency Rating Distribution")
print(f"{'SR Range':<10}  {'Bridges':>9}  {'Pct':>6}")
print("-" * 30)
total_with_sr = sr_dist.sum()
for label, count in sr_dist.items():
    pct = 100 * count / total_with_sr
    print(f"{label:<10}  {count:>9,}  {pct:>5.1f}%")
print(f"\n  SR < 50 (replacement-eligible): {(df['SR_NUM'] < 50).sum():,}")
print(f"  SR < 80 (rehabilitation-eligible): {(df['SR_NUM'] < 80).sum():,}")

# --- Step 3: Age distribution of US bridges ---
current_year = 2027
df["AGE"] = current_year - df["YEAR_BUILT_NUM"]
age_bins = [0, 10, 25, 50, 75, 100, 200]
age_labels = ["<10 yrs", "10-25 yrs", "25-50 yrs", "50-75 yrs", "75-100 yrs", "100+ yrs"]
df["AGE_BIN"] = pd.cut(df["AGE"], bins=age_bins, labels=age_labels, right=True, include_lowest=True)
age_dist = df["AGE_BIN"].value_counts().sort_index()

print("\nUS Bridge Age Distribution")
print(f"{'Age Range':<12}  {'Bridges':>9}  {'Pct':>6}")
print("-" * 32)
total_with_age = age_dist.sum()
for label, count in age_dist.items():
    pct = 100 * count / total_with_age
    print(f"{label:<12}  {count:>9,}  {pct:>5.1f}%")
print(f"\nMedian bridge age: {df['AGE'].median():.0f} years")
print(f"Bridges 50+ years old: {(df['AGE'] >= 50).sum():,} ({100*(df['AGE'] >= 50).mean():.1f}%)")

# --- Step 4: Average daily traffic crossing poor-condition bridges by state ---
poor_df = df[df["POOR"] & df["ADT_NUM"].notna()].copy()
adt_by_state = (
    poor_df.groupby("STATE_CODE_001")["ADT_NUM"]
    .agg(bridges="count", total_adt="sum", mean_adt="mean")
    .reset_index()
)
adt_by_state["abbr"] = adt_by_state["STATE_CODE_001"].map(fips_to_abbr).fillna("OTHER")
adt_by_state = adt_by_state.sort_values("total_adt", ascending=False)

print("\nAverage Daily Traffic Crossing Poor-Condition Bridges (top 10 states by total ADT)")
print(f"{'State':<6}  {'Poor Bridges':>12}  {'Total ADT':>12}  {'Mean ADT':>10}")
print("-" * 46)
for _, row in adt_by_state.head(10).iterrows():
    print(
        f"{row['abbr']:<6}  {int(row['bridges']):>12,}"
        f"  {int(row['total_adt']):>12,}  {int(row['mean_adt']):>10,}"
    )

print("\nAnalysis complete.")
print(f"  Total bridges in dataset:    {len(df):,}")
print(f"  Bridges in poor condition:   {df['POOR'].sum():,} ({100*df['POOR'].mean():.1f}%)")
print(f"  Bridges with ADT data:       {df['ADT_NUM'].notna().sum():,}")

The poor-condition rate analysis by state reveals substantial geographic variation that reflects both the age of state bridge inventories and historical funding levels. States in the northeastern United States—Pennsylvania, New York, West Virginia—typically show elevated poor-condition rates driven by a large stock of older bridges built during the mid-twentieth-century highway construction era, many of which have reached or exceeded their design service lives. States with more recent highway build-out tend to show lower poor-condition rates, though a growing cohort of 1970s-and-1980s bridges is entering the deterioration range in Sun Belt states.

The Sufficiency Rating distribution illuminates the rehabilitation pipeline. Approximately 7 to 8 percent of bridges nationally carry SRs below 50, qualifying them for federal replacement funding. Roughly 30 to 35 percent carry SRs below 80, qualifying for rehabilitation. The gap between poor-condition count (SR threshold eligibility) and the rehabilitation-eligible population illustrates why bridge spending under the IIJA was designed with multiple funding tiers: the highest-priority structures need replacement funding, but a much larger cohort needs rehabilitation to prevent deterioration into poor condition in the coming decade.

The age distribution analysis consistently surfaces the mid-century construction cohort as the dominant at-risk population. More than half of US bridges were built before 1980—structures designed to 40-to-50-year service lives that are now 50-plus years old. Many of these bridges were designed to AASHTO H15 or H20 loading standards that are significantly lower than the current legal truck weights that regularly cross them. Load posting—weight restrictions applied to structurally limited bridges—affects roughly 5 percent of bridges nationally, but load postings on bridges serving agricultural communities or industrial corridors impose significant operational costs through detour requirements for heavy freight movements.