MSP-EMODNet_exporter.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MSP Italy - EMODnet Exporter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from PIL import Image, ImageDraw, ImageFont\n",
    "import fiona\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eu_contries 37\n",
      "uses_pu 4169\n",
      "regioni_istat 20\n",
      "land_EEA_2018 27\n",
      "areas 3\n",
      "subareas 27\n",
      "planning_units 327\n",
      "pu_uses 14\n",
      "pu_type 4\n"
     ]
    }
   ],
   "source": [
    "source = \"data/planning.gpkg\"\n",
    "for layername in fiona.listlayers(source):\n",
    "    with fiona.open(source, layer=layername) as src:\n",
    "        print(layername, len(src))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "msp_areas = gpd.read_file(source, layer=\"areas\")\n",
    "msp_subareas = gpd.read_file(source, layer=\"subareas\")\n",
    "planning_units = gpd.read_file(source, layer=\"planning_units\")\n",
    "pu_uses = gpd.read_file(source, layer=\"pu_uses\").set_index(\"code\")\n",
    "uses_pu = gpd.read_file(source, layer=\"uses_pu\")\n",
    "main_uses_pu = uses_pu.query(\"type=='main'\")\n",
    "pu_uses = gpd.read_file(source, layer=\"pu_uses\").set_index(\"code\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>use_eng</th>\n",
       "      <th>use_ita</th>\n",
       "      <th>label</th>\n",
       "      <th>up_types</th>\n",
       "      <th>sld_style</th>\n",
       "      <th>color</th>\n",
       "      <th>use_ita_short</th>\n",
       "      <th>geometry</th>\n",
       "      <th>emodnet</th>\n",
       "      <th>emo_desc</th>\n",
       "      <th>hilucs</th>\n",
       "      <th>hilucsmsp</th>\n",
       "      <th>complete</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0100</th>\n",
       "      <td>Fishery</td>\n",
       "      <td>Pesca</td>\n",
       "      <td>p</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_pesca.sld</td>\n",
       "      <td>#F4AAAE</td>\n",
       "      <td>Pesca</td>\n",
       "      <td>None</td>\n",
       "      <td>Fishing areas</td>\n",
       "      <td>Planning units primary addressed to fishery</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1500</th>\n",
       "      <td>Aquaculture</td>\n",
       "      <td>Acquacoltura</td>\n",
       "      <td>a</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_acquacoltura.sld</td>\n",
       "      <td>#B9829F</td>\n",
       "      <td>Acquacoltura</td>\n",
       "      <td>None</td>\n",
       "      <td>Aquaculture</td>\n",
       "      <td>Planning units primary addressed to aquaculture</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0200</th>\n",
       "      <td>Marine Traffic and Ports</td>\n",
       "      <td>Trasporto marittimo e portualità</td>\n",
       "      <td>tm</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_trasporto.sld</td>\n",
       "      <td>#6E7C97</td>\n",
       "      <td>Trasporto mar. portualità</td>\n",
       "      <td>None</td>\n",
       "      <td>Maritime Traffic flows</td>\n",
       "      <td>Planning units</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0300</th>\n",
       "      <td>Environmental protecion and resources</td>\n",
       "      <td>Protezione ambiente e risorse naturali</td>\n",
       "      <td>n</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_protezione.sld</td>\n",
       "      <td>#88BC91</td>\n",
       "      <td>Protez. ambiente e ris.naturali</td>\n",
       "      <td>None</td>\n",
       "      <td>Nature Protection Conservation</td>\n",
       "      <td>Nature Protection Conservation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0400</th>\n",
       "      <td>Energy</td>\n",
       "      <td>Energia</td>\n",
       "      <td>e</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_energia.sld</td>\n",
       "      <td>#C04B4B</td>\n",
       "      <td>Energia</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Energy related planning units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0500</th>\n",
       "      <td>Sand extraction</td>\n",
       "      <td>Prelievo di sabbie</td>\n",
       "      <td>sa</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_sabbie.sld</td>\n",
       "      <td>#FCD154</td>\n",
       "      <td>Sabbie</td>\n",
       "      <td>None</td>\n",
       "      <td>Raw material extraction</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://inspire.ec.europa.eu/codelist/HILUCSVal...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0600</th>\n",
       "      <td>Military</td>\n",
       "      <td>Difesa</td>\n",
       "      <td>d</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_difesa.sld</td>\n",
       "      <td>#CCAE99</td>\n",
       "      <td>Difesa</td>\n",
       "      <td>None</td>\n",
       "      <td>Military areas</td>\n",
       "      <td>Military training areas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0700</th>\n",
       "      <td>Tourism</td>\n",
       "      <td>Turismo costiero e marittimo</td>\n",
       "      <td>t</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_turismo.sld</td>\n",
       "      <td>#F4A166</td>\n",
       "      <td>Turismo</td>\n",
       "      <td>None</td>\n",
       "      <td>Tourism and recreation</td>\n",
       "      <td>Tourism and recreation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000</th>\n",
       "      <td>Landscape/Seascape Cultural Heritage</td>\n",
       "      <td>Paesaggio e Patrimonio Culturale</td>\n",
       "      <td>ppc</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_paesaggio.sld</td>\n",
       "      <td>#778775</td>\n",
       "      <td>Paesaggio e Patrimonio Cult.</td>\n",
       "      <td>None</td>\n",
       "      <td>Cultural heritage</td>\n",
       "      <td>Landscape and cultural heritage (visibile, und...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1400</th>\n",
       "      <td>Scientific research &amp; Innovation</td>\n",
       "      <td>Ricerca scientifica e innovazione</td>\n",
       "      <td>r</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_ricerca.sld</td>\n",
       "      <td>#999999</td>\n",
       "      <td>Ricerca sc. e innovaz.</td>\n",
       "      <td>None</td>\n",
       "      <td>Scientific research</td>\n",
       "      <td>Scientific research</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1300</th>\n",
       "      <td>Safety and security</td>\n",
       "      <td>Sicurezza marittima, della navigazione e sorve...</td>\n",
       "      <td>s</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_sicurezza.sld</td>\n",
       "      <td>#9C6F69</td>\n",
       "      <td>Sicurezza marittima</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200</th>\n",
       "      <td>Coastal defence, seabed restoration</td>\n",
       "      <td>Difesa costiera, protezione dalle alluvioni, r...</td>\n",
       "      <td>dc</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_difesa_costiera.sld</td>\n",
       "      <td>#999999</td>\n",
       "      <td>Difesa costiera</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1100</th>\n",
       "      <td>Offshore (other than oil) discharge location</td>\n",
       "      <td>Immersione sedimenti dragati</td>\n",
       "      <td>sd</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_sedimenti.sld</td>\n",
       "      <td>#999999</td>\n",
       "      <td>Imm. sedimenti dragati</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0900</th>\n",
       "      <td>Submarine cables</td>\n",
       "      <td>Telecomunicazioni</td>\n",
       "      <td>#</td>\n",
       "      <td>100</td>\n",
       "      <td>zoning_elements_telecomunicazioni.sld</td>\n",
       "      <td>#BBC8E4</td>\n",
       "      <td>Telecom.</td>\n",
       "      <td>None</td>\n",
       "      <td>Cables</td>\n",
       "      <td>cables</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0410</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>Ocean Energy Facilities</td>\n",
       "      <td>Renewable energy</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0420</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>Oil and Gas\\nExtraction</td>\n",
       "      <td>Areas that are corrently used by Oil and Gas e...</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>Other/misc</td>\n",
       "      <td>Generic planning units  has no specific use pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           use_eng  \\\n",
       "0100                                       Fishery   \n",
       "1500                                   Aquaculture   \n",
       "0200                      Marine Traffic and Ports   \n",
       "0300         Environmental protecion and resources   \n",
       "0400                                        Energy   \n",
       "0500                               Sand extraction   \n",
       "0600                                      Military   \n",
       "0700                                       Tourism   \n",
       "1000          Landscape/Seascape Cultural Heritage   \n",
       "1400              Scientific research & Innovation   \n",
       "1300                           Safety and security   \n",
       "1200           Coastal defence, seabed restoration   \n",
       "1100  Offshore (other than oil) discharge location   \n",
       "0900                              Submarine cables   \n",
       "0410                                           NaN   \n",
       "0420                                           NaN   \n",
       "0000                                           NaN   \n",
       "\n",
       "                                                use_ita label up_types  \\\n",
       "0100                                              Pesca     p      100   \n",
       "1500                                       Acquacoltura     a      100   \n",
       "0200                   Trasporto marittimo e portualità    tm      100   \n",
       "0300             Protezione ambiente e risorse naturali     n      100   \n",
       "0400                                            Energia     e      100   \n",
       "0500                                 Prelievo di sabbie    sa      100   \n",
       "0600                                             Difesa     d      100   \n",
       "0700                       Turismo costiero e marittimo     t      100   \n",
       "1000                   Paesaggio e Patrimonio Culturale   ppc      100   \n",
       "1400                  Ricerca scientifica e innovazione     r      100   \n",
       "1300  Sicurezza marittima, della navigazione e sorve...     s      100   \n",
       "1200  Difesa costiera, protezione dalle alluvioni, r...    dc      100   \n",
       "1100                       Immersione sedimenti dragati    sd      100   \n",
       "0900                                  Telecomunicazioni     #      100   \n",
       "0410                                                NaN   NaN      NaN   \n",
       "0420                                                NaN   NaN      NaN   \n",
       "0000                                                NaN   NaN      NaN   \n",
       "\n",
       "                                  sld_style    color  \\\n",
       "0100              zoning_elements_pesca.sld  #F4AAAE   \n",
       "1500       zoning_elements_acquacoltura.sld  #B9829F   \n",
       "0200          zoning_elements_trasporto.sld  #6E7C97   \n",
       "0300         zoning_elements_protezione.sld  #88BC91   \n",
       "0400            zoning_elements_energia.sld  #C04B4B   \n",
       "0500             zoning_elements_sabbie.sld  #FCD154   \n",
       "0600             zoning_elements_difesa.sld  #CCAE99   \n",
       "0700            zoning_elements_turismo.sld  #F4A166   \n",
       "1000          zoning_elements_paesaggio.sld  #778775   \n",
       "1400            zoning_elements_ricerca.sld  #999999   \n",
       "1300          zoning_elements_sicurezza.sld  #9C6F69   \n",
       "1200    zoning_elements_difesa_costiera.sld  #999999   \n",
       "1100          zoning_elements_sedimenti.sld  #999999   \n",
       "0900  zoning_elements_telecomunicazioni.sld  #BBC8E4   \n",
       "0410                                    NaN      NaN   \n",
       "0420                                    NaN      NaN   \n",
       "0000                                    NaN      NaN   \n",
       "\n",
       "                        use_ita_short geometry  \\\n",
       "0100                            Pesca     None   \n",
       "1500                     Acquacoltura     None   \n",
       "0200        Trasporto mar. portualità     None   \n",
       "0300  Protez. ambiente e ris.naturali     None   \n",
       "0400                          Energia     None   \n",
       "0500                           Sabbie     None   \n",
       "0600                           Difesa     None   \n",
       "0700                         Turismo      None   \n",
       "1000     Paesaggio e Patrimonio Cult.     None   \n",
       "1400           Ricerca sc. e innovaz.     None   \n",
       "1300              Sicurezza marittima     None   \n",
       "1200                  Difesa costiera     None   \n",
       "1100           Imm. sedimenti dragati     None   \n",
       "0900                         Telecom.     None   \n",
       "0410                              NaN     None   \n",
       "0420                              NaN     None   \n",
       "0000                              NaN     None   \n",
       "\n",
       "                             emodnet  \\\n",
       "0100                  Fishing areas    \n",
       "1500                     Aquaculture   \n",
       "0200          Maritime Traffic flows   \n",
       "0300  Nature Protection Conservation   \n",
       "0400                             NaN   \n",
       "0500         Raw material extraction   \n",
       "0600                  Military areas   \n",
       "0700          Tourism and recreation   \n",
       "1000               Cultural heritage   \n",
       "1400             Scientific research   \n",
       "1300                             NaN   \n",
       "1200                             NaN   \n",
       "1100                             NaN   \n",
       "0900                          Cables   \n",
       "0410         Ocean Energy Facilities   \n",
       "0420         Oil and Gas\\nExtraction   \n",
       "0000                      Other/misc   \n",
       "\n",
       "                                               emo_desc  \\\n",
       "0100        Planning units primary addressed to fishery   \n",
       "1500    Planning units primary addressed to aquaculture   \n",
       "0200                                    Planning units    \n",
       "0300                     Nature Protection Conservation   \n",
       "0400                      Energy related planning units   \n",
       "0500                                                NaN   \n",
       "0600                            Military training areas   \n",
       "0700                             Tourism and recreation   \n",
       "1000  Landscape and cultural heritage (visibile, und...   \n",
       "1400                                Scientific research   \n",
       "1300                                                NaN   \n",
       "1200                                                NaN   \n",
       "1100                                                NaN   \n",
       "0900                                             cables   \n",
       "0410                                   Renewable energy   \n",
       "0420  Areas that are corrently used by Oil and Gas e...   \n",
       "0000  Generic planning units  has no specific use pr...   \n",
       "\n",
       "                                                 hilucs  \\\n",
       "0100  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "1500  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "0200  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "0300                                                NaN   \n",
       "0400                                                NaN   \n",
       "0500  http://inspire.ec.europa.eu/codelist/HILUCSVal...   \n",
       "0600                                                NaN   \n",
       "0700                                                NaN   \n",
       "1000                                                NaN   \n",
       "1400                                                NaN   \n",
       "1300                                                NaN   \n",
       "1200                                                NaN   \n",
       "1100                                                NaN   \n",
       "0900                                                NaN   \n",
       "0410  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "0420  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "0000                                                NaN   \n",
       "\n",
       "                                              hilucsmsp complete  \n",
       "0100  http://www.geoportal.ulpgc.es/registro/planned...      yes  \n",
       "1500  http://www.geoportal.ulpgc.es/registro/planned...      yes  \n",
       "0200  http://www.geoportal.ulpgc.es/registro/planned...      NaN  \n",
       "0300  http://www.geoportal.ulpgc.es/registro/planned...       no  \n",
       "0400                                                NaN      NaN  \n",
       "0500  http://www.geoportal.ulpgc.es/registro/planned...       no  \n",
       "0600  http://www.geoportal.ulpgc.es/registro/planned...      yes  \n",
       "0700  http://www.geoportal.ulpgc.es/registro/planned...       no  \n",
       "1000  http://www.geoportal.ulpgc.es/registro/planned...       no  \n",
       "1400  http://www.geoportal.ulpgc.es/registro/planned...       no  \n",
       "1300                                                NaN       no  \n",
       "1200                                                NaN       no  \n",
       "1100                                                NaN       no  \n",
       "0900  http://www.geoportal.ulpgc.es/registro/planned...      NaN  \n",
       "0410  http://www.geoportal.ulpgc.es/registro/planned...       no  \n",
       "0420  http://www.geoportal.ulpgc.es/registro/planned...      NaN  \n",
       "0000                                                NaN      NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extend uses to emodnet names\n",
    "emodnet_uses = pd.read_csv('data/msp_emodnet_uses.csv')\n",
    "emodnet_uses['code'] = emodnet_uses[\"code\"].astype(str).str.zfill(4)\n",
    "emodnet_uses.set_index(\"code\", inplace=True)\n",
    "df_uses = pd.DataFrame(pu_uses)\n",
    "pu_uses_ext = pd.concat([df_uses, emodnet_uses], axis=1)\n",
    "pu_uses_ext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load attribute dictionary for  zoning elements\n",
    "\n",
    "fulldict = pd.read_csv('data/msp_emodnet_dictionary.csv')\n",
    "ze_dict = fulldict.query('emodnet_layername == \"MSP Zoning Element\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://inspire.ec.europa.eu/codelist/HILUCSValue/2_4_4_RenewableEnergyProduction\n"
     ]
    }
   ],
   "source": [
    "def get_use(code, key):\n",
    "    if code=='0000':\n",
    "        return 'generic'\n",
    "    r = pu_uses_ext.at[code,key] \n",
    "    if type(r)==str:       \n",
    "        return r\n",
    "    else:\n",
    "        return '-'\n",
    "\n",
    "#TODO single function that get first non null value from dictionary\n",
    "def get_field_value(fieldname):\n",
    "    v = ze_dict.query('emodnet_ha_fieldname == \"'+ fieldname +'\"')['italian_msp_fieldame']\n",
    "    if v.item().startswith('['):\n",
    "        l = v.item().strip('[]').split(',')\n",
    "        s = [x.strip() for x in l]\n",
    "        return s\n",
    "    else:\n",
    "        return v.item()\n",
    "def get_static_value(fieldname):\n",
    "    v = ze_dict.query('emodnet_ha_fieldname == \"'+ fieldname +'\"')['italian_msp_staticvalue']\n",
    "    return v.item()\n",
    "key = get_field_value('HilucLU')[1].strip()\n",
    "r = get_use('0410', key)\n",
    "print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to set different energy use for each PU\n",
    "energy_uses = {\n",
    "    'presenza piattaforme':'0410',\n",
    "    'potenziale rinnovabili': '0420'\n",
    "}\n",
    "\n",
    "\n",
    "def get_energy_use(up_code):\n",
    "    if type(up_code)!= str:\n",
    "        up_code = up_code.item()\n",
    "    energy= main_uses_pu.query(\"use_code_s=='0400'\")\n",
    "    #print(energy)\n",
    "    curruse = energy.query(\"pu_code=='\" +up_code +\"'\")\n",
    "    #print(curruse['notes'])\n",
    "    usenote = curruse['notes'].item().replace(';',',')\n",
    "    notelist = usenote.split(',')\n",
    "    notelist = [x.strip() for x in notelist]\n",
    "    enerlist =[]\n",
    "    for n in notelist:\n",
    "        for e in energy_uses:\n",
    "            if n == e:\n",
    "                enerlist.append(energy_uses[e])\n",
    "    return enerlist\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MS</th>\n",
       "      <th>LocalID</th>\n",
       "      <th>VersionID</th>\n",
       "      <th>OffSource</th>\n",
       "      <th>HilucLU</th>\n",
       "      <th>hilucsMSP</th>\n",
       "      <th>SeaUseFct</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IT</td>\n",
       "      <td>IT_A/3_01P(t)</td>\n",
       "      <td>0.1</td>\n",
       "      <td>https://www.sid.mit.gov.it</td>\n",
       "      <td>-</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>Priority</td>\n",
       "      <td>MULTIPOLYGON (((7023412.570 4941571.750, 70201...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IT</td>\n",
       "      <td>IT_A/3_02P(a)</td>\n",
       "      <td>0.1</td>\n",
       "      <td>https://www.sid.mit.gov.it</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>Priority</td>\n",
       "      <td>MULTIPOLYGON (((7025074.508 4954529.347, 70251...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IT</td>\n",
       "      <td>IT_A/3_04P(n,d)</td>\n",
       "      <td>0.1</td>\n",
       "      <td>https://www.sid.mit.gov.it</td>\n",
       "      <td>-</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>Priority</td>\n",
       "      <td>MULTIPOLYGON (((7052426.141 4944327.128, 70523...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IT</td>\n",
       "      <td>IT_A/3_04P(n,d)</td>\n",
       "      <td>0.1</td>\n",
       "      <td>https://www.sid.mit.gov.it</td>\n",
       "      <td>-</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>Priority</td>\n",
       "      <td>MULTIPOLYGON (((7052426.141 4944327.128, 70523...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IT</td>\n",
       "      <td>IT_A/3_05P(t)</td>\n",
       "      <td>0.1</td>\n",
       "      <td>https://www.sid.mit.gov.it</td>\n",
       "      <td>-</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>Priority</td>\n",
       "      <td>MULTIPOLYGON (((7060232.056 4863396.003, 70602...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MS          LocalID VersionID                   OffSource  \\\n",
       "0  IT    IT_A/3_01P(t)       0.1  https://www.sid.mit.gov.it   \n",
       "1  IT    IT_A/3_02P(a)       0.1  https://www.sid.mit.gov.it   \n",
       "2  IT  IT_A/3_04P(n,d)       0.1  https://www.sid.mit.gov.it   \n",
       "2  IT  IT_A/3_04P(n,d)       0.1  https://www.sid.mit.gov.it   \n",
       "3  IT    IT_A/3_05P(t)       0.1  https://www.sid.mit.gov.it   \n",
       "\n",
       "                                             HilucLU  \\\n",
       "0                                                  -   \n",
       "1  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "2                                                  -   \n",
       "2                                                  -   \n",
       "3                                                  -   \n",
       "\n",
       "                                           hilucsMSP SeaUseFct  \\\n",
       "0  http://www.geoportal.ulpgc.es/registro/planned...  Priority   \n",
       "1  http://www.geoportal.ulpgc.es/registro/planned...  Priority   \n",
       "2  http://www.geoportal.ulpgc.es/registro/planned...  Priority   \n",
       "2  http://www.geoportal.ulpgc.es/registro/planned...  Priority   \n",
       "3  http://www.geoportal.ulpgc.es/registro/planned...  Priority   \n",
       "\n",
       "                                            geometry  \n",
       "0  MULTIPOLYGON (((7023412.570 4941571.750, 70201...  \n",
       "1  MULTIPOLYGON (((7025074.508 4954529.347, 70251...  \n",
       "2  MULTIPOLYGON (((7052426.141 4944327.128, 70523...  \n",
       "2  MULTIPOLYGON (((7052426.141 4944327.128, 70523...  \n",
       "3  MULTIPOLYGON (((7060232.056 4863396.003, 70602...  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# explode dataframe for primary uses\n",
    "planning_units['main_uses_list'] = planning_units.main_uses.str.split(',')\n",
    "# explode dataframe for multiple uses  on the same code, apply only on energy (0400)\n",
    "planning_units['main_uses_list'] = planning_units.apply(lambda x: get_energy_use(x['Code']) if '0400' in x['main_uses_list'] else x['main_uses_list'], axis=1)\n",
    "ze = planning_units.explode('main_uses_list')\n",
    "ze.rename(columns ={'main_uses_list':'usecode'}, inplace=True)\n",
    "# add emodnet columns\n",
    "ze['MS'] = 'IT'\n",
    "ze['LocalID'] = 'IT_' + ze['Code'] + ze['Type']\n",
    "ze['VersionID'] = '0.1'\n",
    "ze['OffSource'] = get_static_value('OffSource')\n",
    "l = get_field_value('HilucLU')\n",
    "ze['HilucLU'] = ze['usecode'].apply(lambda x: get_use(x,l[1]))\n",
    "l = get_field_value('hilucsMSP')\n",
    "ze['hilucsMSP'] = ze['usecode'].apply(lambda x: get_use(x,l[1]))\n",
    "l = get_field_value('SeaUseName')\n",
    "ze['SeaUseName'] = ze['usecode'].apply(lambda x: get_use(x,l[1]))\n",
    "# get sea use function bsed on PU type\n",
    "seaFunction = {100: 'Allowed', 200:'Priority', 300: 'Priority', 400: 'Reserved'}\n",
    "ze['SeaUseFct'] = ze['pu_type'].apply(lambda x : seaFunction[x])\n",
    "l = get_field_value('SeaUseDsc')\n",
    "ze['SeaUseDsc'] = \n",
    "ze['OriginalUseName']\n",
    "ze['OriginalUseNameEn']\n",
    "#select only columns relevant for EMODnet data model\n",
    "sel_cols = [ 'LocalID', 'VersionID','OffSource','MS','HilucLU', 'hilucsMSP','SeaUseName','SeaUseFct','SeaUseDsc', 'geometry']\n",
    "ze = ze[sel_cols]\n",
    "ze.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MS</th>\n",
       "      <th>LocalID</th>\n",
       "      <th>VersionID</th>\n",
       "      <th>OffSource</th>\n",
       "      <th>HilucLU</th>\n",
       "      <th>hilucsMSP</th>\n",
       "      <th>SeaUseFct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>IT</td>\n",
       "      <td>IT_A/3_07P(e)</td>\n",
       "      <td>0.1</td>\n",
       "      <td>https://www.sid.mit.gov.it</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>Priority</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>IT</td>\n",
       "      <td>IT_A/3_07P(e)</td>\n",
       "      <td>0.1</td>\n",
       "      <td>https://www.sid.mit.gov.it</td>\n",
       "      <td>https://inspire.ec.europa.eu/codelist/HILUCSVa...</td>\n",
       "      <td>http://www.geoportal.ulpgc.es/registro/planned...</td>\n",
       "      <td>Priority</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    MS        LocalID VersionID                   OffSource  \\\n",
       "25  IT  IT_A/3_07P(e)       0.1  https://www.sid.mit.gov.it   \n",
       "25  IT  IT_A/3_07P(e)       0.1  https://www.sid.mit.gov.it   \n",
       "\n",
       "                                              HilucLU  \\\n",
       "25  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "25  https://inspire.ec.europa.eu/codelist/HILUCSVa...   \n",
       "\n",
       "                                            hilucsMSP SeaUseFct  \n",
       "25  http://www.geoportal.ulpgc.es/registro/planned...  Priority  \n",
       "25  http://www.geoportal.ulpgc.es/registro/planned...  Priority  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ze.query(\"LocalID=='IT_A/3_07P(e)'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "msp_italy",
   "language": "python",
   "name": "msp_italy"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}