Project_test_jobtitle_cleaning .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "job = pd.read_excel('V3.0 Project OA Data_excel_Testingdata.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "job['Clean Job Title']= job['Job Title'].str.upper().replace('[^A-Z0-9]+',' ',regex=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>S.No</th>\n",
       "      <th>Job Class</th>\n",
       "      <th>Job Title</th>\n",
       "      <th>Min_Salary_USD</th>\n",
       "      <th>Max_Salary_USD</th>\n",
       "      <th>Average Salary_USD</th>\n",
       "      <th>Range_salary_USD</th>\n",
       "      <th>Job Description</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Rating Band</th>\n",
       "      <th>...</th>\n",
       "      <th>Competitors</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Location_City</th>\n",
       "      <th>Location_State</th>\n",
       "      <th>Headquarters_City</th>\n",
       "      <th>Headquarters_State</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON1</th>\n",
       "      <th>Clean Job Title</th>\n",
       "      <th>skillsset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12382</td>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>Architect Data Analysis of Research Domain</td>\n",
       "      <td>52925</td>\n",
       "      <td>93000</td>\n",
       "      <td>72962.5</td>\n",
       "      <td>40075</td>\n",
       "      <td>Th candidate should be  able to perform the fo...</td>\n",
       "      <td>3.5</td>\n",
       "      <td>Medium Rated</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ARCHITECT DATA ANALYSIS OF RESEARCH DOMAIN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12389</td>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Assistant Manager - Data Scientist</td>\n",
       "      <td>48000</td>\n",
       "      <td>85000</td>\n",
       "      <td>66500.0</td>\n",
       "      <td>37000</td>\n",
       "      <td>The candidate should be having the below menti...</td>\n",
       "      <td>3.5</td>\n",
       "      <td>Medium Rated</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ASSISTANT MANAGER DATA SCIENTIST</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12390</td>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Lead Data Scientist</td>\n",
       "      <td>58000</td>\n",
       "      <td>103000</td>\n",
       "      <td>80500.0</td>\n",
       "      <td>45000</td>\n",
       "      <td>The candidates  role would be:\\nTo primarily l...</td>\n",
       "      <td>4.5</td>\n",
       "      <td>High Rated</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LEAD DATA SCIENTIST</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    S.No       Job Class                                   Job Title  \\\n",
       "0  12382    Data Analyst  Architect Data Analysis of Research Domain   \n",
       "1  12389  Data Scientist          Assistant Manager - Data Scientist   \n",
       "2  12390  Data Scientist                         Lead Data Scientist   \n",
       "\n",
       "   Min_Salary_USD  Max_Salary_USD  Average Salary_USD  Range_salary_USD  \\\n",
       "0           52925           93000             72962.5             40075   \n",
       "1           48000           85000             66500.0             37000   \n",
       "2           58000          103000             80500.0             45000   \n",
       "\n",
       "                                     Job Description  Rating   Rating Band  \\\n",
       "0  Th candidate should be  able to perform the fo...     3.5  Medium Rated   \n",
       "1  The candidate should be having the below menti...     3.5  Medium Rated   \n",
       "2  The candidates  role would be:\\nTo primarily l...     4.5    High Rated   \n",
       "\n",
       "   ... Competitors  Easy Apply  Location_City  Location_State  \\\n",
       "0  ...         NaN         0.0            NaN             NaN   \n",
       "1  ...         NaN         NaN            NaN             NaN   \n",
       "2  ...         NaN         NaN            NaN             NaN   \n",
       "\n",
       "   Headquarters_City  Headquarters_State LAT LON1  \\\n",
       "0                NaN                 NaN NaN  NaN   \n",
       "1                NaN                 NaN NaN  NaN   \n",
       "2                NaN                 NaN NaN  NaN   \n",
       "\n",
       "                              Clean Job Title skillsset  \n",
       "0  ARCHITECT DATA ANALYSIS OF RESEARCH DOMAIN       NaN  \n",
       "1            ASSISTANT MANAGER DATA SCIENTIST       NaN  \n",
       "2                         LEAD DATA SCIENTIST       NaN  \n",
       "\n",
       "[3 rows x 38 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: nltk in c:\\users\\rupal\\anaconda3\\lib\\site-packages (3.5)\n",
      "Requirement already satisfied: regex in c:\\users\\rupal\\anaconda3\\lib\\site-packages (from nltk) (2020.6.8)\n",
      "Requirement already satisfied: click in c:\\users\\rupal\\anaconda3\\lib\\site-packages (from nltk) (7.1.2)\n",
      "Requirement already satisfied: tqdm in c:\\users\\rupal\\anaconda3\\lib\\site-packages (from nltk) (4.47.0)\n",
      "Requirement already satisfied: joblib in c:\\users\\rupal\\anaconda3\\lib\\site-packages (from nltk) (0.16.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install nltk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import string\n",
    "import nltk\n",
    "from nltk import word_tokenize\n",
    "from nltk.corpus import stopwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "showing info https://raw.githubusercontent.com/nltk/nltk_data/gh-pages/index.xml\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nltk.download()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "newstopwords=stopwords.words(\"English\")+['TX','REMOTE','URGENT','YRS','REQUIRED','NATIONWIDE','OPPORTUNITIES','AND','ON','WITH','LEVEL','OR','HOUSTON','SAN','DIEGO','OR','ATLANTA','GA','FOR','NORTH','AMERICAN','NY','MULTIPLE','POSITIONS','ONLY','OPPORTUNITY','FUTURE','YEAR','NYC','MONTH','ROSTER','DIRECT','HIRE','MAY','RESIDE','ANYWHERE','IN','THE','US','UP','TO','TRAVEL','IS','YEARS','NEED','TEXAS','DALLAS','REQUIREMENTS','XYZ','CANADA','PHILADELPHIA','FA','PA','A']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def pre_process(text): \n",
    "    \n",
    "    tokens = nltk.word_tokenize(text)\n",
    "    tokens=[t.upper() for t in tokens]\n",
    "    tokens=[ t for t in tokens if ((t.isnumeric() and len(t)==1) or ( not t.isnumeric()))]\n",
    "    tokens=[word for word in tokens if word not in newstopwords]\n",
    "    text_after_process=' '.join(tokens)\n",
    "    return(text_after_process)\n",
    "\n",
    "#Apply the function on each document\n",
    "job['Clean Job Title'] = job['Clean Job Title'].apply(pre_process)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "job['Clean Job Title']= job['Clean Job Title'].replace(['BUSINESS INTELLIGENCE','INFORMATION TECHNOLOGY','QUALITY ASSURANCE','USER EXPERIENCE','USER INTERFACE','DATA WAREHOUSE','DATA BASE','DATA QUALITY','DATA GOVERNANCE','DATA MANAGEMENT','SCIENTIST INDUSTRIAL','ACCENTURE INFRASTRUCTURE','0','1','2','3','4','PRINCIPAL'],\n",
    "    ['BI','IT','QA','UX','UI','DATA_WAREHOUSE','DATABASE','DATA_QUALITY','DATA_GOVERNANCE','DATA_MANAGEMENT','SCIENTIST_INDUSTRIAL','ACCENTURE_INFRASTRUCTURE','I','I','II','III','IV','LEAD'],regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'ARCHITECT DATA ANALYSIS OF RESEARCH DOMAIN'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job['Clean Job Title'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "job['Clean Job Title']=job['Clean Job Title'].replace(['SYSTEMS','PRODUCTS','SOLUTIONS','GAMES','ANALYTICS','FINANCE','ENGINEERING','INTERN','TRAINEE','FRESHER','ENTRY','ANALYSTS','SR','JR',' III',' II',' IV','[^A-Z0-9]I[^A-Z0-9]','[^A-Z0-9]I$','^I[^A-Z0-9]','[^A-Z0-9]V[^A-Z0-9]','[^A-Z0-9]V$','^V[^A-Z0-9]'],\n",
    "                                                      ['SYSTEM','PRODUCT','ANALYTIC','SOLUTION','GAME','FINANCIAL','ENGINEER','JUNIOR','JUNIOR','JUNIOR','JUNIOR','ANALYST','SENIOR','JUNIOR',' SENIOR',' SENIOR',' LEAD','JUNIOR',' JUNIOR','JUNIOR ','LEAD',' LEAD','LEAD '],regex=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'ARCHITECT DATA ANALYSIS OF RESEARCH DOMAIN'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job['Clean Job Title'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "job['Clean Job Title']= job['Clean Job Title'].replace(\n",
    "    ['JUNIOR JUNIOR','JUNIORJUNIOR','SENIOR SENIOR','SENIORSENIOR'],['JUNIOR',' JUNIOR','SENIOR',' SENIOR'],regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "job['Clean Job Title']= job['Clean Job Title'].replace(\n",
    "    ['DATA ANALYST JUNIOR','DATA ANALYST SENIOR','BUSINESS ANALYST JUNIOR','BUSINESS ANALYST SENIOR','DATA ENGINEER JUNIOR','BUSINESS SYSTEM ANALYST JUNIOR','DATA SCIENTIST JUNIOR','DATA ENGINEER SENIOR','DATA ARCHITECT JUNIOR','BUSINESS SYSTEM ANALYST SENIOR','BI ANALYST JUNIOR','PROGRAM ANALYST JUNIOR','DATA SCIENTIST SENIOR','SOFTWARE ENGINEER JUNIOR','SCIENTIST JUNIOR','DATA ANALYST LEAD','BUSINESS ANALYST LEAD','DATA ENGINEER LEAD','BUSINESS SYSTEM ANALYST LEAD','DATA SCIENTIST LEAD'],\n",
    "    ['JUNIOR DATA ANALYST','SENIOR DATA ANALYST','JUNIOR BUSINESS ANALYST','SENIOR BUSINESS ANALYST','JUNIOR DATA ENGINEER','JUNIOR BUSINESS SYSTEM ANALYST','JUNIOR DATA SCIENTIST','SENIOR DATA ENGINEER','JUNIOR DATA ARCHITECT','SENIOR BUSINESS SYSTEM ANALYST','JUNIOR BI ANALYST','JUNIOR PROGRAM ANALYST','SENIOR DATA SCIENTIST','JUNIOR SOFTWARE ENGINEER','JUNIOR SCIENTIST','LEAD DATA ANALYST','LEAD BUSINESS ANALYST','LEAD DATA ENGINEER','LEAD BUSINESS SYSTEM ANALYST','LEAD DATA SCIENTIST'],regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "jobCount=job.groupby('Clean Job Title')[['Clean Job Title']].count().rename(\n",
    "    columns={'Clean Job Title':'Count'}).sort_values('Count',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(jobCount)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "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>Count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Clean Job Title</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ARCHITECT DATA ANALYSIS OF RESEARCH DOMAIN</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASSISTANT MANAGER DATA SCIENTIST</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LEAD DATA SCIENTIST</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Count\n",
       "Clean Job Title                                  \n",
       "ARCHITECT DATA ANALYSIS OF RESEARCH DOMAIN      1\n",
       "ASSISTANT MANAGER DATA SCIENTIST                1\n",
       "LEAD DATA SCIENTIST                             1"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jobCount[0:50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "job.to_excel('V3.0 Project OA Data_excel_Testingdata.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}