pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7fbda0e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: geopandas in /home/travisdawson/anaconda3/lib/python3.9/site-packages (0.12.2)\n",
      "Requirement already satisfied: shapely>=1.7 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from geopandas) (2.0.1)\n",
      "Requirement already satisfied: pandas>=1.0.0 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from geopandas) (1.4.4)\n",
      "Requirement already satisfied: packaging in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from geopandas) (21.3)\n",
      "Requirement already satisfied: pyproj>=2.6.1.post1 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from geopandas) (3.4.1)\n",
      "Requirement already satisfied: fiona>=1.8 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from geopandas) (1.9.1)\n",
      "Requirement already satisfied: setuptools in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from fiona>=1.8->geopandas) (63.4.1)\n",
      "Requirement already satisfied: click~=8.0 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from fiona>=1.8->geopandas) (8.0.4)\n",
      "Requirement already satisfied: attrs>=19.2.0 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from fiona>=1.8->geopandas) (21.4.0)\n",
      "Requirement already satisfied: munch>=2.3.2 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from fiona>=1.8->geopandas) (2.5.0)\n",
      "Requirement already satisfied: click-plugins>=1.0 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from fiona>=1.8->geopandas) (1.1.1)\n",
      "Requirement already satisfied: cligj>=0.5 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from fiona>=1.8->geopandas) (0.7.2)\n",
      "Requirement already satisfied: certifi in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from fiona>=1.8->geopandas) (2022.9.14)\n",
      "Requirement already satisfied: python-dateutil>=2.8.1 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from pandas>=1.0.0->geopandas) (2.8.2)\n",
      "Requirement already satisfied: pytz>=2020.1 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from pandas>=1.0.0->geopandas) (2022.1)\n",
      "Requirement already satisfied: numpy>=1.18.5 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from pandas>=1.0.0->geopandas) (1.21.5)\n",
      "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from packaging->geopandas) (3.0.9)\n",
      "Requirement already satisfied: six in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from munch>=2.3.2->fiona>=1.8->geopandas) (1.16.0)\n",
      "Requirement already satisfied: geopy in /home/travisdawson/anaconda3/lib/python3.9/site-packages (2.3.0)\n",
      "Requirement already satisfied: geographiclib<3,>=1.52 in /home/travisdawson/anaconda3/lib/python3.9/site-packages (from geopy) (2.0)\n",
      "Requirement already satisfied: CurrencyConverter in /home/travisdawson/anaconda3/lib/python3.9/site-packages (0.17.5)\n"
     ]
    }
   ],
   "source": [
    "!pip3 install geopandas\n",
    "!pip3 install geopy\n",
    "!pip3 install CurrencyConverter "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "50eb2845",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Imports\n",
    "\n",
    "# Geo Tools\n",
    "import geopandas as gpd\n",
    "from geopy.geocoders import Nominatim\n",
    "from geopy.extra.rate_limiter import RateLimiter\n",
    "\n",
    "# NLP\n",
    "import re\n",
    "from functools import partial\n",
    "from transformers import pipeline\n",
    "from currency_converter import CurrencyConverter\n",
    "\n",
    "# Data Manipulation & Visualisation\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import statistics\n",
    "import os\n",
    "\n",
    "# Own modules\n",
    "from dataExtraction import extract"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "becd178c",
   "metadata": {},
   "source": [
    "## Import Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "84d42a76",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11311, 8)\n"
     ]
    },
    {
     "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>title</th>\n",
       "      <th>company_name</th>\n",
       "      <th>location</th>\n",
       "      <th>via</th>\n",
       "      <th>description</th>\n",
       "      <th>job_highlights</th>\n",
       "      <th>detected_extensions</th>\n",
       "      <th>job_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Junior Data Scientist</td>\n",
       "      <td>ING</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>ING Careers</td>\n",
       "      <td>As the data driven mindset is more and more em...</td>\n",
       "      <td>['As the data driven mindset is more and more ...</td>\n",
       "      <td>{'posted_at': '6 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJKdW5pb3IgRGF0YSBTY2llbnRpc3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JUNIOR DATA SCIENTIST - Dubai, UAE</td>\n",
       "      <td>Cobblestone Energy</td>\n",
       "      <td>Utrecht</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Location: Dubai, UAE (We provide visa sponsors...</td>\n",
       "      <td>[\"Location: Dubai, UAE (We provide visa sponso...</td>\n",
       "      <td>{'posted_at': '4 hours ago', 'schedule_type': ...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJKVU5JT1IgREFUQSBTQ0lFTlRJU1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Data Scientist Mobiliteit</td>\n",
       "      <td>TNO</td>\n",
       "      <td>The Hague</td>\n",
       "      <td>TNO</td>\n",
       "      <td>Halen we in Nederland de klimaatdoelen op het ...</td>\n",
       "      <td>['Halen we in Nederland de klimaatdoelen op he...</td>\n",
       "      <td>{'posted_at': '5 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBNb2JpbG...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Data Scientist Real Estate for Catella Investm...</td>\n",
       "      <td>Catella Investment Management Benelux</td>\n",
       "      <td>Maastricht</td>\n",
       "      <td>Limburgvac</td>\n",
       "      <td>As a Data Scientist in the Research &amp; Investme...</td>\n",
       "      <td>['As a Data Scientist in the Research &amp; Invest...</td>\n",
       "      <td>{'posted_at': '20 hours ago', 'schedule_type':...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBSZWFsIE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Effectory</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Effectory Jobs</td>\n",
       "      <td>Improving the working lives of millions of peo...</td>\n",
       "      <td>['Improving the working lives of millions of p...</td>\n",
       "      <td>{'schedule_type': 'Full–time'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Adyen</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Nationale Vacaturebank</td>\n",
       "      <td>Functieomschrijving Data Analytics Amsterdam T...</td>\n",
       "      <td>[\"Functieomschrijving\\n\\nData Analytics Amster...</td>\n",
       "      <td>{'posted_at': '17 hours ago', 'schedule_type':...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Data Scientist bij Transavia</td>\n",
       "      <td>Transavia</td>\n",
       "      <td>Schiphol</td>\n",
       "      <td>Vacatures - Transa</td>\n",
       "      <td>Wij zoeken jou als Data Scientist Voor ons Str...</td>\n",
       "      <td>['Wij zoeken jou als Data Scientist\\n\\nVoor on...</td>\n",
       "      <td>{'schedule_type': 'Full–time'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBiaWogVH...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Data Science Lead - Amsterdam</td>\n",
       "      <td>Bynder</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Careers At Bynder</td>\n",
       "      <td>Bynder goes far beyond managing digital assets...</td>\n",
       "      <td>['Bynder goes far beyond managing digital asse...</td>\n",
       "      <td>{'posted_at': '2 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgTGVhZCAtIE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LEAD DATA SCIENTIST - Dubai, UAE</td>\n",
       "      <td>Cobblestone Energy</td>\n",
       "      <td>Rotterdam</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Employment type: Full-time &amp; Permanent Reports...</td>\n",
       "      <td>[\"Employment type: Full-time &amp; Permanent\\n\\nRe...</td>\n",
       "      <td>{'posted_at': '1 day ago', 'schedule_type': 'F...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJMRUFEIERBVEEgU0NJRU5USVNUIC...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Data Science and Artificial Intelligence Fello...</td>\n",
       "      <td>Wageningen University &amp; Research</td>\n",
       "      <td>Wageningen</td>\n",
       "      <td>AcademicTransfer</td>\n",
       "      <td>Are you a computer scientist with a PhD degree...</td>\n",
       "      <td>['Are you a computer scientist with a PhD degr...</td>\n",
       "      <td>{'posted_at': '2 days ago'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgYW5kIEFydG...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title  \\\n",
       "0                              Junior Data Scientist   \n",
       "1                 JUNIOR DATA SCIENTIST - Dubai, UAE   \n",
       "2                          Data Scientist Mobiliteit   \n",
       "3  Data Scientist Real Estate for Catella Investm...   \n",
       "4                                     Data Scientist   \n",
       "5                                     Data Scientist   \n",
       "6                       Data Scientist bij Transavia   \n",
       "7                      Data Science Lead - Amsterdam   \n",
       "8                   LEAD DATA SCIENTIST - Dubai, UAE   \n",
       "9  Data Science and Artificial Intelligence Fello...   \n",
       "\n",
       "                            company_name    location                      via  \\\n",
       "0                                    ING   Amsterdam              ING Careers   \n",
       "1                     Cobblestone Energy     Utrecht                 LinkedIn   \n",
       "2                                    TNO   The Hague                      TNO   \n",
       "3  Catella Investment Management Benelux  Maastricht               Limburgvac   \n",
       "4                              Effectory   Amsterdam           Effectory Jobs   \n",
       "5                                  Adyen   Amsterdam   Nationale Vacaturebank   \n",
       "6                              Transavia    Schiphol       Vacatures - Transa   \n",
       "7                                 Bynder   Amsterdam        Careers At Bynder   \n",
       "8                     Cobblestone Energy   Rotterdam                 LinkedIn   \n",
       "9       Wageningen University & Research  Wageningen         AcademicTransfer   \n",
       "\n",
       "                                         description  \\\n",
       "0  As the data driven mindset is more and more em...   \n",
       "1  Location: Dubai, UAE (We provide visa sponsors...   \n",
       "2  Halen we in Nederland de klimaatdoelen op het ...   \n",
       "3  As a Data Scientist in the Research & Investme...   \n",
       "4  Improving the working lives of millions of peo...   \n",
       "5  Functieomschrijving Data Analytics Amsterdam T...   \n",
       "6  Wij zoeken jou als Data Scientist Voor ons Str...   \n",
       "7  Bynder goes far beyond managing digital assets...   \n",
       "8  Employment type: Full-time & Permanent Reports...   \n",
       "9  Are you a computer scientist with a PhD degree...   \n",
       "\n",
       "                                      job_highlights  \\\n",
       "0  ['As the data driven mindset is more and more ...   \n",
       "1  [\"Location: Dubai, UAE (We provide visa sponso...   \n",
       "2  ['Halen we in Nederland de klimaatdoelen op he...   \n",
       "3  ['As a Data Scientist in the Research & Invest...   \n",
       "4  ['Improving the working lives of millions of p...   \n",
       "5  [\"Functieomschrijving\\n\\nData Analytics Amster...   \n",
       "6  ['Wij zoeken jou als Data Scientist\\n\\nVoor on...   \n",
       "7  ['Bynder goes far beyond managing digital asse...   \n",
       "8  [\"Employment type: Full-time & Permanent\\n\\nRe...   \n",
       "9  ['Are you a computer scientist with a PhD degr...   \n",
       "\n",
       "                                 detected_extensions  \\\n",
       "0  {'posted_at': '6 days ago', 'schedule_type': '...   \n",
       "1  {'posted_at': '4 hours ago', 'schedule_type': ...   \n",
       "2  {'posted_at': '5 days ago', 'schedule_type': '...   \n",
       "3  {'posted_at': '20 hours ago', 'schedule_type':...   \n",
       "4                     {'schedule_type': 'Full–time'}   \n",
       "5  {'posted_at': '17 hours ago', 'schedule_type':...   \n",
       "6                     {'schedule_type': 'Full–time'}   \n",
       "7  {'posted_at': '2 days ago', 'schedule_type': '...   \n",
       "8  {'posted_at': '1 day ago', 'schedule_type': 'F...   \n",
       "9                        {'posted_at': '2 days ago'}   \n",
       "\n",
       "                                              job_id  \n",
       "0  eyJqb2JfdGl0bGUiOiJKdW5pb3IgRGF0YSBTY2llbnRpc3...  \n",
       "1  eyJqb2JfdGl0bGUiOiJKVU5JT1IgREFUQSBTQ0lFTlRJU1...  \n",
       "2  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBNb2JpbG...  \n",
       "3  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBSZWFsIE...  \n",
       "4  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...  \n",
       "5  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...  \n",
       "6  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBiaWogVH...  \n",
       "7  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgTGVhZCAtIE...  \n",
       "8  eyJqb2JfdGl0bGUiOiJMRUFEIERBVEEgU0NJRU5USVNUIC...  \n",
       "9  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgYW5kIEFydG...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data.csv')\n",
    "print(df.shape)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4128aa8d",
   "metadata": {},
   "source": [
    "Basic string clean-up, by removing new line characters, unneccessary quotation marks, and brackets that may effect further processing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d270b7a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['job_highlights'] = df['job_highlights'].replace(r'\\n\\n|\\n•|\\n|\\\\n|\\\\n•|•', '', regex=True)  # Remove new line char\n",
    "df['job_highlights'] = df['job_highlights'].str.slice(2, -2)  # Remove [] and additional \"\" marks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c6bcb8f",
   "metadata": {},
   "source": [
    "## Salary Extraction\n",
    "\n",
    "The resulting dataset, does not have a specified field for salary information, as this is not legally required information globally.\n",
    "\n",
    "Salary information, can be found spread amongst three columns: *title*, *description*, and *highlights*. \n",
    "\n",
    "There exist three possibilities for each listing, based on the salary informations search space:\n",
    "\n",
    "1. The job listing will contain no salary information.\n",
    "2. The job listing will contain an exact salary specification.\n",
    "3. The job listing will contain a salary range. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50059d5b",
   "metadata": {},
   "source": [
    "**Step 1: NLP salary extraction** \\\n",
    "Due to the unstructured data format, Natural Language Processing is the optimal tool to perform salary extraction from the defined text search space. \n",
    "\n",
    "[BERT](https://huggingface.co/docs/transformers/model_doc/bert) (Bidirectional Encoder Representations from Transformers) is a pre-trained Natural Language model trained on Masked Lanuage Modelling (predict missing words based on context of the surrounding words) and Next Sentence Prediction (NSP). BERT offers a [Question-Answer model](https://huggingface.co/distilbert-base-cased-distilled-squad#technical-specifications), whereby an answer to a posed question is extracted from provided text. This was utilized for the task of salary extraction, due to its contextual capabilities.\n",
    "\n",
    "The Question posed is \"What is the salary or salary-range for the job\". The provided text from which to extrapolate the data was the combined three columns. The returned answer is accepted if a score of 70% accuracy is provided. If no salary is determined, the salary is determined \"Not available\".\n",
    "\n",
    "**Note: The below cell takes approximately 2 hours to run to completion. Instead read in the already extracted salary results (cell as the start of *Country Extraction*)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e575f29b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_salary():\n",
    "    qa_model = pipeline(\"question-answering\", model='distilbert-base-cased-distilled-squad') # Define the model \n",
    "    question = \"What is the salary or salary range for the job?\"  # Define the quesiton to be answered\n",
    "    # Map question-answer model accross dataframe\n",
    "    df['salary'] = df.apply(lambda x: \"Not available\" if qa_model(question=question, \n",
    "                                                                           context=(x['job_highlights'] + \n",
    "                                                                                    x['title'] + \n",
    "                                                                                    x['description']))['score'] < 0.3\n",
    "                                                      else qa_model(question=question, \n",
    "                                                                              context=(x['job_highlights'] + \n",
    "                                                                                       x['title'] + \n",
    "                                                                                       x['description']))['answer'], axis=1)\n",
    "    return df\n",
    "    \n",
    "df = extract_salary()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd7daf4b",
   "metadata": {},
   "source": [
    "Remove any dots or commas from the extracted salary string. \n",
    "\n",
    "**Note:** The returned salary, is still within an unstructured string format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e7e56f21",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['salary'] = df['salary'].str.replace(',', '', regex=True)  # Replace comma point in numerical values\n",
    "df['salary'] = df['salary'].str.replace('.', '', regex=True)  # Replace dot point in numerical values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6eead8f9",
   "metadata": {},
   "source": [
    "**Step 2: Isolate salary or generate from range**\n",
    "For each extracted salary from the listing, the previously discussed three possibilities are handled. \n",
    "\n",
    "1. No salary information is provided. No further steps for this listing.\n",
    "2. The exactly numerical value is detected (single numerical value) through regex matching, and extracted into the *salary* column.\n",
    "3. A salary range is detected (two numerical values) through regex matching. The salary range is extracted into the *salary_range* column, whereas the median of the upper and lower bound is calculated and placed  in the *salary* column as an approximation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "74fa4b04",
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_specified_salary(x):    \n",
    "    values = re.findall(r'\\d+', str(x))  # Identify numerical values\n",
    "    salary = 'Not available'\n",
    "    salary_range = 'Not available'\n",
    "    if len(values) == 1:  # Single numerical value, not a range\n",
    "        salary = int(values[0]) \n",
    "        \n",
    "    elif len(values) == 2:  # Two numerical values indicate a range\n",
    "        min_salary = int(values[0])  # Min of salary range\n",
    "        max_salary = int(values[1])  # Max of salary range\n",
    "        salary = statistics.median([min_salary, max_salary])  # Calculate range median\n",
    "        salary_range = str(min_salary) + \"-\" + str(max_salary)  # Format range\n",
    "        \n",
    "    return pd.Series([salary, salary_range])\n",
    "\n",
    "\n",
    "df[['salary', 'salary_range']] = df['salary'].apply(find_specified_salary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d4ffa985",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data_cache/data_salaries.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4672460c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of salaries extracted:  2157\n"
     ]
    }
   ],
   "source": [
    "test = df[df.salary != 'Not available']\n",
    "print(\"Number of salaries extracted: \", len(test))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "630e440a",
   "metadata": {},
   "source": [
    "## Country Extraction\n",
    "Each job listing is accompanied by a specified location, in the form of the city in which the company is listed.\n",
    "\n",
    "Geographic information is an essential data feature, allowing for the undertsnading of the global distribution of scraped jobs, a stable indication of currency, and coordinates.  \n",
    "\n",
    "A geo-location library 'GeoPy' was utilized in order to generate API calls to Nominatim (Backend of www.openstreetmap.com), in order to geolocate the provided location, returning country, city, and coordinates. \n",
    "\n",
    "Nominatim enforces a API limit, of approximately 2500 requests per day. The accumulated datset size is approximately 8000 job listings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "25fe8a2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data_cache/data_salaries.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "26b1c8e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "United States             622\n",
       "New York, NY, USA         561\n",
       "San Francisco, CA, USA    317\n",
       "Anywhere                  249\n",
       "Chicago, IL, USA          224\n",
       "                         ... \n",
       "Würzburg                    1\n",
       "Hagen                       1\n",
       "Jüchen                      1\n",
       "Künzelsau                   1\n",
       "Titusville, PA, USA         1\n",
       "Name: location, Length: 928, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['location'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "129e2dd0",
   "metadata": {},
   "source": [
    "There exist approximately 920 unique locations. \n",
    "\n",
    "Due the high quantity of location repetition, caching can be utilized to store a location's features once collected, allowing for re-use. This reduces the API requests to the number of unique locations, a substantial reduction. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "915caa06",
   "metadata": {},
   "source": [
    "### Location Cache\n",
    "The below code generates a cache based on the number of unique locations within the scraped job listings. \n",
    "\n",
    "**Note: This should only be run of additional jobs are scraped. The GeoPy API has a daily rate limit of 2500 requests per day.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "29e2afe9",
   "metadata": {},
   "outputs": [],
   "source": [
    "locations = df.location.unique()  # Determine unique locations\n",
    "locations_df = pd.DataFrame(data=locations, columns=['location'])  # Unique locations dataframe\n",
    "\n",
    "geolocator = Nominatim(user_agent=\"data_madness\")  # Initialize the geolocator to use Nominatim\n",
    "geocode = RateLimiter(geolocator.geocode, min_delay_seconds=1)  # Enforce a request delay to respect API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6a160032",
   "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>location</th>\n",
       "      <th>address</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Amsterdam, North Holland, Netherlands</td>\n",
       "      <td>52.37308</td>\n",
       "      <td>4.892453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Utrecht</td>\n",
       "      <td>Utrecht, Netherlands</td>\n",
       "      <td>52.080986</td>\n",
       "      <td>5.127684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Hague</td>\n",
       "      <td>The Hague, South Holland, Netherlands</td>\n",
       "      <td>52.079984</td>\n",
       "      <td>4.311346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Maastricht</td>\n",
       "      <td>Maastricht, Limburg, Netherlands</td>\n",
       "      <td>50.857985</td>\n",
       "      <td>5.696988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Schiphol</td>\n",
       "      <td>Schiphol, Haarlemmermeer, North Holland, Nethe...</td>\n",
       "      <td>52.308039</td>\n",
       "      <td>4.762198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Rotterdam</td>\n",
       "      <td>Rotterdam, South Holland, Netherlands</td>\n",
       "      <td>51.924442</td>\n",
       "      <td>4.47775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Wageningen</td>\n",
       "      <td>Wageningen, Gelderland, Netherlands</td>\n",
       "      <td>51.968582</td>\n",
       "      <td>5.668298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>Groningen, Netherlands</td>\n",
       "      <td>53.219065</td>\n",
       "      <td>6.568008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>52.243498</td>\n",
       "      <td>5.634323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Dordrecht</td>\n",
       "      <td>Dordrecht, South Holland, Netherlands</td>\n",
       "      <td>51.795881</td>\n",
       "      <td>4.677935</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      location                                            address   latitude  \\\n",
       "0    Amsterdam              Amsterdam, North Holland, Netherlands   52.37308   \n",
       "1      Utrecht                               Utrecht, Netherlands  52.080986   \n",
       "2    The Hague              The Hague, South Holland, Netherlands  52.079984   \n",
       "3   Maastricht                   Maastricht, Limburg, Netherlands  50.857985   \n",
       "4     Schiphol  Schiphol, Haarlemmermeer, North Holland, Nethe...  52.308039   \n",
       "5    Rotterdam              Rotterdam, South Holland, Netherlands  51.924442   \n",
       "6   Wageningen                Wageningen, Gelderland, Netherlands  51.968582   \n",
       "7    Groningen                             Groningen, Netherlands  53.219065   \n",
       "8  Netherlands                                        Netherlands  52.243498   \n",
       "9    Dordrecht              Dordrecht, South Holland, Netherlands  51.795881   \n",
       "\n",
       "  longitude  \n",
       "0  4.892453  \n",
       "1  5.127684  \n",
       "2  4.311346  \n",
       "3  5.696988  \n",
       "4  4.762198  \n",
       "5   4.47775  \n",
       "6  5.668298  \n",
       "7  6.568008  \n",
       "8  5.634323  \n",
       "9  4.677935  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def locator(x):\n",
    "    if x['location'] == 'Anywhere':   # Job location listed as \"Anywhere\"\n",
    "        x['address'] = \"Not available\"\n",
    "        x['latitude'] = \"Not available\"\n",
    "        x['longitude'] = \"Not available\"\n",
    "        return x\n",
    "        \n",
    "    location = geolocator.geocode(x['location'], # Perform Geolocation search\n",
    "                                  language='en', \n",
    "                                  exactly_one=True)  \n",
    "    if location is not None:  # A location was found.\n",
    "        x['address'] = location.address\n",
    "        x['latitude'] = location.latitude\n",
    "        x['longitude'] = location.longitude\n",
    "    else:  # No location was found\n",
    "        x['address'] = \"Not available\"\n",
    "        x['latitude'] = \"Not available\"\n",
    "        x['longitude'] = \"Not available\"\n",
    "    return x\n",
    "\n",
    "locations_df = locations_df.apply(locator, axis=1)\n",
    "locations_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "5ddfeda6",
   "metadata": {},
   "outputs": [],
   "source": [
    "locations_df.to_csv('data_cache/locations_cache.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e033fbf",
   "metadata": {},
   "source": [
    "### Country Extraction\n",
    "Extract the country, latitude, and longitude from the locations cache, and apply it to the dataframe in order to generate the required information for each listing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "46b73025",
   "metadata": {},
   "outputs": [],
   "source": [
    "locations_df = pd.read_csv('data_cache/locations_cache.csv')  # Read in location cache from file\n",
    "locations_df.set_index('location', inplace=True)  # Set location as index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d5ad9778",
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = ['United Kingdom', 'France', 'Italy', 'Spain', 'Belgium',\n",
    "            'Netherlands', 'Germany', 'Denmark', 'Switzerland', 'Austria',\n",
    "            'Czech Republic', 'Canada', 'United States', 'South Africa',\n",
    "            'Singapore', 'United Arab Emirates', 'Brazil', 'India', \n",
    "            'Portugal', 'Kenya', 'Argentina', 'Egypt', 'Suriname',\n",
    "             'Mali', 'Malawi', 'Afghanistan', 'Philippines', \n",
    "             'Serbia', 'Not available']  # Country search space based on scraping locations\n",
    "\n",
    "\n",
    "def find_country(address):  # The geopy API call returns a location string. Extract country from string\n",
    "    regex_pattern = '|'.join([f'{country}' for country in countries])  # Create regex pattern of countries\n",
    "    country = re.search(regex_pattern, address)  # Identify country in string\n",
    "    if country == None: # New country determined. Print result so it can be added to countries\n",
    "        print(address)\n",
    "    \n",
    "    return country.group()  # Retrun country name\n",
    "\n",
    "def identify_country(x):  # Method extracts all geo-information from cache\n",
    "    location = x['location']\n",
    "    if location == 'Anywhere': # Take into account remote work\n",
    "        x['latitude'] = 'Not available'\n",
    "        x['longitude'] = 'Not available'\n",
    "        x['country'] = 'Anywhere'\n",
    "        return x\n",
    "    \n",
    "    x['latitude'] = locations_df.loc[location, 'latitude']\n",
    "    x['longitude'] = locations_df.loc[location, 'longitude']\n",
    "    x['country'] = find_country(locations_df.loc[location, 'address'])\n",
    "    return x\n",
    "\n",
    "df = df.apply(identify_country, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "05c04ef8",
   "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>Unnamed: 0</th>\n",
       "      <th>title</th>\n",
       "      <th>company_name</th>\n",
       "      <th>location</th>\n",
       "      <th>via</th>\n",
       "      <th>description</th>\n",
       "      <th>job_highlights</th>\n",
       "      <th>detected_extensions</th>\n",
       "      <th>job_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>salary_range</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Junior Data Scientist</td>\n",
       "      <td>ING</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>ING Careers</td>\n",
       "      <td>As the data driven mindset is more and more em...</td>\n",
       "      <td>As the data driven mindset is more and more em...</td>\n",
       "      <td>{'posted_at': '6 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJKdW5pb3IgRGF0YSBTY2llbnRpc3...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>Not available</td>\n",
       "      <td>52.3730796</td>\n",
       "      <td>4.8924534</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>JUNIOR DATA SCIENTIST - Dubai, UAE</td>\n",
       "      <td>Cobblestone Energy</td>\n",
       "      <td>Utrecht</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Location: Dubai, UAE (We provide visa sponsors...</td>\n",
       "      <td>Location: Dubai, UAE (We provide visa sponsors...</td>\n",
       "      <td>{'posted_at': '4 hours ago', 'schedule_type': ...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJKVU5JT1IgREFUQSBTQ0lFTlRJU1...</td>\n",
       "      <td>60000</td>\n",
       "      <td>Not available</td>\n",
       "      <td>52.080985600000005</td>\n",
       "      <td>5.12768396945229</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Data Scientist Mobiliteit</td>\n",
       "      <td>TNO</td>\n",
       "      <td>The Hague</td>\n",
       "      <td>TNO</td>\n",
       "      <td>Halen we in Nederland de klimaatdoelen op het ...</td>\n",
       "      <td>Halen we in Nederland de klimaatdoelen op het ...</td>\n",
       "      <td>{'posted_at': '5 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBNb2JpbG...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>Not available</td>\n",
       "      <td>52.0799838</td>\n",
       "      <td>4.3113461</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Data Scientist Real Estate for Catella Investm...</td>\n",
       "      <td>Catella Investment Management Benelux</td>\n",
       "      <td>Maastricht</td>\n",
       "      <td>Limburgvac</td>\n",
       "      <td>As a Data Scientist in the Research &amp; Investme...</td>\n",
       "      <td>As a Data Scientist in the Research &amp; Investme...</td>\n",
       "      <td>{'posted_at': '20 hours ago', 'schedule_type':...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBSZWFsIE...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>Not available</td>\n",
       "      <td>50.85798545</td>\n",
       "      <td>5.6969881818221095</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Effectory</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Effectory Jobs</td>\n",
       "      <td>Improving the working lives of millions of peo...</td>\n",
       "      <td>Improving the working lives of millions of peo...</td>\n",
       "      <td>{'schedule_type': 'Full–time'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...</td>\n",
       "      <td>68750.0</td>\n",
       "      <td>47500-90000</td>\n",
       "      <td>52.3730796</td>\n",
       "      <td>4.8924534</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                                              title  \\\n",
       "0           0                              Junior Data Scientist   \n",
       "1           1                 JUNIOR DATA SCIENTIST - Dubai, UAE   \n",
       "2           2                          Data Scientist Mobiliteit   \n",
       "3           3  Data Scientist Real Estate for Catella Investm...   \n",
       "4           4                                     Data Scientist   \n",
       "\n",
       "                            company_name    location              via  \\\n",
       "0                                    ING   Amsterdam      ING Careers   \n",
       "1                     Cobblestone Energy     Utrecht         LinkedIn   \n",
       "2                                    TNO   The Hague              TNO   \n",
       "3  Catella Investment Management Benelux  Maastricht       Limburgvac   \n",
       "4                              Effectory   Amsterdam   Effectory Jobs   \n",
       "\n",
       "                                         description  \\\n",
       "0  As the data driven mindset is more and more em...   \n",
       "1  Location: Dubai, UAE (We provide visa sponsors...   \n",
       "2  Halen we in Nederland de klimaatdoelen op het ...   \n",
       "3  As a Data Scientist in the Research & Investme...   \n",
       "4  Improving the working lives of millions of peo...   \n",
       "\n",
       "                                      job_highlights  \\\n",
       "0  As the data driven mindset is more and more em...   \n",
       "1  Location: Dubai, UAE (We provide visa sponsors...   \n",
       "2  Halen we in Nederland de klimaatdoelen op het ...   \n",
       "3  As a Data Scientist in the Research & Investme...   \n",
       "4  Improving the working lives of millions of peo...   \n",
       "\n",
       "                                 detected_extensions  \\\n",
       "0  {'posted_at': '6 days ago', 'schedule_type': '...   \n",
       "1  {'posted_at': '4 hours ago', 'schedule_type': ...   \n",
       "2  {'posted_at': '5 days ago', 'schedule_type': '...   \n",
       "3  {'posted_at': '20 hours ago', 'schedule_type':...   \n",
       "4                     {'schedule_type': 'Full–time'}   \n",
       "\n",
       "                                              job_id         salary  \\\n",
       "0  eyJqb2JfdGl0bGUiOiJKdW5pb3IgRGF0YSBTY2llbnRpc3...  Not available   \n",
       "1  eyJqb2JfdGl0bGUiOiJKVU5JT1IgREFUQSBTQ0lFTlRJU1...          60000   \n",
       "2  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBNb2JpbG...  Not available   \n",
       "3  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBSZWFsIE...  Not available   \n",
       "4  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...        68750.0   \n",
       "\n",
       "    salary_range            latitude           longitude      country  \n",
       "0  Not available          52.3730796           4.8924534  Netherlands  \n",
       "1  Not available  52.080985600000005    5.12768396945229  Netherlands  \n",
       "2  Not available          52.0799838           4.3113461  Netherlands  \n",
       "3  Not available         50.85798545  5.6969881818221095  Netherlands  \n",
       "4    47500-90000          52.3730796           4.8924534  Netherlands  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b33fb8ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "United States     8536\n",
       "Netherlands        437\n",
       "Germany            302\n",
       "Italy              277\n",
       "France             254\n",
       "Spain              250\n",
       "Anywhere           249\n",
       "United Kingdom     230\n",
       "Belgium            198\n",
       "Switzerland        151\n",
       "Austria            150\n",
       "Not available      117\n",
       "Denmark            109\n",
       "South Africa        19\n",
       "India               10\n",
       "Philippines          7\n",
       "Afghanistan          5\n",
       "Suriname             3\n",
       "Argentina            3\n",
       "Serbia               2\n",
       "Mali                 1\n",
       "Portugal             1\n",
       "Name: country, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fae7731f",
   "metadata": {},
   "source": [
    "There exists a total of 22 countries in which job postings are positioned, with the majority occuring in the United States. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d1a7ab4",
   "metadata": {},
   "source": [
    "## Currency Extraction\n",
    "Extract the currency based on the extracted country, based on the below dictionary of ISO currencies. Libraries did not offer an expediant solution, hence the hard-coding of values below. Note the ISO format is chosen to enable currency conversion at a later date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7a18e1ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_currency_mapping = {  # Mapping of country to currency (ISO format)\n",
    "    'United Kingdom': 'GBP', \n",
    "    'France': 'EUR',\n",
    "    'Italy': 'EUR', \n",
    "    'Spain': 'EUR', \n",
    "    'Belgium': 'EUR',\n",
    "    'Netherlands': 'EUR', \n",
    "    'Germany': 'EUR', \n",
    "    'Denmark': 'DKK', \n",
    "    'Switzerland': 'CHF', \n",
    "    'Austria': 'EUR',\n",
    "    'Czech Republic': 'CZK', \n",
    "    'Canada': 'CAD', \n",
    "    'United States': 'USD', \n",
    "    'South Africa': 'ZAR',\n",
    "    'Singapore': 'SGD', \n",
    "    'United Arab Emirates': 'AED', \n",
    "    'Brazil': 'BRL', \n",
    "    'India': 'INR', \n",
    "    'Portugal': 'EUR', \n",
    "    'Kenya': 'KES', \n",
    "    'Argentina': 'ARS', \n",
    "    'Egypt': 'EGP', \n",
    "    'Suriname': 'SRD',\n",
    "    'Mali': 'XOF', \n",
    "    'Malawi': 'MWK', \n",
    "    'Afghanistan': 'AFN', \n",
    "    'Philippines': 'PHP', \n",
    "    'Serbia': 'RSD', \n",
    "    'Anywhere': 'USD', # Assumed USD due to the high quantity of USA listings\n",
    "    'Not available': 'Not available'  # No location was determined for job listing\n",
    "}\n",
    "\n",
    "def extract_currency(x):\n",
    "    x['currency'] = country_currency_mapping[x['country']]  # Populate each listings location currency\n",
    "    return x\n",
    "    \n",
    "    \n",
    "df = df.apply(extract_currency, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a319b03f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "USD              8785\n",
       "EUR              1869\n",
       "GBP               230\n",
       "CHF               151\n",
       "Not available     117\n",
       "DKK               109\n",
       "ZAR                19\n",
       "INR                10\n",
       "PHP                 7\n",
       "AFN                 5\n",
       "SRD                 3\n",
       "ARS                 3\n",
       "RSD                 2\n",
       "XOF                 1\n",
       "Name: currency, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.currency.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a18919bd",
   "metadata": {},
   "source": [
    "There is expected to be a degree of error, such that Jobs may be posted within a country, but offered from international origins, making the country of origin to currency mapping incorrect. However this occurance is a minority, and if extreme outlier occur due to this, the values can be transformed or removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b9abf889",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data_cache/data_countries.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "903c6ca9",
   "metadata": {},
   "source": [
    "## Currency Conversion\n",
    "Convert from a multiplitude of currencies into **Euros** to enable fair comparable values, when using salary as a feature during modelling or other processes.\n",
    "\n",
    "A library CurrencyConverter, enables currency conversion. It requires currency type to be specified in ISO format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "0af5a3c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_currencies(x):\n",
    "    if x['salary'] == 'Not available' or x['currency'] == 'Not available':\n",
    "        x['euro_value'] = 'Not available' \n",
    "        return x\n",
    "    \n",
    "    conv = CurrencyConverter()\n",
    "    x['euro_value'] = conv.convert(x['salary'], x['currency'], 'EUR')  # Perform currency conversion\n",
    "    return x\n",
    "\n",
    "\n",
    "df = df.apply(convert_currencies, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9edea032",
   "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>Unnamed: 0</th>\n",
       "      <th>title</th>\n",
       "      <th>company_name</th>\n",
       "      <th>location</th>\n",
       "      <th>via</th>\n",
       "      <th>description</th>\n",
       "      <th>job_highlights</th>\n",
       "      <th>detected_extensions</th>\n",
       "      <th>job_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>salary_range</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>euro_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11306</th>\n",
       "      <td>11306</td>\n",
       "      <td>Data Scientist with Security Clearance</td>\n",
       "      <td>ClearanceJobs</td>\n",
       "      <td>Bethesda, MD, USA</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Data Scientist Job Category: Science Time Type...</td>\n",
       "      <td>Must be a U.S. citizen', 'Must currently hold ...</td>\n",
       "      <td>{'posted_at': '26 days ago', 'schedule_type': ...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCB3aXRoIF...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>Not available</td>\n",
       "      <td>38.98127255</td>\n",
       "      <td>-77.12335871396549</td>\n",
       "      <td>United States</td>\n",
       "      <td>USD</td>\n",
       "      <td>Not available</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11307</th>\n",
       "      <td>11307</td>\n",
       "      <td>Data Scientist - Tiktok Ads, Ads Measurement</td>\n",
       "      <td>TikTok</td>\n",
       "      <td>Mountain View, CA, USA</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Responsibilities TikTok is the leading destina...</td>\n",
       "      <td>3+ years industry experience and advanced degr...</td>\n",
       "      <td>{'posted_at': '7 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCAtIFRpa3...</td>\n",
       "      <td>224500.0</td>\n",
       "      <td>153000-296000</td>\n",
       "      <td>37.3893889</td>\n",
       "      <td>-122.0832101</td>\n",
       "      <td>United States</td>\n",
       "      <td>USD</td>\n",
       "      <td>207601.257629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11308</th>\n",
       "      <td>11308</td>\n",
       "      <td>RWE Clinical Data Scientist - Sales Solutions</td>\n",
       "      <td>Tempus Labs, Inc.</td>\n",
       "      <td>New York, NY, USA</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Passionate about precision medicine and advanc...</td>\n",
       "      <td>Education to Masters or PhD level combining qu...</td>\n",
       "      <td>{'posted_at': '9 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJSV0UgQ2xpbmljYWwgRGF0YSBTY2...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>Not available</td>\n",
       "      <td>40.7127281</td>\n",
       "      <td>-74.0060152</td>\n",
       "      <td>United States</td>\n",
       "      <td>USD</td>\n",
       "      <td>Not available</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11309</th>\n",
       "      <td>11309</td>\n",
       "      <td>Healthcare Data Scientist</td>\n",
       "      <td>IQuest Solutions Corporation</td>\n",
       "      <td>Irving, TX, USA</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Responsibilities  Provide analysis to identify...</td>\n",
       "      <td>5 or more years of relevant work experience', ...</td>\n",
       "      <td>{'schedule_type': 'Full–time'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJIZWFsdGhjYXJlIERhdGEgU2NpZW...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>Not available</td>\n",
       "      <td>32.8295183</td>\n",
       "      <td>-96.9442177</td>\n",
       "      <td>United States</td>\n",
       "      <td>USD</td>\n",
       "      <td>Not available</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11310</th>\n",
       "      <td>11310</td>\n",
       "      <td>Geospatial Data Scientist</td>\n",
       "      <td>Climate</td>\n",
       "      <td>Chicago, IL, USA</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>At Climate, our mission is to use technologies...</td>\n",
       "      <td>MS in Statistics, Data Science, Math, Physics,...</td>\n",
       "      <td>{'posted_at': '11 days ago', 'schedule_type': ...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJHZW9zcGF0aWFsIERhdGEgU2NpZW...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>Not available</td>\n",
       "      <td>41.8755616</td>\n",
       "      <td>-87.6244212</td>\n",
       "      <td>United States</td>\n",
       "      <td>USD</td>\n",
       "      <td>Not available</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0                                          title  \\\n",
       "11306       11306         Data Scientist with Security Clearance   \n",
       "11307       11307   Data Scientist - Tiktok Ads, Ads Measurement   \n",
       "11308       11308  RWE Clinical Data Scientist - Sales Solutions   \n",
       "11309       11309                      Healthcare Data Scientist   \n",
       "11310       11310                      Geospatial Data Scientist   \n",
       "\n",
       "                       company_name                location        via  \\\n",
       "11306                 ClearanceJobs       Bethesda, MD, USA   LinkedIn   \n",
       "11307                        TikTok  Mountain View, CA, USA   LinkedIn   \n",
       "11308             Tempus Labs, Inc.       New York, NY, USA   LinkedIn   \n",
       "11309  IQuest Solutions Corporation         Irving, TX, USA   LinkedIn   \n",
       "11310                       Climate        Chicago, IL, USA   LinkedIn   \n",
       "\n",
       "                                             description  \\\n",
       "11306  Data Scientist Job Category: Science Time Type...   \n",
       "11307  Responsibilities TikTok is the leading destina...   \n",
       "11308  Passionate about precision medicine and advanc...   \n",
       "11309  Responsibilities  Provide analysis to identify...   \n",
       "11310  At Climate, our mission is to use technologies...   \n",
       "\n",
       "                                          job_highlights  \\\n",
       "11306  Must be a U.S. citizen', 'Must currently hold ...   \n",
       "11307  3+ years industry experience and advanced degr...   \n",
       "11308  Education to Masters or PhD level combining qu...   \n",
       "11309  5 or more years of relevant work experience', ...   \n",
       "11310  MS in Statistics, Data Science, Math, Physics,...   \n",
       "\n",
       "                                     detected_extensions  \\\n",
       "11306  {'posted_at': '26 days ago', 'schedule_type': ...   \n",
       "11307  {'posted_at': '7 days ago', 'schedule_type': '...   \n",
       "11308  {'posted_at': '9 days ago', 'schedule_type': '...   \n",
       "11309                     {'schedule_type': 'Full–time'}   \n",
       "11310  {'posted_at': '11 days ago', 'schedule_type': ...   \n",
       "\n",
       "                                                  job_id         salary  \\\n",
       "11306  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCB3aXRoIF...  Not available   \n",
       "11307  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCAtIFRpa3...       224500.0   \n",
       "11308  eyJqb2JfdGl0bGUiOiJSV0UgQ2xpbmljYWwgRGF0YSBTY2...  Not available   \n",
       "11309  eyJqb2JfdGl0bGUiOiJIZWFsdGhjYXJlIERhdGEgU2NpZW...  Not available   \n",
       "11310  eyJqb2JfdGl0bGUiOiJHZW9zcGF0aWFsIERhdGEgU2NpZW...  Not available   \n",
       "\n",
       "        salary_range     latitude           longitude        country currency  \\\n",
       "11306  Not available  38.98127255  -77.12335871396549  United States      USD   \n",
       "11307  153000-296000   37.3893889        -122.0832101  United States      USD   \n",
       "11308  Not available   40.7127281         -74.0060152  United States      USD   \n",
       "11309  Not available   32.8295183         -96.9442177  United States      USD   \n",
       "11310  Not available   41.8755616         -87.6244212  United States      USD   \n",
       "\n",
       "          euro_value  \n",
       "11306  Not available  \n",
       "11307  207601.257629  \n",
       "11308  Not available  \n",
       "11309  Not available  \n",
       "11310  Not available  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0822bd2",
   "metadata": {},
   "source": [
    "## Skills Extraction\n",
    "\n",
    "Identify skills, and tools listed within the job listings in order to determine in-demand skillsets or tools within the Data Science profession. \n",
    "\n",
    "A collection of skills (skills_list.txt) associated with Data Science was generated through research, to serve as a checklist, against which job listings were analyzed. \n",
    "\n",
    "The result is a One-Hot-Encoded checklist per listing, indicating the skill or tool required by the job. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b1c09105",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add empty columns\n",
    "skills_list = []\n",
    "with open(\"notes_and_prototypes/skills_list.txt\") as file:\n",
    "    while (line := file.readline().rstrip()):\n",
    "        if line == \"R\":\n",
    "            skills_list.append(\" R \")\n",
    "        else:\n",
    "            skills_list.append(line)\n",
    "\n",
    "\n",
    "# Replace blank spaces with underscores for dataframe columns\n",
    "columns_list = []\n",
    "for element in skills_list:\n",
    "    columns_list.append(element.replace(\" \", \"_\"))\n",
    "\n",
    "for element in columns_list:\n",
    "    df[element] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e1daef2f",
   "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>Unnamed: 0</th>\n",
       "      <th>title</th>\n",
       "      <th>company_name</th>\n",
       "      <th>location</th>\n",
       "      <th>via</th>\n",
       "      <th>description</th>\n",
       "      <th>job_highlights</th>\n",
       "      <th>detected_extensions</th>\n",
       "      <th>job_id</th>\n",
       "      <th>salary</th>\n",
       "      <th>...</th>\n",
       "      <th>Tensorflow</th>\n",
       "      <th>Teamwork</th>\n",
       "      <th>Time_series_analysis</th>\n",
       "      <th>Unsupervised_learning</th>\n",
       "      <th>UX_design</th>\n",
       "      <th>Visualization</th>\n",
       "      <th>Web_development</th>\n",
       "      <th>XML</th>\n",
       "      <th>YAML</th>\n",
       "      <th>YARN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Junior Data Scientist</td>\n",
       "      <td>ING</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>ING Careers</td>\n",
       "      <td>As the data driven mindset is more and more em...</td>\n",
       "      <td>As the data driven mindset is more and more em...</td>\n",
       "      <td>{'posted_at': '6 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJKdW5pb3IgRGF0YSBTY2llbnRpc3...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>JUNIOR DATA SCIENTIST - Dubai, UAE</td>\n",
       "      <td>Cobblestone Energy</td>\n",
       "      <td>Utrecht</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Location: Dubai, UAE (We provide visa sponsors...</td>\n",
       "      <td>Location: Dubai, UAE (We provide visa sponsors...</td>\n",
       "      <td>{'posted_at': '4 hours ago', 'schedule_type': ...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJKVU5JT1IgREFUQSBTQ0lFTlRJU1...</td>\n",
       "      <td>60000</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Data Scientist Mobiliteit</td>\n",
       "      <td>TNO</td>\n",
       "      <td>The Hague</td>\n",
       "      <td>TNO</td>\n",
       "      <td>Halen we in Nederland de klimaatdoelen op het ...</td>\n",
       "      <td>Halen we in Nederland de klimaatdoelen op het ...</td>\n",
       "      <td>{'posted_at': '5 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBNb2JpbG...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Data Scientist Real Estate for Catella Investm...</td>\n",
       "      <td>Catella Investment Management Benelux</td>\n",
       "      <td>Maastricht</td>\n",
       "      <td>Limburgvac</td>\n",
       "      <td>As a Data Scientist in the Research &amp; Investme...</td>\n",
       "      <td>As a Data Scientist in the Research &amp; Investme...</td>\n",
       "      <td>{'posted_at': '20 hours ago', 'schedule_type':...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBSZWFsIE...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Effectory</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Effectory Jobs</td>\n",
       "      <td>Improving the working lives of millions of peo...</td>\n",
       "      <td>Improving the working lives of millions of peo...</td>\n",
       "      <td>{'schedule_type': 'Full–time'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...</td>\n",
       "      <td>68750.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Adyen</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Nationale Vacaturebank</td>\n",
       "      <td>Functieomschrijving Data Analytics Amsterdam T...</td>\n",
       "      <td>FunctieomschrijvingData Analytics AmsterdamThi...</td>\n",
       "      <td>{'posted_at': '17 hours ago', 'schedule_type':...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Data Scientist bij Transavia</td>\n",
       "      <td>Transavia</td>\n",
       "      <td>Schiphol</td>\n",
       "      <td>Vacatures - Transa</td>\n",
       "      <td>Wij zoeken jou als Data Scientist Voor ons Str...</td>\n",
       "      <td>Wij zoeken jou als Data ScientistVoor ons Stra...</td>\n",
       "      <td>{'schedule_type': 'Full–time'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBiaWogVH...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Data Science Lead - Amsterdam</td>\n",
       "      <td>Bynder</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Careers At Bynder</td>\n",
       "      <td>Bynder goes far beyond managing digital assets...</td>\n",
       "      <td>Bynder goes far beyond managing digital assets...</td>\n",
       "      <td>{'posted_at': '2 days ago', 'schedule_type': '...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgTGVhZCAtIE...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>LEAD DATA SCIENTIST - Dubai, UAE</td>\n",
       "      <td>Cobblestone Energy</td>\n",
       "      <td>Rotterdam</td>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>Employment type: Full-time &amp; Permanent Reports...</td>\n",
       "      <td>Employment type: Full-time &amp; PermanentReports ...</td>\n",
       "      <td>{'posted_at': '1 day ago', 'schedule_type': 'F...</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJMRUFEIERBVEEgU0NJRU5USVNUIC...</td>\n",
       "      <td>150000</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>Data Science and Artificial Intelligence Fello...</td>\n",
       "      <td>Wageningen University &amp; Research</td>\n",
       "      <td>Wageningen</td>\n",
       "      <td>AcademicTransfer</td>\n",
       "      <td>Are you a computer scientist with a PhD degree...</td>\n",
       "      <td>Are you a computer scientist with a PhD degree...</td>\n",
       "      <td>{'posted_at': '2 days ago'}</td>\n",
       "      <td>eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgYW5kIEFydG...</td>\n",
       "      <td>Not available</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 103 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                                              title  \\\n",
       "0           0                              Junior Data Scientist   \n",
       "1           1                 JUNIOR DATA SCIENTIST - Dubai, UAE   \n",
       "2           2                          Data Scientist Mobiliteit   \n",
       "3           3  Data Scientist Real Estate for Catella Investm...   \n",
       "4           4                                     Data Scientist   \n",
       "5           5                                     Data Scientist   \n",
       "6           6                       Data Scientist bij Transavia   \n",
       "7           7                      Data Science Lead - Amsterdam   \n",
       "8           8                   LEAD DATA SCIENTIST - Dubai, UAE   \n",
       "9           9  Data Science and Artificial Intelligence Fello...   \n",
       "\n",
       "                            company_name    location                      via  \\\n",
       "0                                    ING   Amsterdam              ING Careers   \n",
       "1                     Cobblestone Energy     Utrecht                 LinkedIn   \n",
       "2                                    TNO   The Hague                      TNO   \n",
       "3  Catella Investment Management Benelux  Maastricht               Limburgvac   \n",
       "4                              Effectory   Amsterdam           Effectory Jobs   \n",
       "5                                  Adyen   Amsterdam   Nationale Vacaturebank   \n",
       "6                              Transavia    Schiphol       Vacatures - Transa   \n",
       "7                                 Bynder   Amsterdam        Careers At Bynder   \n",
       "8                     Cobblestone Energy   Rotterdam                 LinkedIn   \n",
       "9       Wageningen University & Research  Wageningen         AcademicTransfer   \n",
       "\n",
       "                                         description  \\\n",
       "0  As the data driven mindset is more and more em...   \n",
       "1  Location: Dubai, UAE (We provide visa sponsors...   \n",
       "2  Halen we in Nederland de klimaatdoelen op het ...   \n",
       "3  As a Data Scientist in the Research & Investme...   \n",
       "4  Improving the working lives of millions of peo...   \n",
       "5  Functieomschrijving Data Analytics Amsterdam T...   \n",
       "6  Wij zoeken jou als Data Scientist Voor ons Str...   \n",
       "7  Bynder goes far beyond managing digital assets...   \n",
       "8  Employment type: Full-time & Permanent Reports...   \n",
       "9  Are you a computer scientist with a PhD degree...   \n",
       "\n",
       "                                      job_highlights  \\\n",
       "0  As the data driven mindset is more and more em...   \n",
       "1  Location: Dubai, UAE (We provide visa sponsors...   \n",
       "2  Halen we in Nederland de klimaatdoelen op het ...   \n",
       "3  As a Data Scientist in the Research & Investme...   \n",
       "4  Improving the working lives of millions of peo...   \n",
       "5  FunctieomschrijvingData Analytics AmsterdamThi...   \n",
       "6  Wij zoeken jou als Data ScientistVoor ons Stra...   \n",
       "7  Bynder goes far beyond managing digital assets...   \n",
       "8  Employment type: Full-time & PermanentReports ...   \n",
       "9  Are you a computer scientist with a PhD degree...   \n",
       "\n",
       "                                 detected_extensions  \\\n",
       "0  {'posted_at': '6 days ago', 'schedule_type': '...   \n",
       "1  {'posted_at': '4 hours ago', 'schedule_type': ...   \n",
       "2  {'posted_at': '5 days ago', 'schedule_type': '...   \n",
       "3  {'posted_at': '20 hours ago', 'schedule_type':...   \n",
       "4                     {'schedule_type': 'Full–time'}   \n",
       "5  {'posted_at': '17 hours ago', 'schedule_type':...   \n",
       "6                     {'schedule_type': 'Full–time'}   \n",
       "7  {'posted_at': '2 days ago', 'schedule_type': '...   \n",
       "8  {'posted_at': '1 day ago', 'schedule_type': 'F...   \n",
       "9                        {'posted_at': '2 days ago'}   \n",
       "\n",
       "                                              job_id         salary  ...  \\\n",
       "0  eyJqb2JfdGl0bGUiOiJKdW5pb3IgRGF0YSBTY2llbnRpc3...  Not available  ...   \n",
       "1  eyJqb2JfdGl0bGUiOiJKVU5JT1IgREFUQSBTQ0lFTlRJU1...          60000  ...   \n",
       "2  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBNb2JpbG...  Not available  ...   \n",
       "3  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBSZWFsIE...  Not available  ...   \n",
       "4  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...        68750.0  ...   \n",
       "5  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCIsImh0aW...  Not available  ...   \n",
       "6  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVudGlzdCBiaWogVH...  Not available  ...   \n",
       "7  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgTGVhZCAtIE...  Not available  ...   \n",
       "8  eyJqb2JfdGl0bGUiOiJMRUFEIERBVEEgU0NJRU5USVNUIC...         150000  ...   \n",
       "9  eyJqb2JfdGl0bGUiOiJEYXRhIFNjaWVuY2UgYW5kIEFydG...  Not available  ...   \n",
       "\n",
       "  Tensorflow Teamwork Time_series_analysis Unsupervised_learning UX_design  \\\n",
       "0          0        0                    0                     0         0   \n",
       "1          0        0                    0                     0         0   \n",
       "2          0        0                    0                     0         0   \n",
       "3          0        0                    0                     0         0   \n",
       "4          0        0                    0                     0         0   \n",
       "5          1        0                    0                     0         0   \n",
       "6          1        0                    0                     0         0   \n",
       "7          0        0                    0                     0         0   \n",
       "8          0        0                    0                     0         0   \n",
       "9          0        0                    0                     0         0   \n",
       "\n",
       "  Visualization  Web_development  XML  YAML  YARN  \n",
       "0             1                0    0     0     0  \n",
       "1             1                0    0     0     0  \n",
       "2             0                0    0     0     0  \n",
       "3             0                0    0     0     0  \n",
       "4             1                0    0     0     0  \n",
       "5             0                0    0     0     0  \n",
       "6             1                0    0     0     0  \n",
       "7             1                0    0     0     0  \n",
       "8             1                0    0     0     0  \n",
       "9             0                0    0     0     0  \n",
       "\n",
       "[10 rows x 103 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find keywords and set columns accordingly\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    for keyword in skills_list:\n",
    "        if keyword.lower() in df.at[i, \"description\"].lower():\n",
    "            col = keyword.replace(\" \", \"_\")\n",
    "            df.at[i, col] = 1\n",
    "# Print first results to check\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4dbfc32",
   "metadata": {},
   "source": [
    "## Export Data Frame to new CSV\n",
    "With the new csv, all necessary information is extracted and will be the new basis to work with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5ce03d8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data_clean.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "data_madness",
   "language": "python",
   "name": "data_madness"
  },
  "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}