Analyzing USDA Crop Insurance Indemnities, 1989–2023

The corrected USDA snapshot contains 4,173,884 Cause of Loss Summary of Business records across commodity years 1989–2023. Each record summarizes a combination of geography, commodity, insurance plan, coverage, stage, cause and reported loss month/year. Records are not individual claims, payments, farms or unique policies.

The September 2026 rebuild preserves every field from the 35 pinned annual files and verifies annual financial totals. It corrects a shifted financial mapping: use indemnity_amount at source position 29. The former indemnity field read the EFA premium discount position. Weather tables, weather joins and inferred cause categories are excluded.

Load exact monetary values

Install duckdb and pyarrow, extract the package, and run these examples from its directory. Parquet preserves decimal amounts, text identifiers and raw source tokens. All 30 agency fields are retained under raw_ names alongside normalized fields. This is a breaking schema change.

import duckdb
con = duckdb.connect()
con.execute("CREATE VIEW losses AS SELECT * FROM read_parquet('usda_crop_insurance_indemnities.parquet')")
rows, first_year, last_year = con.sql("""
    SELECT count(*), min(commodity_year), max(commodity_year)
    FROM losses
""").fetchone()
print(rows, first_year, last_year)
assert (rows, first_year, last_year) == (4173884, 1989, 2023)

Sum recorded indemnities by commodity year

Use commodity_year for the period. Retain negative and zero amounts when reproducing the source sums. The source contains 74,886 negative indemnities and 47,524 zero rows, plus two exact duplicate records that are explicitly retained. These totals are nominal dollars and do not adjust for inflation, changes in insured exposure or reporting coverage.

annual = con.sql("""
    SELECT commodity_year, count(*) AS summary_rows,
           sum(indemnity_amount) AS net_indemnity_usd
    FROM losses
    GROUP BY 1 ORDER BY 1
""").fetchall()
print(annual)
assert len(annual) == 35
# DuckDB returns exact Decimal amounts, without binary-float conversion.
from decimal import Decimal
assert sum(row[2] for row in annual) == Decimal("196825109993")

Inspect causes within one commodity year

Keep the source cause code and description together. There are 161,137 missing descriptions across the full release, and labels can vary over time. This example compares recorded 2023 amounts without assuming a universal cause classification or inferring the reason for a missing description.

causes_2023 = con.sql("""
    SELECT cause_of_loss_code,
           coalesce(cause_of_loss_desc, '[description missing]') AS description,
           count(*) AS summary_rows,
           sum(indemnity_amount) AS net_indemnity_usd
    FROM losses WHERE commodity_year = 2023
    GROUP BY 1, 2 ORDER BY net_indemnity_usd DESC
    LIMIT 10
""").fetchall()
print(causes_2023)

Dates, duplicates and denominators

• Loss year is missing in 1,442,370 records and invalid in 2,320. Loss month is missing in 22 and invalid in 48,287. Nullable typed fields have matching status fields; raw values remain available.
• Another 2,068 calendar-valid loss years differ from commodity year by more than two years. A calendar-valid flag does not establish that the reported date is correct.
• Two exact duplicates and 11 excess records sharing a dimension key remain as published by USDA. Use source_file plus source_row as a key within this release. These are not persistent claim identifiers.
• Do not sum policy counts as unique farms or combine quantities with different commodity units. Do not average row loss ratios or treat cause-of-loss premiums as exposure for the entire program.
• This snapshot supports descriptive summaries of the supplied records. It does not establish climate causation, forecast losses or measure geographic risk.