Almost every big and mid sized organizations utilities a support ticket system. ServiceNow is one of the most commonly used such a ticket system across all organizations.
A support request is raised by users in service now for any hardware or software related issues-like procurement of new desktop, creation open office 365 account, etc. These tickets are then assigned to the appropriate technical support team by L1 engineer.
Our aim is to automate the work of this L1 engineer and to assign the tickets to the appropriate technical support team.
Now coming to 2nd step i.e. data collection. And we are lucky here as ServiceNow has a functionality to export data in the form of CSV files.
- 1st we will remove all filters. Right click on hamburger icon →select filters →None.
- Now right click on hamburger icon above any column →Export →CSV.
That’s it. We have our data.
# To read and manipulate dataframe
import pandas as pd# For EDA
import matplotlib.pyplot as plt
import seaborn as sns
from wordcloud import WordCloud, STOPWORDSstopwords = set(STOPWORDS)
%matplotlib inline
For starting I have imported some modules and later we can import more when required.
As data is in tabular form, I am using Pandas to read and manipulate our data.
For EDA I have used matplotlib.pyplot, seaborn and wordcloud.
Also I have created a variable called stopwords which will store all English stopwords such as the, is, etc.
%matplotlib inline command will help to show all the charts in notebook itself.
Now we need to read our data.
incident_df = pd.read_csv('incident.csv', encoding='ISO-8859-1')
I have used pd.read_csv to read csv files and given filename and encoding type as parameter. My file is present in root directory, if you have in some other location then you can mention full path.
Once we have our data ready now we need to clean it I Have kept only three columns that is short description description and assignment group and dropped rest of the columns which is not required.
df1 = incident_df[[‘Short description’,’Description’,’Assignment group’]]
First we need to check missing values for that I have created a function which take dataframe as an input and will return number of value and percentage in the form of dataframe.
# Function to check and create dataframe for missing valuedef check_miss(df):'''This function will check number and % of missing value in each columnif it is more than 0 then it will return a dataframe'''#Column which have missing valuemiss_col = [col for col in df.columns if df[col].isnull().sum()>0]#DataFrame that contains no. and % of missing valuemiss_df = pd.DataFrame([df[miss_col].isnull().sum(),df[miss_col].isnull().mean()*100],index=['Missing Value','Missing Value %'])return miss_dfcheck_miss(df1)
As we have very less percentage of missing value then we can directly drop those rows for that I have created a copy of data frame and have used dropna command which will drop rows with missing values.
df2 = df1.copy()df2.dropna(inplace=True)
There are two ways to create a ticket. Either user can call L1 team and they can raise ticket on behalf of user or user himself/herself can raise a ticket. When L1 team raise a ticket they follow format which contain a lot of unnecessary text description which I have removed using regex.
df3 = df2.copy()df3['Description'] = df2['Description'].apply(lambda x: x[:x.find('Issue Status:')])
Now we can start our EDA process.