KeyFiguresElectricalInstallations36kV.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/nrohrbach/KeyFiguresElectricalInstallations/blob/main/KeyFiguresElectricalInstallations36kV.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Kennzahlen Geobasisdatensatz Elektrische Anlagen mit einer Nennspannung von über 36kV\n",
        "\n",
        "---\n",
        "\n",
        "Notebook um Kennzahlen des Geobasisdatensatzes \"Elektrische Anlagen mit einer Nennspannung von über 36kV abzuleiten.\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "TvAaPsApM6kf"
      },
      "id": "TvAaPsApM6kf"
    },
    {
      "cell_type": "code",
      "source": [
        "#pip install geopandas"
      ],
      "metadata": {
        "id": "BtBcPFkSsYlX"
      },
      "id": "BtBcPFkSsYlX",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#pip install datetime"
      ],
      "metadata": {
        "id": "FIFlyBbH1nym"
      },
      "id": "FIFlyBbH1nym",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#pip install numpy"
      ],
      "metadata": {
        "id": "SgnmLfym-vE4"
      },
      "id": "SgnmLfym-vE4",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#pip install owslib"
      ],
      "metadata": {
        "id": "sACWjB2uyXDQ"
      },
      "id": "sACWjB2uyXDQ",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import geopandas as gpd\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "from datetime import datetime\n",
        "import matplotlib.pyplot as plt\n",
        "%matplotlib inline\n",
        "from owslib.wfs import WebFeatureService\n",
        "from requests import Request"
      ],
      "metadata": {
        "id": "O1snWJw5Nm8f"
      },
      "id": "O1snWJw5Nm8f",
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# WFS-Domain\n",
        "url = \"https://geodienste.ch/db/elektrische_anlagen_ueber_36kv_v1_0_0/deu\"\n",
        "\n",
        "# Initialize\n",
        "wfs = WebFeatureService(url=url)\n",
        "\n",
        "# Parameter für Datenfetching (Count 100 entfernen)\n",
        "paramsLeitung = dict(service='WFS', version=\"2.0.0\", request='GetFeature', typeName='leitung', outputFormat='geojson', startIndex=0)\n",
        "paramsStation = dict(service='WFS', version=\"2.0.0\", request='GetFeature', typeName='station_punkt', outputFormat='geojson', startIndex=0)\n",
        "paramsMast = dict(service='WFS', version=\"2.0.0\", request='GetFeature', typeName='mast', outputFormat='geojson', startIndex=0)\n",
        "\n",
        "# Parse URL mit Parametern\n",
        "urlleitung = Request('GET', url, params=paramsLeitung).prepare().url\n",
        "urlstation = Request('GET', url, params=paramsStation).prepare().url\n",
        "urlmast = Request('GET', url, params=paramsMast).prepare().url\n",
        "\n",
        "# Daten als Dataframe lesen\n",
        "leitung = gpd.read_file(urlleitung)\n",
        "station = gpd.read_file(urlstation)\n",
        "mast = gpd.read_file(urlmast)"
      ],
      "metadata": {
        "id": "iWF0-Ah1jdTo"
      },
      "id": "iWF0-Ah1jdTo",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Mapping auf bestehendes Datenmodell\n",
        "dfLeitung = leitung.rename(columns ={\n",
        "                         'id':'xtf_id',\n",
        "                         'bezeichnung':'Bezeichnung',\n",
        "                         'eigentuemer':'Eigentuemer',\n",
        "                         'eigentum_vollstaendig':'EigentumVollstaendig',\n",
        "                         'lagegenauigkeit':'Lagegenauigkeit',\n",
        "                         'stromnetz_typ':'StromnetzTyp',\n",
        "                         'leitung_typ':'LeitungTyp',\n",
        "                         'spannung':'Spannung',\n",
        "                         'betriebsstatus':'Betriebsstatus',\n",
        "                         'frequenz':'Frequenz'})\n",
        "\n",
        "dfStation = station.rename(columns ={\n",
        "                        'id':'xtf_id',\n",
        "                        'bezeichnung':'Bezeichnung',\n",
        "                        'eigentuemer':'Eigentuemer',\n",
        "                        'eigentum_vollstaendig':'EigentumVollstaendig',\n",
        "                        'lagegenauigkeit':'Lagegenauigkeit',\n",
        "                        'stromnetz_typ':'StromnetzTyp',\n",
        "                        'station_typ': 'StationTyp'})\n",
        "\n",
        "dfMast = mast.rename(columns ={\n",
        "                        'id':'xtf_id',\n",
        "                        'bezeichnung':'Bezeichnung',\n",
        "                        'eigentuemer':'Eigentuemer',\n",
        "                        'eigentum_vollstaendig':'EigentumVollstaendig',\n",
        "                        'lagegenauigkeit':'Lagegenauigkeit',\n",
        "                        'stromnetz_typ':'StromnetzTyp',\n",
        "                        'mast_typ':'MastTyp',\n",
        "                        'hoehe':'Hoehe'})\n"
      ],
      "metadata": {
        "id": "nYvtqbPaopoH"
      },
      "id": "nYvtqbPaopoH",
      "execution_count": 8,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Kennzahlen Leitungen"
      ],
      "metadata": {
        "id": "PRptTEzMFt3o"
      },
      "id": "PRptTEzMFt3o"
    },
    {
      "cell_type": "code",
      "source": [
        "# Länge der Leitungen berechnen und nach Spannung aufsummieren\n",
        "dfLeitung[\"Laenge\"] = dfLeitung.length\n",
        "dfLeitungKPI = dfLeitung.groupby(['StromnetzTyp','Spannung','LeitungTyp'])['Laenge'].sum()\n",
        "dfLeitungKPI = dfLeitungKPI.to_frame().reset_index()\n",
        "dfLeitungKPI['Datum'] = datetime.today().strftime(\"%Y-%m-%d\")"
      ],
      "metadata": {
        "id": "A5dxmBTfvY3u"
      },
      "id": "A5dxmBTfvY3u",
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Speichern\n",
        "dfLeitungKPI.to_csv(\"data/Kennzahlen_Leitungen.csv\", header=False, index=False, mode='a')"
      ],
      "metadata": {
        "id": "Dt1dONLBRkNV"
      },
      "id": "Dt1dONLBRkNV",
      "execution_count": 11,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Kennzahlen Masten"
      ],
      "metadata": {
        "id": "pH1oWHxLGONC"
      },
      "id": "pH1oWHxLGONC"
    },
    {
      "cell_type": "code",
      "source": [
        "dfMastKPI = dfMast.groupby(['StromnetzTyp','MastTyp'])['xtf_id'].count().reset_index()\n",
        "dfMastKPI = dfMastKPI.rename(columns={'xtf_id':'Anzahl'})\n",
        "dfMastKPI['Datum'] = datetime.today().strftime(\"%Y-%m-%d\")"
      ],
      "metadata": {
        "id": "cIqj0wiVGSCa"
      },
      "id": "cIqj0wiVGSCa",
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Speichern\n",
        "dfMastKPI.to_csv(\"data/Kennzahlen_Masten.csv\", header=False, index=False, mode='a')"
      ],
      "metadata": {
        "id": "N-dEcwdHGTgn"
      },
      "id": "N-dEcwdHGTgn",
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Kennzahlen Stationen"
      ],
      "metadata": {
        "id": "GGnwlH5EJX4A"
      },
      "id": "GGnwlH5EJX4A"
    },
    {
      "cell_type": "code",
      "source": [
        "dfStationKPI = dfStation.groupby(['StromnetzTyp','StationTyp'])['xtf_id'].count().reset_index()\n",
        "dfStationKPI = dfStationKPI.rename(columns={'xtf_id':'Anzahl'})\n",
        "dfStationKPI['Datum'] = datetime.today().strftime(\"%Y-%m-%d\")"
      ],
      "metadata": {
        "id": "Xa3Vnx8NGvEI"
      },
      "id": "Xa3Vnx8NGvEI",
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Speichern\n",
        "dfStationKPI.to_csv(\"data/Kennzahlen_Stationen.csv\", header=False, index=False, mode='a')"
      ],
      "metadata": {
        "id": "V1XOLqR3Jo5Q"
      },
      "id": "V1XOLqR3Jo5Q",
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Kennzahlen Werke"
      ],
      "metadata": {
        "id": "be5rdoCHR_Bs"
      },
      "id": "be5rdoCHR_Bs"
    },
    {
      "cell_type": "code",
      "source": [
        "# Leitungslänge pro Werk ableiten.\n",
        "dfWerkeLeitung = dfLeitung.groupby(['Eigentuemer','LeitungTyp'])['Laenge'].sum()\n",
        "dfWerkeLeitung = dfWerkeLeitung.to_frame().reset_index()\n",
        "dfWerkeLeitung = pd.pivot(dfWerkeLeitung,index='Eigentuemer',columns='LeitungTyp')\n",
        "dfWerkeLeitung = dfWerkeLeitung.reset_index()\n",
        "dfWerkeLeitung.columns = dfWerkeLeitung.columns.droplevel()\n",
        "dfWerkeLeitung.columns = ['Werk','Freileitung','Kabelleitung']\n",
        "dfWerkeLeitung = dfWerkeLeitung.fillna(0)\n",
        "dfWerkeLeitung['LeitungGesamt']= dfWerkeLeitung['Kabelleitung']+dfWerkeLeitung['Freileitung']"
      ],
      "metadata": {
        "id": "4nqMeP4onPy_"
      },
      "id": "4nqMeP4onPy_",
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Anzahl Masten pro Werk ableiten.\n",
        "dfWerkeMast = dfMast\n",
        "dfWerkeMast['Werk'] = dfWerkeMast['Eigentuemer']\n",
        "dfWerkeMast = dfWerkeMast.groupby(['Werk'])['xtf_id'].count().reset_index()\n",
        "dfWerkeMast = dfWerkeMast.rename(columns={'xtf_id':'AnzahlMasten'})"
      ],
      "metadata": {
        "id": "PGtabzpdUiYk"
      },
      "id": "PGtabzpdUiYk",
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Anzahl Stationen pro Werk ableiten.\n",
        "dfWerkeStation = dfStation\n",
        "dfWerkeStation['Werk'] = dfWerkeStation['Eigentuemer']\n",
        "dfWerkeStation = dfWerkeStation.groupby(['Werk'])['xtf_id'].count().reset_index()\n",
        "dfWerkeStation = dfWerkeStation.rename(columns={'xtf_id':'AnzahlStationen'})"
      ],
      "metadata": {
        "id": "ol2pE__CUjgk"
      },
      "id": "ol2pE__CUjgk",
      "execution_count": 18,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Zusammenführen\n",
        "dfWerke = pd.merge(dfWerkeLeitung, dfWerkeMast, how=\"outer\", on=[\"Werk\"])\n",
        "dfWerke = pd.merge(dfWerke, dfWerkeStation, how=\"outer\", on=[\"Werk\"])\n",
        "dfWerke = dfWerke.fillna(0)\n",
        "dfWerke['Datum'] = datetime.today().strftime(\"%Y-%m-%d\")"
      ],
      "metadata": {
        "id": "h-zBkvApU5g9"
      },
      "id": "h-zBkvApU5g9",
      "execution_count": 19,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Speichern\n",
        "dfWerke.to_csv(\"data/Kennzahlen_Werke.csv\", header=False, index=False, mode='a')"
      ],
      "metadata": {
        "id": "BtD90pTTU6Rr"
      },
      "id": "BtD90pTTU6Rr",
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Kennzahlen für Visualisierung in Storymap ableiten"
      ],
      "metadata": {
        "id": "StNwzcm9AaFu"
      },
      "id": "StNwzcm9AaFu"
    },
    {
      "cell_type": "code",
      "source": [
        "dfLeitungStorymap = dfLeitungKPI\n",
        "\n",
        "# Gewünschte Kategorien definieren\n",
        "Bedingungen = [dfLeitungStorymap['Spannung']=='S380kV',\n",
        "               dfLeitungStorymap['Spannung']=='S220kV',\n",
        "               (dfLeitungStorymap['StromnetzTyp']=='Allgemeinstrom') & (dfLeitungStorymap['Spannung'].isin(['S150kV','S132kV','S130kV','S125kV','S110kV'])),\n",
        "               (dfLeitungStorymap['StromnetzTyp']=='Allgemeinstrom') & (dfLeitungStorymap['Spannung'].isin(['S66kV','S65kV','S60kV','S50kV','S36kV','Andere'])),\n",
        "               (dfLeitungStorymap['StromnetzTyp']=='Bahnstrom') & (dfLeitungStorymap['Spannung']=='S132kV'),\n",
        "               (dfLeitungStorymap['StromnetzTyp']=='Bahnstrom') & (dfLeitungStorymap['Spannung'].isin(['S66kV','Andere']))\n",
        "               ]\n",
        "\n",
        "Spannungen = ['s380','s220','s150','s66','s132B','s66B']\n",
        "\n",
        "# Neues Attribut \"Kategorie\" erstellen welches für alle Objekte die gewünschte Kategorie bestimmt\n",
        "dfLeitungStorymap['Kategorie'] =  np.select(Bedingungen,Spannungen)"
      ],
      "metadata": {
        "id": "pTRV_22WvXn2"
      },
      "id": "pTRV_22WvXn2",
      "execution_count": 21,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Dataframe für Totale Länge vorbereiten\n",
        "dfTotal = dfLeitungStorymap.groupby('Kategorie').sum(numeric_only=True).reset_index()\n",
        "dfTotal['Kategorie'] = 'Total_'+ dfTotal['Kategorie']\n",
        "dfTotal = dfTotal.transpose()\n",
        "dfTotal.columns = dfTotal.iloc[0]\n",
        "dfTotal = dfTotal[1:]\n",
        "dfTotal['Datum'] = datetime.today().strftime(\"%Y-%m-%d\")"
      ],
      "metadata": {
        "id": "LcuJ0bh_vkRl"
      },
      "id": "LcuJ0bh_vkRl",
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Dataframe für Länge von Kabel vorbereiten\n",
        "dfKabel = dfLeitungStorymap.loc[dfLeitungStorymap['LeitungTyp']=='Kabelleitung'].groupby('Kategorie').sum(numeric_only=True).reset_index()\n",
        "dfKabel['Kategorie'] = 'Kabel_'+ dfKabel['Kategorie']\n",
        "dfKabel = dfKabel.transpose()\n",
        "dfKabel.columns = dfKabel.iloc[0]\n",
        "dfKabel = dfKabel[1:]\n",
        "dfKabel['Datum'] = datetime.today().strftime(\"%Y-%m-%d\")"
      ],
      "metadata": {
        "id": "kCBR3i-ZvkPF"
      },
      "id": "kCBR3i-ZvkPF",
      "execution_count": 23,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Zusammenführen\n",
        "dfStorymap = pd.merge(dfTotal, dfKabel, how='left', on=[\"Datum\"])"
      ],
      "metadata": {
        "id": "HegE9FV7XF3Z"
      },
      "id": "HegE9FV7XF3Z",
      "execution_count": 24,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Temporäre Platzhalter für die Bahnstromkategorien erstellen. kann später gelöscht werden!\n",
        "dfStorymap['Total_s132B'] = 1\n",
        "dfStorymap['Kabel_s132B'] = 1\n",
        "dfStorymap['Total_s66B'] = 1\n",
        "dfStorymap['Kabel_s66B'] = 1"
      ],
      "metadata": {
        "id": "K-SEPYHuaE8e"
      },
      "id": "K-SEPYHuaE8e",
      "execution_count": 25,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Speichern\n",
        "dfStorymap.to_csv(\"data/Kennzahlen_Storymap.csv\", header=False, index=False, mode='a')"
      ],
      "metadata": {
        "id": "oyMD6TWfXYfj"
      },
      "id": "oyMD6TWfXYfj",
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Visualisierungen Werke"
      ],
      "metadata": {
        "id": "YqQRyW_ncLAT"
      },
      "id": "YqQRyW_ncLAT"
    },
    {
      "cell_type": "code",
      "source": [
        "#Daten für Visualisierung laden\n",
        "df = pd.read_csv(\"data/Kennzahlen_Werke.csv\", parse_dates=['Datum'])\n",
        "dfVis = df\n",
        "dfVis['Freileitung'] = dfVis['Freileitung']/1000\n",
        "dfVis['Kabelleitung'] = dfVis['Kabelleitung']/1000\n",
        "dfVis['LeitungGesamt'] = dfVis['LeitungGesamt']/1000\n",
        "dfVis['Freileitung'] = dfVis['Freileitung'].round(0)\n",
        "dfVis['Kabelleitung'] = dfVis['Kabelleitung'].round(0)\n",
        "dfVis['LeitungGesamt'] = dfVis['LeitungGesamt'].round(0)\n",
        "dfVis = df.loc[df['Datum'] == datetime.today().strftime(\"%Y-%m-%d\")]"
      ],
      "metadata": {
        "id": "h-iOxBcgYbVg"
      },
      "id": "h-iOxBcgYbVg",
      "execution_count": 66,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Anzahl Masten pro Werkbetreiber"
      ],
      "metadata": {
        "id": "ZOunrA7sEiJM"
      },
      "id": "ZOunrA7sEiJM"
    },
    {
      "cell_type": "code",
      "source": [
        "#Barchart Anzahl Masten\n",
        "dfVisMast = dfVis[dfVis[\"AnzahlMasten\"]>0]\n",
        "dfVisMast = dfVisMast.sort_values('AnzahlMasten',ascending=False)\n",
        "fig, ax = plt.subplots()\n",
        "ax.bar(dfVisMast['Werk'], dfVisMast['AnzahlMasten'])\n",
        "ax.set_title('Anzahl Masten')\n",
        "plt.xticks(rotation = 90)\n",
        "plt.ylabel(\"Anzahl Masten\")\n",
        "plt.rcParams[\"figure.figsize\"] = (10,5)\n",
        "ax.bar_label(ax.containers[0], label_type='edge')\n",
        "plt.savefig('plots/AnzahlMasten.png',bbox_inches='tight')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "ufMSOqQ40J0t"
      },
      "id": "ufMSOqQ40J0t",
      "execution_count": 86,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Anzahl Stationen pro Werkbetreiber"
      ],
      "metadata": {
        "id": "yU-JFKUuEpiz"
      },
      "id": "yU-JFKUuEpiz"
    },
    {
      "cell_type": "code",
      "source": [
        "#Barchart Anzahl Stationen\n",
        "dfVisStationen = dfVis[dfVis[\"AnzahlStationen\"]>0]\n",
        "dfVisStationen = dfVisStationen.sort_values('AnzahlStationen',ascending=False)\n",
        "fig, ax = plt.subplots()\n",
        "plt.rcParams[\"figure.figsize\"] = (4,2)\n",
        "ax.bar(dfVisStationen['Werk'], dfVisStationen['AnzahlStationen'])\n",
        "ax.set_title('Anzahl Stationen')\n",
        "plt.xticks(rotation = 90)\n",
        "plt.ylabel(\"Anzahl Stationen\")\n",
        "ax.bar_label(ax.containers[0], label_type='edge')\n",
        "plt.rcParams[\"figure.figsize\"] = (10,5)\n",
        "plt.savefig('plots/AnzahlStationen.png',bbox_inches='tight')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "m-Ey3roy2Lbx"
      },
      "id": "m-Ey3roy2Lbx",
      "execution_count": 88,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Länge der Leitungen pro Werkbetreiber"
      ],
      "metadata": {
        "id": "XSIrEb4TErbk"
      },
      "id": "XSIrEb4TErbk"
    },
    {
      "cell_type": "code",
      "source": [
        "#Barchart Länge Leitungen\n",
        "\n",
        "dfVisLaenge = dfVis[dfVis[\"LeitungGesamt\"]>0]\n",
        "dfVisLaenge = dfVisLaenge.sort_values('LeitungGesamt',ascending=False)\n",
        "\n",
        "#bar chart properties\n",
        "x = np.arange(len(dfVisLaenge['Werk']))\n",
        "width = 0.4\n",
        "\n",
        "#draw grouped bar chart\n",
        "fig, ax = plt.subplots()\n",
        "bar1 = ax.bar(x - width/2, dfVisLaenge['Freileitung'], width, label='Freileitung')\n",
        "bar2 = ax.bar(x + width/2, dfVisLaenge['Kabelleitung'], width, label='Kabelleitung')\n",
        "\n",
        "#ax.set_xlabel('Year')\n",
        "ax.set_ylabel('Länge (km)')\n",
        "ax.set_title('Länge der Leitungen')\n",
        "ax.set_xticks(x, dfVisLaenge['Werk'])\n",
        "ax.legend()\n",
        "\n",
        "#setting bar labels\n",
        "ax.bar_label(bar1)\n",
        "ax.bar_label(bar2)\n",
        "plt.xticks(rotation = 90)\n",
        "\n",
        "plt.rcParams[\"figure.figsize\"] = (20,6)\n",
        "plt.savefig('plots/LaengeLeitungen.png',bbox_inches='tight')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "P6YydczLQ9FF"
      },
      "id": "P6YydczLQ9FF",
      "execution_count": 91,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Visualisierungen Masten"
      ],
      "metadata": {
        "id": "SOX-r9khEwN1"
      },
      "id": "SOX-r9khEwN1"
    },
    {
      "cell_type": "code",
      "source": [
        "#Zeitliche Entwicklung Anzahl Masten\n",
        "dfAnzahlMasten = pd.read_csv(\"data/Kennzahlen_Masten.csv\", parse_dates=['Datum'])\n",
        "dfAnzahlMastenEntwicklung = dfAnzahlMasten.pivot(index=\"Datum\", columns=[\"MastTyp\",\"StromnetzTyp\"],values=\"Anzahl\")\n",
        "dfAnzahlMastenEntwicklung.plot(figsize=(15,10))\n",
        "plt.legend(loc='lower left')\n",
        "plt.title(\"Übersicht Anzahl Masten\")\n",
        "plt.savefig('plots/UebersichtAnzahlMasten.png')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "cusvFpStU0EJ"
      },
      "id": "cusvFpStU0EJ",
      "execution_count": 92,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Visualisierung Stationen"
      ],
      "metadata": {
        "id": "_oyEBJJEE221"
      },
      "id": "_oyEBJJEE221"
    },
    {
      "cell_type": "code",
      "source": [
        "#Zeitliche Entwicklung Anzahl Stationen\n",
        "dfAnzahlStationen = pd.read_csv(\"data/Kennzahlen_Stationen.csv\", parse_dates=['Datum'])\n",
        "dfAnzahlStationen = dfAnzahlStationen.pivot(index=\"Datum\", columns=[\"StationTyp\",\"StromnetzTyp\"],values=\"Anzahl\")\n",
        "dfAnzahlStationen.plot(figsize=(15,10))\n",
        "plt.legend(loc='lower left')\n",
        "plt.title(\"Übersicht Anzahl Stationen\")\n",
        "plt.savefig('plots/UebersichtAnzahlStationen.png')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "ZuiZW9tXVP4R"
      },
      "id": "ZuiZW9tXVP4R",
      "execution_count": 94,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Visualisierung Leitungen\n"
      ],
      "metadata": {
        "id": "F59FEU0QE4nW"
      },
      "id": "F59FEU0QE4nW"
    },
    {
      "cell_type": "code",
      "source": [
        "#Zeitliche Entwicklung Länge der Leitungen - Daten vorbereiten\n",
        "dfLaengeLeitungen = pd.read_csv(\"data/Kennzahlen_Leitungen.csv\", parse_dates=['Datum'])\n",
        "dfLaengeLeitungen['Laenge'] = dfLaengeLeitungen['Laenge']/1000\n",
        "dfLaengeLeitungen['Laenge'] = dfLaengeLeitungen['Laenge'].round(0)"
      ],
      "metadata": {
        "id": "E8PacBEeVcAk"
      },
      "id": "E8PacBEeVcAk",
      "execution_count": 95,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Leitungslänge pro Leitungstyp - zeitliche Entwicklung"
      ],
      "metadata": {
        "id": "SVpJL8qtE9x8"
      },
      "id": "SVpJL8qtE9x8"
    },
    {
      "cell_type": "code",
      "source": [
        "#Zeitliche Entwicklung Länge der Leitungen - Leitungstyp\n",
        "dfLaengeLeitungTyp = dfLaengeLeitungen.groupby(['LeitungTyp','Datum'])['Laenge'].sum().reset_index()\n",
        "dfLaengeLeitungTyp = dfLaengeLeitungTyp.pivot(index=\"Datum\", columns=[\"LeitungTyp\"],values=\"Laenge\")\n",
        "dfLaengeLeitungTyp.plot(figsize=(15,10))\n",
        "plt.legend(loc='lower left')\n",
        "plt.title(\"Länge der Leitungen (km) - Leitungstyp\")\n",
        "plt.savefig('plots/LaengeLeitungen_Typ.png')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "Ml7JNM_beh2G"
      },
      "id": "Ml7JNM_beh2G",
      "execution_count": 96,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Leitungslänge pro Spannungsebene - zeitliche Entwicklung"
      ],
      "metadata": {
        "id": "Gzrd7g9vFHh9"
      },
      "id": "Gzrd7g9vFHh9"
    },
    {
      "cell_type": "code",
      "source": [
        "#Zeitliche Entwicklung Länge der Leitungen - Spannung\n",
        "dfLaengeLeitungTyp = dfLaengeLeitungen.groupby(['Spannung','Datum'])['Laenge'].sum().reset_index()\n",
        "dfLaengeLeitungTyp = dfLaengeLeitungTyp.pivot(index=\"Datum\", columns=[\"Spannung\"],values=\"Laenge\")\n",
        "dfLaengeLeitungTyp.plot(figsize=(15,10))\n",
        "plt.legend(loc='lower left')\n",
        "plt.title(\"Länge der Leitungen (km) - Spannung\")\n",
        "plt.savefig('plots/LaengeLeitungen_Spannung.png')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "gfqjqdEbf1ud"
      },
      "id": "gfqjqdEbf1ud",
      "execution_count": 97,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Leitungslänge pro Stromnetztyp - zeitliche Entwicklung"
      ],
      "metadata": {
        "id": "BqPpm7FbFKVM"
      },
      "id": "BqPpm7FbFKVM"
    },
    {
      "cell_type": "code",
      "source": [
        "#Zeitliche Entwicklung Länge der Leitungen - StromnetzTyp\n",
        "dfLaengeLeitungTyp = dfLaengeLeitungen.groupby(['StromnetzTyp','Datum'])['Laenge'].sum().reset_index()\n",
        "dfLaengeLeitungTyp = dfLaengeLeitungTyp.pivot(index=\"Datum\", columns=[\"StromnetzTyp\"],values=\"Laenge\")\n",
        "dfLaengeLeitungTyp.plot(figsize=(15,10))\n",
        "plt.legend(loc='lower left')\n",
        "plt.title(\"Länge der Leitungen (km) - StromnetzTyp\")\n",
        "plt.savefig('plots/LaengeLeitungen_StromnetzTyp.png')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "Awg0WZImgGyK"
      },
      "id": "Awg0WZImgGyK",
      "execution_count": 98,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Leitungslänge nach Spannung"
      ],
      "metadata": {
        "id": "3nqVaes9FM7Z"
      },
      "id": "3nqVaes9FM7Z"
    },
    {
      "cell_type": "code",
      "source": [
        "#Barchart Länge Leitungen nach Spannung\n",
        "\n",
        "#Nur aktuellste Zeile\n",
        "dfLaengeLeitungen = dfLaengeLeitungen.loc[dfLaengeLeitungen['Datum'] == datetime.today().strftime(\"%Y-%m-%d\")]\n",
        "\n",
        "#Daten vorbereiten\n",
        "dfLeitungenBarChart = dfLaengeLeitungen.pivot_table(index='Spannung',columns=['LeitungTyp'],values='Laenge', aggfunc='sum').reset_index()\n",
        "dfLeitungenBarChart = dfLeitungenBarChart.fillna(0)\n",
        "dfLeitungenBarChart['Spannung'] = dfLeitungenBarChart['Spannung'].replace({'S':''},regex=True)\n",
        "dfLeitungenBarChart = dfLeitungenBarChart.sort_values('Freileitung',ascending=False)\n",
        "\n",
        "#bar chart properties\n",
        "x = np.arange(len(dfLeitungenBarChart['Spannung']))\n",
        "width = 0.3\n",
        "\n",
        "#draw grouped bar chart\n",
        "fig, ax = plt.subplots(figsize=(15,10))\n",
        "bar1 = ax.bar(x - width/2, dfLeitungenBarChart['Freileitung'], width, label='Freileitung')\n",
        "bar2 = ax.bar(x + width/2, dfLeitungenBarChart['Kabelleitung'], width, label='Kabelleitung')\n",
        "\n",
        "#ax.set_xlabel('Year')\n",
        "ax.set_ylabel('Länge (km)')\n",
        "ax.set_title('Länge der Leitungen')\n",
        "ax.set_xticks(x, dfLeitungenBarChart['Spannung'])\n",
        "\n",
        "#setting bar labels\n",
        "ax.bar_label(bar1)\n",
        "ax.bar_label(bar2)\n",
        "plt.xticks(rotation = 90)\n",
        "plt.legend(loc='upper right')\n",
        "plt.savefig('plots/LaengeLeitungen_Spannung_Uebersicht.png',bbox_inches='tight')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "Tefs2SwBwz4B"
      },
      "id": "Tefs2SwBwz4B",
      "execution_count": 100,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Masttyp Übersicht"
      ],
      "metadata": {
        "id": "_Bn9J5T7FYNH"
      },
      "id": "_Bn9J5T7FYNH"
    },
    {
      "cell_type": "code",
      "source": [
        "#Daten vorbereiten\n",
        "dfAnzahlMastenUebersicht = dfAnzahlMasten\n",
        "dfAnzahlMastenUebersicht = dfAnzahlMastenUebersicht.loc[dfAnzahlMastenUebersicht['Datum'] == datetime.today().strftime(\"%Y-%m-%d\")]\n",
        "\n",
        "#Barchart\n",
        "x = np.arange(len(dfAnzahlMastenUebersicht['MastTyp']))\n",
        "fig, ax = plt.subplots(figsize=(15,10))\n",
        "bar1 = ax.bar(x, dfAnzahlMastenUebersicht['Anzahl'], label='MastTyp')\n",
        "ax.set_ylabel('Anzahl Masten')\n",
        "ax.set_title('Anzahl Masten')\n",
        "ax.set_xticks(x, dfAnzahlMastenUebersicht['MastTyp'])\n",
        "ax.bar_label(bar1)\n",
        "plt.savefig('plots/AnzahlMasten_Uebersicht.png',bbox_inches='tight')\n",
        "plt.close()"
      ],
      "metadata": {
        "id": "baCONmEtBcoq"
      },
      "id": "baCONmEtBcoq",
      "execution_count": 101,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "2Ztw06ATe4ZD"
      },
      "id": "2Ztw06ATe4ZD",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "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.7.11"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}