Analyzing Spanish Electricity Market Data from I90 Files

I90 files are the richest public dataset for understanding the Spanish electricity market at unit level. They contain the daily generation programs, real-time constraints, and curtailment data published by REE (Red Eléctrica de España). Yet most analysts still download them manually from the ESIOS website and wrestle with their multi-sheet Excel format.

In this article, we’ll automate the entire pipeline — from downloading I90 archives via the ESIOS API to producing interactive visualizations of curtailment patterns, generation mix, and price correlations. We use five weeks of data to keep execution fast, but the same code scales to any date range. We’ll use python-esios for API access and plotly for charts.

Questions

1. How do you download I90 files programmatically from the ESIOS API?
2. How do you parse the multi-sheet Excel structure into analysis-ready DataFrames?
3. What are the curtailment trends by technology?
4. How does curtailment correlate with electricity prices?
5. Which programming units are most affected?

Download I90 files

I90 archives are available through the ESIOS archives API. The I90DIA archive (daily programming unit data) has archive ID 34:

from esios import ESIOSClient

client = ESIOSClient()

archive = client.archives.get(34)
print(f"Archive: {archive.name} (ID: {archive.id})")

Archive: I90DIA (ID: 34)

Now let’s download five weeks of data. The download() method iterates day by day and caches each file locally — re-running is instant. It returns the list of downloaded file paths:

START_DATE = "2025-05-05"
END_DATE = "2025-06-08"

xls_files = archive.download(start=START_DATE, end=END_DATE)

To fetch a full year, just change the date range to start="2025-01-01", end="2025-12-31". The cache means you only download each day once.

Downloaded 35 I90 files.

Parse with I90Book

Each I90 file is an Excel workbook with multiple sheets — generation programs (PBF), real-time constraints, curtailment, and more. The I90Book class from python-esios handles parsing:

from esios.processing.i90 import I90Book

# Parse a single file to explore the structure
book = I90Book(xls_files[0])

The key sheets for our analysis:

• I90DIA26: PBF — Base Functioning Program (scheduled generation by unit)
• I90DIA03: Restricciones en el Mercado Diario — daily market constraints (curtailment)
• I90DIA08: Restricciones en Tiempo Real — real-time constraints

Let’s extract the curtailment data from I90DIA03:

sheet = book["I90DIA03"]
df_sample = sheet.df

The DataFrame has a multi-level index (Unidad de Programación, Sentido, constraint type, etc.) and a UTC DatetimeIndex with hourly resolution. The value column contains the energy in MWh.

Build the full dataset

Now let’s parse all files and concatenate into a single DataFrame:

all_curtailment = []

for xls_path in xls_files:
    try:
        book = I90Book(xls_path)
        sheet = book["I90DIA03"]
        df = sheet.df
        if not df.empty:
            all_curtailment.append(df.reset_index())
    except Exception:
        continue  # Some days may have format variations

df_curtailment = pd.concat(all_curtailment, ignore_index=True)

Sheet I90DIA03 contains both Subir (raise) and Bajar (lower) constraint adjustments. Curtailment — energy that could have been generated but wasn’t — corresponds to the Bajar direction.

We also need to exclude the ECO redespacho type, which represents economic redispatch (market-based rebalancing) rather than technical curtailment:

df_curtailment = df_curtailment[
    (df_curtailment["Sentido"] == "Bajar")
    & (~df_curtailment["Redespacho"].str.startswith("ECO"))
].copy()
df_curtailment["value"] = df_curtailment["value"].abs()

Dataset size: 46,560 rows, 246 unique programming units, covering 2025-05-05 to 2025-06-08.

Curtailment analysis

Daily curtailment trends

Let’s aggregate curtailment by day to see how it varies across the week:

Curtailment by technology

The I90 unit codes alone don’t tell you the technology — you need REE’s programming units registry. Download the CSV export from esios.ree.es/en/programming-units and save it as data/programming_units.csv. Then load it as a lookup table and map the detailed production types into chart-friendly categories:

df_units = pd.read_csv("data/programming_units.csv", sep=";")
unit_tech = df_units.set_index("UP Code")["Production Type"]

TECH_MAP = {
    "Onshore wind": "Wind",
    "Offshore wind": "Wind",
    "Solar PV": "Solar PV",
    "Solar thermal": "Solar thermal",
    "Hydro UGH": "Hydro",
    "Hydro non UGH": "Hydro",
    "Turbine pumping": "Hydro",
    "Pump consumption": "Hydro",
    "Nuclear": "Nuclear",
    "Combined cycle GT": "Gas CCGT",
    "Natural Gas": "Gas CCGT",
    "Natural Gas Cogeneration": "Cogeneration",
    "Biogas": "Cogeneration",
    "Biomass": "Cogeneration",
    "Oil products and coal": "Other",
    "Soft coal/Anthracite": "Other",
    "Sub-bituminous coal": "Other",
    "Fuel": "Other",
    "Mining subproducts": "Other",
    "Sundry waste": "Cogeneration",
    "Household and similar wastes": "Cogeneration",
    "Storage": "Other",
    "Hybrid renewable-storage": "Other",
    "Renewable hybrid": "Solar PV",
    "Renewable-renewable-thermal hybrid": "Cogeneration",
    "Residual energy": "Cogeneration",
    "Geothermal and Ocean": "Other",
}

def classify_technology(unit_code: str) -> str:
    prod_type = unit_tech.get(unit_code, "Unknown")
    return TECH_MAP.get(prod_type, "Other")

Hourly curtailment heatmap

The most valuable insight: when does curtailment happen? A heatmap of hour × day reveals the intra-day pattern:

The heatmap reveals when the system operator imposes the most curtailment. Solar curtailment concentrates during midday hours (10:00–16:00), while wind curtailment spreads across nighttime when demand is lowest. With a full year of data, you’d see clear seasonal patterns too.

Generation mix from PBF

The PBF (Base Functioning Program) in sheet I90DIA26 shows the scheduled generation for each unit. Let’s parse it to understand the generation mix:

all_pbf = []

for xls_path in xls_files:
    try:
        book = I90Book(xls_path)
        sheet = book["I90DIA26"]
        df = sheet.df
        if not df.empty:
            all_pbf.append(df.reset_index())
    except Exception:
        continue

df_pbf = pd.concat(all_pbf, ignore_index=True)

Price correlation

Does curtailment increase when prices drop? Let’s overlay OMIE day-ahead prices with curtailment volumes. We’ll fetch indicator 600 (day-ahead market price for Spain) from the ESIOS API:

prices = client.indicators.get(600)
df_prices = prices.historical(START_DATE, END_DATE, geo_ids=[3])  # Spain (geo_id=3)

Now let’s compute daily averages for both curtailment and prices and plot them together:

The pattern is visible: days with higher renewable output (and thus more curtailment) tend to have lower prices. Over a full year, you’d see an even clearer negative correlation — high renewable generation pushes both curtailment up and prices down.

Top curtailed programming units

Which specific units bear the most curtailment? This is crucial for investors evaluating renewable projects:

The most curtailed unit in this period is FGASNE2 (Solar PV) with 11,470 MWh of curtailment. At an average captured price of 30 €/MWh, that represents approximately 344 k€ in lost revenue — in just five weeks.

Conclusions

We’ve built a complete analysis pipeline for Spanish electricity market data using I90 files:

1. Automated download via python-esios — no manual ESIOS website navigation needed
2. Structured parsing with I90Book — handles the complex multi-sheet Excel format
3. pandas analysis — groupby, pivot, and merge for curtailment and generation data
4. Interactive visualizations with Plotly — heatmaps, stacked bars, and dual-axis charts

We used five weeks to keep execution tractable, but the same code works for any date range — just change START_DATE and END_DATE. With a full year you’d see seasonal patterns in the heatmap and get more representative top-unit rankings.

Related articles

• Automate preprocessing of I90 Excel files — step-by-step manual preprocessing approach
• Build a relational database for I90 files — storing I90 data in a normalized database
• python-esios library tutorial — comprehensive guide to the python-esios library
• Fetch OMIE electricity prices — price data directly from OMIE