Power_Plants_DE.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Power Plants in Germany"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This file covers german power plants. It downloads the power plant list from the BNetzA and augments it with more information."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table of Contents\n",
    "* [Power Plants in Germany](#Power-Plants-in-Germany)\n",
    "* [License](#License)\n",
    "* [Prepare the environment](#Prepare-the-environment)\n",
    "* [Specify the source URLs:](#Specify-the-source-URLs:)\n",
    "* [Define functions](#Define-functions)\n",
    "* [Downloads](#Downloads)\n",
    "\t* [Download the BNetzA power plant list](#Download-the-BNetzA-power-plant-list)\n",
    "\t* [Download the Uba Plant list](#Download-the-Uba-Plant-list)\n",
    "* [Translate contents](#Translate-contents)\n",
    "\t* [Columns](#Columns)\n",
    "\t* [Fuel types](#Fuel-types)\n",
    "\t* [Power plant status](#Power-plant-status)\n",
    "\t* [CHP Capability](#CHP-Capability)\n",
    "\t* [EEG](#EEG)\n",
    "\t* [UBA Columns](#UBA-Columns)\n",
    "\t* [UBA Technologies](#UBA-Technologies)\n",
    "* [Process data](#Process-data)\n",
    "\t* [Set index to the BNetzA power plant ID](#Set-index-to-the-BNetzA-power-plant-ID)\n",
    "\t* [Merge data from UBA List](#Merge-data-from-UBA-List)\n",
    "\t\t* [case 1-1](#case-1-1)\n",
    "\t\t* [case n-1](#case-n-1)\n",
    "\t\t* [case 1-n](#case-1-n)\n",
    "\t\t* [Merge into plantlist](#Merge-into-plantlist)\n",
    "\t* [Delete fuels not in focus](#Delete-fuels-not-in-focus)\n",
    "\t* [Add Columns for shutdown and retrofit](#Add-Columns-for-shutdown-and-retrofit)\n",
    "\t* [Convert input colums to usable data types](#Convert-input-colums-to-usable-data-types)\n",
    "\t* [Identify generation technology](#Identify-generation-technology)\n",
    "\t\t* [Process technology information from UBA list](#Process-technology-information-from-UBA-list)\n",
    "\t\t* [Identify generation technology based on BNetzA information](#Identify-generation-technology-based-on-BNetzA-information)\n",
    "\t* [Add efficiency data](#Add-efficiency-data)\n",
    "\t\t* [Efficiencies from research](#Efficiencies-from-research)\n",
    "\t\t\t* [Import data](#Import-data)\n",
    "\t\t\t* [Plot efficiencies by year of commissioning](#Plot-efficiencies-by-year-of-commissioning)\n",
    "\t\t\t* [Determine least-squares approximation based on researched data](#Determine-least-squares-approximation-based-on-researched-data)\n",
    "\t\t\t* [Apply efficiency approximation from least squares approximation](#Apply-efficiency-approximation-from-least-squares-approximation)\n",
    "\t\t* [Efficiencies from literature](#Efficiencies-from-literature)\n",
    "\t\t\t* [Import data](#Import-data)\n",
    "\t\t\t* [Apply efficiency approximation from literature](#Apply-efficiency-approximation-from-literature)\n",
    "\t* [Add geodata](#Add-geodata)\n",
    "* [Validity Checks](#Validity-Checks)\n",
    "\t* [Visual validity check](#Visual-validity-check)\n",
    "\t\t* [Sum of capacity by fuel type](#Sum-of-capacity-by-fuel-type)\n",
    "\t\t* [Capacities by plant status](#Capacities-by-plant-status)\n",
    "\t\t* [Power plant age](#Power-plant-age)\n",
    "\t\t* [Block size vs year of commissioning](#Block-size-vs-year-of-commissioning)\n",
    "\t* [Logical checks](#Logical-checks)\n",
    "\t\t* [Every power plant needs a capacity](#Every-power-plant-needs-a-capacity)\n",
    "\t\t* [Commissioning Dates](#Commissioning-Dates)\n",
    "\t\t* [Compare UBA and BNetzA data](#Compare-UBA-and-BNetzA-data)\n",
    "\t\t\t* [Postcodes of BNetzA and UBA lists should match](#Postcodes-of-BNetzA-and-UBA-lists-should-match)\n",
    "\t\t\t* [Compare Installed capacities](#Compare-Installed-capacities)\n",
    "\t\t\t* [Compare Comissioning Years](#Compare-Comissioning-Years)\n",
    "* [Define final output](#Define-final-output)\n",
    "* [Documenting the data package (meta data)](#Documenting-the-data-package-%28meta-data%29)\n",
    "* [Write the results to file](#Write-the-results-to-file)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# License"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- This notebook is published under the LICENSENAME"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare the environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "import csv\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import posixpath\n",
    "import urllib.parse\n",
    "import datetime  \n",
    "import re\n",
    "import os.path\n",
    "import yaml  # http://pyyaml.org/, pip install pyyaml, conda install pyyaml\n",
    "import json\n",
    "import subprocess\n",
    "from bokeh.charts import Scatter, show\n",
    "from bokeh.io import output_notebook\n",
    "output_notebook()\n",
    "%matplotlib inline\n",
    "import logging\n",
    "logger = logging.getLogger('notebook')\n",
    "logger.setLevel('INFO')\n",
    "nb_root_logger = logging.getLogger()\n",
    "formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s',\n",
    "                              datefmt='%d %b %Y %H:%M:%S')\n",
    "nb_root_logger.handlers[0].setFormatter(formatter)\n",
    "\n",
    "#create download and output folder if they do not exist\n",
    "if not os.path.exists('downloads/'): os.makedirs('downloads/')\n",
    "if not os.path.exists('output/'): os.makedirs('output/')\n",
    "if not os.path.exists('output/datapackage_powerplants_germany'): os.makedirs('output/datapackage_powerplants_germany')    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Specify the source URLs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# BNetzA Power plant list\n",
    "url_bnetza = 'http://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/Energie/Unternehmen_Institutionen/Versorgungssicherheit/Erzeugungskapazitaeten/Kraftwerksliste/Kraftwerksliste_CSV.csv?__blob=publicationFile&v=5'\n",
    "\n",
    "# UBA Power plant list\n",
    "url_uba = 'http://www.umweltbundesamt.de/sites/default/files/medien/376/dokumente/kraftwerke_in_deutschland_ab_100_megawatt_elektrischer_leistung_2015_09.xls'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This section defines functions used multiple times within this script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def downloadandcache(url):\n",
    "    \"\"\"This function downloads a file into a folder called \n",
    "    downloads and returns the local filepath.\"\"\"\n",
    "    path = urllib.parse.urlsplit(url).path\n",
    "    filename = posixpath.basename(path)\n",
    "    now = datetime.datetime.now()\n",
    "    datestring = \"\"\n",
    "    datestring = str(now.year)+\"-\"+str(now.month)+\"-\"+str(now.day)\n",
    "    filepath = \"downloads/\"+datestring+\"-\"+filename\n",
    "    \n",
    "    #check if file exists, otherwise download it\n",
    "    if os.path.exists(filepath) == False:\n",
    "        print(\"Downloading file\", filename)\n",
    "        urllib.request.urlretrieve(url, filepath)\n",
    "    else:\n",
    "        print(\"Using local file from\", filepath)\n",
    "    filepath = './'+filepath\n",
    "    return filepath\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Downloads"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download the BNetzA power plant list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This section downloads the BNetzA power plant list and converts it to a pandas data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bnetza_data_filepath=(downloadandcache(url_bnetza))\n",
    "#print(bnetza_data_filepath)\n",
    "plantlist=pd.read_csv(bnetza_data_filepath, \n",
    "               skiprows=9,\n",
    "               sep=';',  # CSV field separator, default is ','\n",
    "               thousands='.',  # Thousands separator, default is ','\n",
    "               decimal=',',  # Decimal separator, default is '.')  \n",
    "               encoding='cp1252')\n",
    "plantlist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download the Uba Plant list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "uba_data_filepath=(downloadandcache(url_uba))\n",
    "plantlist_uba=pd.read_excel(uba_data_filepath,\n",
    "                           skiprows=9\n",
    "                           )\n",
    "plantlist_uba.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Translate contents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A dictionary with the original column names to the new column names is created. This dictionary is used to translate the column names.\n",
    "\n",
    "Original Name|Translation\n",
    "-|-\n",
    "Kraftwerksnummer Bundesnetzagentur|id\n",
    "Unternehmen|company\n",
    "Kraftwerksname|name\n",
    "PLZ\\n(Standort Kraftwerk)|postcode\n",
    "Ort\\n(Standort Kraftwerk)|city\n",
    "Straße und Hausnummer (Standort Kraftwerk)|street\n",
    "Bundesland|state\n",
    "Blockname|block\n",
    "Aufnahme der kommerziellen Stromerzeugung der derzeit in Betrieb befindlichen Erzeugungseinheit\\n(Jahr)|commissioned\n",
    "Kraftwerksstatus \\n(in Betrieb/\\nvorläufig stillgelegt/\\nsaisonale Konservierung\\nReservekraftwerk/\\nSonderfall)|status\n",
    "Energieträger|fuel_basis\n",
    "Spezifizierung \"Mehrere Energieträger\" und \"Sonstige Energieträger\" - Hauptbrennstoff|fuel_multiple1\n",
    "Spezifizierung \"Mehrere Energieträger\" - Zusatz- / Ersatzbrennstoffe|fuel_multiple2\n",
    "Auswertung\\nEnergieträger (Zuordnung zu einem Hauptenergieträger bei Mehreren Energieträgern)|fuel\n",
    "Vergütungsfähig nach EEG\\n(ja/nein)|eeg\n",
    "Wärmeauskopplung (KWK)\\n(ja/nein)|chp\n",
    "Netto-Nennleistung (elektrische Wirkleistung) in MW|capacity\n",
    "Bezeichnung Verknüpfungspunkt (Schaltanlage) mit dem Stromnetz der Allgemeinen Versorgung gemäß Netzbetreiber|network_node\n",
    "Netz- oder Umspannebene des Anschlusses in kV|voltage\n",
    "Name Stromnetzbetreiber|network_operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "dict_columns = { 'Kraftwerksnummer Bundesnetzagentur':'id',\n",
    "            'Unternehmen':'company',\n",
    "            'Kraftwerksname':'name',\n",
    "            'PLZ\\n(Standort Kraftwerk)':'postcode',\n",
    "            'Ort\\n(Standort Kraftwerk)':'city',\n",
    "            'Straße und Hausnummer (Standort Kraftwerk)':'street',\n",
    "            'Bundesland':'state',\n",
    "            'Blockname':'block',\n",
    "            'Aufnahme der kommerziellen Stromerzeugung der derzeit in Betrieb befindlichen Erzeugungseinheit\\n(Jahr)':'commissioned',\n",
    "            'Kraftwerksstatus \\n(in Betrieb/\\nvorläufig stillgelegt/\\nsaisonale Konservierung\\nReservekraftwerk/\\nSonderfall)':'status',\n",
    "            'Energieträger':'fuel_basis',\n",
    "            'Spezifizierung \"Mehrere Energieträger\" und \"Sonstige Energieträger\" - Hauptbrennstoff':'fuel_multiple1',\n",
    "            'Spezifizierung \"Mehrere Energieträger\" - Zusatz- / Ersatzbrennstoffe':'fuel_multiple2',\n",
    "            'Auswertung\\nEnergieträger (Zuordnung zu einem Hauptenergieträger bei Mehreren Energieträgern)':'fuel',\n",
    "            'Vergütungsfähig nach EEG\\n(ja/nein)':'eeg',\n",
    "            'Wärmeauskopplung (KWK)\\n(ja/nein)':'chp',\n",
    "            'Netto-Nennleistung (elektrische Wirkleistung) in MW':'capacity',\n",
    "            'Bezeichnung Verknüpfungspunkt (Schaltanlage) mit dem Stromnetz der Allgemeinen Versorgung gemäß Netzbetreiber':'network_node',\n",
    "            'Netz- oder Umspannebene des Anschlusses in kV':'voltage',\n",
    "            'Name Stromnetzbetreiber':'network_operator',\n",
    "            'Kraftwerksname / Standort': 'uba_name',\n",
    "            'Betreiber ': 'uba_company',\n",
    "            'Standort-PLZ': 'uba_postcode',\n",
    "            'Kraftwerksstandort': 'uba_city',\n",
    "            'Elektrische Bruttoleistung (MW)': 'uba_capacity',\n",
    "            'Fernwärme-leistung (MW)': 'uba_chp_capacity',\n",
    "            'Inbetriebnahme  (ggf. Ertüchtigung)': 'uba_commissioned',\n",
    "            'Anlagenart': 'uba_technology',\n",
    "            'Primärenergieträger': 'uba_fuel',\n",
    "          }\n",
    "plantlist.rename(columns=dict_columns, inplace=True)\n",
    "\n",
    "# Check if all columns have been translated\n",
    "for columnnames in plantlist.columns:\n",
    "    #print(columnnames)\n",
    "    if not columnnames in dict_columns.values():\n",
    "        logger.error(\"Untranslated column: \"+ columnnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fuel types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dict_fuels = {'Steinkohle':'coal',\n",
    "              'Erdgas':'gas',\n",
    "              'Braunkohle':'lignite',\n",
    "              'Kernenergie':'uranium',\n",
    "              'Pumpspeicher':'pumped_storage',\n",
    "              'Biomasse':'biomass',\n",
    "              'Mineralölprodukte':'oil',\n",
    "              'Laufwasser':'run_of_river',\n",
    "              'Sonstige Energieträger\\n(nicht erneuerbar) ':'other_non_renewable',\n",
    "              'Abfall':'waste',\n",
    "              'Speicherwasser (ohne Pumpspeicher)':'reservoir',\n",
    "              'Unbekannter Energieträger\\n(nicht erneuerbar)':'unknown_non_renewable',\n",
    "              'Mehrere Energieträger\\n(nicht erneuerbar)':'multiple_non_renewable',\n",
    "              'Deponiegas':'gas_landfill',\n",
    "              'Windenergie (Onshore-Anlage)':'wind_onshore',\n",
    "              'Windenergie (Offshore-Anlage)':'wind_offshore',\n",
    "              'Solare Strahlungsenergie':'solar',\n",
    "              'Klärgas':'gas_sewage',\n",
    "              'Geothermie':'geothermal',\n",
    "              'Grubengas':'gas_mine'\n",
    "                        }\n",
    "plantlist[\"fuel\"].replace(dict_fuels, inplace=True)\n",
    "plantlist[\"fuel\"].unique()\n",
    "\n",
    "# Check if all fuels have been translated\n",
    "for fuelnames in plantlist[\"fuel\"].unique():\n",
    "    #print(columnnames)\n",
    "    if not fuelnames in dict_fuels.values():\n",
    "        logger.error(\"Untranslated fuel: \"+ fuelnames)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Power plant status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dict_plantstatus ={\n",
    "'in Betrieb':'operating',\n",
    "'vorläufig stillgelegt':'shutdown_temporal',\n",
    "'Sonderfall':'special_case',\n",
    "'saisonale Konservierung':'seasonal_conservation',\n",
    "'Reservekraftwerk':'reserve',\n",
    "'Endgültig Stillgelegt 2011':'shutdown_2011',\n",
    "'Endgültig Stillgelegt 2012':'shutdown_2012',\n",
    "'Endgültig Stillgelegt 2013':'shutdown_2013',\n",
    "'Endgültig Stillgelegt 2014':'shutdown_2014',\n",
    "'Endgültig Stillgelegt 2015':'shutdown_2015',\n",
    "'Endgültig stillgelegt 2015':'shutdown_2015',\n",
    "}\n",
    "plantlist[\"status\"].replace(dict_plantstatus, inplace=True)\n",
    "plantlist[\"status\"].unique()\n",
    "\n",
    "# Check if all fuels have been translated\n",
    "for statusnames in plantlist[\"status\"].unique():\n",
    "    #print(columnnames)\n",
    "    if not statusnames in dict_plantstatus.values():\n",
    "        logger.error(\"Untranslated plant status: \"+ statusnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CHP Capability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dict_yesno ={\n",
    "'Nein':'no',\n",
    "'nein':'no',\n",
    "'Ja':'yes',\n",
    "'ja':'yes',    \n",
    "}\n",
    "plantlist[\"chp\"].replace(dict_yesno, inplace=True)\n",
    "plantlist[\"chp\"].unique()\n",
    "\n",
    "# Check if all fuels have been translated\n",
    "for chpnames in plantlist[\"chp\"].unique():\n",
    "    if (not chpnames in dict_yesno.values()) & (str(chpnames) != \"nan\"):\n",
    "        logger.error(\"Untranslated chp capability: \" + str(chpnames))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## EEG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "plantlist[\"eeg\"].replace(dict_yesno, inplace=True)\n",
    "plantlist[\"eeg\"].unique()\n",
    "\n",
    "# Check if all fuels have been translated\n",
    "for eegnames in plantlist[\"eeg\"].unique():\n",
    "    if (not eegnames in dict_yesno.values()) & (str(eegnames) != \"nan\"):\n",
    "        logger.error(\"Untranslated EEG type: \" + str(eegnames))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UBA Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the same dictionary as above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dict_uba_columns = {\n",
    "            'Kraftwerksname / Standort': 'uba_name',\n",
    "            'Betreiber ': 'uba_company',\n",
    "            'Standort-PLZ': 'uba_postcode',\n",
    "            'Kraftwerksstandort': 'uba_city',\n",
    "            'Elektrische Bruttoleistung (MW)': 'uba_capacity',\n",
    "            'Fernwärme-leistung (MW)': 'uba_chp_capacity',\n",
    "            'Inbetriebnahme  (ggf. Ertüchtigung)': 'uba_commissioned',\n",
    "            'Anlagenart': 'uba_technology',\n",
    "            'Primärenergieträger': 'uba_fuel',\n",
    "            'Bundesland':'uba_state',\n",
    "          }\n",
    "plantlist_uba.rename(columns=dict_uba_columns, inplace=True)\n",
    "\n",
    "# Check if all columns have been translated\n",
    "for columnnames in plantlist_uba.columns:\n",
    "    #print(columnnames)\n",
    "    if not columnnames in dict_uba_columns.values():\n",
    "        logger.error(\"Untranslated column: \"+ columnnames)\n",
    "        \n",
    "#Prepare for matching\n",
    "plantlist_uba['uba_id_string'] = plantlist_uba['uba_name'] + '_' + plantlist_uba['uba_fuel']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UBA Technologies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Translate UBA Technologies here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set index to the BNetzA power plant ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Set Index to Kraftwerksnummer_Bundesnetzagentur\n",
    "plantlist['bnetza_id'] = plantlist['id']\n",
    "plantlist = plantlist.set_index('id')\n",
    "\n",
    "# Add comment column\n",
    "plantlist['comment'] = ' '\n",
    "\n",
    "plantlist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge data from UBA List"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# UBA List import here\n",
    "matchinglist=pd.read_csv('inputs/matching_bnetza_uba.csv', \n",
    "               skiprows=0,\n",
    "               sep=';',  # CSV field separator, default is ','\n",
    "               thousands='.',  # Thousands separator, default is ','\n",
    "               decimal=',',  # Decimal separator, default is '.')  \n",
    "               encoding='cp1252')\n",
    "matchinglist['uba_id_string'] = matchinglist['uba_match_name'] + '_' + matchinglist['uba_match_fuel']\n",
    "matchinglist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### case 1-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Check for cases:\n",
    "#1-1 One BNetzA ID to one UBA-ID\n",
    "\n",
    "match1t1 = matchinglist[(matchinglist.duplicated(subset=['uba_id_string'], keep=False) == False) & (matchinglist.duplicated(subset=['ID BNetzA'], keep=False)== False)]\n",
    "match1t1 = pd.merge(match1t1, plantlist_uba, left_on='uba_id_string', right_on='uba_id_string', how='left')\n",
    "match1t1 = match1t1.set_index('ID BNetzA')\n",
    "match1t1.head()\n",
    "\n",
    "#Add comment\n",
    "match1t1['merge_comment'] = 'List matching type: Single UBA power plant assigned to single BNetzA power plant'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### case n-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Match multiple BNetza IDs to One UBA ID\n",
    "# Matching structure: \n",
    "# bnetza_id uba_id\n",
    "# 1         1\n",
    "# 2         1\n",
    "# 3         1\n",
    "# 4         2\n",
    "# 5         2\n",
    "\n",
    "# Get relevant entries from the matchinglist and merge the corresponding UBA Data to the list.\n",
    "matchnt1= matchinglist[(matchinglist.duplicated(subset=['uba_id_string'], keep=False) == True) & (matchinglist.duplicated(subset=['ID BNetzA'], keep=False)== False)]\n",
    "matchnt1 = pd.merge(matchnt1, plantlist_uba, left_on='uba_id_string', right_on='uba_id_string', how='left')\n",
    "matchnt1 = matchnt1.set_index('ID BNetzA')\n",
    "\n",
    "# Import BNetzA Capacities and CHP criterion into matchnt1 dataframe\n",
    "plantlist_capacities = pd.DataFrame(plantlist[['capacity','chp']])\n",
    "plantlist_capacities=plantlist_capacities.rename(columns = {'capacity':'capacity_bnetza'})\n",
    "plantlist_capacities=plantlist_capacities.rename(columns = {'chp':'chp_bnetza'})\n",
    "#print(plantlist_capacities)\n",
    "matchnt1 = pd.merge(matchnt1, plantlist_capacities, left_index=True, right_index=True, how='left')\n",
    "\n",
    "# Get sum of BNetzA Capacitites for each UBA Index and merge into matchnt1 dataframe\n",
    "plantlist_uba_capacitysum = pd.DataFrame(matchnt1.groupby('uba_id_string').sum()['capacity_bnetza'])\n",
    "plantlist_uba_capacitysum=plantlist_uba_capacitysum.rename(columns ={'capacity_bnetza':'capacity_bnetza_aggregate'})\n",
    "matchnt1 = pd.merge(matchnt1, plantlist_uba_capacitysum, left_index=True,right_index=True, how='left')\n",
    "#matchnt1 = pd.merge(matchnt1, plantlist_uba_capacitysum, left_index=True, right_on='uba_id_string', how='left')\n",
    "\n",
    "# Scale UBA Capacities based BNEtza Data\n",
    "matchnt1['uba_capacity_scaled'] = matchnt1['uba_capacity'] * matchnt1['capacity_bnetza']/matchnt1['capacity_bnetza_aggregate']\n",
    "\n",
    "#determine sum of capacities with chp capability and add to matchnt1\n",
    "plantlist_uba_chp_capacities = matchnt1[(matchnt1['chp_bnetza'] == 'yes')]\n",
    "plantlist_uba_chp_capacitysum = pd.DataFrame(plantlist_uba_chp_capacities.groupby('uba_id_string').sum()[['capacity_bnetza']])\n",
    "plantlist_uba_chp_capacitysum = plantlist_uba_chp_capacitysum.rename(columns = {'capacity_bnetza':'capacity_bnetza_with_chp'})\n",
    "plantlist_uba_chp_capacitysum\n",
    "matchnt1 = pd.merge(matchnt1, plantlist_uba_chp_capacitysum, left_index=True, right_index=True, how='left')\n",
    "\n",
    "matchnt1['uba_chp_capacity_scaled'] = matchnt1['uba_chp_capacity'] * matchnt1['capacity_bnetza']/matchnt1['capacity_bnetza_with_chp']\n",
    "\n",
    "# Change column names for merge later on\n",
    "matchnt1['uba_chp_capacity_original'] = matchnt1['uba_chp_capacity']\n",
    "matchnt1['uba_chp_capacity'] = matchnt1['uba_chp_capacity_scaled']\n",
    "matchnt1['uba_capacity_original'] = matchnt1['uba_capacity']\n",
    "matchnt1['uba_capacity'] = matchnt1['uba_capacity_scaled']\n",
    "\n",
    "#Add comment\n",
    "matchnt1['merge_comment'] = 'List matching type: UBA capacity distributed proportionally to multiple BNetzA power plants'\n",
    "\n",
    "# Drop columns not needed anymore\n",
    "colsToDrop = ['capacity_bnetza', 'chp_bnetza','capacity_bnetza_with_chp', 'capacity_bnetza_aggregate', 'uba_chp_capacity_scaled', 'uba_capacity_scaled']\n",
    "matchnt1 = matchnt1.drop(colsToDrop, axis=1)\n",
    "matchnt1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### case 1-n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#1-n Case here\n",
    "#The resulting DataFrame should be called \"match1tn\"\n",
    "# Matching structure: \n",
    "# bnetza_id uba_id\n",
    "# 1         1\n",
    "# 1         2\n",
    "# 1         3\n",
    "# 2         4\n",
    "# 2         5\n",
    "\n",
    "# Get relevant entries from the matchinglist and merge the corresponding UBA Data to the list.\n",
    "match1tn= matchinglist[(matchinglist.duplicated(subset=['ID BNetzA'], keep=False) == True) & (matchinglist.duplicated(subset=['uba_id_string'], keep=False)== False)]\n",
    "match1tn = pd.merge(match1tn, plantlist_uba, left_on='uba_id_string', right_on='uba_id_string', how='left')\n",
    "match1tn = match1tn.set_index('ID BNetzA')\n",
    "match1tn.head()\n",
    "\n",
    "# Import BNetzA Capacities and CHP criterion into match1tn dataframe\n",
    "plantlist_capacities = pd.DataFrame(plantlist[['capacity','chp']])\n",
    "plantlist_capacities=plantlist_capacities.rename(columns = {'capacity':'capacity_bnetza'})\n",
    "plantlist_capacities=plantlist_capacities.rename(columns = {'chp':'chp_bnetza'})\n",
    "#plantlist_capacities.head()\n",
    "#print(plantlist_capacities)\n",
    "match1tn = pd.merge(match1tn, plantlist_capacities, left_index=True, right_index=True, how='left')\n",
    "match1tn.index.names=['ID BNetzA']\n",
    "match1tn.head()\n",
    "\n",
    "# Get sum of UBA Capacitites for each BNetzA Index and merge into match1tn dataframe\n",
    "plantlist_bnetza_capacitysum = pd.DataFrame(match1tn.groupby(match1tn.index).sum()['uba_capacity'])\n",
    "plantlist_bnetza_capacitysum=plantlist_bnetza_capacitysum.rename(columns ={'uba_capacity':'uba_capacity_aggregate'})\n",
    "#print(plantlist_uba_capacitysum)\n",
    "match1tn = pd.merge(match1tn, plantlist_bnetza_capacitysum, left_index=True, right_index=True, how='left')\n",
    "\n",
    "# Get sum of UBA CHP Capacities for each BNetzA Index and merge inot match1tn datafram\n",
    "plantlist_bnetza_chp_capacitysum = pd.DataFrame(match1tn.groupby(match1tn.index).sum()['uba_chp_capacity'])\n",
    "plantlist_bnetza_chp_capacitysum=plantlist_bnetza_chp_capacitysum.rename(columns ={'uba_chp_capacity':'uba_chp_capacity_aggregate'})\n",
    "match1tn = pd.merge(match1tn, plantlist_bnetza_chp_capacitysum, left_index=True, right_index=True, how='left')\n",
    "\n",
    "# Get UBA Technology for each BNetzA Index and merge into match1tn dataframe \n",
    "## Option 1: Take all technologies and merge them\n",
    "#match1tn['uba_technology_aggregate'] = pd.DataFrame(match1tn.groupby(match1tn.index).transform(lambda x: ', '.join(x))['uba_technology'])\n",
    "## Option 2 (currently preferred): Take technology with highest occurence\n",
    "match1tn['uba_technology_aggregate'] = pd.DataFrame(match1tn.groupby(match1tn.index)['uba_technology'].agg(lambda x:x.value_counts().index[0]))\n",
    "\n",
    "# Get UBA Plant name\n",
    "match1tn['uba_name_aggregate'] = pd.DataFrame(match1tn.groupby(match1tn.index).transform(lambda x: ', '.join(x))['uba_name'])\n",
    "\n",
    "# Get UBA company name\n",
    "match1tn['uba_company_aggregate'] = pd.DataFrame(match1tn.groupby(match1tn.index)['uba_company'].agg(lambda x:x.value_counts().index[0]))\n",
    "\n",
    "# Change column names for merge later on\n",
    "match1tn = match1tn.rename(columns={'uba_chp_capacity':'uba_chp_capacity_original','uba_capacity':'uba_capacity_original',\n",
    "                                   'uba_chp_capacity_aggregate':'uba_chp_capacity','uba_capacity_aggregate':'uba_capacity'})\n",
    "#match1tn['uba_chp_capacity_original'] = match1tn['uba_chp_capacity_aggregate']\n",
    "#match1tn['uba_capacity_original'] = match1tn['uba_capacity_aggregate']\n",
    "\n",
    "#Add comment\n",
    "match1tn['merge_comment'] = 'List matching type: Multiple UBA capacities aggregated to single BNetzA power plant'\n",
    "\n",
    "# Drop columns not needed anymore\n",
    "colsToDrop = ['capacity_bnetza', 'chp_bnetza']\n",
    "match1tn = match1tn.drop(colsToDrop, axis=1)\n",
    "\n",
    "# Drop duplicate rows and keep first entry\n",
    "match1tn = match1tn.reset_index().drop_duplicates(subset='ID BNetzA', keep='first').set_index('ID BNetzA')\n",
    "\n",
    "match1tn.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge into plantlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Merge the UBA DataFrames\n",
    "\n",
    "#Merge first two dataframes\n",
    "plantlist_uba_for_merge = match1t1.append(matchnt1)\n",
    "\n",
    "#Add third dataframe\n",
    "plantlist_uba_for_merge = plantlist_uba_for_merge.append(match1tn)\n",
    "\n",
    "#Merge plantlist_uba_for_merge into the plantlist\n",
    "plantlist = pd.merge(plantlist, plantlist_uba_for_merge, left_index=True, right_index=True, how='left')\n",
    "\n",
    "# Add Merge comment to comment column\n",
    "plantlist['comment'] = plantlist['comment'] + plantlist['merge_comment']\n",
    "plantlist = plantlist.drop('merge_comment', axis=1)\n",
    "\n",
    "#plantlist_uba_for_merge\n",
    "#plantlist.head()\n",
    "#plantlist.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plantlist.loc[['BNA0073']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Delete fuels not in focus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Delete unwanted fuels\n",
    "plantlist = plantlist[plantlist.fuel != 'solar']\n",
    "plantlist = plantlist[plantlist.fuel != 'wind_onshore']\n",
    "plantlist = plantlist[plantlist.fuel != 'wind_offshore']\n",
    "\n",
    "# Delete placeholder values\n",
    "plantlist = plantlist[plantlist.company != 'EEG-Anlagen < 10 MW']\n",
    "plantlist = plantlist[plantlist.company != 'Nicht-EEG-Anlagen < 10 MW']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add Columns for shutdown and retrofit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Add columns with empty data\n",
    "plantlist['shutdown'] = 'NaN'\n",
    "#plantlist['retrofit']= 'NaN'\n",
    "\n",
    "# Fill shutdown data column based on Plant status\n",
    "#plantlist['shutdown'] = np.where(plantlist['status'] == 'shutdown_2010', 2010, plantlist['shutdown'])\n",
    "#plantlist['shutdown'] = np.where(plantlist['status'] == 'shutdown_2011', 2011, plantlist['shutdown'])\n",
    "#plantlist['shutdown'] = np.where(plantlist['status'] == 'shutdown_2012', 2012, plantlist['shutdown'])\n",
    "#plantlist['shutdown'] = np.where(plantlist['status'] == 'shutdown_2013', 2013, plantlist['shutdown'])\n",
    "#plantlist['shutdown'] = np.where(plantlist['status'] == 'shutdown_2014', 2014, plantlist['shutdown'])\n",
    "#plantlist['shutdown'] = np.where(plantlist['status'] == 'shutdown_2015', 2015, plantlist['shutdown'])\n",
    "#plantlist['shutdown'] = np.where(plantlist['status'] == 'shutdown_2016', 2016, plantlist['shutdown'])\n",
    "\n",
    "plantlist['shutdown'] = pd.to_numeric(plantlist['status'].str.extract('[\\w].+(\\d\\d\\d\\d)'),errors='coerce')\n",
    "plantlist['status'][plantlist['shutdown'] > 0] = 'shutdown'\n",
    "\n",
    "# Fill retrofit data column\n",
    "# Identify restrofit dates in UBA list\n",
    "plantlist['retrofit'] = pd.to_numeric(plantlist['uba_commissioned'].str.extract('[(.+](\\d\\d\\d\\d)'),errors='coerce')\n",
    "\n",
    "# Split multiple commissioning dates as listed in UBA\n",
    "plantlist['uba_commissioned_1'] = pd.to_numeric(plantlist['uba_commissioned'].str.extract('(\\d\\d\\d\\d)'),errors='coerce')\n",
    "plantlist['uba_commissioned_1'][plantlist['uba_commissioned_1'].isnull()] = pd.to_numeric(plantlist['uba_commissioned'].str.extract('(\\d\\d\\d\\d).+[\\w]'),errors='coerce')\n",
    "plantlist['uba_commissioned_2'] = pd.to_numeric(plantlist['uba_commissioned'].str.extract('[\\w].+(\\d\\d\\d\\d).+[\\w]'),errors='coerce')\n",
    "plantlist['uba_commissioned_3'] = pd.to_numeric(plantlist['uba_commissioned'].str.extract('[\\w].+(\\d\\d\\d\\d)'),errors='coerce')\n",
    "\n",
    "plantlist['uba_commissioned_1'][plantlist['retrofit']==plantlist['uba_commissioned_1']] = ''\n",
    "plantlist['uba_commissioned_2'][plantlist['retrofit']==plantlist['uba_commissioned_2']] = ''\n",
    "plantlist['uba_commissioned_3'][plantlist['retrofit']==plantlist['uba_commissioned_3']] = ''\n",
    "\n",
    "# Split multiple commissioning dates as listed in BNetzA\n",
    "plantlist['commissioned_1'] = pd.to_numeric(plantlist['commissioned'].str.extract('(\\d\\d\\d\\d)'),errors='coerce')\n",
    "plantlist['commissioned_1'][plantlist['commissioned_1'].isnull()] = pd.to_numeric(plantlist['commissioned'].str.extract('(\\d\\d\\d\\d).+[\\w]'),errors='coerce')\n",
    "plantlist['commissioned_2'] = pd.to_numeric(plantlist['commissioned'].str.extract('[\\w].+(\\d\\d\\d\\d).+[\\w]'),errors='coerce')\n",
    "plantlist['commissioned_3'] = pd.to_numeric(plantlist['commissioned'].str.extract('[\\w].+(\\d\\d\\d\\d)'),errors='coerce')\n",
    "\n",
    "# Show plantlist\n",
    "plantlist[plantlist['status']=='shutdown']\n",
    "\n",
    "#plantlist.to_excel('power_plants_germany_tech.xlsx', sheet_name='output')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert input colums to usable data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plantlist['capacity_float'] = pd.to_numeric(plantlist['capacity'],errors='coerce')\n",
    "plantlist['commissioned_float'] = pd.to_numeric(plantlist[['commissioned','commissioned_1','commissioned_2','commissioned_3']].max(axis=1),errors='coerce')\n",
    "plantlist['retrofit_float'] = pd.to_numeric(plantlist['retrofit'],errors='coerce')\n",
    "plantlist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify generation technology"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Process technology information from UBA list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Split uba_technology information into technology (GT, CC,...) and type (HKW, IKW, ...)\n",
    "plantlist['technology'] = plantlist['uba_technology']\n",
    "plantlist['type'] = plantlist['uba_technology']\n",
    "\n",
    "# Translate technologies\n",
    "dict_technology = {\n",
    "            'GT': 'GT',\n",
    "            'GuD': 'CC',\n",
    "            'DKW': 'ST',\n",
    "            'LWK': 'ROR',\n",
    "            'PSW': 'PSP',\n",
    "            'DWR': 'ST', #Pressurized water reactor\n",
    "            'G/AK': 'GT', #GT with heat recovery\n",
    "            'SWR': 'ST', #boiling water reactor\n",
    "            'SWK': 'SPP', #storage power plant\n",
    "            'SSA': '', #bus bar\n",
    "            'HKW (DT)': 'ST',\n",
    "            'HKW / GuD': 'CC',\n",
    "            'GuD / HKW': 'CC',\n",
    "            'IKW / GuD': 'CC',\n",
    "            'IKW /GuD': 'CC',\n",
    "            'HKW / SSA': '',\n",
    "            'IKW / SSA': '',\n",
    "            'HKW': '',\n",
    "            'IKW': '',\n",
    "            'IKW / HKW': ''\n",
    "}\n",
    "plantlist[\"technology\"].replace(dict_technology, inplace=True)\n",
    "plantlist[\"technology\"].unique()\n",
    "\n",
    "# Check if all technologies have been translated\n",
    "for technology in plantlist[\"technology\"].unique():\n",
    "    if (not technology in dict_technology.values()) & (str(technology) != \"nan\"):\n",
    "        logger.error(\"Untranslated technology: \" + str(technology))\n",
    "\n",
    "# Translate types\n",
    "dict_type = {\n",
    "            'HKW': 'CHP', #thermal power plant,\n",
    "            'HKW (DT)': 'CHP',\n",
    "            'IKW': 'IPP', #industrial power plant         \n",
    "            'HKW / GuD': 'CHP',\n",
    "            'GuD / HKW': 'CHP',\n",
    "            'IKW / GuD': 'IPP',\n",
    "            'IKW /GuD': 'IPP',\n",
    "            'IKW / SSA': 'IPP',\n",
    "            'HKW / SSA': 'CHP',\n",
    "            'IKW / HKW': 'CHP',\n",
    "            'GT': '',\n",
    "            'GuD': '',\n",
    "            'DKW': '',\n",
    "            'LWK': '',\n",
    "            'PSW': '',\n",
    "            'DWR': '', #Pressurized water reactor\n",
    "            'G/AK': 'CHP', #GT with heat recovery\n",
    "            'SWR': '', #boiling water reactor\n",
    "            'SWK': '', #storage power plant\n",
    "            'SSA': '', #\n",
    "    \n",
    "}\n",
    "plantlist[\"type\"].replace(dict_type, inplace=True)\n",
    "plantlist[\"type\"].unique()\n",
    "\n",
    "# Check if all types have been translated\n",
    "for type in plantlist[\"type\"].unique():\n",
    "    if (not type in dict_type.values()) & (str(type) != \"nan\"):\n",
    "        logger.error(\"Untranslated type: \" + str(type))\n",
    "\n",
    "#plantlist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify generation technology based on BNetzA information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Set technology based on fuels\n",
    "plantlist['technology'][(plantlist['fuel']=='uranium')&((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'ST'\n",
    "plantlist['technology'][(plantlist['fuel']=='lignite')&((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'ST'\n",
    "plantlist['technology'][(plantlist['fuel']=='coal')&((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'ST'\n",
    "plantlist['technology'][(plantlist['fuel']=='run_of_river')&((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'ROR'\n",
    "plantlist['technology'][(plantlist['fuel']=='pumped_storage')&((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'PSP'\n",
    "plantlist['technology'][(plantlist['fuel']=='reservoir')&((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'RES'\n",
    "\n",
    "\n",
    "# Set technology based on name and block information combined with fuels (e.g. combined-cycle, gas turbine)\n",
    "## Define technology CC as combination of GT and DT\n",
    "plantlist['technology'][((plantlist['name'].str.contains(\"GT\"))|(plantlist['block'].str.contains(\"GT\")))&\n",
    "                        ((plantlist['name'].str.contains(\"DT\"))|(plantlist['block'].str.contains(\"DT\")))&\n",
    "#                        ((plantlist['fuel']=='gas')|(plantlist['fuel']=='oil'))&\n",
    "                        ((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'CC'\n",
    "## Define technology CC if specified as GuD\n",
    "plantlist['technology'][((plantlist['name'].str.contains(\"GuD\"))|(plantlist['block'].str.contains(\"GuD\"))|\n",
    "                            (plantlist['name'].str.contains(\"GUD\"))|(plantlist['name'].str.contains(\"GUD\")))&\n",
    "#                        ((plantlist['fuel']=='gas')|(plantlist['fuel']=='oil'))&\n",
    "                        ((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'CC'\n",
    "## Define technology GT\n",
    "plantlist['technology'][((plantlist['name'].str.contains(\"GT\"))|(plantlist['block'].str.contains(\"GT\"))|\n",
    "                            (plantlist['name'].str.contains(\"Gasturbine\"))|(plantlist['block'].str.contains(\"Gasturbine\")))&\n",
    "#                        ((plantlist['fuel']=='gas')|(plantlist['fuel']=='oil'))&\n",
    "                        ((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'GT'\n",
    "## Define technology ST\n",
    "plantlist['technology'][((plantlist['name'].str.contains(\"DT\"))|(plantlist['block'].str.contains(\"DT\"))|\n",
    "                            (plantlist['name'].str.contains(\"Dampfturbine\"))|(plantlist['block'].str.contains(\"Dampfturbine\"))|\n",
    "                            (plantlist['name'].str.contains(\"Dampfkraftwerk\"))|(plantlist['block'].str.contains(\"Dampfkraftwerk\"))|\n",
    "                            (plantlist['name'].str.contains(\"DKW\"))|(plantlist['block'].str.contains(\"DKW\")))&\n",
    "#                        ((plantlist['fuel']=='gas')|(plantlist['fuel']=='oil'))&\n",
    "                        ((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'ST'\n",
    "## Define technology CB\n",
    "plantlist['technology'][((plantlist['name'].str.contains(\"motor\"))|(plantlist['block'].str.contains(\"motor\"))|\n",
    "                            (plantlist['name'].str.contains(\"Motor\"))|(plantlist['block'].str.contains(\"Motor\")))&\n",
    "#                        ((plantlist['fuel']=='gas')|(plantlist['fuel']=='oil'))&\n",
    "                        ((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'CB'\n",
    "\n",
    "# Set technology ST for all technologies which could not be identified\n",
    "plantlist['technology'][((plantlist['technology']=='')|(plantlist['technology'].isnull()))] = 'ST'\n",
    "\n",
    "#plantlist.to_excel('power_plants_germany_tech.xlsx', sheet_name='output')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add efficiency data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Efficiencies from research"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Efficiencies\n",
    "data_efficiencies_bnetza=pd.read_csv('inputs/input_efficiency_de.csv',\n",
    "                                     sep=';',  # CSV field separator, default is ','\n",
    "                                     decimal='.',  # Decimal separator, default is '.')  \n",
    "                                     encoding='cp1252')\n",
    "data_efficiencies_bnetza = data_efficiencies_bnetza.set_index('id')\n",
    "data_efficiencies_bnetza['efficiency_net'] = pd.to_numeric(data_efficiencies_bnetza['efficiency_net'],errors='coerce')\n",
    "data_efficiencies_bnetza['efficiency_source'] = 'Efficiency source: ' + data_efficiencies_bnetza['efficiency_source']\n",
    "data_efficiencies_bnetza = data_efficiencies_bnetza.dropna(subset=['efficiency_net'])\n",
    "data_efficiencies_bnetza\n",
    "\n",
    "plantlist = pd.merge(plantlist, data_efficiencies_bnetza, left_index=True, right_index=True, how='left')\n",
    "\n",
    "#Add comment \n",
    "plantlist['comment_interim'] = plantlist.apply(lambda x:'%s ; %s' % (x['comment'],x['efficiency_source']),axis=1)\n",
    "plantlist['comment_interim'] = plantlist['comment_interim'].str.replace('nan ;', '')\n",
    "plantlist['comment_interim'] = plantlist['comment_interim'].str.replace('; nan', '')\n",
    "plantlist['comment_interim'] = plantlist['comment_interim'].str.replace('nan', '')\n",
    "plantlist = plantlist.drop('comment', axis=1)\n",
    "plantlist = plantlist.rename(columns = {'comment_interim':'comment'})\n",
    "\n",
    "plantlist.head()\n",
    "#plantlist['efficiency_net']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plot efficiencies by year of commissioning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plantlist_for_efficiency_analysis = plantlist\n",
    "plantlist_for_efficiency_analysis = plantlist_for_efficiency_analysis.dropna(subset=['efficiency_net'])\n",
    "fuel_for_plot = ['lignite', 'coal', 'oil', 'gas']\n",
    "plantlist_for_efficiency_analysis = plantlist_for_efficiency_analysis[plantlist_for_efficiency_analysis.fuel.isin(fuel_for_plot)]\n",
    "plot_efficiency_type = Scatter(plantlist_for_efficiency_analysis, \n",
    "                              notebook=True, \n",
    "                              x='commissioned_float', \n",
    "                              y='efficiency_net',\n",
    "                              color='fuel', \n",
    "                              title='Efficiency vs commissioning year', \n",
    "                              xlabel='Year', \n",
    "                              ylabel='Efficiency',\n",
    "                              legend=\"top_left\",\n",
    "                              height=700,\n",
    "                              width=1000,\n",
    "                             )\n",
    "show(plot_efficiency_type)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Determine least-squares approximation based on researched data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import statsmodels.api as sm\n",
    "from statsmodels.formula.api import ols\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "olslist = {}\n",
    "for fuelnames in plantlist[\"fuel\"].unique():\n",
    "    plantlist_for_efficiency_analysis = plantlist[(plantlist.fuel==fuelnames) & (plantlist.efficiency_net.notnull()==True)]\n",
    "    if len(plantlist_for_efficiency_analysis.index)>=4:\n",
    "        efficiencyestimate = ols(\"efficiency_net  ~  commissioned_float + chp +uba_technology \", plantlist_for_efficiency_analysis).fit()\n",
    "        #Other possible inputs: + chp + capacity_float + status + np.square(commissioned_float) + np.log(commissioned_float)\n",
    "        #efficiencyestimate.summary()\n",
    "        olslist[fuelnames]=efficiencyestimate\n",
    "        #print(efficiencyestimate.params)\n",
    "        print(efficiencyestimate.summary())\n",
    "        #estimatedict = efficiencyestimate.params.to_dict()\n",
    "        #plantlist['efficiency_estimated'] = estimatedict['Intercept']\n",
    "        #for key in estimatedict:\n",
    "        #    if key != 'Intercept':\n",
    "        #        plantlist['efficiency_estimated'][plantlist.fuel==fuelnames] += estimatedict[key] * plantlist[key][plantlist.fuel==fuelnames]\n",
    "        \n",
    "        fig, ax = plt.subplots()\n",
    "        fig = sm.graphics.plot_fit(efficiencyestimate, 'commissioned_float',  ax=ax)\n",
    "        plt.ylabel(\"Efficiency\")\n",
    "        plt.xlabel(\"Commissioned\")\n",
    "        plt.title(fuelnames)\n",
    "        #plt.plot(plantlist['commissioned_float'][plantlist.fuel==fuelnames], plantlist['efficiency_net'][plantlist.fuel==fuelnames], 'ro')\n",
    "        #plt.plot(plantlist['commissioned_float'][plantlist.fuel==fuelnames], plantlist['efficiency_estimated'][plantlist.fuel==fuelnames], 'bo')\n",
    "        plt.legend(['Data', 'Fitted model'], loc=2)\n",
    "        #plt.axis([1900, 2020, 0.15, 1])\n",
    "        plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "#### Apply efficiency approximation from least squares approximation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Todo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Efficiencies from literature"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jonas Egerer, Clemens Gerbaulet, Richard Ihlenburg, Friedrich Kunz, Benjamin Reinhard, Christian von Hirschhausen, Alexander Weber, Jens Weibezahn (2014): **Electricity Sector Data for Policy-Relevant Modeling: Data Documentation and Applications to the German and European Electricity Markets**. DIW Data Documentation 72, Berlin, Germany."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data_efficiencies_literature=pd.read_csv('inputs/input_efficiency_literature_by_fuel_technology.csv',\n",
    "                                     sep=',',  # CSV field separator, default is ','\n",
    "                                     decimal='.',  # Decimal separator, default is '.')  \n",
    "                                     encoding='utf8')\n",
    "data_efficiencies_literature['technology'] = data_efficiencies_literature['technology'].str.upper()\n",
    "data_efficiencies_literature = data_efficiencies_literature.set_index(['fuel','technology'])\n",
    "data_efficiencies_literature"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Apply efficiency approximation from literature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plantlist = plantlist.join(data_efficiencies_literature,on=['fuel','technology'])\n",
    "plantlist['efficiency_literature'] = plantlist['efficiency_intercept']+plantlist['efficiency_slope']*plantlist[['commissioned_float','retrofit_float']].max(axis=1)\n",
    "plantlist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add geodata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_plant_locations=pd.read_csv('inputs/input_plant_locations_de.csv',\n",
    "                                     sep=';',  # CSV field separator, default is ','\n",
    "                                     decimal='.',  # Decimal separator, default is '.')  \n",
    "                                     encoding='cp1252')\n",
    "\n",
    "data_plant_locations = data_plant_locations.set_index('id')\n",
    "#data_plant_locations['googlemapslink'] = 'http://maps.google.com/maps?q=loc:' + data_plant_locations['lat'].map(str) + ',' + data_plant_locations['lon'].map(str) + '&t=h'\n",
    "\n",
    "data_plant_locations['lat'] = pd.to_numeric(data_plant_locations['lat'], errors='coerce')\n",
    "data_plant_locations['lon'] = pd.to_numeric(data_plant_locations['lon'], errors='coerce')\n",
    "\n",
    "plantlist = pd.merge(plantlist, data_plant_locations, left_index=True, right_index=True, how='left')\n",
    "plantlist.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Validity Checks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This section tests the results in various ways to find errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visual validity check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sum of capacity by fuel type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pivot_fuel_capacity = pd.pivot_table(\n",
    "                        plantlist, \n",
    "                        values='capacity', \n",
    "                        index='fuel', \n",
    "                        aggfunc=[np.sum]\n",
    "                        )\n",
    "pivot_fuel_capacity.sort_values(by='sum', inplace=True, ascending=0)\n",
    "#pivot_fuel_capacity\n",
    "pivot_fuel_capacity_plot=pivot_fuel_capacity.plot(kind='bar', legend=False, figsize=(12, 6))\n",
    "pivot_fuel_capacity_plot.set_ylabel(\"MW\")\n",
    "pivot_fuel_capacity_plot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Capacities by plant status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pivot_status_capacity = pd.pivot_table(\n",
    "                        plantlist, \n",
    "                        values='capacity',\n",
    "                        columns='status',\n",
    "                        index='fuel', \n",
    "                        aggfunc=np.sum\n",
    "                        )\n",
    "pivot_status_capacity.sort_values(by='operating', inplace=True, ascending=0)\n",
    "#pivot_status_capacity\n",
    "pivot_status_capacity_plot=pivot_status_capacity.plot(kind='barh', stacked=True,legend=True, figsize=(12, 6))\n",
    "pivot_status_capacity_plot.set_xlabel(\"MW\")\n",
    "pivot_status_capacity_plot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Power plant age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plantlist_filtered = plantlist#[plantlist.fuel=='coal']\n",
    "pivot_age_capacity = pd.pivot_table(\n",
    "                        plantlist_filtered, \n",
    "                        values='capacity',\n",
    "                        columns='fuel',\n",
    "                        index='commissioned', \n",
    "#                        index='fuel',\n",
    "#                        columns='commissioned',     \n",
    "                        aggfunc=np.sum,\n",
    "                        dropna=True\n",
    "                        )\n",
    "#pivot_age_capacity\n",
    "pivot_age_capacity_plot=pivot_age_capacity.plot(kind='bar', stacked=True,legend=True, figsize=(17, 10))\n",
    "pivot_age_capacity_plot.set_ylabel(\"MW\")\n",
    "pivot_age_capacity_plot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Block size vs year of commissioning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This chart is suitable to check outliers of commissioning years and block sizes. \n",
    "In theory, there should be no unexpected values, e.g. all commissioning years should be greater than 1900. \n",
    "Block sizes above 2000 MW are also unlikely."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plantlist_for_plot = plantlist.copy(deep=True)\n",
    "plantlist_for_plot['capacity_float'] = pd.to_numeric(plantlist_for_plot['capacity'], errors='coerce')\n",
    "plantlist_for_plot['commissioned_float'] = pd.to_numeric(plantlist_for_plot['commissioned'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plot_blocksize_year = Scatter(plantlist_for_plot, \n",
    "                              notebook=True, \n",
    "                              x='commissioned_float', \n",
    "                              y='capacity_float',\n",
    "                              color='fuel', \n",
    "                              title='Block-Size vs Year of Commissioning', \n",
    "                              xlabel='Year', \n",
    "                              ylabel='MW',\n",
    "                              legend=\"top_left\",\n",
    "                              height=500,\n",
    "                              width=700,\n",
    "                             )\n",
    "show(plot_blocksize_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Logical checks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Every power plant needs a capacity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List all entries with zero capacity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plantlist[plantlist.capacity == 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Commissioning Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Show all Plants with commisioning dates below 1900 \n",
    "plantlist[plantlist['commissioned_float'] <=1900]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Show all Plants with invalid commisioning dates\n",
    "plantlist[plantlist['commissioned_float'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare UBA and BNetzA data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Postcodes of BNetzA and UBA lists should match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# List all entries with diverging postcodes (if a postcode is given)\n",
    "plantlist[(plantlist['uba_postcode'].notnull() == True) & (pd.to_numeric(plantlist.postcode, errors='coerce') != pd.to_numeric(plantlist.uba_postcode, errors='coerce'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compare Installed capacities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "capacitycomparison = pd.DataFrame(plantlist.capacity / plantlist.uba_capacity)\n",
    "capacitycomparison['Name'] = plantlist.name\n",
    "capacitycomparison['Block'] = plantlist.block\n",
    "capacitycomparison['BnetzaCapacity'] = plantlist.capacity\n",
    "capacitycomparison['UBACapacity'] = plantlist.uba_capacity\n",
    "capacitycomparison.dropna(inplace=True)\n",
    "capacitycomparison.sort_values(by=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compare Comissioning Years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "\n",
    "commissioningcomparison = pd.DataFrame(plantlist.commissioned)\n",
    "commissioningcomparison['UBACommissioned'] = plantlist.uba_commissioned\n",
    "commissioningcomparison['commissioned_float'] = pd.to_numeric(commissioningcomparison['commissioned'],errors='coerce')\n",
    "commissioningcomparison['UBACommissioned_float'] = pd.to_numeric(commissioningcomparison['UBACommissioned'],errors='coerce')\n",
    "\n",
    "commissioningcomparison['Faktor'] = commissioningcomparison.commissioned_float - commissioningcomparison.UBACommissioned_float\n",
    "commissioningcomparison['Name'] = plantlist.name\n",
    "commissioningcomparison['Block'] = plantlist.block\n",
    "\n",
    "commissioningcomparison.dropna(subset = ['commissioned'], inplace=True)\n",
    "commissioningcomparison.dropna(subset = ['UBACommissioned'], inplace=True)\n",
    "commissioningcomparison = commissioningcomparison.sort_values(by='Faktor')\n",
    "\n",
    "commissioningcomparison"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define final output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Merge uba_name_aggregate and uba_name\n",
    "plantlist['uba_name_aggregate'][plantlist['uba_name_aggregate'].isnull()] = plantlist['uba_name']\n",
    "\n",
    "# Drop columns not relevant for output\n",
    "colsToDrop = ['bnetza_id',\n",
    "              'capacity',\n",
    "              'uba_name',\n",
    "              'uba_capacity_original',\n",
    "              'uba_chp_capacity_original',\n",
    "              'uba_city', \n",
    "              'uba_commissioned', \n",
    "              'uba_company', \n",
    "              'uba_company_aggregate', \n",
    "              'uba_fuel', \n",
    "              'uba_postcode', \n",
    "              'uba_state', \n",
    "              'uba_technology', \n",
    "              'uba_technology_aggregate', \n",
    "              'retrofit',\n",
    "              'uba_commissioned_1', \n",
    "              'uba_commissioned_2', \n",
    "              'uba_commissioned_3', \n",
    "              'commissioned_1', \n",
    "              'commissioned_2', \n",
    "              'commissioned_3', \n",
    "              'fuel_basis', \n",
    "              'fuel_multiple1', \n",
    "              'fuel_multiple2',\n",
    "              'efficiency_gross',\n",
    "              'efficiency_intercept',\n",
    "              'efficiency_slope',\n",
    "              'efficiency_source',\n",
    "              'source_type',\n",
    "              'date',\n",
    "              'location_checked',\n",
    "             ]\n",
    "plantlist = plantlist.drop(colsToDrop, axis=1)\n",
    "\n",
    "# Rename columns\n",
    "plantlist = plantlist.rename(columns={'commissioned':'commissioned_original', \n",
    "                                      'commissioned_float':'commissioned', \n",
    "                                      'retrofit_float':'retrofit', \n",
    "                                      'capacity_float':'capacity',\n",
    "                                      'uba_capacity':'capacity_uba', \n",
    "                                      'uba_chp_capacity':'chp_capacity_uba', \n",
    "                                      'efficiency_net':'efficiency_data', \n",
    "                                      'efficiency_literature':'efficiency_estimate', \n",
    "                                      'uba_name_aggregate':'name_uba'})\n",
    "\n",
    "# Sort columns\n",
    "columns_sorted = ['company',\n",
    "                 'name',\n",
    "                 'postcode',\n",
    "                 'city',\n",
    "                 'street',\n",
    "                 'state',\n",
    "                 'block',\n",
    "                 'commissioned_original',\n",
    "                 'commissioned',\n",
    "                 'retrofit',\n",
    "                 'shutdown',\n",
    "                 'status',\n",
    "                 'fuel',\n",
    "                 'technology',\n",
    "                 'type',\n",
    "                 'eeg',\n",
    "                 'chp',\n",
    "                 'capacity',\n",
    "                 'capacity_uba',\n",
    "                 'chp_capacity_uba',\n",
    "                 'efficiency_data',\n",
    "                 'efficiency_estimate',\n",
    "                 'network_node',\n",
    "                 'voltage',\n",
    "                 'network_operator',\n",
    "                 'name_uba',\n",
    "                 'lat',\n",
    "                 'lon',\n",
    "                 'comment']\n",
    "plantlist = plantlist.reindex(columns=columns_sorted)\n",
    "\n",
    "plantlist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Documenting the data package (meta data)"
   ]
  },
  {
   "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": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Here we define meta data of the resulting data package.\n",
    "# The meta data follows the specification at:\n",
    "# http://dataprotocols.org/data-packages/\n",
    "\n",
    "metadata = \"\"\"\n",
    "\n",
    "name: opsd-power-plants-germany\n",
    "title: List of power plants in Germany.\n",
    "description: This dataset contains an augmented and corrected power plant list based on the power plant list provided by the BNetzA.\n",
    "version: 0.0.1\n",
    "keywords: [power plants,germany]\n",
    "\n",
    "resources:\n",
    "    path: power_plants_germany.csv\n",
    "    format: csv\n",
    "    mediatype: text/csv\n",
    "    schema:  # Schema according to: http://dataprotocols.org/json-table-schema/        \n",
    "        fields:\n",
    "            - name: id\n",
    "              description: Power plant ID based on the ID provided in the BNetzA-list.\n",
    "              type: string\n",
    "            - name: company\n",
    "              description: Company name\n",
    "              type: string\n",
    "            - name: name\n",
    "              description: Power plant name\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: postcode\n",
    "              description: Postcode\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: city\n",
    "              description: City\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: street\n",
    "              description: Street\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: state\n",
    "              description: State\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: block\n",
    "              description: Power plant block \n",
    "              type: string\n",
    "              format: default\n",
    "            - name: commissioned_original\n",
    "              description: Year of commissioning (raw data)\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: commissioned\n",
    "              description: Year of commissioning \n",
    "              type: integer\n",
    "              format: default\n",
    "            - name: retrofit\n",
    "              description: Year of modernization according to UBA data\n",
    "              type: integer\n",
    "              format: default\n",
    "            - name: shutdown\n",
    "              description: Year of decommissioning\n",
    "              type: integer\n",
    "              format: default\n",
    "            - name: status\n",
    "              description: Power plant status\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: fuel\n",
    "              description: Used fuel or energy source\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: technology\n",
    "              description: Power plant technology or sort\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: type\n",
    "              description: Purpose of the produced power\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: eeg\n",
    "              description: Status of being entitled to a renumeration\n",
    "              type: boolean\n",
    "              format: default\n",
    "            - name: chp\n",
    "              description: Status of being able to supply heat\n",
    "              type: boolean\n",
    "              format: default\n",
    "            - name: capacity\n",
    "              description: Power capacity\n",
    "              type: number\n",
    "              format: default\n",
    "            - name: capacity_uba\n",
    "              description: Power capacity according to UBA data\n",
    "              type: number\n",
    "              format: default\n",
    "            - name: chp_capacity_uba\n",
    "              description: Heat capacity according to UBA data\n",
    "              type: number\n",
    "              format: default\n",
    "            - name: efficiency_data\n",
    "              description: Proportion between power output and input\n",
    "              type: number\n",
    "              format: default\n",
    "            - name: efficiency_estimate\n",
    "              description: Estimated proportion between power output and input\n",
    "              type: number\n",
    "              format: default\n",
    "            - name: network_node\n",
    "              description: Connection point to the electricity grid \n",
    "              type: string\n",
    "              format: default\n",
    "            - name: voltage\n",
    "              description: Grid or transformation level of the network node\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: network_operator\n",
    "              description: Network operator of the grid or transformation level\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: uba_name_aggregate\n",
    "              description: Power plant name according to UBA data\n",
    "              type: string\n",
    "              format: default\n",
    "            - name: lat\n",
    "              description: Precise geographic coordinates - latitude\n",
    "              type: number\n",
    "              format: default\n",
    "            - name: lon\n",
    "              description: Precise geographic coordinates - longitude\n",
    "              type: number\n",
    "              format: default\n",
    "            - name: comment\n",
    "              description: Further comments\n",
    "              type: string\n",
    "              format: default\n",
    "\n",
    "licenses:\n",
    "    - url: http://example.com/license/url/here\n",
    "      name: License Name Here\n",
    "      version: 1.0\n",
    "      id: license-id-from-open\n",
    "\n",
    "sources:\n",
    "    - name: BNetzA Kraftwerksliste,\n",
    "      web: http://www.bundesnetzagentur.de/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/Versorgungssicherheit/Erzeugungskapazitaeten/Kraftwerksliste/kraftwerksliste-node.html\n",
    "    - name: Umweltbundesamt Datenbank Kraftwerke in Deutschland,\n",
    "      web: http://www.umweltbundesamt.de/dokument/datenbank-kraftwerke-in-deutschland\n",
    "\n",
    "maintainers:\n",
    "    - name: OPSD-Team,\n",
    "      email: OPSD-Team-email,\n",
    "      web: http://open-power-system-data.org/\n",
    "\n",
    "views:\n",
    "    # You can put hints here which kind of graphs or maps make sense to display your data. This makes the \n",
    "    # Data Package Viewer at http://data.okfn.org/tools/view automatically display visualazations of your data.\n",
    "    # See http://data.okfn.org/doc/data-package#views for more details.    \n",
    "\n",
    "# extend your datapackage.json with attributes that are not\n",
    "# part of the data package spec\n",
    "# you can add your own attributes to a datapackage.json, too\n",
    "\n",
    "openpowersystemdata-enable-listing: True  # This is just an example we don't actually make use of yet.\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "metadata = yaml.load(metadata)\n",
    "\n",
    "datapackage_json = json.dumps(metadata, indent=4, separators=(',', ': '))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Write the results to file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write the outputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "output_path = 'output/datapackage_powerplants_germany/'\n",
    "output_path2 = 'output/datapackage_powerplants_germany'\n",
    "\n",
    "#Write the result to file\n",
    "plantlist.to_csv(output_path+'power_plants_germany.csv', encoding='utf-8')\n",
    "\n",
    "#Write the results to excel file\n",
    "plantlist.to_excel(output_path+'power_plants_germany.xlsx', sheet_name='output')\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)\n",
    "\n",
    "#Set this string to this notebook's filename!    \n",
    "nb_filename = 'Power_Plants_DE.ipynb'\n",
    "\n",
    "# Save a copy of the notebook to markdown, to serve as the package README file\n",
    "subprocess.call(['ipython', 'nbconvert', '--to', 'markdown', nb_filename])\n",
    "path_readme = os.path.join(output_path2, 'README.md')\n",
    "try:\n",
    "    os.remove(path_readme)\n",
    "except Exception:\n",
    "    pass\n",
    "os.rename(nb_filename.replace('.ipynb', '.md'), path_readme)    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}