Pandas and Numpys Homework - Swiss Data Example.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7a1d6c09",
   "metadata": {},
   "source": [
    "### Data provided by the Swiss National Science Foundation\n",
    "1. list of all projects to which funds have been allocated since 1975. 2. all people to which funds have been awarded during the same period"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09236a05",
   "metadata": {},
   "source": [
    "### objectives\n",
    "1. How much money is awarded on average depending on gender?\n",
    "2. How long does it take for a new researcher to apply for grant? (operationalized as time to go from employee to applicant status)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "978c9b6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba3b2139",
   "metadata": {},
   "source": [
    "#### import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4706bfa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# local import; downloaded from \"http://p3.snf.ch/P3Export/\"\n",
    "projects = pd.read_csv('Data/P3_GrantExport.csv',sep = ';')\n",
    "persons = pd.read_csv('Data/P3_PersonExport.csv',sep = ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d351778b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# quick look at the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cfdec328",
   "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>Project Number</th>\n",
       "      <th>Project Number String</th>\n",
       "      <th>Project Title</th>\n",
       "      <th>Project Title English</th>\n",
       "      <th>Responsible Applicant</th>\n",
       "      <th>Funding Instrument</th>\n",
       "      <th>Funding Instrument Hierarchy</th>\n",
       "      <th>Institution</th>\n",
       "      <th>Institution Country</th>\n",
       "      <th>University</th>\n",
       "      <th>Discipline Number</th>\n",
       "      <th>Discipline Name</th>\n",
       "      <th>Discipline Name Hierarchy</th>\n",
       "      <th>All disciplines</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Approved Amount</th>\n",
       "      <th>Keywords</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1000-000001</td>\n",
       "      <td>Schlussband (Bd. VI) der Jacob Burckhardt-Biog...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kaegi Werner</td>\n",
       "      <td>Project funding (Div. I-III)</td>\n",
       "      <td>Project funding</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unassignable - NA</td>\n",
       "      <td>10302</td>\n",
       "      <td>Swiss history</td>\n",
       "      <td>Humanities and Social Sciences;Theology &amp; reli...</td>\n",
       "      <td>10302</td>\n",
       "      <td>01.10.1975</td>\n",
       "      <td>30.09.1976</td>\n",
       "      <td>11619.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1000-000004</td>\n",
       "      <td>Batterie de tests à l'usage des enseignants po...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Massarenti Léonard</td>\n",
       "      <td>Project funding (Div. I-III)</td>\n",
       "      <td>Project funding</td>\n",
       "      <td>Faculté de Psychologie et des Sciences de l'Ed...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Geneva - GE</td>\n",
       "      <td>10104</td>\n",
       "      <td>Educational science and Pedagogy</td>\n",
       "      <td>Humanities and Social Sciences;Psychology, edu...</td>\n",
       "      <td>10104</td>\n",
       "      <td>01.10.1975</td>\n",
       "      <td>30.09.1976</td>\n",
       "      <td>41022.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>1000-000005</td>\n",
       "      <td>Kritische Erstausgabe der 'Evidentiae contra D...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kommission für das Corpus philosophorum medii ...</td>\n",
       "      <td>Project funding (Div. I-III)</td>\n",
       "      <td>Project funding</td>\n",
       "      <td>Kommission für das Corpus philosophorum medii ...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>Non-profit organisations (libraries, museums, ...</td>\n",
       "      <td>10101</td>\n",
       "      <td>Philosophy</td>\n",
       "      <td>Humanities and Social Sciences;Linguistics and...</td>\n",
       "      <td>10101</td>\n",
       "      <td>01.03.1976</td>\n",
       "      <td>28.02.1985</td>\n",
       "      <td>79732.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>1000-000006</td>\n",
       "      <td>Katalog der datierten Handschriften in der Sch...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burckhardt Max</td>\n",
       "      <td>Project funding (Div. I-III)</td>\n",
       "      <td>Project funding</td>\n",
       "      <td>Abteilung Handschriften und Alte Drucke Univer...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Basel - BS</td>\n",
       "      <td>10302</td>\n",
       "      <td>Swiss history</td>\n",
       "      <td>Humanities and Social Sciences;Theology &amp; reli...</td>\n",
       "      <td>10302</td>\n",
       "      <td>01.10.1975</td>\n",
       "      <td>30.09.1976</td>\n",
       "      <td>52627.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>1000-000007</td>\n",
       "      <td>Wissenschaftliche Mitarbeit am Thesaurus Lingu...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Schweiz. Thesauruskommission</td>\n",
       "      <td>Project funding (Div. I-III)</td>\n",
       "      <td>Project funding</td>\n",
       "      <td>Schweiz. Thesauruskommission</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>Non-profit organisations (libraries, museums, ...</td>\n",
       "      <td>10303</td>\n",
       "      <td>Ancient history and Classical studies</td>\n",
       "      <td>Humanities and Social Sciences;Theology &amp; reli...</td>\n",
       "      <td>10303</td>\n",
       "      <td>01.01.1976</td>\n",
       "      <td>30.04.1978</td>\n",
       "      <td>120042.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Project Number Project Number String  \\\n",
       "0               1           1000-000001   \n",
       "1               4           1000-000004   \n",
       "2               5           1000-000005   \n",
       "3               6           1000-000006   \n",
       "4               7           1000-000007   \n",
       "\n",
       "                                       Project Title Project Title English  \\\n",
       "0  Schlussband (Bd. VI) der Jacob Burckhardt-Biog...                   NaN   \n",
       "1  Batterie de tests à l'usage des enseignants po...                   NaN   \n",
       "2  Kritische Erstausgabe der 'Evidentiae contra D...                   NaN   \n",
       "3  Katalog der datierten Handschriften in der Sch...                   NaN   \n",
       "4  Wissenschaftliche Mitarbeit am Thesaurus Lingu...                   NaN   \n",
       "\n",
       "                               Responsible Applicant  \\\n",
       "0                                       Kaegi Werner   \n",
       "1                                 Massarenti Léonard   \n",
       "2  Kommission für das Corpus philosophorum medii ...   \n",
       "3                                     Burckhardt Max   \n",
       "4                       Schweiz. Thesauruskommission   \n",
       "\n",
       "             Funding Instrument Funding Instrument Hierarchy  \\\n",
       "0  Project funding (Div. I-III)              Project funding   \n",
       "1  Project funding (Div. I-III)              Project funding   \n",
       "2  Project funding (Div. I-III)              Project funding   \n",
       "3  Project funding (Div. I-III)              Project funding   \n",
       "4  Project funding (Div. I-III)              Project funding   \n",
       "\n",
       "                                         Institution Institution Country  \\\n",
       "0                                                NaN                 NaN   \n",
       "1  Faculté de Psychologie et des Sciences de l'Ed...         Switzerland   \n",
       "2  Kommission für das Corpus philosophorum medii ...         Switzerland   \n",
       "3  Abteilung Handschriften und Alte Drucke Univer...         Switzerland   \n",
       "4                       Schweiz. Thesauruskommission         Switzerland   \n",
       "\n",
       "                                          University  Discipline Number  \\\n",
       "0                                  Unassignable - NA              10302   \n",
       "1                          University of Geneva - GE              10104   \n",
       "2  Non-profit organisations (libraries, museums, ...              10101   \n",
       "3                           University of Basel - BS              10302   \n",
       "4  Non-profit organisations (libraries, museums, ...              10303   \n",
       "\n",
       "                         Discipline Name  \\\n",
       "0                          Swiss history   \n",
       "1       Educational science and Pedagogy   \n",
       "2                             Philosophy   \n",
       "3                          Swiss history   \n",
       "4  Ancient history and Classical studies   \n",
       "\n",
       "                           Discipline Name Hierarchy All disciplines  \\\n",
       "0  Humanities and Social Sciences;Theology & reli...           10302   \n",
       "1  Humanities and Social Sciences;Psychology, edu...           10104   \n",
       "2  Humanities and Social Sciences;Linguistics and...           10101   \n",
       "3  Humanities and Social Sciences;Theology & reli...           10302   \n",
       "4  Humanities and Social Sciences;Theology & reli...           10303   \n",
       "\n",
       "   Start Date    End Date Approved Amount Keywords  \n",
       "0  01.10.1975  30.09.1976        11619.00      NaN  \n",
       "1  01.10.1975  30.09.1976        41022.00      NaN  \n",
       "2  01.03.1976  28.02.1985        79732.00      NaN  \n",
       "3  01.10.1975  30.09.1976        52627.00      NaN  \n",
       "4  01.01.1976  30.04.1978       120042.00      NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "projects.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dc902939",
   "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>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>Person ID SNSF</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>Projects as Applicant</th>\n",
       "      <th>Projects as Partner</th>\n",
       "      <th>Projects as Practice Partner</th>\n",
       "      <th>Projects as Employee</th>\n",
       "      <th>Projects as Contact Person</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Davide</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53856</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36549</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>132628</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67368</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A. Jafari</td>\n",
       "      <td>Golnaz</td>\n",
       "      <td>female</td>\n",
       "      <td>Universität Luzern</td>\n",
       "      <td>Luzern</td>\n",
       "      <td>747886</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191432</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aaberg</td>\n",
       "      <td>Johan</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>575257</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>119868</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aahman</td>\n",
       "      <td>Josefin</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>629557</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>141014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Last Name First Name  Gender      Institute Name Institute Place  \\\n",
       "0    a Marca     Davide    male                 NaN             NaN   \n",
       "1    a Marca     Andrea    male                 NaN             NaN   \n",
       "2  A. Jafari     Golnaz  female  Universität Luzern          Luzern   \n",
       "3     Aaberg      Johan    male                 NaN             NaN   \n",
       "4     Aahman    Josefin  female                 NaN             NaN   \n",
       "\n",
       "   Person ID SNSF OCRID Projects as responsible Applicant  \\\n",
       "0           53856   NaN                               NaN   \n",
       "1          132628   NaN                             67368   \n",
       "2          747886   NaN                            191432   \n",
       "3          575257   NaN                               NaN   \n",
       "4          629557   NaN                               NaN   \n",
       "\n",
       "  Projects as Applicant Projects as Partner Projects as Practice Partner  \\\n",
       "0                   NaN                 NaN                          NaN   \n",
       "1                   NaN                 NaN                          NaN   \n",
       "2                   NaN                 NaN                          NaN   \n",
       "3                   NaN                 NaN                          NaN   \n",
       "4                   NaN                 NaN                          NaN   \n",
       "\n",
       "  Projects as Employee Projects as Contact Person  \n",
       "0                36549                        NaN  \n",
       "1                  NaN                        NaN  \n",
       "2                  NaN                        NaN  \n",
       "3               119868                        NaN  \n",
       "4               141014                        NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "persons.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9488f4fc",
   "metadata": {},
   "source": [
    "#### merge tables\n",
    "First we need to figure out how to merge the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1ae798e7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Last Name                                                                    Bodenmann\n",
       "First Name                                                                         Guy\n",
       "Gender                                                                            male\n",
       "Institute Name                       Lehrstuhl für Klinische Psychologie Kinder/Jug...\n",
       "Institute Place                                                                 Zürich\n",
       "Person ID SNSF                                                                   47670\n",
       "OCRID                                                              0000-0003-0964-6409\n",
       "Projects as responsible Applicant    46820;56660;62901;109547;115948;128960;129627;...\n",
       "Projects as Applicant                                             112141;122090;166348\n",
       "Projects as Partner                                                                NaN\n",
       "Projects as Practice Partner                                                       NaN\n",
       "Projects as Employee                                                             62901\n",
       "Projects as Contact Person                                                         NaN\n",
       "Name: 10041, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's sample an individual researcher\n",
    "persons.iloc[10041]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ecd9854",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Most important are the entries \"Projects as Employee\" and \"Projects as Responsible Applicant\"\n",
    "# These indicate the projects applied for and will need to be matched to the project database\n",
    "# From there, summary wrt the objects should be straightforward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c60a5c4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# But how to merge?\n",
    "# By project id is the obvious solution, but they are lumped together as a CSV nested list"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f72da7e4",
   "metadata": {},
   "source": [
    "#### split strings and reassign to proper rows\n",
    "Like R, we can use a string-split function and apply it across the relevant columns.\n",
    "We will split, clean, and then assign to the appropriate personal ids."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0eb581e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, we run the string splits for each entry and save\n",
    "projID_a = persons['Projects as responsible Applicant'].apply(lambda x: x.split(';') if not pd.isna(x) else np.nan)\n",
    "projID_e = persons['Projects as Employee'].apply(lambda x: x.split(';') if not pd.isna(x) else np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "81622322",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                             NaN\n",
       "1                                         [67368]\n",
       "2                                        [191432]\n",
       "3                                             NaN\n",
       "4                                             NaN\n",
       "                           ...                   \n",
       "110811    [52821, 143769, 147153, 165510, 183584]\n",
       "110812                                        NaN\n",
       "110813                                        NaN\n",
       "110814                                        NaN\n",
       "110815                                        NaN\n",
       "Name: Projects as responsible Applicant, Length: 110816, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now lets take a quick scan of the entire result\n",
    "projID_a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "373bbee6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Quite a few NaN, meaning these employees had no relevant activity wrt grant applications...\n",
    "# As far as merging is concerned, we can therefore safely remove the entries that contain NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6156a717",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          True\n",
       "1         False\n",
       "2         False\n",
       "3          True\n",
       "4          True\n",
       "          ...  \n",
       "110811    False\n",
       "110812     True\n",
       "110813     True\n",
       "110814     True\n",
       "110815     True\n",
       "Name: Projects as responsible Applicant, Length: 110816, dtype: bool"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas has a function that we can use to index columns with NaN, pd.isna; lets\n",
    "pd.isna(projID_a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39479e86",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Seems okay..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a20cb094",
   "metadata": {},
   "outputs": [],
   "source": [
    "# So now, we use it to create \"clean\" copies of our indexing list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9564463b",
   "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>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>Person ID SNSF</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>Projects as Applicant</th>\n",
       "      <th>Projects as Partner</th>\n",
       "      <th>Projects as Practice Partner</th>\n",
       "      <th>Projects as Employee</th>\n",
       "      <th>Projects as Contact Person</th>\n",
       "      <th>projID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>132628</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67368</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[67368]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A. Jafari</td>\n",
       "      <td>Golnaz</td>\n",
       "      <td>female</td>\n",
       "      <td>Universität Luzern</td>\n",
       "      <td>Luzern</td>\n",
       "      <td>747886</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191432</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[191432]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Aapro</td>\n",
       "      <td>Matti S.</td>\n",
       "      <td>male</td>\n",
       "      <td>Clinique de Genolier F.M.H. Oncologie-Hématolo...</td>\n",
       "      <td>Genolier</td>\n",
       "      <td>3268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8532;9513</td>\n",
       "      <td>8155</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[8532, 9513]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Aas</td>\n",
       "      <td>Gregor</td>\n",
       "      <td>male</td>\n",
       "      <td>Lehrstuhl für Pflanzenphysiologie Universität ...</td>\n",
       "      <td>Bayreuth</td>\n",
       "      <td>36412</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[52037]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Abadier</td>\n",
       "      <td>Michael</td>\n",
       "      <td>male</td>\n",
       "      <td>The La Jolla Institute for Allergy and Immunology</td>\n",
       "      <td>La Jolla</td>\n",
       "      <td>592871</td>\n",
       "      <td>NaN</td>\n",
       "      <td>158972</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>133092</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[158972]</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",
       "      <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>110788</th>\n",
       "      <td>Zwolensky</td>\n",
       "      <td>Anton</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>594698</td>\n",
       "      <td>NaN</td>\n",
       "      <td>138839</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[138839]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110792</th>\n",
       "      <td>Zwyssig</td>\n",
       "      <td>Philipp</td>\n",
       "      <td>male</td>\n",
       "      <td>Abteilung für Neuere Geschichte Historisches I...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>624211</td>\n",
       "      <td>NaN</td>\n",
       "      <td>179533</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>144564</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[179533]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110807</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Martin</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75080</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46488</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[46488]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110809</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Alain</td>\n",
       "      <td>male</td>\n",
       "      <td>Chaire de droit international public et de dro...</td>\n",
       "      <td>Fribourg</td>\n",
       "      <td>581625</td>\n",
       "      <td>NaN</td>\n",
       "      <td>148582</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>130447</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[148582]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110811</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[52821, 143769, 147153, 165510, 183584]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28704 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Last Name First Name  Gender  \\\n",
       "1         a Marca     Andrea    male   \n",
       "2       A. Jafari     Golnaz  female   \n",
       "7           Aapro   Matti S.    male   \n",
       "11            Aas     Gregor    male   \n",
       "17        Abadier    Michael    male   \n",
       "...           ...        ...     ...   \n",
       "110788  Zwolensky      Anton    male   \n",
       "110792    Zwyssig    Philipp    male   \n",
       "110807     Zysset     Martin    male   \n",
       "110809     Zysset      Alain    male   \n",
       "110811     Zysset   Philippe    male   \n",
       "\n",
       "                                           Institute Name Institute Place  \\\n",
       "1                                                     NaN             NaN   \n",
       "2                                      Universität Luzern          Luzern   \n",
       "7       Clinique de Genolier F.M.H. Oncologie-Hématolo...        Genolier   \n",
       "11      Lehrstuhl für Pflanzenphysiologie Universität ...        Bayreuth   \n",
       "17      The La Jolla Institute for Allergy and Immunology        La Jolla   \n",
       "...                                                   ...             ...   \n",
       "110788                                                NaN             NaN   \n",
       "110792  Abteilung für Neuere Geschichte Historisches I...            Bern   \n",
       "110807                                                NaN             NaN   \n",
       "110809  Chaire de droit international public et de dro...        Fribourg   \n",
       "110811  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "\n",
       "        Person ID SNSF                OCRID  \\\n",
       "1               132628                  NaN   \n",
       "2               747886                  NaN   \n",
       "7                 3268                  NaN   \n",
       "11               36412                  NaN   \n",
       "17              592871                  NaN   \n",
       "...                ...                  ...   \n",
       "110788          594698                  NaN   \n",
       "110792          624211                  NaN   \n",
       "110807           75080                  NaN   \n",
       "110809          581625                  NaN   \n",
       "110811           40950  0000-0002-4712-7047   \n",
       "\n",
       "        Projects as responsible Applicant    Projects as Applicant  \\\n",
       "1                                   67368                      NaN   \n",
       "2                                  191432                      NaN   \n",
       "7                               8532;9513                     8155   \n",
       "11                                  52037                      NaN   \n",
       "17                                 158972                      NaN   \n",
       "...                                   ...                      ...   \n",
       "110788                             138839                      NaN   \n",
       "110792                             179533                      NaN   \n",
       "110807                              46488                      NaN   \n",
       "110809                             148582                      NaN   \n",
       "110811  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "\n",
       "       Projects as Partner Projects as Practice Partner Projects as Employee  \\\n",
       "1                      NaN                          NaN                  NaN   \n",
       "2                      NaN                          NaN                  NaN   \n",
       "7                      NaN                          NaN                  NaN   \n",
       "11                     NaN                          NaN                  NaN   \n",
       "17                     NaN                          NaN               133092   \n",
       "...                    ...                          ...                  ...   \n",
       "110788                 NaN                          NaN                  NaN   \n",
       "110792                 NaN                          NaN               144564   \n",
       "110807                 NaN                          NaN                  NaN   \n",
       "110809                 NaN                          NaN               130447   \n",
       "110811                 NaN                          NaN                33565   \n",
       "\n",
       "       Projects as Contact Person                                   projID  \n",
       "1                             NaN                                  [67368]  \n",
       "2                             NaN                                 [191432]  \n",
       "7                             NaN                             [8532, 9513]  \n",
       "11                            NaN                                  [52037]  \n",
       "17                            NaN                                 [158972]  \n",
       "...                           ...                                      ...  \n",
       "110788                        NaN                                 [138839]  \n",
       "110792                        NaN                                 [179533]  \n",
       "110807                        NaN                                  [46488]  \n",
       "110809                        NaN                                 [148582]  \n",
       "110811                        NaN  [52821, 143769, 147153, 165510, 183584]  \n",
       "\n",
       "[28704 rows x 14 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First, for applicants\n",
    "applicants = persons.copy()\n",
    "applicants['projID'] = projID_a\n",
    "applicants = applicants[~pd.isna(projID_a)]\n",
    "applicants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ed078174",
   "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>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>Person ID SNSF</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>Projects as Applicant</th>\n",
       "      <th>Projects as Partner</th>\n",
       "      <th>Projects as Practice Partner</th>\n",
       "      <th>Projects as Employee</th>\n",
       "      <th>Projects as Contact Person</th>\n",
       "      <th>projID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Davide</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53856</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36549</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[36549]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aaberg</td>\n",
       "      <td>Johan</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>575257</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>119868</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[119868]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aahman</td>\n",
       "      <td>Josefin</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>629557</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>141014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[141014]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Aalai</td>\n",
       "      <td>Mandana</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54856</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[32102]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Äämismaa</td>\n",
       "      <td>Päivi</td>\n",
       "      <td>male</td>\n",
       "      <td>Abteilung Biophysikalische Chemie Biozentrum d...</td>\n",
       "      <td>Basel</td>\n",
       "      <td>509100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58800;107793</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[58800, 107793]</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",
       "      <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>110811</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[33565]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110812</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Simonetta</td>\n",
       "      <td>female</td>\n",
       "      <td>Institut für Computersysteme ETH Zürich</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>554773</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>122957</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[122957]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110813</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Annina</td>\n",
       "      <td>female</td>\n",
       "      <td>Departement Gesundheit Institut für Gesundheit...</td>\n",
       "      <td>Winterthur</td>\n",
       "      <td>636704</td>\n",
       "      <td>0000-0002-9871-5324</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>147673</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[147673]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110814</th>\n",
       "      <td>Zytowski</td>\n",
       "      <td>Torsten</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52396</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32730</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[32730]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110815</th>\n",
       "      <td>Zyuzin</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>male</td>\n",
       "      <td>Departement Physik Universität Basel</td>\n",
       "      <td>Basel</td>\n",
       "      <td>616410</td>\n",
       "      <td>0000-0003-2323-2886</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>134470;145281;152896</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[134470, 145281, 152896]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>81200 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Last Name First Name  Gender  \\\n",
       "0        a Marca     Davide    male   \n",
       "3         Aaberg      Johan    male   \n",
       "4         Aahman    Josefin  female   \n",
       "5          Aalai    Mandana  female   \n",
       "6       Äämismaa      Päivi    male   \n",
       "...          ...        ...     ...   \n",
       "110811    Zysset   Philippe    male   \n",
       "110812    Zysset  Simonetta  female   \n",
       "110813    Zysset     Annina  female   \n",
       "110814  Zytowski    Torsten    male   \n",
       "110815    Zyuzin  Alexander    male   \n",
       "\n",
       "                                           Institute Name Institute Place  \\\n",
       "0                                                     NaN             NaN   \n",
       "3                                                     NaN             NaN   \n",
       "4                                                     NaN             NaN   \n",
       "5                                                     NaN             NaN   \n",
       "6       Abteilung Biophysikalische Chemie Biozentrum d...           Basel   \n",
       "...                                                   ...             ...   \n",
       "110811  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "110812            Institut für Computersysteme ETH Zürich          Zürich   \n",
       "110813  Departement Gesundheit Institut für Gesundheit...      Winterthur   \n",
       "110814                                                NaN             NaN   \n",
       "110815               Departement Physik Universität Basel           Basel   \n",
       "\n",
       "        Person ID SNSF                OCRID  \\\n",
       "0                53856                  NaN   \n",
       "3               575257                  NaN   \n",
       "4               629557                  NaN   \n",
       "5                54856                  NaN   \n",
       "6               509100                  NaN   \n",
       "...                ...                  ...   \n",
       "110811           40950  0000-0002-4712-7047   \n",
       "110812          554773                  NaN   \n",
       "110813          636704  0000-0002-9871-5324   \n",
       "110814           52396                  NaN   \n",
       "110815          616410  0000-0003-2323-2886   \n",
       "\n",
       "        Projects as responsible Applicant    Projects as Applicant  \\\n",
       "0                                     NaN                      NaN   \n",
       "3                                     NaN                      NaN   \n",
       "4                                     NaN                      NaN   \n",
       "5                                     NaN                      NaN   \n",
       "6                                     NaN                      NaN   \n",
       "...                                   ...                      ...   \n",
       "110811  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "110812                                NaN                      NaN   \n",
       "110813                                NaN                      NaN   \n",
       "110814                                NaN                      NaN   \n",
       "110815                                NaN                      NaN   \n",
       "\n",
       "       Projects as Partner Projects as Practice Partner  Projects as Employee  \\\n",
       "0                      NaN                          NaN                 36549   \n",
       "3                      NaN                          NaN                119868   \n",
       "4                      NaN                          NaN                141014   \n",
       "5                      NaN                          NaN                 32102   \n",
       "6                      NaN                          NaN          58800;107793   \n",
       "...                    ...                          ...                   ...   \n",
       "110811                 NaN                          NaN                 33565   \n",
       "110812                 NaN                          NaN                122957   \n",
       "110813                 NaN                          NaN                147673   \n",
       "110814                 NaN                          NaN                 32730   \n",
       "110815                 NaN                          NaN  134470;145281;152896   \n",
       "\n",
       "       Projects as Contact Person                    projID  \n",
       "0                             NaN                   [36549]  \n",
       "3                             NaN                  [119868]  \n",
       "4                             NaN                  [141014]  \n",
       "5                             NaN                   [32102]  \n",
       "6                             NaN           [58800, 107793]  \n",
       "...                           ...                       ...  \n",
       "110811                        NaN                   [33565]  \n",
       "110812                        NaN                  [122957]  \n",
       "110813                        NaN                  [147673]  \n",
       "110814                        NaN                   [32730]  \n",
       "110815                        NaN  [134470, 145281, 152896]  \n",
       "\n",
       "[81200 rows x 14 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Next, for employees\n",
    "employees = persons.copy()\n",
    "employees['projID'] = projID_e\n",
    "employees = employees[~pd.isna(projID_e)]\n",
    "employees"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "46f75fdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# projIDs are there now, but still presents as a list.\n",
    "# we will next use Pandas equivalent of sapply to \"explode\" them out to individual rows for each project\n",
    "applicants = applicants.explode('projID')\n",
    "employees = employees.explode('projID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "962ce709",
   "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>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>Person ID SNSF</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>Projects as Applicant</th>\n",
       "      <th>Projects as Partner</th>\n",
       "      <th>Projects as Practice Partner</th>\n",
       "      <th>Projects as Employee</th>\n",
       "      <th>Projects as Contact Person</th>\n",
       "      <th>projID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>132628</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67368</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A. Jafari</td>\n",
       "      <td>Golnaz</td>\n",
       "      <td>female</td>\n",
       "      <td>Universität Luzern</td>\n",
       "      <td>Luzern</td>\n",
       "      <td>747886</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191432</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Aapro</td>\n",
       "      <td>Matti S.</td>\n",
       "      <td>male</td>\n",
       "      <td>Clinique de Genolier F.M.H. Oncologie-Hématolo...</td>\n",
       "      <td>Genolier</td>\n",
       "      <td>3268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8532;9513</td>\n",
       "      <td>8155</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Aapro</td>\n",
       "      <td>Matti S.</td>\n",
       "      <td>male</td>\n",
       "      <td>Clinique de Genolier F.M.H. Oncologie-Hématolo...</td>\n",
       "      <td>Genolier</td>\n",
       "      <td>3268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8532;9513</td>\n",
       "      <td>8155</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Aas</td>\n",
       "      <td>Gregor</td>\n",
       "      <td>male</td>\n",
       "      <td>Lehrstuhl für Pflanzenphysiologie Universität ...</td>\n",
       "      <td>Bayreuth</td>\n",
       "      <td>36412</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52037</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",
       "      <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>110811</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110811</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>143769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110811</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>147153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110811</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>165510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110811</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>183584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74657 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Last Name First Name  Gender  \\\n",
       "1         a Marca     Andrea    male   \n",
       "2       A. Jafari     Golnaz  female   \n",
       "7           Aapro   Matti S.    male   \n",
       "7           Aapro   Matti S.    male   \n",
       "11            Aas     Gregor    male   \n",
       "...           ...        ...     ...   \n",
       "110811     Zysset   Philippe    male   \n",
       "110811     Zysset   Philippe    male   \n",
       "110811     Zysset   Philippe    male   \n",
       "110811     Zysset   Philippe    male   \n",
       "110811     Zysset   Philippe    male   \n",
       "\n",
       "                                           Institute Name Institute Place  \\\n",
       "1                                                     NaN             NaN   \n",
       "2                                      Universität Luzern          Luzern   \n",
       "7       Clinique de Genolier F.M.H. Oncologie-Hématolo...        Genolier   \n",
       "7       Clinique de Genolier F.M.H. Oncologie-Hématolo...        Genolier   \n",
       "11      Lehrstuhl für Pflanzenphysiologie Universität ...        Bayreuth   \n",
       "...                                                   ...             ...   \n",
       "110811  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "110811  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "110811  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "110811  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "110811  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "\n",
       "        Person ID SNSF                OCRID  \\\n",
       "1               132628                  NaN   \n",
       "2               747886                  NaN   \n",
       "7                 3268                  NaN   \n",
       "7                 3268                  NaN   \n",
       "11               36412                  NaN   \n",
       "...                ...                  ...   \n",
       "110811           40950  0000-0002-4712-7047   \n",
       "110811           40950  0000-0002-4712-7047   \n",
       "110811           40950  0000-0002-4712-7047   \n",
       "110811           40950  0000-0002-4712-7047   \n",
       "110811           40950  0000-0002-4712-7047   \n",
       "\n",
       "        Projects as responsible Applicant    Projects as Applicant  \\\n",
       "1                                   67368                      NaN   \n",
       "2                                  191432                      NaN   \n",
       "7                               8532;9513                     8155   \n",
       "7                               8532;9513                     8155   \n",
       "11                                  52037                      NaN   \n",
       "...                                   ...                      ...   \n",
       "110811  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "110811  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "110811  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "110811  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "110811  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "\n",
       "       Projects as Partner Projects as Practice Partner Projects as Employee  \\\n",
       "1                      NaN                          NaN                  NaN   \n",
       "2                      NaN                          NaN                  NaN   \n",
       "7                      NaN                          NaN                  NaN   \n",
       "7                      NaN                          NaN                  NaN   \n",
       "11                     NaN                          NaN                  NaN   \n",
       "...                    ...                          ...                  ...   \n",
       "110811                 NaN                          NaN                33565   \n",
       "110811                 NaN                          NaN                33565   \n",
       "110811                 NaN                          NaN                33565   \n",
       "110811                 NaN                          NaN                33565   \n",
       "110811                 NaN                          NaN                33565   \n",
       "\n",
       "       Projects as Contact Person  projID  \n",
       "1                             NaN   67368  \n",
       "2                             NaN  191432  \n",
       "7                             NaN    8532  \n",
       "7                             NaN    9513  \n",
       "11                            NaN   52037  \n",
       "...                           ...     ...  \n",
       "110811                        NaN   52821  \n",
       "110811                        NaN  143769  \n",
       "110811                        NaN  147153  \n",
       "110811                        NaN  165510  \n",
       "110811                        NaN  183584  \n",
       "\n",
       "[74657 rows x 14 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's check....\n",
    "applicants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a87c2db6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Looks good; individual project ids now occupy their own row with appropriate duplication of other info"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a26bb0d2",
   "metadata": {},
   "source": [
    "#### Let's continue our cleaning operation..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c8d03eb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74657"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First confirm number of entries\n",
    "len(applicants)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "85e655b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# And removes blanks entries, if any\n",
    "applicants = applicants[applicants.projID.apply(lambda x: len(x) > 0)]\n",
    "employees = employees[employees.projID.apply(lambda x: len(x) > 0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "9fed46d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74655"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lets check length now...\n",
    "len(applicants)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba015568",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Seems to have been useful. Good."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74fa8a4d",
   "metadata": {},
   "source": [
    "#### Continue with merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "828a702b",
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/l9/zf8fhrds2nd_sfhd808zjdd80000gn/T/ipykernel_47382/1830019523.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Now we can merge\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mmerged_appl\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mapplicants\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mprojects\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mleft_on\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'projID'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mright_on\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'Project Number'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0mmerged_empl\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0memployees\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mprojects\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mleft_on\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'projID'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mright_on\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'Project Number'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/anaconda3/lib/python3.9/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36mmerge\u001b[0;34m(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[1;32m    104\u001b[0m     \u001b[0mvalidate\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mstr\u001b[0m \u001b[0;34m|\u001b[0m \u001b[0;32mNone\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    105\u001b[0m ) -> DataFrame:\n\u001b[0;32m--> 106\u001b[0;31m     op = _MergeOperation(\n\u001b[0m\u001b[1;32m    107\u001b[0m         \u001b[0mleft\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    108\u001b[0m         \u001b[0mright\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/anaconda3/lib/python3.9/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[1;32m    701\u001b[0m         \u001b[0;31m# validate the merge keys dtypes. We may need to coerce\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    702\u001b[0m         \u001b[0;31m# to avoid incompatible dtypes\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 703\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_maybe_coerce_merge_keys\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    704\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    705\u001b[0m         \u001b[0;31m# If argument passed to validate,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/anaconda3/lib/python3.9/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36m_maybe_coerce_merge_keys\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1254\u001b[0m                     \u001b[0minferred_right\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mstring_types\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0minferred_left\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mstring_types\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1255\u001b[0m                 ):\n\u001b[0;32m-> 1256\u001b[0;31m                     \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1257\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1258\u001b[0m             \u001b[0;31m# datetimelikes must match exactly\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat"
     ]
    }
   ],
   "source": [
    "# Now we can merge\n",
    "merged_appl = pd.merge(applicants, projects, left_on='projID', right_on='Project Number')\n",
    "merged_empl = pd.merge(employees, projects, left_on='projID', right_on='Project Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7f4ff9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Yikes. Seems projID is stored as a string. Lets convert to int and rerun the merge..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "ad4f566d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert projID to integer\n",
    "applicants.projID = applicants.projID.astype(int)\n",
    "employees.projID = employees.projID.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "cdf57847",
   "metadata": {},
   "outputs": [],
   "source": [
    "# re-run merge\n",
    "merged_appl = pd.merge(applicants, projects, left_on='projID', right_on='Project Number')\n",
    "merged_empl = pd.merge(employees, projects, left_on='projID', right_on='Project Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "fb1642e6",
   "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>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>Person ID SNSF</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>Projects as Applicant</th>\n",
       "      <th>Projects as Partner</th>\n",
       "      <th>...</th>\n",
       "      <th>Institution Country</th>\n",
       "      <th>University</th>\n",
       "      <th>Discipline Number</th>\n",
       "      <th>Discipline Name</th>\n",
       "      <th>Discipline Name Hierarchy</th>\n",
       "      <th>All disciplines</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Approved Amount</th>\n",
       "      <th>Keywords</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>132628</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67368</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unassignable - NA</td>\n",
       "      <td>10206</td>\n",
       "      <td>Social geography and ecology</td>\n",
       "      <td>Humanities and Social Sciences;Ethnology, soci...</td>\n",
       "      <td>10206/10302</td>\n",
       "      <td>01.12.2002</td>\n",
       "      <td>31.12.2002</td>\n",
       "      <td>20120.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A. Jafari</td>\n",
       "      <td>Golnaz</td>\n",
       "      <td>female</td>\n",
       "      <td>Universität Luzern</td>\n",
       "      <td>Luzern</td>\n",
       "      <td>747886</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191432</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Norway</td>\n",
       "      <td>Institution abroad - IACH</td>\n",
       "      <td>10205</td>\n",
       "      <td>Legal sciences</td>\n",
       "      <td>Humanities and Social Sciences;Economics, law</td>\n",
       "      <td>10205</td>\n",
       "      <td>01.02.2020</td>\n",
       "      <td>31.07.2021</td>\n",
       "      <td>data not included in P3</td>\n",
       "      <td>Information, Communications Technology; Distri...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Aapro</td>\n",
       "      <td>Matti S.</td>\n",
       "      <td>male</td>\n",
       "      <td>Clinique de Genolier F.M.H. Oncologie-Hématolo...</td>\n",
       "      <td>Genolier</td>\n",
       "      <td>3268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8532;9513</td>\n",
       "      <td>8155</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>Other Hospitals - ASPIT</td>\n",
       "      <td>30703</td>\n",
       "      <td>Clinical Cancer Research</td>\n",
       "      <td>Biology and Medicine;Clinical Medicine</td>\n",
       "      <td>30703</td>\n",
       "      <td>01.01.1986</td>\n",
       "      <td>31.12.1988</td>\n",
       "      <td>211427.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aapro</td>\n",
       "      <td>Matti S.</td>\n",
       "      <td>male</td>\n",
       "      <td>Clinique de Genolier F.M.H. Oncologie-Hématolo...</td>\n",
       "      <td>Genolier</td>\n",
       "      <td>3268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8532;9513</td>\n",
       "      <td>8155</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>Other Hospitals - ASPIT</td>\n",
       "      <td>30703</td>\n",
       "      <td>Clinical Cancer Research</td>\n",
       "      <td>Biology and Medicine;Clinical Medicine</td>\n",
       "      <td>30703</td>\n",
       "      <td>01.10.1988</td>\n",
       "      <td>30.09.1990</td>\n",
       "      <td>174021.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aas</td>\n",
       "      <td>Gregor</td>\n",
       "      <td>male</td>\n",
       "      <td>Lehrstuhl für Pflanzenphysiologie Universität ...</td>\n",
       "      <td>Bayreuth</td>\n",
       "      <td>36412</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unassignable - NA</td>\n",
       "      <td>30205</td>\n",
       "      <td>Agricultural and Forestry Sciences</td>\n",
       "      <td>Biology and Medicine;General Biology</td>\n",
       "      <td>30205</td>\n",
       "      <td>11.09.1997</td>\n",
       "      <td>10.10.1997</td>\n",
       "      <td>8865.00</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>74650</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>EPF Lausanne - EPFL</td>\n",
       "      <td>30714</td>\n",
       "      <td>Biomedical Engineering</td>\n",
       "      <td>Biology and Medicine;Clinical Medicine</td>\n",
       "      <td>30714</td>\n",
       "      <td>01.07.1998</td>\n",
       "      <td>30.06.2001</td>\n",
       "      <td>150524.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74651</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Berne - BE</td>\n",
       "      <td>30714</td>\n",
       "      <td>Biomedical Engineering</td>\n",
       "      <td>Biology and Medicine;Clinical Medicine</td>\n",
       "      <td>30714/20502/20506</td>\n",
       "      <td>01.02.2013</td>\n",
       "      <td>30.09.2016</td>\n",
       "      <td>346000.00</td>\n",
       "      <td>osteoporosis; finite element analysis; proxima...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74652</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Berne - BE</td>\n",
       "      <td>30714</td>\n",
       "      <td>Biomedical Engineering</td>\n",
       "      <td>Biology and Medicine;Clinical Medicine</td>\n",
       "      <td>30714/20502/20506</td>\n",
       "      <td>01.06.2013</td>\n",
       "      <td>31.05.2017</td>\n",
       "      <td>262960.00</td>\n",
       "      <td>magnetic resonance imaging; finite element ana...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74653</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Berne - BE</td>\n",
       "      <td>20505</td>\n",
       "      <td>Material Sciences</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20505/20502/30809</td>\n",
       "      <td>01.10.2016</td>\n",
       "      <td>31.03.2021</td>\n",
       "      <td>449517.00</td>\n",
       "      <td>computational modelling; micro-cracks; osteopo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74654</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Berne - BE</td>\n",
       "      <td>50000</td>\n",
       "      <td>Interdisciplinary</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50000/30720/30714</td>\n",
       "      <td>01.08.2019</td>\n",
       "      <td>31.07.2023</td>\n",
       "      <td>1433628.00</td>\n",
       "      <td>fall risk; fracture risk; finite element analy...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74655 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Last Name First Name  Gender  \\\n",
       "0        a Marca     Andrea    male   \n",
       "1      A. Jafari     Golnaz  female   \n",
       "2          Aapro   Matti S.    male   \n",
       "3          Aapro   Matti S.    male   \n",
       "4            Aas     Gregor    male   \n",
       "...          ...        ...     ...   \n",
       "74650     Zysset   Philippe    male   \n",
       "74651     Zysset   Philippe    male   \n",
       "74652     Zysset   Philippe    male   \n",
       "74653     Zysset   Philippe    male   \n",
       "74654     Zysset   Philippe    male   \n",
       "\n",
       "                                          Institute Name Institute Place  \\\n",
       "0                                                    NaN             NaN   \n",
       "1                                     Universität Luzern          Luzern   \n",
       "2      Clinique de Genolier F.M.H. Oncologie-Hématolo...        Genolier   \n",
       "3      Clinique de Genolier F.M.H. Oncologie-Hématolo...        Genolier   \n",
       "4      Lehrstuhl für Pflanzenphysiologie Universität ...        Bayreuth   \n",
       "...                                                  ...             ...   \n",
       "74650  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "74651  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "74652  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "74653  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "74654  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "\n",
       "       Person ID SNSF                OCRID  Projects as responsible Applicant  \\\n",
       "0              132628                  NaN                              67368   \n",
       "1              747886                  NaN                             191432   \n",
       "2                3268                  NaN                          8532;9513   \n",
       "3                3268                  NaN                          8532;9513   \n",
       "4               36412                  NaN                              52037   \n",
       "...               ...                  ...                                ...   \n",
       "74650           40950  0000-0002-4712-7047  52821;143769;147153;165510;183584   \n",
       "74651           40950  0000-0002-4712-7047  52821;143769;147153;165510;183584   \n",
       "74652           40950  0000-0002-4712-7047  52821;143769;147153;165510;183584   \n",
       "74653           40950  0000-0002-4712-7047  52821;143769;147153;165510;183584   \n",
       "74654           40950  0000-0002-4712-7047  52821;143769;147153;165510;183584   \n",
       "\n",
       "         Projects as Applicant Projects as Partner  ... Institution Country  \\\n",
       "0                          NaN                 NaN  ...                 NaN   \n",
       "1                          NaN                 NaN  ...              Norway   \n",
       "2                         8155                 NaN  ...         Switzerland   \n",
       "3                         8155                 NaN  ...         Switzerland   \n",
       "4                          NaN                 NaN  ...                 NaN   \n",
       "...                        ...                 ...  ...                 ...   \n",
       "74650  33565;61944;64562;67942                 NaN  ...         Switzerland   \n",
       "74651  33565;61944;64562;67942                 NaN  ...         Switzerland   \n",
       "74652  33565;61944;64562;67942                 NaN  ...         Switzerland   \n",
       "74653  33565;61944;64562;67942                 NaN  ...         Switzerland   \n",
       "74654  33565;61944;64562;67942                 NaN  ...         Switzerland   \n",
       "\n",
       "                      University Discipline Number  \\\n",
       "0              Unassignable - NA             10206   \n",
       "1      Institution abroad - IACH             10205   \n",
       "2        Other Hospitals - ASPIT             30703   \n",
       "3        Other Hospitals - ASPIT             30703   \n",
       "4              Unassignable - NA             30205   \n",
       "...                          ...               ...   \n",
       "74650        EPF Lausanne - EPFL             30714   \n",
       "74651   University of Berne - BE             30714   \n",
       "74652   University of Berne - BE             30714   \n",
       "74653   University of Berne - BE             20505   \n",
       "74654   University of Berne - BE             50000   \n",
       "\n",
       "                          Discipline Name  \\\n",
       "0            Social geography and ecology   \n",
       "1                          Legal sciences   \n",
       "2                Clinical Cancer Research   \n",
       "3                Clinical Cancer Research   \n",
       "4      Agricultural and Forestry Sciences   \n",
       "...                                   ...   \n",
       "74650              Biomedical Engineering   \n",
       "74651              Biomedical Engineering   \n",
       "74652              Biomedical Engineering   \n",
       "74653                   Material Sciences   \n",
       "74654                   Interdisciplinary   \n",
       "\n",
       "                               Discipline Name Hierarchy    All disciplines  \\\n",
       "0      Humanities and Social Sciences;Ethnology, soci...        10206/10302   \n",
       "1          Humanities and Social Sciences;Economics, law              10205   \n",
       "2                 Biology and Medicine;Clinical Medicine              30703   \n",
       "3                 Biology and Medicine;Clinical Medicine              30703   \n",
       "4                   Biology and Medicine;General Biology              30205   \n",
       "...                                                  ...                ...   \n",
       "74650             Biology and Medicine;Clinical Medicine              30714   \n",
       "74651             Biology and Medicine;Clinical Medicine  30714/20502/20506   \n",
       "74652             Biology and Medicine;Clinical Medicine  30714/20502/20506   \n",
       "74653  Mathematics, Natural- and Engineering Sciences...  20505/20502/30809   \n",
       "74654                                                NaN  50000/30720/30714   \n",
       "\n",
       "       Start Date    End Date          Approved Amount  \\\n",
       "0      01.12.2002  31.12.2002                 20120.00   \n",
       "1      01.02.2020  31.07.2021  data not included in P3   \n",
       "2      01.01.1986  31.12.1988                211427.00   \n",
       "3      01.10.1988  30.09.1990                174021.00   \n",
       "4      11.09.1997  10.10.1997                  8865.00   \n",
       "...           ...         ...                      ...   \n",
       "74650  01.07.1998  30.06.2001                150524.00   \n",
       "74651  01.02.2013  30.09.2016                346000.00   \n",
       "74652  01.06.2013  31.05.2017                262960.00   \n",
       "74653  01.10.2016  31.03.2021                449517.00   \n",
       "74654  01.08.2019  31.07.2023               1433628.00   \n",
       "\n",
       "                                                Keywords  \n",
       "0                                                    NaN  \n",
       "1      Information, Communications Technology; Distri...  \n",
       "2                                                    NaN  \n",
       "3                                                    NaN  \n",
       "4                                                    NaN  \n",
       "...                                                  ...  \n",
       "74650                                                NaN  \n",
       "74651  osteoporosis; finite element analysis; proxima...  \n",
       "74652  magnetic resonance imaging; finite element ana...  \n",
       "74653  computational modelling; micro-cracks; osteopo...  \n",
       "74654  fall risk; fracture risk; finite element analy...  \n",
       "\n",
       "[74655 rows x 32 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# and lets check the results\n",
    "merged_appl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "2df6c388",
   "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>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>Person ID SNSF</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>Projects as Applicant</th>\n",
       "      <th>Projects as Partner</th>\n",
       "      <th>...</th>\n",
       "      <th>Institution Country</th>\n",
       "      <th>University</th>\n",
       "      <th>Discipline Number</th>\n",
       "      <th>Discipline Name</th>\n",
       "      <th>Discipline Name Hierarchy</th>\n",
       "      <th>All disciplines</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Approved Amount</th>\n",
       "      <th>Keywords</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Davide</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53856</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Lausanne - LA</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Benslama</td>\n",
       "      <td>Kamal</td>\n",
       "      <td>male</td>\n",
       "      <td>Department of Physics University of Regina</td>\n",
       "      <td>Regina</td>\n",
       "      <td>56493</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Lausanne - LA</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Frei</td>\n",
       "      <td>Raymond</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13911</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Lausanne - LA</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Galoumian</td>\n",
       "      <td>Pavlik</td>\n",
       "      <td>male</td>\n",
       "      <td>CERN Division PPE</td>\n",
       "      <td>Genève 23</td>\n",
       "      <td>25506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Lausanne - LA</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hertig</td>\n",
       "      <td>Jean-Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14162</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Lausanne - LA</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>127126</th>\n",
       "      <td>Zwicky</td>\n",
       "      <td>Heinrich</td>\n",
       "      <td>male</td>\n",
       "      <td>Departement Soziale Arbeit ZHAW Zürcher Hochsc...</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>13256</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2063;28262;30423;120223</td>\n",
       "      <td>64746</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>University of Zurich - ZH</td>\n",
       "      <td>10201</td>\n",
       "      <td>Sociology</td>\n",
       "      <td>Humanities and Social Sciences;Sociology, soci...</td>\n",
       "      <td>10201</td>\n",
       "      <td>01.04.1990</td>\n",
       "      <td>28.02.1991</td>\n",
       "      <td>40000.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127127</th>\n",
       "      <td>Zwicky</td>\n",
       "      <td>Heinrich</td>\n",
       "      <td>male</td>\n",
       "      <td>Departement Soziale Arbeit ZHAW Zürcher Hochsc...</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>13256</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2063;28262;30423;120223</td>\n",
       "      <td>64746</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>University of Zurich - ZH</td>\n",
       "      <td>10201</td>\n",
       "      <td>Sociology</td>\n",
       "      <td>Humanities and Social Sciences;Sociology, soci...</td>\n",
       "      <td>10201</td>\n",
       "      <td>01.04.1991</td>\n",
       "      <td>30.06.1993</td>\n",
       "      <td>202631.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127128</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Sylvia</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100263</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>University of Basel - BS</td>\n",
       "      <td>10501</td>\n",
       "      <td>German and English languages and literature</td>\n",
       "      <td>Humanities and Social Sciences;Linguistics and...</td>\n",
       "      <td>10501</td>\n",
       "      <td>01.11.1998</td>\n",
       "      <td>31.12.2001</td>\n",
       "      <td>117014.00</td>\n",
       "      <td>SHAKESPEARE; AUSGABE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127129</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>EPF Lausanne - EPFL</td>\n",
       "      <td>20505</td>\n",
       "      <td>Material Sciences</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20505</td>\n",
       "      <td>01.11.1992</td>\n",
       "      <td>31.10.1993</td>\n",
       "      <td>40051.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127130</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Simonetta</td>\n",
       "      <td>female</td>\n",
       "      <td>Institut für Computersysteme ETH Zürich</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>554773</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>ETH Zurich - ETHZ</td>\n",
       "      <td>20506</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20506</td>\n",
       "      <td>01.10.2008</td>\n",
       "      <td>30.09.2011</td>\n",
       "      <td>107750.00</td>\n",
       "      <td>enterprise computing; distributed systems; dat...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>127131 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Last Name     First Name  Gender  \\\n",
       "0         a Marca         Davide    male   \n",
       "1        Benslama          Kamal    male   \n",
       "2            Frei        Raymond    male   \n",
       "3       Galoumian         Pavlik    male   \n",
       "4          Hertig  Jean-Philippe    male   \n",
       "...           ...            ...     ...   \n",
       "127126     Zwicky       Heinrich    male   \n",
       "127127     Zwicky       Heinrich    male   \n",
       "127128     Zysset         Sylvia  female   \n",
       "127129     Zysset       Philippe    male   \n",
       "127130     Zysset      Simonetta  female   \n",
       "\n",
       "                                           Institute Name Institute Place  \\\n",
       "0                                                     NaN             NaN   \n",
       "1              Department of Physics University of Regina          Regina   \n",
       "2                                                     NaN             NaN   \n",
       "3                                       CERN Division PPE       Genève 23   \n",
       "4                                                     NaN             NaN   \n",
       "...                                                   ...             ...   \n",
       "127126  Departement Soziale Arbeit ZHAW Zürcher Hochsc...          Zürich   \n",
       "127127  Departement Soziale Arbeit ZHAW Zürcher Hochsc...          Zürich   \n",
       "127128                                                NaN             NaN   \n",
       "127129  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "127130            Institut für Computersysteme ETH Zürich          Zürich   \n",
       "\n",
       "        Person ID SNSF                OCRID  \\\n",
       "0                53856                  NaN   \n",
       "1                56493                  NaN   \n",
       "2                13911                  NaN   \n",
       "3                25506                  NaN   \n",
       "4                14162                  NaN   \n",
       "...                ...                  ...   \n",
       "127126           13256                  NaN   \n",
       "127127           13256                  NaN   \n",
       "127128          100263                  NaN   \n",
       "127129           40950  0000-0002-4712-7047   \n",
       "127130          554773                  NaN   \n",
       "\n",
       "        Projects as responsible Applicant    Projects as Applicant  \\\n",
       "0                                     NaN                      NaN   \n",
       "1                                     NaN                      NaN   \n",
       "2                                     NaN                      NaN   \n",
       "3                                     NaN                      NaN   \n",
       "4                                     NaN                      NaN   \n",
       "...                                   ...                      ...   \n",
       "127126            2063;28262;30423;120223                    64746   \n",
       "127127            2063;28262;30423;120223                    64746   \n",
       "127128                                NaN                      NaN   \n",
       "127129  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "127130                                NaN                      NaN   \n",
       "\n",
       "       Projects as Partner  ... Institution Country  \\\n",
       "0                      NaN  ...         Switzerland   \n",
       "1                      NaN  ...         Switzerland   \n",
       "2                      NaN  ...         Switzerland   \n",
       "3                      NaN  ...         Switzerland   \n",
       "4                      NaN  ...         Switzerland   \n",
       "...                    ...  ...                 ...   \n",
       "127126                 NaN  ...                 NaN   \n",
       "127127                 NaN  ...                 NaN   \n",
       "127128                 NaN  ...         Switzerland   \n",
       "127129                 NaN  ...         Switzerland   \n",
       "127130                 NaN  ...         Switzerland   \n",
       "\n",
       "                         University Discipline Number  \\\n",
       "0       University of Lausanne - LA             20403   \n",
       "1       University of Lausanne - LA             20403   \n",
       "2       University of Lausanne - LA             20403   \n",
       "3       University of Lausanne - LA             20403   \n",
       "4       University of Lausanne - LA             20403   \n",
       "...                             ...               ...   \n",
       "127126    University of Zurich - ZH             10201   \n",
       "127127    University of Zurich - ZH             10201   \n",
       "127128     University of Basel - BS             10501   \n",
       "127129          EPF Lausanne - EPFL             20505   \n",
       "127130            ETH Zurich - ETHZ             20506   \n",
       "\n",
       "                                    Discipline Name  \\\n",
       "0                                  Particle Physics   \n",
       "1                                  Particle Physics   \n",
       "2                                  Particle Physics   \n",
       "3                                  Particle Physics   \n",
       "4                                  Particle Physics   \n",
       "...                                             ...   \n",
       "127126                                    Sociology   \n",
       "127127                                    Sociology   \n",
       "127128  German and English languages and literature   \n",
       "127129                            Material Sciences   \n",
       "127130                       Information Technology   \n",
       "\n",
       "                                Discipline Name Hierarchy All disciplines  \\\n",
       "0       Mathematics, Natural- and Engineering Sciences...           20403   \n",
       "1       Mathematics, Natural- and Engineering Sciences...           20403   \n",
       "2       Mathematics, Natural- and Engineering Sciences...           20403   \n",
       "3       Mathematics, Natural- and Engineering Sciences...           20403   \n",
       "4       Mathematics, Natural- and Engineering Sciences...           20403   \n",
       "...                                                   ...             ...   \n",
       "127126  Humanities and Social Sciences;Sociology, soci...           10201   \n",
       "127127  Humanities and Social Sciences;Sociology, soci...           10201   \n",
       "127128  Humanities and Social Sciences;Linguistics and...           10501   \n",
       "127129  Mathematics, Natural- and Engineering Sciences...           20505   \n",
       "127130  Mathematics, Natural- and Engineering Sciences...           20506   \n",
       "\n",
       "        Start Date    End Date Approved Amount  \\\n",
       "0       01.04.1993  31.03.1994       553356.00   \n",
       "1       01.04.1993  31.03.1994       553356.00   \n",
       "2       01.04.1993  31.03.1994       553356.00   \n",
       "3       01.04.1993  31.03.1994       553356.00   \n",
       "4       01.04.1993  31.03.1994       553356.00   \n",
       "...            ...         ...             ...   \n",
       "127126  01.04.1990  28.02.1991        40000.00   \n",
       "127127  01.04.1991  30.06.1993       202631.00   \n",
       "127128  01.11.1998  31.12.2001       117014.00   \n",
       "127129  01.11.1992  31.10.1993        40051.00   \n",
       "127130  01.10.2008  30.09.2011       107750.00   \n",
       "\n",
       "                                                 Keywords  \n",
       "0                                                     NaN  \n",
       "1                                                     NaN  \n",
       "2                                                     NaN  \n",
       "3                                                     NaN  \n",
       "4                                                     NaN  \n",
       "...                                                   ...  \n",
       "127126                                                NaN  \n",
       "127127                                                NaN  \n",
       "127128                               SHAKESPEARE; AUSGABE  \n",
       "127129                                                NaN  \n",
       "127130  enterprise computing; distributed systems; dat...  \n",
       "\n",
       "[127131 rows x 32 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# and employees...\n",
    "merged_empl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1bea67a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ID column not named, but otherwise looks good!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1a063d8",
   "metadata": {},
   "source": [
    "#### On to our analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd240ffd",
   "metadata": {},
   "source": [
    "#### Objective 1: how much money awarded on average per year, based on gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06fc0c14",
   "metadata": {},
   "outputs": [],
   "source": [
    "# And we need the year..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "499be71e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, we have to convert the year column to time-date format and save as date\n",
    "merged_empl['date'] = pd.to_datetime(merged_empl['Start Date'])\n",
    "merged_appl['date'] = pd.to_datetime(merged_appl['Start Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "919d725b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# And next, we add a column for just the year\n",
    "merged_empl['year'] = merged_empl.date.apply(lambda x: x.year)\n",
    "merged_appl['year'] = merged_appl.date.apply(lambda x: x.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "0ee27598",
   "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>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>Person ID SNSF</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>Projects as Applicant</th>\n",
       "      <th>Projects as Partner</th>\n",
       "      <th>...</th>\n",
       "      <th>Discipline Number</th>\n",
       "      <th>Discipline Name</th>\n",
       "      <th>Discipline Name Hierarchy</th>\n",
       "      <th>All disciplines</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Approved Amount</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a Marca</td>\n",
       "      <td>Davide</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53856</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-04</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Benslama</td>\n",
       "      <td>Kamal</td>\n",
       "      <td>male</td>\n",
       "      <td>Department of Physics University of Regina</td>\n",
       "      <td>Regina</td>\n",
       "      <td>56493</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-04</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Frei</td>\n",
       "      <td>Raymond</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13911</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-04</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Galoumian</td>\n",
       "      <td>Pavlik</td>\n",
       "      <td>male</td>\n",
       "      <td>CERN Division PPE</td>\n",
       "      <td>Genève 23</td>\n",
       "      <td>25506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-04</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hertig</td>\n",
       "      <td>Jean-Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14162</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20403</td>\n",
       "      <td>Particle Physics</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20403</td>\n",
       "      <td>01.04.1993</td>\n",
       "      <td>31.03.1994</td>\n",
       "      <td>553356.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-04</td>\n",
       "      <td>1993</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>127126</th>\n",
       "      <td>Zwicky</td>\n",
       "      <td>Heinrich</td>\n",
       "      <td>male</td>\n",
       "      <td>Departement Soziale Arbeit ZHAW Zürcher Hochsc...</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>13256</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2063;28262;30423;120223</td>\n",
       "      <td>64746</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>10201</td>\n",
       "      <td>Sociology</td>\n",
       "      <td>Humanities and Social Sciences;Sociology, soci...</td>\n",
       "      <td>10201</td>\n",
       "      <td>01.04.1990</td>\n",
       "      <td>28.02.1991</td>\n",
       "      <td>40000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1990-01-04</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127127</th>\n",
       "      <td>Zwicky</td>\n",
       "      <td>Heinrich</td>\n",
       "      <td>male</td>\n",
       "      <td>Departement Soziale Arbeit ZHAW Zürcher Hochsc...</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>13256</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2063;28262;30423;120223</td>\n",
       "      <td>64746</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>10201</td>\n",
       "      <td>Sociology</td>\n",
       "      <td>Humanities and Social Sciences;Sociology, soci...</td>\n",
       "      <td>10201</td>\n",
       "      <td>01.04.1991</td>\n",
       "      <td>30.06.1993</td>\n",
       "      <td>202631.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1991-01-04</td>\n",
       "      <td>1991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127128</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Sylvia</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100263</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>10501</td>\n",
       "      <td>German and English languages and literature</td>\n",
       "      <td>Humanities and Social Sciences;Linguistics and...</td>\n",
       "      <td>10501</td>\n",
       "      <td>01.11.1998</td>\n",
       "      <td>31.12.2001</td>\n",
       "      <td>117014.00</td>\n",
       "      <td>SHAKESPEARE; AUSGABE</td>\n",
       "      <td>1998-01-11</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127129</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für chirurgische Technologien und Bio...</td>\n",
       "      <td>Bern</td>\n",
       "      <td>40950</td>\n",
       "      <td>0000-0002-4712-7047</td>\n",
       "      <td>52821;143769;147153;165510;183584</td>\n",
       "      <td>33565;61944;64562;67942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20505</td>\n",
       "      <td>Material Sciences</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20505</td>\n",
       "      <td>01.11.1992</td>\n",
       "      <td>31.10.1993</td>\n",
       "      <td>40051.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1992-01-11</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127130</th>\n",
       "      <td>Zysset</td>\n",
       "      <td>Simonetta</td>\n",
       "      <td>female</td>\n",
       "      <td>Institut für Computersysteme ETH Zürich</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>554773</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20506</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20506</td>\n",
       "      <td>01.10.2008</td>\n",
       "      <td>30.09.2011</td>\n",
       "      <td>107750.00</td>\n",
       "      <td>enterprise computing; distributed systems; dat...</td>\n",
       "      <td>2008-01-10</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>127131 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Last Name     First Name  Gender  \\\n",
       "0         a Marca         Davide    male   \n",
       "1        Benslama          Kamal    male   \n",
       "2            Frei        Raymond    male   \n",
       "3       Galoumian         Pavlik    male   \n",
       "4          Hertig  Jean-Philippe    male   \n",
       "...           ...            ...     ...   \n",
       "127126     Zwicky       Heinrich    male   \n",
       "127127     Zwicky       Heinrich    male   \n",
       "127128     Zysset         Sylvia  female   \n",
       "127129     Zysset       Philippe    male   \n",
       "127130     Zysset      Simonetta  female   \n",
       "\n",
       "                                           Institute Name Institute Place  \\\n",
       "0                                                     NaN             NaN   \n",
       "1              Department of Physics University of Regina          Regina   \n",
       "2                                                     NaN             NaN   \n",
       "3                                       CERN Division PPE       Genève 23   \n",
       "4                                                     NaN             NaN   \n",
       "...                                                   ...             ...   \n",
       "127126  Departement Soziale Arbeit ZHAW Zürcher Hochsc...          Zürich   \n",
       "127127  Departement Soziale Arbeit ZHAW Zürcher Hochsc...          Zürich   \n",
       "127128                                                NaN             NaN   \n",
       "127129  Institut für chirurgische Technologien und Bio...            Bern   \n",
       "127130            Institut für Computersysteme ETH Zürich          Zürich   \n",
       "\n",
       "        Person ID SNSF                OCRID  \\\n",
       "0                53856                  NaN   \n",
       "1                56493                  NaN   \n",
       "2                13911                  NaN   \n",
       "3                25506                  NaN   \n",
       "4                14162                  NaN   \n",
       "...                ...                  ...   \n",
       "127126           13256                  NaN   \n",
       "127127           13256                  NaN   \n",
       "127128          100263                  NaN   \n",
       "127129           40950  0000-0002-4712-7047   \n",
       "127130          554773                  NaN   \n",
       "\n",
       "        Projects as responsible Applicant    Projects as Applicant  \\\n",
       "0                                     NaN                      NaN   \n",
       "1                                     NaN                      NaN   \n",
       "2                                     NaN                      NaN   \n",
       "3                                     NaN                      NaN   \n",
       "4                                     NaN                      NaN   \n",
       "...                                   ...                      ...   \n",
       "127126            2063;28262;30423;120223                    64746   \n",
       "127127            2063;28262;30423;120223                    64746   \n",
       "127128                                NaN                      NaN   \n",
       "127129  52821;143769;147153;165510;183584  33565;61944;64562;67942   \n",
       "127130                                NaN                      NaN   \n",
       "\n",
       "       Projects as Partner  ... Discipline Number  \\\n",
       "0                      NaN  ...             20403   \n",
       "1                      NaN  ...             20403   \n",
       "2                      NaN  ...             20403   \n",
       "3                      NaN  ...             20403   \n",
       "4                      NaN  ...             20403   \n",
       "...                    ...  ...               ...   \n",
       "127126                 NaN  ...             10201   \n",
       "127127                 NaN  ...             10201   \n",
       "127128                 NaN  ...             10501   \n",
       "127129                 NaN  ...             20505   \n",
       "127130                 NaN  ...             20506   \n",
       "\n",
       "                                    Discipline Name  \\\n",
       "0                                  Particle Physics   \n",
       "1                                  Particle Physics   \n",
       "2                                  Particle Physics   \n",
       "3                                  Particle Physics   \n",
       "4                                  Particle Physics   \n",
       "...                                             ...   \n",
       "127126                                    Sociology   \n",
       "127127                                    Sociology   \n",
       "127128  German and English languages and literature   \n",
       "127129                            Material Sciences   \n",
       "127130                       Information Technology   \n",
       "\n",
       "                                Discipline Name Hierarchy  All disciplines  \\\n",
       "0       Mathematics, Natural- and Engineering Sciences...            20403   \n",
       "1       Mathematics, Natural- and Engineering Sciences...            20403   \n",
       "2       Mathematics, Natural- and Engineering Sciences...            20403   \n",
       "3       Mathematics, Natural- and Engineering Sciences...            20403   \n",
       "4       Mathematics, Natural- and Engineering Sciences...            20403   \n",
       "...                                                   ...              ...   \n",
       "127126  Humanities and Social Sciences;Sociology, soci...            10201   \n",
       "127127  Humanities and Social Sciences;Sociology, soci...            10201   \n",
       "127128  Humanities and Social Sciences;Linguistics and...            10501   \n",
       "127129  Mathematics, Natural- and Engineering Sciences...            20505   \n",
       "127130  Mathematics, Natural- and Engineering Sciences...            20506   \n",
       "\n",
       "        Start Date    End Date Approved Amount  \\\n",
       "0       01.04.1993  31.03.1994       553356.00   \n",
       "1       01.04.1993  31.03.1994       553356.00   \n",
       "2       01.04.1993  31.03.1994       553356.00   \n",
       "3       01.04.1993  31.03.1994       553356.00   \n",
       "4       01.04.1993  31.03.1994       553356.00   \n",
       "...            ...         ...             ...   \n",
       "127126  01.04.1990  28.02.1991        40000.00   \n",
       "127127  01.04.1991  30.06.1993       202631.00   \n",
       "127128  01.11.1998  31.12.2001       117014.00   \n",
       "127129  01.11.1992  31.10.1993        40051.00   \n",
       "127130  01.10.2008  30.09.2011       107750.00   \n",
       "\n",
       "                                                 Keywords       date  year  \n",
       "0                                                     NaN 1993-01-04  1993  \n",
       "1                                                     NaN 1993-01-04  1993  \n",
       "2                                                     NaN 1993-01-04  1993  \n",
       "3                                                     NaN 1993-01-04  1993  \n",
       "4                                                     NaN 1993-01-04  1993  \n",
       "...                                                   ...        ...   ...  \n",
       "127126                                                NaN 1990-01-04  1990  \n",
       "127127                                                NaN 1991-01-04  1991  \n",
       "127128                               SHAKESPEARE; AUSGABE 1998-01-11  1998  \n",
       "127129                                                NaN 1992-01-11  1992  \n",
       "127130  enterprise computing; distributed systems; dat... 2008-01-10  2008  \n",
       "\n",
       "[127131 rows x 34 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lets check...\n",
    "merged_empl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0a58e6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# good!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "cbcbc7bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Then, we clean up approved amount\n",
    "merged_appl['Approved Amount'] = pd.to_numeric(merged_appl['Approved Amount'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "e0a796ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Then, we narrow down the funding amounts to \"project funding\"\n",
    "merged_projects = merged_appl[merged_appl['Funding Instrument Hierarchy'] == 'Project funding']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "dd68acc0",
   "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>Gender</th>\n",
       "      <th>year</th>\n",
       "      <th>Approved Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>101433.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>145017.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>177826.157895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>female</td>\n",
       "      <td>1978.0</td>\n",
       "      <td>141489.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>female</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>218496.904762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>male</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>429717.055907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>male</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>507521.397098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>male</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>582461.020513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>male</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>624826.387985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>male</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>617256.523404</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Gender    year  Approved Amount\n",
       "0   female  1975.0    101433.200000\n",
       "1   female  1976.0    145017.750000\n",
       "2   female  1977.0    177826.157895\n",
       "3   female  1978.0    141489.857143\n",
       "4   female  1979.0    218496.904762\n",
       "..     ...     ...              ...\n",
       "87    male  2016.0    429717.055907\n",
       "88    male  2017.0    507521.397098\n",
       "89    male  2018.0    582461.020513\n",
       "90    male  2019.0    624826.387985\n",
       "91    male  2020.0    617256.523404\n",
       "\n",
       "[92 rows x 3 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Finally, we group by gender and year, and extract the mean\n",
    "grouped_gender = merged_projects.groupby(['Gender','year'])['Approved Amount'].mean().reset_index()\n",
    "grouped_gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "492ac93c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='year', ylabel='Approved Amount'>"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZgAAAEGCAYAAABYV4NmAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAABPQ0lEQVR4nO3dd3hUVfrA8e+bDiEJBAKEhBIg9E4IzUKRYkGxoFhWXFHsZXfV1VV/6rrs6q6rrroWFBRsiGBBV5AOIiUkIL1DKhBKSAghPef3x72BCWmTkEnj/TzPPDNz7j13zlx03pwuxhiUUkqpquZW0wVQSilVP2mAUUop5RIaYJRSSrmEBhillFIuoQFGKaWUS3jUdAFqi2bNmpl27drVdDGUUqpOiYmJOW6MCSrpmAYYW7t27YiOjq7pYiilVJ0iInGlHdMmMqWUUi6hAUYppZRLaIBRSinlEtoHU4bc3FwSExPJysqq6aLUKj4+PoSGhuLp6VnTRVFK1WIaYMqQmJiIn58f7dq1Q0Rquji1gjGGEydOkJiYSFhYWE0XRylVi2kTWRmysrJo2rSpBhcHIkLTpk21VqeUKpcGmHJocClO74lSyhkaYJRSqr5LS4INH0HG8Wr9WA0w1SQ5OZnbbruN9u3b079/fwYPHsy33357wdddsWIF11xzTRWUUClVby16Dv73J3i9K3x7PyTFVMvHaoCpBsYYxo8fz2WXXcaBAweIiYlh9uzZJCYmVntZ8vLyqv0zlVI16NRh2Dkfet4M/SbBzh/gwxHw4UjY/BXkZbvsozXAVINly5bh5eXF/ffffzatbdu2PPLII+Tn5/Pkk08yYMAAevXqxQcffABYNZNhw4Zx00030aVLF26//XYKdx9duHAhXbp04ZJLLuGbb745e82MjAzuvvtuBgwYQN++ffn+++8B+OSTT5gwYQLjxo1j9OjR1fjNlVI1LuYTKMiHYU/D1a/BH3fClf+CrDT4dgq83g2W/x1csLuxDlOuBtu3b6dfv34lHps+fToBAQFs2LCB7Oxshg4dejYIbNq0ie3bt9OqVSuGDh3Kr7/+SkREBPfeey/Lli2jY8eO3HLLLWevNXXqVEaMGMGMGTNITU0lMjKSK664AoC1a9eyZcsWAgMDXf+FlVK1Q14OxHwM4aOgaQcrzccfBk6BAffAwRUQ9SEc2wUuGLyjAaYGPPTQQ6xevRovLy/atm3Lli1bmDt3LgBpaWns3bsXLy8vIiMjCQ0NBaBPnz7ExsbSqFEjwsLCCA8PB+COO+5g2rRpACxatIj58+fz2muvAdYw6/j4eABGjRqlwUWpi83O+XA6GSLvK37MzQ06jLAeBfku+XgNMNWge/fuzJs37+z7//73vxw/fpyIiAjatGnD22+/zZgxY4rkWbFiBd7e3mffu7u7n+0/KW2YsDGGefPm0blz5yLp69evx9fXt6q+jlKqroiaBoHtrSBSFjd3l3y8S/tgRKSxiMwVkV0islNEBotIoIgsFpG99nMTh/OfEZF9IrJbRMY4pPcXka32sbfE/oUVEW8R+cpOXy8i7RzyTLI/Y6+ITHLl9yzPiBEjyMrK4r333jubdubMGQDGjBnDe++9R25uLgB79uwhIyOj1Gt16dKFgwcPsn//fgC+/PLLs8fGjBnD22+/fbavZtOmTVX+XZRSdcSh3yBhPQy416qt1ABXf+p/gIXGmC5Ab2An8DSw1BgTDiy13yMi3YCJQHdgLPCuiBSG1feAKUC4/Rhrp08GThpjOgJvAK/a1woEXgAGApHAC46BrLqJCN999x0rV64kLCyMyMhIJk2axKuvvso999xDt27d6NevHz169OC+++4rc6SXj48P06ZN4+qrr+aSSy6hbdu2Z489//zz5Obm0qtXL3r06MHzzz9fHV9PKVUbRX0Ing2hz201VgQxLhg5ACAi/sBmoL1x+BAR2Q0MM8YcFpFgYIUxprOIPANgjPmHfd7PwItALLDcDlKIyK12/vsKzzHGrBURD+AIEIQVqIYZY+6z83xgf865P/fPExERYc7fcGznzp107dq1Cu5G/aP3Rqla7EwK/LsL9L0drnnDpR8lIjHGmIiSjrmyBtMeOAZ8LCKbROQjEfEFWhhjDgPYz83t80OABIf8iXZaiP36/PQieYwxeUAa0LSMaymlVP23cRbkZ1vNYzXIlQHGA+gHvGeM6QtkYDeHlaKknmtTRnpl85z7QJEpIhItItHHjh0ro2hKKVVHFOTDhunQ7lJo0a1Gi+LKAJMIJBpj1tvv52IFnGS7aQz7+ajD+a0d8ocCh+z00BLSi+Sxm8gCgJQyrlWEMWaaMSbCGBMRFBRUya+plFK1yJ6FkBYPkTVbewEXBhhjzBEgQUQKx8yOBHYA84HCUV2TgO/t1/OBifbIsDCszvwouxktXUQG2aPH7jwvT+G1bgKW2f09PwOjRaSJ3bk/2k5TSqn6LWoa+IdA56truiQunwfzCPC5iHgBB4DfYwW1OSIyGYgHJgAYY7aLyBysIJQHPGSMKZz98wDwCdAAWGA/AKYDn4rIPqyay0T7Wiki8jKwwT7vr8aYFFd+UaWUqnHHdsOBFTDieXCv+WmOLi2BMeY3oKTRBSNLOX8qMLWE9GigRwnpWdgBqoRjM4AZFSiuUkrVbRs+Ancva1FLB8YYMnPzaehVvUFHF7usA9566y26du3K7bff7pLrv/jii2eXl1FK1VFZp+C3L6D7DdDoXJ9yelYu986Kps9fF/Paz7s5k1N9K6rXfB1Klevdd99lwYIFhIWF1XRRlFK11f6lkHMa+p+rvRw8nsG9s6KJPZ7B4A5NeWf5PubGJPLMVV24tncrl+9OqzWYWu7+++/nwIEDXHvttUydOrXU5fjHjx/PuHHjCAsL45133uH111+nb9++DBo0iJQUq/vpww8/ZMCAAfTu3Zsbb7zx7HI1jvbv38/YsWPp378/l156Kbt27arW76uUqqS4tdbM/dABAKzac4zr3lnNidPZfDp5IJ9OHsi8BwYT5OfNY7N/Y8L7a9mWlObSImkNxkkv/bCdHYdOVek1u7Xy54Vx3cs85/3332fhwoUsX76c119/vdTl+Ldt28amTZvIysqiY8eOvPrqq2zatIk//OEPzJo1i8cff5wbbriBe++1hi4+99xzTJ8+nUceeaTI502ZMoX333+f8PBw1q9fz4MPPsiyZcuq9HsrpVwgbg2EDsC4eTD9lwP8/aeddGrhx4d3RtA6sCEA/dsG8t1DQ5kbk8A/F+5m3DuruSWiNU+M6UyzRt7lfEDFaYCpQ8pajn/48OH4+fnh5+dHQEAA48aNA6Bnz55s2bIFsILQc889R2pqKqdPny62gvPp06dZs2YNEyacGzeRne263e6UUlUkMxWSt5F76VM8/fUW5m1MZGz3lvz75t74ehf9mXd3E24Z0IYrewbz1pK9fLImlui4kyz+w2VV3mSmAcZJ5dU0qkNZy/E7Lu3v5uZ29r2bm9vZxTPvuusuvvvuO3r37s0nn3zCihUrilynoKCAxo0b89tvv7n0eyilqlhCFGB4eWsA844k8vgV4Tw6Ihw3t9IDhr+PJ89d042JkW1IPpXlkv4Y7YOpQy50Of709HSCg4PJzc3l888/L3bc39+fsLAwvv76a8AKaJs3b77wgiulXCt+DQXiwZwjwfzrpl48fkWnMoOLo47NGzG0YzOXFEsDTB1yocvxv/zyywwcOJBRo0bRpUuXEs/5/PPPmT59Or1796Z79+5nBxIopWqxuLUk+3YhW7y5plermi7NWS5brr+u0eX6K0bvjVK1RG4WvNKaRY3G84/821n+xLBq/fiaWq5fKaWUqyXFQH4OK7LD6dLSr6ZLU4QGGKWUqsvi1gDwU1pbOmuAUUopVWXi15DZpDOpppHWYJRSSlWR/DxIiCLJrw8AXVr612x5zqMBRiml6qrkrZBzms3uXWng6U4be8Z+baEBRiml6qq4tQAsz+xIpxaNnJ77Ul00wNRjK1as4JprrqnpYiilXCV+DaZxG9Yc86l1zWOgAUYppeomYyBuLVmtBpGSkVPrRpCBBphaLzY2li5dunDPPffQo0cPbr/9dpYsWcLQoUMJDw8nKiqKqKgohgwZQt++fRkyZAi7d+8udp2MjIwSl/pXStVRx/fCmeMk+PUGoEtw7QswutilsxY8DUe2Vu01W/aEK18p97R9+/bx9ddfM23aNAYMGMAXX3zB6tWrmT9/Pn//+9+ZNWsWq1atwsPDgyVLlvCXv/yFefPmFbnG1KlTS1zq39fXt2q/k1KqesRb819+oxuQWSubyDTA1AFhYWH07NkTgO7duzNy5EhEhJ49exIbG0taWhqTJk1i7969iAi5ubnFrlHaUv+63ItSdVTcWvANYv2pQIL8jhPo61XTJSpGA4yznKhpuEp5S/E///zzDB8+nG+//ZbY2FiGDRtW7BqlLfWvlKqj4tdAm0HsSk6vdRMsC2kfTD2QlpZGSEgIYG2fXJILXepfKVWLpCVBajz5rQez9+hpDTDKdZ566imeeeYZhg4dSn5+fonnXOhS/0qpWiTemv9yKKAfOXkFtbL/BXS5/rN0uf6K0XujVA368Q+w5Wt+vHotD3+5hR8fuYQeIQE1UhRdrl8ppeqTuLXQOpLdyWdwdxM6Nm9U0yUqkQYYpZSqS86kwLGd0HYwu46kE9bMFx9P95ouVYk0wJRDmxCL03uiVA2KX2c9txnCriOnauUM/kIaYMrg4+PDiRMn9AfVgTGGEydO4OPjU9NFUeriFL8G3L04HdSbhJRMurSovQFG58GUITQ0lMTERI4dO1bTRalVfHx8CA0NreliKHVxilsLIf3ZfdyaUN0luHaOIAMXBxgRiQXSgXwgzxgTISKBwFdAOyAWuNkYc9I+/xlgsn3+o8aYn+30/sAnQAPgJ+AxY4wREW9gFtAfOAHcYoyJtfNMAp6zi/I3Y8zMipbf09OTsLCwCn9vpZRyiZwzcPg3GPIIu4+kA9TaOTBQPU1kw40xfRyGsT0NLDXGhANL7feISDdgItAdGAu8KyKFPVfvAVOAcPsx1k6fDJw0xnQE3gBeta8VCLwADAQigRdEpIlLv6VSSrlaUgwU5EGbwew+copG3h6ENG5Q06UqVU30wVwHFNYmZgLjHdJnG2OyjTEHgX1ApIgEA/7GmLXG6gyZdV6ewmvNBUaKiABjgMXGmBS7drSYc0FJKaXqpgS7gz90ADuPpNfKTcYcuTrAGGCRiMSIyBQ7rYUx5jCA/dzcTg8BEhzyJtppIfbr89OL5DHG5AFpQNMyrlWEiEwRkWgRidZ+FqVUrRe/HoK6YBo0YfeRdDrX0hn8hVzdyT/UGHNIRJoDi0VkVxnnlhSGTRnplc1zLsGYacA0sGbyl1E2pZSqWQUFkBgF3caTfCqbtMxcutbCPWAcubQGY4w5ZD8fBb7F6g9Jtpu9sJ+P2qcnAq0dsocCh+z00BLSi+QREQ8gAEgp41pKKVU3HdsFWWnQZhA7j5wCoHMtHqIMLgwwIuIrIn6Fr4HRwDZgPjDJPm0SULi14nxgooh4i0gYVmd+lN2Mli4ig+z+lTvPy1N4rZuAZXY/zc/AaBFpYnfuj7bTlFKqbirsf2k90GEE2cXbRNYC+NaKCXgAXxhjForIBmCOiEwG4oEJAMaY7SIyB9gB5AEPGWMKlwZ+gHPDlBfYD4DpwKcisg+r5jLRvlaKiLwMbLDP+6sxJsWF31UppVwrfj34BkFge3Yd/o3gAB8CGnrWdKnK5LIAY4w5APQuIf0EMLKUPFOBqSWkRwM9SkjPwg5QJRybAcyoWKmVUqqWSlgHrQeCCLuOpNfqJWIK6VIxSilV26Unw8lYaDOI3PwC9h87Xeubx0ADjFJK1X5n+18GceBYBrn5plbP4C+kAUYppWq7+PXg7g3BvdlVOIKsPgQYEXnVmTSllFIukrAOQvqBhxe7j6Tj4SZ0CKqdm4w5cqYGM6qEtCuruiBKKaVKkHMGDm+2OviBXUfS6RDUCC+P2t8AVeooMhF5AHgQaC8iWxwO+QG/urpgSimlgEMb7QUuBwGw+0g6Ee3qxtq9ZQ1T/gJrvsk/sFc8tqXrnBKllKom8ecmWJ44nU1Saia/C25bs2VyUql1LGNMmjEm1hhzK9bSK7lY63k1EpE21VVApZS6qCWsh2adoGEgG2Ktv+0HtAus4UI5p9yJliLyMPAikAwU2MkG6OW6YimllKKgABKioOs4ANYfTMHH042eIQE1XDDnODOT/3Ggsz0DXymlVHU5vgeyUs/2v0QdTKFv6yZ1ooMfnBtFloC1z4pSSqnq5DDB8lRWLjsPnyIyrG40j4FzNZgDwAoR+R+QXZhojHndZaVSSillTbBs2AyadiBmzzEKDAysZwEm3n542Q+llFLVwWGBy6iDKXi4CX3b1I0hyuBEgDHGvFQdBVFKKeXg9FFIOQD97wKs/pdeoQE08HKv2XJVgDOjyJZT8nbDI1xSIqWUUtbwZIDWg8jMyWdLYip3XxJWs2WqIGeayJ5weO0D3Ii1IZhSSilXiV9nLXDZqg+b4k6Sm2/qVP8LONdEFnNe0q8istJF5VFKKQVWDaZVX/DwZsPBeESgf9t6FmBExPEbuQH9gZYuK5FSSl3scjPh0G8w+EEAomJP0LWlPwENavcWyedzpoksBqsPRrCaxg4Ck11ZKKWUuqgd2gQFudB6EDl5BcTEnWTigLq3QpczTWR1q1dJKaXqOocFLrcdSiMrt6DO9b+Ac01knsADwGV20grgA2NMrgvLpZRSF6+4NdA0HHybEhW9H4CIOrLApSNnlop5D6vf5V370d9OU0opVdWSd8C+JWcXuIw6mEL7IF+C/LxruGAV50wfzABjTG+H98tEZLOrCqSUUhe1Ff8Ar0Yw5BHyCwwbYlO4pldwTZeqUpypweSLSIfCNyLSHsh3XZGUUuoidXgL7JwPgx6AhoHsPpJOelZenVrg0pEzNZgngeUicgBrJFlb4PcuLZVSSl2MVvwDfAJg8EMARB20dkmJDGtak6WqNGdGkS0VkXCgM1aA2WWMyS4nm1JKqYpIioHdP8Hw56BBYwCiYlMIadyAkMYNarZsleTMKDJ3YAzQzj5/pIjocv1KKVWVlv8dGjSBQfcDYIwh6mAKl4YH1XDBKs+ZJrIfgCxgK+e2TFZKKVVV4tdbI8eueBG8/QA4cDyD46dz6mz/CzgXYEKNMb1cXhKllLpYLZ8KvkEQOeVs0oaDKQB1OsA4M4psgYiMruwHiIi7iGwSkR/t94EislhE9trPTRzOfUZE9onIbhEZ45DeX0S22sfeEhGx071F5Cs7fb2ItHPIM8n+jL0iMqmy5VdKKZeKXQ0HV8IlfwAv37PJUQdTaNbIi/bNfMvIXLs5E2DWAd+KSKaInBKRdBE5VYHPeAzY6fD+aWCpMSYcWGq/R0S6AROB7sBY4F27/wesiZ1TgHD7MdZOnwycNMZ0BN4AXrWvFQi8AAwEIoEXHAOZUkrVCsbAsqnQqCVE3F3k0PqDKUSGBWL/PV0nORNg/g0MBhoaY/yNMX7GGH9nLi4iocDVwEcOydcBM+3XM4HxDumzjTHZxpiDwD4gUkSCAX9jzFpjjAFmnZen8FpzsQYgCNaghMXGmBRjzElgMeeCklJK1Q4HlkP8Grj0T+B5bqRY4skzJKVmMqAOLg/jyJkAsxfYZv+4V9SbwFMUHRzQwhhzGMB+bm6nhwAJDucl2mkh9uvz04vkMcbkAWlA0zKuVYSITBGRaBGJPnbsWCW+nlJKVZIx1sgx/1DoX7QVf0Ns3e9/Aec6+Q8DK0RkAXB2/kt5w5RF5BrgqDEmRkSGOfE5JdUDTRnplc1zLsGYacA0gIiIiMoEUKWUqpy9iyFxA1zzJnicW2fs6KksFmw9gp+PB11aOtVYVGs5E2AO2g8v+wEl/FiXYChwrYhchbXVsr+IfAYki0iwMeaw3fx11D4/EWjtkD8UOGSnh5aQ7pgnUUQ8gAAgxU4fdl6eFU6UWSmlXK8gH5a8CI3bkhR2I+s3JrL+QApRsSkcPJ4BwLjerXB3q7v9L+DcTP6XHN+LiA8wzol8zwDP2HmGAU8YY+4QkX8Bk4BX7Ofv7SzzgS9E5HWgFVZnfpQxJt8eWDAIWA/cCbztkGcSsBa4CVhmjDEi8jPwd4eO/dGFZVFKqRq3+Us4up3/8/wTs/71CwD+Ph5EhgVya2RrBoY1pXurul17AedqMIWz+UcDt9rPq4GvK/mZrwBzRGQyEA9MADDGbBeROcAOrJ0zHzLGFC6q+QDwCdAAWGA/AKYDn4rIPqyay0T7Wiki8jKwwT7vr8aYlEqWVymlqk7OGcyyv7HTLZyVnpfwwrgwIsMC6dLSv87XWM4nZfXdi8hlwG1YI8GisJq92htjzlRP8apPRESEiY6OruliKKXqu1X/gmV/Y0L2//H7227jqp51cyn+QiISY4yJKOlYqTUYEUnEqmG8BzxpjEkXkYP1MbgopVS1OH0Ms/o/rPMcxPFG/RnTvWVNl8ilyhqmPA9raO8twDgR8cW5zn2llFIlWfkK5J7h2dM3cd9l7etdk9j5Sg0wxpjHsFZQfh0YDuwBgkTkZhFpVD3FU0qpeuL4Xoj+mCUNr+J0ozCu71dsal69U+ZES2NZZoy5FyvY3IY1iz7W5SVTSqn6ZMmL5Hs04OkTV3HPpWF4e7iXn6eOc2oUGYAxJhdr6f4fRKRu7n6jlFI1IW4N7PqRHwPvJje3KbdGtqnpElULZ5aKKcYYk1nVBVFKqXrJGFj0PHm+LXn68KXcObgdfj6eNV2qalGpAKOUUspJ27+FpGi+bXwXBe4NuGtou5ouUbXRAKOUUq6SkwFLXyK3WVeei+3JLQNa06yRd/n56omy5sH8QBnDko0x17qkREopVdedjIMNH8LGTyErla87vUneITfuvbR9TZesWpXVyf+a/XwD0BL4zH5/KzqKTCmlijIGDqyAqGmwewGIG3S9hvQ+9zD1szOM69WC1oENa7qU1arUAGOMWQkgIi8bYy5zOPSDiKxyecmUUqqu+O0LWP0mHN8NDZvCpX+0dqgMCOWTpXvJyNnD/cM61HQpq50zw5SDRKS9MeYAgIiEAUGuLZZSStURB1fBdw9Ay14w/j3ofgN4+gCQmZPPx2tiGd45qM7v7VIZzgSYP2BtOHbAft8OuM9lJVJKqbrCGFjyEviHwOTFZwNLoS+j4knJyOGBYR1rqIA1y5n9YBaKSDjQxU7aZYzJLiuPUkpdFHYvgKRoGPdWseCSlZvPeyv3M6h9YJ3f+riyyh2mLCINgSeBh40xm4E29nbISil18SrIh2UvQ2AH6HN7scNfRsVzLD2bx0Z2qoHC1Q7OzIP5GMgBBtvvE4G/uaxESilVF2ydC0d3wIhnwb1oY1BWbj7vrdjPwLBABndoWkMFrHnOBJgOxph/ArlwdpmY+r3GtFJKlSUvB5ZPhZY9odv1xQ7PjornaHo2j10RXgOFqz2c6eTPsRe3NAAi0gHQPhil1MVr0yxIjYPb54Jb0b/TC/teIsMCGdz+4q29gHM1mBeBhUBrEfkcWAo85cpCKaVUrZVzBlb+C9oMho5XFDv81YYEkk9l8/gV4Yhc3I09zowiWyQiMcAgrKaxx4wxx11eMqWUqo2iPoDTR2DCJ3BeAMnKzefdFfuIbKe1F3AiwIjIfOBLYL4xJsP1RVJKqVoqM9WasR8+GtoOLnZ4TrRVe3nj5j4Xfe0FnGsi+zdwKbBDRL4WkZtExKe8TEopVe+seRuyUmHE88UOZeXm8+7y/Qxo1+SiHjnmqNwAY4xZaYx5EGgPTANuBo66umBKKXVBju2BE/utEV9V4fRRWGcvBRPcq9jhOdEJHDmVxeNXdNLai82pLZPtUWTjgFuAfsBMVxZKKaUuyOEt8MFlWINfBfxbQeM29qMttOwBXa8t1odSplX/grwsGPFcsUPZeVbtJaJtE4Zo7eUsZ/pgvgIGYo0k+y+wwhhT4OqCKaVUpa39L3j5wth/QFoSpMZbw4rj1sDWr8EUwFWvQeS9zl0vbg1EfWitkNy0+KrIczZYtZfXJvTW2osDZ2owHwO3GWPyXV0YpZS6YKcOwba5MOBe6Hdn8eP5ufDlrfDzs9B2KLToVvb1stLgm/ugSTsY9VKxw0fTs/jv8v30b9uEoR219uLImU7+pcBDIjLXfjwiIp6uLphSSlVK1DSrhjKwlEXf3T1h/Lvg4w/zJkNuZtnX++kpOJUEN3wI3n5FDh04dpob31tDWmYuz17dVWsv53EmwLwH9AfetR/97DSllKpdsk9D9Azocg0EhpV+XqPmMP59ay2xxf9X+nnb5sGW2XDZk9B6QJFDm+JPctP7azmTnc/sKYPo16ZJFX2J+sOZJrIBxpjeDu+XichmVxVIKaUqbfOXVpPW4IfLPzf8Chj0IKx7FzqMhM5jKSgw5OQX4OPpDmmJ8OMfICTCCjAOlu1K5qHPNxHk582suyNp18zXRV+obnOmBpNvrz8GgIi0B8rtjxERHxGJEpHNIrJdRF6y0wNFZLGI7LWfmzjkeUZE9onIbhEZ45DeX0S22sfeErseKiLeIvKVnb5eRNo55Jlkf8ZeEZnk1N1QStVdBflW537oAGgz0Lk8V7wILXrC9w9C+hH+8u1Wer20iAdmbeDEZ3dj8vPghmlFVkv+akM8986KoWPzRsx7YIgGlzI4E2CeAJaLyAoRWQksA/7kRL5sYIRd++kDjBWRQcDTwFJjTDhW/87TACLSDZgIdAfGAu+KiLt9rfeAKUC4/Rhrp08GThpjOgJvAK/a1woEXsAa/RYJvOAYyJRS9dDuBXDyIAx+yPk8Ht5w03TIOUPal/fw1YY4eocG0Dl2Fk2Pref/cn/HU8tP8+u+4+QXGN5aupc/z9vK0I7NmD1lEEF+3q77PvVAmU1k9g98b6wf9c5Ya5E5taOlMcYAp+23nvbDANcBw+z0mcAK4M92+mz72gdFZB8QKSKxgL8xZq1dplnAeGCBnedF+1pzgXfs2s0YYLExJsXOsxgrKH1ZXrmVUnXU2v9CQBvoMq5i+YI6UzDmHwT873Ee9+3AlKvvwufjLzneejRnGt3Kz1uPMCc6ET8fD9Kz8rihXwiv3tgLT3dn/j6/uJUZYIwx+SJyrTHmDWBLRS9uB6gYoCPwX2PMehFpYYw5bF//sIg0t08PAdY5ZE+003Lt1+enF+ZJsK+VJyJpQFPH9BLyOJZvClbNiDZt2lT06ymlaoukGIhfA2P+XmzzL2d8njecoPwBPCKf4/bNCmjYlGYT3+ffvk2ZmpvP0p1HWbDtMN1bBXD/5e11tJiTnPmXWCMi7wBfAWcXuzTGbCwvoz13po+INAa+FZEeZZxe0r+YKSO9snkcyzcNa/kbIiIiih1XStURa/8L3v7Q93cVzpqSkcNri/YwMOQpxmQ8Bidj4Y5vwNea0+Lj6c7VvYK5uldwFRe6/nMmwAyxn//qkGaAEc5+iDEmVURWYDVTJYtIsF17CebcumaJQGuHbKHAITs9tIR0xzyJIuIBBAApdvqw8/KscLa8Sqk6JDUBtn8Hgx6w5rZU0L9+3kVGdh5PXn8pIt/Cib3QcWTVl/Mi5Mxil8NLeJQbXEQkyK65FK5ldgWwC5gPFI7qmgR8b7+eD0y0R4aFYfX7RNnNaekiMsjuX7nzvDyF17oJWGb3/fwMjBaRJnbn/mg7TSlV30R9YD2XNrGyDJsTUpm9IYG7hrQjvIUfNO8CXSvYh6NK5cxaZE2xRmRdglVzWQ381RhzopyswcBMux/GDZhjjPlRRNYCc0RkMhAPTAAwxmwXkTnADiAPeMhheZoHgE+ABlid+wvs9OnAp/aAgBSsUWgYY1JE5GVgg33eXws7/JVS9UjWKYiZCd2usxayrICCAsP/zd9Os0bePHZFuIsKeHFzpolsNrAKuNF+fztWf0zxvUIdGGO2AH1LSD8BlFj/NMZMBaaWkB4NFOu/McZkYQeoEo7NAGaUVUalVN128tcZNMk+5dzEyvPMjUlkc0Iqb9zSGz8fXf3KFZwZZxdojHnZGHPQfvwNaOzicimlVJkyU4+S+8ubRBd05oEVsO7ACawW8vKlncnllYW7GNCuCeP7FBtgqqqIMzWY5SIyEZhjv78J+J/riqSUqnGZJ+HQb5BzGjqNtRaIrE0K8kn9/C4CTTrL27/Cmv0nWLDtCF1a+jFpSDvG9wmhgZd7qdlfX7yb1DM5vHhtpA45diEpL+KLSDrgCxTuAePGueHKxhhT8WEbtVBERISJjo6u6WIoVf2yTsHhzXBo07nHyYPnjjduA5c+AX1uqz2BZsUrsOIfvOHzII//+e9k5RYwf3MSn6yJY+fhUwQ08OSm/qEE+XlzJjuPMzn5ZOTkcyYnj4zsfJbtSuaOQW3563VlzZxQzhCRGGNMRInHnK1S1ncaYFStUZAPx/dA866u/Zy0RFj5T/jtcyjIs9IC2kCrPtCqr/XIy7J2ckyKORdoet8KHl6uLVtZ9i7BfH4T3+RfQsaVb3PnkHOrJhtj2BB7kplrYlm4/Qj5BQYRaOjpTkNvD3y93Gng5UHbwIa8emMvAhrWkoBZh11wgBGRGzg3iuwXY8x3VVrCWkADjKoVcrPg2ymw43u4eZY1OqqqnT4Gq1+HDdOtfVP6T4JOV1qBxbdZ8fONgX1LYMU/aj7QpCbAB5dyqKAJ12S+yMq/XFVqB/2ZnDwEwcfTTZvBXKisAOPMMOV3sZZ6KVzH634RGWWMqcCKckqpcmWehNm3Q9yv0LApLJtq7WviVnpfQsWunwpr34G170JeJvS+DS5/Cpq0LTufCISPgo5XnAs0Pzxq7bty14/FNuEq08k4K0BV5gc/Lxu+noTJz+OuM49wZf/2ZY7+auhV8SVjVNVy5l/gcqCHPYEREZkJbHVpqZS62KQlwWc3Qsp+uGkGiDt8PQm2zoXet1zYtQsKYN1/YdVrkJUK3a+HYX+BoE4Vu45joNn+Dcy7F+ZMgtu+cq5vZvWbsOQFiJgMV/+74kHm579AUgxLe77Gng0t+M+gcgKjqnHOBJjdQBsgzn7fmkosfKmUKsXRnVZwyU6HO+ZB2GVWUGjZ06ot9Lih8p3rmSfhmymwdxF0HAUjn4fg3uXnK4sI9LgRcjJg/iPww+Nw3TtlB4w171jBJbADRE+3amgjnnX+M7d8DRs+wgx+hL9t6ciAdt50Da4X44vqNWcCTFNgp4hE2e8HAOtEZD6AMeZaVxVOqQopKIDkrbBvqfXISoXWkdBmMLQZVOGZ3tUibg18ORE8GsDvf7KCCoCbGwx/Dr68BX77wuonqajk7VaTW1oiXP06RNxduaap0vS706p5rXwFAkJh+DMln7fuPVj0LHQbDzd+BP/7I6z6JzQMtNYPK/d77LCa5NoM4Ze2DxG7fCN/GFXB2peqEc4EmDI2rFaqhmUch/3LrICyfylkHLPSW/S09l3fOtfqKwDwD7UCTZtB1g8iAGL/6NrPHj7QdkjV9XuUJj8PdnwH3z1oBb7ffVM8AHYaY23Xu/Kf0HuitTmWs7bOtWoX3v5W4GodWaXFP2vY0/ZotFcgIMQKOo6iPoSFT1vre934kVUTu+ZNq2a18GnwaQx9bi352gUFVm1nyYvg1QgmfMyn3ybSrJEXY3u0dM33UVWq3ABjjFnp+F5EhgK3aSe/qnHrp8HCP1sjoRoEQocRVv9AhxHg18I6pyDf+ks+fh3Er7U60LfNLfu6vW+D8e9W7V/7OWcgKRri1lr7liRsgNwMCI20+jAaBhbPIwIjnoNPx1vrbQ2cUv7n5OdZTVFr37FqbhNmnrsXriAC496E9ENWU5lfsNVPA9YotZ+egM5Xw40zzjXzubnDjdPh8wnw/UPgEwBdrip63eN7rQAZvxY6jIRxb5KUH8DSnTE8MKwD3h4u/gNAVQlnhyn3AW4DbgYOAvOMMe+4tmjVS4cp1zHb5sHcyRA+Gob9GYL7OFfrMAZS4+FM4Vqtxt4pyFjHdn4Pa962OqEH3HNhZUxNsJq39i2xJi8W5AICLbpbtaQ2g6HzVeDpU3Z5Z46z5sU8+ht4NSz93Izj8PVdEPsLRE6B0VOrbxhxdjp8fCWcOGDVmA5vtpq1wsfALZ+WXPvKToeZ11p/APzuG2h3CeTnwpq3YMWr4NkAxv7DGg4twms/7+bdFftY9dRwQpuUcR9UtarUPBgR6YS1OvGtwAmsBS6fMMbUy6EbGmDqkAMrrU7x0AHwu2/L/oGuqIICq99j/3K463/QZmDF8ufnwp6fYeNM2LvYSguNgLZDraDSeiA0aFyxa8athY/HwqiXYeijJZ8Tu9oa1ZWZYjVBldbs5ErpR+CjKyD7lLU6QMeRcMvnZf/7ZJwgb/pYOJXE+m7PMujIl7gf3WrN/7nyX2drXzl5BQx5ZSl9Wjfho0kl/papGlLZAFMA/AJMNsbss9MOGGPau6ykNUgDTB1xeAt8fBU0bg2/X1DxH2tnZJ6EacMhNxPuW+VcE9PJWNg4CzZ9BqeTraaivndYOyyWN8/EGZ/dCEkb4fEtReedFORbM+1XvgpNwmDCxxc+SuxCHN1lBcNWfWHiF1YtxIExhtgTZ9gQm0J0bArRsSc5czyeud4vESrHOUZjYno8x+XX3l1kLbH5mw/x6JebmHl3JJd3Cqrub6XKUNmJljdi1WCWi8hCrGX7dTqsqjknY+Hzm6w2+9vnuia4ADRoArd8BtNHWXNRJv1Q+jDh9GRrfsa2uSBuVpNd/7usIcGV2Bu+VMOfhQ+HWyOyLn/KSjt1yKq1xK2GXhPh6tcqNunRFZp3gT9sB8+GxfqwDqdlMnHaOuJOnAGgcUNPIto2YcCAoZwMnId77I/8LXkw/4vOImj3ch4d0ZFbBrTBy8ONz9bG0bZpQy7tWMJKA6rWcmaxS19gPFZT2QhgJvCtMWaRy0tXjbQGU8tlHIfpo62+k8mLIKiz6z9z61yYNxkG3g9Xvlr0WEGB1Qy25AWrpjP4YavPJsCFS7/Pvh0OroLHNkPiBvj2fmt2+9X/rpkmsQr6v++38cX6eF64tjuDwgLpENQIN7fif7NuiE3hXwt3ExWbQmiTBkwc0JrXFu3h2au6cu9l9bIBpU6rssUuRSQQa4OvW5zZNrku0QBTi2Wftjq6j+6AO+dXvF/kQiz8izUL/vpp52bUH91pjZhKWAftLoVr3oBm1bAjYvJ2eG+oNUggeZs1Z+amT6BZR9d/9gU6eiqLS/65nOv7hPDqTb3KPd8Yw6q9x/nXz7vYlnQKbw831j0zkia+NbjIpirRBa1F5sjedvgD+6GU6+VmWs1Uh3+zOoyrM7gAjHrJHhH1GAS2hz0L4Nf/WPNLxr93doRTtWjR3ZrVv20eRN4Ho/5atQMcXGjaqgPkFxgeHN7BqfNFhMs7BXFZeDMW7UjGTUSDSx2kq8Gp2ivlIMz5HRzZCuPeKj5XwgXSMnNJz8rF2KOWDQZG/peQr8bgMd3eJbz3bTD6b+Db1OXlKebat2HoY1XWkR91MIWXftjOH0d1YmRX5+fLJJ/KIje/wKnhwidOZ/P5+niu692Ktk19K1Q+EWFMd51UWVdpgFG1056f4Zt7rde3zbFmtbvYodRMhr+2guy8gmLHeslD/MFjLl97X49PznCG7MpkaMdMggMalHAlF/LyrbLgEhN3kt9/HEVmbj5TPo3hlRt6MiGidbn5Vu45xsNfbMTDTVj4+GW08C+7FjV99UGy8vJ5cHjtb8pTVUsDjKpdCvKt3QpX/dPqY7j5UwgMKz9fFZgbk0h2XgEvXdudBl7uuIngZq8g4yZ9SMy6DrcDJ1ix5xjfbEoCoH2QL0M7NGN83xD6t21SLeWsCpsTUrlrRhRBft5Mv2sAL87fzpNzt3D8dA73X96+xP1TjDFMX32Qv/+0k/DmfsSnnOHx2b/x2T0DcS+hsx4g9UwOs9bGcVXPYDo2b+Tqr6VqGQ0wqvY4k2KN2tq/DPrcYQ279ayeGkJBgWFOdAJDOjRl0pB2pZ73u0FtKSgw7DqSzpr9x/l133HmbUzkqw0JrHxqWPXXaCphW1Iav5u+nsa+nnxx7yBaNW7A9EkDeOLrzby6cBfH0rN57uquRUZ4ZeXm8+y325i3MZEre7TktQm9+d+Wwzw1bwvvr9zPQ6XUTj5ZE8vp7Dwe1trLRUkDjKodkjbCnDutSYrj/gP9JlVf5zmw7sAJEk9m8uSY8oc/u7kJ3Vr5062VP/dc2p6ElDMMf20F76/Yz0sXuMf76ew8Zq6JZdWeY/x+aDvG9gi+oOudb+fhU9wxfT1+Pp58cY8VXAC8PNx485Y+BPp6MePXg5zIyOZfN/XGy8ONo6eyuO+zGDbFp/L4FeE8OiIcNzdhQkQoq/Ye4/XFexjcoSn92hStwaVn5TJj9UFGdWuhS+tfpDTAqJqVa+/5/uub1uz3u3+GkH7VXoyvohPw8/GoVIdy68CG3NgvlC83JPDg8I7l9kmUJD0rl1lr4/jwlwOknsklyM+b+z/byPg+rXjx2u40blj2CKqcvALmbz5E6pkc+rVtQo9WAXh5uBU5Z29yOnd8tB4fD3e+uHcgrQOLdtC7uQkvjOtGc39v/rlwNykZOTw4rCN/+Oo30jJzee/2flzZ81zAExGmXt+T3xJSefTLTfz02KX4O+ww+em6OE5l5fHICK29XKw0wNQhWbn5jPz3Sp4Y04nr+4aWn6G2i1trrZh7Yq81MmvM1JJXFS5Dbn4BR9KySErNJOlkZpHnsGa+/PW67uXux552JpcF245wS0RrfDwrt0rvQ8M7MndjIu+t2M+L13Z3Ol96Vi4z18Ty0eqDpJ7JZXjnIB67ohPdW/nz3+X7eGfZPtbsP8ErN/ZkRJfio7wyc/KZvSGeaasOcDgt62y6t4cbvVs3JqJtEyLaNaGprzeTZ0bj5iZ8ce/AUkdziQgPDutIM19vnv5mC7/sPU5I4wbMe2AI3VoVr4UENPDkPxP7cvMHa/nLN1t5+9a+iAhncvL46JeDXN4piF6hjZ2+H6p+0QBTh2xNSiMpNZPlu47V7QCTdQqWvgQbPrL2QLnjG2thxApavCOZh77YSM55o76C/Lzx9/Fg9b7jjOjanOGdm5d5nfmbk8jJK+BmJ0ZQlaZN04bc0DeEL6PieXBYB5qXU4sxxvDBqgO8t2I/aZm5jOzSnEdHhtO7deOz5zx+RSeu6NqCP83ZzN2fRDOhfyjPj+uGv48n6Vm5fLoujum/HORERg6R7QJ59cZedGnpR0zcSaLtx7RVB3h3hTWZuqmvF7OnDKJ9UPmd7TcPaE2QnzcLth3mz2O70LRR6XvR9G/bhD+O6sS/ft7NZeFB3DygNV+sjyclI4dHR2rt5WKmAaYO2RCbAsCWxNSaLUhZsk7BgRXWsvINAq11vRoGWhMTRWD3QmtHw1OHYNCD1hpb3hUfXZSdl89LP2ynbWBD7rk0jJDGDQlp0oDgAB98PN3JyStg1BsreeWnXVwWHlTqKCeAOdGJdA32p0fIhfUTPDyiI99sSuL9lQf4v3Hdyjx3xq+xvLJgF8M7B/HHUZ3pGRpQ4nk9QgKY/8hQ/rNkL++v3M+v+45zZc9gvo5O4FRWHpd1CuLh4R2JDDtX87uyZ/DZpqzMnHw2J6ay/dApRnRpTlgz5+ehDO/SnOFdyg7Ohe6/vAOr9x7nhfnb6R7iz7RVBxjcvin921asRqrqFw0wdUhM7EkAYk+cIe1MLgENK7lPu6ucOgSf3gDHdhY/Ju7WIpWZKdC8G9w8y1rGvpI+XRtH4slMPp0cyaXhxVfX9fJw489ju/Dg5xuZG5PALQNK3i55x6FTbE1K44Vx3cptSitP26a+jO8Twufr47h/WHua+5Vci4mOTeEfP+1kdLcWfPC7/uV+rreHO0+N7cKobi3409ebmb76IKO7teDhER3LbX5q4OXOoPZNGdTetZNC3d2ENyf2Yeybq7j5/bVk5OTz5sQ+Lv1MVftpgKkjCgoM0XEnaR3YgISUTLYmpXFJeC1aWfbYHvjsBshMteauNGpuLXufedIaflz4OjDMWubkAjbCSsvM5Z3l+7g0vFmJwaXQlT1a0q9NY/69aA/jereioVfx/9znRCfg5e7G+D5Vs0jlwyM68u2mRKatPMBz1xSvxRxLz+ahLzYS2qQBr93cu0JBrW+bJix87DJOZeXSrIwmq5rSwt+H1yb0ZvLMaCLaNmGwi4Oaqv3cyj+lckSktYgsF5GdIrJdRB6z0wNFZLGI7LWfmzjkeUZE9onIbhEZ45DeX0S22sfeEvv/ShHxFpGv7PT1ItLOIc8k+zP2isgkV33P6rL/2GnSMnOZNLgdAFuSUmu0PEUkxsCMMZCXBb//H3S71tr3vvOV0Oc2GPIwjHwernkdhjxywbssFvZbPH1llzLPExGevborR9Oz+eiXg8WOZ+fl891vSYzu3qLK1rkKa2bVYj5bH8ex9Owix/LyC3j0y03WiKw7+hcZceUsLw+3WhlcCo3s2oLpkyJ4c2KfC64RqrrPZQEGyAP+ZIzpCgwCHhKRbsDTwFJjTDiw1H6PfWwi0B0YC7wrIoVDet4DpgDh9mOsnT4ZOGmM6Qi8AbxqXysQeAEYCEQCLzgGslov8yRkpRVJ2mA3j43s2oK2TRuyJSGtpJzVb+8SmHkN+PhbQ4xdvNlVUmomM349yPV9QujequR+C0f92wYytntL3l+5n6PpWUWOLd6RTOqZ3Avq3C/JwyM6kpNXwIe/HCiS/vriPaw9cIK/je9Zr+eFjOzaQrc0VoALm8iMMYeBw/brdBHZCYQA1wHD7NNmAiuAP9vps40x2cBBEdkHRIpILOBvjFkLICKzsPanWWDnedG+1lzgHbt2MwZYbK/+jIgsxgpKX7rq+1aZ2F+tBR5zs6wdEQc9AIFhRMel0KyRF+2aNqRXaGM2xlkBB2MgIQqipsHO+ZCfU/J1fZvDZU9A/99X3T7tW+bAdw9A865w+zzndn68QK8v2gPAH0d3cjrPn6/swpKdyby5ZC9/v77n2fSvNiQQ0rgBQ6t4E6v2QY24tncrPl0bx5TL2tOskTeLdyTz7or93BrZhpv61+ERgEpVQLX0wdhNV32B9UALO/hgjDksIoXDVEKAdQ7ZEu20XPv1+emFeRLsa+WJSBrQ1DG9hDyO5ZqCVTOiTZuSO4GrVcxM+N+frC12O0ZA9AzY8CF0HUfG/qH0b2t1CPcKCWDR5ljS136M3+YZcGQLeAdY2/P6ltInEfcrLHjK2hFx5P9B9+srP1O+IB/WvQuLnrP2Q5n4udWB72I7D5/im02JTLm0fYX+Qg5r5ssdg9ry6bo47h7ajo7N/UhKzWT1vuM8MiK8zBFmlfXwiHC+33yID385wG2RbfjjnN/oGRLAC+WMLlOqPnF5gBGRRsA84HFjzKky2mVLOmDKSK9snnMJxkwDpoG14VhpBXO5/DxY9Cysfx86jISbZljbAV/xIkR9QMGGGXyQ/T1HjveBrY9wdfJ6bvL+Ar+fT1sjsq55A3reXPZwX2Ng72JrB8a5v4c1b1v7iYRd6lwZ87LhwErY9QPsXgAZx6DrOLjho2rbk+SVBbvw9/HkwWEVn1vx6Mhw5sUk8sqCXXw0aQBzoxMxBia4qDbRsXkjxvWyajErdx/DTYR3b+9X6YmcStVFLg0wIuKJFVw+N8Z8Yycni0iwXXsJBo7a6YmAY2N4KHDITg8tId0xT6KIeAABQIqdPuy8PCuq6GtVrcyT8PXv4cByGPSQ9aNfuJe7fzBc8SKLm97B2rn/4Zn8ZTBvMsHizsKC/mT0vpubbpzoXE1EBDqNtiY0bp4Ny6dafSfho6HfndYy8J4NwcPHevb0ATdPiF8DO3+0glNOOnj5QfgoK7h0uw7cqucH89d9x1m55xjPXtW1UsOzA329eGB4B/65cDdr9h/n65gEhnZsWmy5lKr06MiO/LDlELuOpPPxXQNc+llK1UYuCzB2X8h0YKcx5nWHQ/OBScAr9vP3DulfiMjrQCuszvwoY0y+iKSLyCCsJrY7gbfPu9Za4CZgmTHGiMjPwN8dOvZHA8+46KtW3rE98OUtkJoA174D/X5X4mnrErOZ7XYVf3n035C4Fmnagden76fN6YbcVNFmLjd36Hu7tTPi+g/gl9dh76Ky8zRsBj2uhy7joP3l4FG9o5gKCgz/WLCTkMYN+N3gtpW+zt1Dw/hsbRyPfrmJ46dznFrY8kJ0bO7H02O74Ofj6fSERaXqE1fWYIYCvwO2ishvdtpfsALLHBGZDMQDEwCMMdtFZA6wA2sE2kPGmHw73wPAJ0ADrM79BXb6dOBTe0BACtYoNIwxKSLyMrDBPu+vhR3+tUJeNmz5Cn5+Fty9YNIP0HZwqafHxJ2kd2hjvLy8rB94oGfocVbtOY4xpnLDQT0bwCWPQ8TdkLLfGlSQe8YaapybaT3ysqxtelsPrLaaSkl+2HKIbUmneOOW3hfUxOTj6c6fRnfmT19vxr+SC1tW1H2XO7dFsFL1kStHka2m5L4QgBIXnjLGTAWmlpAeDRRbB90Yk4UdoEo4NgOY4Wx5q0VWGkR/bPW1pB+GVv3g5pnWelylyMjOY/uhUzxw3g9V79DGfLMxiSOnsi5sDxIff2jVt/L5XSwrN59//bybbsH+XNf7widDXt83hO83H6J/mybaH6KUi+lM/uqQlmSNuoqZafVjhF0O171jdeiXU/vYnJBKfoGhf7ui03gK167anJDmkk2uCgoMkz6O4sZ+oYzvWzWz3JNSM8nKzaeDE4stFvr7TztJPJnJF/f0KrIBVmW5uQmz7o684OsopcrnyomWKuMEfHs//KeXNTy402iYshImzWet9OXNpXvLvcSG2JOIUGwzp27B/ni4CVudmNEfezyDORsSyj3P0ebEVH7Ze5xpqw6Uf7ITluxIZswbq7j27dXsPHzKqTwLtx1h1to47r00jCFVPFdFKeV6GmBcJeuUtTbXtm9gwD3w6CZr+HGrPmTn5fPk3M28uWQv6w6cKPMy0XEpdG7hR0CDoiOnfDzd6dTCjy2J5c/of2XBLp6at4XY4xlOF3/xjmQAdhw+xd7kdKfzna+gwPDW0r3cMyuads0a4ufjyT0zo4vNqj9fUmomf563hV6hATw5puwlYZRStZMGGFfIzYQvb4XkbXDLp3Dlq9bkSVvhSsANvdx5e1nptZj8AsOm+FQizmseK9S7dQBbEtMwpvQpPMdPZ7NkpxUsFmw74vRXWLwjmS4t/XAT+P63Q+VnKMHp7Dwe+DyG1xfv4fq+Icy9fwgfTYogJSOHKbNiyMrNLzFfXn4Bj8/eRF5+AW9N7FtsZ0alVN2g/+dWtfxca15L3K8w/n3oNKbI4bQzuby9bB+XdQrij6M68eu+E0THljzAbdeRU5zOzmNAu5L31OgZ0pi0zFziU86UWpxvNyaRV2AIDvBhwbbDTn2F2OMZ7D16mgkRrRnasRnfb04qM4iVdo0b3v2VJTuP8vw13Xj9ZmsEWI+QAN6c2IfNiak88fXmEq/71rJ9bIg9ydTre9KuAvuXKKVqFw0wVamgAL5/GPYsgKtfg17FB7i9u3Ifp7JyeXpsF24f2JZmjbz4Tyl9MdH2Apf925Zcg+lld/SX1kxmjOGr6AT6tmnMXUPasSUxjYQyglGhwhrP6G4tuK5PCAkpmWyMTy03X6GVe45x7TurOZqezay7I5l8SViRodRjurfkz2O78OOWw7y5pOh3X3fgBO8s21ulgwuUUjVDA0xVMQYWPg1bZsOI56x+l/MkpWby8a+x3NA3lG6t/Gng5c69l7bnl73H2Rh/stj5G2JTCA7wIaRxyaPEOrf0w8vDrdQdLjfGp7Lv6GluiWjNlT2sHQ4XOtFMtshuHmsd2JAx3Vvg7eHG978llZsP4OftR/j9x1G0atyAHx6+pNSFJO+7rD0T+ofyn6V7z147JSOHx2f/Rrumvvz1Ouf3tVdK1U4aYKrKilcg6gMY/DBc+kSJpxSuBPwnh5WA7xjUliYNPXn7vFqMMYbo2JP0b9uk1ImUnu5udAv2Z3MpNZg5GxJo6OXONb1b0aZpQ7q38i+3mexkRg7RsSmM6matjOzn48kVXVvw45bD5OYXlJk3N7+Av/+0k04t/PjmwSFlLo0iIky9vieRYYE8+fUWomNTeGruZlIycnjr1r74eusIeqXqOg0wVWHde7DyFWt5/dF/K3Fuy45D1krAvx/ajlYONRJfbw/uubQ9y3cfK1ITSUrN5MiprFL7Xwr1Cg1ge1Ia+QVF+zIysvP4ccshru4ZTCP7x/qqnsFsjE/lcFpmqddbtusoBQau6Hpu6f3r+rQiJSOH1fuOl1mWeTGJxJ04wxOjO5e4e+T5vDzc+OCO/gQ39uG2j9azZOdRnr6yCz1CXL8ys1LK9TTAXKhje+Dnv1iLP17zn1InTr6ycBcBDUpeCfjOwW0JaODJW0v3nU2LiSu7/6VQr9DGZOTkc+DY6SLp/9tymIycfG4ZcG790Ct7WEujlNVMtnhHMi38venp8CM/rHNzAhp48v2m0pvJsvPyeWvpXvq0bszIrs6vu9XE14sZdw2goZc7V3Rtwe+HtnM6r1KqdtMAc6GCOsFtc+DG6edWQT7P6r3HWbXnGA8P71hsPgtYzVCTLwljyc5ktiVZzV0bYlNo5O1Bl5Z+ZX58aR39X0Un0D7It0iAah/UiC4t/ViwteQAk5Wbz6q9x7iia4sis+a9PNy4qmdLFu1I5kxOXol5v1wfz6G0LJ4Y3bnCa6N1CGrE6j+P4IPf9ddtdpWqRzTAVIXwUaWuMFy4EnBok7JXAp40pB1+Ph5n58VEx56kb5vGeLiX/U/UIagRDb3cizSv7TuaTkzcSW6JaF3sB/vKHsFsiEvh6KniEx3X7j/BmZz8s/0vjq7rE8KZnPyzEzAdZebk887y/QwMC2Rox6Zllrc0jbw9XLLxl1Kq5miAcbH5mw+x/dApnhzTGW+P0hdXDGjgyd1Dw/h5ezJRB1PYnZxORNuy+18A3N2EHq0C2JJ0rgYzJzoRDzfhhn7FN9O6qmdLjLFGe51v0Y5kfL3cGdyheJCIbBdIcIBPiZMuZ62N5fjpbJ4YU/Hai1Kq/tIA40KFKwH3CPFnXK9W5Z5/99AwGnl78PjsTRhDqTP4z9crNIAdh06Rm19ATl4B82ISGdGlOUF+xWtV4S386Ni8ET+d10xWUGBYujOZyzsHlRgI3dyEa3u3YtWeY6Rk5JxNT8/K5b2V+7m8U1C5AxKUUhcXDTAu9M3GJJJSM3l6bFenVgIOaOjJXUPacSgtC3c3oU/rxk59Tq/WjcnOK2BPcjrLdiVzIiOnSOf++a7q0ZL1B09w/HT22bQtSWkcTc8usXms0HV9QsgrMPxv67mhzjNWx5J6JpcnRrt28y6lVN2jAcZFjDF8ui6OrsH+FeqXmHxJGA293OkW7O/0XJBeIec6+r/akEBzP28u7xRU6vlX9gymwMCi7ef6UxbvOIK7mzC8c+kjwLoG+xHevNHZ0WSpZ3L46JcDjOne4uz2AUopVUgDjItsjE9l5+FT3DGoTYX6JZr4evH+Hf158dpuTudp27Qh/j4eLN6RzMo9x7ipf2iZgwO6tPQjrJlvkUmXS3YcZUC7JjRu6FVqPhFhfN8QouNOkpByhmmrDnA6J48/jtLai1KqOA0wLvL5ujgaeXswvk/F19O6rFMQ/Z3o4C8kIvQKbXx2kuTNEaU3jxWeP7ZHS9bsP8HJjBziT5xhd3I6o7qVv4Xwtb2tvqQZvx7k419jubZ3KzqXM5RaKXVx0gDjAikZOfy49TA39AuptiVPCufDDGof6NQKxFf1CCa/wLB4RzKLdlgd/qPL6H8p1DqwIf3bNuHjX2PJyS/gsZHhF1ZwpVS9pQs+ucDX0Qnk5BVwx6DS571Utd72gICyOvcd9QjxJ7RJA37adpjMnPyzi1s6Y3yfVsTEneTGfiG0r8D2x0qpi4sGmCpWUGD4IiqeyHaBdGpRfU1HV3Rtwft39HOqmQusZrKregbz8a8HKTDwwOUdnP6s8X1D2HUknUe19qKUKoM2kVWxX/YdJ+7EGW4f1KZaP9fdTRjbI7hCs+Gv7NGS3HxDfoEpc3jy+fx8PJl6fU9a+PtUpqhKqYuE1mCq2Gfr4mjq68XYHs7VJGpSn9aNaRXgQ74xRRa3VEqpqqABpgolpWaydGcy913eocxlYWoLEeGVG3uRb4xTE0GVUqoiNMBUodlR8RjgtsjqbR67EJeVMSFTKaUuhPbBVJHc/AJmb0hgeOfmTo/GUkqp+kwDTBVZtD2ZY+nZ3FHNnftKKVVbaYCpIp+tiyOkcQMu7+T8bo5KKVWfuSzAiMgMETkqItsc0gJFZLGI7LWfmzgce0ZE9onIbhEZ45DeX0S22sfeEnthLxHxFpGv7PT1ItLOIc8k+zP2isgkV33HQvuOprP2wAluG9hGN81SSimbK2swnwBjz0t7GlhqjAkHltrvEZFuwESgu53nXREpHIb1HjAFCLcfhdecDJw0xnQE3gBeta8VCLwADAQigRccA5krfLYuHk93cXoWvVJKXQxcFmCMMauAlPOSrwNm2q9nAuMd0mcbY7KNMQeBfUCkiAQD/saYtcYYA8w6L0/hteYCI+3azRhgsTEmxRhzElhM8UBXZc7k5DFvYyJjewTTrFHJ2yYrpdTFqLr7YFoYYw4D2M+FHRYhQILDeYl2Woj9+vz0InmMMXlAGtC0jGsVIyJTRCRaRKKPHTtWqS+UnpXH5Z2CuHNw9a07ppRSdUFtmQdTUseFKSO9snmKJhozDZgGEBERUeI55Wnh78M7t/WrTFallKrXqrsGk2w3e2E/H7XTEwHHDoxQ4JCdHlpCepE8IuIBBGA1yZV2LaWUUtWougPMfKBwVNck4HuH9In2yLAwrM78KLsZLV1EBtn9K3eel6fwWjcBy+x+mp+B0SLSxO7cH22nKaWUqkYuayITkS+BYUAzEUnEGtn1CjBHRCYD8cAEAGPMdhGZA+wA8oCHjDH59qUewBqR1gBYYD8ApgOfisg+rJrLRPtaKSLyMrDBPu+vxpjzBxsopZRyMbH+6FcREREmOjq6pouhlFJ1iojEGGMiSjqmM/mVUkq5hAYYpZRSLqEBRimllEtogFFKKeUS2slvE5FjQFxNl+MCNQOO13QhahG9H0Xp/ThH70VRF3I/2hpjSty5UANMPSIi0aWN5rgY6f0oSu/HOXovinLV/dAmMqWUUi6hAUYppZRLaICpX6bVdAFqGb0fRen9OEfvRVEuuR/aB6OUUsoltAajlFLKJTTAKKWUcgkNMLWciMwQkaMiss0hrbeIrBWRrSLyg4j42+meIjLTTt8pIs845Olvp+8Tkbfs7Q/qlAreCy8R+dhO3ywiwxzy1Pl7ASAirUVkuf1vvV1EHrPTA0VksYjstZ+bOOR5xv7eu0VkjEN6nb4nFb0XItLUPv+0iLxz3rXq9L2ASt2PUSISY3/vGBEZ4XCtyt8PY4w+avEDuAzoB2xzSNsAXG6/vht42X59GzDbft0QiAXa2e+jgMFYO34uAK6s6e/m4nvxEPCx/bo5EAO41Zd7YX+PYKCf/doP2AN0A/4JPG2nPw28ar/uBmwGvIEwYD/gXh/uSSXuhS9wCXA/8M5516rT96KS96Mv0Mp+3QNIqor7oTWYWs4YswprvxtHnYFV9uvFwI2FpwO+9g6fDYAc4JS9e6i/MWatsf6LmQWMd3XZq1oF70U3YKmd7yiQCkTUl3sBYIw5bIzZaL9OB3YCIcB1wEz7tJmc+37XYf0Bkm2MOQjsAyLrwz2p6L0wxmQYY1YDWY7XqQ/3Aip1PzYZYwp3/t0O+NgbQF7Q/dAAUzdtA661X0/g3BbRc4EM4DDWhm6vGWuztRCsraQLJdpp9UFp92IzcJ2IeIi1S2p/+1i9vBci0g7rr9D1QAtj7QaL/dzcPi0ESHDIVvjd69U9cfJelKZe3Quo1P24EdhkjMnmAu+HBpi66W7gIRGJwar+5tjpkUA+0AqrCeRPItIeq2p7vvoyPr20ezED63+GaOBNYA3Wbqn17l6ISCNgHvC4MeZUWaeWkGbKSK9zKnAvSr1ECWl18l5Axe+HiHQHXgXuK0wq4TSn74fLtkxWrmOM2QWMBhCRTsDV9qHbgIXGmFzgqIj8CkQAvwChDpcIBQ5RD5R2L4wxecAfCs8TkTXAXuAk9eheiIgn1g/I58aYb+zkZBEJNsYctps4jtrpiZyr4cG5755IPbgnFbwXpakX9wIqfj9EJBT4FrjTGLPfTr6g+6E1mDpIRJrbz27Ac8D79qF4YIRYfIFBwC67KpwuIoPsESB3At/XQNGrXGn3QkQa2vcAERkF5BljdtSne2GXfzqw0xjzusOh+cAk+/Ukzn2/+cBEu209DAgHourDPanEvShRfbgXUPH7ISKNgf8Bzxhjfi08+YLvR02PdtBHuaNBvsTqU8nF+mtiMvAY1qiQPcArnFuRoRHwNVYn3Q7gSYfrRGD1V+wH3inMU5ceFbwX7YDdWJ2bS7CWFK8398L+HpdgNVdsAX6zH1cBTbEGOOy1nwMd8jxrf+/dOIwGquv3pJL3IhZr0Mhp+7+nbvXhXlTmfmD9cZbhcO5vQPMLvR+6VIxSSimX0CYypZRSLqEBRimllEtogFFKKeUSGmCUUkq5hAYYpZRSLqEBRimllEtogFGqHhER95oug1KFNMAoVUNE5OXCfTrs91NF5FEReVJENojIFhF5yeH4d/ZeHdtFZIpD+mkR+auIrMdaVl2pWkEDjFI1Zzr2sh32UjcTgWSsJVwigT5AfxG5zD7/bmNMf6yZ1Y+KSFM73Rdrj5yBxlqCXqlaQRe7VKqGGGNiReSEiPQFWgCbgAFYi3dusk9rhBVwVmEFlevt9NZ2+gmsFbTnVWfZlXKGBhilatZHwF1AS6wtBkYC/zDGfOB4klhbPl8BDDbGnBGRFYCPfTjLGJNfTeVVymnaRKZUzfoWGItVc/nZftxt7+OBiITYK0YHACft4NIFa6VspWo1rcEoVYOMMTkishxItWshi0SkK7DWWh2d08AdwELgfhHZgrUS8rqaKrNSztLVlJWqQXbn/kZggjFmb02XR6mqpE1kStUQEekG7AOWanBR9ZHWYJRSSrmE1mCUUkq5hAYYpZRSLqEBRimllEtogFFKKeUSGmCUUkq5xP8D/FBziBAU5SYAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Now lets plot\n",
    "sns.lineplot(data = grouped_gender, x='year', y='Approved Amount', hue='Gender')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f705376",
   "metadata": {},
   "source": [
    "#### Objective 2: how much time from becoming an exployee to applying for a grant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "5f9e6bbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, we summarize by researcher id to find the earliest date\n",
    "first_empl = merged_empl.groupby('Person ID SNSF').date.min().reset_index()\n",
    "first_appl = merged_appl.groupby('Person ID SNSF').date.min().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "89ebb9c9",
   "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>Person ID SNSF</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1611</td>\n",
       "      <td>1990-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1659</td>\n",
       "      <td>1988-01-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1661</td>\n",
       "      <td>1978-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1694</td>\n",
       "      <td>1978-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1712</td>\n",
       "      <td>1982-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80830</th>\n",
       "      <td>768766</td>\n",
       "      <td>2018-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80831</th>\n",
       "      <td>768791</td>\n",
       "      <td>2020-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80832</th>\n",
       "      <td>768792</td>\n",
       "      <td>2019-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80833</th>\n",
       "      <td>768793</td>\n",
       "      <td>2019-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80834</th>\n",
       "      <td>768794</td>\n",
       "      <td>2019-01-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80835 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Person ID SNSF       date\n",
       "0                1611 1990-01-10\n",
       "1                1659 1988-01-11\n",
       "2                1661 1978-01-07\n",
       "3                1694 1978-01-06\n",
       "4                1712 1982-01-04\n",
       "...               ...        ...\n",
       "80830          768766 2018-01-09\n",
       "80831          768791 2020-01-03\n",
       "80832          768792 2019-01-12\n",
       "80833          768793 2019-01-10\n",
       "80834          768794 2019-01-04\n",
       "\n",
       "[80835 rows x 2 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lets chedck\n",
    "first_empl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "bb5c3708",
   "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>Person ID SNSF</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>271</td>\n",
       "      <td>1989-09-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>367</td>\n",
       "      <td>1978-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1236</td>\n",
       "      <td>1983-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1275</td>\n",
       "      <td>1984-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1296</td>\n",
       "      <td>1977-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28171</th>\n",
       "      <td>757915</td>\n",
       "      <td>2020-01-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28172</th>\n",
       "      <td>757920</td>\n",
       "      <td>2020-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28173</th>\n",
       "      <td>758727</td>\n",
       "      <td>2020-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28174</th>\n",
       "      <td>760184</td>\n",
       "      <td>2020-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28175</th>\n",
       "      <td>761967</td>\n",
       "      <td>2017-01-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28176 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Person ID SNSF       date\n",
       "0                 271 1989-09-20\n",
       "1                 367 1978-01-06\n",
       "2                1236 1983-01-04\n",
       "3                1275 1984-01-04\n",
       "4                1296 1977-01-10\n",
       "...               ...        ...\n",
       "28171          757915 2020-01-11\n",
       "28172          757920 2020-01-04\n",
       "28173          758727 2020-01-05\n",
       "28174          760184 2020-01-06\n",
       "28175          761967 2017-01-03\n",
       "\n",
       "[28176 rows x 2 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# And this one...\n",
    "first_appl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00b2c92f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Both look good"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "3d655e9a",
   "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>Person ID SNSF</th>\n",
       "      <th>date_appl</th>\n",
       "      <th>date_empl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1659</td>\n",
       "      <td>1975-01-10</td>\n",
       "      <td>1988-01-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1661</td>\n",
       "      <td>1978-01-07</td>\n",
       "      <td>1978-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1694</td>\n",
       "      <td>1985-01-01</td>\n",
       "      <td>1978-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1712</td>\n",
       "      <td>1982-01-04</td>\n",
       "      <td>1982-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1726</td>\n",
       "      <td>1985-01-03</td>\n",
       "      <td>1985-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10336</th>\n",
       "      <td>748652</td>\n",
       "      <td>2019-01-12</td>\n",
       "      <td>2019-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10337</th>\n",
       "      <td>748760</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10338</th>\n",
       "      <td>749430</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>2020-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10339</th>\n",
       "      <td>749991</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10340</th>\n",
       "      <td>750593</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10341 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Person ID SNSF  date_appl  date_empl\n",
       "0                1659 1975-01-10 1988-01-11\n",
       "1                1661 1978-01-07 1978-01-07\n",
       "2                1694 1985-01-01 1978-01-06\n",
       "3                1712 1982-01-04 1982-01-04\n",
       "4                1726 1985-01-03 1985-01-03\n",
       "...               ...        ...        ...\n",
       "10336          748652 2019-01-12 2019-01-12\n",
       "10337          748760 2020-01-03 2020-01-03\n",
       "10338          749430 2020-01-04 2020-01-04\n",
       "10339          749991 2020-01-03 2020-01-03\n",
       "10340          750593 2020-01-01 2020-01-01\n",
       "\n",
       "[10341 rows x 3 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# So now we merge both and rename as appropriate\n",
    "merge_first = pd.merge(first_appl, first_empl, on = 'Person ID SNSF', suffixes=('_appl', '_empl'))\n",
    "merge_first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "78d60804",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Then we merge back into the main table\n",
    "full_table = pd.merge(merge_first, merged_appl,on = 'Person ID SNSF')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "1a14fd01",
   "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>Person ID SNSF</th>\n",
       "      <th>date_appl</th>\n",
       "      <th>date_empl</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Institute Name</th>\n",
       "      <th>Institute Place</th>\n",
       "      <th>OCRID</th>\n",
       "      <th>Projects as responsible Applicant</th>\n",
       "      <th>...</th>\n",
       "      <th>Discipline Name</th>\n",
       "      <th>Discipline Name Hierarchy</th>\n",
       "      <th>All disciplines</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Approved Amount</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>time_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1659</td>\n",
       "      <td>1975-01-10</td>\n",
       "      <td>1988-01-11</td>\n",
       "      <td>Borner</td>\n",
       "      <td>Silvio</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für Volkswirtschaft WWZ / FGS Univers...</td>\n",
       "      <td>Basel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19;432;1767;9334;10363;10686;25341;30983;35421...</td>\n",
       "      <td>...</td>\n",
       "      <td>Economics</td>\n",
       "      <td>Humanities and Social Sciences;Economics, law</td>\n",
       "      <td>10203</td>\n",
       "      <td>01.10.1975</td>\n",
       "      <td>31.12.1978</td>\n",
       "      <td>112664.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1975-01-10</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>-13.010959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1659</td>\n",
       "      <td>1975-01-10</td>\n",
       "      <td>1988-01-11</td>\n",
       "      <td>Borner</td>\n",
       "      <td>Silvio</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für Volkswirtschaft WWZ / FGS Univers...</td>\n",
       "      <td>Basel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19;432;1767;9334;10363;10686;25341;30983;35421...</td>\n",
       "      <td>...</td>\n",
       "      <td>Economics</td>\n",
       "      <td>Humanities and Social Sciences;Economics, law</td>\n",
       "      <td>10203</td>\n",
       "      <td>01.04.1978</td>\n",
       "      <td>30.11.1980</td>\n",
       "      <td>228536.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1978-01-04</td>\n",
       "      <td>1978.0</td>\n",
       "      <td>-13.010959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1659</td>\n",
       "      <td>1975-01-10</td>\n",
       "      <td>1988-01-11</td>\n",
       "      <td>Borner</td>\n",
       "      <td>Silvio</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für Volkswirtschaft WWZ / FGS Univers...</td>\n",
       "      <td>Basel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19;432;1767;9334;10363;10686;25341;30983;35421...</td>\n",
       "      <td>...</td>\n",
       "      <td>Economics</td>\n",
       "      <td>Humanities and Social Sciences;Economics, law</td>\n",
       "      <td>10203</td>\n",
       "      <td>01.10.1985</td>\n",
       "      <td>31.03.1987</td>\n",
       "      <td>24204.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1985-01-10</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>-13.010959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1659</td>\n",
       "      <td>1975-01-10</td>\n",
       "      <td>1988-01-11</td>\n",
       "      <td>Borner</td>\n",
       "      <td>Silvio</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für Volkswirtschaft WWZ / FGS Univers...</td>\n",
       "      <td>Basel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19;432;1767;9334;10363;10686;25341;30983;35421...</td>\n",
       "      <td>...</td>\n",
       "      <td>Public Health and Health Services</td>\n",
       "      <td>Biology and Medicine;Social Medicine</td>\n",
       "      <td>30911</td>\n",
       "      <td>01.10.1987</td>\n",
       "      <td>30.09.1988</td>\n",
       "      <td>70093.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1987-01-10</td>\n",
       "      <td>1987.0</td>\n",
       "      <td>-13.010959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1659</td>\n",
       "      <td>1975-01-10</td>\n",
       "      <td>1988-01-11</td>\n",
       "      <td>Borner</td>\n",
       "      <td>Silvio</td>\n",
       "      <td>male</td>\n",
       "      <td>Institut für Volkswirtschaft WWZ / FGS Univers...</td>\n",
       "      <td>Basel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19;432;1767;9334;10363;10686;25341;30983;35421...</td>\n",
       "      <td>...</td>\n",
       "      <td>Economics</td>\n",
       "      <td>Humanities and Social Sciences;Economics, law</td>\n",
       "      <td>10203</td>\n",
       "      <td>01.06.1980</td>\n",
       "      <td>31.03.1984</td>\n",
       "      <td>340672.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1980-01-06</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>-13.010959</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>24092</th>\n",
       "      <td>748652</td>\n",
       "      <td>2019-01-12</td>\n",
       "      <td>2019-01-12</td>\n",
       "      <td>Le Bouc</td>\n",
       "      <td>Raphaël</td>\n",
       "      <td>male</td>\n",
       "      <td>Department of Economics Universität Zürich</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>NaN</td>\n",
       "      <td>190501</td>\n",
       "      <td>...</td>\n",
       "      <td>Neurophysiology and Brain Research</td>\n",
       "      <td>Biology and Medicine;Basic Medical Sciences</td>\n",
       "      <td>30302</td>\n",
       "      <td>01.12.2019</td>\n",
       "      <td>31.10.2020</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>orbitofrontal cortex; Motivation; Perception; ...</td>\n",
       "      <td>2019-01-12</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24093</th>\n",
       "      <td>748760</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>Wartmann</td>\n",
       "      <td>Julia Elisabeth</td>\n",
       "      <td>female</td>\n",
       "      <td>Graduate School of Social Sciences (G3S) Depar...</td>\n",
       "      <td>Basel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191726</td>\n",
       "      <td>...</td>\n",
       "      <td>Political science</td>\n",
       "      <td>Humanities and Social Sciences;Sociology, soci...</td>\n",
       "      <td>10202</td>\n",
       "      <td>01.03.2020</td>\n",
       "      <td>30.09.2023</td>\n",
       "      <td>246167.0</td>\n",
       "      <td>gender equality; institutional change; gender ...</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24094</th>\n",
       "      <td>749430</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>Valle</td>\n",
       "      <td>Giacomo</td>\n",
       "      <td>male</td>\n",
       "      <td>Scuola Superiore Sant'Anna The Biorobotics Ins...</td>\n",
       "      <td>Pisa</td>\n",
       "      <td>0000-0002-2637-8007</td>\n",
       "      <td>193724</td>\n",
       "      <td>...</td>\n",
       "      <td>Biomedical Engineering</td>\n",
       "      <td>Biology and Medicine;Clinical Medicine</td>\n",
       "      <td>30714/30708</td>\n",
       "      <td>01.04.2020</td>\n",
       "      <td>31.03.2021</td>\n",
       "      <td>129982.0</td>\n",
       "      <td>prosthesis; electrical stimulation; sensory fe...</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24095</th>\n",
       "      <td>749991</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>Bittel</td>\n",
       "      <td>Sarah</td>\n",
       "      <td>female</td>\n",
       "      <td>Department of Anthropology and Sociology  The ...</td>\n",
       "      <td>Genève 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191592</td>\n",
       "      <td>...</td>\n",
       "      <td>Ethnology</td>\n",
       "      <td>Humanities and Social Sciences;Ethnology, soci...</td>\n",
       "      <td>10403</td>\n",
       "      <td>01.03.2020</td>\n",
       "      <td>30.09.2022</td>\n",
       "      <td>176024.0</td>\n",
       "      <td>Agency; Subjectivities; Politics; Asylum-Polic...</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24096</th>\n",
       "      <td>750593</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>Beck</td>\n",
       "      <td>Sven</td>\n",
       "      <td>male</td>\n",
       "      <td>Management für Informationssysteme ETH Zürich</td>\n",
       "      <td>Zürich</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191539</td>\n",
       "      <td>...</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Mathematics, Natural- and Engineering Sciences...</td>\n",
       "      <td>20506</td>\n",
       "      <td>01.01.2020</td>\n",
       "      <td>31.12.2020</td>\n",
       "      <td>130000.0</td>\n",
       "      <td>Semi structured document understanding; Machin...</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24097 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Person ID SNSF  date_appl  date_empl Last Name       First Name  \\\n",
       "0                1659 1975-01-10 1988-01-11    Borner           Silvio   \n",
       "1                1659 1975-01-10 1988-01-11    Borner           Silvio   \n",
       "2                1659 1975-01-10 1988-01-11    Borner           Silvio   \n",
       "3                1659 1975-01-10 1988-01-11    Borner           Silvio   \n",
       "4                1659 1975-01-10 1988-01-11    Borner           Silvio   \n",
       "...               ...        ...        ...       ...              ...   \n",
       "24092          748652 2019-01-12 2019-01-12   Le Bouc          Raphaël   \n",
       "24093          748760 2020-01-03 2020-01-03  Wartmann  Julia Elisabeth   \n",
       "24094          749430 2020-01-04 2020-01-04     Valle          Giacomo   \n",
       "24095          749991 2020-01-03 2020-01-03    Bittel            Sarah   \n",
       "24096          750593 2020-01-01 2020-01-01      Beck             Sven   \n",
       "\n",
       "       Gender                                     Institute Name  \\\n",
       "0        male  Institut für Volkswirtschaft WWZ / FGS Univers...   \n",
       "1        male  Institut für Volkswirtschaft WWZ / FGS Univers...   \n",
       "2        male  Institut für Volkswirtschaft WWZ / FGS Univers...   \n",
       "3        male  Institut für Volkswirtschaft WWZ / FGS Univers...   \n",
       "4        male  Institut für Volkswirtschaft WWZ / FGS Univers...   \n",
       "...       ...                                                ...   \n",
       "24092    male         Department of Economics Universität Zürich   \n",
       "24093  female  Graduate School of Social Sciences (G3S) Depar...   \n",
       "24094    male  Scuola Superiore Sant'Anna The Biorobotics Ins...   \n",
       "24095  female  Department of Anthropology and Sociology  The ...   \n",
       "24096    male      Management für Informationssysteme ETH Zürich   \n",
       "\n",
       "      Institute Place                OCRID  \\\n",
       "0               Basel                  NaN   \n",
       "1               Basel                  NaN   \n",
       "2               Basel                  NaN   \n",
       "3               Basel                  NaN   \n",
       "4               Basel                  NaN   \n",
       "...               ...                  ...   \n",
       "24092          Zürich                  NaN   \n",
       "24093           Basel                  NaN   \n",
       "24094            Pisa  0000-0002-2637-8007   \n",
       "24095        Genève 1                  NaN   \n",
       "24096          Zürich                  NaN   \n",
       "\n",
       "                       Projects as responsible Applicant  ...  \\\n",
       "0      19;432;1767;9334;10363;10686;25341;30983;35421...  ...   \n",
       "1      19;432;1767;9334;10363;10686;25341;30983;35421...  ...   \n",
       "2      19;432;1767;9334;10363;10686;25341;30983;35421...  ...   \n",
       "3      19;432;1767;9334;10363;10686;25341;30983;35421...  ...   \n",
       "4      19;432;1767;9334;10363;10686;25341;30983;35421...  ...   \n",
       "...                                                  ...  ...   \n",
       "24092                                             190501  ...   \n",
       "24093                                             191726  ...   \n",
       "24094                                             193724  ...   \n",
       "24095                                             191592  ...   \n",
       "24096                                             191539  ...   \n",
       "\n",
       "                          Discipline Name  \\\n",
       "0                               Economics   \n",
       "1                               Economics   \n",
       "2                               Economics   \n",
       "3       Public Health and Health Services   \n",
       "4                               Economics   \n",
       "...                                   ...   \n",
       "24092  Neurophysiology and Brain Research   \n",
       "24093                   Political science   \n",
       "24094              Biomedical Engineering   \n",
       "24095                           Ethnology   \n",
       "24096              Information Technology   \n",
       "\n",
       "                               Discipline Name Hierarchy All disciplines  \\\n",
       "0          Humanities and Social Sciences;Economics, law           10203   \n",
       "1          Humanities and Social Sciences;Economics, law           10203   \n",
       "2          Humanities and Social Sciences;Economics, law           10203   \n",
       "3                   Biology and Medicine;Social Medicine           30911   \n",
       "4          Humanities and Social Sciences;Economics, law           10203   \n",
       "...                                                  ...             ...   \n",
       "24092        Biology and Medicine;Basic Medical Sciences           30302   \n",
       "24093  Humanities and Social Sciences;Sociology, soci...           10202   \n",
       "24094             Biology and Medicine;Clinical Medicine     30714/30708   \n",
       "24095  Humanities and Social Sciences;Ethnology, soci...           10403   \n",
       "24096  Mathematics, Natural- and Engineering Sciences...           20506   \n",
       "\n",
       "       Start Date    End Date  Approved Amount  \\\n",
       "0      01.10.1975  31.12.1978         112664.0   \n",
       "1      01.04.1978  30.11.1980         228536.0   \n",
       "2      01.10.1985  31.03.1987          24204.0   \n",
       "3      01.10.1987  30.09.1988          70093.0   \n",
       "4      01.06.1980  31.03.1984         340672.0   \n",
       "...           ...         ...              ...   \n",
       "24092  01.12.2019  31.10.2020         100000.0   \n",
       "24093  01.03.2020  30.09.2023         246167.0   \n",
       "24094  01.04.2020  31.03.2021         129982.0   \n",
       "24095  01.03.2020  30.09.2022         176024.0   \n",
       "24096  01.01.2020  31.12.2020         130000.0   \n",
       "\n",
       "                                                Keywords       date    year  \\\n",
       "0                                                    NaN 1975-01-10  1975.0   \n",
       "1                                                    NaN 1978-01-04  1978.0   \n",
       "2                                                    NaN 1985-01-10  1985.0   \n",
       "3                                                    NaN 1987-01-10  1987.0   \n",
       "4                                                    NaN 1980-01-06  1980.0   \n",
       "...                                                  ...        ...     ...   \n",
       "24092  orbitofrontal cortex; Motivation; Perception; ... 2019-01-12  2019.0   \n",
       "24093  gender equality; institutional change; gender ... 2020-01-03  2020.0   \n",
       "24094  prosthesis; electrical stimulation; sensory fe... 2020-01-04  2020.0   \n",
       "24095  Agency; Subjectivities; Politics; Asylum-Polic... 2020-01-03  2020.0   \n",
       "24096  Semi structured document understanding; Machin... 2020-01-01  2020.0   \n",
       "\n",
       "       time_diff  \n",
       "0     -13.010959  \n",
       "1     -13.010959  \n",
       "2     -13.010959  \n",
       "3     -13.010959  \n",
       "4     -13.010959  \n",
       "...          ...  \n",
       "24092   0.000000  \n",
       "24093   0.000000  \n",
       "24094   0.000000  \n",
       "24095   0.000000  \n",
       "24096   0.000000  \n",
       "\n",
       "[24097 rows x 37 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Finally, we can add a difference of dates column\n",
    "full_table['time_diff'] = full_table.date_appl-full_table.date_empl\n",
    "# Then we convert to years...\n",
    "full_table.time_diff = full_table.time_diff.apply(lambda x: x.days/365)\n",
    "# And check....\n",
    "full_table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "878231fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[<AxesSubplot:title={'center':'time_diff'}>]], dtype=object)"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAEICAYAAABS0fM3AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAAcEElEQVR4nO3de5BcZ53e8e+D8BrFg2/YTMmSFplFJNjWrllNtK5incysvVhrO8hU1kSUwdLiLVGOyUKtUlheUmCKqFZk15A4Bm8EAssYmGi5lIXBAeP1xEXFRkhgGMuXtYgUowtSAN+GuJSMePJHv7Nq5J6ZVs+lu3WeT1VXn/Oe855+uqunf3Pec7qPbBMREdX1snYHiIiI9kohiIiouBSCiIiKSyGIiKi4FIKIiIpLIYiIqLgUgoiIikshiBOepN+UNCJpThszrJb0nbr5EUmvLdNzJX1N0nOS/ra0/XtJP5P003Zljup4ebsDRMwESXuAP7X9bdtPAz1tjvRrbNfn+WOgF3iV7VFJC4G1wGtsH2pLwKiU7BFEtN9rgL+3PVo3//MUgZgtKQRxwpH0OeA3ga+VIZj3S7Kkl5flQ2Xo5X+U5V+T9CpJn5f0vKTvSVpUt71/Iuk+Sb+Q9KSktzWR4VWStpbtbQN+65jllvQ6SR8GPgj8q5Ll3cB9wDll/o5pe2EixqH81lCciOqHhsqH+m7gpDL0MgQsAC4DfgY8RG2Y9F8DQ8BngCO2/0TSKcAT1D6sPwf8NvAt4J/b3jnB4w9S+0frT4BzgW8Cu23/flluYLHtXZJuBl5n+x1lWT9wl+0F0/RyREwoewRRVZ+1/WPbzwH3Aj8uxxNGgb8F3ljWuxLYY/uztkdtfx/4MrVx/YbKQel/CXzQ9i9tPwpsntFnEzEFOVgcVXWwbvrFBvNjB3NfA/yepGfrlr+c2t7BeM4u6/ykru1/tZw0YoalEMSJarrGPH8C/Hfbf3gcff43MAospDasBLVjFhEdKUNDcaI6CLx2GrZzD/B6Se+UdFK5/VNJbxivg+0jwFeAmyX9I0nnAaumIUvEjEghiBPVXwL/rgzpjDuePxnbLwBvBlYC+4GfAh8FTp6k63uoDS/9FLgD+GyrGSJmWs4aioiouOwRRERUXApBRIsk7Sxf+jr2dk27s0UcjwwNRURUXMefPnrWWWd50aJFLfX95S9/ySmnnDK9gWZYN2aG7szdjZmhO3Mn8+wZy71jx46f2T67qU62O/q2dOlSt+qBBx5ouW+7dGNmuztzd2NmuztzJ/PsGcsNbHeTn7M5RhARUXEpBBERFZdCEBFRcSkEEREVl0IQEVFxKQQRERWXQhARUXEpBBERFZdCEBFRcR3/ExMR3WbRuq83bN+z4YpZThLRnKb3CCTNkfQDSfeU+TMl3SfpqXJ/Rt26N0naJelJSZfVtS+VNFyW3SpJ0/t0IiLieB3P0NB7gcfr5tcB99teDNxf5imX5VsJnA8sBz4paU7pczuwBlhcbsunlD4iIqasqUIgaQFwBfDpuuYVwOYyvRm4qq590PZh27uBXcAySfOAU20/VH4Q6c66PhER0SZNXY9A0peoXQP2lcC/tX2lpGdtn163zjO2z5B0G/Cw7btK+ybgXmAPsMH2paX9YuBG21c2eLw11PYc6O3tXTo4ONjSkxsZGaGnp6elvu3SjZmhO3PPVObhfc81bF8y/7Rp2X5e69nRjZnhaO6BgYEdtvua6TPpwWJJVwKHbO+Q1N/ENhuN+3uC9pc22huBjQB9fX3u72/mYV9qaGiIVvu2Szdmhu7MPVOZV493sPia6XmsvNazoxszQ2u5mzlr6E3AWyRdDrwCOFXSXcBBSfNsHyjDPofK+nuBhXX9FwD7S/uCBu0REdFGkx4jsH2T7QW2F1E7CPx3tt8BbAVWldVWAXeX6a3ASkknSzqX2kHhbbYPAC9IuqicLXRtXZ+IiGiTqXyPYAOwRdJ1wNPA1QC2d0raAjwGjAI32D5S+lwP3AHMpXbc4N4pPH5EREyD4yoEtoeAoTL9c+CScdZbD6xv0L4duOB4Q0ZExMzJT0xERFRcCkFERMWlEEREVFwKQURExaUQRERUXApBRETFpRBERFRcCkFERMWlEEREVFwKQURExaUQRERUXApBRETFpRBERFRcCkFERMWlEEREVFwKQURExU1aCCS9QtI2ST+UtFPSh0v7zZL2SXqk3C6v63OTpF2SnpR0WV37UknDZdmt5ZKVERHRRs1coeww8Ae2RySdBHxH0tglJj9u+6/rV5Z0HrVrG58PnAN8W9Lry+UqbwfWAA8D3wCWk8tVRkS0VTMXr7ftkTJ7Url5gi4rgEHbh23vBnYByyTNA061/ZBtA3cCV00pfURETJlqn8mTrCTNAXYArwM+YftGSTcDq4Hnge3AWtvPSLoNeNj2XaXvJmr/9e8BNti+tLRfDNxo+8oGj7eG2p4Dvb29SwcHB1t6ciMjI/T09LTUt126MTN0Z+6Zyjy877mG7UvmnzYt289rPTu6MTMczT0wMLDDdl8zfZq6eH0Z1rlQ0unAVyVdQG2Y5yPU9g4+AtwCvAtoNO7vCdobPd5GYCNAX1+f+/v7m4n5EkNDQ7Tat126MTN0Z+6Zyrx63dcbtu+5ZnoeK6/17OjGzNBa7uM6a8j2s8AQsNz2QdtHbP8K+BSwrKy2F1hY120BsL+0L2jQHhERbdTMWUNnlz0BJM0FLgWeKGP+Y94KPFqmtwIrJZ0s6VxgMbDN9gHgBUkXlbOFrgXunr6nEhERrWhmaGgesLkcJ3gZsMX2PZI+J+lCasM7e4B3A9jeKWkL8BgwCtxQhpYArgfuAOZSO26QM4YiItps0kJg+0fAGxu0v3OCPuuB9Q3atwMXHGfGiIiYQflmcURExaUQRERUXApBRETFpRBERFRcCkFERMWlEEREVFwKQURExaUQRERUXApBRETFpRBERFRcCkFERMWlEEREVFwKQURExaUQRERUXApBRETFpRBERFTcpBemkfQK4EHg5LL+l2x/SNKZwH8FFlG7QtnbbD9T+twEXAccAf7M9jdL+1KOXqHsG8B7bTe8gH3ETFlULi6/dsnor11ofs+GK9oVKaKtmtkjOAz8ge3fAS4Elku6CFgH3G97MXB/mUfSecBK4HxgOfDJcplLgNuBNdSuY7y4LI+IiDaatBC4ZqTMnlRuBlYAm0v7ZuCqMr0CGLR92PZuYBewrFzs/lTbD5W9gDvr+kRERJs0dYxA0hxJjwCHgPtsfxfotX0AoNy/uqw+H/hJXfe9pW1+mT62PSIi2kjHM0Qv6XTgq8C/Ab5j+/S6Zc/YPkPSJ4CHbN9V2jdROx7wNPCXti8t7RcD77f9Lxo8zhpqQ0j09vYuHRwcbOnJjYyM0NPT01LfdunGzNBduYf3PQdA71w4+OLR9iXzT5vW7R9rurbfTa/1mGSePWO5BwYGdtjua6bPpAeL69l+VtIQtbH9g5Lm2T5Qhn0OldX2Agvrui0A9pf2BQ3aGz3ORmAjQF9fn/v7+48n5j8YGhqi1b7t0o2Zobtyr647WHzL8NE/gT3X9E/r9o81Xdvvptd6TDLPnlZyTzo0JOnssieApLnApcATwFZgVVltFXB3md4KrJR0sqRzqR0U3laGj16QdJEkAdfW9YmIiDZpZo9gHrC5nPnzMmCL7XskPQRskXQdtWGfqwFs75S0BXgMGAVusH2kbOt6jp4+em+5RUREG01aCGz/CHhjg/afA5eM02c9sL5B+3bgguOPGRERMyXfLI6IqLgUgoiIikshiIiouBSCiIiKSyGIiKi4FIKIiIpLIYiIqLgUgoiIikshiIiouBSCiIiKSyGIiKi4FIKIiIpLIYiIqLgUgoiIikshiIiouBSCiIiKSyGIiKi4Zq5ZvFDSA5Iel7RT0ntL+82S9kl6pNwur+tzk6Rdkp6UdFld+1JJw2XZreXaxRER0UbNXLN4FFhr+/uSXgnskHRfWfZx239dv7Kk84CVwPnAOcC3Jb2+XLf4dmAN8DDwDWA5uW5xRERbTbpHYPuA7e+X6ReAx4H5E3RZAQzaPmx7N7ALWCZpHnCq7YdsG7gTuGqqTyAiIqZGtc/kJleWFgEPUrsA/Z8Dq4Hnge3U9hqekXQb8LDtu0qfTdT+698DbLB9aWm/GLjR9pUNHmcNtT0Hent7lw4ODrb05EZGRujp6Wmpb7t0Y2bortzD+54DoHcuHHzxaPuS+adN6/aPNV3b76bXekwyz56x3AMDAzts9zXTp5mhIQAk9QBfBt5n+3lJtwMfAVzubwHeBTQa9/cE7S9ttDcCGwH6+vrc39/fbMxfMzQ0RKt926UbM0N35V697usArF0yyi3DR/8E9lzTP63bP9Z0bb+bXusxyTx7Wsnd1FlDkk6iVgQ+b/srALYP2j5i+1fAp4BlZfW9wMK67guA/aV9QYP2iIhoo2bOGhKwCXjc9sfq2ufVrfZW4NEyvRVYKelkSecCi4Fttg8AL0i6qGzzWuDuaXoeERHRomaGht4EvBMYlvRIafsL4O2SLqQ2vLMHeDeA7Z2StgCPUTvj6IZyxhDA9cAdwFxqxw1yxlBERJtNWghsf4fG4/vfmKDPemB9g/bt1A40R0REh8g3iyMiKi6FICKi4lIIIiIqLoUgIqLiUggiIiouhSAiouJSCCIiKi6FICKi4lIIIiIqLoUgIqLiUggiIiouhSAiouJSCCIiKi6FICKi4lIIIiIqLoUgIqLimrlU5UJJD0h6XNJOSe8t7WdKuk/SU+X+jLo+N0naJelJSZfVtS+VNFyW3VouWRkREW3UzB7BKLDW9huAi4AbJJ0HrAPut70YuL/MU5atBM4HlgOflDSnbOt2YA216xgvLssjIqKNJi0Etg/Y/n6ZfgF4HJgPrAA2l9U2A1eV6RXAoO3DtncDu4Bl5WL3p9p+yLaBO+v6REREm6j2mdzkytIi4EFq1x1+2vbpdcuesX2GpNuAh23fVdo3UbtI/R5gg+1LS/vFwI22r2zwOGuo7TnQ29u7dHBwsKUnNzIyQk9PT0t926UbM0N35R7e9xwAvXPh4ItH25fMP21at3+s6dp+N73WY5J59ozlHhgY2GG7r5k+k168foykHuDLwPtsPz/B8H6jBZ6g/aWN9kZgI0BfX5/7+/ubjflrhoaGaLVvu3RjZuiu3KvXfR2AtUtGuWX46J/Anmv6p3X7x5qu7XfTaz0mmWdPK7mbOmtI0knUisDnbX+lNB8swz2U+0OlfS+wsK77AmB/aV/QoD0iItqombOGBGwCHrf9sbpFW4FVZXoVcHdd+0pJJ0s6l9pB4W22DwAvSLqobPPauj4REdEmzQwNvQl4JzAs6ZHS9hfABmCLpOuAp4GrAWzvlLQFeIzaGUc32D5S+l0P3AHMpXbc4N7peRoREdGqSQuB7e/QeHwf4JJx+qwH1jdo307tQHNERHSIfLM4IqLimj5rKCJ+3aJxzg6K6DbZI4iIqLjsEUR0qPH2ONYuGaV/dqPECS57BBERFZdCEBFRcSkEEREVl0IQEVFxKQQRERWXQhARUXEpBBERFZdCEBFRcSkEEREVl0IQEVFxKQQRERWXQhARUXEpBBERFdfMNYs/I+mQpEfr2m6WtE/SI+V2ed2ymyTtkvSkpMvq2pdKGi7Lbi3XLY6IiDZrZo/gDmB5g/aP276w3L4BIOk8YCVwfunzSUlzyvq3A2uoXcx+8TjbjIiIWTZpIbD9IPCLJre3Ahi0fdj2bmAXsEzSPOBU2w/ZNnAncFWLmSMiYhqp9rk8yUrSIuAe2xeU+ZuB1cDzwHZgre1nJN0GPGz7rrLeJuBeYA+wwfalpf1i4EbbV47zeGuo7T3Q29u7dHBwsKUnNzIyQk9PT0t926UbM0N35R7e9xwAvXPh4ItH25fMP62l7TRrurbfOxdefebxbavduun9MaYbM8PR3AMDAzts9zXTp9UrlN0OfARwub8FeBfQaNzfE7Q3ZHsjsBGgr6/P/f39LYUcGhqi1b7t0o2Zobtyry5X/lq7ZJRbho/+Cey5pr+l7TRrura/dskob+uS13pMN70/xnRjZmgtd0tnDdk+aPuI7V8BnwKWlUV7gYV1qy4A9pf2BQ3aIyKizVraI5A0z/aBMvtWYOyMoq3AFyR9DDiH2kHhbbaPSHpB0kXAd4Frgf88tegRNeNd23fPhitmOUlEd5q0EEj6ItAPnCVpL/AhoF/ShdSGd/YA7wawvVPSFuAxYBS4wfaRsqnrqZ2BNJfacYN7p/F5RHS8FKzoVJMWAttvb9C8aYL11wPrG7RvBy44rnQRETHjWj1YHFEZ4/0nH3GiyE9MRERUXApBRETFpRBERFRcjhFEFDkWEFWVPYKIiIpLIYiIqLgUgoiIikshiIiouBSCiIiKSyGIiKi4nD4a0WY5bTXaLXsEEREVl0IQEVFxKQQRERWXQhARUXGTFgJJn5F0SNKjdW1nSrpP0lPl/oy6ZTdJ2iXpSUmX1bUvlTRclt0qqdEF7SMiYpY1s0dwB7D8mLZ1wP22FwP3l3kknQesBM4vfT4paU7pczuwhtp1jBc32GZERLTBpIXA9oPAL45pXgFsLtObgavq2gdtH7a9G9gFLJM0DzjV9kO2DdxZ1yciItpItc/lSVaSFgH32L6gzD9r+/S65c/YPkPSbcDDtu8q7ZuoXaR+D7DB9qWl/WLgRttXjvN4a6jtPdDb27t0cHCwpSc3MjJCT09PS33bpRszQ3tzD+97rmH7kvmnTbh+71w4+OKMxZoxvXPh1Wc2fm6dqhvf192YGY7mHhgY2GG7r5k+0/2Fskbj/p6gvSHbG4GNAH19fe7v728pzNDQEK32bZduzAztzb16nC9k7bmmf8L11y4Z5Zbh7vtO5dolo7yty94j3fi+7sbM0FruVs8aOliGeyj3h0r7XmBh3XoLgP2lfUGD9oiIaLNWC8FWYFWZXgXcXde+UtLJks6ldlB4m+0DwAuSLipnC11b1yciItpo0v1iSV8E+oGzJO0FPgRsALZIug54GrgawPZOSVuAx4BR4AbbR8qmrqd2BtJcascN7p3WZxIRES2ZtBDYfvs4iy4ZZ/31wPoG7duBC44rXUREzLh8szgiouJSCCIiKi6FICKi4lIIIiIqLoUgIqLiUggiIiouhSAiouK674dWIuK4LRrv95g2XDHLSaITZY8gIqLiUggiIiouhSAiouJSCCIiKi6FICKi4lIIIiIqLoUgIqLi8j2C6BrjnQtfRfleQEynKe0RSNojaVjSI5K2l7YzJd0n6alyf0bd+jdJ2iXpSUmXTTV8RERM3XTsEQzY/lnd/DrgftsbJK0r8zdKOg9YCZwPnAN8W9Lr6y5lGTGtsgcR0ZyZGBpaQe0axwCbgSHgxtI+aPswsFvSLmAZ8NAMZIgOl6GNiM4h2613lnYDzwAG/ovtjZKetX163TrP2D5D0m3Aw7bvKu2bgHttf6nBdtcAawB6e3uXDg4OtpRvZGSEnp6elvq2SzdmhuPPPbzvuYbtS+afdtx9WtU7Fw6+OK2bnBXTmXui13s6deP7uhszw9HcAwMDO2z3NdNnqnsEb7K9X9KrgfskPTHBumrQ1rAK2d4IbATo6+tzf39/S+GGhoZotW+7dGNmOP7cq8cbthn+5QS9pncHdu2SUW4Z7r7zJaYz955r+qdlO5Ppxvd1N2aG1nJP6WCx7f3l/hDwVWpDPQclzQMo94fK6nuBhXXdFwD7p/L4ERExdS0XAkmnSHrl2DTwZuBRYCuwqqy2Cri7TG8FVko6WdK5wGJgW6uPHxER02Mq+5e9wFcljW3nC7b/m6TvAVskXQc8DVwNYHunpC3AY8AocEPOGIqIaL+WC4Ht/wn8ToP2nwOXjNNnPbC+1ceMiPbK2V4npvzERERExaUQRERUXApBRETFpRBERFRcCkFERMWlEEREVFz3fb8+2mq80wfvWH7KLCeJ6ZDTQQNSCGKG5aegIzpfhoYiIiouhSAiouIyNBTTYnjfc+P/tHR0nQzpVUv2CCIiKi57BNFQ/iOMqI4UgoiYssn+cVi7ZLSpocOcttoeGRqKiKi4FIKIiIpLIYiIqLhZP0YgaTnwn4A5wKdtb5jtDFWUg79xIprofZ3jDc2b1UIgaQ7wCeAPgb3A9yRttf3YbOaIiM6U3z5qj9neI1gG7CrXO0bSILCC2gXtTxhT+e+72bMrIqqklb+pqe4FT/a3eCIVJ9mevQeT/hhYbvtPy/w7gd+z/Z5j1lsDrCmz/xh4ssWHPAv4WYt926UbM0N35u7GzNCduZN59ozlfo3ts5vpMNt7BGrQ9pJKZHsjsHHKDyZtt9031e3Mpm7MDN2ZuxszQ3fmTubZ00ru2T5raC+wsG5+AbB/ljNERESd2S4E3wMWSzpX0m8AK4Gts5whIiLqzOrQkO1RSe8Bvknt9NHP2N45gw855eGlNujGzNCdubsxM3Rn7mSePcede1YPFkdEROfJN4sjIiouhSAiouJOyEIg6SOSfiTpEUnfknRO3bKbJO2S9KSky9qZs56kv5L0RMn9VUmn1y3r1MxXS9op6VeS+o5Z1pGZx0haXrLtkrSu3XkakfQZSYckPVrXdqak+yQ9Ve7PaGfGY0laKOkBSY+X98Z7S3un536FpG2Sflhyf7i0d3RuqP1ig6QfSLqnzB9/Ztsn3A04tW76z4C/KdPnAT8ETgbOBX4MzGl33pLtzcDLy/RHgY92QeY3UPvC3xDQV9fesZlLvjkl02uB3yhZz2t3rgY5/xnwu8CjdW3/AVhXpteNvU865QbMA363TL8S+Pvyfuj03AJ6yvRJwHeBizo9d8n158AXgHtafY+ckHsEtp+vmz2Fo19aWwEM2j5sezewi9rPXrSd7W/ZHi2zD1P7jgV0dubHbTf61nfHZi7+4adObP9fYOynTjqK7QeBXxzTvALYXKY3A1fNZqbJ2D5g+/tl+gXgcWA+nZ/btkfK7EnlZjo8t6QFwBXAp+uajzvzCVkIACStl/QT4Brgg6V5PvCTutX2lrZO8y7g3jLdLZnrdXrmTs83kV7bB6D2oQu8us15xiVpEfBGav9dd3zuMsTyCHAIuM92N+T+j8D7gV/VtR135q4tBJK+LenRBrcVALY/YHsh8Hlg7LeMmvqJi3ZlLut8ABillhu6IHOjbg3aOuk85U7P1/Uk9QBfBt53zB56x7J9xPaF1PbGl0m6oM2RJiTpSuCQ7R1T3VbXXrPY9qVNrvoF4OvAh2jzT1xMllnSKuBK4BKXAT46PPM4Ov2nRDo930QOSppn+4CkedT+e+0okk6iVgQ+b/srpbnjc4+x/aykIWA5nZ37TcBbJF0OvAI4VdJdtJC5a/cIJiJpcd3sW4AnyvRWYKWkkyWdCywGts12vkZUu2DPjcBbbP+fukUdm3kCnZ65m3/qZCuwqkyvAu5uY5aXkCRgE/C47Y/VLer03GePnaknaS5wKbXPjY7Nbfsm2wtsL6L2Hv472++glcztPuI9Q0fRvww8CvwI+Bowv27ZB6idMfIk8EftzlqXaxe1cetHyu1vuiDzW6n9d30YOAh8s9Mz1+W7nNoZLT8GPtDuPONk/CJwAPh/5XW+DngVcD/wVLk/s905j8n8+9SG2X5U916+vAty/zbwg5L7UeCDpb2jc9fl7+foWUPHnTk/MRERUXEn5NBQREQ0L4UgIqLiUggiIiouhSAiouJSCCIiKi6FICKi4lIIIiIq7v8D8QBSbK8PA8wAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Now, lets run a histogram\n",
    "full_table.hist(column='time_diff',bins = 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6de0d237",
   "metadata": {},
   "source": [
    "#### Conclusions\n",
    "Objective 1: Males typically earned more then females up until the 2010s. This may have reversed during Corona.\n",
    "Objective 2: Median time delta is five years, with a second peak at year zero. Given that Ph.D. students are employees in Europe, the former possibly corresponds to them wrapping up post doc applications. As for the latter, this is most probably new faculty arriving with a grant having been already rewarded commensurate with employment. "
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}