Energy — Provider-Level Utility Rates
TL;DR
- 5 new tables in the commute DB: utility_providers (3,230), utility_rates_annual (4,141 — utility × state × 2023/2024 residential ¢/kWh, bundled-preferred from EIA-861 revenue÷sales), utility_service_territory (23,558 utility×county; NJ/NY FIPS'd), utility_tariffs (17 URDB majors NY/NJ/CT/PA), state_energy_choice (51 — 15 electric / 16 gas choice states).
- Filled the heating-fuel gap: heating_oil_no2 (21,846 rows) + propane (28,759) inserted into energy_prices — EIA weekly residential $/gal, full 1990→2026-03-30 history, state/PADD/US. Existing rows untouched.
- NJ worked example: JCP&L 15.65 ¢/kWh vs PSE&G 20.42 vs Rockland 21.04 vs ACE 23.41 (2024) — up to $839/yr spread at 10,800 kWh; 2026 BGS-RSCP supply auction: PSE&G 10.938 ¢/kWh (price-to-compare basis, njpowerswitch.com).
- Cross-fuel view energy_cost_per_mmbtu: NJ gas $13.41/MMBTU vs propane $41.78 vs heating oil $42.15 vs electric resistance $67.76 — oil heat ≈ 3.1× utility gas right now.
- utility_savings_county view (8,187 rows): per-county ¢/kWh vs cheapest serving utility + vs state avg, $/yr at 10,800 kWh; single-utility counties flagged no-choice. Refresh: annual 861, weekly oil/propane in season (./load.sh, idempotent).
ENERGY.md — Provider-Level Utility Rate Layer
Built: 2026-06-12 on geocoder · DB: commute @ 172.26.1.152:5433 (gtfs3, trust auth)
Workspace: /home/krish/gpu-pilot/energy/ · Pipeline: ./load.sh (idempotent)
Extends the existing energy_prices table (224K rows, EIA/AAA fuels + state elec/gas) with
provider-level electricity rates, tariff detail, service territories, retail-choice status,
heating oil + propane, and cross-fuel/savings views. No existing rows were modified.
What's loaded
| Object | Rows | Grain / coverage | Vintage |
|---|---|---|---|
utility_providers |
3,230 | one per EIA utility id, nationwide | EIA-861 2023+2024 |
utility_rates_annual |
4,141 | utility × state × year × sector (residential) | 2023, 2024 |
utility_service_territory |
23,558 | utility × county × year; FIPS filled for NJ (98) + NY (354); CT name-only | 2023, 2024 |
utility_tariffs |
17 | top residential URDB tariff per major (≥50k cust) utility, NY/NJ/CT/PA | pulled 2026-06 |
state_energy_choice |
51 | per state+DC; 15 electric-choice, 16 gas-choice | reviewed 2026-06 |
energy_prices += heating_oil_no2 |
21,846 | weekly residential $/gal: state/sub-PADD/PADD/US | 1990-10-01 → 2026-03-30 |
energy_prices += propane |
28,759 | weekly residential $/gal: state/sub-PADD/PADD/US | 1990-10-01 → 2026-03-30 |
view energy_cost_per_mmbtu |
164 | latest state price per fuel, normalized $/MMBTU | follows energy_prices |
view utility_savings_county |
8,187 | county × serving utility: ¢/kWh vs cheapest + state avg, $/yr @ 10,800 kWh | EIA-861 2024 |
Heating oil/propane are heating-season weekly (EIA SHOPP survey, Oct–Mar); the season just ended, so max(period)=2026-03-30 is current. Oil covers ~23 states (Northeast-heavy by design), propane ~38.
Price semantics (the one thing to remember)
utility_rates_annual.avg_price_cents_kwh = 100 × residential revenue / residential sales from
EIA-861 Sales_Ult_Cust, bundled-preferred: if the utility reports Bundled rows (Part A/D)
in a state, price/customers/sales/revenue use bundled rows only — the all-in consumer price.
Delivery-only or energy-only respondents are loaded with their own figures, flagged in
service_types ('Delivery' excludes supply; 'Energy' excludes delivery — don't compare those to
bundled without care). customers_all_types adds delivery/energy customers (e.g. PSE&G NJ 2024:
1.97M bundled, 2.06M total incl. third-party-supply customers). Utility id 99999 (state-level
adjustment rows) is excluded.
Worked example — New Jersey (all numbers from this DB)
Electricity, avg all-in residential ¢/kWh (EIA-861 2024 / 2023):
| Utility (EIA id) | 2023 | 2024 | Customers 2024 | vs cheapest @10,800 kWh |
|---|---|---|---|---|
| JCP&L (9726) | 14.00 | 15.65 | 1,038,258 | — |
| PSE&G (15477) | 18.83 | 20.42 | 2,056,231 | +$515/yr |
| Rockland (16213) | 18.75 | 21.04 | 66,360 | +$583/yr |
| Atlantic City Electric (963) | 20.58 | 23.41 | 506,488 | +$839/yr |
State volume-weighted bundled avg 2024: 19.16 ¢/kWh; EIA monthly NJ residential Feb 2026: 23.12 ¢/kWh.
Price-to-compare basis (supply only): 2026 BGS-RSCP auction clearing prices, effective
2026-06-01 (3-yr tranches; source PDF data/bgs_2026_results.pdf from bgs-auction.com):
PSE&G 10.938, ACE 11.275, JCP&L 11.327, RECO 12.057 ¢/kWh. Customer PTC blends the three
latest auction vintages + reconciliation + SUT — published by NJ BPU (njpowerswitch.com).
NJ has full electric + gas retail supply choice; PSE&G is the dual-fuel default utility.
Tariff detail (URDB): PSE&G RS — $6.00/mo fixed, energy 0.247–0.292 $/kWh (eff. 2026-04); JCP&L $4.27/mo, 0.161–0.230; ACE RS $6.75/mo, 0.257–0.281. (Rockland's URDB entry is stale, 2015 TOU.)
Fuels, $/MMBTU site energy (view energy_cost_per_mmbtu): gas $13.41 (1.341 $/therm,
Feb 2026) · propane $41.78 (3.821 $/gal) · heating oil $42.15 (5.838 $/gal, 2026-03-30) ·
electricity $67.76 (23.12 ¢/kWh). Oil heat ≈ 3.1× utility gas per BTU right now.
Savings view sample (5 NJ counties, utility_savings_county)
| County | Utility | ¢/kWh | Benchmark | Δ ¢/kWh | $/yr |
|---|---|---|---|---|---|
| Atlantic | ACE | 23.41 | no-choice → JCP&L (state cheapest) | +7.77 | +$839 |
| Bergen | Rockland | 21.04 | in-county → PSE&G | +0.63 | +$68 |
| Essex | PSE&G | 20.42 | in-county → JCP&L | +4.77 | +$515 |
| Monmouth | PSE&G | 20.42 | in-county → JCP&L | +4.77 | +$515 |
| Sussex | Rockland | 21.04 | in-county → JCP&L | +5.40 | +$583 |
Caveat baked into the view comment: multiple utilities in a county = adjoining territories, not
household choice; in choice states the supply portion is shoppable. Single-utility counties are
benchmarked vs the cheapest in-state distribution utility and flagged no-choice
(true neighbor-county adjacency = phase 2, needs census adjacency file).
Source registry
| Source | What | URL | Auth |
|---|---|---|---|
| EIA-861 bulk (f861YYYY.zip) | Sales_Ult_Cust, Service_Territory, Utility_Data | eia.gov/electricity/data/eia861/ (older years under /archive/zip/) | none |
EIA API v2 petroleum/pri/wfr |
weekly residential heating oil (EPD2F/PRS) + propane (EPLLPA/PRS) | api.eia.gov | EIA_API_KEY (geo ~/pythonETLRESO/.env) |
| OpenEI URDB bulk | tariff structures | apps.openei.org/USURDB/download/usurdb.csv.gz | none (API would need a key we don't have) |
| NJ BGS auction | BGS-RSCP clearing prices | bgs-auction.com → previous results | none |
| State PUC shopping sites | choice status + PTC | per-row state_energy_choice.source_url |
none |
| EIA zip↔utility crosswalk | iou_zipcodes_YYYY.csv (NOT loaded — see gaps) |
legacy eia.gov URLs now 301→dead page; needs DOE/HIFLD alternative | none |
Refresh cadence (recommend adopting into geo run_energy_prices_etl.py)
- Weekly, Oct–Mar: heating_oil_no2 + propane (script 02 is incremental — ON CONFLICT DO NOTHING)
- Monthly: electricity + natural gas state prices (already in geo ETL)
- Annual (~Oct): EIA-861 new vintage → scripts 01 (+ FIPS backfill), URDB re-pull (script 03)
- Semiannual: review
state_energy_choicenotes; NJ BGS auction each February
Known gaps / open questions
- FirstEnergy Pennsylvania Electric Co (EIA 66101, 1.8M customers) has no URDB tariff — URDB still files under pre-merger ids (Met-Ed 12390, Penelec, West Penn). Could map legacy ids.
- County FIPS only NJ/NY (and CT impossible via ACS — Connecticut now reports planning regions). National FIPS needs the census county file (census.gov not on this task's whitelist).
- Zip-level territory: phase-2. The old EIA
iou_zipcodesURLs (zip_code.php) now redirect to a dead legacy page (checked 2026-06-12); use the DOE/HIFLD zip↔utility crosswalk or NREL utility_rates API (needs a free NREL key) when zip UX is wanted. - Gas provider-level rates (EIA-176) not loaded;
utility_providers.utility_typeis ready for elec/gas/dual. - URDB v0 scope = top tariff per major utility NY/NJ/CT/PA (17 rows). Nationwide bulk is
already on disk (
data/usurdb.csv, 166K tariffs) — expanding is a parser run, not a fetch. - Live supplier offers (TX PowerToChoose API, PA PowerSwitch, NJ TPS lists): legality/ToS review needed before scraping (Krishna decision).
Decisions needed from Krishna
- EnergySavings.certihomes.com go/no-go + host/DNS (spec:
SITE-SPEC.md, prototypesite/index.html) - Zip-level territory source: EIA's old iou_zipcodes URLs are dead — approve DOE/HIFLD crosswalk or a free NREL API key as the zip source?
- Phase-2 supplier live-offer scraping: proceed with ToS review?
- Adopt oil/propane refresh into the geo ETL cron?