<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Notebook Introduction2&nbsp;&nbsp;Import the libraries3&nbsp;&nbsp;Read in the datasets3.1&nbsp;&nbsp;Solar / Wind power generation3.1.1&nbsp;&nbsp;Quick Exploration3.1.2&nbsp;&nbsp;Extracting the relevant columns3.1.3&nbsp;&nbsp;Renaming and Updating columns3.1.4&nbsp;&nbsp;High level Missing Data3.1.5&nbsp;&nbsp;Saving the output3.2&nbsp;&nbsp;Weather Data3.2.1&nbsp;&nbsp;Quick Exploration3.2.2&nbsp;&nbsp;Extracting the relevant columns3.2.3&nbsp;&nbsp;Renaming and Updating columns3.2.4&nbsp;&nbsp;High level Missing Data3.2.5&nbsp;&nbsp;Saving the output  Energy Analysis Project - Notebook 1 : Data Collection / Wrangling**@Author: <NAME>****June 2020**This project will go through the entire data science pipeline by analysing the Energy consumption of various European countries. Most of the data was obtained from the [Open Power System Data](https://open-power-system-data.org/) website with the exception of the sunset and sunrise dataset that is taken from [this website.](https://www.timeanddate.com/sun/). This project has been inspired by a workshop I attended in the conference virtual DSGO that analysed this data to demonstrate the use of MLflow. You can find out more about this project in [this repo.](https://github.com/Samreay/DSGoPipeline) This project also uses Cookiecutter for file structure. You can find more about it in [this article.](https://medium.com/@rrfd/cookiecutter-data-science-organize-your-projects-atom-and-jupyter-2be7862f487e)![energy_image](https://795d665f9fc7b1053e24-4d632937b8453c17306cf8bcb974f77f.ssl.cf3.rackcdn.com/x/1330cm500/images/Sectors/Sustainable_energy/sector-banner_sustainable-energy-banner-fisher-german.jpg)This project will combine various open-source datasets that relate top power generation of various European countries. It will go through the entire data science project outlined below: 1. Data Collection / Wrangling2. Data Manipulation / Cleanning3. Exploratory Data Analysis (EDA)4. Modelling / Validation5. Deployment of ModelThe aim of this project is to learn what affect the energy generation of these countries. The full code for this project can be found here.  Notebook IntroductionThis notebook will focus on data collection and wrangling. This is the process of collecting all the data you will need for your project and transforms it into a usable form. Most of the times data that you can find is in a form that is hard to be analyzed, this part will look at different ways to transform this data and make it usable for the rest of the project.The data sources we will use in this project are:- Solar / Wind power generation- Historic weather data- Energy capacity across different countries- Sunset / Sunrise data  Import the libraries This section imports the libraries needed for this project.<jupyter_code># general python libraries
import os
from datetime import datetime

# data manipulation
import numpy as np
import pandas as pd
import missingno as msno
%matplotlib inline

# import the .src file for custom functions
# this requires that you run the following command
# pip install --editable .
import src

# define the paths
raw_data_path = "../data/raw"<jupyter_output><empty_output><jupyter_text>Read in the datasetsThis section will read the various datasets used in this project.  Solar / Wind power generationAs mentioned in the introduction of this notebook the data for this is taken directly from [this website](https://data.open-power-system-data.org/time_series/). From the website, this data includes Load, wind and solar, prices in hourly resolution for various European countries. We use *Version 2019-06-05*.To start with we will get only the data from Germany (DE), France (FR), Denmark (DK), United Kingdom (UK) and Switzerland (CH) as we also have data for renewable plans on those countries.<jupyter_code># read the data from the direct link
df_energy = pd.read_csv("https://data.open-power-system-data.org/time_series/2019-06-05/time_series_60min_singleindex.csv",
                        parse_dates=[0], index_col=0)
# display the data
df_energy.head()<jupyter_output><empty_output><jupyter_text>Quick ExplorationLet's get some quick information on the dataset. We define a function that we can use later on as we will be dealing primarily with Time series data.<jupyter_code>def get_ts_data_details(df_ts):
    """
    Takes in a time series
    dataset and returns some
    general information
    
    - Col and Row number 
    - Date Range
    
    """
    
    # check whether we have a datetime index
    if pd.api.types.is_datetime64_ns_dtype(df_ts.index):
        
        # get the max and min date
        min_date = df_ts.index.min().date()
        max_date = df_ts.index.max().date()
        
        # print the shape of the dataset and the range
        print(f"There are {df_ts.shape[0]} rows and {df_ts.shape[1]} columns in this dataset.")
        print(f"The data ranges from {min_date} to {max_date}")

    else:
        print("Please use a dataframe with datetime index.")
        
    return

get_ts_data_details(df_ts = df_energy)<jupyter_output>There are 125593 rows and 392 columns in this dataset.
The data ranges from 2004-12-31 to 2019-04-30<jupyter_text>As we can see we have plenty of data (about 125K rows) with various columns that represent the different countries. The data covers approximately 15 years of hourly intervals.<jupyter_code># get a quick summary of all columns
df_energy.describe(include="all")<jupyter_output><empty_output><jupyter_text>From the above we can see that quite a few columns have missing data. We will explore this more in the next section but first we will get only the data we are interested in.  Extracting the relevant columnsThis section will extract the columns for only the countries of interest. We are also interested only on the total Load, Solar and Wind generation. For all other fields please check the full list of columns on the [Open Power System Data](https://data.open-power-system-data.org/time_series/) website.<jupyter_code>df_energy.head()
# define the country codes and rel columns
country_codes = ['DE', 'FR', 'DK', 'UK', 'CH']

# get the load columns
load_cols = ["DE_load_actual_entsoe_power_statistics",
             "FR_load_actual_entsoe_power_statistics",
             "GB_UKM_load_actual_entsoe_power_statistics",
             "DK_load_actual_entsoe_power_statistics",
             "CH_wind_onshore_generation_actual"]

# get the solar generation columns
solar_cols = ["DE_solar_generation_actual",
              "FR_solar_generation_actual",
              "GB_UKM_solar_generation_actual",
              "CH_solar_generation_actual",
              "DK_solar_generation_actual"]

# get the wind generation columns
wind_cols = ["DE_wind_generation_actual",
             "FR_wind_onshore_generation_actual",
             "GB_UKM_wind_onshore_generation_actual",
             "GB_UKM_wind_offshore_generation_actual",
             "DK_wind_offshore_generation_actual",
             "DK_wind_onshore_generation_actual",
             "CH_wind_onshore_generation_actual"]

all_cols = load_cols + solar_cols + wind_cols

# extract the data
df_energy_rel = df_energy[all_cols].copy()<jupyter_output><empty_output><jupyter_text>Renaming and Updating columnsThe next step is to rename some of the columns and combine others. As observed, for some countries we only have "onshore" wind energy. For those we will just assume that's the total wind generation we have. For others we have a column for both "onshore" and offshore, for those we will just combine them and assume that's the total energy.<jupyter_code># sum the columns that have both onshore and offshore
# the min_count ensures that at least one value is present
# otherwise it defaults to NaN
df_energy_rel['GB_UKM_wind_generation_actual'] = df_energy_rel[['GB_UKM_wind_offshore_generation_actual',
                                                                'GB_UKM_wind_onshore_generation_actual']].sum(min_count=1,
                                                                                                          axis=1)

df_energy_rel['DK_wind_generation_actual'] = df_energy_rel[['DK_wind_offshore_generation_actual',
                                                            'DK_wind_onshore_generation_actual']].sum(min_count=1,
                                                                                                      axis=1)

# drop the original columns
df_energy_rel = df_energy_rel.drop(["GB_UKM_wind_onshore_generation_actual",
                                    "GB_UKM_wind_offshore_generation_actual",
                                    "DK_wind_offshore_generation_actual",
                                    "DK_wind_onshore_generation_actual"], axis=1)

df_energy_rel = df_energy_rel.rename(columns={"FR_wind_onshore_generation_actual" : "FR_wind_generation_actual"})

# replace "GB_UKM" with just "UK"
df_energy_rel.columns = df_energy_rel.columns.str.replace("GB_UKM", "UK")

# create a list to rename the columns
renamed_cols = []

# Loop through the columns and make the replacements
for col in df_energy_rel.columns:
    
    if "wind" in col:
        renamed_cols.append(col.split("_")[0] + "_" + "wind")
    elif "solar" in col:
        renamed_cols.append(col.split("_")[0] + "_" + "solar")
    elif "load" in col:
        renamed_cols.append(col.split("_")[0] + "_" + "load")
    else:
        renamed_cols.append(col)
        
# add the new columns and present the result
df_energy_rel.columns = renamed_cols
df_energy_rel.head(5)<jupyter_output><empty_output><jupyter_text>High level Missing DataThis section will look at for "large" chunks of missing data (e.g more than 3 month) and deal with them. It won't deal with any imputing methods as this will be covered in the next notebook. For this we will use the [Missingno library](https://github.com/ResidentMario/missingno).<jupyter_code># create a missing data matrix
msno.matrix(df_energy_rel);<jupyter_output><empty_output><jupyter_text>As we can see there are different levels of missing data depending on the country. For our study to be as meaningful as possible we need data from all three areas (Load, Wind and Solar). For that reason we will chose our "cutoff" point where the DE_solar and FR_solar columns start. This is because Solar is the column with the least data compared to the other three. Although other countries have less data (e.g. UK and CH) as we will be modelling these separately we will deal with that at a later stage.<jupyter_code># # column to use as basis
# basis_drop_col = "DE_solar"
# rn_before = df_energy_rel.shape[0]

# # we wouldn't normally use this as it can drop values lower in the column
# # however as seen above there are no such values for DE_solar
# df_energy_rel = df_energy_rel.dropna(subset=[basis_drop_col], axis=0)
# rn_after = df_energy_rel.shape[0]
# perc_rem = round(rn_after / rn_before * 100,0)

# print new data stats
print(f"Total of {perc_rem}% rows remain after dropping nulls for column {basis_drop_col}.")
get_ts_data_details(df_ts=df_energy_rel)

# create the matrix again
msno.matrix(df_energy_rel);<jupyter_output>Total of 51.0% rows remain after dropping nulls for column DE_solar
There are 64190 rows and 15 columns in this data
The data ranges from 2012-01-01 to 2019-04-30<jupyter_text>We have retained only about 51% of our data which now ranges from 2012-2019.<jupyter_code># show the final data
df_energy_rel.head()<jupyter_output><empty_output><jupyter_text>Saving the output We are now happy to save our output data into the *raw* data folder of the project.<jupyter_code># save the output
output_path = os.path.join(raw_data_path, "power_data_raw.csv")
df_energy_rel.to_csv(output_path)<jupyter_output><empty_output><jupyter_text>Weather DataFor this project we will also use some weather information that you can also find on [Open Power System Data](https://data.open-power-system-data.org/weather_data/) website. The data has hour resolution for European countries. We are using *Version : 2019-04-09*<jupyter_code># read in the data directly from the link
df_weather = pd.read_csv("https://data.open-power-system-data.org/weather_data/2019-04-09/weather_data.csv", 
                         parse_dates=[0], index_col=0)

# print the head
df_weather.head()<jupyter_output><empty_output><jupyter_text>Quick Exploration<jupyter_code># get quick stats on the data
get_ts_data_details(df_ts = df_weather)
df_weather.describe(include="all")<jupyter_output><empty_output><jupyter_text>As seen we only have data up to the end of 2016. This means when we combine the weather and energy data we will data from years 2012-2017. Under normal circumstances we would seek weather data for later years but for simplicity we will just use those years. For now we will just filter to data from 2012 onwards and merge them in the next part of the project.<jupyter_code># take all data after 2012
df_weather = df_weather.loc["2012":].copy()<jupyter_output><empty_output><jupyter_text>Extracting the relevant columns This section will extract the columns for the countries of interest. As you will see below unfortunately we don't have data wind data for the UK. That means we won't be able to carry out all part of the analysis for that country.<jupyter_code># create a search string for the columns of interest
col_string = "DE_|GB_|CH_|DK_|FR_"

# extract only the relevant columns
weather_cols = df_weather.columns
weather_cols = weather_cols[weather_cols.str.contains(col_string)]

# filter the data
df_weather_rel = df_weather[weather_cols].copy()<jupyter_output><empty_output><jupyter_text>Renaming and Updating columns This part will rename the columns to something simpler.<jupyter_code># create a rename dictionary
rename_dict = {'windspeed_10m':'wind',
               'radiation_diffuse_horizontal' : 'rad_diff',
               'radiation_direct_horizontal' : 'rad_dir',
               'temperature' : 'temp'}

# rename the columns
df_weather_rel.columns = pd.Series(df_weather_rel.columns).replace(rename_dict, regex=True)

# display the final result
df_weather_rel.head()<jupyter_output><empty_output><jupyter_text>High level Missing Data Similar to the above we will check for missing data using Missigno.<jupyter_code># create a missing data matrix
msno.matrix(df_weather_rel);<jupyter_output><empty_output><jupyter_text>We have no missing data so we can just output the file as it is.  Saving the output We save this data into the raw data folder.<jupyter_code># save the output
output_path = os.path.join(raw_data_path, "weather_data_raw.csv")
df_weather_rel.to_csv(output_path)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07522424571894537, 'fraction_numerical': 0.016988312041315574, 'mean_word_length': 4.129661903102126, 'pattern_counts': {'":': 1, '<': 68, '<?xml version=': 0, '>': 68, 'https://': 11, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '33157', 'license': 'MIT', 'n_tokens_mistral': 4433, 'n_tokens_neox': 4182, 'n_words': 1505, 'path': 'notebooks/Notebook 1 - Data Collection.ipynb', 'repo_name': 'Stratoshad/power_generation_analysis', 'size': '14729'}