EU Weekly Oil Bulletin

curl -O 'https://energy.ec.europa.eu/document/download/906e60ca-8b6a-44e7-8589-652854d2fd3f_en?filename=Weekly_Oil_Bulletin_Prices_History_maticni_4web.xlsx'

"""EU aggregate weekly taxed petroleum prices -> pandas -> Parquet."""
import io
import zipfile
import xml.etree.ElementTree as ET
import requests
import pandas as pd

URL = "https://energy.ec.europa.eu/document/download/906e60ca-8b6a-44e7-8589-652854d2fd3f_en?filename=Weekly_Oil_Bulletin_Prices_History_maticni_4web.xlsx"
response = requests.get(URL, timeout=120)
response.raise_for_status()
ns = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
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/sheet1.xml"), events=("end",)):
        if row.tag != ns + "row" or int(row.attrib["r"]) < 4:
            continue
        cells = {}
        for cell in row.findall(ns + "c"):
            ref = cell.attrib["r"]
            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)]
            cells["".join(c for c in ref if c.isalpha())] = value
        try:
            date_serial = float(cells.get("A", ""))
            values = [float(cells[column]) for column in "CDEFGH"]
        except (KeyError, ValueError):
            row.clear()
            continue
        if date_serial > 10000:
            rows.append({
                "date_serial": date_serial,
                "eu_eurosuper95_per_1000l": values[0],
                "eu_diesel_per_1000l": values[1],
                "eu_heating_oil_per_1000l": values[2],
                "eu_low_sulphur_fuel_oil_per_t": values[3],
                "eu_high_sulphur_fuel_oil_per_t": values[4],
                "eu_motor_lpg_per_1000l": values[5],
            })
        row.clear()
df = pd.DataFrame(rows)
df["date"] = pd.Timestamp("1899-12-30") + pd.to_timedelta(df.pop("date_serial"), unit="D")
df = df.sort_values("date")
df.to_parquet("eu_weekly_oil_prices.parquet", index=False)
print(len(df), "weekly EU observations", df["date"].min().date(), "to",
      df["date"].max().date(), "across", len(df.columns) - 1, "price series")