1. edh_group_04
  2. egdh_group_04_notebook.ipynb
In [96]:
import pandas as pd
from google.colab import drive
import matplotlib.pyplot as plt
import datetime as dt
import plotly.express as px
from pandas_profiling import ProfileReport
In [97]:
drive.mount('/content/drive')

Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount("/content/drive", force_remount=True).
In [98]:
file = ('/content/drive/MyDrive/00_Energy Data/01_Historical_elec_dem_Swissgrid/EnergieUebersichtCH-2009.xls')
sheet='Zeitreihen0h15'
data = pd.read_excel(file, sheet_name=sheet, index_col=0)
In [99]:
data
Out[99]:
Summe endverbrauchte Energie Regelblock Schweiz\nTotal energy consumed by end users in the Swiss controlblock Summe produzierte Energie Regelblock Schweiz\nTotal energy production Swiss controlblock Summe verbrauchte Energie Regelblock Schweiz\nTotal energy consumption Swiss controlblock Netto Ausspeisung aus dem Übertragungsnetz Schweiz\nNet outflow of the Swiss transmission grid Vertikale Einspeisung ins Übertragungsnetz Schweiz\nGrid feed-in Swiss transmission grid Positive Sekundär-Regelenergie\nPositive secundary control energy Negative Sekundär-Regelenergie\nNegative secundary control energy Positive Tertiär-Regelenergie\nPositive tertiary control energy Negative Tertiär-Regelenergie\nNegative tertiary control energy Verbundaustausch CH->AT\nCross Border Exchange CH->AT Verbundaustausch AT->CH\nCross Border Exchange AT->CH Verbundaustausch CH->DE\nCross Border Exchange CH->DE Verbundaustausch DE->CH\nCross Border Exchange DE->CH Verbundaustausch CH->FR\nCross Border Exchange CH->FR Verbundaustausch FR->CH\nCross Border Exchange FR->CH Verbundaustausch CH->IT\nCross Border Exchange CH->IT Verbundaustausch IT->CH\nCross Border Exchange IT->CH Transit Import Export
Zeitstempel kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh kWh
2009-01-01 00:15:00 1.82997e+06 1.32884e+06 1.9235e+06 535032 1.05166e+06 0 -42000 0 0 0 245700 9000 563000 95750 313000 479250 48750 584000 1170450 584000
2009-01-01 00:30:00 1.71517e+06 1.31052e+06 1.90559e+06 531263 1.03093e+06 0 -64000 0 0 0 234300 0 552000 80750 316000 473250 41750 554000 1144050 554000
2009-01-01 00:45:00 1.73258e+06 1.33042e+06 1.91973e+06 531175 1.05461e+06 0 -52000 0 0 0 246400 1000 561000 86750 316000 475250 42750 563000 1166150 563000
2009-01-01 01:00:00 1.70669e+06 1.30333e+06 1.89183e+06 534226 1.03694e+06 0 -51000 0 0 0 249500 2000 561000 95750 306000 466250 43750 564000 1160250 564000
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2009-12-31 23:00:00 1.59472e+06 1.46343e+06 1.75076e+06 301174 1.04904e+06 0 -30100 0 0 0 248650 10000 391000 4000 292250 623000 0 637000 931900 637000
2009-12-31 23:15:00 1.62881e+06 1.50676e+06 1.79053e+06 292677 1.09858e+06 0 -7400 0 0 0 252600 13000 423000 13000 251500 617000 0 643000 927100 643000
2009-12-31 23:30:00 1.60725e+06 1.48576e+06 1.76776e+06 293602 1.08366e+06 0 -8200 0 0 0 248200 9000 408000 13000 251500 601000 0 623000 907700 623000
2009-12-31 23:45:00 1.60726e+06 1.4788e+06 1.76658e+06 295531 1.08548e+06 0 -2700 0 0 0 237200 9000 394000 17000 251500 567000 0 593000 882700 593000
2010-01-01 00:00:00 1.58056e+06 1.44028e+06 1.73005e+06 307828 1.05346e+06 0 -23000 0 0 0 232200 9000 358000 14000 247500 526000 0 549000 837700 549000

35041 rows × 20 columns

In [100]:
data.rename(columns = {'Summe verbrauchte Energie Regelblock Schweiz\nTotal energy consumption Swiss controlblock':'consumption'}, inplace = True)
In [101]:
fig = px.line(data, y="consumption")
fig.show()
In [102]:
data.drop(data.columns.difference(['consumption']), 1, inplace=True)

data
Out[102]:
consumption
Zeitstempel kWh
2009-01-01 00:15:00 1.9235e+06
2009-01-01 00:30:00 1.90559e+06
2009-01-01 00:45:00 1.91973e+06
2009-01-01 01:00:00 1.89183e+06
... ...
2009-12-31 23:00:00 1.75076e+06
2009-12-31 23:15:00 1.79053e+06
2009-12-31 23:30:00 1.76776e+06
2009-12-31 23:45:00 1.76658e+06
2010-01-01 00:00:00 1.73005e+06

35041 rows × 1 columns

In [103]:
data = data.iloc[1:]
#This drops the frist row
In [104]:
data.describe()
Out[104]:
consumption
count 3.504000e+04
unique 3.504000e+04
top 1.644684e+06
freq 1.000000e+00
In [105]:
fig = px.violin(data,  y="consumption")
fig.show()
In [106]:
data.reset_index(inplace=True)
data.rename(columns = {'index':'datetime'}, inplace = True)
data

/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py:4308: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
Out[106]:
datetime consumption
0 2009-01-01 00:15:00 1.9235e+06
1 2009-01-01 00:30:00 1.90559e+06
2 2009-01-01 00:45:00 1.91973e+06
3 2009-01-01 01:00:00 1.89183e+06
4 2009-01-01 01:15:00 1.91992e+06
... ... ...
35035 2009-12-31 23:00:00 1.75076e+06
35036 2009-12-31 23:15:00 1.79053e+06
35037 2009-12-31 23:30:00 1.76776e+06
35038 2009-12-31 23:45:00 1.76658e+06
35039 2010-01-01 00:00:00 1.73005e+06

35040 rows × 2 columns

In [119]:
data_hourly = data.groupby([pd.Grouper(key='datetime', freq='h')])['consumption'].sum().reset_index().sort_values('datetime')
In [120]:
fig = px.line(data_hourly, y="consumption")
fig.show()