Electricity Tariffs

How much will you pay for electricity next year?

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

In this data story, we will show you how to calculate your electricity bills for next year using Linked Data. Mainly, you will see how to extract, process, and interpret with data on electricity tariffs.

Enjoy the ride!

In [1]:
import json
import re
import string

import branca.colormap as cm
import folium
import mapclassify
import matplotlib as mpl
import matplotlib.pyplot as plt
import matplotlib.colors as mcolors
import pandas as pd

from graphly.api_client import SparqlClient

%matplotlib inline

import warnings
warnings.filterwarnings('ignore')
In [2]:
# Uncomment to install dependencies in Colab environment
#!pip install mapclassify
#!pip install git+https://github.com/zazuko/graphly.git

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.

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#>",
})

What do you want to know?

Define your parameters here: consumer category, current year, and upcoming year.

In [4]:
category = "H2"
year_old = 2022
year_new = 2023

Wonder what categories you can choose from?

Check it out here!

In [ ]:
 

Retriving the data

Here is where the magic happens. Using SPARQL endpoint, you will be able to get data on electricity prices in all swiss communes in single SPARQL query!

In [5]:
query = """
SELECT ?municipality_id ?increase ?fixcosts_old ?fixcosts_new ?price_old ?price_new ?elcom_details
FROM <https://lindas.admin.ch/elcom/electricityprice>
FROM <https://lindas.admin.ch/territorial>
WHERE {{
    <https://energy.ld.admin.ch/elcom/electricityprice/observation/> cube:observation ?observation_old.
    <https://energy.ld.admin.ch/elcom/electricityprice/observation/> cube:observation ?observation_new.
  
  VALUES ?category {{ <https://energy.ld.admin.ch/elcom/electricityprice/category/{0}>}}
    ?observation_new
      elcom:category ?category;
      elcom:municipality ?municipality_id;
      elcom:period "{1}"^^<http://www.w3.org/2001/XMLSchema#gYear>;
      elcom:product <https://energy.ld.admin.ch/elcom/electricityprice/product/standard>;
      elcom:fixcosts ?fixcosts_new;
      elcom:total ?variablecosts_new.
  
    ?observation_old
      elcom:category ?category;
      elcom:municipality ?municipality_id;
      elcom:period "{2}"^^<http://www.w3.org/2001/XMLSchema#gYear>;
      elcom:product <https://energy.ld.admin.ch/elcom/electricityprice/product/standard>;
      elcom:fixcosts ?fixcosts_old;
      elcom:total ?variablecosts_old.
  
  ?municipality_id schema:name ?municipality ;
                   schema:identifier ?mun_num .

  BIND(IRI(CONCAT("https://www.strompreis.elcom.admin.ch/municipality/", STR(?mun_num), "?period={2}")) AS ?elcom_details )
  
  BIND((?variablecosts_new/?variablecosts_old - 1)*100 AS ?increase)
  BIND(?variablecosts_old/100 AS ?price_old)
  BIND(?variablecosts_new/100 AS ?price_new)
}}
""".format(category, year_new, year_old)

tariffs = sparql.send_query(query)
tariffs = tariffs.groupby(["municipality_id"]).min().reset_index().sort_values(by="increase", ascending=False)
tariffs.head()
Out[5]:
municipality_id increase fixcosts_old fixcosts_new price_old price_new elcom_details
918 https://ld.admin.ch/municipality/4073 284.39150 0.00 0.00 0.1512 0.5812 https://www.strompreis.elcom.admin.ch/municipa...
736 https://ld.admin.ch/municipality/3423 195.02513 3.60 3.60 0.1990 0.5871 https://www.strompreis.elcom.admin.ch/municipa...
615 https://ld.admin.ch/municipality/301 188.07631 3.84 4.80 0.1887 0.5436 https://www.strompreis.elcom.admin.ch/municipa...
1130 https://ld.admin.ch/municipality/4646 175.15560 3.12 3.84 0.1928 0.5305 https://www.strompreis.elcom.admin.ch/municipa...
1160 https://ld.admin.ch/municipality/4831 170.83983 3.36 3.36 0.1917 0.5192 https://www.strompreis.elcom.admin.ch/municipa...

Great! We can analyse all price components across municipalities and consumption categories. Do you want to play around? Check out our query here

But what our consumption category stands for?

In [6]:
query = """
SELECT *
WHERE {{
  GRAPH <https://lindas.admin.ch/elcom/electricityprice> {{
    
    <https://energy.ld.admin.ch/elcom/electricityprice/category/{0}> schema:description ?description .
  }}
}}
""".format(category)
cat_description = sparql.send_query(query)["description"].iloc[0]
cat_description
Out[6]:
"2'500 kWh/Jahr: 4-Zimmerwohnung mit Elektroherd"
In [7]:
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)))

consumption = extract_consumption(cat_description)
tariffs["monthly_bill_old"] = ((consumption*tariffs.price_old/12 + tariffs.fixcosts_old)).round(2)
tariffs["monthly_bill_new"] = ((consumption*tariffs.price_new/12 + tariffs.fixcosts_new)).round(2)
tariffs["price_old"] = tariffs["price_old"].round(2)
tariffs["price_new"] = tariffs["price_new"].round(2)
tariffs.increase = tariffs.increase.round(0)

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?

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 "{}-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)
}}

""".format(year_old)
communes = geosparql.send_query(query)
communes = communes.set_crs(epsg=4326)
join = pd.merge(communes[["municipality_id", "municipality", "population", "boundary"]], tariffs, how="left", on="municipality_id")

How much are you paying now?

Let's take a look at average monthly bill for a household. How much was it in 2022? How much will in be next year?

In [9]:
cols=["monthly_bill_old", "monthly_bill_new"]
years = [year_old, year_new]
fig, axs = plt.subplots(1, len(cols), figsize=(20,8))

for i, (year, col) in enumerate(zip(years, cols)):

    if i == 0:
        has_legend = False
    else:
        has_legend = True
    join.plot(ax=axs[i], column=col, cmap="GnBu", scheme="User_Defined", classification_kwds=dict(bins=[40,50,60,70,80,100]), legend=has_legend, legend_kwds={"fmt": "{:.0f}"})
    axs[i].set_axis_off()
    axs[i].set_title(year)
        
plt.text(x=0.5, y=0.90, s="Average monthly bill for households", fontsize=26, ha="center", transform=fig.transFigure)
plt.text(x=0.5, y=0.85, s= cat_description, fontsize=12, ha="center", transform=fig.transFigure)
plt.show()

How much will you pay next year?

On the first glimpsee, you will spot significant increases. How big are they exactly?

And what does that mean for your households in your commune?

In [10]:
colors = ['#006d2c', '#74c476', '#ffffb2','#fed976','#feb24c','#fd8d3c','#f03b20','#bd0026']
BINS=[-79, -25, 0, 25, 50, 75, 100, 150]
colormap = cm.StepColormap(colors,
                       vmin=-79, vmax=275, index=BINS,
                       caption='Price increase in %')

style_function = lambda x: {"weight":0.1, 
                            'color':'#000000',
                            'fillColor': colormap(x['properties']['increase']) if x['properties']['increase'] else '#fafafa', 
                            'fillOpacity': 1}
                        

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

def plot_tariffs(df, variables, variables_description, title):
    
    classifier = mapclassify.UserDefined(y=df[variables[0]], bins = BINS)
    bins = [df[variables[0]].min()] + list(classifier.bins)

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

    folium.Choropleth(
        geo_data=json.loads(df.to_json()),
    ).add_to(m)

    hover_has_data = folium.features.GeoJson(
        df[~df.increase.isna()],
        style_function=style_function, 
        control=False,
        highlight_function=highlight_function,
        tooltip=folium.features.GeoJsonTooltip(
            fields=['municipality', *variables],
            aliases=['Municipality: '] + [v + ": " for v in variables_description],
            style=("background-color: white; color: #333333; font-family: arial; font-size: 12px; padding: 10px;") 
        )
    )

    hover_missing_data = folium.features.GeoJson(
        df[df.increase.isna()],
        style_function=style_function, 
        control=False,
        highlight_function=highlight_function,
        tooltip=folium.features.GeoJsonTooltip(
            fields=['municipality'],
            aliases=['Municipality: '],
            style=("background-color: white; color: #333333; font-family: arial; font-size: 12px; padding: 10px;") 
        ),
    )
    colormap.add_to(m)
    m.add_child(hover_has_data)
    m.add_child(hover_missing_data)
    m.keep_in_front(hover_has_data)
    
    title_html = '''<h3 align="center" style="font-size:22px"><b>How much will your electricity bill be next year?</b><br></h3><p align="center">{}</p>'''.format(title)   
    m.get_root().html.add_child(folium.Element(title_html))

    return m
In [11]:
variables=["increase", "price_old", "price_new", "monthly_bill_old", "monthly_bill_new", "elcom_details"]
description = ["Price increase [%]", "Price in {} [CHF/kWh]".format(year_old), "Price in {} [CHF/kWh]".format(year_new), "Avg monthly bill in {} [CHF]".format(year_old), "Avg monthly bill in {} [CHF]".format(year_new), "Details"]
plot_tariffs(join, variables, description, cat_description)
Out[11]:
Make this Notebook Trusted to load map: File -> Trust Notebook