{ "cells": [ { "attachments": {}, "cell_type": "markdown", "id": "cd2f0c7a", "metadata": {}, "source": [ "
\n", " \n", "
\n", " \n", "
Run in Google Colab
\n", "
\n", " \n", " \n", "
\n", " \n", "
Download Notebook
\n", "
\n", " \n", " \n", "
\n", " \n", "
View on GitHub
\n", "
\n", " \n", "
\n", "\n", "# Electricity tariffs in Switzerland\n", "\n", "ElCom, the Federal Electricity Commission, collects data on the electricity tariffs for households and companies. This data is published as [Linked Data](https://en.wikipedia.org/wiki/Linked_data). \n", "\n", "In this tutorial, we will show **how to work with Linked Data.** Mainly, we will see how to work with data on electricity tariffs. \n", "We will look into how to query, process, and visualize it. \n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "9d51691e", "metadata": {}, "source": [ "[1. Setup](#Setup) \n", "      [1.1 SPARQL endpoints](#SPARQL-endpoints) \n", "      [1.2 SPARQL client](#SPARQL-endpoints) \n", "\n", "[2. Data](#Data) \n", "      [2.1 Electricity tariffs](#Electricity-tariffs) \n", "      [2.2 Municipalities](#Municipalities) \n", "\n", "\n", "[3. Analysis](#Analysis) \n", "\n", "\n", "      [3.1 Energy prices across regions](#Energy-prices-across-regions) \n", "      [3.2 Energy prices for small and big enterprises](#Energy-prices-for-small-and-big-enterprises) \n", "      [3.3 Energy prices for small and big households](#Energy-prices-for-small-and-big-households) \n", "      [3.4 Competition between energy providers](#Competition-between-energy-providers) \n", "      [3.5 Grid costs and population density](#Grid-costs-and-population-density) \n", "      [3.6 Free grid usage](#Free-grid-usage) \n", "      [3.7 Free energy](#Free-energy) \n", "      [3.8 Free aidfee](#Free-aidfee) \n", "      [3.9 How many people benefit from fee waivers?](#How-many-people-benefit-from-fee-waivers?) \n", "      [3.10 Which companies benefit from fee waivers?](#Which-companies-benefit-from-fee-waivers?) \n", "\n", "-------------------------------------------------------------------------------------------------------------------" ] }, { "attachments": {}, "cell_type": "markdown", "id": "eb7bef9c", "metadata": {}, "source": [ "## Setup" ] }, { "attachments": {}, "cell_type": "markdown", "id": "cc8bbfa9", "metadata": {}, "source": [ "### SPARQL endpoints\n", "\n", "#### For electricity tariffs\n", "Data on all electricity tariffs are published as Linked Data. They can be accessed with [SPARQL queries](https://www.w3.org/TR/rdf-sparql-query/). \n", "You can send queries using HTTP requests. The API endpoint is **[https://lindas.admin.ch/query/](https://int.lindas.admin.ch/query).** \n", "\n", "#### For geodata\n", "Different municipalities may have different tariffs. To understand their location, we will work with \n", "Swiss geodata. It is published as Linked Data. It can be accessed using the API endpoint under **[https://geo.ld.admin.ch/query](https://geo.ld.admin.ch/query).** " ] }, { "attachments": {}, "cell_type": "markdown", "id": "da9db59c", "metadata": {}, "source": [ "### SPARQL client\n", "\n", "Let's use `SparqlClient` from [graphly](https://github.com/zazuko/graphly) to communicate with both databases.\n", "Graphly will allow us to:\n", "* send SPARQL queries\n", "* automatically add prefixes to all queries\n", "* format responses to `pandas` or `geopandas`" ] }, { "cell_type": "code", "execution_count": null, "id": "d2f8138f", "metadata": {}, "outputs": [], "source": [ "import json\n", "import re\n", "import string\n", "\n", "import folium\n", "import mapclassify\n", "import matplotlib as mpl\n", "import matplotlib.pyplot as plt\n", "import pandas as pd\n", "import plotly.express as px\n", "import plotly.graph_objects as go\n", "from plotly.subplots import make_subplots\n", "\n", "from graphly.api_client import SparqlClient\n", "\n", "%matplotlib inline" ] }, { "cell_type": "code", "execution_count": null, "id": "f80432ff", "metadata": {}, "outputs": [], "source": [ "# Uncomment to install dependencies in Colab environment\n", "#!pip install mapclassify\n", "#!pip install git+https://github.com/zazuko/graphly.git" ] }, { "cell_type": "code", "execution_count": null, "id": "55a2bac1", "metadata": {}, "outputs": [], "source": [ "sparql = SparqlClient(\"https://lindas.admin.ch/query\")\n", "geosparql = SparqlClient(\"https://geo.ld.admin.ch/query\")\n", "\n", "sparql.add_prefixes({\n", " \"schema\": \"\",\n", " \"cube\": \"\",\n", " \"elcom\": \"\",\n", " \"admin\": \"\"\n", "})\n", "\n", "geosparql.add_prefixes({\n", " \"dct\": \"\",\n", " \"geonames\": \"\",\n", " \"schema\": \"\",\n", " \"geosparql\": \"\",\n", "})" ] }, { "attachments": {}, "cell_type": "markdown", "id": "55416e9c", "metadata": {}, "source": [ "SPARQL queries can become very long. To improve the readability, we will work with [prefixes](https://en.wikibooks.org/wiki/SPARQL/Prefixes).\n", " \n", "Using the `add_prefixes` method, we can define persistent prefixes. \n", "Every time you send a query, `graphly` will now automatically add the prefixes for you." ] }, { "attachments": {}, "cell_type": "markdown", "id": "855930a3", "metadata": {}, "source": [ "### Example query\n", "The first query returns the total electricity price for municipalities. It is a good example of how easy it is to join linked data. Here, we return results by postal code even though municipalities have another identifier in the database." ] }, { "cell_type": "code", "execution_count": null, "id": "e38c7f2f", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT DISTINCT ?postalCode ?total ?operatorName \n", "FROM \n", "WHERE {\n", " \n", " ?offer a schema:Offer ;\n", " schema:areaServed ?muniri ;\n", " schema:postalCode ?postalCode .\n", " \n", " cube:observationSet/cube:observation ?obs .\n", " \n", " ?obs a cube:Observation ;\n", " elcom:period \"2023\"^^xsd:gYear ; # data available up to 10 years ago\n", " elcom:municipality ?muniri ;\n", " elcom:category ; # categories are explained below\n", " elcom:operator ?operator ;\n", " elcom:product ; # Some municipalities also have a \"cheapest\" product\n", " elcom:total ?total .\n", " \n", " ?operator schema:name ?operatorName .\n", "\n", " # FILTER( ?postalCode = \"2502\")\n", " \n", "}\n", "\"\"\"\n", "\n", "totalByPostCode = sparql.send_query(query)\n", "totalByPostCode.head()\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "0587e94e", "metadata": {}, "source": [ "Let's now start a more thorough analysis of electricity prices." ] }, { "attachments": {}, "cell_type": "markdown", "id": "9a40ef42", "metadata": {}, "source": [ "## Data\n", "\n", "Our goal is to understand the variability in electricity prices. To do so, we will work with electricity tariffs and municipality data. \n", "\n", "### Electricity tariffs\n", "\n", "Electricity prices are composed of:\n", "\n", "* **Grid fee** \n", "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. \n", "\n", "\n", "* **Energy price** \n", "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. \n", "\n", "\n", "* **Municipality taxes** \n", "Municipal and cantonal taxes and fees. These include, for example, concession fees or local political energy levies. \n", "\n", "\n", "* **Aidfee** \n", "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. \n", "\n", "\n", "These components vary across:\n", "\n", "* time\n", "* products\n", "* consumption categories\n", "* energy providers\n", "* municipalities\n", "\n", "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. \n", "\n", "-----------------------------------------------------------------------------------\n", "\n", "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. \n", "\n", "We can query the tariffs for different municipalities and categories as follows (heads up, the query may take a while to execute!):" ] }, { "cell_type": "code", "execution_count": null, "id": "4ff96374", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ?municipality_id ?category ?energy ?grid ?aidfee (?community_fees + ?aidfee as ?taxes) ?fixcosts ?variablecosts \n", "FROM \n", "WHERE {\n", " cube:observation ?observation.\n", " \n", " ?observation\n", " elcom:category/schema:name ?category;\n", " elcom:municipality ?municipality_id;\n", " elcom:period \"2020\"^^;\n", " elcom:product ;\n", " elcom:fixcosts ?fixcosts;\n", " elcom:total ?variablecosts;\n", " elcom:gridusage ?grid;\n", " elcom:energy ?energy;\n", " elcom:charge ?community_fees;\n", " elcom:aidfee ?aidfee.\n", " \n", "}\n", "ORDER BY ?muncipality ?category ?variablecosts\n", "\"\"\"\n", "\n", "tariffs = sparql.send_query(query)\n", "tariffs = tariffs.groupby([\"municipality_id\", \"category\"]).first().reset_index()\n", "tariffs.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "f0669451", "metadata": {}, "source": [ "Great! We can analyse all price components across municipalities and consumption categories. But what do these consumption categories stand for?\n", "\n", "Let's query all categories and their description." ] }, { "cell_type": "code", "execution_count": null, "id": "9a15d12c", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT DISTINCT ?category ?description\n", "WHERE {\n", " GRAPH {\n", " \n", " ?s ?category_uri.\n", " ?category_uri schema:name ?category .\n", " ?category_uri schema:description ?description .\n", " }\n", "}\n", "ORDER BY ?category\n", "\"\"\"\n", "df = sparql.send_query(query)\n", "df" ] }, { "attachments": {}, "cell_type": "markdown", "id": "2233e0dd", "metadata": {}, "source": [ "So we have 15 different categories. Those knowing German will notice that the categories cover households and companies. The difference is in expected energy consumption.\n", "\n", "The tariffs themselves are not very meaningful. \"How much is my monthly bill?\" is a much more relatable question. Let's try to answer that." ] }, { "cell_type": "code", "execution_count": null, "id": "74a84340", "metadata": {}, "outputs": [], "source": [ "def extract_consumption(description: str) -> int:\n", " \"\"\"\n", " Extract average electricity consumption from a description.\n", " Args:\n", " description: Category description for electricity tariffs\n", " \n", " Returns: \n", " int: Electricity consumption in kWh/year\n", " \n", " \"\"\"\n", " \n", " number_as_string = description.split(\" kWh/Jahr\")[0]\n", " return int(number_as_string.translate(str.maketrans('', '', string.punctuation)))" ] }, { "cell_type": "code", "execution_count": null, "id": "af4fffd5", "metadata": {}, "outputs": [], "source": [ "cat2description = dict(zip(df.category, df.description))\n", "cat2consumption = dict(zip(df.category, [extract_consumption(d) for d in df.description]))\n", "\n", "tariffs[\"consumption\"] = tariffs[\"category\"].map(cat2consumption)\n", "tariffs[\"monthly_bill\"] = ((tariffs.consumption*tariffs.variablecosts/12 + tariffs.fixcosts)/100).round(2)\n", "tariffs.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "a63e79cd", "metadata": {}, "source": [ "We can now use the `tariffs` table to analyze regional differences in energy prices." ] }, { "attachments": {}, "cell_type": "markdown", "id": "99adba35", "metadata": {}, "source": [ "### Municipalities" ] }, { "attachments": {}, "cell_type": "markdown", "id": "7df02c96", "metadata": {}, "source": [ "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?\n", "\n", "This information can be obtained using the SPARQL Endpoint from Swisstopo, Switzerland's national mapping agency.\n", "\n", "We can query the list of municipalities, alongside its boundaries as follows:" ] }, { "cell_type": "code", "execution_count": null, "id": "8b4ff07d", "metadata": {}, "outputs": [], "source": [ "query = \"\"\" \n", "SELECT ?municipality_id ?municipality ?population ?boundary \n", "\n", "WHERE {\n", " ?muni_iri dct:hasVersion ?version ;\n", " geonames:featureCode geonames:A.ADM3 .\n", " \n", " ?version schema:validUntil \"2020-12-31\"^^;\n", " geonames:population ?population;\n", " schema:name ?municipality;\n", " geosparql:hasGeometry/geosparql:asWKT ?boundary.\n", " \n", " BIND(IRI(REPLACE(STR(?muni_iri), \"https://geo.ld.admin.ch/boundaries/\", \"https://ld.admin.ch/\")) AS ?municipality_id)\n", "}\n", "\n", "\"\"\"\n", "communes = geosparql.send_query(query)\n", "communes = communes.set_crs(epsg=4326)\n", "communes.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "31ff168e", "metadata": {}, "source": [ "You may notice that we defined a coordinate reference system. The default projection for this endpoint is `EPSG:4326`, also known as WGS84.\n", "\n", "`SparqlClient` automatically returns a `geopandas` dataframe. By defining the coordinate reference system, we can start using its geoanalytics capacities immediately!\n", "\n", "Let's start by visually inspecting all the communes." ] }, { "cell_type": "code", "execution_count": null, "id": "d98c05bd", "metadata": {}, "outputs": [], "source": [ "communes.plot()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "9e5044f1", "metadata": {}, "source": [ "Great! Now we have a geospatial representation of all Swiss municipalities and we also have energy tariffs across the municipalities.\n", "\n", "Let's join this information in one dataframe. " ] }, { "cell_type": "code", "execution_count": null, "id": "77cc9b63", "metadata": {}, "outputs": [], "source": [ "join = pd.merge(communes[[\"municipality_id\", \"municipality\", \"population\", \"boundary\"]], tariffs, how=\"inner\", on=\"municipality_id\")\n", "join.drop(columns=[\"variablecosts\", \"consumption\"], inplace=True)\n", "join.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "1a14996f", "metadata": {}, "source": [ "## Analysis\n", "\n", "Now that we know what is in the data, let's dive deeper into it. \n", "\n", "### Energy prices across regions\n", "\n", "We have seen the prices vary across regions. But how big are these differences? Let's take a look.\n", "\n", "We will start with defining a `plot_tariffs` function. It will classify prices into buckets, and show the differences on the map." ] }, { "cell_type": "code", "execution_count": null, "id": "f39f252f", "metadata": {}, "outputs": [], "source": [ "style_function = lambda x: {'fillColor': '#ffffff', \n", " 'color':'#000000', \n", " 'fillOpacity': 0.1, \n", " 'weight': 0.1}\n", "\n", "highlight_function = lambda x: {'fillColor': '#989898', \n", " 'color':'#000000', \n", " 'fillOpacity': 0.8}\n", "\n", "def plot_tariffs(df, variable, variable_description, title):\n", " \n", " classifier = mapclassify.NaturalBreaks(y=df[variable], k=5)\n", " bins = [df[variable].min()] + list(classifier.bins)\n", "\n", " m = folium.Map(location=[46.83, 8.13], zoom_start=8, tiles=\"cartodbpositron\")\n", "\n", " folium.Choropleth(\n", " geo_data=json.loads(df.to_json()),\n", " data=df,\n", " columns=[\"municipality\", variable],\n", " key_on=\"feature.properties.municipality\",\n", " fill_color=\"YlGn\",\n", " fill_opacity=1,\n", " line_weight=0,\n", " smooth_factor=0,\n", " bins=bins,\n", " reset=True,\n", " legend_name=variable_description,\n", " ).add_to(m)\n", "\n", " hover = folium.features.GeoJson(\n", " df,\n", " style_function=style_function, \n", " control=False,\n", " highlight_function=highlight_function,\n", " tooltip=folium.features.GeoJsonTooltip(\n", " fields=['municipality', variable],\n", " aliases=['Municipality: ', variable_description + \": \"],\n", " style=(\"background-color: white; color: #333333; font-family: arial; font-size: 12px; padding: 10px;\") \n", " )\n", " )\n", "\n", " folium.LayerControl().add_to(m)\n", " m.add_child(hover)\n", " m.keep_in_front(hover)\n", " \n", " title_html = '''

{}

'''.format(title) \n", " m.get_root().html.add_child(folium.Element(title_html))\n", "\n", " return m" ] }, { "attachments": {}, "cell_type": "markdown", "id": "a9e9318f", "metadata": {}, "source": [ "Now let's use it! How do the prices vary for an average household?" ] }, { "cell_type": "code", "execution_count": null, "id": "bd837b36", "metadata": {}, "outputs": [], "source": [ "category = \"H2\"\n", "plot_df = join[join.category == category]\n", "plot_tariffs(plot_df, \"monthly_bill\", \"Average monthly bill [CHF]\", cat2description[category])" ] }, { "attachments": {}, "cell_type": "markdown", "id": "777d3631", "metadata": {}, "source": [ "What about a small company?" ] }, { "cell_type": "code", "execution_count": null, "id": "6af9de58", "metadata": {}, "outputs": [], "source": [ "category = \"C2\"\n", "plot_df = join[join.category == \"C2\"]\n", "plot_tariffs(plot_df, \"monthly_bill\", \"Average monthly bill [CHF]\", cat2description[category])" ] }, { "attachments": {}, "cell_type": "markdown", "id": "df7ff934", "metadata": {}, "source": [ "### Energy prices for small and big enterprises " ] }, { "attachments": {}, "cell_type": "markdown", "id": "3fdeaba4", "metadata": {}, "source": [ "The energy prices can vary a lot! The location may become an important factor when starting a business.\n", "\n", "What would be the best place to locate a small or a big company (based on expected energy consumption)?\n" ] }, { "cell_type": "code", "execution_count": null, "id": "2459e2a4", "metadata": {}, "outputs": [], "source": [ "cols = 2\n", "\n", "titles = {\"C1\": \"8 000 kWh/year\\nSmall company\", \"C7\": \"1 500 000 kWh/year\\nBig company with own transformator\"}\n", "categories = list(titles.keys())\n", "fig, axs = plt.subplots(1, cols, figsize=(20,8))\n", "\n", "for col in range(cols):\n", " cat = categories[col]\n", " plot_df = join[join.category == cat]\n", " plot_df.plot(ax=axs[col], column=\"monthly_bill\", cmap=\"GnBu\", scheme=\"Natural_Breaks\", k=5, legend=True, legend_kwds={\"fmt\": \"{:.0f}\"})\n", " axs[col].set_axis_off()\n", " axs[col].set_title(titles[cat])\n", " \n", "fig = fig.suptitle(\"Average monthly bill for enterprises\", fontsize=24)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "129e5225", "metadata": {}, "source": [ "### Energy prices for small and big households\n", "\n", "What about the households? Are the regional differences the same for small and big households?" ] }, { "cell_type": "code", "execution_count": null, "id": "9d1e1115", "metadata": {}, "outputs": [], "source": [ "cols = 2\n", "\n", "titles = {\"H1\": \"1 600 kWh/year\\n2-room appartment\", \"H5\": \"7 500 kW/year\\n5-room house\"}\n", "categories = list(titles.keys())\n", "fig, axs = plt.subplots(1, cols, figsize=(20,8))\n", "\n", "for col in range(cols):\n", " cat = categories[col]\n", " plot_df = join[join.category == cat]\n", " plot_df.plot(ax=axs[col], column=\"monthly_bill\", cmap=\"YlGn\", scheme=\"Natural_Breaks\", k=5, legend=True, legend_kwds={\"fmt\": \"{:.0f}\"})\n", " axs[col].set_axis_off()\n", " axs[col].set_title(titles[cat])\n", " \n", "fig = fig.suptitle(\"Average monthly bill for households\", fontsize=24)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "1f45e9cb", "metadata": {}, "source": [ "### Price distribution\n", "\n", "These plots enable us to easily find the cheapest regions. But how many people actually pay the cheapest electricity price?\n", "\n", "Let's now take a look at the number of consumers per price. The histograms will show us how many people are eligible for certain tariffs. They **do not show how many people actually pay those prices**.\n", "\n", "As people live in different households, their tariff varies with apartment size. \n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "e5c243ca", "metadata": {}, "source": [ "First, let's take a look at the price distribution of the H1 tariff (1600 kWh/year, corresponding to a 2-room apartment)" ] }, { "cell_type": "code", "execution_count": null, "id": "4a82ed25", "metadata": {}, "outputs": [], "source": [ "category = \"H1\"\n", "plot_df = join[[\"monthly_bill\", \"population\"]][join.category == category]\n", "plot_df[\"monthly_bill\"] = plot_df[\"monthly_bill\"].round().astype(int)\n", "plot_df = plot_df.groupby(\"monthly_bill\").sum().reset_index()\n", "plot_df.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "b72a438d", "metadata": {}, "outputs": [], "source": [ "fig = px.bar(plot_df, x=\"monthly_bill\", y=\"population\")\n", "\n", "fig.update_layout(\n", " title='Electricity prices for a 2-room apartment (1600 kWh/year)', \n", " title_x=0.5,\n", " yaxis_title=\"Eligible population\",\n", " xaxis_title=\"Average monthly bill\"\n", ")\n", "fig.show()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "523b0cc8", "metadata": {}, "source": [ "What about bigger households? \n", "\n", "Let's take a look at the price distribution of H5 tariff (7500 kW/year, corresponding to 5-room house)" ] }, { "cell_type": "code", "execution_count": null, "id": "8511677c", "metadata": {}, "outputs": [], "source": [ "category = \"H5\"\n", "plot_df = join[[\"monthly_bill\", \"population\"]][join.category == category]\n", "plot_df[\"monthly_bill\"] = plot_df[\"monthly_bill\"].round().astype(int)\n", "plot_df = plot_df.groupby(\"monthly_bill\").sum().reset_index()\n", "plot_df.head()\n", "\n", "fig = px.bar(plot_df, x=\"monthly_bill\", y=\"population\")\n", "fig.update_layout(\n", " title='Electricity prices for 5-room household (7500 kWh/year)', \n", " title_x=0.5,\n", " yaxis_title=\"Eligible population\",\n", " xaxis_title=\"Average monthly bill\"\n", ")\n", "fig.show()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "6bb82ac7", "metadata": {}, "source": [ "### Competition between energy providers\n", "\n", "At one place, multiple energy providers can operate. The customers are free to use the provider of their choice. This, in theory, should introduce competition. In the long run, the electricity prices in these regions should also be lower. \n", "\n", "Which municipalities have access to more than one electricity provider?\n", "\n", "First, let's find all municipalities with more than one provider. We can do it using the SPARQL aggregate query." ] }, { "cell_type": "code", "execution_count": null, "id": "0f18114b", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "PREFIX cube: \n", "PREFIX elcom: \n", "PREFIX schema: \n", "\n", "SELECT ?municipality_id (MAX(?providers) AS ?providers)\n", "FROM \n", "WHERE {\n", " SELECT ?municipality_id ?category (COUNT(*) AS ?providers)\n", "\n", " WHERE {\n", " cube:observation ?observation.\n", "\n", " ?observation\n", " elcom:category/schema:name ?category;\n", " elcom:municipality ?municipality_id;\n", " elcom:period \"2020\"^^;\n", " elcom:product ;\n", " elcom:energy ?energy.\n", " }\n", " GROUP BY ?municipality_id ?category\n", " HAVING (COUNT(*) > 1)\n", "}\n", "GROUP BY ?municipality_id\n", "\"\"\"\n", "df = sparql.send_query(query)\n", "df.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "c45d324a", "metadata": {}, "source": [ "All municipalities not on the list have one provider only. Let's add them doing a join on `communes`.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "78261cc3", "metadata": {}, "outputs": [], "source": [ "plot_df = pd.merge(communes[[\"municipality_id\", \"municipality\", \"population\", \"boundary\"]], df, how=\"outer\", on=\"municipality_id\").fillna(1)\n", "plot_df.providers = plot_df.providers.astype(int)\n", "plot_df.head()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "3857d1e4", "metadata": {}, "source": [ "Now that we know how many providers operate at each location, let's plot it. First, we will define a `plot_categories` function. It will allow us to plot categorical variables on a map." ] }, { "cell_type": "code", "execution_count": null, "id": "93179dd2", "metadata": {}, "outputs": [], "source": [ "def plot_categories(df, variable, label, title, colormap): \n", "\n", " m = folium.Map(location=[46.83, 8.13], zoom_start=8, tiles=\"cartodbpositron\")\n", "\n", " \n", " colormap = {str(k):v for k,v in colormap.items()}\n", " df[variable] = df[variable].astype(str)\n", " \n", " style_function = lambda x: {'color': \"#a9a9a9\",\n", " 'fillOpacity': 1,\n", " 'weight': 0.05,\n", " 'fillColor': colormap[x['properties'][variable]]}\n", " \n", "\n", " categories = df[variable].unique()\n", " for cat in categories:\n", " \n", " data = json.loads(df[[\"municipality\", \"boundary\", variable]][df[variable] == cat].to_json())\n", " folium.features.GeoJson(\n", " data,\n", " smooth_factor=0,\n", " style_function=style_function, \n", " name=cat,\n", " highlight_function=highlight_function, \n", " tooltip=folium.features.GeoJsonTooltip(\n", " fields=['municipality', variable],\n", " aliases=['Municipality: ', label + \": \"],\n", " style=(\"background-color: white; color: #333333; font-family: arial; font-size: 12px; padding: 10px;\") \n", " )\n", " ).add_to(m)\n", " \n", " folium.LayerControl().add_to(m)\n", " title_html = '''

{}

'''.format(title) \n", " m.get_root().html.add_child(folium.Element(title_html))\n", "\n", " return m" ] }, { "attachments": {}, "cell_type": "markdown", "id": "7c4546a3", "metadata": {}, "source": [ "Let's plot the number of providers per municipality." ] }, { "cell_type": "code", "execution_count": null, "id": "10d68a57", "metadata": {}, "outputs": [], "source": [ "categories = plot_df.providers.unique()\n", "colormap = {cat: mpl.colors.rgb2hex(mpl.cm.plasma((i)/(len(categories)-1))) for i, cat in enumerate(categories)}\n", "\n", "plot_categories(plot_df, \"providers\", \"Electricity providers\", \"Electricity providers\", colormap)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "6b5db935", "metadata": {}, "source": [ "### Grid costs and population density \n", "\n", "Grid costs are a core price component. They are used to finance the maintenance and expansion of the electricity grid, for example overhead lines, pylons and transformers. \n", "\n", "Densely populated regions can share the infrastructure costs among more people. The space is smaller, and the grid is denser. All else equal, this should result in cheaper maintenance costs in such areas. \n", "\n", "Is this a valid hypothesis? Let's find out!\n", "\n", "--------------------------------------------------\n", "\n", "First, let's calculate population density. Remember to use a coordinate reference system that is meant for representing areas!" ] }, { "cell_type": "code", "execution_count": null, "id": "069d7b7d", "metadata": {}, "outputs": [], "source": [ "join[\"hectares\"] = join.to_crs(epsg=3035).area/10000 # In hectares\n", "join[\"population_density\"] = join[\"population\"]/join[\"hectares\"]\n", "join.head(3)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "ab65931d", "metadata": {}, "source": [ "Let's now take a look at the relationship between grid costs, and population density" ] }, { "cell_type": "code", "execution_count": null, "id": "d01df195", "metadata": {}, "outputs": [], "source": [ "plot_df = join[join.category == \"H1\"]\n", "fig = px.scatter(plot_df, y=\"grid\", x=\"population_density\", hover_data=[\"municipality\"],\n", " labels={\"population_density\": \"Inbahitants per ha\", \"grid\": \"Grid costs per kWh\"})\n", "\n", "fig.show()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "4c5fe816", "metadata": {}, "source": [ "It looks like there is no relation between grid costs and population density. However, we can observe one other interesting thing:\n", "\n", "Some communes pay zero grid fees!" ] }, { "attachments": {}, "cell_type": "markdown", "id": "2cc79c17", "metadata": {}, "source": [ "### Free grid usage" ] }, { "attachments": {}, "cell_type": "markdown", "id": "4cfb7bfc", "metadata": {}, "source": [ "Let's find out who pays zero grid fees. First, we will assign a binary variable defining whether a commune pays grid fees, aidfee and energy." ] }, { "cell_type": "code", "execution_count": null, "id": "2adbe10d", "metadata": {}, "outputs": [], "source": [ "join = join.assign(pays_grid=lambda x: x.grid!=0, \n", " pays_aidfee=lambda x: x.aidfee!=0,\n", " pays_energy=lambda x: x.energy!=0)\n", "\n", "join.head(3)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "8c0deaf1", "metadata": {}, "source": [ "Now, let's use the `plot_categories` function to find out who benefits from free grid usage." ] }, { "cell_type": "code", "execution_count": null, "id": "9eaf9d60", "metadata": {}, "outputs": [], "source": [ "plot_df = join[join.category==\"C1\"].reset_index(drop=True)\n", "colormap = {True: \"#636EFA\", False: \"#EF553B\"}\n", "\n", "plot_categories(plot_df, \"pays_grid\", \"Paid grid usage\", \"Grid fees\", colormap)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "5e66e468", "metadata": {}, "source": [ "Which communes are those? How many people live there?" ] }, { "cell_type": "code", "execution_count": null, "id": "16289c09", "metadata": {}, "outputs": [], "source": [ "temp = join[[\"municipality_id\", \"municipality\", \"pays_grid\", \"population\"]].groupby([\"municipality_id\", \"municipality\", \"population\"]).all().reset_index()\n", "pop_free_grid = temp[(~temp.pays_grid)][\"population\"].sum()\n", "\n", "temp[(~temp.pays_grid)].reset_index(drop=True)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "b3707546", "metadata": {}, "source": [ "### Free energy\n", "\n", "We have seen that some places do not pay for grid usage. Another twist is that the energy world also knows negative or zero prices. Are there any places that have zero energy tariffs?" ] }, { "cell_type": "code", "execution_count": null, "id": "1ed24784", "metadata": {}, "outputs": [], "source": [ "join[\"has_free_energy\"] = ~join.pays_energy\n", "temp = join[[\"municipality_id\", \"municipality\", \"has_free_energy\", \"population\"]].groupby([\"municipality_id\", \"municipality\", \"population\"]).all().reset_index()\n", "\n", "temp[temp.has_free_energy]" ] }, { "attachments": {}, "cell_type": "markdown", "id": "98cb6b34", "metadata": {}, "source": [ "Not really. There is no municipality with free electricity!" ] }, { "attachments": {}, "cell_type": "markdown", "id": "fa9a86fa", "metadata": {}, "source": [ "### Free aidfee" ] }, { "attachments": {}, "cell_type": "markdown", "id": "ed50187e", "metadata": {}, "source": [ "What about aidfee? \n", "\n", "The government states that everyone pays aidfee. Do we find the same insights in data?" ] }, { "cell_type": "code", "execution_count": null, "id": "7f25f5c2", "metadata": {}, "outputs": [], "source": [ "plot_df = join[join.category==\"C1\"].reset_index(drop=True)\n", "colormap = {True: \"#00CC96\", False: \"#AB63FA\"}\n", "\n", "plot_categories(plot_df, \"pays_aidfee\", \"Pays aidfee\", \"Aidfees\", colormap)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "53f20195", "metadata": {}, "source": [ "Which communes do not pay aidfee? How many people live there?" ] }, { "cell_type": "code", "execution_count": null, "id": "e448069a", "metadata": {}, "outputs": [], "source": [ "temp = join[[\"municipality_id\", \"municipality\", \"pays_aidfee\", \"population\"]].groupby([\"municipality_id\", \"municipality\", \"population\"]).all().reset_index()\n", "pop_free_aidfee = temp[(~temp.pays_aidfee)][\"population\"].sum()\n", "\n", "temp[(~temp.pays_aidfee)].reset_index(drop=True)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "226e9bf8", "metadata": {}, "source": [ "**DISCLAIMER**: ElKom states:\n", "\n", "`The amount of the aidfee is set annually by the Federal Council. The levy is the same everywhere.`\n", "\n", "However, that is not what we find in the data. Possible explanation that we have not verified?\n", "\n", "Some communes managed to get a waiver for aidfee. The aidfee is hence the same for everyone. Except that not everyone pays it. Or, maybe more probable, there is an issue with the data as provided by ELKom." ] }, { "attachments": {}, "cell_type": "markdown", "id": "dafc6604", "metadata": {}, "source": [ "### How many people benefit from fee waivers?" ] }, { "attachments": {}, "cell_type": "markdown", "id": "ce9b64d6", "metadata": {}, "source": [ "Few communes benefit from fee waivers for aidfee, and grid usage. How many people benefit from these waivers?\n", "\n", "Let's find out!" ] }, { "cell_type": "code", "execution_count": null, "id": "fb9de8da", "metadata": {}, "outputs": [], "source": [ "pop_total = communes.population.sum()\n", "\n", "fig = make_subplots(rows=1, cols=2, subplot_titles=[\"grid usage\", \"Aidfee\"], specs=[[{\"type\": \"pie\"}, {\"type\": \"pie\"}]])\n", "\n", "fig.add_trace(go.Pie(\n", " values=[pop_free_grid,pop_total-pop_free_grid],\n", " labels=[\"Not paying\", \"Paying\"],\n", " name=\"Grid Usage\"), \n", " row=1, col=1)\n", "\n", "fig.add_trace(go.Pie(\n", " values=[pop_free_aidfee,pop_total-pop_free_aidfee],\n", " labels=[\"Not paying\", \"Paying\"],\n", " name=\"Aidfee\"),\n", " row=1, col=2)\n", "\n", "fig.update_annotations(yshift=-280)\n", "fig.update_layout(height=400, title={\"text\": \"Population benefiting from free tariffs\", \"x\": 0.5})\n", "fig.show()\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "c1e02d9e", "metadata": {}, "source": [ "### Which companies benefit from fee waivers? " ] }, { "attachments": {}, "cell_type": "markdown", "id": "930cf167", "metadata": {}, "source": [ "We have seen which population benefits from fee waivers. What about companies? \n", "\n", "For a manufacturing company in an energy-intensive sector, it may make sense to optimize its location. Such companies may explicitly look for places with cheaper electricity. Do places with free grid usage attract \n", "energy-intensive companies? Are there some companies that benefit from this waiver?\n", "\n", "---------------------------------------\n", "\n", "To find out who benefits from grid fee waivers, let's take a look at ZEFIX. ZEFIX is the Swiss commerce register. ZEFIX will provide us with company name, type, description, and address and ZEFIX is also available as Linked Data.\n", "\n", "Let's find all companies registered in municipalities with free grid usage. To start, we will list all those municipalities:" ] }, { "cell_type": "code", "execution_count": null, "id": "2e797fbd", "metadata": {}, "outputs": [], "source": [ "municipalities_free_energy = join[\"municipality_id\"][~(join.pays_energy)].unique()\n", "municipalities_free_energy" ] }, { "attachments": {}, "cell_type": "markdown", "id": "2bfaee1e", "metadata": {}, "source": [ "Now let's find all companies registered in one of those villages (and hence are entitled to free grid usage)." ] }, { "cell_type": "code", "execution_count": null, "id": "61710ed8", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ?company ?type ?company_iri\n", "WHERE {\n", " ?company_iri a admin:ZefixOrganisation.\n", " ?company_iri schema:legalName ?company.\n", " ?company_iri admin:municipality .\n", " ?company_iri schema:additionalType/schema:name ?type .\n", " \n", " FILTER(LANG(?type) = \"de\")\n", "}\n", "ORDER BY ?type ?company\n", "\"\"\"\n", "df = sparql.send_query(query)\n", "df.head(7)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "1eb6c9e3", "metadata": {}, "source": [ "This gives us a list of ALL companies in the region. However, only the big energy consumers will see an impact of fee waivers on their accounts. While we don't know which enterprise pays how much, we can hypothesize the following:\n", "\n", "Only the big, energy-intensive businesses will have high enough energy bills to justify moving their production lines. Such companies would typically be joint-stock entities.\n", "\n", "We can hence filter our company list to only include joint-stock companies only." ] }, { "cell_type": "code", "execution_count": null, "id": "0b34811d", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ?municipality ?company ?company_iri \n", "WHERE {{\n", " ?company_iri a admin:ZefixOrganisation.\n", " ?company_iri schema:legalName ?company.\n", " ?company_iri admin:municipality ?municipality_iri.\n", " ?company_iri schema:additionalType .\n", " \n", " ?municipality_iri schema:name ?municipality.\n", " \n", " FILTER(?municipality_iri IN({}))\n", " \n", "}}\n", "ORDER BY ?municipality ?company\n", "\"\"\".format(\"<\" + \">,<\".join(municipalities_free_energy) + \">\")\n", "df = sparql.send_query(query)\n", "df" ] }, { "attachments": {}, "cell_type": "markdown", "id": "fc2f2656", "metadata": {}, "source": [ "Et volià! The list of all potential beneficiaries of free grid usage." ] } ], "metadata": { "kernelspec": { "display_name": "zazuko", "language": "python", "name": "zazuko" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.6" }, "title": "Electricity Tariffs" }, "nbformat": 4, "nbformat_minor": 5 }