<jupyter_start><jupyter_text>Data origin :: smard The origin of this data is the onlien plattform "smard" provided and maintained by "Bundesnetzagentur". It's a central collection and publiaction of electricity generation, transportation and consumption data and information of the german electricty market. The url for the following data is: [smard; Data](https://www.smard.de/home/downloadcenter/download-marktdaten!?downloadAttributes=%7B%22selectedCategory%22:false,%22selectedSubCategory%22:false,%22selectedRegion%22:false,%22from%22:1628373600000,%22to%22:1629323999999,%22selectedFileType%22:false%7D)  Feature Description Features are documented in the [smard manual](https://www.smard.de/home/benutzerhandbuch).  Import<jupyter_code>import datetime, time, os
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import seaborn as sns
import pandas as pd
import glob
#from pandas_profiling import ProfileReport
import json

#from cesium import datasets

from functools import reduce

from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_absolute_error, mean_squared_error

# Make numpy printouts easier to read.
#np.set_printoptions(precision=3, suppress=True)

from datetime import datetime, timezone, timedelta
import pytz
resample_factor = 15

import warnings<jupyter_output><empty_output><jupyter_text>Time Zone Adaption All time information on SMARD refers to the time valid at the respective timeCentral European Standard Time (CET or UTC+1) or Central European Summer Time (CEST or UTC+2).(CEST or UTC+2).- The changeover from standard time to daylight saving time takes place every last Sundayin March at 02:00 by skipping the hour from 02:00 to 03:00.- The changeover from summer time to standard time takes place every last Sunday inin October at 03:00 by repeating the hour from 02:00 to 03:00o'clock  Part 1: SMARD Data Mining  1.1 Import "Predicted Energy Consumption"<jupyter_code># Read csv file and assign to dataframe
df_prog_cons = pd.read_csv("../data/smard/df_prog_cons.csv")
print(df_prog_cons.shape)
df_prog_cons.head()
df_prog_cons.tail()
# Convert time to datetime object and set it as index
df_prog_cons["dt_start_cet"] = pd.to_datetime(df_prog_cons["dt_start_cet"])
df_prog_cons.set_index('dt_start_cet', inplace=True)

# Convert CET time zone to UTC time zone and rename index.
df_prog_cons.index = df_prog_cons.index.tz_localize('Europe/Berlin', ambiguous="NaT" , nonexistent="NaT")
df_prog_cons.index = df_prog_cons.index.tz_convert(pytz.utc)
df_prog_cons.index.names = ['dt_start_utc']
df_prog_cons.index = pd.to_datetime(df_prog_cons.index)
df_prog_cons.index = df_prog_cons.index.tz_localize(None)<jupyter_output><empty_output><jupyter_text>We will need drop all the columns that are constituted by infinite values and NaNs, as they are unusable.<jupyter_code>count_inf = np.isinf(df_prog_cons).values.sum()
count_null = df_prog_cons.isnull().sum().sum()

print("The data frame contains " + str(count_inf) + " infinite values and " + str(count_null) + " missing values.")<jupyter_output>The data frame contains 0 infinite values and 0 missing values.<jupyter_text>Fortunately, we can keep every column of the dataframe.<jupyter_code># Print the type of each variable in the dataframe

df_prog_cons.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 158876 entries, 2016-12-31 22:00:00 to 2021-07-13 19:45:00
Data columns (total 5 columns):
 #   Column            Non-Null Count   Dtype  
---  ------            --------------   -----  
 0   50Hertz_power_mw  158876 non-null  float64
 1   DE_power_mw       158876 non-null  float64
 2   DK_power_mw       158876 non-null  float64
 3   DK1_power_mw      158876 non-null  float64
 4   TTG_power_mw      158876 non-null  float64
dtypes: float64(5)
memory usage: 7.3 MB<jupyter_text>At this point, we like to implement a feature that sums up the total predicted energy consumption, which is the sum of all columns.All features are numerical, so we don't need to take action in converting the value type.<jupyter_code>df_prog_cons['total_pred_cons'] = df_prog_cons.sum(axis=1)<jupyter_output><empty_output><jupyter_text>1.2 Import "Realisierte Erzeugung"  This dataset contains the realized electrical energy.<jupyter_code>df = pd.read_csv("../data/smard/Stromerzeugung/Realisierte_Erzeugung_2021.csv", delimiter=";", decimal=',')

df.shape
df.head()
# Merge and convert "Datum" und "Uhrzeit" to datetime object and set it as index
df["datetime"]= df['Datum'] + ' ' + df['Uhrzeit']
df.drop(columns=['Datum', 'Uhrzeit'], inplace=True)
df["datetime"] = pd.to_datetime(df["datetime"], dayfirst=True)

df["datetime"] = pd.to_datetime(df["datetime"])
df.set_index('datetime', inplace=True)


#Convert CET time zone to UTC time zone and rename index.
df.index = df.index.tz_localize('Europe/Berlin', ambiguous="NaT" , nonexistent="NaT")
df.index = df.index.tz_convert(pytz.utc)
df.index = pd.to_datetime(df.index)
df.index = df.index.tz_localize(None)
df.index.names = ['dt_start_utc']
df.head()
# Print the type of each variable in the dataframe
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 17372 entries, 2020-12-31 23:00:00 to 2021-06-30 21:45:00
Data columns (total 12 columns):
 #   Column                        Non-Null Count  Dtype 
---  ------                        --------------  ----- 
 0   Biomasse[MWh]                 17372 non-null  object
 1   Wasserkraft[MWh]              17372 non-null  int64 
 2   Wind Offshore[MWh]            17372 non-null  object
 3   Wind Onshore[MWh]             17372 non-null  object
 4   Photovoltaik[MWh]             17372 non-null  object
 5   Sonstige Erneuerbare[MWh]     17372 non-null  int64 
 6   Kernenergie[MWh]              17372 non-null  object
 7   Braunkohle[MWh]               17372 non-null  object
 8   Steinkohle[MWh]               17372 non-null  object
 9   Erdgas[MWh]                   17372 non-null  object
 10  Pumpspeicher[MWh]             17372 non-null  object
 11  Sonstige Konventionelle[MWh]  17372 non-null  int64 
dtypes: int64(3), object(9)
memory usage: 1.[...]<jupyter_text>Not all columns are numerical type. For futher work we need to convert them to numerical type.<jupyter_code>df[df['Erdgas[MWh]'].str.contains("-")].shape[0]<jupyter_output><empty_output><jupyter_text>The column "Erdgas[MWh]" has strings with the value "-". We replace them with the numerical value "0".<jupyter_code>df["Erdgas[MWh]"] = df["Erdgas[MWh]"].replace("-", int(0))<jupyter_output><empty_output><jupyter_text>Now we can convert all values to a numerical tpye.<jupyter_code>df = df.astype("float32", copy=False)
df.dtypes<jupyter_output><empty_output><jupyter_text>At this point, we like to implement a feature that sums up the total supplied energy, which is the sum of all columns.<jupyter_code>df['rel_total'] = df.sum(axis=1)<jupyter_output><empty_output><jupyter_text>The data is typically published with a delay of 2 hours. We need to shif them by 8 rows, because the time delta of two rows are 15 min.<jupyter_code>df = df.shift(periods=8)
df_real_prov = df.copy()
df_real_prov.head()<jupyter_output><empty_output><jupyter_text>Merge  We merge booth data frames to a single dataframe<jupyter_code>df_merge = df_prog_cons.merge(df_real_prov, left_index=True, right_index=True)
df_merge.head()<jupyter_output><empty_output><jupyter_text>We create a new feature. The difference between forcasted consumption and supplied power.<jupyter_code>df_merge.eval("diff_prog_real = total_pred_cons - rel_total", inplace=True)<jupyter_output><empty_output><jupyter_text>Save Data Set For further work we save the data frame as a "pickle".<jupyter_code>df_merge.to_pickle('../data/pickle/df_merged_1.pickle')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10228578725577832, 'fraction_numerical': 0.040097050185161534, 'mean_word_length': 4.775811209439528, 'pattern_counts': {'":': 0, '<': 56, '<?xml version=': 0, '>': 56, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '560822', 'license': 'MIT', 'n_tokens_mistral': 2841, 'n_tokens_neox': 2575, 'n_words': 723, 'path': '1_Data_Mining_Cleaning/imbalance_price_1_1_data_mining_smard.ipynb', 'repo_name': 'Jaoaud/Capstone_Energy', 'size': '7831'}