"""Conservative EIA-860 gas-unit staging; source data are public domain.
Only OP, single-fuel NG, non-combined-cycle generators are accepted. CC
components and mixed fuels require a separately reviewed boundary crosswalk.
"""
from __future__ import annotations
import io
import json
import math
import zipfile
from collections.abc import Iterable
from typing import Any
import openpyxl
from energy_data_retrieval.pv_catalogue.catalogue import canonical_json
from .catalogue import stable_id
from .gppd import evidence_record
TECH = {"GT": "open_cycle", "IC": "reciprocating_engine", "ST": "steam_turbine"}
def _text(value: Any) -> str:
return "" if value is None else str(value).strip()
[docs]
def stage_rows(
raw_rows: Iterable[dict[str, Any]],
*,
states: set[str],
source_id: str,
now: str,
year: int = 2025,
) -> tuple[list[dict[str, Any]], list[dict[str, Any]]]:
"""Stage annual observations, never substitute download time for status year."""
observed = f"{year}-12-31"
if observed > now[:10]:
raise ValueError("Future reporting period")
rows, ledger = [], []
seen: set[str] = set()
for line, r in enumerate(raw_rows, 3):
if _text(r.get("State")) not in states:
continue
plant, gen = _text(r.get("Plant Code")), _text(r.get("Generator ID"))
ident = f"{plant}:{gen}"
if not plant.isdigit() or not gen or ident in seen:
raise ValueError("Missing or duplicate EIA plant-generator identity")
seen.add(ident)
fuels = [_text(r.get(f"Energy Source {i}")) for i in range(1, 7)]
locator = f"3_1_Generator_Y{year}.xlsx / Operable / row {line}; {ident}"
item = {"external_id": ident, "locator": locator}
if "NG" not in fuels:
ledger.append({**item, "decision": "excluded", "reason": "No NG fuel code"})
continue
if (
fuels[0] != "NG"
or any(fuels[1:])
or _text(r.get("Multiple Fuels?")) != "N"
or _text(r.get("Status")) != "OP"
or r.get("Prime Mover") not in TECH
):
ledger.append(
{
**item,
"decision": "held",
"reason": "Review mixed fuels, lifecycle or CC component boundary",
}
)
continue
observations = []
for field, basis in [
("Nameplate Capacity (MW)", "unspecified"),
("Summer Capacity (MW)", "net"),
("Winter Capacity (MW)", "net"),
]:
if not _text(r.get(field)):
continue
value = float(r[field])
if not math.isfinite(value) or value < 0:
raise ValueError("Invalid electrical MW")
observations.append(
{
"field": field,
"value": value,
"unit": "MW",
"kind": "reported",
"period": str(year),
"boundary": ident,
"source_id": source_id,
"locator": locator + "; column " + field,
"electrical_basis": basis,
}
)
values = {
"record_id": stable_id("projects", "EIA-860-generator", ident),
"entity_type": "projects",
"record_scope": "unit",
"name": f"{r['Plant Name']} — generator {gen}",
"country_code": "USA",
"external_ids_json": canonical_json({"eia_plant_generator": ident}),
"aggregation_role": "unresolved",
"overlap_group": f"EIA-plant:{plant}",
"capacity_scope": "Individual EIA generator; WRI crosswalk unresolved; non-additive",
"status": "operational",
"status_as_of": observed,
"status_date_basis": "EIA-860 instructions: year-end equipment status; OP code",
"technology": TECH[r["Prime Mover"]],
"fuel_type": "natural_gas",
"operator_names": [r["Utility Name"]] if _text(r.get("Utility Name")) else None,
"is_chp": {"Y": True, "N": False}.get(
r.get("Associated with Combined Heat and Power System")
),
"observations_json": canonical_json(observations),
"extensions_json": canonical_json(
{
k: r.get(k) if _text(r.get(k)) else None
for k in [
"State",
"County",
"Plant Code",
"Generator ID",
"Prime Mover",
"Unit Code",
"Utility ID",
"Operating Month",
"Operating Year",
"Planned Retirement Month",
"Planned Retirement Year",
"RTO/ISO LMP Node Designation",
"Synchronized to Transmission Grid",
]
}
),
"notes": "Annual observation; not current operation. "
"Seasonal net and nameplate MW separate; no owner, OEM or unit location inferred.",
}
row = evidence_record(values, source_id=source_id, now=now, locator=locator)
# Preserve source-coded inputs for audit without copying the source workbook.
evidence = json.loads(row["evidence_json"])
columns = {
"technology": ["Prime Mover"],
"fuel_type": ["Energy Source 1"],
"status": ["Status"],
"operator_names": ["Utility Name"],
"is_chp": ["Associated with Combined Heat and Power System"],
"observations_json": [
"Nameplate Capacity (MW)",
"Summer Capacity (MW)",
"Winter Capacity (MW)",
],
}
for key, value in evidence.items():
value["source_values"] = {
k: r.get(k) for k in columns.get(key, ["Plant Code", "Generator ID"])
}
value["normalization"] = (
"EIA LayoutY2025 field definitions and code tables; annual 2025 observation"
)
row["evidence_json"] = canonical_json(evidence)
rows.append(row)
ledger.append({**item, "decision": "accepted", "record_id": row["record_id"]})
return rows, ledger
[docs]
def parse_eia860(data: bytes, **kwargs: Any) -> tuple[list[dict[str, Any]], list[dict[str, Any]]]:
"""Read an unmodified EIA archive locally; no network or source-byte writes."""
year = kwargs.get("year", 2025)
with zipfile.ZipFile(io.BytesIO(data)) as archive:
book = openpyxl.load_workbook(
io.BytesIO(archive.read(f"3_1_Generator_Y{year}.xlsx")), read_only=True, data_only=True
)
try:
it = book["Operable"].values
title = next(it)[0]
if not str(title).startswith(f"{year} Form EIA-860"):
raise ValueError("Unexpected reporting year")
headers = next(it)
required = {
"Plant Code",
"Generator ID",
"State",
"Status",
"Prime Mover",
"Energy Source 1",
"Multiple Fuels?",
"Nameplate Capacity (MW)",
}
required |= {f"Energy Source {i}" for i in range(2, 7)}
if not required <= set(headers):
raise ValueError("Missing EIA headers")
return stage_rows((dict(zip(headers, r, strict=True)) for r in it), **kwargs)
finally:
book.close()