preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "common-spanish",
   "metadata": {},
   "source": [
    "# Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "stunning-sperm",
   "metadata": {},
   "outputs": [],
   "source": [
    "# math and data packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import math\n",
    "\n",
    "# charting and graphics\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "# os and file types\n",
    "import os\n",
    "import sys\n",
    "import datetime as dt\n",
    "import json\n",
    "import csv\n",
    "\n",
    "# images and display\n",
    "import base64, io, IPython\n",
    "from PIL import Image as PILImage\n",
    "from IPython.display import Markdown as md\n",
    "from IPython.display import display, Math, Latex\n",
    "\n",
    "\n",
    "\n",
    "# set useful variables for accessing the files\n",
    "here = os.getcwd()\n",
    "flora_h = F\"{here}/resources/reference-data/\"\n",
    "flora_h_ws = F\"{flora_h}atlasws/\"\n",
    "flora_h_55 = F\"{flora_h}atlas5x5/\"\n",
    "data_2020 = F\"{here}/resources/survey-data/\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "drawn-individual",
   "metadata": {},
   "source": [
    "## Import data\n",
    "\n",
    "Fix any known formatting problems here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "wound-banks",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing the data files\n",
    "# create variables for convenience method and grouping\n",
    "\n",
    "# start by organizing them into dictionaries.\n",
    "my_data_methods = {\"csv\":pd.read_csv}\n",
    "\n",
    "# dict for the 2020 survey data files\n",
    "d_files = {\n",
    "    \"surveys\":\"surveys.csv\",\n",
    "    \"map_keys\":\"map-keys.csv\",    \n",
    "}\n",
    "\n",
    "# dict for the reference files that are not Welten Sutter or Flora Helvetica 5X5 lists\n",
    "w_lists = {\n",
    "    \"list_2014\":\"BL_WL_2014_modified.csv\",\n",
    "    \"under_sampled\":\"taxa_sous_echantillonnes.csv\",\n",
    "    \"red_list\":\"redlist2019.csv\",\n",
    "    \"cert_list\": \"Certification_specieslist_2021.csv\"\n",
    "}\n",
    "\n",
    "# dict for the Welten-Sutter map reference files, downloaded from here: https://www.infoflora.ch/de/daten/artenliste-welten-sutter.html\n",
    "# all observations included in this report were conducted within one of these geographic boundaries\n",
    "ws_lists ={\n",
    "    \"151\":\"AtlasWS_151_Biel.csv\",\n",
    "    \"252\":\"AtlasWS_252_Erlach.csv\",\n",
    "    \"300\":\"AtlasWS_300_Aarberg.csv\",\n",
    "    \"301\":\"AtlasWS_301_Bueren.csv\",\n",
    "    \"154\":\"AtlasWS_154_Grenchen.csv\",\n",
    "    \"572\":\"AtlasWS_572_Beatenberg.csv\",\n",
    "    \"573\":\"AtlasWS_573_Interlaken.csv\",\n",
    "    \"226\":\"AtlasWS_226_Estavayer.csv\",\n",
    "    \"251\":\"AtlasWS_251_BernWest.csv\",\n",
    "    \"145\":\"AtlasWS_145_LesRangiers.csv\"\n",
    "}\n",
    "\n",
    "# housekeeping: 585220 is separated by \",\" not \";\" like the rest of the data sources\n",
    "df = pd.read_csv(\"resources/reference-data/atlas5x5/Atlas5x5_585_220.csv\", sep = \",\", encoding=\"utf-16\")\n",
    "df.to_csv('resources/reference-data/atlas5x5/Atlas5x5_585_220_1.csv', sep=';', encoding = \"utf-16\", index = False)\n",
    "\n",
    "# 5X5 Flora helvitca lists\n",
    "fx_lists = {\n",
    "    \"585215\":\"Atlas5x5_585_215.csv\", # Ipsach, Bielersee\n",
    "    \"585220\":\"Atlas5x5_585_220_1.csv\", # Biel Stadt, Suze / Bielersee\n",
    "    \"580220\":\"Atlas5x5_580_220.csv\", # Biel Mett, Suze\n",
    "    \"580215\":\"Atlas5x5_580_215.csv\", # Port, Nidau-Bueren Kanal\n",
    "    \"625165\":\"Atlas5x5_625_165.csv\", # Untersee, Thunersee\n",
    "    \"625170\":\"Atlas5x5_625_170.csv\", # Sundlauenen, Thunersee\n",
    "    \"550185\":\"Atlas5x5_550_185.csv\", # Estavayer, Lac de Neuchatel\n",
    "    \"575210\":\"Atlas5x5_575_210.csv\", # Leuecherz, Bielersee\n",
    "    \"600200\":\"Atlas5x5_600_200.csv\", # Bern west, Aare\n",
    "    \"575245\":\"Atlas5x5_575_245.csv\",  # Saint-Ursanne, Aare\n",
    "    \"545180\":\"Atlas5x5_545_180.csv\",\n",
    "    \"575215\":\"Atlas5x5_575_215.csv\"\n",
    "}\n",
    "\n",
    "# convenience method to gather up all the files:\n",
    "def get_the_data(file_exts, a_dir, methods, this_method=\"csv\", myencoding=None):\n",
    "    wiw = {}\n",
    "    for k,v in file_exts.items():\n",
    "        if myencoding == None:\n",
    "            wiw.update({k:methods[this_method](F\"{a_dir}{v}\")})            \n",
    "        else:\n",
    "            wiw.update({k:methods[this_method](F\"{a_dir}{v}\",sep = \";\", encoding=myencoding)})\n",
    "    return wiw\n",
    "\n",
    "# use the get_the_data method to collect these files\n",
    "data_and_keys = get_the_data(d_files, data_2020, my_data_methods, this_method=\"csv\")\n",
    "watch_lists = get_the_data(w_lists, flora_h, my_data_methods, this_method=\"csv\")\n",
    "\n",
    "# Why are we usig utf-16 here? Don't we lose some options later on?\n",
    "welt_sut =  get_the_data(ws_lists, flora_h_ws, my_data_methods, this_method=\"csv\", myencoding = \"utf-16\" )\n",
    "fivex =  get_the_data(fx_lists, flora_h_55, my_data_methods, this_method=\"csv\", myencoding = \"utf-16\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "united-multimedia",
   "metadata": {},
   "source": [
    "## The Species columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "suitable-program",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make sure that each data set has the column \"species\", with the value species:\n",
    "watch_lists[\"list_2014\"][\"species\"] = watch_lists[\"list_2014\"].Latin\n",
    "watch_lists[\"under_sampled\"][\"species\"] = watch_lists[\"under_sampled\"].taxon\n",
    "watch_lists[\"red_list\"][\"species\"] = watch_lists[\"red_list\"].scientific_name\n",
    "watch_lists[\"cert_list\"][\"species\"] = watch_lists[\"cert_list\"][\"Short Name\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "stuffed-cancer",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make a species slug (genus-species) to link data from across the survey and reference files. \n",
    "# This is necessary as some species columns have only \"Genus species\", some include subspecies, and some include the taxonomic reference.\n",
    "\n",
    "# function to make the species slugs\n",
    "def to_species_slug(x):\n",
    "    try: \n",
    "        int_data = x.split()\n",
    "        data = int_data[:2]\n",
    "        data = \"-\".join(data)\n",
    "        data = data.lower()\n",
    "    except:\n",
    "        data = \"none\"\n",
    "    return data\n",
    "\n",
    "# create a new column to hold the slug\n",
    "for element in [fivex, welt_sut, watch_lists]:\n",
    "    for the_data in element:\n",
    "        element[the_data]['species_slug'] = 'none'\n",
    "\n",
    "# make the species slug for all reference files\n",
    "for element in [fivex, welt_sut, watch_lists]:\n",
    "    for the_data in element:\n",
    "        element[the_data]['species_slug'] = element[the_data].species.map(lambda x: to_species_slug(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "perceived-parade",
   "metadata": {},
   "source": [
    "## The _map_ column and the _spec\\_map_ columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "surface-volleyball",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add identifying columns to the reference datasets\n",
    "\n",
    "# add a column to identify the map source for the geographic data:\n",
    "for element in [fivex, welt_sut]:\n",
    "    for the_data in element:\n",
    "        element[the_data]['map'] = the_data\n",
    "        element[the_data]['spec_map'] = list(zip(element[the_data].species_slug,element[the_data].map))\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "viral-median",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['585215', '585220', '580220', '580215', '625165', '625170', '550185', '575210', '600200', '575245', '545180', '575215'])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fivex.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "modern-wesley",
   "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>taxon_id</th>\n",
       "      <th>isfs</th>\n",
       "      <th>species</th>\n",
       "      <th>code-ws</th>\n",
       "      <th>status</th>\n",
       "      <th>number_observations</th>\n",
       "      <th>years_min</th>\n",
       "      <th>years_max</th>\n",
       "      <th>douteuse</th>\n",
       "      <th>species_slug</th>\n",
       "      <th>map</th>\n",
       "      <th>spec_map</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Abies alba Mill.</td>\n",
       "      <td>Fréquent</td>\n",
       "      <td>Indigène</td>\n",
       "      <td>2</td>\n",
       "      <td>1982</td>\n",
       "      <td>2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>abies-alba</td>\n",
       "      <td>585215</td>\n",
       "      <td>(abies-alba, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000020</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Abutilon theophrasti Medik.</td>\n",
       "      <td>-</td>\n",
       "      <td>Néophyte</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>abutilon-theophrasti</td>\n",
       "      <td>585215</td>\n",
       "      <td>(abutilon-theophrasti, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000070</td>\n",
       "      <td>300.0</td>\n",
       "      <td>Acer campestre L.</td>\n",
       "      <td>Fréquent</td>\n",
       "      <td>Indigène</td>\n",
       "      <td>7</td>\n",
       "      <td>1982</td>\n",
       "      <td>2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acer-campestre</td>\n",
       "      <td>585215</td>\n",
       "      <td>(acer-campestre, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000120</td>\n",
       "      <td>700.0</td>\n",
       "      <td>Acer platanoides L.</td>\n",
       "      <td>Fréquent</td>\n",
       "      <td>Indigène</td>\n",
       "      <td>2</td>\n",
       "      <td>1982</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acer-platanoides</td>\n",
       "      <td>585215</td>\n",
       "      <td>(acer-platanoides, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000130</td>\n",
       "      <td>800.0</td>\n",
       "      <td>Acer pseudoplatanus L.</td>\n",
       "      <td>Fréquent</td>\n",
       "      <td>Indigène</td>\n",
       "      <td>13</td>\n",
       "      <td>1982</td>\n",
       "      <td>2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acer-pseudoplatanus</td>\n",
       "      <td>585215</td>\n",
       "      <td>(acer-pseudoplatanus, 585215)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   taxon_id   isfs                      species   code-ws    status  \\\n",
       "0   1000000  100.0             Abies alba Mill.  Fréquent  Indigène   \n",
       "1   1000020  150.0  Abutilon theophrasti Medik.         -  Néophyte   \n",
       "2   1000070  300.0            Acer campestre L.  Fréquent  Indigène   \n",
       "3   1000120  700.0          Acer platanoides L.  Fréquent  Indigène   \n",
       "4   1000130  800.0       Acer pseudoplatanus L.  Fréquent  Indigène   \n",
       "\n",
       "   number_observations  years_min  years_max  douteuse          species_slug  \\\n",
       "0                    2       1982       2021       NaN            abies-alba   \n",
       "1                    1       2017       2017       NaN  abutilon-theophrasti   \n",
       "2                    7       1982       2019       NaN        acer-campestre   \n",
       "3                    2       1982       2020       NaN      acer-platanoides   \n",
       "4                   13       1982       2019       NaN   acer-pseudoplatanus   \n",
       "\n",
       "      map                        spec_map  \n",
       "0  585215            (abies-alba, 585215)  \n",
       "1  585215  (abutilon-theophrasti, 585215)  \n",
       "2  585215        (acer-campestre, 585215)  \n",
       "3  585215      (acer-platanoides, 585215)  \n",
       "4  585215   (acer-pseudoplatanus, 585215)  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fivex['585215'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "biblical-position",
   "metadata": {},
   "source": [
    "## The _watch\\_list_ column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "amino-islam",
   "metadata": {},
   "outputs": [],
   "source": [
    "for element in [watch_lists]:\n",
    "    for the_data in element:\n",
    "        if the_data == \"list_2014\":\n",
    "            element[the_data]['watch_list'] = element[the_data][the_data]\n",
    "        else:\n",
    "            element[the_data]['watch_list'] = the_data\n",
    "\n",
    "# housekeeping: fill in nan values in the watchlist and certification list reference files.\n",
    "fill_nans = watch_lists[\"list_2014\"].copy()\n",
    "fill_nans = fill_nans.fillna(0)\n",
    "watch_lists.update({\"list_2014\":fill_nans[fill_nans.watch_list != 0]})\n",
    "\n",
    "fill_nans = watch_lists[\"cert_list\"].copy()\n",
    "fill_nans = fill_nans.fillna(0)\n",
    "watch_lists.update({\"cert_list\":fill_nans[fill_nans.watch_list != 0]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "vietnamese-forty",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['list_2014', 'under_sampled', 'red_list', 'cert_list'])"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "watch_lists.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "criminal-workstation",
   "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>id_ISFS</th>\n",
       "      <th>family</th>\n",
       "      <th>genus</th>\n",
       "      <th>species</th>\n",
       "      <th>scientific_name</th>\n",
       "      <th>Deutscher Name</th>\n",
       "      <th>Nom en français</th>\n",
       "      <th>Nome in italiano</th>\n",
       "      <th>CH</th>\n",
       "      <th>crit_CH</th>\n",
       "      <th>...</th>\n",
       "      <th>NA</th>\n",
       "      <th>crit_NA</th>\n",
       "      <th>WA</th>\n",
       "      <th>crit_WA</th>\n",
       "      <th>EA</th>\n",
       "      <th>crit_EA</th>\n",
       "      <th>SA</th>\n",
       "      <th>crit_SA</th>\n",
       "      <th>species_slug</th>\n",
       "      <th>watch_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>Pinaceae</td>\n",
       "      <td>Abies</td>\n",
       "      <td>Abies alba</td>\n",
       "      <td>Abies alba</td>\n",
       "      <td>Tanne</td>\n",
       "      <td>Sapin</td>\n",
       "      <td>Abete bianco</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>abies-alba</td>\n",
       "      <td>red_list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>300</td>\n",
       "      <td>Sapindaceae</td>\n",
       "      <td>Acer</td>\n",
       "      <td>Acer campestre</td>\n",
       "      <td>Acer campestre</td>\n",
       "      <td>Feld-Ahorn</td>\n",
       "      <td>Erable champêtre</td>\n",
       "      <td>Acero oppio</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acer-campestre</td>\n",
       "      <td>red_list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>600</td>\n",
       "      <td>Sapindaceae</td>\n",
       "      <td>Acer</td>\n",
       "      <td>Acer opalus</td>\n",
       "      <td>Acer opalus</td>\n",
       "      <td>Schneeballblättriger Ahorn</td>\n",
       "      <td>Erable à feuilles d'obier</td>\n",
       "      <td>Acero alpino</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acer-opalus</td>\n",
       "      <td>red_list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>700</td>\n",
       "      <td>Sapindaceae</td>\n",
       "      <td>Acer</td>\n",
       "      <td>Acer platanoides</td>\n",
       "      <td>Acer platanoides</td>\n",
       "      <td>Spitz-Ahorn</td>\n",
       "      <td>Erable plane</td>\n",
       "      <td>Acero riccio</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acer-platanoides</td>\n",
       "      <td>red_list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>800</td>\n",
       "      <td>Sapindaceae</td>\n",
       "      <td>Acer</td>\n",
       "      <td>Acer pseudoplatanus</td>\n",
       "      <td>Acer pseudoplatanus</td>\n",
       "      <td>Berg-Ahorn</td>\n",
       "      <td>Erable des montagnes</td>\n",
       "      <td>Acero di monte</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acer-pseudoplatanus</td>\n",
       "      <td>red_list</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_ISFS       family  genus              species      scientific_name  \\\n",
       "0      100     Pinaceae  Abies           Abies alba           Abies alba   \n",
       "1      300  Sapindaceae   Acer       Acer campestre       Acer campestre   \n",
       "2      600  Sapindaceae   Acer          Acer opalus          Acer opalus   \n",
       "3      700  Sapindaceae   Acer     Acer platanoides     Acer platanoides   \n",
       "4      800  Sapindaceae   Acer  Acer pseudoplatanus  Acer pseudoplatanus   \n",
       "\n",
       "               Deutscher Name            Nom en français Nome in italiano  CH  \\\n",
       "0                       Tanne                      Sapin     Abete bianco  LC   \n",
       "1                  Feld-Ahorn           Erable champêtre      Acero oppio  LC   \n",
       "2  Schneeballblättriger Ahorn  Erable à feuilles d'obier     Acero alpino  LC   \n",
       "3                 Spitz-Ahorn               Erable plane     Acero riccio  LC   \n",
       "4                  Berg-Ahorn       Erable des montagnes   Acero di monte  LC   \n",
       "\n",
       "  crit_CH  ...  NA crit_NA  WA crit_WA  EA crit_EA  SA crit_SA  \\\n",
       "0     NaN  ...  LC     NaN  LC     NaN  LC     NaN  LC     NaN   \n",
       "1     NaN  ...  LC     NaN  LC     NaN  LC     NaN  LC     NaN   \n",
       "2     NaN  ...  LC     NaN  LC     NaN   -     NaN   -     NaN   \n",
       "3     NaN  ...  LC     NaN  LC     NaN  LC     NaN  LC     NaN   \n",
       "4     NaN  ...  LC     NaN  LC     NaN  LC     NaN  LC     NaN   \n",
       "\n",
       "          species_slug watch_list  \n",
       "0           abies-alba   red_list  \n",
       "1       acer-campestre   red_list  \n",
       "2          acer-opalus   red_list  \n",
       "3     acer-platanoides   red_list  \n",
       "4  acer-pseudoplatanus   red_list  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "watch_lists['red_list'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "beneficial-culture",
   "metadata": {},
   "source": [
    "### Species name and observations: harmonizing taxonomy\n",
    "\n",
    "The genus-species nomenclature will be used to group observations.\n",
    "\n",
    "All observations will be classified according to that standard. As a result subspecies will be folded in with the parent species. This is a reflection of the survey method and the expectation of reasonable results, not a prioritization of importance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "interesting-progressive",
   "metadata": {},
   "outputs": [],
   "source": [
    "def account_for_subspecies(an_array, a_dict):\n",
    "    for element in an_array:\n",
    "        try:\n",
    "            a_dict[element[0]].append(element[1])\n",
    "        except:\n",
    "            a_dict[element[0]] = [element[1]]\n",
    "    return a_dict\n",
    "a_dict ={}\n",
    "\n",
    "for element in [fivex, welt_sut, watch_lists]:\n",
    "    for label in element:\n",
    "        # use this data frame\n",
    "        som_data = element[label].copy()\n",
    "        \n",
    "        # group by species slug and count the number of unique species values\n",
    "        c_s_p_s = som_data.groupby('species_slug', as_index=False).species.nunique()        \n",
    "        \n",
    "        # just the records with more than one species value\n",
    "        m_t_one = c_s_p_s[c_s_p_s.species > 1].species_slug\n",
    "        \n",
    "        # pair the species_slug to the species name:\n",
    "        mto = som_data.loc[som_data.species_slug.isin(m_t_one)][['species_slug', 'species']].copy().to_numpy()\n",
    "        \n",
    "        # update the dict\n",
    "        account_for_subspecies(mto, a_dict)\n",
    "\n",
    "# the species_slugs that account for more than one sub species\n",
    "sub_species_accounted =  {k:list(set(v)) for k,v in a_dict.items()}\n",
    "\n",
    "# the species_slugs\n",
    "gs_parent = list(sub_species_accounted.keys())\n",
    "\n",
    "# the number of species_slugs\n",
    "number_of_gs = len(gs_parent)\n",
    "\n",
    "# the the number of sub species accounted for\n",
    "number_of_ss = sum([len(v) for k,v in sub_species_accounted.items()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "juvenile-locator",
   "metadata": {},
   "source": [
    "## Determine wether or not a species was detected within a geographic limit\n",
    "\n",
    "The territory is divided into different segments. Flora-helvitica and WS maps have different geographic bounds. Here the presence or not of a species within the confines of one of the different boundaries is determined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "earlier-petersburg",
   "metadata": {},
   "outputs": [],
   "source": [
    "# collect all the observations from flora helvitaca and the WS into one df\n",
    "fx = pd.concat([v[['species_slug', 'map', 'spec_map']] for k,v in fivex.items()])\n",
    "wsx = pd.concat([v[['species_slug', 'map', 'spec_map']] for k,v in welt_sut.items()])\n",
    "f_w_obs = pd.concat([fx, wsx])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "36940cac-8e2c-4959-929b-c75968b313ed",
   "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>species_slug</th>\n",
       "      <th>map</th>\n",
       "      <th>spec_map</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>abies-alba</td>\n",
       "      <td>585215</td>\n",
       "      <td>(abies-alba, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abutilon-theophrasti</td>\n",
       "      <td>585215</td>\n",
       "      <td>(abutilon-theophrasti, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>acer-campestre</td>\n",
       "      <td>585215</td>\n",
       "      <td>(acer-campestre, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>acer-platanoides</td>\n",
       "      <td>585215</td>\n",
       "      <td>(acer-platanoides, 585215)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>acer-pseudoplatanus</td>\n",
       "      <td>585215</td>\n",
       "      <td>(acer-pseudoplatanus, 585215)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           species_slug     map                        spec_map\n",
       "0            abies-alba  585215            (abies-alba, 585215)\n",
       "1  abutilon-theophrasti  585215  (abutilon-theophrasti, 585215)\n",
       "2        acer-campestre  585215        (acer-campestre, 585215)\n",
       "3      acer-platanoides  585215      (acer-platanoides, 585215)\n",
       "4   acer-pseudoplatanus  585215   (acer-pseudoplatanus, 585215)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f_w_obs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d9e820fa-20aa-41b5-b20b-d8904d797ee2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# replacedict = {\n",
    "#     'verbanum bonariensis ':'verbena bonariensis',\n",
    "#     'medicago varia':'medicago sativa',\n",
    "#     \"oenothera\":\"oenothera biennis\",\n",
    "#     \"geranium pratens\":\"geranium pratense\",\n",
    "#     \"oenothera biennis \": \"oenothera biennis\",\n",
    "#     \"oenothera biennis agg.\": \"oenothera biennis\",\n",
    "#     \"solidalgo canadensis\": \"solidago canadensis\",\n",
    "#     \"verbascum lynchitis\":\"verbascum lychnitis\",\n",
    "#     \"verbascum negris\":\"verbascum nigrum\",\n",
    "#     \"securigea varia\": \"securigera varia\",\n",
    "#     \"melilotus officianalis\": \"melilotus officinalis\",\n",
    "#     \"knautia maxima\": \"knautia dipsacifolia\",\n",
    "#     \"hieracium aurantiacum\":\"pilosella aurantiaca\",\n",
    "#     \"sysimbrium officinale\":\"sisymbrium officinale\",\n",
    "#     \"geranium robertanium\":\"geranium robertianum\",\n",
    "#     \"mycelis muralis\": \"lactuca muralis\",\n",
    "#     \"calamintha-nepeta\":\"clinopodium nepeta\",\n",
    "#     \"polygonum-persicaria\":\"persicaria maculosa\",\n",
    "#     \"sorbus-aria\":\"aria edulis\",\n",
    "#     \"taraxacum\": \"taraxacum officinale\",\n",
    "#     \"jacobaea vulgaris\" : \"senecio jacobaea\",\n",
    "#     \"erigeron canadensis\" : \"conyza canadensis\",\n",
    "#     \"rorippa islandica\" : \"rorippa palustris\",\n",
    "#     \"malus sylvestris\" : \"malus domestica\",\n",
    "#     \"hylotelephium telephium\" : \"sedum telephium\",\n",
    "#     \"lactuca muralis\": \"mycelis muralis\",\n",
    "#     \"chaenorhinum minus\": \"chaenorrhinum minus\",\n",
    "#     \"erigeron canadensis\": \"conzya canadensis\",\n",
    "#     \"erigeron canadensis\": \"conzya canadensis\",\n",
    "#     \"borkhausenia intermedia\": \"scandosorbus intermedia\",\n",
    "#     \"centaurea nigra\" : \"centaurea jacea\"\n",
    "# }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7a2ef6b1-a02c-43b7-9248-f7eb016d4418",
   "metadata": {},
   "outputs": [],
   "source": [
    "# f_w_obs['ns'] = f_w_obs.species_slug\n",
    "# def replace_this(x,a_dict):\n",
    "#     if x in a_dict.keys():\n",
    "#         data=a_dict[x]\n",
    "#     else:\n",
    "#         data = x\n",
    "#     return data\n",
    "# f_w_obs['ns'] = f_w_obs.ns.map(lambda x: replace_this(x, replacedict))\n",
    "# f_w_obs['species_slug'] = f_w_obs.ns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "waiting-jesus",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "\n",
       "There are 22 different map boundaries in this study\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "astring = F\"\"\"\n",
    "There are {len(f_w_obs.map.unique())} different map boundaries in this study\n",
    "\"\"\"\n",
    "md(astring)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0e810772-748d-4815-8655-c79a53aea479",
   "metadata": {},
   "outputs": [],
   "source": [
    "# a = f_w_obs.set_index('species_slug')\n",
    "# a.loc['bryonia-dioica']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "million-welcome",
   "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>585215</th>\n",
       "      <th>585220</th>\n",
       "      <th>580220</th>\n",
       "      <th>580215</th>\n",
       "      <th>625165</th>\n",
       "      <th>625170</th>\n",
       "      <th>550185</th>\n",
       "      <th>575210</th>\n",
       "      <th>600200</th>\n",
       "      <th>575245</th>\n",
       "      <th>...</th>\n",
       "      <th>151</th>\n",
       "      <th>252</th>\n",
       "      <th>300</th>\n",
       "      <th>301</th>\n",
       "      <th>154</th>\n",
       "      <th>572</th>\n",
       "      <th>573</th>\n",
       "      <th>226</th>\n",
       "      <th>251</th>\n",
       "      <th>145</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>species_slug</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>abies-alba</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abies-nordmanniana</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abutilon-theophrasti</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>acacia-dealbata</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>acanthus-mollis</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      585215  585220  580220  580215  625165  625170  550185  \\\n",
       "species_slug                                                                   \n",
       "abies-alba                 1       1       1       1       1       1       1   \n",
       "abies-nordmanniana         0       0       0       0       0       0       0   \n",
       "abutilon-theophrasti       1       1       0       0       0       0       0   \n",
       "acacia-dealbata            0       0       0       0       0       0       0   \n",
       "acanthus-mollis            0       0       0       0       0       0       0   \n",
       "\n",
       "                      575210  600200  575245  ...  151  252  300  301  154  \\\n",
       "species_slug                                  ...                            \n",
       "abies-alba                 1       1       1  ...    1    1    1    1    1   \n",
       "abies-nordmanniana         0       1       0  ...    0    0    0    0    1   \n",
       "abutilon-theophrasti       0       1       0  ...    1    1    1    1    0   \n",
       "acacia-dealbata            0       0       0  ...    0    0    0    0    0   \n",
       "acanthus-mollis            0       1       0  ...    1    0    0    0    0   \n",
       "\n",
       "                      572  573  226  251  145  \n",
       "species_slug                                   \n",
       "abies-alba              1    1    1    1    1  \n",
       "abies-nordmanniana      0    0    0    1    0  \n",
       "abutilon-theophrasti    0    0    0    1    0  \n",
       "acacia-dealbata         0    0    1    0    0  \n",
       "acanthus-mollis         0    0    0    1    0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# gather up the map names\n",
    "m_ap_columns = f_w_obs.map.unique()\n",
    "\n",
    "# create a column for each map, indicate\n",
    "for col in m_ap_columns:\n",
    "    \n",
    "    f_w_obs[col] = f_w_obs['map'] == col\n",
    "\n",
    "obs_map =f_w_obs.groupby(['species_slug']).sum()\n",
    "\n",
    "# human readable column names need to be introduced here or a dict to rename\n",
    "obs_map.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3f20d736-f344-4796-ba70-ae8add44c469",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "585215    0\n",
       "585220    1\n",
       "580220    1\n",
       "580215    1\n",
       "625165    0\n",
       "625170    0\n",
       "550185    1\n",
       "575210    1\n",
       "600200    1\n",
       "575245    0\n",
       "545180    1\n",
       "575215    1\n",
       "151       1\n",
       "252       1\n",
       "300       0\n",
       "301       0\n",
       "154       1\n",
       "572       0\n",
       "573       0\n",
       "226       1\n",
       "251       0\n",
       "145       0\n",
       "Name: bryonia-dioica, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "obs_map.loc['bryonia-dioica']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "lightweight-induction",
   "metadata": {},
   "outputs": [],
   "source": [
    "a_file_name = \"species_map_located.csv\"\n",
    "obs_map.to_csv(F\"resources/survey-data/{a_file_name}\", index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "superb-resistance",
   "metadata": {},
   "source": [
    "### Key the species to the different maps it was identified in\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "manual-marks",
   "metadata": {},
   "outputs": [],
   "source": [
    "# #exports the dictionary to a .json file\n",
    "# nt = obs_map['maps'].to_dict()\n",
    "\n",
    "# ntx = {k:list(v) for k,v in nt.items()}\n",
    "\n",
    "# with open(F\"{here}/output/ws_list.json\",\"w\") as afile:\n",
    "#     json.dump(ntx,afile)\n",
    "    \n",
    "# print(F\"\\nWhich maps 'trifolium-incarnatum' were found in? indifferent of subspecies?:\\n\\n{ntx['trifolium-incarnatum']}\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "functioning-economics",
   "metadata": {},
   "source": [
    "## The _species\\_slug_ column\n",
    "\n",
    "A coder friendly way to find the species but still maintain the proper nomenclature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "powerful-picnic",
   "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>place</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>sci</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:09:04 AM</td>\n",
       "      <td>plantago lanceolata</td>\n",
       "      <td>plantain lancéolé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:08:19 AM</td>\n",
       "      <td>centaurea nigra</td>\n",
       "      <td>centaurée noire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:06:59 AM</td>\n",
       "      <td>plantago media</td>\n",
       "      <td>plantain moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:06:05 AM</td>\n",
       "      <td>chenopodium album agg.</td>\n",
       "      <td>chénopode blanc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:05:18 AM</td>\n",
       "      <td>centaurea jacea agg.</td>\n",
       "      <td>centaurée jacée</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            place        date         time                     sci  \\\n",
       "0  alleestrasse 1  2020-09-02  11:09:04 AM     plantago lanceolata   \n",
       "1  alleestrasse 1  2020-09-02  11:08:19 AM         centaurea nigra   \n",
       "2  alleestrasse 1  2020-09-02  11:06:59 AM          plantago media   \n",
       "3  alleestrasse 1  2020-09-02  11:06:05 AM  chenopodium album agg.   \n",
       "4  alleestrasse 1  2020-09-02  11:05:18 AM    centaurea jacea agg.   \n",
       "\n",
       "                name  \n",
       "0  plantain lancéolé  \n",
       "1    centaurée noire  \n",
       "2     plantain moyen  \n",
       "3    chénopode blanc  \n",
       "4    centaurée jacée  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# format scientific name in the sample dataset\n",
    "samples = data_and_keys['surveys'].copy()\n",
    "\n",
    "samples.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "37cb7790-181e-4d9b-a3a8-64b5e60f8f9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dictionary of replacement values that are incorrect\n",
    "replacedict = {\n",
    "    'verbanum bonariensis ':'verbena bonariensis',\n",
    "    'medicago varia':'medicago sativa',\n",
    "    \"oenothera\":\"oenothera biennis\",\n",
    "    \"geranium pratens\":\"geranium pratense\",\n",
    "    \"oenothera biennis \": \"oenothera biennis\",\n",
    "    \"oenothera biennis agg.\": \"oenothera biennis\",\n",
    "    \"solidalgo canadensis\": \"solidago canadensis\",\n",
    "    \"verbascum lynchitis\":\"verbascum lychnitis\",\n",
    "    \"verbascum negris\":\"verbascum nigrum\",\n",
    "    \"securigea varia\": \"securigera varia\",\n",
    "    \"melilotus officianalis\": \"melilotus officinalis\",\n",
    "    \"knautia maxima\": \"knautia dipsacifolia\",\n",
    "    \"hieracium aurantiacum\":\"pilosella aurantiaca\",\n",
    "    \"sysimbrium officinale\":\"sisymbrium officinale\",\n",
    "    \"geranium robertanium\":\"geranium robertianum\",\n",
    "    \"mycelis muralis\": \"lactuca muralis\",\n",
    "    \"calamintha-nepeta\":\"clinopodium nepeta\",\n",
    "    \"polygonum-persicaria\":\"persicaria maculosa\",\n",
    "    \"sorbus-aria\":\"aria edulis\",\n",
    "    \"taraxacum\": \"taraxacum officinale\",\n",
    "    \"jacobaea vulgaris\" : \"senecio jacobaea\",\n",
    "    \"erigeron canadensis\" : \"conyza canadensis\",\n",
    "    \"rorippa islandica\" : \"rorippa palustris\",\n",
    "    \"malus sylvestris\" : \"malus domestica\",\n",
    "    \"hylotelephium telephium\" : \"sedum telephium\",\n",
    "    \"lactuca muralis\": \"mycelis muralis\",\n",
    "    \"chaenorhinum minus\": \"chaenorrhinum minus\",\n",
    "    \"erigeron canadensis\": \"conzya canadensis\",\n",
    "    \"erigeron canadensis\": \"conzya canadensis\",\n",
    "    \"borkhausenia intermedia\": \"scandosorbus intermedia\",\n",
    "    \"centaurea nigra\" : \"centaurea jacea\"\n",
    "}\n",
    "\n",
    "# function to assign the correct value of the key is in the samples dictionary.\n",
    "def new_func(x,keys):\n",
    "    try:\n",
    "        data = keys[x]\n",
    "    except:\n",
    "        data = x\n",
    "    return data\n",
    "\n",
    "# apply the funtion to a copy of the surveys data set.\n",
    "samples[\"species2\"] = samples.sci.map(lambda x: new_func(x, replacedict))\n",
    "samples[\"species_slug\"] = samples.species2.map(lambda x: to_species_slug(x))\n",
    "\n",
    "# update the surveys dataset.\n",
    "data_and_keys.update({'surveys':samples})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "invisible-developer",
   "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>place</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>sci</th>\n",
       "      <th>name</th>\n",
       "      <th>species2</th>\n",
       "      <th>species_slug</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:09:04 AM</td>\n",
       "      <td>plantago lanceolata</td>\n",
       "      <td>plantain lancéolé</td>\n",
       "      <td>plantago lanceolata</td>\n",
       "      <td>plantago-lanceolata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:08:19 AM</td>\n",
       "      <td>centaurea nigra</td>\n",
       "      <td>centaurée noire</td>\n",
       "      <td>centaurea jacea</td>\n",
       "      <td>centaurea-jacea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:06:59 AM</td>\n",
       "      <td>plantago media</td>\n",
       "      <td>plantain moyen</td>\n",
       "      <td>plantago media</td>\n",
       "      <td>plantago-media</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:06:05 AM</td>\n",
       "      <td>chenopodium album agg.</td>\n",
       "      <td>chénopode blanc</td>\n",
       "      <td>chenopodium album agg.</td>\n",
       "      <td>chenopodium-album</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>alleestrasse 1</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>11:05:18 AM</td>\n",
       "      <td>centaurea jacea agg.</td>\n",
       "      <td>centaurée jacée</td>\n",
       "      <td>centaurea jacea agg.</td>\n",
       "      <td>centaurea-jacea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930</th>\n",
       "      <td>weissenau</td>\n",
       "      <td>2020-08-10</td>\n",
       "      <td>09:14:53</td>\n",
       "      <td>allium carinatum</td>\n",
       "      <td>ail caréné</td>\n",
       "      <td>allium carinatum</td>\n",
       "      <td>allium-carinatum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>931</th>\n",
       "      <td>weissenau</td>\n",
       "      <td>2020-08-10</td>\n",
       "      <td>09:09:48</td>\n",
       "      <td>viburnum lantana</td>\n",
       "      <td>mancienne, viorne lantane</td>\n",
       "      <td>viburnum lantana</td>\n",
       "      <td>viburnum-lantana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>932</th>\n",
       "      <td>weissenau</td>\n",
       "      <td>2020-08-10</td>\n",
       "      <td>09:07:35</td>\n",
       "      <td>filipendula ulmaria</td>\n",
       "      <td>reine des prés, spirée</td>\n",
       "      <td>filipendula ulmaria</td>\n",
       "      <td>filipendula-ulmaria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>933</th>\n",
       "      <td>weissenau</td>\n",
       "      <td>2020-08-10</td>\n",
       "      <td>09:05:26</td>\n",
       "      <td>lythrum salicaria</td>\n",
       "      <td>salicaire commune</td>\n",
       "      <td>lythrum salicaria</td>\n",
       "      <td>lythrum-salicaria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>934</th>\n",
       "      <td>weissenau</td>\n",
       "      <td>2020-08-10</td>\n",
       "      <td>09:04:12</td>\n",
       "      <td>lysimachia vulgaris</td>\n",
       "      <td>lysimaque commune</td>\n",
       "      <td>lysimachia vulgaris</td>\n",
       "      <td>lysimachia-vulgaris</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>935 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              place        date         time                     sci  \\\n",
       "0    alleestrasse 1  2020-09-02  11:09:04 AM     plantago lanceolata   \n",
       "1    alleestrasse 1  2020-09-02  11:08:19 AM         centaurea nigra   \n",
       "2    alleestrasse 1  2020-09-02  11:06:59 AM          plantago media   \n",
       "3    alleestrasse 1  2020-09-02  11:06:05 AM  chenopodium album agg.   \n",
       "4    alleestrasse 1  2020-09-02  11:05:18 AM    centaurea jacea agg.   \n",
       "..              ...         ...          ...                     ...   \n",
       "930       weissenau  2020-08-10     09:14:53        allium carinatum   \n",
       "931       weissenau  2020-08-10     09:09:48        viburnum lantana   \n",
       "932       weissenau  2020-08-10     09:07:35     filipendula ulmaria   \n",
       "933       weissenau  2020-08-10     09:05:26       lythrum salicaria   \n",
       "934       weissenau  2020-08-10     09:04:12     lysimachia vulgaris   \n",
       "\n",
       "                          name                species2         species_slug  \n",
       "0            plantain lancéolé     plantago lanceolata  plantago-lanceolata  \n",
       "1              centaurée noire         centaurea jacea      centaurea-jacea  \n",
       "2               plantain moyen          plantago media       plantago-media  \n",
       "3              chénopode blanc  chenopodium album agg.    chenopodium-album  \n",
       "4              centaurée jacée    centaurea jacea agg.      centaurea-jacea  \n",
       "..                         ...                     ...                  ...  \n",
       "930                 ail caréné        allium carinatum     allium-carinatum  \n",
       "931  mancienne, viorne lantane        viburnum lantana     viburnum-lantana  \n",
       "932     reine des prés, spirée     filipendula ulmaria  filipendula-ulmaria  \n",
       "933          salicaire commune       lythrum salicaria    lythrum-salicaria  \n",
       "934          lysimaque commune     lysimachia vulgaris  lysimachia-vulgaris  \n",
       "\n",
       "[935 rows x 7 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_and_keys[\"surveys\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "lovely-percentage",
   "metadata": {},
   "source": [
    "## Format date column to ISO standard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "dependent-equation",
   "metadata": {},
   "outputs": [],
   "source": [
    "# format dates in the sample dataset\n",
    "\n",
    "# function converts DD.MM.YYYY format to YYYY-MM-DD format, ignores if already in YYYY-MM-DD format\n",
    "# Do we even need this any more ? Looking at the cell above it appears the dates have been fixed at the source\n",
    "def change_string(x):\n",
    "    try:\n",
    "        s_data = x.split('.')\n",
    "        data = s_data[::-1]\n",
    "        data = \"-\".join(data)\n",
    "    except:\n",
    "        print(\"no luck\")\n",
    "        data = x\n",
    "    \n",
    "    return data\n",
    "\n",
    "# applies the function to a column in the samples data frame\n",
    "samples['new_date'] = samples.date.map(lambda x: change_string(x))\n",
    "\n",
    "# function makes a timestamp out of the YYYY-MM-DD string.\n",
    "# def make_timestamp(x):\n",
    "#     try:        \n",
    "#         data = dt.datetime.strptime(x, \"%Y-%m-%d\")        \n",
    "#     except:        \n",
    "#         data = 'no luck'\n",
    "    \n",
    "#     return data\n",
    "\n",
    "# run the make_timestamp function and store the results in the samples dataframe.\n",
    "# samples['stamp_date'] = samples.new_date.map(lambda x: make_timestamp(x))\n",
    "# samples['date'] = samples.stamp_date"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adequate-receiver",
   "metadata": {},
   "source": [
    "## The _place1_ column\n",
    "\n",
    "A coder friendly way to find the species but still maintain the proper nomenclature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "silver-toddler",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make place name slugs in the sample dataset\n",
    "#  change_place function turns \"place names 1\" into place-names-1\n",
    "def change_place(x):\n",
    "    data = x.split(\" \")\n",
    "    data = \"-\".join(data)\n",
    "    return data\n",
    "\n",
    "samples[\"place_slug\"] = samples.place.map(lambda x: change_place(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "duplicate-device",
   "metadata": {},
   "source": [
    "## Housekeeping and export of processed survey data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "c81901b7-1fdf-4f5e-a074-acf6b491f8a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean up redundant and unused column names\n",
    "# rename the place_slug column\n",
    "\n",
    "samples.rename(columns= {'place1':'place_slug', 'new_date':'str_date'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b06d9454-672f-43a0-8561-11cc9bb0a38a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['place', 'date', 'time', 'sci', 'name', 'species2', 'species_slug',\n",
       "       'str_date', 'place_slug'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samples.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "instructional-stupid",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove the unnecessary columns for this analysis\n",
    "samples.drop(['time',  'sci', 'name', 'place', 'species2'] , inplace=True, axis=1)\n",
    "\n",
    "samples['loc_date'] = list(zip(samples.place_slug, samples.date))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "ideal-antibody",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export samples to a .csv for later use\n",
    "samples.to_csv(F\"resources/preprocessed/hd_samples_2020.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "intensive-logging",
   "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>date</th>\n",
       "      <th>species_slug</th>\n",
       "      <th>str_date</th>\n",
       "      <th>place_slug</th>\n",
       "      <th>loc_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>plantago-lanceolata</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>alleestrasse-1</td>\n",
       "      <td>(alleestrasse-1, 2020-09-02)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>centaurea-jacea</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>alleestrasse-1</td>\n",
       "      <td>(alleestrasse-1, 2020-09-02)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>plantago-media</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>alleestrasse-1</td>\n",
       "      <td>(alleestrasse-1, 2020-09-02)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>chenopodium-album</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>alleestrasse-1</td>\n",
       "      <td>(alleestrasse-1, 2020-09-02)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>centaurea-jacea</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>alleestrasse-1</td>\n",
       "      <td>(alleestrasse-1, 2020-09-02)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date         species_slug    str_date      place_slug  \\\n",
       "0  2020-09-02  plantago-lanceolata  2020-09-02  alleestrasse-1   \n",
       "1  2020-09-02      centaurea-jacea  2020-09-02  alleestrasse-1   \n",
       "2  2020-09-02       plantago-media  2020-09-02  alleestrasse-1   \n",
       "3  2020-09-02    chenopodium-album  2020-09-02  alleestrasse-1   \n",
       "4  2020-09-02      centaurea-jacea  2020-09-02  alleestrasse-1   \n",
       "\n",
       "                       loc_date  \n",
       "0  (alleestrasse-1, 2020-09-02)  \n",
       "1  (alleestrasse-1, 2020-09-02)  \n",
       "2  (alleestrasse-1, 2020-09-02)  \n",
       "3  (alleestrasse-1, 2020-09-02)  \n",
       "4  (alleestrasse-1, 2020-09-02)  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samples.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "narrative-married",
   "metadata": {},
   "source": [
    "Subspecies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "brief-intro",
   "metadata": {},
   "outputs": [],
   "source": [
    "def account_for_subspecies(an_array, a_dict):\n",
    "    for element in an_array:\n",
    "        try:\n",
    "            a_dict[element[0]].append(element[1])\n",
    "        except:\n",
    "            a_dict[element[0]] = [element[1]]\n",
    "    return a_dict\n",
    "a_dict ={}\n",
    "\n",
    "for element in [fivex, welt_sut, watch_lists]:\n",
    "    for label in element:\n",
    "        # use this data frame\n",
    "        som_data = element[label].copy()\n",
    "        \n",
    "        # group by species slug and count the number of unique species values\n",
    "        c_s_p_s = som_data.groupby('species_slug', as_index=False).species.nunique()        \n",
    "        \n",
    "        # just the records with more than one species value\n",
    "        m_t_one = c_s_p_s[c_s_p_s.species > 1].species_slug\n",
    "        \n",
    "        # pair the species_slug to the species name:\n",
    "        mto = som_data.loc[som_data.species_slug.isin(m_t_one)][['species_slug', 'species']].copy().to_numpy()\n",
    "        \n",
    "        # update the dict\n",
    "        account_for_subspecies(mto, a_dict)\n",
    "\n",
    "# the species_slugs that account for more than one sub species\n",
    "sub_species_accounted =  {k:list(set(v)) for k,v in a_dict.items()}\n",
    "\n",
    "# the species_slugs\n",
    "gs_parent = list(sub_species_accounted.keys())\n",
    "\n",
    "# the number of species_slugs\n",
    "number_of_gs = len(gs_parent)\n",
    "\n",
    "# the the number of sub species accounted for\n",
    "number_of_ss = sum([len(v) for k,v in sub_species_accounted.items()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "indian-involvement",
   "metadata": {},
   "source": [
    "### Key the watch lists to genus-species\n",
    "\n",
    "Identify which species are on which watch list. Produce a boolean matrix with species_slug as index and watch list name for the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "premier-meter",
   "metadata": {},
   "outputs": [],
   "source": [
    "the_lists = list(watch_lists.keys())\n",
    "\n",
    "keep = []\n",
    "\n",
    "for element in the_lists:\n",
    "    a = watch_lists[element][['species_slug','watch_list']]\n",
    "    keep.append(a)\n",
    "wl_species = pd.concat(keep)\n",
    "m_ap_columns = wl_species.watch_list.unique()\n",
    "\n",
    "for col in m_ap_columns:    \n",
    "    wl_species[col] = wl_species['watch_list'] == col\n",
    "\n",
    "wl_sp_map =wl_species.groupby(['species_slug']).sum()\n",
    "\n",
    "wl_sp_map['lists'] = wl_sp_map.index.map(lambda x: wl_species[wl_species.species_slug == x]['watch_list'].unique())\n",
    "\n",
    "a_filename = \"species_keyed_to_watch_list.csv\"\n",
    "wl_sp_map.reset_index().to_csv(F\"resources/{a_filename}\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "greenhouse-moisture",
   "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>BL</th>\n",
       "      <th>WL</th>\n",
       "      <th>under_sampled</th>\n",
       "      <th>red_list</th>\n",
       "      <th>cert_list</th>\n",
       "      <th>lists</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>species_slug</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>abies-alba</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>[red_list, cert_list]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abutilon-theophrasti</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>[BL]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>acacia-dealbata</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>[WL]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>acer-campestre</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>[red_list, cert_list]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>acer-opalus</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>[red_list, cert_list]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>woodsia-ilvensis</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[red_list]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>woodsia-pulchella</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[red_list]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xanthium-strumarium</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[under_sampled, red_list]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xeranthemum-inapertum</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[red_list]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zannichellia-palustris</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[red_list]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2619 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        BL  WL  under_sampled  red_list  cert_list  \\\n",
       "species_slug                                                         \n",
       "abies-alba               0   0              0         1          1   \n",
       "abutilon-theophrasti     1   0              0         0          0   \n",
       "acacia-dealbata          0   1              0         0          0   \n",
       "acer-campestre           0   0              0         1          1   \n",
       "acer-opalus              0   0              0         1          1   \n",
       "...                     ..  ..            ...       ...        ...   \n",
       "woodsia-ilvensis         0   0              0         1          0   \n",
       "woodsia-pulchella        0   0              0         1          0   \n",
       "xanthium-strumarium      0   0              1         1          0   \n",
       "xeranthemum-inapertum    0   0              0         1          0   \n",
       "zannichellia-palustris   0   0              0         1          0   \n",
       "\n",
       "                                            lists  \n",
       "species_slug                                       \n",
       "abies-alba                  [red_list, cert_list]  \n",
       "abutilon-theophrasti                         [BL]  \n",
       "acacia-dealbata                              [WL]  \n",
       "acer-campestre              [red_list, cert_list]  \n",
       "acer-opalus                 [red_list, cert_list]  \n",
       "...                                           ...  \n",
       "woodsia-ilvensis                       [red_list]  \n",
       "woodsia-pulchella                      [red_list]  \n",
       "xanthium-strumarium     [under_sampled, red_list]  \n",
       "xeranthemum-inapertum                  [red_list]  \n",
       "zannichellia-palustris                 [red_list]  \n",
       "\n",
       "[2619 rows x 6 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wl_sp_map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "comparable-sweet",
   "metadata": {},
   "outputs": [],
   "source": [
    "# nt = wl_sp_map['lists'].to_dict()\n",
    "\n",
    "# ntx = {k:list(v) for k,v in nt.items()}\n",
    "\n",
    "# with open(F\"{here}/output/flora_list.json\",\"w\") as afile:\n",
    "#     json.dump(ntx,afile)\n",
    "# wl_sp_map.to_csv(F\"{here}/resources/preprocessed/aggregated_list.csv\",index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d5a1226-19dd-4d69-8249-eb821d83ca3b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}