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
drive.mount('/content/drive')
Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount("/content/drive", force_remount=True).
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)
data
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
data.rename(columns = {'Summe verbrauchte Energie Regelblock Schweiz\nTotal energy consumption Swiss controlblock':'consumption'}, inplace = True)
fig = px.line(data, y="consumption")
fig.show()
data.drop(data.columns.difference(['consumption']), 1, inplace=True)
data
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
data = data.iloc[1:]
#This drops the frist row
data.describe()
consumption | |
---|---|
count | 3.504000e+04 |
unique | 3.504000e+04 |
top | 1.644684e+06 |
freq | 1.000000e+00 |
fig = px.violin(data, y="consumption")
fig.show()
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
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
data_hourly = data.groupby([pd.Grouper(key='datetime', freq='h')])['consumption'].sum().reset_index().sort_values('datetime')
fig = px.line(data_hourly, y="consumption")
fig.show()
data_daily = data.groupby([pd.Grouper(key='datetime', freq='D')])['consumption'].sum().reset_index().sort_values('datetime')
datetime | consumption | |
---|---|---|
0 | 2009-01-01 | 1.628991e+08 |
1 | 2009-01-02 | 1.710086e+08 |
2 | 2009-01-03 | 1.743001e+08 |
3 | 2009-01-04 | 1.692960e+08 |
4 | 2009-01-05 | 2.031190e+08 |
... | ... | ... |
361 | 2009-12-28 | 1.794427e+08 |
362 | 2009-12-29 | 1.766299e+08 |
363 | 2009-12-30 | 1.730471e+08 |
364 | 2009-12-31 | 1.675710e+08 |
365 | 2010-01-01 | 1.730051e+06 |
366 rows × 2 columns