Back to Blog

Electricity Tariffs

Electricity tariffs in Switzerland

ElCom, the Federal Electricity Commission, collects data on the electricity tariffs for household and companies. This data is published as Linked Data.

In this tutorial, we will show how to work with Linked Data. Mainly, we will see how to work with data on electricity tariffs.
We will look into how to query, process, and visualize it.

Setup

SPARQL endpoints

For electricity tariffs

Data on all electricity tariffs is published as Linked Data. It can be accessed with SPARQL queries.
You can send queries using HTTP requests. The API endpoint is https://lindas.admin.ch/query/.

For geodata

Different municipalities may have different tariffs. To understand their location, we will work with Swiss geodata. It is published as Linked Data. It can be accessed using API endpoint under https://ld.geo.admin.ch/query.

SPARQL client

Let's use SparqlClient from graphly to communicate with both databases. Graphly will allow us to:

  • send SPARQL queries
  • automatically add prefixes to all queries
  • format response to pandas or geopandas
In [1]:
import json
import string

import folium
import mapclassify
import matplotlib as mpl
import matplotlib.pyplot as plt
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

from graphly.api_client import SparqlClient

%matplotlib inline
In [2]:
# Uncomment to install dependencies in Colab environment
#!pip install mapclassify
#!pip install git+https://github.com/zazuko/graphly.git
In [3]:
sparql = SparqlClient("https://lindas.admin.ch/query")
geosparql = SparqlClient("https://ld.geo.admin.ch/query")

sparql.add_prefixes({
    "schema": "<http://schema.org/>",
    "cube": "<https://cube.link/>",
    "elcom": "<https://energy.ld.admin.ch/elcom/electricityprice/dimension/>",
    "admin": "<https://schema.ld.admin.ch/>"
})

geosparql.add_prefixes({
    "dct": "<http://purl.org/dc/terms/>",
    "geonames": "<http://www.geonames.org/ontology#>",
    "schema": "<http://schema.org/>",
    "geosparql": "<http://www.opengis.net/ont/geosparql#>",
})

SPARQL queries can become very long. To improve the readibility, we will work wih prefixes.

Using the add_prefixes method, we can define persistent prefixes. Every time you send a query, graphly will now automatically add the prefixes for you.

Data

Our goal is to understand the varaiability in electricity prices. To do so, we will work with electricity tariffs, and municipality data.

Electricity tariffs

Electricity prices are composed of:

  • Grid fee
    Price for transporting electricity from the power plant to the home. The revenue is used to finance the maintenance and expansion of the electricity grid, for example overhead lines, pylons and transformers.
  • Energy price
    Price for the electrical energy supplied. The grid operator either generates this energy with its own power plants or buys it from upstream suppliers. The energy price is also influenced by the type of energy source. A high share of renewable energy (e.g. wind, solar, biomass energy) usually leads to higher energy prices.
  • Municipality taxes
    Municipal and cantonal taxes and fees. These include, for example, concession fees or local political energy levies.
  • Aidfee
    Federal tax for the promotion of renewable energies, and the protection of waters and fish. The amount of the aidfee is set annually by the Federal Council. The levy is the same everywhere.

These components vary across:

  • time
  • products
  • consumption categories
  • energy providers
  • municipalities

The place where you live, the consumption category to which you belong, the energy provider you chose, and the product you subscribed for will affect your monthly bills.


Let's take a look at these differences. To simplify the results, we will work with data from 2020. We will also focus on a single standard product, which is available across all of Switzerland.

We can query the tariffs for different municipalities and categories as follows (heads up, the query may take a while to execute!):

In [4]:
query = """
SELECT ?municipality_id ?category ?energy ?grid ?aidfee (?community_fees + ?aidfee as ?taxes) ?fixcosts ?variablecosts 
FROM <https://lindas.admin.ch/elcom/electricityprice>
WHERE {
    <https://energy.ld.admin.ch/elcom/electricityprice/observation/> cube:observation ?observation.
    
    ?observation
      elcom:category/schema:name ?category;
      elcom:municipality ?municipality_id;
      elcom:period "2020"^^<http://www.w3.org/2001/XMLSchema#gYear>;
      elcom:product <https://energy.ld.admin.ch/elcom/electricityprice/product/standard>;
      elcom:fixcosts ?fixcosts;
      elcom:total ?variablecosts;
      elcom:gridusage ?grid;
      elcom:energy ?energy;
      elcom:charge ?community_fees;
      elcom:aidfee ?aidfee.
      
}
ORDER BY ?muncipality ?category ?variablecosts
"""

tariffs = sparql.send_query(query)
tariffs = tariffs.groupby(["municipality_id", "category"]).first().reset_index()
tariffs.head()
Out[4]:
municipality_id category energy grid aidfee taxes fixcosts variablecosts
0 https://ld.admin.ch/municipality/1 C1 7.721875 7.419375 2.3 2.46 1.20 17.60
1 https://ld.admin.ch/municipality/1 C2 7.368600 6.806200 2.3 2.46 0.32 16.64
2 https://ld.admin.ch/municipality/1 C3 7.232666 5.250667 2.3 2.46 3.82 14.94
3 https://ld.admin.ch/municipality/1 C4 7.058133 4.537034 2.3 2.46 3.15 14.06
4 https://ld.admin.ch/municipality/1 C5 7.058133 4.002167 2.3 2.46 2.88 13.52

Great! We can analyse all price components across municipalities and consumption categories. But what do these consumption categories stand for?

Let's query all categories and their description.

In [5]:
query = """
SELECT DISTINCT ?category ?description
WHERE {
  GRAPH <https://lindas.admin.ch/elcom/electricityprice> {
    
    ?s <https://energy.ld.admin.ch/elcom/electricityprice/dimension/category> ?category_uri.
    ?category_uri schema:name ?category .
    ?category_uri schema:description ?description .
  }
}
ORDER BY ?category
"""
df = sparql.send_query(query)
df
Out[5]:
category description
0 C1 8'000 kWh/Jahr: Kleinstbetrieb' max. beanspruc...
1 C2 30'000 kWh/Jahr: Kleinbetrieb' max. beanspruch...
2 C3 150'000 kWh/Jahr: Mittlerer Betrieb' max. bean...
3 C4 500'000 kWh/Jahr: Grosser Betrieb ' max. beans...
4 C5 500'000 kWh/Jahr: Grosser Betrieb' max. beansp...
5 C6 1'500'000 kWh/Jahr: Grosser Betrieb' max. bean...
6 C7 7'500'000 kWh/Jahr: Grosser Betrieb' max. bean...
7 H1 1'600 kWh/Jahr: 2-Zimmerwohnung mit Elektroherd
8 H2 2'500 kWh/Jahr: 4-Zimmerwohnung mit Elektroherd
9 H3 4'500 kWh/Jahr: 4-Zimmerwohnung mit Elektroher...
10 H4 4'500 kWh/Jahr: 5-Zimmerwohnung mit Elektroher...
11 H5 7'500 kWh/Jahr: 5-Zimmer-Einfamilienhaus mit E...
12 H6 25'000 kWh/Jahr: 5-Zimmer-Einfamilienhaus mit ...
13 H7 13'000 kWh/Jahr: 5-Zimmer-Einfamilienhaus mit ...
14 H8 7'500 kWh/Jahr: Grosse' hoch elektrifizierte E...

So we have 15 different categories. Those knowning german will notice that the categories cover househoulds and companies. The difference is in expected energy consumption.

The tariffs themselves are not very meaningful. "How much is my monthly bill?" is much more relatable question. Let's try to answer that.

In [6]:
def extract_consumption(description: str) -> int:
    """
    Extract average electricity consumption from a description.
    Args:
        description:  Category description for electricity tariffs
        
    Returns: 
        int:          Electricity consumption in kWh/year
    
    """
    
    number_as_string = description.split(" kWh/Jahr")[0]
    return int(number_as_string.translate(str.maketrans('', '', string.punctuation)))
In [7]:
cat2description = dict(zip(df.category, df.description))
cat2consumption = dict(zip(df.category, [extract_consumption(d) for d in df.description]))

tariffs["consumption"] = tariffs[["category"]].replace({"category": cat2consumption})
tariffs["monthly_bill"] = ((tariffs.consumption*tariffs.variablecosts/12 + tariffs.fixcosts)/100).round(2)
tariffs.head()
Out[7]:
municipality_id category energy grid aidfee taxes fixcosts variablecosts consumption monthly_bill
0 https://ld.admin.ch/municipality/1 C1 7.721875 7.419375 2.3 2.46 1.20 17.60 8000 117.35
1 https://ld.admin.ch/municipality/1 C2 7.368600 6.806200 2.3 2.46 0.32 16.64 30000 416.00
2 https://ld.admin.ch/municipality/1 C3 7.232666 5.250667 2.3 2.46 3.82 14.94 150000 1867.54
3 https://ld.admin.ch/municipality/1 C4 7.058133 4.537034 2.3 2.46 3.15 14.06 500000 5858.36
4 https://ld.admin.ch/municipality/1 C5 7.058133 4.002167 2.3 2.46 2.88 13.52 500000 5633.36

We can now use the tariffs table to analyze regional differences in energy prices.

Municipalities

Electricity prices vary across municipalities. The tariff table gives us an overview of prices at each municipality. But what are their names? Where are they located? And how many people live there?

This information can be obtained using the SPARQL Endpoind from Swisstopo, Switzerland's national mapping agency.

We can query the list of municipalities, alongside with its boundaries as follows:

In [8]:
query = """    
SELECT ?municipality_id ?municipality ?population ?boundary 

WHERE {
  ?muni_iri dct:hasVersion ?version ;
            geonames:featureCode geonames:A.ADM3 .
  
  ?version schema:validUntil "2020-12-31"^^<http://www.w3.org/2001/XMLSchema#date>;
           geonames:population ?population;
           schema:name ?municipality;
           geosparql:hasGeometry/geosparql:asWKT ?boundary.
  
  BIND(IRI(REPLACE(STR(?muni_iri), "https://ld.geo.admin.ch/boundaries/", "https://ld.admin.ch/")) AS ?municipality_id)
}

"""
communes = geosparql.send_query(query)
communes = communes.set_crs(epsg=4326)
communes.head()
Out[8]:
municipality_id municipality population boundary
0 https://ld.admin.ch/municipality/1001 Doppleschwand 782 POLYGON ((8.05946 46.99543, 8.05555 46.99679, ...
1 https://ld.admin.ch/municipality/1002 Entlebuch 3284 MULTIPOLYGON (((8.02272 46.97657, 8.01460 46.9...
2 https://ld.admin.ch/municipality/1004 Fl├╝hli 1961 POLYGON ((8.04694 46.78710, 8.03127 46.79021, ...
3 https://ld.admin.ch/municipality/1005 Hasle (LU) 1744 POLYGON ((8.06223 46.90957, 8.07054 46.91640, ...
4 https://ld.admin.ch/municipality/1007 Romoos 666 POLYGON ((7.95239 46.98131, 7.94833 46.99371, ...

You may notice that we defined a corrdinate reference system. The default projection for this endpoint is EPSG:4326, also known as WGS84.

SparqlClient automatically returns a geopandas datafrme. By defining the coordinate reference system, we can start using its geoanalytics capacities immediately!

Let's start by visually inspecting all the communes.

In [9]:
communes.plot()
Out[9]:
<AxesSubplot:>

Great! Now we have a geospatial representation of all swiss municipalities and we also have energy tariffs across the municipalities.

Let's join this information in one dataframe.

In [10]:
join = pd.merge(communes[["municipality_id", "municipality", "population", "boundary"]], tariffs, how="inner", on="municipality_id")
join.drop(columns=["variablecosts", "consumption"], inplace=True)
join.head()
Out[10]:
municipality_id municipality population boundary category energy grid aidfee taxes fixcosts monthly_bill
0 https://ld.admin.ch/municipality/1001 Doppleschwand 782 POLYGON ((8.05946 46.99543, 8.05555 46.99679, ... C1 3.006633 7.674333 2.3 3.067433 0.0000 91.67
1 https://ld.admin.ch/municipality/1001 Doppleschwand 782 POLYGON ((8.05946 46.99543, 8.05555 46.99679, ... C2 2.983378 7.656445 2.3 3.065644 0.0000 342.75
2 https://ld.admin.ch/municipality/1001 Doppleschwand 782 POLYGON ((8.05946 46.99543, 8.05555 46.99679, ... C3 1.237467 7.418445 2.3 3.041844 3.3920 1462.53
3 https://ld.admin.ch/municipality/1001 Doppleschwand 782 POLYGON ((8.05946 46.99543, 8.05555 46.99679, ... C4 1.113240 6.681366 2.3 2.801102 2.8176 4412.53
4 https://ld.admin.ch/municipality/1001 Doppleschwand 782 POLYGON ((8.05946 46.99543, 8.05555 46.99679, ... C5 1.113240 4.340000 2.3 2.625500 2.6400 3366.69

Analysis

Now that we know what is in the data, let's dive deeper into it.

Energy prices across regions

We have seen the prices vary across regions. But how big are these differences? Let's take a look.

We will start with defining a plot_tariffs function. It will classify prices into buckets, and show the differences on the map.

In [11]:
style_function = lambda x: {'fillColor': '#ffffff', 
                            'color':'#000000', 
                            'fillOpacity': 0.1, 
                            'weight': 0.1}

highlight_function = lambda x: {'fillColor': '#989898', 
                                'color':'#000000', 
                                'fillOpacity': 0.8}

def plot_tariffs(df, variable, variable_description, title):
    
    classifier = mapclassify.NaturalBreaks(y=df[variable], k=5)
    bins = [df[variable].min()] + list(classifier.bins)

    m = folium.Map(location=[46.83, 8.13], zoom_start=8, tiles="cartodbpositron")

    folium.Choropleth(
        geo_data=json.loads(df.to_json()),
        data=df,
        columns=["municipality", variable],
        key_on="feature.properties.municipality",
        fill_color="YlGn",
        fill_opacity=1,
        line_weight=0,
        smooth_factor=0,
        bins=bins,
        reset=True,
        legend_name=variable_description,
    ).add_to(m)

    hover = folium.features.GeoJson(
        df,
        style_function=style_function, 
        control=False,
        highlight_function=highlight_function,
        tooltip=folium.features.GeoJsonTooltip(
            fields=['municipality', variable],
            aliases=['Municipality: ', variable_description + ": "],
            style=("background-color: white; color: #333333; font-family: arial; font-size: 12px; padding: 10px;") 
        )
    )

    folium.LayerControl().add_to(m)
    m.add_child(hover)
    m.keep_in_front(hover)
    
    title_html = '''<h3 align="center" style="font-size:16px"><b>{}</b></h3>'''.format(title)   
    m.get_root().html.add_child(folium.Element(title_html))

    return m

Now let's use it! How do the prices vary for an average household?

In [12]:
category = "H2"
plot_df = join[join.category == category]
plot_tariffs(plot_df, "monthly_bill", "Average monthly bill [CHF]", cat2description[category])
Out[12]:
Make this Notebook Trusted to load map: File -> Trust Notebook