"""Exact EIA plant joins for unit site-location and plant grid metadata.
Plant coordinates locate the shared site, never an individual generator. No
plant capacity, status or ownership is propagated to units by this adapter.
"""
from __future__ import annotations
import io
import json
import math
import zipfile
from typing import Any
import openpyxl
from energy_data_retrieval.pv_catalogue.catalogue import canonical_json
[docs]
def plant_patches(
records: list[dict[str, Any]], plants: list[dict[str, Any]], *, source_id: str
) -> tuple[list[dict[str, Any]], list[dict[str, Any]]]:
"""Return upsert candidates; conflicts are left for the catalogue upsert API."""
lookup = {}
for line, plant in enumerate(plants, 3):
code = str(plant.get("Plant Code", "")).strip()
if not code.isdigit() or code in lookup:
raise ValueError("Missing or duplicate plant code")
lookup[code] = (line, plant)
patches, ledger = [], []
for old in records:
identity = json.loads(old.get("external_ids_json") or "{}").get("eia_plant_generator")
if not identity:
continue
if not isinstance(identity, str) or ":" not in identity:
raise ValueError("Malformed EIA plant-generator identity")
code, gen = identity.split(":", 1)
if not gen or code not in lookup:
ledger.append(
{
"record_id": old["record_id"],
"decision": "held",
"reason": "Missing exact plant match",
}
)
continue
line, plant = lookup[code]
ext = json.loads(old.get("extensions_json") or "{}")
if ext.get("State") != plant.get("State"):
raise ValueError("State mismatch for exact plant match")
facts: dict[str, Any] = {}
lat, lon = plant.get("Latitude"), plant.get("Longitude")
lat_missing = lat is None or not str(lat).strip()
lon_missing = lon is None or not str(lon).strip()
if lat_missing != lon_missing:
raise ValueError("Unpaired plant coordinates")
if not lat_missing:
assert lat is not None and lon is not None
lat, lon = float(lat), float(lon)
if (
not math.isfinite(lat)
or not math.isfinite(lon)
or not (-90 <= lat <= 90 and -180 <= lon <= 180)
):
raise ValueError("Invalid plant coordinates")
facts.update(
latitude=lat,
longitude=lon,
location_accuracy=(
"EIA 2025 reported plant-site coordinate; "
"unit position and survey accuracy unspecified"
),
)
parts = []
grid_values = {}
for key in [
"Balancing Authority Code",
"Balancing Authority Name",
"Transmission or Distribution System Owner",
"Grid Voltage (kV)",
"Grid Voltage 2 (kV)",
"Grid Voltage 3 (kV)",
]:
value = plant.get(key)
if value is not None and str(value).strip():
parts.append(f"{key}: {str(value).strip()}")
grid_values[key] = value
if parts:
facts["grid_connection"] = (
"EIA 2025 plant-level connection (not unit terminals): " + "; ".join(parts)
)
changed = {k: v for k, v in facts.items() if old.get(k) != v}
item = {
"record_id": old["record_id"],
"eia_plant_generator": identity,
"locator": f"2___Plant_Y2025.xlsx / Plant / row {line}; Plant Code={code}",
}
if not changed:
ledger.append(
{**item, "decision": "duplicate", "reason": "No new substantive plant facts"}
)
continue
candidate = {**old, **changed}
candidate["sources"] = sorted(set(old["sources"]) | {source_id})
mappings = json.loads(old["field_sources_json"])
evidence = json.loads(old["evidence_json"])
for key in changed:
mappings[key] = [source_id]
evidence[key] = {
"locator": item["locator"],
"original_value": grid_values
if key == "grid_connection"
else {"Latitude": plant.get("Latitude"), "Longitude": plant.get("Longitude")},
"confidence": "high",
"unit": "degrees" if key in {"latitude", "longitude"} else None,
"normalization": (
"Exact plant-code join; shared plant-site metadata only; "
"no unit-specific location claim"
),
"source_columns": ["Latitude", "Longitude"]
if key != "grid_connection"
else list(
k
for k in plant
if k and ("Voltage" in k or "Authority" in k or "System Owner" in k)
),
"source_reporting_year": 2025,
}
candidate["field_sources_json"] = canonical_json(mappings)
candidate["evidence_json"] = canonical_json(evidence)
patches.append(candidate)
ledger.append({**item, "decision": "enriched", "fields": sorted(changed)})
return patches, ledger
[docs]
def parse_plant_patches(
data: bytes, records: list[dict[str, Any]], *, source_id: str
) -> tuple[list[dict[str, Any]], list[dict[str, Any]]]:
"""Read the original 2025 EIA archive without network access."""
with zipfile.ZipFile(io.BytesIO(data)) as archive:
book = openpyxl.load_workbook(
io.BytesIO(archive.read("2___Plant_Y2025.xlsx")), read_only=True, data_only=True
)
try:
it = book["Plant"].values
if not str(next(it)[0]).startswith("2025 Form EIA-860"):
raise ValueError("Wrong plant reporting year")
headers = next(it)
if not {"Plant Code", "State", "Latitude", "Longitude", "Grid Voltage (kV)"} <= set(
headers
):
raise ValueError("Missing plant headers")
return plant_patches(
records, [dict(zip(headers, row, strict=True)) for row in it], source_id=source_id
)
finally:
book.close()