I. Exploratory Data Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AUTOMATIC TICKET ASSIGNMENT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Milestone 1 : Data pre-processing and EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.preprocessing import LabelEncoder,scale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data='DATA'\n",
    "raw_data_folder='RAW_DATA'\n",
    "processed_data_folder='PROCESSED_DATA'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "if data not in os.listdir(os.getcwd()):\n",
    "    os.makedirs(os.path.join(data))\n",
    "DATA_PATH=os.path.join(data)\n",
    "if raw_data_folder not in os.listdir(os.path.join(DATA_PATH)):\n",
    "    os.makedirs(os.path.join(DATA_PATH,raw_data_folder))\n",
    "RAW_DATA_PATH=os.path.join(DATA_PATH,raw_data_folder)\n",
    "if processed_data_folder not in os.listdir(os.path.join(DATA_PATH)):\n",
    "    os.makedirs(os.path.join(DATA_PATH,processed_data_folder))\n",
    "PROCESSED_DATA_PATH=os.path.join(DATA_PATH,processed_data_folder)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the csv file and see some records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Short description</th>\n",
       "      <th>Description</th>\n",
       "      <th>Caller</th>\n",
       "      <th>Assignment group</th>\n",
       "      <th>Lang_Short_Description</th>\n",
       "      <th>Lang_Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>login issue</td>\n",
       "      <td>-verified user details.(employee# &amp; manager na...</td>\n",
       "      <td>spxjnwir pjlcoqds</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>outlook</td>\n",
       "      <td>\\n\\nreceived from: hmjdrvpb.komuaywn@gmail.com...</td>\n",
       "      <td>hmjdrvpb komuaywn</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cant log in to vpn</td>\n",
       "      <td>\\n\\nreceived from: eylqgodm.ybqkwiam@gmail.com...</td>\n",
       "      <td>eylqgodm ybqkwiam</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>unable to access hr_tool page</td>\n",
       "      <td>unable to access hr_tool page</td>\n",
       "      <td>xbkucsvz gcpydteq</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>skype error</td>\n",
       "      <td>skype error</td>\n",
       "      <td>owlgqjme qhcozdfx</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Short description  \\\n",
       "0                    login issue   \n",
       "1                        outlook   \n",
       "2             cant log in to vpn   \n",
       "3  unable to access hr_tool page   \n",
       "4                    skype error   \n",
       "\n",
       "                                         Description             Caller  \\\n",
       "0  -verified user details.(employee# & manager na...  spxjnwir pjlcoqds   \n",
       "1  \\n\\nreceived from: hmjdrvpb.komuaywn@gmail.com...  hmjdrvpb komuaywn   \n",
       "2  \\n\\nreceived from: eylqgodm.ybqkwiam@gmail.com...  eylqgodm ybqkwiam   \n",
       "3                      unable to access hr_tool page  xbkucsvz gcpydteq   \n",
       "4                                        skype error  owlgqjme qhcozdfx   \n",
       "\n",
       "  Assignment group Lang_Short_Description Lang_Description  \n",
       "0            GRP_0                     en               en  \n",
       "1            GRP_0                     en               en  \n",
       "2            GRP_0                     en               en  \n",
       "3            GRP_0                     en               en  \n",
       "4            GRP_0                     en               en  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df=pd.read_csv(os.path.join(RAW_DATA_PATH,'Input Data Translated.csv'), encoding = 'latin-1')\n",
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8500, 6)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 8500 rows and 4 columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## EDA\n",
    "\n",
    "Understanding of the columns is important in order to understand whether to retain/ drop some of them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Caller Column\n",
    "\n",
    "This perhaps indicates the person who filed the ticket; it would be useful to understand the number of such callers (If there are many unique callers with low sample bases, then it might not make sense to include this column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2950"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.Caller.nunique() # Understanding the unique no. of callers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thus across 8500 Rows, there are 2950 unique callers and hence each caller is unlikely to have a sufficient base for analysis. However would be interesting to check the counts of at least the callers who put in tickets more frequently"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bpctwhsn kzqsbmtp    810\n",
       "ZkBogxib QsEJzdZO    151\n",
       "fumkcsji sarmtlhy    134\n",
       "rbozivdq gmlhrtvp     87\n",
       "rkupnshb gsmzfojw     71\n",
       "jloygrwh acvztedi     64\n",
       "spxqmiry zpwgoqju     63\n",
       "oldrctiu bxurpsyi     57\n",
       "olckhmvx pcqobjnd     54\n",
       "dkmcfreg anwmfvlg     51\n",
       "jyoqwxhz clhxsoqy     51\n",
       "efbwiadp dicafxhv     45\n",
       "afkstcev utbnkyop     32\n",
       "gzhapcld fdigznbk     30\n",
       "mnlazfsr mtqrkhnx     28\n",
       "uvrbhlnt bjrmalzi     27\n",
       "entuakhp xrnhtdmk     25\n",
       "jionmpsf wnkpzcmv     24\n",
       "vzqomdgt jwoqbuml     24\n",
       "bozdftwx smylqejw     23\n",
       "rxoynvgi ntgdsehl     21\n",
       "qasdhyzm yuglsrwx     21\n",
       "utyeofsk rdyzpwhi     21\n",
       "vbwszcqn nlbqsuyv     19\n",
       "ugyothfz ugrmkdhx     17\n",
       "hbmwlprq ilfvyodx     17\n",
       "vfrdxtqw jfbmsenz     16\n",
       "niptbwdq csenjruz     16\n",
       "pfzxecbo ptygkvzl     16\n",
       "ughzilfm cfibdamq     16\n",
       "                    ... \n",
       "zkhmvnib sugzyvhp      1\n",
       "xaykwtrf amlswjfr      1\n",
       "atdclmyi wqxzaysu      1\n",
       "szcbhvwe edpouqjl      1\n",
       "sagfvdeb kfqtvgxl      1\n",
       "puxyqwjn zgmnsvjx      1\n",
       "cwryvksu cedsairg      1\n",
       "cvnzhbfa qcvzyrgo      1\n",
       "suyighpt wifujdvq      1\n",
       "trxsychl xamcuong      1\n",
       "recjqtbg xcyuetmj      1\n",
       "zikuvsat spxycizr      1\n",
       "wetajgsq ezmcqkbp      1\n",
       "qdztknml hpcxnyrq      1\n",
       "rvsbtxue cdrwsymj      1\n",
       "jsmhzqpo bpexofya      1\n",
       "ibcsejvn denhyvmr      1\n",
       "anzdtxso trisfkwe      1\n",
       "vwhkldyp efblthqc      1\n",
       "rcmziuob xhblozqe      1\n",
       "bsxvtpke vbfcashd      1\n",
       "tzbqlfao dhlbputy      1\n",
       "nozahtbr ubznqpsy      1\n",
       "pstlrmvf jokbeqnp      1\n",
       "dbroehva dgcyvxhw      1\n",
       "vbihmtzo mphkitvr      1\n",
       "xjeblpst dohnewjg      1\n",
       "melhduty gqchtedl      1\n",
       "nmtekfrz tjxzeuqm      1\n",
       "abyfsqod baezguci      1\n",
       "Name: Caller, Length: 2950, dtype: int64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.Caller.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are only 11 callers who have a sample base > 50; While the top caller has 810 records, the sample per caller diminishes rapidly; thus there does not seem to be any value in retaining this column and it will be dropped "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see the description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>count</th>\n",
       "      <th>unique</th>\n",
       "      <th>top</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Short description</th>\n",
       "      <td>8498</td>\n",
       "      <td>7386</td>\n",
       "      <td>password reset</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Description</th>\n",
       "      <td>8497</td>\n",
       "      <td>7751</td>\n",
       "      <td>the</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Caller</th>\n",
       "      <td>8500</td>\n",
       "      <td>2950</td>\n",
       "      <td>bpctwhsn kzqsbmtp</td>\n",
       "      <td>810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assignment group</th>\n",
       "      <td>8500</td>\n",
       "      <td>74</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>3976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Language</th>\n",
       "      <td>8497</td>\n",
       "      <td>27</td>\n",
       "      <td>en</td>\n",
       "      <td>7733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  count unique                top  freq\n",
       "Short description  8498   7386     password reset    48\n",
       "Description        8497   7751                the    56\n",
       "Caller             8500   2950  bpctwhsn kzqsbmtp   810\n",
       "Assignment group   8500     74              GRP_0  3976\n",
       "Language           8497     27                 en  7733"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.describe().transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above description says below observations:\n",
    "1. 'Short description' column has 2 blank values and 'Desciption' column has 3 blank values\n",
    "2. Out of 8498 there are 7386 unique values in 'Short description' column, out of 8497 there are 7751 unique values in 'Description' column and out of 8500 there are 74 unique values in 'Assignment group' column which is target column. Also there are 27 unique languages in the database\n",
    "3. \"Password reset\" is the top frequent value with 48 occurrences in 'Short description' column, \"the\" is the top frequent value with 56 occurrences in 'Description' column and \"GRP_0\" is the group which has maximum assignment of tickets i.e 3976. English is the most common language with 7733 records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comments: In the Description Column 'the' by itself does not add any value since it would get removed as a stop word; hence useful to see what is Short Description that is in the rows with \"the\" in the Description Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "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>Short description</th>\n",
       "      <th>Description</th>\n",
       "      <th>Caller</th>\n",
       "      <th>Assignment group</th>\n",
       "      <th>Language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1049</th>\n",
       "      <td>reset passwords for soldfnbq uhnbsvqd using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>soldfnbq uhnbsvqd</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1054</th>\n",
       "      <td>reset passwords for fygrwuna gomcekzi using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>fygrwuna gomcekzi</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1144</th>\n",
       "      <td>reset passwords for wvdxnkhf jirecvta using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>wvdxnkhf jirecvta</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1184</th>\n",
       "      <td>reset passwords for pxvjczdt kizsjfpq using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>pxvjczdt kizsjfpq</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1292</th>\n",
       "      <td>reset passwords for cubdsrml znewqgop using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>cubdsrml znewqgop</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1476</th>\n",
       "      <td>reset passwords for bnoupaki cpeioxdz using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>bnoupaki cpeioxdz</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1558</th>\n",
       "      <td>reset passwords for usa feathers using passwor...</td>\n",
       "      <td>the</td>\n",
       "      <td>lmqysdec ljvbnpqw</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1693</th>\n",
       "      <td>reset passwords for eglavnhx uprodleq using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>eglavnhx uprodleq</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1834</th>\n",
       "      <td>reset passwords for hybiaxlk lawptzir using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>hybiaxlk lawptzir</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1850</th>\n",
       "      <td>reset passwords for fylrosuk kedgmiul using pa...</td>\n",
       "      <td>the</td>\n",
       "      <td>fylrosuk kedgmiul</td>\n",
       "      <td>GRP_17</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Short description Description  \\\n",
       "1049  reset passwords for soldfnbq uhnbsvqd using pa...         the   \n",
       "1054  reset passwords for fygrwuna gomcekzi using pa...         the   \n",
       "1144  reset passwords for wvdxnkhf jirecvta using pa...         the   \n",
       "1184  reset passwords for pxvjczdt kizsjfpq using pa...         the   \n",
       "1292  reset passwords for cubdsrml znewqgop using pa...         the   \n",
       "1476  reset passwords for bnoupaki cpeioxdz using pa...         the   \n",
       "1558  reset passwords for usa feathers using passwor...         the   \n",
       "1693  reset passwords for eglavnhx uprodleq using pa...         the   \n",
       "1834  reset passwords for hybiaxlk lawptzir using pa...         the   \n",
       "1850  reset passwords for fylrosuk kedgmiul using pa...         the   \n",
       "\n",
       "                 Caller Assignment group Language  \n",
       "1049  soldfnbq uhnbsvqd           GRP_17       en  \n",
       "1054  fygrwuna gomcekzi           GRP_17       en  \n",
       "1144  wvdxnkhf jirecvta           GRP_17       en  \n",
       "1184  pxvjczdt kizsjfpq           GRP_17       en  \n",
       "1292  cubdsrml znewqgop           GRP_17       en  \n",
       "1476  bnoupaki cpeioxdz           GRP_17       en  \n",
       "1558  lmqysdec ljvbnpqw           GRP_17       en  \n",
       "1693  eglavnhx uprodleq           GRP_17       en  \n",
       "1834  hybiaxlk lawptzir           GRP_17       en  \n",
       "1850  fylrosuk kedgmiul           GRP_17       en  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df[new_df.Description == 'the'].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most of the \"the\" in Description seem to be associated with \"reset passwords\" in the Short Description Column; thus in order to develop a reasonable model it points to the fact that it would be good to concatenate both these columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for the Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Short description    object\n",
       "Description          object\n",
       "Caller               object\n",
       "Assignment group     object\n",
       "Language             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see number of tickets assigned to each group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "GRP_0     3976\n",
      "GRP_8      661\n",
      "GRP_24     289\n",
      "GRP_12     257\n",
      "GRP_9      252\n",
      "GRP_2      241\n",
      "GRP_19     215\n",
      "GRP_3      200\n",
      "GRP_6      184\n",
      "GRP_13     145\n",
      "GRP_10     140\n",
      "GRP_5      129\n",
      "GRP_14     118\n",
      "GRP_25     116\n",
      "GRP_33     107\n",
      "GRP_4      100\n",
      "GRP_29      97\n",
      "GRP_18      88\n",
      "GRP_16      85\n",
      "GRP_17      81\n",
      "GRP_31      69\n",
      "GRP_7       68\n",
      "GRP_34      62\n",
      "GRP_26      56\n",
      "GRP_40      45\n",
      "GRP_28      44\n",
      "GRP_41      40\n",
      "GRP_15      39\n",
      "GRP_30      39\n",
      "GRP_42      37\n",
      "          ... \n",
      "GRP_44      15\n",
      "GRP_36      15\n",
      "GRP_50      14\n",
      "GRP_53      11\n",
      "GRP_65      11\n",
      "GRP_52       9\n",
      "GRP_55       8\n",
      "GRP_51       8\n",
      "GRP_59       6\n",
      "GRP_49       6\n",
      "GRP_46       6\n",
      "GRP_43       5\n",
      "GRP_32       4\n",
      "GRP_66       4\n",
      "GRP_38       3\n",
      "GRP_56       3\n",
      "GRP_58       3\n",
      "GRP_68       3\n",
      "GRP_63       3\n",
      "GRP_54       2\n",
      "GRP_72       2\n",
      "GRP_71       2\n",
      "GRP_57       2\n",
      "GRP_69       2\n",
      "GRP_73       1\n",
      "GRP_61       1\n",
      "GRP_67       1\n",
      "GRP_64       1\n",
      "GRP_70       1\n",
      "GRP_35       1\n",
      "Name: Assignment group, Length: 74, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(new_df['Assignment group'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Group variable has a very long tail and it might not be beneficial to keep the ones that have a low record base; One could potentially club the groups with low record base into 1 group called as \"Others\". Classic statistics state that beyond a sample of 30, distributions tend to be normal and thus this value could be set as a cut-off for a group to be included and all groups with a base of <30 can be combined into \"Others\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check for null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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>Short description</th>\n",
       "      <th>Description</th>\n",
       "      <th>Caller</th>\n",
       "      <th>Assignment group</th>\n",
       "      <th>Language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>False</th>\n",
       "      <td>8498</td>\n",
       "      <td>8497</td>\n",
       "      <td>8500.0</td>\n",
       "      <td>8500.0</td>\n",
       "      <td>8497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Short description  Description  Caller  Assignment group  Language\n",
       "False               8498         8497  8500.0            8500.0      8497\n",
       "True                   2            3     NaN               NaN         3"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.isnull().apply(pd.value_counts)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 2 null values in 'Short description' column and 3 in 'Description' column.There is no null value in target column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If there are rows, with Null values for both, then we would need to remove them; however we need to check for the same"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>Short description</th>\n",
       "      <th>Description</th>\n",
       "      <th>Caller</th>\n",
       "      <th>Assignment group</th>\n",
       "      <th>Language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2604</th>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\nreceived from: ohdrnswl.rezuibdt@gmail.com...</td>\n",
       "      <td>ohdrnswl rezuibdt</td>\n",
       "      <td>GRP_34</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3383</th>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n-connected to the user system using teamview...</td>\n",
       "      <td>qftpazns fxpnytmk</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4395</th>\n",
       "      <td>i am locked out of skype</td>\n",
       "      <td>NaN</td>\n",
       "      <td>viyglzfo ajtfzpkb</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6371</th>\n",
       "      <td>authorization add/delete members</td>\n",
       "      <td>NaN</td>\n",
       "      <td>hpmwliog kqtnfvrl</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7397</th>\n",
       "      <td>browser issue :</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fgejnhux fnkymoht</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Short description  \\\n",
       "2604                               NaN   \n",
       "3383                               NaN   \n",
       "4395          i am locked out of skype   \n",
       "6371  authorization add/delete members   \n",
       "7397                   browser issue :   \n",
       "\n",
       "                                            Description             Caller  \\\n",
       "2604  \\n\\nreceived from: ohdrnswl.rezuibdt@gmail.com...  ohdrnswl rezuibdt   \n",
       "3383  \\n-connected to the user system using teamview...  qftpazns fxpnytmk   \n",
       "4395                                                NaN  viyglzfo ajtfzpkb   \n",
       "6371                                                NaN  hpmwliog kqtnfvrl   \n",
       "7397                                                NaN  fgejnhux fnkymoht   \n",
       "\n",
       "     Assignment group Language  \n",
       "2604           GRP_34       en  \n",
       "3383            GRP_0       en  \n",
       "4395            GRP_0      NaN  \n",
       "6371            GRP_0      NaN  \n",
       "7397            GRP_0      NaN  "
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's look at the rows with null values\n",
    "new_df[pd.isnull(new_df).any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are no records which have Null values for both Short Description and Description; Given that for prediction it is likely that both Columns would be used, replacement of Null values can be done with a blank string (as it is likely that both Short Description and Description would be concatenated while building the model)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "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>Short description</th>\n",
       "      <th>Description</th>\n",
       "      <th>Caller</th>\n",
       "      <th>Assignment group</th>\n",
       "      <th>Language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Short description, Description, Caller, Assignment group, Language]\n",
       "Index: []"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# NULL replacement\n",
    "new_df.fillna(str(), inplace=True)\n",
    "new_df[pd.isnull(new_df).any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now there is no null value in any column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for Duplicates across Short Description and Description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_common=new_df[new_df[[\"Short description\",\"Description\"]].apply(lambda x : x[0]==x[1],axis=1)].reset_index(drop=True).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>Short description</th>\n",
       "      <th>Description</th>\n",
       "      <th>Caller</th>\n",
       "      <th>Assignment group</th>\n",
       "      <th>Language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>unable to access hr_tool page</td>\n",
       "      <td>unable to access hr_tool page</td>\n",
       "      <td>xbkucsvz gcpydteq</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>skype error</td>\n",
       "      <td>skype error</td>\n",
       "      <td>owlgqjme qhcozdfx</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>unable to log in to engineering tool and skype</td>\n",
       "      <td>unable to log in to engineering tool and skype</td>\n",
       "      <td>eflahbxn ltdgrvkz</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ticket_no1550391- employment status - new non-...</td>\n",
       "      <td>ticket_no1550391- employment status - new non-...</td>\n",
       "      <td>eqzibjhw ymebpoih</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>unable to disable add ins on outlook</td>\n",
       "      <td>unable to disable add ins on outlook</td>\n",
       "      <td>mdbegvct dbvichlg</td>\n",
       "      <td>GRP_0</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Short description  \\\n",
       "0                      unable to access hr_tool page   \n",
       "1                                        skype error   \n",
       "2     unable to log in to engineering tool and skype   \n",
       "3  ticket_no1550391- employment status - new non-...   \n",
       "4               unable to disable add ins on outlook   \n",
       "\n",
       "                                         Description             Caller  \\\n",
       "0                      unable to access hr_tool page  xbkucsvz gcpydteq   \n",
       "1                                        skype error  owlgqjme qhcozdfx   \n",
       "2     unable to log in to engineering tool and skype  eflahbxn ltdgrvkz   \n",
       "3  ticket_no1550391- employment status - new non-...  eqzibjhw ymebpoih   \n",
       "4               unable to disable add ins on outlook  mdbegvct dbvichlg   \n",
       "\n",
       "  Assignment group Language  \n",
       "0            GRP_0       en  \n",
       "1            GRP_0       en  \n",
       "2            GRP_0       en  \n",
       "3            GRP_0       en  \n",
       "4            GRP_0       en  "
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_common.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2889 entries, 0 to 2888\n",
      "Data columns (total 5 columns):\n",
      "Short description    2889 non-null object\n",
      "Description          2889 non-null object\n",
      "Caller               2889 non-null object\n",
      "Assignment group     2889 non-null object\n",
      "Language             2889 non-null object\n",
      "dtypes: object(5)\n",
      "memory usage: 112.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df_common.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comments: this shows that out of the 8500 records, 2889 have the same information in both Short description and Description - This would point out that it is better to concatenate the two columns for the modeling stage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Language as a column is not required for the modeling but was just for understanding; dropping the Language Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "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>Short description</th>\n",
       "      <th>Description</th>\n",
       "      <th>Caller</th>\n",
       "      <th>Assignment group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>login issue</td>\n",
       "      <td>-verified user details.(employee# &amp; manager na...</td>\n",
       "      <td>spxjnwir pjlcoqds</td>\n",
       "      <td>GRP_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>outlook</td>\n",
       "      <td>\\n\\nreceived from: hmjdrvpb.komuaywn@gmail.com...</td>\n",
       "      <td>hmjdrvpb komuaywn</td>\n",
       "      <td>GRP_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cant log in to vpn</td>\n",
       "      <td>\\n\\nreceived from: eylqgodm.ybqkwiam@gmail.com...</td>\n",
       "      <td>eylqgodm ybqkwiam</td>\n",
       "      <td>GRP_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>unable to access hr_tool page</td>\n",
       "      <td>unable to access hr_tool page</td>\n",
       "      <td>xbkucsvz gcpydteq</td>\n",
       "      <td>GRP_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>skype error</td>\n",
       "      <td>skype error</td>\n",
       "      <td>owlgqjme qhcozdfx</td>\n",
       "      <td>GRP_0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Short description  \\\n",
       "0                    login issue   \n",
       "1                        outlook   \n",
       "2             cant log in to vpn   \n",
       "3  unable to access hr_tool page   \n",
       "4                    skype error   \n",
       "\n",
       "                                         Description             Caller  \\\n",
       "0  -verified user details.(employee# & manager na...  spxjnwir pjlcoqds   \n",
       "1  \\n\\nreceived from: hmjdrvpb.komuaywn@gmail.com...  hmjdrvpb komuaywn   \n",
       "2  \\n\\nreceived from: eylqgodm.ybqkwiam@gmail.com...  eylqgodm ybqkwiam   \n",
       "3                      unable to access hr_tool page  xbkucsvz gcpydteq   \n",
       "4                                        skype error  owlgqjme qhcozdfx   \n",
       "\n",
       "  Assignment group  \n",
       "0            GRP_0  \n",
       "1            GRP_0  \n",
       "2            GRP_0  \n",
       "3            GRP_0  \n",
       "4            GRP_0  "
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df=new_df.drop('Language',axis=1)\n",
    "new_df.shape\n",
    "new_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Understanding thus far\n",
    "\n",
    "1. All text has been converted into English\n",
    "2. Need to concatenate both the short Description and Description Columns\n",
    "3. Long tails in \"Assignment group\" column need to be taken care of\n",
    "4. Null values solved for by replacing with blank string\n",
    "5. Need to remove email id's from text"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}