DESNZ UK Energy Trends Tables

curl -O 'https://assets.publishing.service.gov.uk/media/6ae0cba87cf9b224890467e4/ET_5.4_OCT_26.xlsx'

"""UK Energy Trends ET 5.4 monthly electricity data -> pandas -> Parquet."""
import io
import zipfile
import xml.etree.ElementTree as ET
import requests
import pandas as pd

URL = "https://assets.publishing.service.gov.uk/media/6ae0cba87cf9b224890467e4/ET_5.4_OCT_26.xlsx"
response = requests.get(URL, timeout=120)
response.raise_for_status()
ns = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
selected = {
    "A": "month", "D": "mpp_supply_twh", "E": "coal_twh", "G": "gas_twh",
    "H": "nuclear_twh", "J": "wind_twh", "M": "solar_twh", "N": "bioenergy_twh",
    "T": "net_imports_twh", "V": "total_available_twh", "W": "low_carbon_twh",
    "X": "renewables_twh", "Y": "fossil_fuels_twh",
}
with zipfile.ZipFile(io.BytesIO(response.content)) as book:
    strings_root = ET.fromstring(book.read("xl/sharedStrings.xml"))
    strings = ["".join(t.text or "" for t in item.iter(ns + "t")) for item in strings_root]
    rows = []
    for _, row in ET.iterparse(book.open("xl/worksheets/sheet9.xml"), events=("end",)):
        if row.tag != ns + "row" or int(row.attrib["r"]) < 8:
            continue
        record = {}
        for cell in row.findall(ns + "c"):
            column = "".join(c for c in cell.attrib["r"] if c.isalpha())
            if column not in selected:
                continue
            value_node = cell.find(ns + "v")
            if value_node is None:
                continue
            value = value_node.text
            if cell.attrib.get("t") == "s":
                value = strings[int(value)]
            record[selected[column]] = value
        if record.get("month"):
            rows.append(record)
        row.clear()
df = pd.DataFrame(rows)
df["is_provisional"] = df["month"].str.contains("provisional", case=False)
df["month"] = df["month"].str.replace(r"\s*\[provisional\]", "", regex=True)
df["month"] = pd.to_datetime(df["month"], format="%B %Y", errors="coerce")
df = df.dropna(subset=["month"])
for column in df.columns.drop(["month", "is_provisional"]):
    df[column] = pd.to_numeric(df[column], errors="coerce")
df = df.sort_values("month")
df.to_parquet("uk_energy_trends_et54.parquet", index=False)
print(len(df), "monthly observations", df["month"].min().date(), "to",
      df["month"].max().date(), "across", len(df.columns) - 2, "electricity series")