{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "\n", " \n", " \n", " \n", "
\n", " National generation capacity: Processing notebook\n", " \n", "
This Notebook is part of the National Generation Capacity Datapackage of Open Power System Data.\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Table of Contents\n", "* [1. Introductory notes](#1.-Introductory-notes)\n", "* [2. Script setup](#2.-Script-setup)\n", "* [3. Data download and import](#3.-Data-download-and-import)\n", "\t* [3.1 Manually compiled dataset](#3.1-Manually-compiled-dataset)\n", "\t* [3.2 EUROSTAT data](#3.2-EUROSTAT-data)\n", "\t* [3.3 ENTSO-E data](#3.3-ENTSO-E-data)\n", "\t\t* [3.3.1 ENTSO-E statistical data](#3.3.1-ENTSO-E-statistical-data)\n", "\t\t* [3.3.2 ENTSO-E SO&AF data](#3.3.2-ENTSO-E-SO&AF-data)\n", "* [4. Data processing](#4.-Data-processing)\n", "\t* [4.1 Manually compiled dataset](#4.1-Manually-compiled-dataset)\n", "\t* [4.2 EUROSTAT data](#4.2-EUROSTAT-data)\n", "\t\t* [4.2.1 Convert cross-table format to list](#4.2.1-Convert-cross-table-format-to-list)\n", "\t\t* [4.2.2 Read definition table for energy sources and merge with dataset](#4.2.2-Read-definition-table-for-energy-sources-and-merge-with-dataset)\n", "\t\t* [4.2.3 Adjust EUROSTAT data to OPSD data format](#4.2.3-Adjust-EUROSTAT-data-to-OPSD-data-format)\n", "\t\t* [4.2.4 Determine aggregated values](#4.2.4-Determine-aggregated-values)\n", "\t\t\t* [4.2.4.1 Technology and fuel level](#4.2.4.1-Technology-and-fuel-level)\n", "\t\t\t* [4.2.4.2 Fuel type level](#4.2.4.2-Fuel-type-level)\n", "\t\t\t* [4.2.4.3 Total level](#4.2.4.3-Total-level)\n", "\t\t* [4.2.5 Add additional information](#4.2.5-Add-additional-information)\n", "\t* [4.3 ENTSO-E statistical data](#4.3-ENTSO-E-statistical-data)\n", "\t\t* [4.3.1 Align table structure to OPSD](#4.3.1-Align-table-structure-to-OPSD)\n", "\t\t* [4.3.2 Standardize definition of energy sources/technologies](#4.3.2-Standardize-definition-of-energy-sources/technologies)\n", "\t\t* [4.3.3 Determine aggregated values](#4.3.3-Determine-aggregated-values)\n", "\t\t\t* [4.3.3.1 Technology and fuel level](#4.3.3.1-Technology-and-fuel-level)\n", "\t\t\t* [4.3.3.2 Fuel type level](#4.3.3.2-Fuel-type-level)\n", "\t\t\t* [4.3.3.3 Total level](#4.3.3.3-Total-level)\n", "\t\t* [4.3.4 Add additional information](#4.3.4-Add-additional-information)\n", "\t* [4.4 ENTSO-E SO&AF data](#4.4-ENTSO-E-SO&AF-data)\n", "\t\t* [4.4.1 Align table structure to OPSD](#4.4.1-Align-table-structure-to-OPSD)\n", "\t\t* [4.4.2 Standardize definition of energy sources/technologies](#4.4.2-Standardize-definition-of-energy-sources/technologies)\n", "\t\t* [4.4.3 Determine aggregated values](#4.4.3-Determine-aggregated-values)\n", "\t\t\t* [4.4.3.1 Technology and fuel level](#4.4.3.1-Technology-and-fuel-level)\n", "\t\t\t* [4.4.3.2 Fuel type and total level](#4.4.3.2-Fuel-type-and-total-level)\n", "\t\t* [4.4.4 Add additional information](#4.4.4-Add-additional-information)\n", "\t* [4.5 Merge data sources](#4.5-Merge-data-sources)\n", "* [5. Implementation of energy source levels](#5.-Implementation-of-energy-source-levels)\n", "* [6. Convert stacked data to crosstable format](#6.-Convert-stacked-data-to-crosstable-format)\n", "* [7. Documentation of the data package](#7.-Documentation-of-the-data-package)\n", "* [8. Write results to file](#8.-Write-results-to-file)\n", "* [9. Formatting of Excel tables](#9.-Formatting-of-Excel-tables)\n", "* [10. Write checksums](#10.-Write-checksums)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 1. Introductory notes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The script processes the compiled nationally aggregated generation capacity for European countries. Due to varying formats and data specifications of references for national generation capacity, the script firstly focuses on rearranging the manually compiled data. Thus, the script itself does not collect, select, download or manage data from original sources. Secondly, international data sources, such as EUROSTAT and ENTSO-E, are directly downloaded from original web sources and complement the initial data set." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 2. Script setup" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import json\n", "import logging\n", "import os.path\n", "import shutil\n", "import sqlite3\n", "import hashlib\n", "import pandas as pd\n", "import yaml # http://pyyaml.org/, pip install pyyaml, conda install pyyaml\n", "import os\n", "import shutil\n", "import numpy as np\n", "import urllib.parse\n", "import urllib.request\n", "import posixpath\n", "import datetime\n", "import zipfile\n", "import openpyxl\n", "from openpyxl.styles import PatternFill, colors, Font, Alignment\n", "from openpyxl.utils import get_column_letter\n", "\n", "logging.basicConfig(\n", " level=logging.INFO,\n", " format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',\n", " datefmt='%d %b %Y %H:%M:%S'\n", ")\n", "\n", "logger = logging.getLogger()\n", "\n", "# create download and output folder if they do not exist\n", "os.makedirs('download', exist_ok=True)\n", "os.makedirs('output', exist_ok=True)\n", "os.makedirs(os.path.join('output'), exist_ok=True)\n", "os.makedirs(os.path.join('output', 'original_data'), exist_ok=True)\n", "# logging.info(format)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_sha_hash(path, blocksize=65536):\n", " sha_hasher = hashlib.sha256()\n", " with open(path, 'rb') as f:\n", " buffer = f.read(blocksize)\n", " while len(buffer) > 0:\n", " sha_hasher.update(buffer)\n", " buffer = f.read(blocksize)\n", " return sha_hasher.hexdigest()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def downloadandcache(url, filename):\n", " \"\"\"\n", " Download a file into a folder called \"downloads\".\n", " Returns the local filepath.\n", "\n", " Parameters\n", " ----------\n", " url : str\n", " Url of a file to be downloaded\n", " filename : str\n", " Name of the downloaded file\n", " \"\"\"\n", "\n", " path = urllib.parse.urlsplit(url).path\n", " now = datetime.datetime.now()\n", " datestring = str(now.year) + \"-\" + str(now.month) + \"-\" + str(now.day)\n", " filepath = os.path.join('download', datestring + \"-\" + filename)\n", " filepath_original_data = os.path.join('output',\n", " 'original_data',\n", " filename)\n", "\n", " # check if file exists, otherwise download it\n", " if not os.path.exists(filepath):\n", " logger.info('Downloading file %s', filename)\n", " urllib.request.urlretrieve(url, filepath)\n", " urllib.request.urlretrieve(url, filepath_original_data)\n", " else:\n", " logger.info('Using local file from %s', filepath)\n", "\n", " return filepath" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def checkIfEmptyAndSetDefault(df, technology, default=0):\n", " sub_df = df.loc[df['technology'] == technology, 'capacity']\n", " \n", " if len(sub_df) == 0:\n", " return default\n", " else:\n", " return sub_df.values[0]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 3. Data download and import" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We compile data from different national and international sources. Firstly, national data sources are manually compiled due to varying data formats and specifications. Secondly, international sources are compiled directly and appended to the compiled data set. The international data sources comprise:\n", "- [EUROSTAT](http://ec.europa.eu/eurostat/product?code=nrg_113a&mode=view)\n", "- [ENTSO-E Statistical data](https://www.entsoe.eu/data/data-portal/miscellaneous/Pages/default.aspx)\n", "- [ENTSO-E System Outlook and Adequacy Forecast](https://www.entsoe.eu/outlooks/maf/Pages/default.aspx)\n", "\n", "In the following section, the data sets are downloaded and uploaded to Python." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3.1 Manually compiled dataset" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The manually compiled dataset is imported and rearranged to a DataFrame for further processing. The dataset comprises for each European country and specified generation technology different data entries, which are based on different sources. As these sources differ by country and year, information on the corresponding reference are directly given with the data entry. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_file = 'National_Generation_Capacities.xlsx'\n", "filepath = os.path.join('input', data_file)\n", "\n", "# Read data into pandas\n", "data_raw = pd.read_excel(filepath,\n", " sheet_name='Summary',\n", " header=None,\n", " na_values=['-'],\n", " skiprows=0)\n", "\n", "\n", "# Deal with merged cells from Excel: fill first three rows with information\n", "data_raw.iloc[0:2] = data_raw.iloc[0:2].fillna(method='ffill', axis=1)\n", "\n", "# Set index for rows\n", "data_raw = data_raw.set_index([0])\n", "data_raw.index.name = 'technology'\n", "\n", "# Extract energylevels from raw data\n", "energylevels_raw = data_raw.iloc[:, 0:5]\n", "\n", "# Delete definition of energy levels from raw data\n", "data_raw = data_raw.drop(data_raw.columns[[0, 1, 2, 3, 4]], axis=1)\n", "\n", "# Set multiindex column names\n", "data_raw.columns = pd.MultiIndex.from_arrays(data_raw[:6].values,\n", " names=['country', 'type', 'year',\n", " 'source', 'source_type',\n", " 'capacity_definition'])\n", "\n", "# Remove 3 rows which are already used as column names\n", "data_raw = data_raw[pd.notnull(data_raw.index)]\n", "\n", "# Extract the ordering of technologies\n", "technology_order = data_raw.index.str.replace('- ', '').values.tolist()\n", "\n", "# data_raw" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3.2 EUROSTAT data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "EUROSTAT publishes annual structural data on national electricity generation capacities for European countries. The dataset is available in the EUROSTAT database within the category 'Environment and Energy' ([nrg_113a](http://ec.europa.eu/eurostat/product?code=nrg_113a&mode=view))." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Data source for EUROSTAT data\n", "# http://ec.europa.eu/eurostat/estat-navtree-portlet-prod/BulkDownloadListing?sort=1&downfile=data%2Fnrg_113a.tsv.gz\n", "\n", "url_eurostat = ('http://ec.europa.eu/eurostat/estat-navtree-portlet-prod/'\n", " 'BulkDownloadListing?sort=1&downfile=data%2Fnrg_113a.tsv.gz')\n", "\n", "filepath_eurostat = downloadandcache(url_eurostat, 'nrg_113a.tsv.gz')\n", "\n", "print(filepath_eurostat)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_eurostat = pd.read_csv(filepath_eurostat,\n", " compression='gzip',\n", " sep='\\t|,',\n", " engine='python')\n", "\n", "data_eurostat['source'] = 'EUROSTAT'\n", "data_eurostat['source_type'] = 'Statistical Office'\n", "data_eurostat['type'] = 'Installed capacity in MW'\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3.3 ENTSO-E data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The ENTSO-E publishes annual data on national generation capacites in different specifications and formats. We use two relevant data sources from the ENTSOE-E, which comprises firstly statistical data within the [Data Portal (up to 2015)](https://www.entsoe.eu/data/data-portal/Pages/default.aspx) or [ENTSO-E Transparency Platform](https://transparency.entsoe.eu/), and secondly datasets compiled within the [ENTSO-E System Outlook & Adequacy Forecast (SO&AF)](https://www.entsoe.eu/outlooks/maf/Pages/default.aspx). The ENTSO-E Transparency Platform is currently not implemented as a data source for national generation capacities.\n", "\n", "The advantage of the ENTSO-E SO&AF is the higher granularity of the data with respect to the main fuel or technology. However, as the SO&AF provides a forecast on future system conditions in particular peak hours, the dataset also accounts for expected capacity changes throughout the years. Therefore, we only consider years which are closest to the publication year of the respective SO&AF." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.3.1 ENTSO-E statistical data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the following, we use the statistical data available in the [Data Portal (up to 2015)](https://www.entsoe.eu/data/data-portal/Pages/default.aspx)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Data source for ENTSO-E capacity data\n", "# https://docstore.entsoe.eu/Documents/Publications/Statistics/NGC_2010-2015.xlsx\n", "\n", "url_entsoe = 'https://docstore.entsoe.eu/Documents/Publications/Statistics/NGC_2010-2015.xlsx'\n", "filepath_entsoe = downloadandcache(url_entsoe, 'Statistics' + '.xls')\n", "data_entsoe_raw = pd.read_excel(filepath_entsoe)\n", "\n", "# correct typos\n", "data_entsoe_raw.rename(index=str, columns={\"Country\": \"country\", 'fossil_fueals': 'fossil_fuels'}, inplace=True)\n", "\n", "data_entsoe_raw.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.3.2 ENTSO-E SO&AF data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The following datasets cover the [ENTSO-E System Outlook & Adequacy Forecast (SO&AF)](https://www.entsoe.eu/outlooks/maf/Pages/default.aspx) for the years 2011-2015. For each SO&AF dataset, we process only the capacity values of the first year, except for the SO&AF 2014, which provides two relevant years." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "url_entsoe_soaf2015 = (\n", " 'https://www.entsoe.eu/Documents/Publications/SDC/data/SO_AF_2015_dataset.zip')\n", "url_entsoe_soaf2014 = (\n", " 'https://www.entsoe.eu/Documents/SDC%20documents/SOAF/140602_SOAF%202014_dataset.zip')\n", "url_entsoe_soaf2013 = (\n", " 'https://www.entsoe.eu/fileadmin/user_upload/_library/publications/entsoe/So_AF_2013-2030/130403_SOAF_2013-2030_dataset.zip')\n", "url_entsoe_soaf2012 = (\n", " 'https://www.entsoe.eu/fileadmin/user_upload/_library/SDC/SOAF/120705_SOAF_2012_Dataset.zip')\n", "url_entsoe_soaf2011 = (\n", " 'https://www.entsoe.eu/fileadmin/user_upload/_library/SDC/SOAF/SO_AF_2011_-_2025_.zip')\n", "\n", "data_soaf = pd.DataFrame()\n", "data_soaf_2011 = pd.DataFrame()\n", "data_soaf_2012 = pd.DataFrame()\n", "data_soaf_2013 = pd.DataFrame()\n", "data_soaf_2014 = pd.DataFrame()\n", "data_soaf_2015 = pd.DataFrame()\n", "\n", "# 2011\n", "filepath_entsoe_soaf = downloadandcache(url_entsoe_soaf2011, 'SO_AF_2011_-_2025_.zip')\n", "with zipfile.ZipFile(filepath_entsoe_soaf, \"r\") as O:\n", " O.extractall('download')\n", "\n", "xlsx = pd.ExcelFile(os.path.join('download', 'SO&AF 2011 - 2025 Scenario B.xls'))\n", "list_sheets = xlsx.sheet_names\n", "\n", "for sheet in list_sheets:\n", " data_to_append = pd.read_excel(os.path.join('download', 'SO&AF 2011 - 2025 Scenario B.xls'),\n", " sheet_name=str(sheet), skiprows=11, header=[0], index_col=0)\n", " data_to_append = data_to_append[[2011]]\n", " data_to_append['country'] = str(sheet[0:2])\n", " data_to_append = data_to_append.set_index(['country'], append=True)\n", " data_soaf_2011 = data_soaf_2011.append(data_to_append)\n", "\n", "# 2012\n", "filepath_entsoe_soaf = downloadandcache(url_entsoe_soaf2012, '120705_SOAF_2012_Dataset.zip')\n", "with zipfile.ZipFile(filepath_entsoe_soaf, \"r\") as O:\n", " O.extractall('download')\n", "\n", "xlsx = pd.ExcelFile(os.path.join('download', 'SOAF 2012 Scenario B.xls'))\n", "list_sheets = xlsx.sheet_names\n", "\n", "for sheet in list_sheets:\n", " data_to_append = pd.read_excel(os.path.join('download', 'SOAF 2012 Scenario B.xls'),\n", " sheet_name=str(sheet), skiprows=11, header=[0], index_col=0)\n", " data_to_append = data_to_append[[2012]]\n", " data_to_append['country'] = str(sheet[0:2])\n", " data_to_append = data_to_append.set_index(['country'], append=True)\n", " data_soaf_2012 = data_soaf_2012.append(data_to_append)\n", "\n", "# 2013\n", "filepath_entsoe_soaf = downloadandcache(url_entsoe_soaf2013, '130403_SOAF_2013-2030_dataset.zip')\n", "with zipfile.ZipFile(filepath_entsoe_soaf, \"r\") as O:\n", " O.extractall('download')\n", "\n", "xlsx = pd.ExcelFile(os.path.join('download', 'ScB.xls'))\n", "list_sheets = xlsx.sheet_names\n", "\n", "for sheet in list_sheets:\n", " data_to_append = pd.read_excel(os.path.join('download', 'ScB.xls'),\n", " sheet_name=str(sheet), skiprows=11, header=[0], index_col=0)\n", " data_to_append = data_to_append[[2013]]\n", " data_to_append['country'] = str(sheet[0:2])\n", " data_to_append = data_to_append.set_index(['country'], append=True)\n", " data_soaf_2013 = data_soaf_2013.append(data_to_append)\n", "\n", "# 2014\n", "filepath_entsoe_soaf = downloadandcache(url_entsoe_soaf2014, '140602_SOAF%202014_dataset.zip')\n", "with zipfile.ZipFile(filepath_entsoe_soaf, \"r\") as O:\n", " O.extractall('download')\n", "\n", "xlsx = pd.ExcelFile(os.path.join('download', 'ScB.xlsx'))\n", "list_sheets = xlsx.sheet_names\n", "\n", "for sheet in list_sheets:\n", " data_to_append = pd.read_excel(os.path.join('download', 'ScB.xlsx'),\n", " sheet_name=str(sheet), skiprows=11, header=[0], index_col=0)\n", " data_to_append = data_to_append[[2014, 2015]]\n", " data_to_append['country'] = str(sheet[0:2])\n", " data_to_append = data_to_append.set_index(['country'], append=True)\n", " data_soaf_2014 = data_soaf_2014.append(data_to_append)\n", "\n", "# 2015\n", "filepath_entsoe_soaf = downloadandcache(url_entsoe_soaf2015, 'SO_AF_2015_dataset.zip')\n", "with zipfile.ZipFile(filepath_entsoe_soaf, \"r\") as O:\n", " O.extractall('download')\n", "\n", "xlsx = pd.ExcelFile(os.path.join('download', 'SO&AF 2015 dataset', 'ScB_publication.xlsx'))\n", "list_sheets = xlsx.sheet_names\n", "\n", "for sheet in list_sheets:\n", " data_to_append = pd.read_excel(os.path.join('download', 'SO&AF 2015 dataset', 'ScB_publication.xlsx'),\n", " sheet_name=str(sheet), skiprows=11, header=[0], index_col=0)\n", " data_to_append = data_to_append[[2016]]\n", " data_to_append['country'] = str(sheet[0:2])\n", " data_to_append = data_to_append.set_index(['country'], append=True)\n", " data_soaf_2015 = data_soaf_2015.append(data_to_append)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_soaf = pd.concat([data_soaf_2011,data_soaf_2012,data_soaf_2013,data_soaf_2014,data_soaf_2015], axis=1)\n", "data_soaf.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 4. Data processing" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the following section, the different data sources are standardized and combined to a single data set." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4.1 Manually compiled dataset" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The initial raw data is given as a cross-table format. To ensure the compatibility of standard data formats, we convert the initial crosstab format of the input data to a list." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Reshape dataframe to list\n", "data_opsd = pd.DataFrame(data_raw.stack(level=['source', 'source_type', 'year',\n", " 'type', 'country',\n", " 'capacity_definition']))\n", "\n", "# Reset index for dataframe\n", "data_opsd = data_opsd.reset_index()\n", "data_opsd['technology'] = data_opsd['technology'].str.replace('- ', '')\n", "data_opsd = data_opsd.set_index('technology')\n", "\n", "# Delete entries with missing source\n", "data_opsd = data_opsd[data_opsd['source'].isnull() == False]\n", "data_opsd = data_opsd[data_opsd['source'] != 0]\n", "\n", "# Delete entries from EUROSTAT and entsoe as they will be directly used from original sources\n", "data_opsd = data_opsd[data_opsd['source'] != 'EUROSTAT']\n", "data_opsd = data_opsd[data_opsd['source'] != 'entsoe']\n", "\n", "\n", "data_opsd = data_opsd.rename(columns={0: 'capacity'})\n", "\n", "data_opsd['capacity'] = pd.to_numeric(data_opsd['capacity'], errors='coerce')\n", "\n", "data_opsd = data_opsd.reset_index()\n", "\n", "# For some source, permission to publish data\n", "data_opsd.loc[(data_opsd['source'] == 'ELIA'),\n", " 'comment'] = 'data available, but cannot be provided'\n", "data_opsd.loc[(data_opsd['source'] == 'BMWi'),\n", " 'comment'] = 'data available, but cannot be provided'\n", "data_opsd.loc[(data_opsd['source'] == 'Mavir'),\n", " 'comment'] = 'data available, but cannot be provided'\n", "\n", "data_opsd.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4.2 EUROSTAT data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.2.1 Convert cross-table format to list" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_eurostat = pd.melt(data_eurostat, id_vars=['unit', 'product',\n", " 'indic_nrg', 'geo\\\\time',\n", " 'source', 'source_type', 'type'],\n", " var_name='year', value_name='value')\n", "\n", "data_eurostat.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.2.2 Read definition table for energy sources and merge with dataset" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The classification of generation capacities in the EUROSTAT dataset is specified in [Regulation (EC) No 1099/2008](http://eur-lex.europa.eu/legal-content/EN/TXT/PDF/?uri=CELEX:32008R1099&from=EN) (Annex B, 3.3). The available EUROSTAT dataset [nrg_113a](http://ec.europa.eu/eurostat/product?code=nrg_113a&mode=view) covers the following indicators:\n", "\n", "|indic_nrg | Description | Technology in OPSD |\n", "|---|---|---|\n", "|**12_1176011**|\t**Electrical capacity, main activity producers - Combustible Fuels**|\tFossil fuels & bioenergy|\n", "|**12_1176012**|\t**Electrical capacity, autoproducers - Combustible Fuels**|\tFossil fuels & bioenergy|\n", "|*12_1176061*|\t*Electrical capacity, main activity producers - Mixed plants*|\t|\n", "|*12_1176101*|\t*Electrical capacity, main activity producers - Other Sources*|\t|\n", "|*12_1176102*|\t*Electrical capacity, autoproducers - Other Sources*|\t|\n", "|*12_1176111*|\t*Electrical capacity, main activity producers - Steam*||\n", "|*12_1176112*|\t*Electrical capacity, autoproducers - Steam*||\n", "|*12_1176121*|\t*Electrical capacity, main activity producers - Gas Turbine*||\n", "|*12_1176122*|\t*Electrical capacity, autoproducers - Gas Turbine*||\n", "|*12_1176131*|\t*Electrical capacity, main activity producers - Combined Cycle*||\n", "|*12_1176132*|\t*Electrical capacity, autoproducers - Combined Cycle*||\n", "|*12_1176141*|\t*Electrical capacity, main activity producers - Internal Combustion*||\n", "|*12_1176142*|\t*Electrical capacity, autoproducers - Internal Combustion*||\n", "|*12_1176401*|\t*Electrical capacity, main activity producers - Other Type of Generation*|\t|\n", "|*12_1176402*|\t*Electrical capacity, autoproducers - Other Type of Generation*|\t|\n", "|12_1176253|\tNet maximum capacity - Municipal Wastes|\tNon-renewable waste|\n", "|12_1176263|\tNet maximum capacity - Wood/Wood Wastes/Other Solid Wastes|\tOther bioenergy and renewable waste|\n", "|12_1176273|\tNet maximum capacity - Biogases|\tBiomass and biogas|\n", "|12_1176283|\tNet maximum capacity - Industrial Wastes (non-renewable)|\tNon-renewable waste|\n", "|12_1176343|\tNet maximum capacity - Liquid Biofuels|\tBiomass and biogas|\n", "|**12_1176031**|\t**Electrical capacity, main activity producers - Nuclear**|\tNuclear|\n", "|**12_1176032**|\t**Electrical capacity, autoproducers - Nuclear**|\tNuclear|\n", "|**12_1176051**|\t**Electrical capacity, main activity producers - Hydro**|\tHydro|\n", "|**12_1176052**|\t**Electrical capacity, autoproducers - Hydro**|\tHydro|\n", "|12_1176071|\tNet electrical capacity, main activity producers - Pure Pumped Hydro|\tPumped storage|\n", "|12_1176072|\tNet electrical capacity, autoproducers - Pure Pumped Hydro|\tPumped storage|\n", "|*12_117615*|\t*Net maximum capacity - Hydro <1 MW*|\t|\n", "|*12_117616*|\t*Net maximum capacity - Hydro >= 1 MW and <= 10 MW*|\t|\n", "|*12_117617*|\t*Net maximum capacity - Hydro 10 MW and over*|\t|\n", "|**12_1176301**|\t**Electrical capacity, main activity producers - Tide, wave and ocean**|\tMarine|\n", "|**12_1176302**|\t**Electrical capacity, autoproducers - Tide, wave and ocean**|\tMarine|\n", "|*12_1176303*|\t*Net maximum capacity - Tide, Wave, Ocean*||\n", "|**12_1176081**|\t**Electrical capacity, main activity producers - Geothermal**|\tGeothermal|\n", "|**12_1176082**|\t**Electrical capacity, autoproducers - Geothermal**|\tGeothermal|\n", "|*12_1176083*|\t*Net maximum capacity - Geothermal*|\t|\n", "|**12_1176091**|\t**Electrical capacity, main activity producers - Wind**|\tWind|\n", "|**12_1176092**|\t**Electrical capacity, autoproducers - Wind**|\tWind|\n", "|**12_1176233**|\t**Net maximum capacity - Solar Photovoltaic**|\tPhotovoltaics|\n", "|**12_1176243**|\t**Net maximum capacity - Solar Thermal Electric**|\tConcentrated solar power|\n", "\n", "**Bold** rows indicate top level classes within the EUROSTAT classification, whereas normal and *italic* rows cover different kinds of subclassifications. Especially within the top level 'Combustible fuels' different kinds of subcategorizations based on fuel or technology are available. Simarily, 'Hydro' is differentiated by type (e.g. pumped-hydro storage) or capacity classes. *Italic* rows are not further considered within the OPSD dataset due to the mismatch with existing technology classes." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_definition = pd.read_csv(os.path.join('input', 'definition_EUROSTAT_indic.txt'),\n", " header=None,\n", " names=['indic', 'description',\n", " 'energy source'],\n", " sep='\\t')\n", "\n", "data_eurostat = data_eurostat.merge(data_definition,\n", " how='left',\n", " left_on='indic_nrg',\n", " right_on='indic')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_eurostat = data_eurostat[data_eurostat['energy source'].isnull() == False]\n", "\n", "data_eurostat['value'] = data_eurostat['value'].astype(str)\n", "data_eurostat['value'], data_eurostat['comment'] = data_eurostat.value.str.split(' ', 1).str\n", "\n", "data_eurostat['value'] = data_eurostat['value'].replace(':', np.nan)\n", "data_eurostat.loc[data_eurostat['value'].isnull() == True,\n", " 'comment'] = 'not available'\n", "\n", "data_eurostat['year'] = data_eurostat['year'].astype(int)\n", "data_eurostat['value'] = data_eurostat['value'].astype(float)\n", "\n", "data_eurostat.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.2.3 Adjust EUROSTAT data to OPSD data format" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the following, we adjust the EUROSTAT dataset to the OPSD data format. The necessary steps are:\n", "1. Rename, delete columns aligned with OPSD dataset,\n", "2. Deselect aggregated countries not contained in the OPSD dataset,\n", "3. Aggregate entries based on energy source, year and country." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_eurostat = data_eurostat.drop(['unit', 'product', 'indic_nrg',\n", " 'indic', 'description'], axis=1)\n", "\n", "data_eurostat = data_eurostat.rename(columns={'geo\\\\time': 'country',\n", " 'energy source': 'technology',\n", " 'value': 'capacity'})\n", "\n", "data_eurostat.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_eurostat['country'] = data_eurostat['country'].replace('UK', 'GB')\n", "data_eurostat['country'] = data_eurostat['country'].replace('EL', 'GR')\n", "\n", "# Limit countries to OPSD countries (if required)\n", "#countries = data_opsd.country.unique()\n", "#data_eurostat = data_eurostat[data_eurostat['country'].isin(countries)]\n", "\n", "# Consider only countries and no aggregates, like EU28 or EA19\n", "data_eurostat = data_eurostat[data_eurostat['country'].apply(len) == 2]\n", "\n", "\n", "data_eurostat.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_eurostat = pd.DataFrame(data_eurostat.groupby(['technology', 'source',\n", " 'source_type', 'year',\n", " 'type', 'country'])\n", " ['capacity'].sum())\n", "\n", "data_eurostat.loc[data_eurostat['capacity'].isnull() == True,\n", " 'comment'] = 'not available'\n", "data_eurostat['comment'] = data_eurostat['comment'].fillna('').astype(str)\n", "data_eurostat = data_eurostat.reset_index()\n", "\n", "data_eurostat.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.2.4 Determine aggregated values" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The categorization of technologies within the OPSD data format requires the specification of aggregates and/or subcategories to ensure consistency of the dataset on each technology level." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.2.4.1 Technology and fuel level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "country_list = data_eurostat.country.unique()\n", "year_list = data_eurostat.year.unique()\n", "\n", "table_to_append = pd.DataFrame(columns=['country', 'year', 'technology', 'capacity'])\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "for country in country_list:\n", " for year in year_list:\n", "\n", " col_list = ['country', 'year', 'technology', 'capacity']\n", " data_selected = data_eurostat.loc[(data_eurostat['country'] == country)\n", " & (data_eurostat['year'] == int(year))].copy()\n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", "\n", " # Solar\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Photovoltaics',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Concentrated solar power',\n", " 'capacity'].values[0]\n", " val = val_1 + val_2\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized solar',\n", " 0]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Solar',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " # Wind\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Wind',\n", " 'capacity'].values[0]\n", " val = val_1\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized wind',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " # Hydro\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Hydro',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Pumped storage',\n", " 'capacity'].values[0]\n", " val = val_1 - val_2\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized hydro',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " # Bioenergy and renewable waste ### Sum of 'Biomass and biogas' and 'Other bioenergy[...]'. No 'Sewage and landfill'\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Biomass and biogas',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Other bioenergy and renewable waste',\n", " 'capacity'].values[0]\n", " val = val_1 + val_2\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Bioenergy and renewable waste',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " # Fossil fuels\n", " # The EUROSTAT definition of combustible fuels includes also biomass, which needs to be\n", " # separated within the OPSD categories.\n", "\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Fossil fuels',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Biomass and biogas',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Other bioenergy and renewable waste',\n", " 'capacity'].values[0]\n", " val = val_1 - val_2 - val_3\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Fossil fuels',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " val_4 = data_selected.loc[data_selected['technology'] == 'Non-renewable waste',\n", " 'capacity'].values[0]\n", " val = val_1 - val_2 - val_3 - val_4\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized fossil fuels',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", "\n", "# Drop 'Fossil Fuels' which still include 'Biomass and biogas'\n", "data_eurostat = data_eurostat[data_eurostat.technology != 'Fossil fuels']\n", "\n", "# Append OPSD-conform data to original frame with corrected 'Fossil fuels' value\n", "data_eurostat = data_eurostat.append(table_to_append, sort=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.2.4.2 Fuel type level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "table_to_append = pd.DataFrame(columns=['country', 'year', 'technology', 'capacity'])\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "for country in country_list:\n", " for year in year_list:\n", "\n", " col_list = ['country', 'year', 'technology', 'capacity']\n", " data_selected = data_eurostat.loc[(data_eurostat['country'] == country)\n", " & (data_eurostat['year'] == int(year))].copy()\n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", "\n", " # Sum Renewable energy sources\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Hydro',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Wind',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Solar',\n", " 'capacity'].values[0]\n", " val_4 = data_selected.loc[data_selected['technology'] == 'Geothermal',\n", " 'capacity'].values[0]\n", " val_5 = data_selected.loc[data_selected['technology'] == 'Marine',\n", " 'capacity'].values[0]\n", " val_6 = data_selected.loc[data_selected['technology'] == 'Bioenergy and renewable waste',\n", " 'capacity'].values[0]\n", " val = val_1 + val_2 + val_3 + val_4 + val_5 + val_6\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Renewable energy sources',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", "# Append to data frame\n", "data_eurostat = data_eurostat.append(table_to_append, sort=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.2.4.3 Total level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "table_to_append = pd.DataFrame(columns=['country', 'year', 'technology', 'capacity'])\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "for country in country_list:\n", " for year in year_list:\n", "\n", " col_list = ['country', 'year', 'technology', 'capacity']\n", " data_selected = data_eurostat.loc[(data_eurostat['country'] == country)\n", " & (data_eurostat['year'] == int(year))].copy()\n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", "\n", " # Sum Renewable energy sources\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Fossil fuels',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Nuclear',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Renewable energy sources',\n", " 'capacity'].values[0]\n", " val = val_1 + val_2 + val_3\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Total',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", "data_eurostat = data_eurostat.append(table_to_append, sort=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.2.5 Add additional information" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_eurostat['source'] = 'EUROSTAT'\n", "data_eurostat['source_type'] = 'Statistical Office'\n", "data_eurostat['capacity_definition'] = 'Unknown'\n", "data_eurostat['type'] = 'Installed capacity in MW'\n", "\n", "data_eurostat.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4.3 ENTSO-E statistical data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.3.1 Align table structure to OPSD" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "data_entsoe = pd.melt(data_entsoe_raw,\n", " id_vars=['country', 'year', 'representativity'],\n", " var_name='energy_source',\n", " value_name='value')\n", "\n", "\n", "data_entsoe = data_entsoe.rename(columns={'energy_source': 'technology',\n", " 'value': 'capacity',\n", " 'representativity': 'comment'})\n", "\n", "data_entsoe['country'] = data_entsoe['country'].replace('NI', 'GB')\n", "\n", "\n", "# Limit countries to OPSD countries (if required)\n", "#countries = data_opsd.country.unique()\n", "#data_entsoe = data_entsoe[data_entsoe['country'].isin(countries)]\n", "\n", "data_entsoe = pd.DataFrame(data_entsoe.groupby(['country', 'year',\n", " 'technology'])\n", " ['capacity'].sum())\n", "data_entsoe = data_entsoe.reset_index()\n", "\n", "data_entsoe['source'] = 'entsoe Statistics'\n", "data_entsoe['source_type'] = 'Other association'\n", "data_entsoe['capacity_definition'] = 'Net capacity'\n", "data_entsoe['type'] = 'Installed capacity in MW'\n", "\n", "data_entsoe.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.3.2 Standardize definition of energy sources/technologies" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dict_energy_source = {'hydro': 'Hydro',\n", " 'of which storage': 'Reservoir',\n", " 'of which run of river': 'Run-of-river',\n", " 'of which pumped storage': 'Pumped storage',\n", " 'nuclear': 'Nuclear',\n", " #'renewable': 'renewable', # auxiliary definition, will be deleted at a later stage\n", " 'of which wind': 'Wind',\n", " 'of which solar': 'Solar',\n", " 'of which biomass': 'Biomass and biogas',\n", " 'fossil_fuels': 'Fossil fuels',\n", " 'others': 'Other or unspecified energy sources',\n", " ' ': 'NaN'}\n", "data_entsoe[\"technology\"].replace(dict_energy_source, inplace=True)\n", "data_entsoe[\"technology\"].replace('NaN', np.nan, inplace=True)\n", "\n", "data_entsoe = data_entsoe[data_entsoe['technology'].isnull() == False]\n", "\n", "data_entsoe.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.3.3 Determine aggregated values" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.3.3.1 Technology and fuel level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "country_list = data_entsoe.country.unique()\n", "year_list = data_entsoe.year.unique()\n", "col_list = ['country', 'year', 'technology', 'capacity']\n", "tech_list = data_entsoe['technology'].unique()\n", "\n", "table_to_append = pd.DataFrame(columns=col_list)\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "for country in country_list:\n", " for year in year_list:\n", " \n", " data_selected = data_entsoe.loc[(data_entsoe['country'] == country)\n", " & (data_entsoe['year'] == int(year))].copy()\n", " \n", " if len(data_selected.index) > 0:\n", " \n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", "\n", "\n", " # Solar\n", " #val_1 = data_selected.loc[data_selected['technology'] == 'Solar',\n", " # 'capacity'].values[0]\n", " val = checkIfEmptyAndSetDefault(data_selected, 'Solar')\n", " \n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized solar',\n", " val]], columns=col_list)\n", " \n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " # Wind\n", " #val_1 = data_selected.loc[data_selected['technology'] == 'Wind',\n", " # 'capacity'].values[0]\n", " \n", " val = checkIfEmptyAndSetDefault(data_selected, 'Wind')\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized wind',\n", " val]], columns=col_list)\n", " \n", " table_to_append = table_to_append.append(row_to_append)\n", " \n", " val_1 = checkIfEmptyAndSetDefault(data_selected, 'Hydro')\n", " val_2 = checkIfEmptyAndSetDefault(data_selected, 'Run-of-river')\n", " val_3 = checkIfEmptyAndSetDefault(data_selected, 'Reservoir')\n", " val_4 = checkIfEmptyAndSetDefault(data_selected, 'Pumped storage')\n", " \n", " val = val_1 - val_2 - val_3 - val_4\n", " \n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized hydro',\n", " val]], columns=col_list)\n", " \n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " # Biomass\n", " #val_1 = data_selected.loc[data_selected['technology'] == 'Biomass and biogas',\n", " # 'capacity'].values[0]\n", " val = checkIfEmptyAndSetDefault(data_selected, 'Biomass and biogas')\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Bioenergy and renewable waste',\n", " val]], columns=col_list)\n", " \n", " table_to_append = table_to_append.append(row_to_append)\n", " \n", " val_1 = checkIfEmptyAndSetDefault(data_selected, 'renewable')\n", " val_2 = checkIfEmptyAndSetDefault(data_selected, 'Wind')\n", " val_3 = checkIfEmptyAndSetDefault(data_selected, 'Solar')\n", " val_4 = checkIfEmptyAndSetDefault(data_selected, 'Biomass and biogas')\n", " \n", " val = val_1 - val_2 - val_3 - val_4\n", " \n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized renewable energy sources',\n", " val]], columns=col_list)\n", " \n", " table_to_append = table_to_append.append(row_to_append)\n", "\n", " # Differently categorized fossil fuels\n", " #val_1 = data_selected.loc[data_selected['technology'] == 'Fossil fuels',\n", " # 'capacity'].values[0]\n", " \n", " val = checkIfEmptyAndSetDefault(data_selected, 'Fossil fuels')\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized fossil fuels',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", " \n", " else:\n", " print('Skipping combination: ', country, year)\n", "\n", "data_entsoe = data_entsoe.append(table_to_append, sort=True)\n", "\n", "# Skip auxiliary technology class\n", "data_entsoe = data_entsoe[data_entsoe['technology'] != 'renewable']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.3.3.2 Fuel type level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "table_to_append = pd.DataFrame(columns=col_list)\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "\n", "for country in country_list:\n", " for year in year_list:\n", "\n", " \n", " data_selected = data_entsoe.loc[(data_entsoe['country'] == country)\n", " & (data_entsoe['year'] == int(year))].copy()\n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", " \n", " if len(data_selected.index) > 0:\n", "\n", " val_1 = checkIfEmptyAndSetDefault(data_selected, 'Solar')\n", " val_2 = checkIfEmptyAndSetDefault(data_selected, 'Wind')\n", " val_3 = checkIfEmptyAndSetDefault(data_selected, 'Hydro')\n", " val_4 = checkIfEmptyAndSetDefault(data_selected, 'Bioenergy and renewable waste')\n", " val_5 = checkIfEmptyAndSetDefault(data_selected, 'Differently categorized renewable energy sources')\n", " \n", " val = val_1 + val_2 + val_3 + val_4 + val_5\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Renewable energy sources',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", " \n", " else:\n", " print('Skipping combination: ', country, year)\n", "\n", "data_entsoe = data_entsoe.append(table_to_append, sort=True)\n", "\n", "data_entsoe.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.3.3.3 Total level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "table_to_append = pd.DataFrame(columns=['country', 'year', 'technology', 'capacity'])\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "\n", "for country in country_list:\n", " for year in year_list:\n", "\n", " data_selected = data_entsoe.loc[(data_entsoe['country'] == country)\n", " & (data_entsoe['year'] == int(year))].copy()\n", " \n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", " \n", " if len(data_selected.index) > 0:\n", " \n", "\n", " # Total capacity \n", " val_1 = checkIfEmptyAndSetDefault(data_selected, 'Renewable energy sources')\n", " val_2 = checkIfEmptyAndSetDefault(data_selected, 'Nuclear') \n", " val_3 = checkIfEmptyAndSetDefault(data_selected, 'Fossil fuels') \n", " val_4 = checkIfEmptyAndSetDefault(data_selected, 'Other or unspecified energy sources')\n", " \n", " val = val_1 + val_2 + val_3 + val_4\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Total',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append)\n", " \n", " else:\n", " print('Skipping combination: ', country, year)\n", "\n", "data_entsoe = data_entsoe.append(table_to_append, sort=True)\n", "\n", "data_entsoe.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.3.4 Add additional information" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_entsoe['source'] = 'entsoe Statistics'\n", "data_entsoe['source_type'] = 'Other association'\n", "data_entsoe['capacity_definition'] = 'Net capacity'\n", "data_entsoe['type'] = 'Installed capacity in MW'\n", "\n", "data_entsoe.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4.4 ENTSO-E SO&AF data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.4.1 Align table structure to OPSD" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_soaf = data_soaf.reset_index()\n", "data_soaf = data_soaf.rename(columns={'level_0': 'technology',\n", " 'level_1': 'country'})\n", "\n", "data_soaf = pd.melt(data_soaf,\n", " id_vars=['technology', 'country'],\n", " var_name='year',\n", " value_name='capacity')\n", "\n", "data_soaf['country'] = data_soaf['country'].replace('NI', 'GB')\n", "\n", "# Limit countries to OPSD countries (if required)\n", "data_soaf = pd.DataFrame(data_soaf.groupby(['country', 'year',\n", " 'technology'])['capacity'].sum())\n", "\n", "data_soaf = data_soaf.reset_index()\n", "\n", "data_soaf.loc[:, 'capacity'] *= 1000\n", "data_soaf['source'] = 'entsoe SOAF'\n", "data_soaf['source_type'] = 'Other association'\n", "data_soaf['capacity_definition'] = 'Net capacity'\n", "data_soaf['type'] = 'Installed capacity in MW'\n", "\n", "data_soaf.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.4.2 Standardize definition of energy sources/technologies" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dict_technologies = {'Nuclear Power': 'Nuclear',\n", " 'Fossil Fuels': 'Fossil fuels',\n", " 'Hard Coal': 'Hard coal',\n", " 'Lignite': 'Lignite',\n", " 'Gas': 'Natural gas',\n", " 'Oil': 'Oil',\n", " 'Mixed Fuels': 'Mixed fossil fuels',\n", " 'Hydro power (total)': 'Hydro',\n", " 'of which renewable hydro generation': 'NaN',\n", " 'of which run-of-river (pre-dominantly)': 'Run-of-river',\n", " 'of which storage and pumped storage (total)': 'Reservoir including pumped storage', # auxiliary class definition\n", " 'Renewable Energy Sources (other than hydro)': 'renewable',\n", " 'Solar': 'Solar',\n", " 'Wind': 'Wind',\n", " 'of which offshore': 'Offshore',\n", " 'of which onshore': 'Onshore',\n", " 'Biomass': 'Biomass and biogas',\n", " 'Not Clearly Identifiable Energy Sources': 'Other or unspecified energy sources',\n", " 'Net generating Capacity': 'NaN',\n", " 'Import Capacity': 'NaN',\n", " 'Export Capacity': 'NaN',\n", " 'Load': 'NaN',\n", " 'Load Management': 'NaN',\n", " 'Maintenance and Overhauls': 'NaN',\n", " 'Margin Against Seasonal Peak Load': 'NaN',\n", " 'Adequacy Reference Margin': 'NaN',\n", " 'National Power Data': 'NaN',\n", " 'Non-Usable Capacity': 'NaN',\n", " 'Outages': 'NaN',\n", " 'Reliable Available Capacity': 'NaN',\n", " 'Remaining Capacity': 'NaN',\n", " 'Spare Capacity': 'NaN',\n", " 'System Service Reserve': 'NaN',\n", " 'Unavailable Capacity': 'NaN',\n", " 'Simultaneous Exportable Capacity for Adequacy': 'NaN',\n", " 'Simultaneous Importable Capacity for Adequacy': 'NaN',\n", " '“The values of Simultaneous Importable/Exportable Capacity for Adequacy do not include the border with Austria as there is a common market between Germany and Austria for which no NTC exists.”': 'NaN'}\n", "\n", "data_soaf['technology'].replace(dict_technologies, inplace=True)\n", "data_soaf['technology'].replace('NaN', np.nan, inplace=True)\n", "data_soaf = data_soaf[data_soaf['technology'].isnull() == False]\n", "\n", "data_soaf.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.4.3 Determine aggregated values" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.4.3.1 Technology and fuel level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "country_list = data_soaf.country.unique()\n", "year_list = data_soaf.year.unique()\n", "\n", "table_to_append = pd.DataFrame(columns=['country', 'year', 'technology', 'capacity'])\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "for country in country_list:\n", " for year in year_list:\n", "\n", " col_list = ['country', 'year', 'technology', 'capacity']\n", " data_selected = data_soaf.loc[(data_soaf['country'] == country)\n", " & (data_soaf['year'] == int(year))].copy()\n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", "\n", " # Solar\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Solar',\n", " 'capacity'].values[0]\n", " val = val_1\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized solar',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append, sort=True)\n", "\n", " # Wind\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Wind',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Offshore',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Onshore',\n", " 'capacity'].values[0]\n", " val = val_1 - val_2 - val_3\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized wind',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append, sort=True)\n", "\n", " # Hydro\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Hydro',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Run-of-river',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Reservoir including pumped storage',\n", " 'capacity'].values[0]\n", " val = val_1 - val_2 - val_3\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized hydro',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append, sort=True)\n", "\n", " # Biomass\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Biomass and biogas',\n", " 'capacity'].values[0]\n", " val = val_1\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Bioenergy and renewable waste',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append, sort=True)\n", "\n", " # Differently categorized renewable energy sources\n", " val_1 = data_selected.loc[data_selected['technology'] == 'renewable',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Wind',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Solar',\n", " 'capacity'].values[0]\n", " val_4 = data_selected.loc[data_selected['technology'] == 'Biomass and biogas',\n", " 'capacity'].values[0]\n", " val = val_1 - val_2 - val_3 - val_4\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized renewable energy sources',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append, sort=True)\n", "\n", " # Fossil fuels\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Fossil fuels',\n", " 'capacity'].values[0]\n", "\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Lignite',\n", " 'capacity'].values[0]\n", "\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Hard coal',\n", " 'capacity'].values[0]\n", "\n", " val_4 = data_selected.loc[data_selected['technology'] == 'Oil',\n", " 'capacity'].values[0]\n", "\n", " val_5 = data_selected.loc[data_selected['technology'] == 'Natural gas',\n", " 'capacity'].values[0]\n", "\n", " val_6 = data_selected.loc[data_selected['technology'] == 'Mixed fossil fuels',\n", " 'capacity'].values[0]\n", "\n", " val = val_1 - val_2 - val_3 - val_4 - val_5 - val_6\n", "\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Differently categorized fossil fuels',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append,sort=True)\n", "\n", "data_soaf = data_soaf.append(table_to_append, sort=True)\n", "\n", "# Skip auxiliary technology class\n", "data_soaf = data_soaf[data_soaf['technology'] != 'renewable']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 4.4.3.2 Fuel type and total level" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "table_to_append = pd.DataFrame(columns=['country', 'year', 'technology', 'capacity'])\n", "table_to_append['year'] = table_to_append['year'].astype(int)\n", "table_to_append['capacity'] = table_to_append['capacity'].astype(float)\n", "for country in country_list:\n", " for year in year_list:\n", "\n", " col_list = ['country', 'year', 'technology', 'capacity']\n", " data_selected = data_soaf.loc[(data_soaf['country'] == country)\n", " & (data_soaf['year'] == int(year))].copy()\n", " data_selected.loc[data_selected['capacity'].isnull() == True, 'capacity'] = 0\n", "\n", " # Renewable energy sources\n", " val_1 = data_selected.loc[data_selected['technology'] == 'Solar',\n", " 'capacity'].values[0]\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Wind',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Hydro',\n", " 'capacity'].values[0]\n", " val_4 = data_selected.loc[data_selected['technology'] == 'Bioenergy and renewable waste',\n", " 'capacity'].values[0]\n", " val_5 = data_selected.loc[data_selected['technology'] == 'Differently categorized renewable energy sources',\n", " 'capacity'].values[0]\n", " val = val_1 + val_2 + val_3 + val_4 + val_5\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Renewable energy sources',\n", " val]], columns=col_list)\n", " table_to_append = table_to_append.append(row_to_append, sort=True)\n", "\n", " # Total capacity\n", " val_1 = val # renewable energy sources\n", " val_2 = data_selected.loc[data_selected['technology'] == 'Fossil fuels',\n", " 'capacity'].values[0]\n", " val_3 = data_selected.loc[data_selected['technology'] == 'Nuclear',\n", " 'capacity'].values[0]\n", " val_4 = data_selected.loc[data_selected['technology'] == 'Other or unspecified energy sources',\n", " 'capacity'].values[0]\n", " val = val_1 + val_2 + val_3 + val_4\n", " row_to_append = pd.DataFrame([[country,\n", " int(year),\n", " 'Total',\n", " val]], columns=col_list)\n", " \n", " table_to_append = table_to_append.append(row_to_append, sort=True)\n", "\n", "\n", "data_soaf = data_soaf.append(table_to_append, sort=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4.4.4 Add additional information" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_soaf['source'] = 'entsoe SO&AF'\n", "data_soaf['source_type'] = 'Other association'\n", "data_soaf['capacity_definition'] = 'Net capacity'\n", "data_soaf['type'] = 'Installed capacity in MW'\n", "\n", "data_soaf.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4.5 Merge data sources" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data = pd.concat([data_opsd, data_eurostat, data_soaf, data_entsoe], sort=False)\n", "data = data.reset_index(drop=True)\n", "\n", "# Define data types\n", "data['comment'] = data['comment'].fillna('').astype(str)\n", "data = data.astype(str)\n", "data['capacity'] = data['capacity'].astype(float)\n", "data['year'] = data['year'].astype(int)\n", "\n", "# Sort columns\n", "data = data[['technology', 'source', 'source_type',\n", " 'year', 'type', 'country',\n", " 'capacity_definition', 'capacity', 'comment']]\n", "\n", "data.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 5. Implementation of energy source levels" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Due to varying categorizations in the country-specific raw data, a revised categorization of energy sources and conversion technologies is applied with the aim to reflect the individual categorization of the different national references at the best. We specify the following four different energy source levels, which can be seen as general levels of the classification:\n", "\n", "- **Energy source level 0** - Total generation capacity\n", "- **Energy source level 1** - Generation capacity by energy type (fossil, nuclear, renewable, other)\n", "- **Energy source level 2** - Generation capacity by fuel or energy source (e.g. coal, lignite, hard coal, natural gas, wind)\n", "- **Energy source level 3** - Generation capacity by fuel or energy source and detailed fuel for bioenergy\n", "- **Technology level** - Generation capacity by fuel and technology (e.g. combined cycle natural gas, gas turbine, onshore wind, offshore wind)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Drop first rows\n", "energylevels = energylevels_raw[5:]\n", "\n", "energylevels.columns = energylevels[:1].values[0]\n", "\n", "# Reset index for Dataframe\n", "energylevels = energylevels.reset_index()\n", "energylevels['technology'] = pd.Series(energylevels['technology'].values.flatten()).str.replace('- ', '')\n", "energylevels = energylevels.set_index('technology')\n", "\n", "# Rename column headings\n", "energylevels.rename(columns={'Level 0': 'energy_source_level_0',\n", " 'Level 1': 'energy_source_level_1',\n", " 'Level 2': 'energy_source_level_2',\n", " 'Level 3': 'energy_source_level_3',\n", " 'Technology level': 'technology_level'},\n", " inplace=True)\n", "\n", "# Convert 0/1 assignment to boolean\n", "energylevels = energylevels.replace({0: False, 1: True})\n", "\n", "# Apply technology levels to data by merging both DataFrames\n", "data = data.merge(energylevels,\n", " left_on='technology',\n", " right_index=True,\n", " how='left')\n", "\n", "data = data.reset_index(drop=True)\n", "\n", "data.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 6. Convert stacked data to crosstable format" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "data_crosstable = pd.pivot_table(data[['technology', 'source',\n", " 'source_type', 'year',\n", " 'type', 'country',\n", " 'capacity_definition',\n", " 'capacity']],\n", " index=['technology'],\n", " columns=['country', 'type', 'year',\n", " 'source', 'source_type',\n", " 'capacity_definition'],\n", " values='capacity')\n", "\n", "# Apply initial ordering of technologies\n", "data_crosstable = data_crosstable.reindex(technology_order)\n", "\n", "# Delete index naming\n", "data_crosstable.index.name = None\n", "data_crosstable.columns.names = ('Country (ISO code)',\n", " 'Type of data', 'Year',\n", " 'Source', 'Type of source',\n", " 'Capacity definition (net, gross, unknown)')\n", "\n", "# data_crosstable.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "energylevels_table = energylevels_raw\n", "\n", "energylevels_table.columns = pd.MultiIndex.from_arrays(energylevels_raw[:6].values,\n", " names=['country', 'type', 'year',\n", " 'source', 'source_type',\n", " 'capacity_definition'\n", " ])\n", "\n", "# Remove 3 rows which are already used as column names\n", "energylevels_table = energylevels_table[pd.notnull(energylevels_table.index)]\n", "\n", "energylevels_table = energylevels_table.reset_index()\n", "energylevels_table['technology'] = energylevels_table['technology'].str.replace('- ', '')\n", "energylevels_table = energylevels_table.set_index('technology')\n", "\n", "# Delete index naming\n", "energylevels_table.index.name = None\n", "energylevels_table.columns.names = ('Country (ISO code)',\n", " 'Description', None,\n", " None, None,\n", " 'Level')\n", "\n", "# energylevels_table.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 7. Write results to file" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "output_path = 'output'\n", "\n", "# Copy original source file to folder original_data\n", "shutil.copy2(os.path.join('input', data_file),\n", " os.path.join('output', 'original_data', data_file))\n", "\n", "# Write the result to file\n", "data.to_csv(os.path.join(output_path, 'national_generation_capacity_stacked.csv'),\n", " encoding='utf-8', index_label='ID')\n", "\n", "# Write the results to excel file\n", "data.to_excel(os.path.join(output_path, 'national_generation_capacity_stacked.xlsx'),\n", " sheet_name='output', index_label='ID')\n", "\n", "writer = pd.ExcelWriter(os.path.join(output_path, 'national_generation_capacity.xlsx'))\n", "data_crosstable.to_excel(writer, sheet_name='output')\n", "energylevels_table.to_excel(writer, sheet_name='technology levels')\n", "writer.save()\n", "\n", "# Write the results to sql database\n", "data.to_sql('national_generation_capacity_stacked',\n", " sqlite3.connect(os.path.join(output_path,\n", " 'national_generation_capacity.sqlite')),\n", " if_exists=\"replace\", index_label='ID')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 8. Formatting of Excel tables" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Formatting of the crosstable output file to improve readability\n", "wb1 = openpyxl.load_workbook(os.path.join(output_path,\n", " 'national_generation_capacity.xlsx'))\n", "wb2 = openpyxl.load_workbook(os.path.join('input',\n", " 'National_Generation_Capacities.xlsx'))\n", "\n", "ws1 = wb1['output']\n", "ws2 = wb1['technology levels']\n", "\n", "# Adjust colum width\n", "ws1.column_dimensions['A'].width = 50\n", "ws2.column_dimensions['A'].width = 50\n", "\n", "# Place additional notes based on input file\n", "ws3 = wb2['Additional notes']\n", "for col in range(1, 3):\n", " for row in range(1, 10):\n", " ws1.cell(row=row + 50,\n", " column=col).value = ws3.cell(row=row, column=col).value\n", " ws1.cell(row=51, column=1).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws1.cell(row=row + 51, column=1).font = Font(color=colors.BLACK,\n", " italic=True, bold=False)\n", "\n", "# Coloring and style of rows, alignment, as well as column width\n", "for col in range(1, 1500):\n", " ws1.column_dimensions[get_column_letter(col + 1)].width = 16\n", " for row in range(2, 7):\n", " ws1.cell(row=row, column=col).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=False)\n", " for row in range(8, 47):\n", " rgb = [242, 242, 242]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws1.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws1.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=True,\n", " bold=False)\n", " ws1.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=2)\n", " for row in [46]:\n", " rgb = [166, 166, 166]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws1.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws1.cell(row=row, column=col).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws1.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws1.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=0)\n", " for row in [8, 21, 22, 45]:\n", " rgb = [191, 191, 191]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws1.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws1.cell(row=row, column=col).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws1.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws1.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=0)\n", " for row in [9, 10, 11, 12, 17, 18, 19, 20, 23, 30, 34, 38, 39, 40, 44]:\n", " rgb = [217, 217, 217]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws1.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws1.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=False)\n", " ws1.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=1)\n", "for col in range(1, 7):\n", " ws2.column_dimensions[get_column_letter(col + 1)].width = 16\n", " for row in range(2, 7):\n", " ws2.cell(row=row, column=col).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=False)\n", " for row in range(8, 47):\n", " rgb = [242, 242, 242]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws2.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws2.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=True,\n", " bold=False)\n", " ws2.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=2)\n", " for row in [46]:\n", " rgb = [166, 166, 166]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws2.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws2.cell(row=row, column=col).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws2.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws2.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=0)\n", " for row in [8, 21, 22, 45]:\n", " rgb = [191, 191, 191]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws2.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws2.cell(row=row, column=col).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws2.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=True)\n", " ws2.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=0)\n", " for row in [9, 10, 11, 12, 17, 18, 19, 20, 23, 30, 34, 38, 39, 40, 44]:\n", " rgb = [217, 217, 217]\n", " colour = \"{0:02X}{1:02X}{2:02X}\".format(*rgb)\n", " ws2.cell(row=row, column=col).fill = PatternFill(fgColor=colour,\n", " bgColor=colour,\n", " patternType=\"solid\")\n", " ws2.cell(row=row, column=1).font = Font(color=colors.BLACK,\n", " italic=False,\n", " bold=False)\n", " ws2.cell(row=row, column=1).alignment = Alignment(horizontal='left',\n", " indent=1)\n", "\n", "wb1.save(os.path.join(output_path, 'national_generation_capacity.xlsx'))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 9. Write checksums" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "output_path = 'output'" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "files = [\n", " 'national_generation_capacity.xlsx',\n", " 'national_generation_capacity_stacked.csv',\n", " 'national_generation_capacity_stacked.xlsx',\n", " 'national_generation_capacity.sqlite'\n", "]\n", "\n", "with open('checksums.txt', 'w') as f:\n", " for file_name in files:\n", " file_hash = get_sha_hash(os.path.join(output_path, file_name))\n", " f.write('{},{}\\n'.format(file_name, file_hash))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 10. Documentation of the data package" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We document the data packages meta data in the specific format JSON as proposed by the Open Knowledge Foundation. See the Frictionless Data project by OKFN (http://data.okfn.org/) and the Data Package specifications (http://dataprotocols.org/data-packages/) for more details.\n", "\n", "In order to keep the notebook more readable, we first formulate the metadata in the human-readable YAML format using a multi-line string. We then parse the string into a Python dictionary and save that to disk as a JSON file." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "output_path = 'output'\n", "\n", "with open(os.path.join('input', 'metadata.yml'), 'r') as f:\n", " metadata = yaml.load(f.read())\n", " \n", "datapackage_json = json.dumps(metadata, indent=4, separators=(',', ': '))\n", "\n", " \n", "# Write the information of the metadata\n", "with open(os.path.join(output_path, 'datapackage.json'), 'w') as f:\n", " f.write(datapackage_json)" ] } ], "metadata": { "anaconda-cloud": {}, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.5" } }, "nbformat": 4, "nbformat_minor": 1 }