<jupyter_start><jupyter_text>This script downlads and extracts the original data of renewable power plant lists from the data sources, processes and merges them. It subsequently adds the geolocation for each power plant. Finally it saves the DataFrames as pickle-files. Make sure you run the download and process Notebook before the validation and output Notebook.  Table of contents * [1. Script setup](1.-Script-setup)* [2. Settings](2.-Settings)    * [2.1 Choose download option](2.1-Choose-download-option)    * [2.2 Download function](2.2-Download-function)    * [2.3 Setup translation dictionaries](2.3-Setup-translation-dictionaries)* [3. Download and process per country](3.-Download-and-process-per-country)    * [3.5 Greece](3.5-Greece)        * [3.5.1 Download and read](3.5.1-Download-and-read)        * [3.5.2 Translate column names](3.5.2-Translate-column-names)        * [3.5.3 Standardize energy source information](3.5.3-Standardize-energy-source-information)        * [3.5.4 Add information about municipalities](3.5.4-Add-information-about-municipalities)        * [3.5.5 Geographical data](3.5.5-Geographical-data)        * [3.5.6 Transform to latin alphabet and match municipalities to the GADM names using the Levenshtein distance](3.5.6-Transform-to-latin-alphabet-and-match-municipalities-to-the-GADM-names-using-the-Levenshtein-distance)        * [3.5.7 Aggregate data by municipality and technology and assign geographical coordinates](3.5.7-Aggregate-data-by-municipality-and-technology-and-assign-geographical-coordinates)        * [3.5.8 Save](3.5.8-Save)* [Part 2: Validation and output](validation_and_output.ipynb)  1. Script setup<jupyter_code>import logging
import os
import posixpath
import urllib.parse
import urllib.request
import re
import zipfile

import numpy as np
import pandas as pd
from collections import OrderedDict
import datetime
import utm  # for transforming geoinformation in the utm format
import requests 


logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    datefmt='%d %b %Y %H:%M:%S'
)

logger = logging.getLogger()

# Create input and output folders if they don't exist
os.makedirs('input/original_data', exist_ok=True)
os.makedirs('output', exist_ok=True)
os.makedirs('output/renewable_power_plants', exist_ok=True)<jupyter_output><empty_output><jupyter_text>2. Settings  2.1 Choose download optionThe original data can either be downloaded from the original data sources as specified below or from the opsd-Server. Default option is to download from the original sources as the aim of the project is to stay as close to original sources as possible. However, if problems with downloads e.g. due to changing urls occur, you can still run the script with the original data from the opsd_server.<jupyter_code>download_from = 'original_sources'
# download_from = 'opsd_server' 
if download_from == 'opsd_server':

    # Specify direction to original_data folder on the opsd data server
    url_opsd = 'http://data.open-power-system-data.org/renewables_power_plants/'
    version = '2017-02-16'
    folder = '/original_data'
    session = requests.Session()<jupyter_output><empty_output><jupyter_text>2.2 Download function<jupyter_code>def download_and_cache(url, session=None):
    """This function downloads a file into a folder called 
    original_data and returns the local filepath."""
    path = urllib.parse.urlsplit(url).path
    filename = posixpath.basename(path)
    filepath = "input/original_data/" + filename
    print(url)

    # check if file exists, if not download it
    filepath = "input/original_data/" + filename
    print(filepath)
    if not os.path.exists(filepath):
        if not session:
            print('No session')
            session = requests.session()
        
        print("Downloading file: ", filename)
        r = session.get(url, stream=True)

        chuncksize = 1024
        with open(filepath, 'wb') as file:
            for chunck in r.iter_content(chuncksize):
                file.write(chunck)
    else:
        print("Using local file from", filepath)
    filepath = '' + filepath
    return filepath<jupyter_output><empty_output><jupyter_text>2.3 Setup translation dictionariesColumn and value names of the original data sources will be translated to English and standardized across different sources. Standardized column names, e.g. "electrical_capacity" are required to merge data in one DataFrame.The column and the value translation lists are provided in the input folder of the Data Package.<jupyter_code># Get column translation list
columnnames = pd.read_csv('input/column_translation_list.csv')
# Get value translation list
valuenames = pd.read_csv('input/value_translation_list.csv')<jupyter_output><empty_output><jupyter_text>3. Download and process per countryFor one country after the other, the original data is downloaded, read, processed, translated, eventually georeferenced and saved. If respective files are already in the local folder, these will be utilized.To process the provided data [pandas DataFrame](http://pandas.pydata.org/pandas-docs/stable/dsintro.htmldataframe) is applied.  3.5 Greece  3.5.1 Download and readThe data which will be processed below is provided by:**[resoffice.gr](https://www.resoffice.gr)** - Information Registry of Operating Stations (only for renewable energy and highly efficient co-generation plants) provided by the General Secretariat of Energy and Mineral Raw Materials of the Ministry of Environment and Energy of Greece. The data is provided by the **[Regulatory Authority for Energy](http://www.rae.gr/site/en_US/portal.csp)**, **[Operator of Electricity Market](http://www.lagie.gr/nc/en/home/)**, **[Independent Power Transmission Operator](http://www.admie.gr/nc/en/home/)** and **[Hellenic Electricity Distribution Network Operator S.A.](https://deddie.gr/en)**<jupyter_code># Download the table with all RES sources including large hydro
if download_from == 'original_sources':
    
    url_GR = "https://www.resoffice.gr/file/reg/stations_1.jsp"

else:
    url_GR = (url_opsd + version + folder + '/GR/stations_1.jsp')

    
# Get data of renewables per project
GR_df = pd.read_excel(download_and_cache(url_GR),
                      encoding='UTF8')

# drop nan rows
GR_df.dropna(how='all', inplace=True)<jupyter_output><empty_output><jupyter_text>3.5.2 Translate column namesTo standardise the DataFrame the original column names wil be translated and new english column names wil be assigned to the DataFrame. The unique column names are required to merge the DataFrame.The column_translation_list is provided here as csv in the input folder. It is loaded in _2.3 Setup of translation dictionaries_.<jupyter_code>idx_GR = columnnames[(columnnames['country'] == 'GR') & (columnnames['data_source'] == 'resoffice.gr')].index
column_dict_GR = columnnames.loc[idx_GR].set_index('original_name')['opsd_name'].to_dict()
column_dict_GR
# Translate columnnames and drop the non useful ones
GR_df.rename(columns = column_dict_GR, inplace=True)
GR_df = GR_df[list(set(column_dict_GR.values()))]
# add data source
GR_df['data_source'] = 'resoffice.gr'
# Choose the translation terms for Greece, create dictionary and show dictionary
idx_GR = valuenames[(valuenames['country'] == 'GR') & (valuenames['data_source'] == 'resoffice.gr')].index
value_dict_GR = valuenames.loc[idx_GR].set_index('original_name')['opsd_name'].to_dict()
value_dict_GR
# Replace all original value names by the OPSD value names
GR_df.replace(value_dict_GR, inplace=True)

# drop nan capacity rows
GR_df.drop(GR_df[GR_df['electrical_capacity'].isnull()].index, inplace=True)<jupyter_output><empty_output><jupyter_text>3.5.3 Standardize energy source informationAssign the energy_source_level categories as well as technology and source<jupyter_code># Create dictionary in order to assign energy_source to its subtype
energy_source_dict_GR = valuenames.loc[idx_GR].set_index('opsd_name')['energy_source_level_2'].to_dict()

GR_df['energy_source_level_1'] = 'Renewable energy'

GR_df['energy_source_level_2'] = GR_df['technology']
GR_df['energy_source_level_2'].replace(energy_source_dict_GR, inplace=True)

biomass_ind = GR_df[GR_df['technology']=='Biomass and biogas'].index
GR_df.loc[biomass_ind, 'energy_source_level_2'] = 'Bioenergy'
GR_df.loc[biomass_ind, 'energy_source_level_3'] = 'Biomass and biogas'

GR_df.loc[biomass_ind, 'technology'] = np.NaN

GR_df['source'] = GR_df['technology']
GR_df.loc[biomass_ind, 'source'] = 'Biomass'<jupyter_output><empty_output><jupyter_text>3.5.4 Add information about municipalitiesSince the original table contains geographical data merely aggregated at regional level, more detailed information is obtained by visiting the registry table of each project individually<jupyter_code>%%time
# spawn multiple asynchronous threads to reduce the waiting time for the server response
# thread pool example taken from
# https://docs.python.org/3/library/concurrent.futures.html#threadpoolexecutor-example

import concurrent.futures
from urllib.request import urlopen
from bs4 import BeautifulSoup

URLS = ["https://www.resoffice.gr/file/reg/view.jsp?mas="+str(code_number) for code_number in GR_df['code_number']]
MAX_WORKERS = 100  # it takes approx. 3 minutes for 100 threads

# Retrieve a single page and report the URL and contents
def load_url(url, timeout):
    with urlopen(url, timeout=timeout) as conn:
        return conn.read()

data = {}
# We can use a with statement to ensure threads are cleaned up promptly
with concurrent.futures.ThreadPoolExecutor(max_workers=MAX_WORKERS) as executor:
    # Start the load operations and mark each future with its URL
    future_to_url = {executor.submit(load_url, url, 60): url for url in URLS}
    for future in concurrent.futures.as_completed(future_to_url):
        url = future_to_url[future]
        try:
            page = future.result()
        except Exception as exc:
            print('%r generated an exception: %s' % (url, exc))
        else:
            #print('%r page is %d bytes' % (url, len(page)))
            pass
            
        # retrieve the municipality name
        soup = BeautifulSoup(page, "lxml")

        table = soup.find_all('table', attrs={'class':'table'})
        rows = table[1].find_all('tr')

        for rid,row in enumerate(rows):
            cols = row.find_all('td')  # contains the column values for each table row
            cols = [ele.text.strip() for ele in cols]
            if rid == 4:  # the fourth row contains the location information ("Θέση εγκατάστασης:")
                try:
                    # the third column holds the municipality name
                    data.update({int(url.split('=')[1]): [ele for ele in cols if ele][3]})
                except:
                    #print('Could not append for page', url)
                    data.update({int(url.split('=')[1]): np.NaN})
                break
                
# append data to dataframe
GR_df.set_index('code_number', inplace=True)
GR_df['municipality'] = pd.Series(data)<jupyter_output><empty_output><jupyter_text>3.5.5 Geographical dataData can be downloaded from: **[Global Administrative Areas](http://www.gadm.org/)**License: "This dataset is freely available for academic use and other non-commercial use. Redistribution, or commercial use is not allowed without prior permission. You are free to create maps and use the data in other ways for publication in academic journals, books, reports, etc."<jupyter_code># The shapefile format is used for extraction of the geographical information
try: 
    import geopandas as gpd
    shp = gpd.read_file(os.path.join('GRC_adm_shp', 'GRC_adm3.shp')).set_index('NAME_3')
    centroids = pd.DataFrame(columns=['lon', 'lat'], index=shp.index)
    centroids.index.name = 'name'
    centroids['lon'] = [p.x for p in shp.centroid]
    centroids['lat'] = [p.y for p in shp.centroid]
except ImportError:
    try:
        print('No geopandas installed, better try the csv file')
        centroids = pd.read_csv('municipality_centroids.csv', index_col='name')
    except OSError:
        raise OSError('Could not find a source for the municipality coordinates')<jupyter_output><empty_output><jupyter_text>3.5.6 Transform to latin alphabet and match municipalities to the GADM names using the Levenshtein distance<jupyter_code>def to_greeklish(s):
    '''
    Transform a string from Greek to Latin alphabet and bring them to String1-String2 format.
    Moreover try to transform from genitive to nominative clause, unfortunately there is no universal rule for that.
    '''
    
    # dictionary to latin alphabet
    greeklish = dict(zip(list('ΑΒΓΔΕΖΗΙΚΛΜΝΞΟΠΡΣΤΥΦΩΪ'), list('avgdeziiklmnxoprstyfoi')))
    greeklish.update({'Θ': 'th', 'Χ': 'ch', 'Ψ': 'ps'})
    
    if isinstance(s, str):
        
        s = ''.join(str(e) for e in [greeklish[char] if char in greeklish.keys() else char for char in s ])
        s = s.replace('oy', 'ou')
        
        # replace all separators with dash
        p = re.compile(',|&|\\bkai\\b')
        s = p.sub('-', s)
        
        # remove whitespaces before and after dashes
        p = re.compile('\s*-\s*')
        s = p.sub('-', s)
        
        # replace some greek terms that in GADM appear in English
        d = {'kentrik': 'central', 'anat': 'east', 'vorei': 'north', 'noti': 'south', 
             'dytik': 'west', 'limni': 'lake', 'mikr': 'lesser'}
        for greek in d.keys():
            p = re.compile('\w+')
            startswith = re.compile(greek, re.I)
            to_replace = [e for e in p.findall(s) if startswith.search(e)]
            if to_replace:
                p = re.compile(''.join(e+'|' for e in to_replace)[:-1])
                s = p.sub(d[greek], s)
                
        #try to transform to nominative
        toNominative = OrderedDict([('iou', 'io'), ('s', ''), ('ou', 'os'), ('on', 'a')])
        for phrase in toNominative.keys():
            p = re.compile('\w+')
            to_replace = [e for e in p.findall(s) if e.endswith(phrase)]
            if to_replace:
                for original in to_replace:
                    p = re.compile(original)
                    s = p.sub(original[:-len(phrase)]+toNominative[phrase], s)
        
        # cut off more than two municipalities
        positions = [pos for pos, char in enumerate(s) if char == '-']
        if len(positions) > 1:
            s = s[:positions[1]]
        
        # remove whitespaces in head and tail
        s = s.strip()
        
        s = s.title()
        
    return s

GR_df['municipality'] = GR_df.municipality.apply(to_greeklish)
engNames = [s for s in set(GR_df['municipality']) if isinstance(s, str)]
def compare_strings(string, string_list):
    import Levenshtein  # https://github.com/ztane/python-Levenshtein/
    match = np.array([Levenshtein.distance(string, s) for s in string_list])
    return match.argmin(), match.min()

# consider only the first out of the two locations for matching
df_names_cut = {s: s.split('-')[0] 
                if isinstance(s, str) and not s.startswith('Agio')
                else s for s in set(GR_df['municipality'])}


toShpNamesDict = {df_name: [shp.index[compare_strings(df_name, centroids.index)[0]],
                            shp.index[compare_strings(df_names_cut[df_name], centroids.index)[0]]]
                  
                           # pick the match with the least score
                           [np.array([compare_strings(df_name, centroids.index)[1],
                                      compare_strings(df_names_cut[df_name], centroids.index)[1]]).argmin()]
                  
                  for df_name in set(GR_df['municipality']) if isinstance(df_name, str)}
## add some changes manually when the algorithm fails
toShpNamesDict.update({'<NAME>': 'Agioi Anargyroi-Kamatero',
                       'Athinaia': 'Athens',
                       'Chalkidea': 'Chalcis',
                       'Delfa': 'Delphi',
                       'Distomos-Arachova': 'Distomo-Arachova-Antikyra',
                       'Iera Poli Mesologgio': 'Missolonghi',
                       'Iita': 'Ios',
                       'Kerkyra': 'Corfu',
                       'Loutrakio-Agia Theodora': 'Loutraki-Agioi Theodoroi',
                       'Mantoudio-Lake': 'Mantoudi-Limni-Agia Anna',
                       'Naxos-Lesser Kyklada': 'Naxos and Lesser Cyclades',
                       'Patrea': 'Patras',
                       'Peiraio': 'Piraeus',
                       'Salamino': 'Salamis Island',
                       'Thira': 'Santorini',
                       'Thivaia': 'Thebes',
                       'Troizinia': 'Troizinia-Methana',
                       'Vari-Voula': 'Vari-Voula-Vouliagmeni'
                       })<jupyter_output><empty_output><jupyter_text>3.5.7 Aggregate data by municipality and technology and assign geographical coordinates<jupyter_code>## group by municipality and source
agg_func = {col: 'first' for col in GR_df.columns.difference(['municipality', 'source'])}
agg_func.update({'electrical_capacity': 'sum'})
GR_df = GR_df.replace(toShpNamesDict).groupby(['municipality', 'source']).agg(agg_func).reset_index(level=1)

## add centroids
GR_df['lon'] = centroids['lon']
GR_df['lat'] = centroids['lat']

# reset index to avoid duplicate indices
GR_df.reset_index(inplace=True)<jupyter_output><empty_output><jupyter_text>3.5.8 Save<jupyter_code>GR_df.to_pickle('GR_renewables.pickle')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11199726558049447, 'fraction_numerical': 0.009000797539022444, 'mean_word_length': 3.4041645760160564, 'pattern_counts': {'":': 0, '<': 50, '<?xml version=': 0, '>': 51, 'https://': 6, 'lorem ipsum': 0, 'www.': 7, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 29, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '571550', 'license': 'MIT', 'n_tokens_mistral': 5533, 'n_tokens_neox': 5335, 'n_words': 1511, 'path': 'download_and_process-Greece.ipynb', 'repo_name': 'ksyranid/RESPlantsGreece', 'size': '17571'}