03_recruiter_NER.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Tagging And Sorting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### After running the classifier in 02_recruiter_classifier, run this!\n",
    "### Tags job titles and classifies them (management, tech, web), job titles can have multiple classes\n",
    "### Tags location using SpaCy\n",
    "### Tags job requirements and classifies them\n",
    "### Tags meta data about the jobs\n",
    "### Filters job ads to interesting and uninteresting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1 Load dependencies and mail dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In case you need to install the model for spacy\n",
    "\n",
    "using English here, you might want to change it for other langauges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install spacy en_core_web_sm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import pickle\n",
    "import pandas as pd\n",
    "import spacy\n",
    "import numpy as np\n",
    "from spacy import displacy\n",
    "import json\n",
    "import en_core_web_sm\n",
    "nlp = en_core_web_sm.load()\n",
    "from bs4 import BeautifulSoup\n",
    "from IPython.core.display import display, HTML\n",
    "import unicodedata\n",
    "from html.parser import HTMLParser\n",
    "import ipysheet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the ground truth recruiter data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df = pd.read_csv('files/hide/ground_truth_recruiter_df.csv')\n",
    "# For dummy dataset job_email_examples as test_set:\n",
    "#trecruiter_df= pd.read_csv('files/dummy_data/job_email_examples.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 Job type labeling on email subjects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will want to find the job titles in the subject and classify them (this could also be done in the message itself).\n",
    "Therefore, we will search through the subjects for the keywords in 'job-types.json'.\n",
    "Then label the 'type' they represent (i.e. 'head of data science' is a combination of management and AI)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "jobTypes = json.load(open('files/job_titles/job-types.json', 'r'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the function for job title parsing (NER)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def checkJobType(subject, jobTypes):\n",
    "    foundJobTypes = []\n",
    "    foundJobs = []\n",
    "    subject = subject.lower()\n",
    "    for name, categories in jobTypes.items():\n",
    "          for category in categories:\n",
    "            if category in subject:\n",
    "                foundJobTypes.append(name)\n",
    "                foundJobs.append(category)\n",
    "    return list(set(foundJobTypes)), foundJobs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional data check: print out the subjects, tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df['subject']\n",
    "for subject in recruiter_df['subject']:\n",
    "    output = checkJobType(subject, jobTypes)\n",
    "    print(subject, '\\\\t->', checkJobType(subject, jobTypes))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Codeblock to rip out job types and append them to new columns in the df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "catTags = []\n",
    "tags = []\n",
    "jobsPerEntry = []\n",
    "categories = jobTypes.keys()\n",
    "for subject in recruiter_df['subject']:\n",
    "    currentJobTypes, jobs = checkJobType(subject, jobTypes)\n",
    "    tags.append(';'.join(currentJobTypes))\n",
    "    jobsPerEntry.append(';'.join(jobs))\n",
    "    \n",
    "recruiter_df['jobTypes'] = tags\n",
    "recruiter_df['jobTags'] = jobsPerEntry"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3 Clean the subjects and extract location labels from subjects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To extract locations and other processing we will have to clean up the subjects a bit. Then to extract the locations from the subject, we will use SpaCy (the locations could also be pulled from the messages)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: For SpaCy it seems their CNN model works best when you keep in captial letters and commas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanText(subject):\n",
    "    subject = re.sub(r'^(re|fwd):\\s*', '', subject, flags=re.I) \n",
    "    subject = re.sub(r'[^a-zA-Z0-9,]+', \" \", subject)\n",
    "    subject = re.sub(r'\\s*,\\s*', ', ', subject)\n",
    "    return subject.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df['subject_cleaned'] = recruiter_df['subject'].apply(cleanText)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This takes the NER from spaCy, applies it to our df on the subject labeling locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = []\n",
    "for subject in recruiter_df['subject_cleaned']:\n",
    "    output = nlp(subject)\n",
    "    all_locations = []\n",
    "    for ent in output.ents:\n",
    "        if ent.label_ == 'GPE' or ent.label_ == 'LOC':\n",
    "            all_locations.append(ent.text)\n",
    "    results.append([subject, ';'.join(all_locations)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: to check your results\n",
    "SpaCy NER doesn't work perfectly\n",
    "I would recommend training a new model from your data if you have enough or applying NER to the message itself"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results_df = pd.DataFrame(results, columns=['subject_cleaned', 'location'], index=recruiter_df.index)\n",
    "results_df['location'].replace('', np.nan, inplace=True)\n",
    "results_df.drop(columns='subject_cleaned', inplace=True)\n",
    "recruiter_df = pd.concat([recruiter_df, results_df], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: How's this shaping up?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df.drop(['date','language','message','name', 'email', 'subject', 'domain', 'prediction', 'class', 'firstname', 'lastname'], axis=1).style.set_properties(subset=['subject_cleaned'], **{'width': '300px'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df.drop(['subject_cleaned'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Optional: Extract and clean requirements from message bullet points"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bullet points are a low hanging fruit for finding requirements. Run this if you want to make your own dictionary file to match custom requirements in emails instead of using the default one (requirement-types.json)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-danger\"><b>!IMPORTANT!</b> If you are running the dummy dataset, skip this</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def select_bullet(item):\n",
    "    lists = BeautifulSoup(item, \"lxml\").select('ul')\n",
    "    if lists:\n",
    "        return lists[0].get_text()\n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df['message_bullets'] = recruiter_df['message'].apply(select_bullet)\n",
    "recruiter_df['message_bullets'] = recruiter_df['message_bullets'].str.replace('\\n', ' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_total = len(recruiter_df)\n",
    "count_na = len(recruiter_df['message_bullets']) - recruiter_df['message_bullets'].count()\n",
    "count_bullet_char = len(recruiter_df[recruiter_df['message'].str.contains('•')])\n",
    "print (\"From a total of\", count_total, \"entries, there are\", count_na, \"without any HTML bullet points\", \"and\", count_bullet_char, \"with symbol bullet points\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ideas for keywords and phrases we will need for later.\n",
    "Take a look at the results to to get a feel for important requirements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df.dropna().drop(['subject', 'location','language', 'message','name', 'email', 'domain', 'prediction', 'class', 'firstname', 'lastname', 'date'], axis=1).style.set_properties(**{'width': '90%'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to get rough word counts of requirements\n",
    "\n",
    "So we will clean the message_bullets\n",
    "\n",
    "Then tokenize and use stop words\n",
    "\n",
    "Finally we will count the remaining keywords\n",
    "\n",
    "Keep in mind, stop words will sometimes change words we don't want to like turn kubernetes into kupernete. Also this will only count single words, not phrases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df['message_bullets_cleaned'] = recruiter_df['message_bullets'].dropna().apply(cleanText).str.replace(',', '')\n",
    "recruiter_df['message_bullets_cleaned'] = recruiter_df['message_bullets_cleaned'].dropna().str.lower().apply(lambda text: \" \".join(token.lemma_ for token in nlp(text) if not token.is_stop))\n",
    "requirement_ideas = recruiter_df['message_bullets_cleaned'].str.split(' ', expand=True).stack().value_counts()\n",
    "requirement_ideas_df = pd.DataFrame(requirement_ideas, columns=['count'])\n",
    "pd.set_option('display.max_rows', None)\n",
    "requirement_ideas_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From our dataframe with some ideas of requirement keywords, as well as our results from the 'message_bullets', we can turn that into a dictionary file (requirement-types.json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requirement_ideas_df.to_csv(r'files/hide/requirement_ideas_df.csv', index=False)\n",
    "recruiter_df.drop(['message_bullets', 'message_bullets_cleaned'], axis=1, inplace=True)\n",
    "pd.set_option('display.max_rows', 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.4 Pull requirements from whole message"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use either the default dictionary (requirement-types.json) or that you made based on the requirements from 4.0 to extract the requirements line by line from the whole message, not just the bullet points"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-danger\"><b>!IMPORTANT!</b> If you are running the dummy dataset skip from the next two steps, the messages have already be cleaned.</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function will clean up the email messages with HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanMessages(html):\n",
    "    soup = BeautifulSoup(html, \"html.parser\")\n",
    "    for script in soup([\"script\", \"style\"]):\n",
    "        script.extract()\n",
    "    text = soup.get_text()\n",
    "    text = unicodedata.normalize(\"NFKD\", text)\n",
    "    text = text.replace('\\xa0', '\\n')\n",
    "    text = text.replace('\\x92', '')\n",
    "    text = text.replace('\\x92s', '')\n",
    "    text = text.replace('\\x96', '')\n",
    "    text = text.replace('\\u200b', '')\n",
    "    text = re.sub(r'(--- mail_boundary ---)\\s*(.+)', '', text) \n",
    "    lines = (line.strip() for line in text.splitlines())\n",
    "    chunks = (phrase.strip() for line in lines for phrase in line.split(\"  \"))\n",
    "    text = '\\n'.join(chunk for chunk in chunks if chunk)\n",
    "    return text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to overwrite message_cleaned with a line split version to work with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df['message_cleaned'] = recruiter_df['message'].dropna().apply(lambda x: cleanMessages(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requirementTypes = json.load(open('files/requirement-types.json', 'r'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Function for requirement parsing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def checkRequirementType(message, requirementTypes):\n",
    "    foundRequirementTypes = []\n",
    "    foundRequirementKeywords = []\n",
    "    foundRequirements = []\n",
    "    message = message.lower()\n",
    "    lines = message.split('\\n')\n",
    "    for name, categories in requirementTypes.items():\n",
    "          for category in categories:\n",
    "            for line in lines:\n",
    "                if category in line:\n",
    "                    foundRequirementTypes.append(name)\n",
    "                    foundRequirementKeywords.append(category)\n",
    "                    foundRequirements.append(line)\n",
    "    return list(set(foundRequirementTypes)), list(set(foundRequirementKeywords)), list(set(foundRequirements))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional data check: print out the found requirement types, keywords, and line it was found in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df['message_cleaned']\n",
    "for message in recruiter_df['message_cleaned']:\n",
    "    output = checkRequirementType(message, requirementTypes)\n",
    "    print(output)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional Example\n",
    "\n",
    "This function changes requirement lines where a line lists devlop/build + ml algorithms\n",
    "\n",
    "Develop/Build is not the same as having a theoretical understanding of ml algorithms\n",
    "\n",
    "You can add your own easily"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_requirements(requirement):\n",
    "    if re.search('(build|develop|design)([^ ]* ){1,5}algorithm|algorithm design', requirement, re.I):\n",
    "        requirement = re.sub('algorithm', 'research', requirement)\n",
    "    return requirement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Code block to rip out requirement types, the keywords found, and the lines they were found on.\n",
    "\n",
    "Then appends them to new columns in the df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tags = []\n",
    "keywordsPerEntry = []\n",
    "requirementsPerEntry = []\n",
    "categories = requirementTypes.keys()\n",
    "for message in recruiter_df['message_cleaned']:\n",
    "    message = clean_requirements(message)\n",
    "    currentRequirementTypes, keywords,requirements = checkRequirementType(message, requirementTypes)\n",
    "    tags.append(';'.join(currentRequirementTypes))\n",
    "    keywordsPerEntry.append(';'.join(keywords))\n",
    "    requirementsPerEntry.append(';'.join(requirements))\n",
    "    \n",
    "recruiter_df['requirementTypes'] = tags\n",
    "recruiter_df['requirementKeywords'] = keywordsPerEntry\n",
    "recruiter_df['requirements'] = requirementsPerEntry"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional to check results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df.drop(['location', 'jobTypes', 'jobTags','date', 'message','message_cleaned','name', 'email', 'subject', 'domain', 'prediction', 'class', 'firstname', 'lastname', 'language'], axis=1).style.set_properties(**{'width': '1%'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.5 Extract meta job data (salary, title, etc.) from messages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By looking at the messages themselves, it can be seen that one or more of the following patterns often occurs:\n",
    "\n",
    "role: (role)\n",
    "salary: (salary)\n",
    "title: (title)\n",
    "\n",
    "Because of this we can extract this job meta data for further analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to overwrite message_cleaned with a line split version to work with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df['message_cleaned'] = recruiter_df['message_cleaned'].str.split('\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bring in the tags (notice, I have also done this whole notebook for German too)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "role_re = re.compile(r'(title|titel|roll?e|position)\\s*:\\s*(.+)', re.I)\n",
    "location_re = re.compile(r'(Location|(?:stand)?ort):\\s*(.+)', re.I)\n",
    "duration_re = re.compile(r'(duration|dauer):\\s*(.+)', re.I)\n",
    "salary_re = re.compile(r'(gehalt|salary)::(?!\\\\r)\\s*(.+)*', re.I)\n",
    "education_re = re.compile(r'(education)::(?!\\\\r)\\s*(.+)', re.I)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Function for extracting the meta job data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extractMetaData(clean_message):\n",
    "    extractRegEx = {'role':role_re, 'location':location_re, 'duration':duration_re, 'salary':salary_re, 'education':education_re}\n",
    "    metaData = {}\n",
    "    results = []\n",
    "    greedy = False\n",
    "    for line in clean_message:\n",
    "        for label, regEx in extractRegEx.items():\n",
    "            extracted = regEx.findall(line)\n",
    "            if extracted:\n",
    "                result = extracted[0][1].strip()\n",
    "                metaData[label] = result\n",
    "                if not result:\n",
    "                    greedy = True\n",
    "                elif greedy:\n",
    "                    result = line\n",
    "                    metaData[label] += result\n",
    "    return metaData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = []\n",
    "\n",
    "for clean_message in recruiter_df['message_cleaned']:\n",
    "    output = extractMetaData(clean_message) \n",
    "    results.append(output)\n",
    "    \n",
    "metaData_df = pd.DataFrame.from_dict(results)\n",
    "metaData_df['location'] = metaData_df['location'].str.replace('/', ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metaData_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Experience is done slightly differently.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = []\n",
    "experience_re = re.compile(r'(?:at least |minimum )?([^\\s]+ ?y(?:ea)rs?[\\w ]+?experience(?:(?: in| with)(?:.+)?)?)', re.I)\n",
    "for clean_message in recruiter_df['message_cleaned']:\n",
    "    result = []\n",
    "    for line in clean_message:\n",
    "        output = experience_re.findall(line)\n",
    "        if output:\n",
    "            result.extend(output)\n",
    "    if result:\n",
    "        results.append(';'.join(set(result)))\n",
    "    else:\n",
    "        results.append(np.nan)\n",
    "results\n",
    "experience_df = pd.DataFrame(zip((results)), columns=['experience'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metaData_df = pd.concat([metaData_df, experience_df], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: review the complete job meta data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metaData_df.style.set_properties(**{'width': '200px'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's fill in our NaNs with the non-NaN locations ripped from the subject by SpaCy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metaData_df['location'] = metaData_df['location'].fillna(recruiter_df['location'].dropna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df.drop(['location'], axis=1, inplace=True)\n",
    "recruiter_df = pd.concat([recruiter_df, metaData_df], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.6 Response sorting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Job offers analysis\n",
    "\n",
    "Here's an example of some of the insights we can gleem from our data very easily\n",
    "\n",
    "I am personally interested in ai jobs, especially that contain jobTypes 'ai' and 'management'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_recruiter_df = recruiter_df[recruiter_df['jobTypes'].str.contains('ai')].copy()\n",
    "ai_job_count = len(ai_recruiter_df)\n",
    "# !WATCH OUT! if the requirementKeywords are empty, they will be seen as duplicates\n",
    "ai_job_dup_requirements_count = len(ai_recruiter_df[ai_recruiter_df.duplicated(subset='requirements', keep='first')])\n",
    "ai_job_dup_requirementKeywords_count = len(ai_recruiter_df[ai_recruiter_df.duplicated(subset='requirementKeywords', keep='first')])\n",
    "\n",
    "print(\"There are\", ai_job_count, \"AI jobs\", \"with\", ai_job_dup_requirements_count, \"probable duplicates and\", ai_job_dup_requirementKeywords_count, \"possible duplicates\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: Look at Probable duplicates (duplicates based on requirements)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_recruiter_df[ai_recruiter_df.duplicated(subset='requirements', keep=False)].drop(['email', 'firstname', 'lastname','message', 'message_cleaned', 'class', 'prediction', 'language', 'jobTypes', 'requirementTypes'], axis=1).sort_values('requirementKeywords').style.set_properties(**{'width': '200px'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: it seems like some duplicates might have nan values in some columns so let's fill those missing values and drop the duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_recuiter_df = ai_recruiter_df.groupby('requirements').apply(lambda x: x.ffill().bfill()).drop_duplicates(subset='requirements')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are possible duplicates based on requirement keywords\n",
    "\n",
    "Having no or few requirement keywords might wrongly identify duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_recruiter_df[ai_recruiter_df.duplicated(subset='requirementKeywords', keep=False)].drop(['email', 'firstname', 'lastname','message', 'message_cleaned', 'class', 'prediction', 'language', 'jobTypes', 'requirementTypes'], axis=1).sort_values('requirementKeywords').style.set_properties(**{'width': '200px'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: I personally like to filter the above probable requirement keywords duplicates with location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_recruiter_df = ai_recruiter_df.dropna(subset=['location']).drop_duplicates(subset=['requirementKeywords', 'location'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the AI jobs how many combine other job types?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_recruiter_df['jobTypes'].str.split(';', expand=True).stack().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_management_recruiter_df = ai_recruiter_df[ai_recruiter_df['jobTypes'].str.contains('management')].copy()\n",
    "ai_management_job_count = len(ai_management_recruiter_df)\n",
    "print(\"There are\", ai_management_job_count,  \"jobs that also combine keywords with management\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Quick look at the AI-management keywords and their counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_management_recruiter_df['jobTags'].str.split(';', expand=True).stack().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a bug work around for ipysheet\n",
    "\n",
    "If there is only one row in a df converted to sheet, when trying to convert back into df it throws an exception\n",
    "\n",
    "`Exception: Data must be 1-dimensional`\n",
    "\n",
    "Note: This will add a row of NaN until removed after converting back into df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if ai_management_job_count == 1:\n",
    "    ai_management_recruiter_df.loc['temp'] = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's actually review the results and put a check mark in the roles we want to apply to\n",
    "\n",
    "This is the part were JupyterLab users should \"Create New View for Output\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_management_recruiter_df = ai_management_recruiter_df.assign(reply=None)\n",
    "ai_management_recruiter_df['reply'] = ai_management_recruiter_df['reply'].astype(bool)\n",
    "ai_management_recruiter_df.drop(['jobTypes', 'subject', 'message_cleaned', 'language', 'message','name', 'email', 'domain', 'prediction', 'class', 'firstname', 'lastname', 'date', 'requirements', 'requirementTypes'], axis=1, inplace=True, errors='ignore')\n",
    "ai_management_recruiter_sheet = ipysheet.from_dataframe(ai_management_recruiter_df)\n",
    "ai_management_recruiter_sheet.layout.overflow_y = 'scroll'\n",
    "ai_management_recruiter_sheet.layout.overflow_x = 'scroll'\n",
    "ai_management_recruiter_sheet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn the sheet back into a df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_management_recruiter_df = ipysheet.to_dataframe(ai_management_recruiter_sheet)\n",
    "ai_management_recruiter_df.drop(index = 'temp', inplace=True, errors='ignore')\n",
    "# NOTE: ipysheet messes up the index turning it into strings\n",
    "ai_management_recruiter_df.index = pd.to_numeric(ai_management_recruiter_df.index)\n",
    "ai_management_recruiter_df['reply'] = ai_management_recruiter_df['reply'].astype(str)\n",
    "ai_management_recruiter_df['reply'].replace({'True':'interested', 'False':'uninterested'}, inplace=True)\n",
    "ai_management_recruiter_df.drop(['jobTags', 'location', 'requirementKeywords', 'experience', 'duration', 'role'], inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ai_management_recruiter_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bring the role_interesting information back into the main recruiter df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "processed_recruiter_df = pd.concat([recruiter_df, ai_management_recruiter_df], axis=1)\n",
    "processed_recruiter_df = processed_recruiter_df.fillna('uninterested')\n",
    "processed_recruiter_df.drop(['prediction', 'class', 'jobTags', 'jobTypes', 'requirementTypes', 'location', 'message_cleaned', 'requirementKeywords', 'requirements', 'experience', 'message', 'role', 'duration'], inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.7 Export data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Export the data to send responses back to recruiters "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "processed_recruiter_mails = processed_recruiter_df.to_json(orient='records')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: have a look to make sure it checks out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "processed_recruiter_mails"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('processed_recruiter_mails.json', 'w') as outfile:\n",
    "    json.dump('files/hide/processed_recruiter_mails', outfile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: Save your recruiter_df as a csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recruiter_df.to_csv(r'files/hide/recruiter_df.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Onward to the final step, <a href=\"./04_recruiter_send_emails.ipynb\">04_recruiter_send_emails…</a>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}