{ "nbformat": 4, "nbformat_minor": 0, "metadata": { "colab": { "name": "Tim.ipynb", "provenance": [], "collapsed_sections": [] }, "kernelspec": { "name": "python3", "display_name": "Python 3" }, "language_info": { "name": "python" } }, "cells": [ { "cell_type": "code", "metadata": { "id": "aJPcwTW8UlmZ" }, "source": [ "import pandas as pd\n", "from google.colab import drive\n", "import matplotlib.pyplot as plt\n", "import datetime as dt\n", "import plotly.express as px\n", "from pandas_profiling import ProfileReport\n", "\n", "\n" ], "execution_count": 96, "outputs": [] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "jGL2Y6xYY1uf", "outputId": "7892647a-aed1-4349-ec61-cb60b84587e1" }, "source": [ "drive.mount('/content/drive')" ], "execution_count": 97, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n" ] } ] }, { "cell_type": "code", "metadata": { "id": "CsvpBzzQVBlE" }, "source": [ "file = ('/content/drive/MyDrive/00_Energy Data/01_Historical_elec_dem_Swissgrid/EnergieUebersichtCH-2009.xls')\n", "sheet='Zeitreihen0h15'\n", "data = pd.read_excel(file, sheet_name=sheet, index_col=0)" ], "execution_count": 98, "outputs": [] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 745 }, "id": "4UxpIbbtY9il", "outputId": "fd33faf8-082a-4b16-95c1-94344475d4a9" }, "source": [ "data" ], "execution_count": 99, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Summe endverbrauchte Energie Regelblock Schweiz\\nTotal energy consumed by end users in the Swiss controlblockSumme produzierte Energie Regelblock Schweiz\\nTotal energy production Swiss controlblockSumme verbrauchte Energie Regelblock Schweiz\\nTotal energy consumption Swiss controlblockNetto Ausspeisung aus dem Übertragungsnetz Schweiz\\nNet outflow of the Swiss transmission gridVertikale Einspeisung ins Übertragungsnetz Schweiz\\nGrid feed-in Swiss transmission gridPositive Sekundär-Regelenergie\\nPositive secundary control energyNegative Sekundär-Regelenergie\\nNegative secundary control energyPositive Tertiär-Regelenergie\\nPositive tertiary control energyNegative Tertiär-Regelenergie\\nNegative tertiary control energyVerbundaustausch CH->AT\\nCross Border Exchange CH->ATVerbundaustausch AT->CH\\nCross Border Exchange AT->CHVerbundaustausch CH->DE\\nCross Border Exchange CH->DEVerbundaustausch DE->CH\\nCross Border Exchange DE->CHVerbundaustausch CH->FR\\nCross Border Exchange CH->FRVerbundaustausch FR->CH\\nCross Border Exchange FR->CHVerbundaustausch CH->IT\\nCross Border Exchange CH->ITVerbundaustausch IT->CH\\nCross Border Exchange IT->CHTransitImportExport
ZeitstempelkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWhkWh
2009-01-01 00:15:001.82997e+061.32884e+061.9235e+065350321.05166e+060-42000000245700900056300095750313000479250487505840001170450584000
2009-01-01 00:30:001.71517e+061.31052e+061.90559e+065312631.03093e+060-64000000234300055200080750316000473250417505540001144050554000
2009-01-01 00:45:001.73258e+061.33042e+061.91973e+065311751.05461e+060-52000000246400100056100086750316000475250427505630001166150563000
2009-01-01 01:00:001.70669e+061.30333e+061.89183e+065342261.03694e+060-51000000249500200056100095750306000466250437505640001160250564000
...............................................................
2009-12-31 23:00:001.59472e+061.46343e+061.75076e+063011741.04904e+060-301000002486501000039100040002922506230000637000931900637000
2009-12-31 23:15:001.62881e+061.50676e+061.79053e+062926771.09858e+060-740000025260013000423000130002515006170000643000927100643000
2009-12-31 23:30:001.60725e+061.48576e+061.76776e+062936021.08366e+060-82000002482009000408000130002515006010000623000907700623000
2009-12-31 23:45:001.60726e+061.4788e+061.76658e+062955311.08548e+060-27000002372009000394000170002515005670000593000882700593000
2010-01-01 00:00:001.58056e+061.44028e+061.73005e+063078281.05346e+060-230000002322009000358000140002475005260000549000837700549000
\n", "

35041 rows × 20 columns

\n", "
" ], "text/plain": [ " Summe endverbrauchte Energie Regelblock Schweiz\\nTotal energy consumed by end users in the Swiss controlblock ... Export\n", "Zeitstempel kWh ... kWh\n", "2009-01-01 00:15:00 1.82997e+06 ... 584000\n", "2009-01-01 00:30:00 1.71517e+06 ... 554000\n", "2009-01-01 00:45:00 1.73258e+06 ... 563000\n", "2009-01-01 01:00:00 1.70669e+06 ... 564000\n", "... ... ... ...\n", "2009-12-31 23:00:00 1.59472e+06 ... 637000\n", "2009-12-31 23:15:00 1.62881e+06 ... 643000\n", "2009-12-31 23:30:00 1.60725e+06 ... 623000\n", "2009-12-31 23:45:00 1.60726e+06 ... 593000\n", "2010-01-01 00:00:00 1.58056e+06 ... 549000\n", "\n", "[35041 rows x 20 columns]" ] }, "metadata": {}, "execution_count": 99 } ] }, { "cell_type": "code", "metadata": { "id": "GpJCbuRbmWU-" }, "source": [ "data.rename(columns = {'Summe verbrauchte Energie Regelblock Schweiz\\nTotal energy consumption Swiss controlblock':'consumption'}, inplace = True)" ], "execution_count": 100, "outputs": [] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 542 }, "id": "yaK0g40_nDb9", "outputId": "2ed697be-4741-4090-dfd8-8850107616fe" }, "source": [ "fig = px.line(data, y=\"consumption\")\n", "fig.show()" ], "execution_count": 101, "outputs": [ { "output_type": "display_data", "data": { "text/html": [ "\n", "\n", "\n", "
\n", " \n", " \n", " \n", "
\n", " \n", "
\n", "\n", "" ] }, "metadata": {} } ] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 419 }, "id": "DXJ3fL6Ypvna", "outputId": "baa7a867-c3b0-4613-a590-ca7042cb73f1" }, "source": [ "data.drop(data.columns.difference(['consumption']), 1, inplace=True)\n", "\n", "data" ], "execution_count": 102, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
consumption
ZeitstempelkWh
2009-01-01 00:15:001.9235e+06
2009-01-01 00:30:001.90559e+06
2009-01-01 00:45:001.91973e+06
2009-01-01 01:00:001.89183e+06
......
2009-12-31 23:00:001.75076e+06
2009-12-31 23:15:001.79053e+06
2009-12-31 23:30:001.76776e+06
2009-12-31 23:45:001.76658e+06
2010-01-01 00:00:001.73005e+06
\n", "

35041 rows × 1 columns

\n", "
" ], "text/plain": [ " consumption\n", "Zeitstempel kWh\n", "2009-01-01 00:15:00 1.9235e+06\n", "2009-01-01 00:30:00 1.90559e+06\n", "2009-01-01 00:45:00 1.91973e+06\n", "2009-01-01 01:00:00 1.89183e+06\n", "... ...\n", "2009-12-31 23:00:00 1.75076e+06\n", "2009-12-31 23:15:00 1.79053e+06\n", "2009-12-31 23:30:00 1.76776e+06\n", "2009-12-31 23:45:00 1.76658e+06\n", "2010-01-01 00:00:00 1.73005e+06\n", "\n", "[35041 rows x 1 columns]" ] }, "metadata": {}, "execution_count": 102 } ] }, { "cell_type": "code", "metadata": { "id": "dHXCN9-YrAPy" }, "source": [ "data = data.iloc[1:]\n", "#This drops the frist row" ], "execution_count": 103, "outputs": [] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 173 }, "id": "TpOZBT4Tpico", "outputId": "1bdcc33f-1047-4cb7-e1e5-d1bc1771d8ce" }, "source": [ "data.describe()" ], "execution_count": 104, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
consumption
count3.504000e+04
unique3.504000e+04
top1.644684e+06
freq1.000000e+00
\n", "
" ], "text/plain": [ " consumption\n", "count 3.504000e+04\n", "unique 3.504000e+04\n", "top 1.644684e+06\n", "freq 1.000000e+00" ] }, "metadata": {}, "execution_count": 104 } ] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 542 }, "id": "BBqM5j_wrZsE", "outputId": "f174e023-bf4d-4765-99b4-a025c6ccd7b6" }, "source": [ "fig = px.violin(data, y=\"consumption\")\n", "fig.show()" ], "execution_count": 105, "outputs": [ { "output_type": "display_data", "data": { "text/html": [ "\n", "\n", "\n", "
\n", " \n", " \n", " \n", "
\n", " \n", "
\n", "\n", "" ] }, "metadata": {} } ] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 538 }, "id": "Send4_hTrl91", "outputId": "19d72789-bc73-4fbc-9c81-279196921d6c" }, "source": [ "data.reset_index(inplace=True)\n", "data.rename(columns = {'index':'datetime'}, inplace = True)\n", "data" ], "execution_count": 106, "outputs": [ { "output_type": "stream", "name": "stderr", "text": [ "/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py:4308: SettingWithCopyWarning:\n", "\n", "\n", "A value is trying to be set on a copy of a slice from a DataFrame\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", "\n" ] }, { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datetimeconsumption
02009-01-01 00:15:001.9235e+06
12009-01-01 00:30:001.90559e+06
22009-01-01 00:45:001.91973e+06
32009-01-01 01:00:001.89183e+06
42009-01-01 01:15:001.91992e+06
.........
350352009-12-31 23:00:001.75076e+06
350362009-12-31 23:15:001.79053e+06
350372009-12-31 23:30:001.76776e+06
350382009-12-31 23:45:001.76658e+06
350392010-01-01 00:00:001.73005e+06
\n", "

35040 rows × 2 columns

\n", "
" ], "text/plain": [ " datetime consumption\n", "0 2009-01-01 00:15:00 1.9235e+06\n", "1 2009-01-01 00:30:00 1.90559e+06\n", "2 2009-01-01 00:45:00 1.91973e+06\n", "3 2009-01-01 01:00:00 1.89183e+06\n", "4 2009-01-01 01:15:00 1.91992e+06\n", "... ... ...\n", "35035 2009-12-31 23:00:00 1.75076e+06\n", "35036 2009-12-31 23:15:00 1.79053e+06\n", "35037 2009-12-31 23:30:00 1.76776e+06\n", "35038 2009-12-31 23:45:00 1.76658e+06\n", "35039 2010-01-01 00:00:00 1.73005e+06\n", "\n", "[35040 rows x 2 columns]" ] }, "metadata": {}, "execution_count": 106 } ] }, { "cell_type": "code", "metadata": { "id": "bqW2ssMeyJZc" }, "source": [ "data_hourly = data.groupby([pd.Grouper(key='datetime', freq='h')])['consumption'].sum().reset_index().sort_values('datetime')" ], "execution_count": 119, "outputs": [] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 542 }, "id": "plER7n9ZzcMM", "outputId": "c9addb35-a218-4f3c-f59b-0be6b0dd92e4" }, "source": [ "fig = px.line(data_hourly, y=\"consumption\")\n", "fig.show()" ], "execution_count": 120, "outputs": [ { "output_type": "display_data", "data": { "text/html": [ "\n", "\n", "\n", "
\n", " \n", " \n", " \n", "
\n", " \n", "
\n", "\n", "" ] }, "metadata": {} } ] }, { "cell_type": "code", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 419 }, "id": "IwoJbvkzzv4u", "outputId": "776896e8-143d-4e3a-efb7-19ee6be16fdb" }, "source": [ "data_daily = data.groupby([pd.Grouper(key='datetime', freq='D')])['consumption'].sum().reset_index().sort_values('datetime')\n" ], "execution_count": 122, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datetimeconsumption
02009-01-011.628991e+08
12009-01-021.710086e+08
22009-01-031.743001e+08
32009-01-041.692960e+08
42009-01-052.031190e+08
.........
3612009-12-281.794427e+08
3622009-12-291.766299e+08
3632009-12-301.730471e+08
3642009-12-311.675710e+08
3652010-01-011.730051e+06
\n", "

366 rows × 2 columns

\n", "
" ], "text/plain": [ " datetime consumption\n", "0 2009-01-01 1.628991e+08\n", "1 2009-01-02 1.710086e+08\n", "2 2009-01-03 1.743001e+08\n", "3 2009-01-04 1.692960e+08\n", "4 2009-01-05 2.031190e+08\n", ".. ... ...\n", "361 2009-12-28 1.794427e+08\n", "362 2009-12-29 1.766299e+08\n", "363 2009-12-30 1.730471e+08\n", "364 2009-12-31 1.675710e+08\n", "365 2010-01-01 1.730051e+06\n", "\n", "[366 rows x 2 columns]" ] }, "metadata": {}, "execution_count": 122 } ] } ] }