In this Jupyter Notebook we will analyze the energy consumption of different sources from Spain.

Energy Consumption Analysis

Using a dataset of energy consumption in Spain, we will use statistic techniques to extract insightful information with data manipulation and visualization.

Context

In a paper released early 2019, forecasting in energy markets is identified as one of the highest leverage contribution areas of Machine/Deep Learning toward transitioning to a renewable based electrical infrastructure.

Content

This dataset contains 4 years of electrical consumption, generation, pricing, and weather data for Spain. Consumption and generation data was retrieved from ENTSOE, a public portal for Transmission Service Operator (TSO) data. Settlement prices were obtained from the Spanish TSO Red Electric España. Weather data was purchased as part of a personal project from the Open Weather API for the 5 largest cities in Spain and made public here.

Acknowledgements

This data is publicly available via ENTSOE and REE and may be found in the links above.

The Data

This is a dataset containing two files, energy_dataset.csv and the weather_features.csv.

Energy Dataset

The energy dataset contains 29 columns:

• time: datetime index localized to CET
• generation biomass: generated biomass energy in MW
• generation fossil brown coal/lignite: generated coal energy in MW
• generation fossil coal-derived gas: generated coal-derived gas energy in MW
• generation fossil gas: generated natural gas energy in MW
• generation fossil hard coal: generated hard coal energy in MW
• generation fossil oil: generated conventional oil energy in MW
• generation fossil oil shale: generated unconventional oil energy in MW
• generation fossil peat: generated peat energy in MW
• generation geothermal: generated geothermal energy in MW
• generation hydro pumped storage aggregated: generated hydro pumped storage aggregated energy in MW
• generation hydro pumped storage consumption: generated hydro pumped storage consumption energy in MW
• generation hydro run-of-river and poundage: generated hydro run-of-river and poundage energy in MW
• generation hydro water reservoir: generated hydro water reservoir energy in MW
• generation marine: generated marine (wave) energy in MW
• generation nuclear: generated nuclear energy in MW
• generation other: other non-renewable sources of energy in MW
• generation other renewable: other renewable sources of energy in MW
• generation solar: generated solar energy in MW
• generation waste: generated waste energy in MW
• generation wind offshore: generated wind (offshore) energy in MW
• generation wind onshore: generated wind (onshore) energy in MW
• forecast solar day ahead: forecasted solar energy generation
• forecast wind offshore eday ahead: forecasted offshore wind generation
• forecast wind onshore day ahead: forecasted onshore wind generation
• total load forecast: forecasted electrical demand
• total load actual: actual electrical demand
• price day ahead: forecasted price EUR/MWh
• price actual: price in EUR/MWh

Weather Features

The weather datset contains 17 columns:

• dt_iso: datetime index localized to CET
• city_name: name of city
• temp: temperature in k (Kelvin)
• temp_min: minimum in k
• temp_max: maximum in k
• pressure: pressure in hPa
• humidity: humidity in %
• wind_speed: wind speed in m/s
• wind_deg: wind direction
• rain_1h: rain in last hour in mm
• rain_3h: rain last 3 hours in mm
• snow_3h: show last 3 hours in mm
• clouds_all: cloud cover in %
• weather_id: Code used to describe weather
• weather_main: Short description of current weather
• weather_description: Long description of current weather
• weather_icon: Weather icon code for website

Data Analysis

We will simplify the data for the analysis, by combining columns and grouping by year and month.

Loading the data

# To supress warnings and deprecated messages
import warnings

warnings.filterwarnings("ignore")

# Core
import numpy as np
import pandas as pd

# Visualization
import seaborn as sns
import matplotlib.pyplot as plt

%matplotlib inline

energy = pd.read_csv("energy_dataset.csv")
energy

weather = pd.read_csv("weather_features.csv")
weather

Checking data types

Using the method info:

Energy data

energy.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 35064 entries, 0 to 35063
Data columns (total 29 columns):
 #   Column                                       Non-Null Count  Dtype  
---  ------                                       --------------  -----  
 0   time                                         35064 non-null  object 
 1   generation biomass                           35045 non-null  float64
 2   generation fossil brown coal/lignite         35046 non-null  float64
 3   generation fossil coal-derived gas           35046 non-null  float64
 4   generation fossil gas                        35046 non-null  float64
 5   generation fossil hard coal                  35046 non-null  float64
 6   generation fossil oil                        35045 non-null  float64
 7   generation fossil oil shale                  35046 non-null  float64
 8   generation fossil peat                       35046 non-null  float64
 9   generation geothermal                        35046 non-null  float64
 10  generation hydro pumped storage aggregated   0 non-null      float64
 11  generation hydro pumped storage consumption  35045 non-null  float64
 12  generation hydro run-of-river and poundage   35045 non-null  float64
 13  generation hydro water reservoir             35046 non-null  float64
 14  generation marine                            35045 non-null  float64
 15  generation nuclear                           35047 non-null  float64
 16  generation other                             35046 non-null  float64
 17  generation other renewable                   35046 non-null  float64
 18  generation solar                             35046 non-null  float64
 19  generation waste                             35045 non-null  float64
 20  generation wind offshore                     35046 non-null  float64
 21  generation wind onshore                      35046 non-null  float64
 22  forecast solar day ahead                     35064 non-null  float64
 23  forecast wind offshore eday ahead            0 non-null      float64
 24  forecast wind onshore day ahead              35064 non-null  float64
 25  total load forecast                          35064 non-null  float64
 26  total load actual                            35028 non-null  float64
 27  price day ahead                              35064 non-null  float64
 28  price actual                                 35064 non-null  float64
dtypes: float64(28), object(1)
memory usage: 7.8+ MB

Except for the column time, which is of the object type, all the columns are of the numeric type.

We will fix the time column by converting it to datetime.

energy['time'] = pd.to_datetime(energy['time'], format='%Y-%m-%d', utc=True)
print(energy.info())
energy.head()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 35064 entries, 0 to 35063
Data columns (total 29 columns):
 #   Column                                       Non-Null Count  Dtype              
---  ------                                       --------------  -----              
 0   time                                         35064 non-null  datetime64[ns, UTC]
 1   generation biomass                           35045 non-null  float64            
 2   generation fossil brown coal/lignite         35046 non-null  float64            
 3   generation fossil coal-derived gas           35046 non-null  float64            
 4   generation fossil gas                        35046 non-null  float64            
 5   generation fossil hard coal                  35046 non-null  float64            
 6   generation fossil oil                        35045 non-null  float64            
 7   generation fossil oil shale                  35046 non-null  float64            
 8   generation fossil peat                       35046 non-null  float64            
 9   generation geothermal                        35046 non-null  float64            
 10  generation hydro pumped storage aggregated   0 non-null      float64            
 11  generation hydro pumped storage consumption  35045 non-null  float64            
 12  generation hydro run-of-river and poundage   35045 non-null  float64            
 13  generation hydro water reservoir             35046 non-null  float64            
 14  generation marine                            35045 non-null  float64            
 15  generation nuclear                           35047 non-null  float64            
 16  generation other                             35046 non-null  float64            
 17  generation other renewable                   35046 non-null  float64            
 18  generation solar                             35046 non-null  float64            
 19  generation waste                             35045 non-null  float64            
 20  generation wind offshore                     35046 non-null  float64            
 21  generation wind onshore                      35046 non-null  float64            
 22  forecast solar day ahead                     35064 non-null  float64            
 23  forecast wind offshore eday ahead            0 non-null      float64            
 24  forecast wind onshore day ahead              35064 non-null  float64            
 25  total load forecast                          35064 non-null  float64            
 26  total load actual                            35028 non-null  float64            
 27  price day ahead                              35064 non-null  float64            
 28  price actual                                 35064 non-null  float64            
dtypes: datetime64[ns, UTC](1), float64(28)
memory usage: 7.8 MB
None

Weather Features

weather.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 178396 entries, 0 to 178395
Data columns (total 17 columns):
 #   Column               Non-Null Count   Dtype  
---  ------               --------------   -----  
 0   dt_iso               178396 non-null  object 
 1   city_name            178396 non-null  object 
 2   temp                 178396 non-null  float64
 3   temp_min             178396 non-null  float64
 4   temp_max             178396 non-null  float64
 5   pressure             178396 non-null  int64  
 6   humidity             178396 non-null  int64  
 7   wind_speed           178396 non-null  int64  
 8   wind_deg             178396 non-null  int64  
 9   rain_1h              178396 non-null  float64
 10  rain_3h              178396 non-null  float64
 11  snow_3h              178396 non-null  float64
 12  clouds_all           178396 non-null  int64  
 13  weather_id           178396 non-null  int64  
 14  weather_main         178396 non-null  object 
 15  weather_description  178396 non-null  object 
 16  weather_icon         178396 non-null  object 
dtypes: float64(6), int64(6), object(5)
memory usage: 23.1+ MB

Most of the columns are numeric. dt_iso, city_name, weather_main, weather_description, and weather_icon are of the object type. We will convert dt_iso to the datetime type and rename it as time:

weather['dt_iso'] = pd.to_datetime(weather['dt_iso'], format='%Y-%m-%d', utc=True)
weather.rename(columns={'dt_iso':'time'}, inplace=True)
print(weather.info())
weather.head()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 178396 entries, 0 to 178395
Data columns (total 17 columns):
 #   Column               Non-Null Count   Dtype              
---  ------               --------------   -----              
 0   time                 178396 non-null  datetime64[ns, UTC]
 1   city_name            178396 non-null  object             
 2   temp                 178396 non-null  float64            
 3   temp_min             178396 non-null  float64            
 4   temp_max             178396 non-null  float64            
 5   pressure             178396 non-null  int64              
 6   humidity             178396 non-null  int64              
 7   wind_speed           178396 non-null  int64              
 8   wind_deg             178396 non-null  int64              
 9   rain_1h              178396 non-null  float64            
 10  rain_3h              178396 non-null  float64            
 11  snow_3h              178396 non-null  float64            
 12  clouds_all           178396 non-null  int64              
 13  weather_id           178396 non-null  int64              
 14  weather_main         178396 non-null  object             
 15  weather_description  178396 non-null  object             
 16  weather_icon         178396 non-null  object             
dtypes: datetime64[ns, UTC](1), float64(6), int64(6), object(4)
memory usage: 23.1+ MB
None

print('Date range for Energy data:')
print(energy['time'].min())
print(energy['time'].max())

Date range for Energy data:
2014-12-31 23:00:00+00:00
2018-12-31 22:00:00+00:00

print('Date range for Weather data:')
print(weather['time'].min())
print(weather['time'].max())

Date range for Weather data:
2014-12-31 23:00:00+00:00
2018-12-31 22:00:00+00:00

Both data have the same date range.

Weather

Before extracting year, month, and day from the weather dataframe, we will understand it better. How many different cities there are in the data?

print(weather['city_name'].unique())
print('Number of cities: ', weather['city_name'].nunique())

['Valencia' 'Madrid' 'Bilbao' ' Barcelona' 'Seville']
Number of cities:  5

There are 5 different cities in the data (Valencia, Madrid, Bilbao, Barcelona, Seville) meaning that the weather information are from these locations. However, the generation data (energy) contains the total energy production in Spain.

To match the structure of the files we have, we can average the weather columns (the numeric ones) over the dates and assume that they are the mean weather of Spain for that specific time.

print(energy.shape[0])
print(weather.shape[0]/5)

35064
35679.2

Apparently, at least one of the cities may have more dates than the production data.

Check, for all cities, how many rows of data they have

weather['city_name'][weather['city_name']==' Barcelona'] = 'Barcelona'
weather.groupby(weather['city_name'])['city_name'].count()

city_name
Barcelona    35476
Bilbao       35951
Madrid       36267
Seville      35557
Valencia     35145
Name: city_name, dtype: int64

Apparently, all the cities have at least slight more data than the energy production data. We need further analysis to understand the reason.

Grouping the Weather Data

Now, the next step is to average the numeric weather info for each date. So, we just need to groupby the data by time and compute the mean.

new_weather = weather.groupby(weather['time'], as_index=False).mean()
new_weather.head()

print(new_weather.shape[0])
print(energy.shape[0])

35064
35064

Now the number of rows is the same for both data files.

Sum columns

In this part, to make the analysis easier, let’s sum together, in the energy dataframe, the production of fossils derivative in one column, renewable in others, and so on.

First, let’s select all the column names that contain the word fossil. To extract the columns names from a dataframe:

df.columns

or

list(df)

Let’s use the second option, as we are sure that they will be extracted to a list.

cols = list(energy)
cols

['time',
 'generation biomass',
 'generation fossil brown coal/lignite',
 'generation fossil coal-derived gas',
 'generation fossil gas',
 'generation fossil hard coal',
 'generation fossil oil',
 'generation fossil oil shale',
 'generation fossil peat',
 'generation geothermal',
 'generation hydro pumped storage aggregated',
 'generation hydro pumped storage consumption',
 'generation hydro run-of-river and poundage',
 'generation hydro water reservoir',
 'generation marine',
 'generation nuclear',
 'generation other',
 'generation other renewable',
 'generation solar',
 'generation waste',
 'generation wind offshore',
 'generation wind onshore',
 'forecast solar day ahead',
 'forecast wind offshore eday ahead',
 'forecast wind onshore day ahead',
 'total load forecast',
 'total load actual',
 'price day ahead',
 'price actual']

First, let’s only select the elements of the list that contain the word generation using conditions in a list comprehension.

cols_gen = [col for col in cols if 'generation' in col]
cols_gen

['generation biomass',
 'generation fossil brown coal/lignite',
 'generation fossil coal-derived gas',
 'generation fossil gas',
 'generation fossil hard coal',
 'generation fossil oil',
 'generation fossil oil shale',
 'generation fossil peat',
 'generation geothermal',
 'generation hydro pumped storage aggregated',
 'generation hydro pumped storage consumption',
 'generation hydro run-of-river and poundage',
 'generation hydro water reservoir',
 'generation marine',
 'generation nuclear',
 'generation other',
 'generation other renewable',
 'generation solar',
 'generation waste',
 'generation wind offshore',
 'generation wind onshore']

Now we can separate the elements that contain the word fossil and the ones that don’t (which are the renewable energy).

cols_fossil = [col for col in cols_gen if 'fossil' in col]
cols_fossil

['generation fossil brown coal/lignite',
 'generation fossil coal-derived gas',
 'generation fossil gas',
 'generation fossil hard coal',
 'generation fossil oil',
 'generation fossil oil shale',
 'generation fossil peat']

cols_renew = [col for col in cols_gen if 'fossil' not in col]
cols_renew

['generation biomass',
 'generation geothermal',
 'generation hydro pumped storage aggregated',
 'generation hydro pumped storage consumption',
 'generation hydro run-of-river and poundage',
 'generation hydro water reservoir',
 'generation marine',
 'generation nuclear',
 'generation other',
 'generation other renewable',
 'generation solar',
 'generation waste',
 'generation wind offshore',
 'generation wind onshore']

• Now we can sum these columns using the pandas method sum with the parameter axis = 1.
• Create a new column for fossil generation and another one for renewable generation.
• Finally drop the previous columns.

energy['fossil_generation'] = energy[cols_fossil].sum(axis=1)
energy.head()

energy['renewable_generation'] = energy[cols_renew].sum(axis=1)
energy.head()

Let’s also create a column with the total energy generation.

energy['total_generation'] = energy['fossil_generation'] + \
    energy['renewable_generation']
energy.head()

Now that we have the summarized columns, we can drop the generation ones:

energy.drop(columns = cols_gen, inplace=True)
energy.head()

Let’s also drop the forecast columns as we are not doing any modeling to compare.

cols_forecast = [col for col in list(energy) if 'forecast' in col]
cols_forecast

['forecast solar day ahead',
 'forecast wind offshore eday ahead',
 'forecast wind onshore day ahead',
 'total load forecast']

energy.drop(columns=cols_forecast, inplace=True)
energy.head()

Drop weather_id column

new_weather.drop(columns=['weather_id'], inplace=True)
new_weather.head()

Merging Dataframes

Merging dataframes on time.

data = energy.merge(new_weather, on='time', how='inner')
data

Now we have a new dataframe with 35064 rows and 21 columns.

Extracting year and month from date columns

After we converted the dates from object to datetime, now we can use methods inside Pandas to get the year, the month and add them to new columns.

Data

data['year'] = data['time'].dt.year
data['month'] = data['time'].dt.month_name()
data.loc[:3,['year', 'month']]

Checking for duplicated rows

data.duplicated().sum()

0

Grouping the data

This analysis can be simplified by averaging the columns by year/month.

data_month = data.groupby(['year', 'month'], as_index=False).mean()
data_month.head()

Now we reduced the data to 48 rows.

Note that the first row, for December 2014, comes from the average of a single day. Let’s remove it.

data_month.drop(0, axis=0, inplace=True)  # Dropping first row
data_month.reset_index(inplace=True, drop=True)  # Reseting indexes
data_month.head()

Now we have the data ready for plotting.

Data Visualization

We organized our data and now we can start the visualization step.

Let’s start by checking the columns statistics.

data_month.describe().T

• Data goes from 2015 to 2018
• Mean and median of all columns seem to have close values, suggesting a symmetric distribution

Checking data distribution

Distribution of the features can be checked with the boxplot and histograms.

Fossil Energy

plt.figure(figsize=(10,3))
sns.boxplot(data=data_month, x='fossil_generation');

plt.figure(figsize=(10,5))
sns.histplot(data=data_month, x='fossil_generation', bins=10);

For the fossil energy production, the distribution is close fo normal with no outliers.

A first comment is that we will keep using the boxplot and the histogram for all the features. To make it easier, let’s create a function to help us.

def hist_box(data, feature, figsize=(12, 7)):

    # Subplot canvas
    fig, (ax_box, ax_hist) = plt.subplots(nrows=2, sharex=True,
                                          gridspec_kw={"height_ratios": (0.25, 0.75)}, figsize=figsize)

    # Boxplot on top
    # boxplot will be created and a triangle will indicate the mean value of the column
    sns.boxplot(data=data, x=feature, ax=ax_box, showmeans=True, color="pink")

    # Histogram on bottom
    # histogram will be created
    sns.histplot(data=data, x=feature, ax=ax_hist)

    # Add mean and median to histogram
    ax_hist.axvline(data[feature].mean(), color="green")  # mean
    ax_hist.axvline(data[feature].median(), color="orange")  # median

    # Title
    fig.suptitle("Distribution of " + feature, fontsize=16)

hist_box(data_month, "fossil_generation")

We can confirm that the mean (green) and and median (orange) are close to each other.

Renewable Energy

hist_box(data_month, "renewable_generation")

The distribution is right skewed with outliers.

# Filtering data for outlier
data_month[data_month["renewable_generation"] > 23000]

March 2018 is shown as an outlier, with a higher average renewable energy generation.

Total Energy

hist_box(data_month, "total_generation")

The distribution is right skewed with no outliers.

Total Load

hist_box(data_month, "total load actual")

The total usage of all types of energy has a symmetric distribution.

Temperature

Temp

hist_box(data_month, "temp")

Temperature seems to be bi-modal (two peaks). Probably Summer and Winter effects.

Min and max temperatures

hist_box(data_month, "temp_min")

hist_box(data_month, "temp_max")

Same observation as temp.

Pressure

hist_box(data_month, "pressure")

Pressure has a huge outlier. Let’s look at it closer.

data_month[data_month['pressure'] > 1500]

February 2015 is the outlier for pressure, with an average pressure more than 3 times larger than any other month, pointing to something wrong. We can’t check which day, but we can replace the value with the average for the month of February:

data_month.loc[3, 'pressure'] = data_month[(data_month['month'] == 'February') & (data_month['year'] != 2015)]['pressure'].mean()

hist_box(data_month, "pressure")

The distribution for the pressure is right skewed with outliers that are now, apparently, possible to happen.

Humidity

hist_box(data_month, "humidity")

Humidity has a symmetric distribution with no outliers.

Wind

Wind Speed

hist_box(data_month, "wind_speed")

A right skewed distribution with outliers over 3.2.

data_month[data_month["wind_speed"] > 3.2]

These outliers are from months with higher production on renewable energy, probably driven by the wind power.

Wind Direction

hist_box(data_month, "wind_deg")

Wind direction has a close to normal distribution with outliers.

data_month[data_month["wind_deg"] > 190]

data_month[data_month["wind_deg"] < 130]

Wind direction seems to have correlation with the renewable energy production, as higher angles suggest higher production.

Rain

hist_box(data_month, 'rain_1h')

hist_box(data_month, 'rain_3h')

Both distributions are right skewed and contain outliers.

Snow

hist_box(data_month, 'snow_3h')

For most of the days, the quantity of snowfall is zero.

Clouds

hist_box(data_month, 'clouds_all')

It is a right skewed distribution without outliers.

Energy Price

hist_box(data_month, 'price actual')

The price of the energy is left skewed with no outliers.

Pairs Plot

Let’s quickly check the scatter plot of the features with each other using the pairplot.

sns.pairplot(data_month);

• Temperature features are highly related to each other.
• Humidity seems to have significant correlation with temperature.
• Wind speed and direction seem to influence the renewable energy production.

# Converting columns year and month to categorical
data_month["month"] = data_month["month"].astype('category')
data_month["year"] = data_month["year"].astype('category')

sns.pairplot(data_month, hue="year");

Price, load, and energy generation changes over the years.

Energy Generation in Time

Energy data

We can visualize energy generation, load, and price over the years.

plt.figure(figsize=(17, 7))
sns.lineplot(data=data, x="time", y="price actual");

data[data['price actual'] == data['price actual'].max()]

We can see how the price of energy has changed in the dataset, with a higher peak in October 23th 2017, and a low period from January to June 2016.

plt.figure(figsize=(23, 7))
sns.lineplot(data=energy, x="time", y="total load actual");

The total load has a fairly high range (20000 to 40000), but is relatively steady over the years.

plt.figure(figsize=(17, 7))
sns.lineplot(data=energy, x="time", y="fossil_generation");

Energy from fossil sources has kept relatively steady over time.

plt.figure(figsize = (17, 7))
sns.lineplot(data = energy, x = "time", y = "renewable_generation");

Same for the renewable energy.

Let’s now check the production, load, and price average per month and per year.

Price

plt.figure(figsize = (15,8))
sns.barplot(data = data_month, x = "year", y = "price actual");

months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

plt.figure(figsize = (15,8))
sns.barplot(data = data_month, x = "month", y = "price actual", order = months);

Price suffered a considerable drop in 2016, but has maintaining the same price over the years. And prices are lower by the end of Winter and during the Spring.

Load

plt.figure(figsize = (15,8))
sns.barplot(data = data_month, x = "year", y = "total load actual");

sns.lineplot(data = data_month, x='year', y='total load actual', marker='o');

plt.figure(figsize=(15, 8))
sns.barplot(data=data_month, x="month", y="total load actual", order=months)

Demand for energy is slightly increasing over the years. As per months, end of Winter and Spring are the periods with lower demand of energy, which may explain the lower price during that time of the year.

Fossil Generation

plt.figure(figsize=(15, 8))
sns.barplot(data=data_month, x="year", y="fossil_generation");

plt.figure(figsize=(15, 8))
sns.barplot(data=data_month, x="month", y="fossil_generation", order=months);

Fossil generation has fluctuated over the years, and follow the same trend as price and load for the months.

Renewable Generation

plt.figure(figsize=(15, 8))
sns.barplot(data=data_month, x="year", y="renewable_generation");

plt.figure(figsize = (15,8))
sns.barplot(data = data_month, x = "month", y = "fossil_generation", order = months);

Same trend as per the fossil generation.

Summary

Insights from the analysis:

1. There is a correlation between wind speed and direction with renewable energy generation
2. We could find an outlier (pressure) that might be an error, and we replaced it by the average of the months from others years
3. Energy demand, price, and production follow the same trend over the months, with lower valuer by the end of Winter and during Spring
4. Energy demand haven’t changed much over the years
5. Generation, for both fossil and renewable, fluctuate over the year
6. Price had a signifcant drop in 2016, and it requires further research to learn the reason.