02_python/1863@stadt-winterthur.ipynb

{"cells": [{"cell_type": "markdown", "id": "db7f08c1", "metadata": {}, "source": ["## Open Government Data, provided by **Canton Zurich**\n", "*Autogenerated Python starter code for data set with identifier* **1863@stadt-winterthur**"]}, {"cell_type": "markdown", "id": "164c4327", "metadata": {}, "source": ["## Dataset\n", "# **Bruttolastgang elektrische Energie der Stadt Winterthur**"]}, {"cell_type": "markdown", "id": "46d13c8b-4ac3-4e2f-940f-0c14ec5b7c0d", "metadata": {}, "source": ["## Description\n", "\n", "Der Bruttolastgang entspricht der im Netzgebiet von Stadtwerk Winterthur an Endverbraucher abgegebenen elektrischen Energie in einer Aufl\u00f6sung von 15 Minuten. Die Berechnung erfolgt t\u00e4glich gem\u00e4ss dem Metering Code Schweiz mit den verf\u00fcgbaren Daten und wird zwischen dem 5. und 10. Arbeitstag f\u00fcr den Vormonat plausibilisiert. Aufgrund der grossen Anzahl an Datens\u00e4tzen, die der Berechnung der Bruttolastgangsumme zugrunde liegen, k\u00f6nnen die Werte im Verlauf der Zeit, bis max. 6 Monate zur\u00fcck, noch nachtr\u00e4glich angepasst werden. Dargestellt sind die Stromverbrauchsdaten seit 2013. \nDie Daten werden \u00fcber das ganze Jahr der Winterzeit in der Zeitzone (CET) als Datum und Uhrzeit ausgewiesen. Daher ist 'T+01:00' am Ende der Datum- und Uhrzeit Angabe w\u00e4hrend dem ganzen Jahr konstant und zeigt die Zeitverschiebung zu UTC in der Winterzeit an. F\u00fcr die Daten in der Sommerzeit muss man ber\u00fccksichtigen, dass man der Zeit noch 1 Stunde dazu addieren muss. Damit entstehen bei der Zeitumstellung keine doppelten oder fehlenden Eintr\u00e4ge."]}, {"cell_type": "markdown", "id": "cad813bb-c986-4bb4-b52b-f78bb608086f", "metadata": {}, "source": ["## Data set links\n", "\n", "[Direct data shop link for dataset](https://www.zh.ch/de/politik-staat/statistik-daten/datenkatalog.html#/datasets/1863@stadt-winterthur)"]}, {"cell_type": "markdown", "id": "9d4813e9", "metadata": {}, "source": ["## Metadata\n", "- **Issued** `2022-11-25T09:02:55`\n- **Modified** `2025-04-13T08:19:42`\n- **Startdate** `2013-01-01`\n- **Enddate** `2025-04-12`\n- **Theme** `['http://publications.europa.eu/resource/authority/data-theme/ENER']`\n- **Keyword** `['gemeinden', 'strom', 'stromverbrauch', 'ogd']`\n- **Publisher** `['Stadt Winterthur']`\n- **Landingpage** `None`\n"]}, {"cell_type": "markdown", "id": "8a857d65", "metadata": {"jp-MarkdownHeadingCollapsed": true, "tags": []}, "source": ["## Imports and helper functions"]}, {"cell_type": "code", "execution_count": null, "id": "93b39602-1c1e-46d2-ae70-1716b1481e9b", "metadata": {"tags": []}, "outputs": [], "source": ["%matplotlib inline\n", "import matplotlib.pyplot as plt\n", "plt.style.use('ggplot')\n", "\n", "params = {\n", "    'text.color': (0.25, 0.25, 0.25),\n", "    'figure.figsize': [18, 6],\n", "   }\n", "\n", "plt.rcParams.update(params)\n", "\n", "import pandas as pd "]}, {"cell_type": "code", "execution_count": null, "id": "aa6611d7-e1c0-40a7-b0ff-601a8ef8439b", "metadata": {"tags": []}, "outputs": [], "source": ["# helper function for reading datasets with proper separator\n", "def get_dataset(url):\n", "    if url[-3:] != \"csv\":\n", "        print(\"The data set URL has no proper 'csv' extension. Reading the dataset might not have worked as expected.\\nPlease check the dataset link and adjust pandas' read_csv() parameters accordingly.\")\n", "    data = pd.read_csv(url, sep=\",\", on_bad_lines='warn', encoding_errors='ignore', low_memory=False)\n", "    # if dataframe only has one column or less the data is not comma separated, use \";\" instead\n", "    if data.shape[1] <= 1:\n", "        data = pd.read_csv(url, sep=';', on_bad_lines='warn', encoding_errors='ignore', low_memory=False)\n", "        if data.shape[1] <= 1:\n", "            print(\"The data wasn't imported properly. Very likely the correct separator couldn't be found.\\nPlease check the dataset manually and adjust the code.\")\n", "    return data"]}, {"cell_type": "markdown", "id": "02ce518f", "metadata": {}, "source": ["## Load data\n", "\n", "- The dataset has **`4` distribution(s)** in CSV format.\n", "- All available CSV distributions are listed below and can be read into a pandas dataframe."]}, {"cell_type": "code", "execution_count": null, "id": "0", "metadata": {"tags": []}, "outputs": [], "source": "# Distribution 0\n# Ktzhdistid               : 3561\n# Title                    : Energieverbrauch als Bruttolastgang 2013-2015\n# Description              : Der Bruttolastgang wird pro Viertelstunde in Kilowattstunden zusammengefasst.\n# Issued                   : 2022-11-25T09:02:55\n# Modified                 : 2022-11-25T09:02:55\n\ndf = get_dataset('https://www.web.statistik.zh.ch/ogd/daten/ressourcen/KTZH_00001863_00003561.csv')\n\n# Distribution 1\n# Ktzhdistid               : 3562\n# Title                    : Energieverbrauch als Bruttolastgang ab 2022\n# Description              : Der Bruttolastgang wird pro Viertelstunde in Kilowattstunden zusammengefasst.\n# Issued                   : 2022-11-25T09:02:55\n# Modified                 : 2025-04-13T10:19:42\n\ndf = get_dataset('https://www.web.statistik.zh.ch/ogd/daten/ressourcen/KTZH_00001863_00003562.csv')\n\n# Distribution 2\n# Ktzhdistid               : 3563\n# Title                    : Energieverbrauch als Bruttolastgang 2016-2018\n# Description              : Der Bruttolastgang wird pro Viertelstunde in Kilowattstunden zusammengefasst.\n# Issued                   : 2022-11-25T09:02:55\n# Modified                 : 2022-11-25T09:02:55\n\ndf = get_dataset('https://www.web.statistik.zh.ch/ogd/daten/ressourcen/KTZH_00001863_00003563.csv')\n\n# Distribution 3\n# Ktzhdistid               : 3564\n# Title                    : Energieverbrauch als Bruttolastgang 2019-2021\n# Description              : Der Bruttolastgang wird pro Viertelstunde in Kilowattstunden zusammengefasst.\n# Issued                   : 2022-11-25T09:02:55\n# Modified                 : 2022-11-25T09:02:55\n\ndf = get_dataset('https://www.web.statistik.zh.ch/ogd/daten/ressourcen/KTZH_00001863_00003564.csv')\n\n"}, {"cell_type": "markdown", "id": "4ce1f78f", "metadata": {}, "source": ["## Analyze data"]}, {"cell_type": "code", "execution_count": null, "id": "3e3dab86", "metadata": {}, "outputs": [], "source": ["# drop columns that have no values\n", "df.dropna(how='all', axis=1, inplace=True)"]}, {"cell_type": "code", "execution_count": null, "id": "841bd8d2", "metadata": {}, "outputs": [], "source": ["print(f'The dataset has {df.shape[0]:,.0f} rows (observations) and {df.shape[1]:,.0f} columns (variables).')\n", "print(f'There seem to be {df.duplicated().sum()} exact duplicates in the data.')"]}, {"cell_type": "code", "execution_count": null, "id": "75e73c96", "metadata": {}, "outputs": [], "source": ["df.info(memory_usage='deep', verbose=True)"]}, {"cell_type": "code", "execution_count": null, "id": "02f3df4d", "metadata": {}, "outputs": [], "source": ["df.head()"]}, {"cell_type": "code", "execution_count": null, "id": "a0d7d898", "metadata": {}, "outputs": [], "source": ["# display a small random sample transposed in order to see all variables\n", "df.sample(3).T"]}, {"cell_type": "code", "execution_count": null, "id": "786806dd", "metadata": {}, "outputs": [], "source": ["# describe non-numerical features\n", "try:\n", "    with pd.option_context('display.float_format', '{:,.2f}'.format):\n", "        display(df.describe(exclude='number'))\n", "except:\n", "    print(\"No categorical data in dataset.\")"]}, {"cell_type": "code", "execution_count": null, "id": "e744a6b6", "metadata": {}, "outputs": [], "source": ["# describe numerical features\n", "try:\n", "    with pd.option_context('display.float_format', '{:,.2f}'.format):\n", "        display(df.describe(include='number'))\n", "except:\n", "    print(\"No numercial data in dataset.\")"]}, {"cell_type": "code", "execution_count": null, "id": "7a65d95d", "metadata": {}, "outputs": [], "source": ["# check missing values with missingno\n", "# https://github.com/ResidentMario/missingno\n", "import missingno as msno\n", "msno.matrix(df, labels=True, sort='descending');"]}, {"cell_type": "code", "execution_count": null, "id": "fcc604b7", "metadata": {}, "outputs": [], "source": ["# plot a histogram for each numerical feature\n", "try:\n", "    df.hist(bins=25, rwidth=.9)\n", "    plt.tight_layout()\n", "    plt.show()\n", "except:\n", "    print(\"No numercial data to plot.\") "]}, {"cell_type": "code", "execution_count": null, "id": "e13cc6eb", "metadata": {}, "outputs": [], "source": ["# continue your code here..."]}, {"cell_type": "code", "execution_count": null, "id": "18886378", "metadata": {}, "outputs": [], "source": []}, {"cell_type": "code", "execution_count": null, "id": "59fce071", "metadata": {}, "outputs": [], "source": []}, {"cell_type": "code", "execution_count": null, "id": "f42f25aa", "metadata": {}, "outputs": [], "source": []}, {"cell_type": "markdown", "id": "c6b87d83", "metadata": {}, "source": ["**Contact**: Stadt Winterthur | Stadtwerk Winterthur | stadtwerk.messdaten@win.ch"]}], "metadata": {"kernelspec": {"display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3"}, "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.8.15"}}, "nbformat": 4, "nbformat_minor": 5}