FIPI is a SQL-derived composite index that ranks U.S. counties by cumulative food access vulnerability — merging four federal datasets on a single FIPS key so the counties failing on every front at once are named, not buried in four separate spreadsheets.
Approximately 19.5 million Americans live in low-income, low-access census tracts — the nearest grocery store is over a mile away in cities, or ten miles in rural counties. Diabetes prevalence in high-food-desert counties runs 25–30% above counties with full food access. Rural food desert counties have median incomes more than $15,000 below the national median.
The data to see this already exists — USDA, Census, County Health Rankings, and SNAP administrative records each describe part of the picture. What doesn't exist is a single, reproducible ranking that says which counties are failing on every dimension at once, and by how much.
FIPI treats that as a data-integration and scoring problem, not a mapping problem: four datasets, one FIPS key, one weighted composite score, ranked.
A county with high poverty but low SNAP uptake has a safety net that exists on paper but fails in practice — 45% of the 1,054 counties scored here fall into "Severe" or "Critical" SNAP-strand territory.
Existing tools identify that a tract is underserved. None weight the compounding effect of low access + low SNAP reach + high health burden happening in the same county simultaneously.
USDA ERS, USDA FNS, RWJF/County Health Rankings, and the Census Bureau each publish independently. No unified county-level view connects all four for resource-allocation decisions.
Every dataset below is joined on a 5-digit county FIPS code — eliminating name-matching failures like "St. Louis" vs. "Saint Louis" — except SNAP administrative data, published at the state level.
Core food desert flag (1mi urban / 10mi rural), low-access population, poverty rate, median family income, vehicle access — at the census tract level.
Enrollment counts, average monthly benefit. Combined with ACS eligibility estimates to engineer the SNAP participation gap.
Diabetes prevalence, adult obesity rate, food insecurity rate — the health-consequence dimension that turns geography into a public health argument.
County poverty rate, median household income, SNAP receipt %, no-vehicle % — the eligibility side of the participation gap.
A food desert is concerning. A food desert with low SNAP uptake, above-average diabetes rates, and no nearby pantry is a compounding systemic failure — and needs to be named as one.
final_fipi_score = (access_score × 0.30) + (poverty_score × 0.30) + (snap_score × 0.25) + (vehicle_score × 0.15) -- each dimension min-max normalized first: ROUND((value - MIN(value) OVER()) / NULLIF(MAX(value) OVER() - MIN(value) OVER(), 0) * 100, 1) -- counties with fewer than 10 tracts are dropped -- (too small to score reliably): HAVING COUNT(*) >= 10 AND SUM(lilatracts_1and10) > 0
Every row below is a real scored county from final_fipi_scores.csv. Click any column header to re-sort.
| Rank | County | State | % Desert Tracts | Poverty Rate | SNAP Strand Rate | People Affected | FIPI Score |
|---|
County-level food desert concentration was correlated against County Health Rankings outcomes across 3,142 counties using Pearson's r (scipy.stats.pearsonr).
Both relationships are statistically significant at p < 0.001 across n = 3,142 counties, confirming the model differentiates high-need from low-need counties on a real downstream outcome — not just on the variables that define the index itself. Coefficients are modest in size, which is expected: food access is one of several drivers of chronic disease, which is exactly why FIPI weights health burden as one of four dimensions rather than the sole criterion.
Project manifest plus the full 130+ column DDL for the raw USDA Food Access Atlas table.
View SQL →Verification queries, state-level aggregation, and county-level ranking by people affected.
View SQL →CASE WHEN logic tiering counties into Extreme / Severe / High / Elevated risk.
View SQL →First composite index: Access 40% + Poverty 35% + Vehicle Access 25%, min-max normalized.
View SQL →Counties where SNAP enrollment is concentrated inside food-desert tracts, tiered by severity.
View SQL →Final weighted model: Access 30% + Poverty 30% + SNAP Gap 25% + Vehicle Access 15%.
View SQL →Full 12-file SQL progression (including both exploratory passes at days 06 and 07) lives in /sql. Data dictionary and executive insights are in /docs and /outputs.
State and county-level maps, filterable by FIPI score, SNAP strand rate, and health burden.