Analysing UK electricity prices (Part 2)

Guy Lipman
4 min readFeb 28, 2019

In my last blog post, I explained how to obtain day ahead hourly prices, day ahead half-hourly prices, and imbalance prices for UK electricity for 2018. In this post I will perform some additional exploratory analysis on the data. In most cases I will include the python code I used to generate the results. Please note that I’m not a professional data scientist, so if you are, I’m sure you’ll know how to do this kind of analysis in a much better way.

I started off with a pandas dataframe containing all data for the year, at half-hourly level.

For simplicity, all dates are in CET, i.e. it does not adjust for daylight savings. It should also remind readers that the day ahead half-hourly are implied from Octopus Energy tariffs, which are capped, so I will not be seeing the highest half-hourly prices. To check the data is correct, I calculated some summary statistics:

import pandas as pd
import numpy as np
summary = {'mean': df.mean(), 'std': df.std(),
'min': df.min(), 'max': df.max()}
summary = pd.DataFrame(summary)
summary = summary.transpose()[['DA_H','DA_HH','Imbalance']]

which gave the following results, which match the summary statistics I reported in the previous post.

I then wanted to create some box and whisker plots for this data:

import matplotlib.pyplot as plt
cols = ['DA_H','DA_HH','Imbalance']
data = [df[col] for col in cols]
fig, ax = plt.subplots()
labels=['{}\nmin={:3.1f}\nmax={:3.1f}'.format(col, df[col].min(), df[col].max()) for col in cols]
ax.boxplot(data, whis=[5,95],
meanprops={'marker': 'x', 'markeredgecolor': 'black'})

In these diagrams, the boxes represent the 2nd and 3rd quartiles of the prices, and the whiskers reach up to the 95th percentile and down to the 5th percentile. The x marker indicates the mean, which in all cases is higher than the median. Minimum and maximum values are indicated below the chart.

I next wanted to see how the half-hourly data differed by month. I used the following code:

df['month'] = [d.month for d in]
data3 = []
for i in range(1,13):

fig, ax = plt.subplots()
ax.boxplot(data3, whis=[5,95],
meanprops={'marker': 'x', 'markeredgecolor': 'black'})

If I’m honest, I don’t draw too much of a conclusion from these plots. The values were higher in the last four months of the year, but I’m not sure that is evidence of seasonality (if anything, I’d expect January and February to be higher than December).

The next piece of analysis I wanted to do was to see what the distribution of the imbalance prices were, conditional on the day ahead half-hourly price. I would expect the expected imbalance prices to be higher when the day ahead prices were higher (otherwise there would be an opportunity to buy when day ahead prices were cheap, sell when they were expensive). To show this, I split the data into 7 buckets based on the day ahead half-hourly price, and generated a box and whisker plot for each:

bands =  [(df.DA_HH.min(), 40), (40, 50), (50, 60), 
(60,70), (70, 80), (80,90),
(90, df.DA_HH.max())]
data2 = []
labels2 = []
for band in bands:
seg = df[(df.DA_HH>=band[0])&(df.DA_HH<band[1])].Imbalance
labelstr = '{:3.0f}-{:3.0f}\nn={}\nmin={:3.0f}\nmax={:3.0f}'
labelstr = labelstr.format(band[0], band[1], len(seg),
seg.min(), seg.max())
labels2.append(labelstr )
fig, ax = plt.subplots()
ax.boxplot(data2, whis=[5,95],
meanprops={'marker': 'x', 'markeredgecolor': 'black'})

From this I can see that, as I expected, when the day ahead prices were lowest, the imbalance prices were lowest. However, there is significant range within each group — greater than the differences between groups.

I don’t really have any great insights from this analysis, but sometimes knowing that there are no surprises is itself useful. I suspect that the next bit of analysis I will do will be looking at generation and demand forecasts, and trying to understand to what extent this data can be used to predict what either day ahead or imbalance prices are going to be.



Guy Lipman

Fascinated by what makes societies and markets work, especially in sustainable energy. Views not necessarily reflect those of my employer.