Economic Indicators
Job Growth Methodology
Job Growth Methodology
Job counts come from the U.S. Census Bureau's Longitudinal Employer-Household Dynamics (LEHD) Workplace Area Characteristics (WAC) file, which reports jobs at the workplace location for every Census block that has employment. The chart aggregates those block-level counts to each of the supported app geographies and breaks them down into four broad sectors.
Source Data
| Component | Source | Detail |
|---|---|---|
| Block-level job counts | LEHD LODES WAC | lodes.wac (one row per block, year, with c000 total jobs and cns01-cns20 industry counts) |
| Block-to-geography crosswalk | U.S. Census | xwalks.xwalk (links 2020 tabulation blocks to county, place, county subdivision, CBSA) |
The WAC table is large (~300M rows across the full vintage range), so the chart uses a single-pass aggregation strategy described below.
Sector Groupings
The 20 LODES NAICS-derived industry columns (cns01-cns20) are collapsed to four sector groupings:
| Sector | LODES columns | Description |
|---|---|---|
| Knowledge sector | cns09-cns14 | Information; finance; real estate; professional, scientific & technical; management; administrative & support |
| Blue collar | cns01-cns05, cns08 | Agriculture; mining; utilities; construction; manufacturing; transportation & warehousing |
| Service sector | cns06, cns07, cns17-cns19 | Wholesale; retail; arts & entertainment; accommodation & food; other services |
| Eds, meds & public | cns15, cns16, cns20 | Educational services; health care & social assistance; public administration |
Each column is summed across the blocks that fall within a geography. Total jobs are taken directly from c000.
Supported Geographies
| Level | Crosswalk column | Geoid length |
|---|---|---|
| State | first two chars of w_geocode | 2 |
| County | cty | 5 |
| Place (incorporated & CDP) | stplc | 7 |
| County subdivision (MCD) | ctycsub | 10 |
| CBSA | cbsa | 5 |
Sentinel codes (9999999 for place, 9999999999 for cousub, 99999 for CBSA) are filtered out so blocks outside the relevant geography do not contribute to any sub-state total.
Aggregation Strategy
A naive approach (one GROUP BY per geo level per sector) would scan the full WAC table many times. Instead, the chart performs a single-pass temp-table aggregation:
- Build a narrow temp table (
_wac_geo) by joininglodes.wactoxwalks.xwalkonce. The temp table carries year, total jobs, and the four pre-summed sector totals for each (block, year), along with the state/county/place/cousub/CBSA identifiers for that block. - Filter by target geoids at temp-table creation time using a WHERE clause that combines state-prefix filters and sub-state identifier filters. Sub-state geoids whose parent state is already targeted are skipped to avoid double-coverage.
- Run fast GROUP BY queries per geo level against the narrow temp table to produce a wide DataFrame keyed on
(geoid, year). - Emit two output tables from the same wide DataFrame:
job_growth(total jobs) andjobs_by_sector(long-form sector breakdown), both upserted by(geoid, year)and(geoid, year, sector)respectively. A full run uses replace mode; partial geoid-scoped runs use upsert with stale-row cleanup.
Output Tables
job_growth
| Column | Description |
|---|---|
geoid | Geography identifier |
year | LODES vintage year |
total_jobs | Sum of c000 across blocks in this geoid |
jobs_by_sector
| Column | Description |
|---|---|
geoid | Geography identifier |
year | LODES vintage year |
sector | One of the four sector labels |
jobs | Sum of the sector's LODES columns across blocks in this geoid |
Caveats
- LODES counts jobs at the workplace location, not workers by residence. Sectors and totals describe where jobs are, not where employees live.
- The LODES vintage range lags the ACS by about two years.
- LODES coverage is incomplete or absent for some federal employment and for a small number of states in specific years; consult the underlying LEHD documentation when interpreting low counts.