<jupyter_start><jupyter_text>{{cookiecutter.project_name}}{{cookiecutter.description}} Data Sources- file1 : Description of where this file came from Changes- {% now 'utc', '%m-%d-%Y' %} : Started project<jupyter_code>import pandas as pd
import numpy as np
import glob
import datetime as dt
import pickle
import os
from pandas import ExcelWriter
import re
from zipfile import ZipFile
import matplotlib.pyplot as plt
import seaborn as sns
import datetime<jupyter_output><empty_output><jupyter_text>Functions<jupyter_code>def categorize_columns(df, numunique= 205):
    '''clean names of columns and caegorize object columns if unique is less than , numunique
       downcast for int and floats'''
    # https://stackoverflow.com/questions/30763351/removing-space-in-dataframe-python
    df.columns = [x.strip() for x in df.columns]
    df.rename(columns = lambda x: x.replace(" ","_"), inplace= True)
    df.rename(columns = lambda x: x.replace(".","_"), inplace= True)
    df.rename(columns = lambda x: x.replace("&","and"), inplace= True)
    df.rename(columns = lambda x: x.replace("(",""), inplace= True)
    df.rename(columns = lambda x: x.replace(")",""), inplace= True)
    df.rename(columns = lambda x: x.replace("/",""), inplace= True)
    df.rename(columns = lambda x: x.replace("-","_"), inplace= True)
    df.rename(columns = lambda x: x.replace("+","_plus_"), inplace= True)
    for y in df.columns:
        #categorize columns
        if df[y].dtype == np.object:
            if len(df[y].unique()) <= numunique:
                print('converted ' + y + ' ' +str(df[y].dtype) + ' records=' + str(len(df[y].unique())))
                df[y] = df[y].astype('category')
        elif(df[y].dtype == np.float64 or df[y].dtype == np.int64):
            df[y] = pd.to_numeric(df[y], downcast='unsigned')
            print('DOWNCAST ' +  y + ' ' +str(df[y].dtype))
    return df

def add_business_days(from_date, ndays):
    '''Consider weekends when add days to a date'''
    business_days_to_add = abs(ndays)
    current_date = from_date
    sign = ndays/abs(ndays)
    while business_days_to_add > 0:
        current_date += datetime.timedelta(sign * 1)
        weekday = current_date.weekday()
        if weekday >= 5: # sunday = 6
            continue
        business_days_to_add -= 1
    return current_date<jupyter_output><empty_output><jupyter_text>File Locations<jupyter_code>fileticketsname = input('Tickets file name: ')
fileappsname = input('Apps file name: ')
print(os.getcwd())
originalpath = (os.getcwd())
print(originalpath)

os.chdir(originalpath)
#os.chdir('..')
path = os.getcwd()
print(path)

#filename = os.path.join(path, 'data','Power - Tickets - 2018 - v2.1-2.xlsx')
filename = os.path.join(path, 'data','raw',fileticketsname)
fileapps = os.path.join(path, 'data', 'raw', fileappsname)

#outputfiles
today = datetime.datetime.today()
directory_name = '{{cookiecutter.directory_name}}'
fileoriginaltickets = os.path.join(path, 'data','processed',
                                   directory_name + '_tickets{:%m%d%y}.pkl').format(today)
fileoriginalapps = os.path.join(path, 'data','processed',
                                directory_name + '_apps{:%m%d%y}.pkl').format(today)<jupyter_output><empty_output><jupyter_text>Read files from Excel or CSV<jupyter_code># if it is a CSV file ->tickets_file = pd.read_csv(filename)
tickets_file = pd.ExcelFile(filename, 
                            converters= {'opened_at': pd.to_datetime, 'closed_at': pd.to_datetime})
print(tickets_file.sheet_names)
#get list of countries ina dataframe

#read applications file
apps_file = pd.ExcelFile(fileapps)
print(apps_file.sheet_names)

sheetname= input('Sheet to load for tickets: ')
tickets_final = tickets_file.parse(sheetname, skiprows=0)
print(len(tickets_final))

sheetname= input('Sheet to load foro apps: ')
apps_final = apps_file.parse(sheetname, skiprows=0)
print(len(apps_final))<jupyter_output><empty_output><jupyter_text>Column Cleanup- Remove all leading and trailing spaces- Get columns names and determine columns to rename df.columns- Rename the columns for consistency.<jupyter_code>#clean name columns, categorize columns, downcast columns
tickets_final= categorize_columns(tickets_final)
apps_final= categorize_columns(apps_final)
cols_to_rename = {'col1': 'New_Name'}
tickets_final.rename(columns=cols_to_rename, inplace=True)
tickets_final.columns<jupyter_output><empty_output><jupyter_text>Clean Up Data Types<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>Data Manipulation<jupyter_code>#For tickets file
tickets_final.drop_duplicates(['number'], inplace= True)
print(len(tickets_final))
tickets_final.dropna(subset=['App_Bucket'], inplace= True)
print(len(tickets_final))

#add fields with correct hrs in EST
tickets_final['x_opened_at_est'] = tickets_final['opened_at'] -  pd.to_timedelta(5, unit='h')
tickets_final['x_closed_at_est'] = tickets_final['closed_at'] -  pd.to_timedelta(5, unit='h')

tickets_final['x_yearmonth']= tickets_final['x_opened_at_est'].dt.strftime('%Y%m')
tickets_final['x_dayweek']= tickets_final['x_opened_at_est'].dt.strftime('%w')

date = pd.to_datetime(tickets_final['x_opened_at_est'])
tickets_final['x_hourday']= date.dt.hour
#tickets_final['hourday']= tickets_final['opened_at_est'].dt.strftime('%H')
tickets_final['x_year']= tickets_final['x_opened_at_est'].dt.strftime('%Y')

#set distribution
'''adjust hourday to EST timezone and separate in 3 bins
0,1  ,2,3,4,5,6,7,  8,9,10,11,12,13,14,15,16,17,18,19,  20,21,22,23,0, 1,   
     '2-7'          '8-20'                              '20-2'           
'''
tickets_final['x_bins_day']= pd.cut(tickets_final['x_hourday'], bins=[-float("inf"),1,7,19], 
                               labels=['20-2','2-8','8-20'])
tickets_final['x_bins_day']= tickets_final['x_bins_day'].fillna('20-2')

#cycletime
tickets_final['x_cycletime'] = tickets_final['x_closed_at_est'] - tickets_final['x_opened_at_est']
tickets_final['x_cycletime']=tickets_final['x_cycletime']/np.timedelta64(1,'D')
tickets_final['x_cycletime'] = tickets_final['x_cycletime'].fillna(value=0, downcast='infer')

#clean SSO where SSO = admin update to NaN
#tickets_final['SSO_closed_by_name'] = tickets_final['closed_by_name'].str.extract('(\d\d\d\d\d\d\d\d\d)', expand=True)
tickets_final['x_closed_by_user_name']= tickets_final['closed_by_user_name']
#tickets_final.loc[tickets_final['x_closed_by_user_name'] == 'admin' ,  'x_closed_by_user_name'] = np.nan
#tickets_final['x_closed_by_user_name'] = pd.to_numeric(tickets_final['x_closed_by_user_name'], downcast=None, errors= 'coerce')

tickets_final['x_Vendor_Closeassign'] = tickets_final['Vendor_Closeassign'].str.lower()

#column to identify access management
tickets_final['x_access_management'] = 0

#add calculate SLA , due_date and In_SLA
days_sla= {
    '4 - Low' : 5,
    '1 - Critical': .5,
    '2 - High': 1,
    '3 - Moderate' : 5 
}
tickets_final['priority'] = np.where(tickets_final['priority'] == '3 - Low', '4 - Low', tickets_final['priority'])
tickets_final['x_days_sla']= tickets_final['priority'].map(days_sla).fillna(5)

tickets_final['x_days_sla'].dtype == np.float64
#add 5 days to days_sla if it is Incident
tickets_final['x_days_sla']= np.where(tickets_final['Type']== 'Incident',
                                      tickets_final['x_days_sla'] + 5,tickets_final['x_days_sla'])

tickets_final['x_due_date']= tickets_final['x_opened_at_est'] + tickets_final['x_days_sla'].astype("timedelta64[D]")
tickets_final['x_inSLA'] = np.where(tickets_final['x_closed_at_est'] > tickets_final['x_due_date'], 0, 
                               np.where(tickets_final['x_closed_at_est'].isnull(), 0, 1))

#caclualte aging based in extract date
tickets_final['x_aging'] = np.where(tickets_final['state'].isin(['Pending', 'Work in Progress','Open', 'In Progress', 'On Hold', 'Submitted', 'Scheduled', 'Accepted']),
                                  1,0) 
tickets_final['x_agingdays']= np.where(tickets_final['x_aging'] == 1,
                                     dt.datetime(2019, 1, 31) - tickets_final['x_opened_at_est'],0)
tickets_final['x_agingdays']=tickets_final['x_agingdays']/np.timedelta64(1,'D')

tickets_final['x_vendor'] = np.where(tickets_final['x_Vendor_Closeassign'] == 'employee', 'GE',
                                   np.where(tickets_final['x_Vendor_Closeassign'] == 'softtek','Softtek','Other'))

#cosinder weekends in the duedate and verify if it is in SLA or OUTSLA
tickets_final['x_due_date2'] = tickets_final.apply(lambda row: add_business_days(row['x_opened_at_est'],row['x_days_sla']), axis=1)
tickets_final['x_inSLA2'] = np.where(tickets_final['x_closed_at_est'] > tickets_final['x_due_date2'], 0, 
                               np.where(tickets_final['x_closed_at_est'].isnull(), 0, 1))


#For apps file
#fillna for columns that make sense to fillna
#apps_final['L2_SSO'] = apps_final['L2_SSO'].fillna(value=0, downcast='infer')

#add total of tickets for each app
groupedappsbyticket= tickets_final.groupby(['cmdb_ci_name'], 
                            as_index = False).agg({'number' : 'count'})
apps_final = pd.merge(apps_final,
                     groupedappsbyticket[['cmdb_ci_name', 'number']] ,
                     left_on='Application' ,
                     right_on='cmdb_ci_name', 
                     how="left", 
                     suffixes=('','x_ticket'))
apps_final.rename(columns = {'number': 'x_tot_tickets'}, inplace= True)
apps_final= apps_final.drop('cmdb_ci_name', axis=1)
apps_final = apps_final.fillna({'x_tot_tickets': 0})

#idetify type of service PbtD or SLA and vendor supported
apps_final['x_supported_by']= 'Other'
apps_final['x_service_type']= 'SLA'<jupyter_output><empty_output><jupyter_text>Save output file into processed directorySave a file in the processed directory that is cleaned properly. It will be read in and used later for further analysis.<jupyter_code>#save file a pkl for fast access
tickets_final.to_pickle(fileoriginaltickets, 'gzip')
print('original tickets file saved as {}'.format(fileoriginaltickets))

#save file a pkl for fast access
apps_final.to_pickle(fileoriginalapps, 'gzip')
print('original apps file saved as {}'.format(fileoriginalapps))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14698983580922595, 'fraction_numerical': 0.014464425332290852, 'mean_word_length': 4.735986547085202, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 38, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '330154', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3694, 'n_tokens_neox': 3565, 'n_words': 667, 'path': '{{cookiecutter.directory_name}}/1-Data_Prep_{{cookiecutter.directory_name}}.ipynb', 'repo_name': 'alfgarciagarcia/cookiecutter_rts_analysis', 'size': '10232'}