Skip to article frontmatterSkip to article content
Site not loading correctly?

This may be due to an incorrect BASE_URL configuration. See the MyST Documentation for reference.

Pandas is a an open source library providing tabular data structures and data analysis tools.In other words, if you can imagine the data in an Excel spreadsheet, then Pandas is the tool for the job.

Package Imports

This will be our first experience with importing a package.

Usually we import pandas with the alias pd.

We might also need numpy, Python’s main library for numerical computations.

import numpy as np
import pandas as pd

Series

A Series represents a one-dimensional array of data. It is similar to a dictionary consisting of an index and values, but has more functions.

names = ["Neckarwestheim", "Isar 2", "Emsland"]
values = [1269, 1365, 1290]
s = pd.Series(values, index=names)
s
Neckarwestheim 1269 Isar 2 1365 Emsland 1290 dtype: int64
dictionary = {
    "Neckarwestheim": 1269,
    "Isar 2": 1365,
    "Emsland": 1290,
}
s = pd.Series(dictionary)
s
Neckarwestheim 1269 Isar 2 1365 Emsland 1290 dtype: int64

Arithmetic operations can be applied to the whole pd.Series.

s**0.5
Neckarwestheim 35.623026 Isar 2 36.945906 Emsland 35.916570 dtype: float64

We can access the underlying index object if we need to:

s.index
Index(['Neckarwestheim', 'Isar 2', 'Emsland'], dtype='object')

We can get values back out using the index via the .loc attribute

s.loc["Isar 2"]
np.int64(1365)

Or by raw position using .iloc

s.iloc[2]
np.int64(1290)

We can pass a list or array to loc to get multiple rows back:

s.loc[["Neckarwestheim", "Emsland"]]
Neckarwestheim 1269 Emsland 1290 dtype: int64

DataFrame

Series are limited to a single column. A more useful Pandas data structure is the DataFrame. A DataFrame is basically a bunch of series that share the same index.

data = {
    "capacity": [1269, 1365, 1290],  # MW
    "type": ["PWR", "PWR", "PWR"],
    "start_year": [1989, 1988, 1988],
    "end_year": [np.nan, np.nan, np.nan],
}
df = pd.DataFrame(data, index=["Neckarwestheim", "Isar 2", "Emsland"])
df
Loading...

A wide range of statistical functions are available on both Series and DataFrames.

df.min()
capacity 1269 type PWR start_year 1988 end_year NaN dtype: object
df.mean(numeric_only=True)
capacity 1308.000000 start_year 1988.333333 end_year NaN dtype: float64

We can get a single column as a Series using python’s getitem syntax on the DataFrame object.

df["capacity"]
Neckarwestheim 1269 Isar 2 1365 Emsland 1290 Name: capacity, dtype: int64

Indexing works very similar to series

df.loc["Emsland"]
capacity 1290 type PWR start_year 1988 end_year NaN Name: Emsland, dtype: object

But we can also specify the column(s) and row(s) we want to access

df.at["Emsland", "start_year"]
np.int64(1988)

We can also add new columns to the DataFrame:

df["reduced_capacity"] = df.capacity * 0.8
df
Loading...

We can also remove columns or rows from a DataFrame:

df.drop("reduced_capacity", axis="columns", inplace=True)

We can also drop columns with only NaN values

df.dropna(axis=1)
Loading...

Or fill it up with default “fallback” data:

df.fillna(2023)
Loading...

Sorting Data

We can also sort the entries in dataframes, e.g. alphabetically by index or numerically by column values

df.sort_index()
Loading...
df.sort_values(by="capacity", ascending=False)
Loading...

Filtering Data

We can also filter a DataFrame using a boolean series obtained from a condition. This is very useful to build subsets of the DataFrame.

df.capacity > 1300
Neckarwestheim False Isar 2 True Emsland False Name: capacity, dtype: bool
df[df.capacity > 1300]
Loading...

We can also combine multiple conditions, but we need to wrap the conditions with brackets!

df[(df.capacity > 1300) & (df.start_year >= 1988)]
Loading...

Or we make SQL-like queries:

df.query("start_year == 1988")
Loading...
threshold = 1300
df.query("start_year == 1988 and capacity > @threshold")
Loading...

Modifying Values

In many cases, we want to modify values in a dataframe based on some rule. To modify values, we need to use .loc or .iloc

df.loc["Isar 2", "capacity"] = 1366
df
Loading...

Sometimes it can be useful to rename columns:

df.rename(columns=dict(type="reactor"))
Loading...

Sometimes it can be useful to replace values:

df.replace({"PWR": "Pressurized water reactor"})
Loading...

Time Series

Time indexes are great when handling time-dependent data.

Let’s first read some time series data, using the pd.read_csv() function, which takes a local file path ora link to an online resource.

The example data hourly time series for Germany in 2015 for:

  1. electricity demand from OPSD in GW

  2. onshore wind capacity factors from renewables.ninja in per-unit of installed capacity

  3. offshore wind capacity factors from renewables.ninja in per-unit of installed capacity

  4. solar PV capacity factors from renewables.ninja in per-unit of installed capacity

  5. electricity day-ahead spot market prices in €/MWh from EPEX Spot zone DE/AT/LU retrieved via SMARD platform

# Backup: https://cloud.pypsalabs.org/s/FSJ3qwQjqCxcWXQ/download/time-series-sample.csv
url = "https://raw.githubusercontent.com/pypsalabs/workshop-202610/main/berlin/data/time-series-sample.csv"

ts = pd.read_csv(url, index_col=0, parse_dates=True)

ts.head()
Loading...

We can use Python’s slicing notation inside .loc to select a date range, and then use the built-in plotting feature of Pandas:

ts.loc["2015-01-01":"2015-03-01", "load"].plot()
<Axes: >
<Figure size 640x480 with 1 Axes>
ts.loc["2015-05-01", "solar"].plot()
<Axes: >
<Figure size 640x480 with 1 Axes>

A common operation is to change the resolution of a dataset by resampling in time, which Pandas exposes through the resample function.

ts["onwind"].resample("ME").mean().plot()
<Axes: >
<Figure size 640x480 with 1 Axes>

Groupby Functionality

DataFrame objects have a groupby method. The simplest way to think about it is that you pass another series, whose values are used to split the original object into different groups.

Here’s an example which retrieves the total generation capacity per country:

fn = "https://raw.githubusercontent.com/PyPSA/powerplantmatching/master/powerplants.csv"
df = pd.read_csv(fn, index_col=0)
df.iloc[:5, :10]
Loading...
grouped = df.groupby("Country").Capacity.sum()
grouped.head()
Country Albania 2707.566000 Austria 27621.980368 Belgium 24199.510150 Bosnia and Herzegovina 5578.000000 Bulgaria 22191.440000 Name: Capacity, dtype: float64

Let’s break apart this operation a bit. The workflow with groupby can be divided into three general steps:

  1. Split: Partition the data into different groups based on some criterion.

  2. Apply: Do some caclulation within each group, e.g. minimum, maximum, sums.

  3. Combine: Put the results back together into a single object.

Grouping is not only possible on a single columns, but also on multiple columns. For instance, we might want to group the capacities by country and fuel type. To achieve this, we pass a list of functions to the groupby functions.

capacities = df.groupby(["Country", "Fueltype"]).Capacity.sum()
capacities
Country Fueltype Albania Battery 24.000000 Hydro 2079.366000 Solar 454.200000 Wind 150.000000 Austria Battery 73.920000 ... United Kingdom Oil 616.800000 Solar 13742.797835 Solid Biomass 4154.200000 Waste 2073.050000 Wind 39634.791500 Name: Capacity, Length: 328, dtype: float64

By grouping by multiple attributes, our index becomes a pd.MultiIndex (a hierarchical index with multiple levels.

capacities.index[:5]
MultiIndex([('Albania', 'Battery'), ('Albania', 'Hydro'), ('Albania', 'Solar'), ('Albania', 'Wind'), ('Austria', 'Battery')], names=['Country', 'Fueltype'])

We can use the .unstack function to reshape the multi-indexed pd.Series into a pd.DataFrame which has the second index level as columns.

capacities.unstack().tail().T
Loading...

Exercises

Task 1: Provide a list of unique fuel types included in the power plants dataset.

Notebook Cell
df.Fueltype.unique()
array(['Hydro', 'Natural Gas', 'Hard Coal', 'Lignite', 'Wind', 'Nuclear', 'Solid Biomass', 'Oil', 'Waste', 'Solar', 'Battery', 'Hydrogen Storage', 'Geothermal', 'Heat Storage', 'Other', 'Biogas', 'Mechanical Storage'], dtype=object)

Task 2: Filter the dataset by power plants with the fuel type “Hard Coal”. How many hard coal power plants are there?

Notebook Cell
coal = df.loc[df.Fueltype == "Hard Coal"]
coal
Loading...

Task 3: Identify the three largest coal power plants. In which countries are they located? When were they built?

Notebook Cell
coal.loc[coal.Capacity.nlargest(3).index]
Loading...

Task 4: What is the average “DateIn” of each “Fueltype”? Which type of power plants is the oldest on average?

Notebook Cell
2024 - df.groupby("Fueltype").DateIn.mean().sort_values()
Fueltype Hydrogen Storage 54.000000 Hard Coal 53.167683 Hydro 49.976034 Lignite 45.402685 Nuclear 43.907563 Other 20.953947 Oil 19.592767 Geothermal 19.525000 Waste 18.543524 Solid Biomass 15.591623 Biogas 11.830821 Wind 10.828076 Natural Gas 9.628990 Solar 6.531413 Battery 0.037083 Heat Storage NaN Mechanical Storage NaN Name: DateIn, dtype: float64

Task 5: In the time series provided, calculate the annual average capacity factors of wind and solar.

Notebook Cell
ts.mean()
load 54.736992 onwind 0.205556 offwind 0.362993 solar 0.122621 prices 31.835717 dtype: float64

Task 6: In the time series provided, calculate and plot the monthly average electricity price.

Notebook Cell
ts["prices"].resample("ME").mean().plot()
<Axes: >
<Figure size 640x480 with 1 Axes>
PyPSA Workshop Berlin
Annex: Python basics