cp_5392_skill_standardization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CLEAN SKILL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "from __future__ import division\n",
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process\n",
    "import pandas as pd\n",
    "import string\n",
    "import time\n",
    "import random\n",
    "import os\n",
    "\n",
    "# BASE_PATH = os.path.dirname(__file__)\n",
    "\n",
    "class CleanSkills():\n",
    "\n",
    "    def __init__(self, input_skill_file,  clean_skill_file, output_skill_file):\n",
    "        if type(input_skill_file) is pd.core.frame.DataFrame:\n",
    "            self.input_skills_list = input_skill_file['lemmatised_skill']\n",
    "        else:\n",
    "            self.input_skills_list = pd.read_csv(input_skill_file)['lemmatised_skill']\n",
    "\n",
    "        if type(clean_skill_file) is pd.core.frame.DataFrame:\n",
    "            self.clean_skills_list = clean_skill_file['skill']\n",
    "        else:\n",
    "            self.clean_skills_list = pd.read_csv(clean_skill_file)['skill']\n",
    "        self.output_clean_skill_file = output_skill_file\n",
    "        self.res = {}        \n",
    "\n",
    "    def clean_skills(self, output_type='DataFrame'):\n",
    "        result_df = pd.DataFrame()\n",
    "        cols = ['skill', 'clean_skill', 'actual_skill']\n",
    "\n",
    "        # self.input_skills_list = ['oracle database: 11g', 'self confidant', 'uv visible spectroscopy', 'junior software developer', 'angular material design', 'signal processsing']\n",
    "        for skill in self.input_skills_list:\n",
    "            skill = str(skill).strip()\n",
    "            if skill.lower() in self.res.keys():\n",
    "                skills_stat_df = pd.DataFrame({'skill':[skill], 'clean_skill':[self.res[skill.lower()]]})\n",
    "                result_df = result_df.append(skills_stat_df)\n",
    "                continue\n",
    "            actual_skill, skill_list, clean_skill_list, total_score_list, ratio_list, partial_ratio_list,\\\n",
    "            sort_ratio_list, partial_sort_ratio_list, set_ratio_list, partial_set_ratio_list = [], [], [], [], [], [], [], [], [], []\n",
    "            for clean_skill in self.clean_skills_list:\n",
    "                clean_skill = str(clean_skill)\n",
    "                actual_skill.append(clean_skill)\n",
    "                clean_skill = clean_skill.lower()\n",
    "                ratio = fuzz.ratio(skill, clean_skill)\n",
    "                partial_ratio = fuzz.partial_ratio(skill, clean_skill)\n",
    "                sort_ratio = fuzz.token_sort_ratio(skill, clean_skill)\n",
    "                partial_sort_ratio = fuzz.partial_token_sort_ratio(skill, clean_skill)\n",
    "                set_ratio = fuzz.token_set_ratio(skill, clean_skill)\n",
    "                partial_set_ratio = fuzz.partial_token_set_ratio(skill, clean_skill)\n",
    "\n",
    "                total_score = (1 * ratio) + (0.8 * set_ratio) + (0.8 * sort_ratio) + (0.5 * partial_ratio) + \\\n",
    "                              (0.3 * partial_set_ratio) + (0.3 * partial_sort_ratio)\n",
    "                \n",
    "                skill_list.append(skill)\n",
    "                clean_skill_list.append(clean_skill)\n",
    "                total_score_list.append(total_score)\n",
    "                ratio_list.append(ratio)\n",
    "                partial_ratio_list.append(partial_ratio)\n",
    "                sort_ratio_list.append(sort_ratio)\n",
    "                partial_sort_ratio_list.append(partial_sort_ratio)\n",
    "                set_ratio_list.append(set_ratio)\n",
    "                partial_set_ratio_list.append(partial_set_ratio)\n",
    "            skills_stat_df = pd.DataFrame({'skill':skill_list, 'clean_skill':clean_skill_list,\\\n",
    "                            'actual_skill':actual_skill,\\\n",
    "                            'total_score':total_score_list, 'ratio':ratio_list,\\\n",
    "                            'partial_ratio':partial_ratio_list, 'sort_ratio':sort_ratio_list,\\\n",
    "                            'partial_sort_ratio':partial_sort_ratio_list, 'set_ratio':set_ratio_list, 'partial_set_ratio':partial_set_ratio_list})\\\n",
    "                            .sort_values(by=['total_score', 'ratio', 'sort_ratio', 'set_ratio', 'partial_ratio'], ascending=False)[:1].reset_index(drop=True)\n",
    "#             print(skills_stat_df['skill'])\n",
    "#             print(skills_stat_df['clean_skill'])\n",
    "            self.res[skills_stat_df['skill'][0].lower()] = skills_stat_df['actual_skill'][0]\n",
    "            result_df = result_df.append(skills_stat_df[cols])\n",
    "\n",
    "        if output_type != 'DataFrame':\n",
    "            result_df.to_csv(self.output_clean_skill_file)\n",
    "        else:\n",
    "            return result_df.reset_index(drop=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "LEMMATISE SKILLS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import os\n",
    "# import nltk\n",
    "# from nltk.stem import WordNetLemmatizer\n",
    "# import csv\n",
    "# import sys\n",
    "# import string\n",
    "\n",
    "# class LemmatiseSkills():\n",
    "    \n",
    "#     def __init__(self):\n",
    "#         self.wordnet_lemmatizer = WordNetLemmatizer()\n",
    "#         # nltk.download('wordnet')\n",
    "\n",
    "#     def lemmatise_file(self, in_file, out_file):\n",
    "#         with open(in_file, 'r') as input_file, open(out_file, 'w') as output_file:\n",
    "#             line = input_file.readline()\n",
    "#             while line:\n",
    "#                 flag = 0\n",
    "#                 for char in line:\n",
    "#                     if ord(char) > 127:\n",
    "#                         flag = 1\n",
    "#                         break\n",
    "#                 if flag:\n",
    "#                     output_file.write('\" \"\\n')\n",
    "#                     line = input_file.readline()\n",
    "#                     continue\n",
    "#                 line = line.translate(str.maketrans('-/\"()&', '      ', ''))\n",
    "#                 new_line = ''\n",
    "#                 for word in line.split(' '):\n",
    "#                     word = word.strip().lower()\n",
    "#                     lemma = self.wordnet_lemmatizer.lemmatize(word)\n",
    "#                     new_line += word+' ' if len(word) <= 4 else lemma+' '\n",
    "#                 line = input_file.readline()\n",
    "#                 output_file.write('\"{new_line}\"\\n'.format(new_line=new_line.strip()))\n",
    "\n",
    "\n",
    "import os\n",
    "import nltk\n",
    "from nltk.stem import WordNetLemmatizer\n",
    "import csv\n",
    "import sys\n",
    "import string\n",
    "\n",
    "class LemmatiseSkills():\n",
    "    \n",
    "    def __init__(self):\n",
    "        self.wordnet_lemmatizer = WordNetLemmatizer()\n",
    "        self.out_df = pd.DataFrame()\n",
    "        # nltk.download('wordnet')\n",
    "\n",
    "    def lemmatise_file(self, in_df, column_name):\n",
    "        result = []\n",
    "        for index, row in in_df.iterrows():\n",
    "            line = str(row[column_name])\n",
    "            flag = 0\n",
    "            for char in line:\n",
    "                if ord(char) > 127:\n",
    "                    flag = 1\n",
    "                    break\n",
    "            if flag:\n",
    "                result.append(' ')\n",
    "                continue\n",
    "            line = line.translate(str.maketrans('-/\"()&', '      ', ''))\n",
    "            new_line = ''\n",
    "            for word in line.split(' '):\n",
    "                word = word.strip().lower()\n",
    "                lemma = self.wordnet_lemmatizer.lemmatize(word)\n",
    "                new_line += word+' ' if len(word) <= 4 else lemma+' '\n",
    "            result.append(new_line.strip())\n",
    "        self.out_df[column_name] = result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CLEANING DRIVER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "verified_skills_file = '/home/jasmeet16-jtg/projects/temp_scripts/skill_standardization/verified_skills.csv'\n",
    "not_verified_skills_file = '/home/jasmeet16-jtg/projects/temp_scripts/skill_standardization/input_unverified_skills.csv'\n",
    "\n",
    "verified_skill_all_data = verified_skills = pd.read_csv(verified_skills_file)\n",
    "\n",
    "vs1 = verified_skills[verified_skills['Status']=='Verified']['Name']\n",
    "vs2 = verified_skills[verified_skills['New Status']=='Verified']['New Name']\n",
    "verified_skills = pd.concat([pd.DataFrame({'skill': vs1}), pd.DataFrame({'skill': vs2})])\n",
    "verified_skills.to_csv('/home/jasmeet16-jtg/projects/temp_scripts/skill_standardization/all_skills_inc_calyx.csv')\n",
    "\n",
    "vs1 = verified_skill_all_data[verified_skill_all_data['Status']=='Verified']\n",
    "vs2 = verified_skill_all_data[verified_skill_all_data['New Status']=='Verified']\n",
    "verified_skill_all_data = pd.concat([vs1, vs2])\n",
    "verified_skill_all_data.to_csv('/home/jasmeet16-jtg/projects/temp_scripts/skill_standardization/only_verified_skills_data.csv')\n",
    "\n",
    "not_verified_skills = pd.read_csv(verified_skills_file)\n",
    "not_verified_skills = not_verified_skills[(not_verified_skills['Status'] != 'Verified')\\\n",
    "                                          & (not_verified_skills['New Status'] != 'Verified')].reset_index(drop=True)\n",
    "\n",
    "ls = LemmatiseSkills()\n",
    "ls.lemmatise_file(not_verified_skills, 'Name')\n",
    "\n",
    "not_verified_skills['lemmatised_skill'] = ls.out_df['Name']\n",
    "not_verified_skills.to_csv(not_verified_skills_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jasmeet16-jtg/projects/virtualenvs/calyx_ds3/lib/python3.5/site-packages/pandas/core/frame.py:6692: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  sort=sort)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cleaned 20 skills\n",
      "Cleaned 40 skills\n",
      "Cleaned 60 skills\n",
      "Cleaned 80 skills\n",
      "Cleaned 100 skills\n",
      "Cleaned 120 skills\n",
      "Cleaned 140 skills\n",
      "Cleaned 160 skills\n",
      "Cleaned 180 skills\n"
     ]
    }
   ],
   "source": [
    "chunksize = 20\n",
    "final_output = '/home/jasmeet16-jtg/projects/temp_scripts/skill_standardization/final_output.csv'\n",
    "verified_skills = pd.read_csv('/home/jasmeet16-jtg/projects/temp_scripts/skill_standardization/all_skills_inc_calyx.csv')\n",
    "i = 0\n",
    "header=True\n",
    "for chunk in pd.read_csv(not_verified_skills_file, chunksize=chunksize):\n",
    "    chunk = chunk.reset_index(drop=True)\n",
    "    cs = CleanSkills(chunk, verified_skills, 'xyz')\n",
    "    df = cs.clean_skills()\n",
    "    chunk['New Name'] = df['actual_skill']\n",
    "    chunk['New Status'] = ['Delete']*chunksize\n",
    "    chunk.to_csv(final_output, mode='a', header=header, index=False)\n",
    "    i += chunksize\n",
    "    print('Cleaned {i} skills'.format(i=i))\n",
    "    header=False\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   x\n",
       "0  1\n",
       "1  4"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q = pd.DataFrame({'x':[1,2,3,4]})\n",
    "q = q[(q['x']==1) | (q['x']==4)]\n",
    "q = q.reset_index(drop=True)\n",
    "q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                       2g\n",
       "1                 concepts\n",
       "2                      k-1\n",
       "3                   x 10.1\n",
       "4                      10k\n",
       "5                      10k\n",
       "6     personal development\n",
       "7         ssl certificates\n",
       "8                    linux\n",
       "9           plain language\n",
       "10                database\n",
       "11                   solar\n",
       "12                 loyalty\n",
       "13                      2g\n",
       "14                  ubuntu\n",
       "15                    java\n",
       "16            positive pay\n",
       "17                   j1939\n",
       "18               lotus 123\n",
       "19                 circuit\n",
       "Name: clean_skill, dtype: object"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['clean_skill']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "system admin > data verification > verification scripts"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "calyx_ds3",
   "language": "python",
   "name": "calyx_ds3"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}