---
name: uk-energy-trends-ingestion
description: "How to get DESNZ UK Energy Trends Tables data into a pandas DataFrame and Parquet: access, the request, the format, limits, quirks and terms."
---

# Load UK monthly electricity trends into pandas and Parquet

Source entry: https://signal-lab-jet.vercel.app/datasets/uk-energy-trends
Provider: https://www.gov.uk/government/collections/energy-trends
Checked against the provider's documentation: 2026-11-01

## Access
- No account or key; Energy Trends spreadsheets are public downloads.
- This example parses ET 5.4 directly from XLSX ZIP/XML content and preserves the provisional flag.

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

## Format
Multi-sheet XLSX workbook with cover, contents, notes, commentary and annual, quarterly and monthly electricity tables; ET 5.4 measures TWh by fuel, supply, trade and aggregate shares.

## Load into pandas and Parquet
Needs Python 3 with requests, pandas and pyarrow.
```python
"""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")
```
Run by us on 2026-11-01 (python 3.13.5 | pandas 3.1.0 | pyarrow 26.0.0 | requests 2.34.2). Result: 356 monthly observations 1997-01-01 to 2026-08-01 across 12 electricity series

## Limits
- Rate limit: Not stated.
- Monthly statistics are published two months in arrears and quarterly statistics one quarter in arrears.
- History and workbook structure vary by Energy Trends table.

## Quirks
- The checked ET 5.4 workbook stores the latest month as text with a provisional marker; remove the marker only after preserving it in a separate field.
- The marker [x] means data is unavailable and should become a missing value rather than zero.
- Workbook sheet positions and asset URLs can change with each release, so discover the latest file from the table page before scheduling ingestion.

## Terms and attribution
- GOV.UK content is OGL v3.0 except where otherwise stated.
- Acknowledge the source and link to the licence where possible.
- Terms page: https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/

## Sources
- https://www.gov.uk/government/collections/energy-trends: "Monthly data is published 2 months in arrears; quarterly data is published a quarter in arrears."
- https://www.gov.uk/government/statistics/electricity-section-5-energy-trends: "Monthly data focuses on fuel use and electricity generation by major power producers, and electricity consumption."
- https://www.gov.uk/government/statistics/electricity-section-5-energy-trends: "We publish these monthly tables on the last Thursday of each month."
- https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/: "Contains public sector information licensed under the Open Government Licence v3.0."

## Help improve this skill
Missing a source, or see something to improve? Call the MCP tool suggest_source or feedback (or POST /api/suggest or /api/feedback). Reviewed ideas: https://signal-lab-jet.vercel.app/agent-suggestions

Information for educational purposes only. Free tiers, limits and licenses belong to the providers and may change; check the provider's own terms before using the data.