0_preprocess_data.py

import os
from sklearn import preprocessing
import sys
import numpy as np
import pandas as pd
import pickle
import time
sys.path.append(".")
sys.path.append("..")

removedWordsList = (['xxxxx1'])


def removeNonEnglish(text, englishWords):
    global removedWordsList
    wordList = text.split()
    if len(wordList) == 0:
        return " "
    y = np.array(wordList)
    x = np.array(englishWords)
    index = np.arange(len(englishWords))
    sorted_index = np.searchsorted(x, y)
    yindex = np.take(index, sorted_index, mode="clip")
    mask = x[yindex] != y
    maskedArr = np.ma.array(yindex, mask=mask).compressed()
    result = x[maskedArr]
    text = np.array2string(result)\
        .replace("\'", "")\
        .replace("[", "")\
        .replace("]", "")\
        .replace("\n", "")\
        .replace("\r", "")

    # Logging removed words
    removedWords = set(wordList)-set(result)
    removedWordsList += set(list(removedWords))-set(removedWordsList)
    return text

#import csv

def encryptSingleColumn(data,column):
    le = preprocessing.LabelEncoder()
    le.fit(data.astype(str))
    le_name_mapping = dict(zip(le.transform(le.classes_), le.classes_))
    #le_name_mapping.to_csv(data, index=False, index_label=False)
    
    pickle.dump(
            le_name_mapping,
            open(os.path.join(
                '.', 'outputs', column+".feature"),
                'wb'
            )
        )
    return le.transform(data.astype(str))


def encryptColumnsCollection(data, columnsToEncrypt):
    for column in columnsToEncrypt:
        data[column] = encryptSingleColumn(data[column],column)
    return data


def removeString(data, regex):
    return data.str.lower().str.replace(regex.lower(), ' ')


def cleanDataset(dataset, columnsToClean, regexList):
    for column in columnsToClean:
        for regex in regexList:
            dataset[column] = removeString(dataset[column], regex)
    return dataset


def getRegexList():
    regexList = []
    regexList += ['From:(.*)\r\n']  # from line
    # regexList += ['RITM[0-9]*'] # request id
    # regexList += ['INC[0-9]*'] # incident id
    # regexList += ['TKT[0-9]*'] # ticket id
    regexList += ['Sent:(.*)\r\n']  # sent to line
    regexList += ['Received:(.*)\r\n']  # received data line
    regexList += ['To:(.*)\r\n']  # to line
    regexList += ['CC:(.*)\r\n']  # cc line
    regexList += ['The information(.*)infection']  # footer
    regexList += ['Endava Limited is a company(.*)or omissions']  # footer
    regexList += ['The information in this email is confidential and may be legally(.*)interference if you are not the intended recipient']  # footer
    regexList += ['\[cid:(.*)]']  # images cid
    regexList += ['https?:[^\]\n\r]+']  # https & http
    regexList += ['Subject:']
    # regexList += ['[\w\d\-\_\.]+@[\w\d\-\_\.]+']  # emails
    # regexList += ['[0-9][\-0–90-9 ]+']  # phones
    # regexList += ['[0-9]']  # numbers
    # regexList += ['[^a-zA-z 0-9]+']  # anything that is not a letter
    # regexList += ['[\r\n]']  # \r\n
    # regexList += [' [a-zA-Z] ']  # single letters
    # regexList += [' [a-zA-Z][a-zA-Z] ']  # two-letter words
    # regexList += ["  "]  # double spaces

    regexList += ['^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$']
    regexList += ['[\w\d\-\_\.]+ @ [\w\d\-\_\.]+']
    regexList += ['Subject:']
    regexList += ['[^a-zA-Z]']

    return regexList


if __name__ == '__main__':
    ####################
    # Use this with AML Workbench to load data from data prep file
    # dfIncidents = package.run('Incidents.dprep', dataflow_idx=0)
    # dfIncidents = pd.read_csv('allIncidents.csv', encoding="ISO-8859-1")
    # dfRequests = package.run('Requests.dprep', dataflow_idx=0)
    #dfIncidents = package.run('IncidentsCleaned.dprep', dataflow_idx=0)
    #dfRequests = package.run('RequestsCleaned.dprep', dataflow_idx=0)

    # Load dataset from file
    # dfIncidents = pd.read_csv('./data/endava_tickets/all_incidents.csv')
    # dfRequests = pd.read_csv('./data/endava_tickets/all_requests.csv')
    #####################

    dfIncidents = pd.read_csv('./datasets/preprocessed_incidents.csv',  nrows=None, dtype=str, low_memory=False)#, dtype=str, low_memory=False, error_bad_lines=False, warn_bad_lines=False, nrows=None)
    print("Shape incidents:",dfIncidents.shape)
    dfRequests = pd.read_csv('./datasets/preprocessed_requests.csv',  nrows=None, dtype=str, low_memory=False)#, dtype=str, low_memory=False, error_bad_lines=False, warn_bad_lines=False, nrows=None)
    print("Shape requests:",dfRequests.shape)
    
    
    # Merge incidents and requests datasets
    dfTickets = dfRequests.append(
        dfIncidents,
        ignore_index=True)  # set True to avoid index duplicates
    print("Shape after merging requests with incidents:",dfTickets.shape)
    
    # Reorder columns
    columnsOrder = [
        'title', 'body', 'ticket_type', 'business_service',
        'category', 'sub_category1', 'sub_category2']



    # Merge 'title' and 'body' columns into single column 'body'
    dfTickets['body'] = (dfTickets['title']+
       " " + dfTickets['body']).map(str)
    dfTickets = dfTickets.drop(['title'], axis=1)
    print("Shape after drop title:",dfTickets.shape)

    
    # Remove duplicates
    columnsToDropDuplicates = ['body']
    dfTickets = dfTickets.drop_duplicates(columnsToDropDuplicates)
    print("Shape after removed duplicates:",dfTickets.shape)

    # Select columns for cleaning
    columnsToClean = ['body']

    # Create list of regex to remove sensitive data
    # Clean dataset and remove sensitive data
    cleanDataset(dfTickets, columnsToClean, getRegexList())

    ########################################
    # Remove all non english words + names #
    ########################################
    # Firstly load english words dataset and names dataset
    # dfWordsEn = package.run('EnglishWords.dprep', dataflow_idx=0)
    # dfWordsEn = package.run('EnglishWordsAlpha.dprep', dataflow_idx=0)
    # dfWordsEn = package.run('EnglishWordsMerged.dprep', dataflow_idx=0)
    dfWordsEn = pd.read_csv('./datasets/WordsEn.txt',dtype=str, low_memory=False)
    dfFirstNames = pd.read_csv('./datasets/CSV_Database_of_First_Names.csv',dtype=str, low_memory=False)
    #dfBlackListWords = pd.DataFrame()


    # Transform all words to lower case
    dfWordsEn['Word'] = dfWordsEn['Word'].str.lower()
    dfFirstNames['Word'] = dfFirstNames['Word'].str.lower()
    #dfBlackListWords['Line'] = dfBlackListWords['Line'].str.lower()

    # Merge datasets removing names from English words dataset
    print("Shape before removing first names from\
        english words dataset: "+str(dfWordsEn.shape))
    dfWords = dfWordsEn.merge(
        dfFirstNames.drop_duplicates(),
        on=['Word'], how='left', indicator=True)
    # Select words without names only
    dfWords = dfWords.loc[dfWords['_merge'] == 'left_only']
    print("Shape dfWords after removing first names from\
        english words dataset: "+str(dfWords.shape))
    dfWords = dfWords.drop("_merge", axis=1)  # Drop merge indicator column
    

    # Remove non english words and names

    start_time = time.time()
    dfTickets['body'] = dfTickets['body'].astype(str).apply(
        lambda emailBody: removeNonEnglish(emailBody, dfWords['Word'].astype(str)))
    #dfTickets['title'] = dfTickets['title'].astype(str).apply(
    #    lambda emailBody: removeNonEnglish(emailBody, dfWords['Word'].astype(str)))
    end_time = time.time()
    print("Remove NonEnglish in {:.2f} sec.".format(end_time - start_time))


    #dfTickets['body'] = dfTickets['body'].apply(
    #    lambda emailBody: removeNonEnglish(emailBody, dfWords['Word']))
    #dfTickets['title'] = dfTickets['title'].apply(
    #    lambda emailBody: removeNonEnglish(emailBody, dfWords['Word']))
    
    # Remove empty strings and null rows after removing non english words
    print("Before removing empty: " + str(dfTickets.shape))
    dfTickets = dfTickets[dfTickets.body != " "]
    dfTickets = dfTickets[dfTickets.body != ""]
    dfTickets = dfTickets[~dfTickets.body.isnull()]
    print("After removing empty: " + str(dfTickets.shape))

    ########################################################
    # Data encryption and anonymization using LabelEncoder #
    ########################################################
    # Select columns for encryption
    columnsToEncrypt = [
        'business_service', 'ticket_type','category',
        'sub_category1', 'sub_category2'
    ]

    # Encrypt data for each of selected columns
    dfTickets = encryptColumnsCollection(dfTickets, columnsToEncrypt)

    ##########################

    # Remove duplicates x2
    columnsToDropDuplicates = ['body']
    dfTickets = dfTickets.drop_duplicates(columnsToDropDuplicates)
    print(dfTickets.shape)

    # Save cleaned and encrypted dataset back to csv without indexes
    dfTickets.to_csv('all_tickets.csv', index=False, index_label=False)
    sortedRemovedWordsList = np.sort(removedWordsList)
    dfx = pd.DataFrame(sortedRemovedWordsList)
    dfx.to_csv("removed_words.csv", index=False, index_label=False)