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()

In [122]:

data_daily = data.groupby([pd.Grouper(key='datetime', freq='D')])['consumption'].sum().reset_index().sort_values('datetime')

Out[122]:

	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