<commit_before>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Open Power System Data: Renewable Energy Power Plant List\n",
    "\n",
    "\n",
    "## Part 1: Download and Processing the Original Data\n",
    "\n",
    "This script downlads and extracts the original data from the sources and merges them in a single data\n",
    "frame. It subsequently adds the geolocation for each power plant and gives an overview over the\n",
    "structure of the data frame. Finally it saves the data frame to a SQLite file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of contents \n",
    "\n",
    "* [1. Script Setup](#1.-Script-Setup)\n",
    "* [2. Download and Data Extraction](#2.-Download-and-Data-Extraction)\n",
    "    * [2.1 Download and Create Individual Data Frames](##-2.1-Download-and-Create-Individual-Data-Frames)\n",
    "    * [2.2 Definition of Data Frame Structure](##-2.2-Definition-of-Data-Frame-Structure)\n",
    "    * [2.3 Merge Data Frames](##2.3-Merge-Data-Frames)\n",
    "    * [2.4 Translation and Summary](##2.4-Translation-and-Summary)\n",
    "* [3 Georeferencing](#3-Georeferencing)\n",
    "    * [3.1 Get Coordinates by Postcode](##-3.1-Get-Coordinates-by-Postcode)\n",
    "    * [3.2 Transform Geoinformation](##3.2-Transform-Geoinformation)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Script Setup\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# importing all necessary Python libraries for this Script\n",
    "#%matplotlib inline\n",
    "\n",
    "from collections import OrderedDict\n",
    "import io\n",
    "import json\n",
    "import os\n",
    "import subprocess\n",
    "import zipfile\n",
    "import posixpath\n",
    "import urllib.parse\n",
    "import urllib.request\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "# not in use\n",
    "# import requests\n",
    "import datetime  \n",
    "import sqlite3 \n",
    "import utm\n",
    "import logging\n",
    "\n",
    "# Set up a log\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'\\\n",
    "                              '- %(message)s',datefmt='%d %b %Y %H:%M:%S')\n",
    "nb_root_logger.handlers[0].setFormatter(formatter)\n",
    "\n",
    "# Create input and output folders if they don't exist\n",
    "os.makedirs('input/original_data', exist_ok=True)\n",
    "os.makedirs('output', exist_ok=True)\n",
    "os.makedirs('output/datapackage_renewables', exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Download and Data Extraction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data which will be processed below is provided by the following sources:\n",
    "\n",
    "**1. [Netztransparenz.de](https://www.netztransparenz.de/de/Anlagenstammdaten.htm)** - Official grid transparency platform from the German TSOs (50Hertz, Amprion, TenneT and TransnetBW).\n",
    "\n",
    "**2. Bundesnetzagentur (BNetzA)** - German Federal Network Agency for Electricity, Gas, Telecommunications, Posts and Railway (Data for [roof-mounted PV power plants](http://www.bundesnetzagentur.de/cln_1422/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/ErneuerbareEnergien/Photovoltaik/DatenMeldgn_EEG-VergSaetze/DatenMeldgn_EEG-VergSaetze_node.html) and for [all other renewable energy power plants](http://www.bundesnetzagentur.de/cln_1412/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/ErneuerbareEnergien/Anlagenregister/Anlagenregister_Veroeffentlichung/Anlagenregister_Veroeffentlichungen_node.html))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Download and Create Individual Data Frames\n",
    "\n",
    "**Specify the source URLs:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# point URLs to original data\n",
    "url_netztransparenz ='https://www.netztransparenz.de/de/file/'\\\n",
    "                  'Anlagenstammdaten_2014_4UeNB.zip'\n",
    "\n",
    "url_bnetza ='http://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/'\\\n",
    "            'Energie/Unternehmen_Institutionen/ErneuerbareEnergien/Anlagenregister/'\\\n",
    "            'VOeFF_Anlagenregister/2016_04_Veroeff_AnlReg.xls?__blob=publicationFile&v=2'\n",
    "        \n",
    "url_bnetza_pv = 'https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/'\\\n",
    "                'Sachgebiete/Energie/Unternehmen_Institutionen/'\\\n",
    "                'ErneuerbareEnergien/Photovoltaik/Datenmeldungen/'\\\n",
    "                'Meldungen_Aug-Mrz2016.xls?__blob=publicationFile&v=2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Download Data:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def download_and_cache(url):\n",
    "    \"\"\"This function downloads a file into a folder called \n",
    "    original_data and returns the local filepath.\"\"\"\n",
    "    logger_script.info(\"Downloading: \"+url)\n",
    "    path = urllib.parse.urlsplit(url).path\n",
    "    filename = posixpath.basename(path)\n",
    "    filepath = \"input/original_data/\" + filename\n",
    "\n",
    "    # check if file exists, if not download it\n",
    "    # changed from == False to not\n",
    "    if not os.path.exists(filepath):\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Unzip and load downloaded data as data frames**\n",
    "\n",
    "To process the provided data [pandas DataFrame](http://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe) is applied.<br>\n",<commit_msg>Removed reference to my own script version of the notebook<commit_after>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Open Power System Data: Renewable Energy Power Plant List\n",
    "\n",
    "\n",
    "## Part 1: Download and Processing the Original Data\n",
    "\n",
    "This script downlads and extracts the original data from the sources and merges them in a single data\n",
    "frame. It subsequently adds the geolocation for each power plant and gives an overview over the\n",
    "structure of the data frame. Finally it saves the data frame to a SQLite file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of contents \n",
    "\n",
    "* [1. Script Setup](#1.-Script-Setup)\n",
    "* [2. Download and Data Extraction](#2.-Download-and-Data-Extraction)\n",
    "    * [2.1 Download and Create Individual Data Frames](##-2.1-Download-and-Create-Individual-Data-Frames)\n",
    "    * [2.2 Definition of Data Frame Structure](##-2.2-Definition-of-Data-Frame-Structure)\n",
    "    * [2.3 Merge Data Frames](##2.3-Merge-Data-Frames)\n",
    "    * [2.4 Translation and Summary](##2.4-Translation-and-Summary)\n",
    "* [3 Georeferencing](#3-Georeferencing)\n",
    "    * [3.1 Get Coordinates by Postcode](##-3.1-Get-Coordinates-by-Postcode)\n",
    "    * [3.2 Transform Geoinformation](##3.2-Transform-Geoinformation)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Script Setup\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# importing all necessary Python libraries for this Script\n",
    "#%matplotlib inline\n",
    "\n",
    "from collections import OrderedDict\n",
    "import io\n",
    "import json\n",
    "import os\n",
    "import subprocess\n",
    "import zipfile\n",
    "import posixpath\n",
    "import urllib.parse\n",
    "import urllib.request\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "# not in use\n",
    "# import requests\n",
    "import datetime  \n",
    "import sqlite3 \n",
    "import utm\n",
    "import logging\n",
    "\n",
    "# Set up a log\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'\\\n",
    "                              '- %(message)s',datefmt='%d %b %Y %H:%M:%S')\n",
    "nb_root_logger.handlers[0].setFormatter(formatter)\n",
    "\n",
    "# Create input and output folders if they don't exist\n",
    "os.makedirs('input/original_data', exist_ok=True)\n",
    "os.makedirs('output', exist_ok=True)\n",
    "os.makedirs('output/datapackage_renewables', exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Download and Data Extraction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data which will be processed below is provided by the following sources:\n",
    "\n",
    "**1. [Netztransparenz.de](https://www.netztransparenz.de/de/Anlagenstammdaten.htm)** - Official grid transparency platform from the German TSOs (50Hertz, Amprion, TenneT and TransnetBW).\n",
    "\n",
    "**2. Bundesnetzagentur (BNetzA)** - German Federal Network Agency for Electricity, Gas, Telecommunications, Posts and Railway (Data for [roof-mounted PV power plants](http://www.bundesnetzagentur.de/cln_1422/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/ErneuerbareEnergien/Photovoltaik/DatenMeldgn_EEG-VergSaetze/DatenMeldgn_EEG-VergSaetze_node.html) and for [all other renewable energy power plants](http://www.bundesnetzagentur.de/cln_1412/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/ErneuerbareEnergien/Anlagenregister/Anlagenregister_Veroeffentlichung/Anlagenregister_Veroeffentlichungen_node.html))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Download and Create Individual Data Frames\n",
    "\n",
    "**Specify the source URLs:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# point URLs to original data\n",
    "url_netztransparenz ='https://www.netztransparenz.de/de/file/'\\\n",
    "                  'Anlagenstammdaten_2014_4UeNB.zip'\n",
    "\n",
    "url_bnetza ='http://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/'\\\n",
    "            'Energie/Unternehmen_Institutionen/ErneuerbareEnergien/Anlagenregister/'\\\n",
    "            'VOeFF_Anlagenregister/2016_04_Veroeff_AnlReg.xls?__blob=publicationFile&v=2'\n",
    "        \n",
    "url_bnetza_pv = 'https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/'\\\n",
    "                'Sachgebiete/Energie/Unternehmen_Institutionen/'\\\n",
    "                'ErneuerbareEnergien/Photovoltaik/Datenmeldungen/'\\\n",
    "                'Meldungen_Aug-Mrz2016.xls?__blob=publicationFile&v=2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Download Data:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def download_and_cache(url):\n",
    "    \"\"\"This function downloads a file into a folder called \n",
    "    original_data and returns the local filepath.\"\"\"\n",
    "    path = urllib.parse.urlsplit(url).path\n",
    "    filename = posixpath.basename(path)\n",
    "    filepath = \"input/original_data/\" + filename\n",
    "\n",
    "    # check if file exists, if not download it\n",
    "    # changed from == False to not\n",
    "    if not os.path.exists(filepath):\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Unzip and load downloaded data as data frames**\n",
    "\n",
    "To process the provided data [pandas DataFrame](http://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe) is applied.<br>\n",
{'fraction_non_alphanumeric': 0.1736197270471464, 'fraction_numerical': 0.010235732009925558, 'mean_word_length': 2.882299819385912, 'pattern_counts': {'":': 88, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 6, 'lorem ipsum': 0, 'www.': 12, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 33, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7189913', 'n_tokens_mistral': 4745, 'n_tokens_neox': 4596, 'n_words': 892}