<jupyter_start><jupyter_text>Year : 2017-18<jupyter_code>import pandas as pd
import numpy as np
import random
from random import randint
from datetime import timedelta
from datetime import datetime
from random import uniform<jupyter_output><empty_output><jupyter_text>Leads(2018-19)<jupyter_code>Ld = pd.read_csv("C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2018-19/Leads(2018-19).csv")
def product_id(row):
    if row["Product_Name"] == "Proxima-C":
        return "PRO-23-0493"
    elif row["Product_Name"] == "Kits Dragon":
        return "KTD-32-3231"
    elif row["Product_Name"] == "Phoenix":
        return "PHO-52-1928"
    elif row["Product_Name"] == "Sirius":
        return "SIR-10-0293"
    elif row["Product_Name"] == "Aurora":
        return "AUR-67-4989"
    elif row["Product_Name"] == "Apollo":
        return "APO-09-8723"
    elif row["Product_Name"] == "Agyrap-S":
        return "AGY-90-2818"
    else:
        return "ANH-02-0987"
    
Ld = Ld.assign(Product_ID = Ld.apply(product_id, axis = 1))
def random_dates(start, end, n=1000):

    start_u = start.value//10**9
    end_u = end.value//10**9

    return pd.to_datetime(np.random.randint(start_u, end_u, n), unit='s')
start = pd.to_datetime('31-10-2018')
end = pd.to_datetime('01-11-2019')
Ld['Lead_Created_on'] = random_dates(start, end)
Ld.head(10)
Ld.to_excel("Leads_final(2018-19).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Leads(2017-18)<jupyter_code>Ld2 = pd.read_csv("C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2017-18/Leads(2017-18).csv")
def product_id(row):
    if row["Product_Name"] == "Proxima-C":
        return "PRO-23-0493"
    elif row["Product_Name"] == "Kits Dragon":
        return "KTD-32-3231"
    elif row["Product_Name"] == "Phoenix":
        return "PHO-52-1928"
    elif row["Product_Name"] == "Sirius":
        return "SIR-10-0293"
    elif row["Product_Name"] == "Aurora":
        return "AUR-67-4989"
    elif row["Product_Name"] == "Apollo":
        return "APO-09-8723"
    elif row["Product_Name"] == "Agyrap-S":
        return "AGY-90-2818"
    else:
        return "ANH-02-0987"
    
Ld2 = Ld2.assign(Product_ID = Ld2.apply(product_id, axis = 1))
def random_dates(start, end, n=1000):

    start_u = start.value//10**9
    end_u = end.value//10**9

    return pd.to_datetime(np.random.randint(start_u, end_u, n), unit='s')
start = pd.to_datetime('31-10-2017')
end = pd.to_datetime('01-11-2018')
Ld2['Lead_Created_on'] = random_dates(start, end)
Ld2.head(10)
Ld2.to_excel("Leads_final(2017-18).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Opportunities(2018-19)<jupyter_code>Op = pd.read_csv("C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2018-19/Opportunities(2018-19).csv")
Op.head(10)
Op['Lead_ID'] = Ld['Lead_ID']
Op['Product_Name'] = Ld['Product_Name']
Op['Product_ID'] = Ld['Product_ID']
Op['Email_address'] = Ld['Email_address']
Op['Opportunity_Created_on'] = Ld['Lead_Created_on'].map(lambda a: a + pd.DateOffset(days=randint(5,20), hours=randint(0,12), minutes=randint(0,60), seconds=randint(0,60)))
Op['Opportunity_Close_Date'] = Op['Created_on'].map(lambda a: a + pd.DateOffset(days=randint(5,10), hours=randint(0,12), minutes=randint(0,60), seconds=randint(0,60)))
Op.drop(['Created_on', 'Close_Date'], axis = 1, inplace = True)
Op.head(10)
Op['Revenue'] = randint(1,100)

Proxima_C = 70
Kits_Dragon = 30
Phoenix = 42
Sirius = 55
Aurora = 40
Apollo = 50

Agyrap_S = 65
Anhee_C = 60
Op['Revenue'] = Op['Revenue'].map(lambda a: (Proxima_C*randint(0,5))+(Kits_Dragon*randint(0,5))+(Phoenix*randint(0,5))+(Sirius*randint(0,5))+(Aurora*randint(0,2))+(Apollo*randint(0,2))+(Agyrap_S*randint(0,5))+(Anhee_C*randint(0,5)))
Op.head(10)
Op.to_excel("Opportunities_final(2018-19).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Opportunities(2017-18)<jupyter_code>Op2 = pd.read_csv("C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2017-18/Opportunities(2017-18).csv")
Op2.head(10)
Op2['Lead_ID'] = Ld2['Lead_ID']
Op2['Product_Name'] = Ld2['Product_Name']
Op2['Product_ID'] = Ld2['Product_ID']
Op2['Email_address'] = Ld2['Email_address']
Op2['Opportunity_Created_on'] = Ld2['Lead_Created_on'].map(lambda a: a + pd.DateOffset(days=randint(5,20), hours=randint(0,12), minutes=randint(0,60), seconds=randint(0,60)))
Op2['Opportunity_Close_Date'] = Op2['Opportunity_Created_on'].map(lambda a: a + pd.DateOffset(days=randint(5,10), hours=randint(0,12), minutes=randint(0,60), seconds=randint(0,60)))

Op2.head(10)
Op2['Revenue'] = randint(1,100)

Proxima_C = 60
Kits_Dragon = 30
Phoenix = 35
Sirius = 55
Aurora = 40
Apollo = 40

Agyrap_S = 60
Anhee_C = 55
Op2['Revenue'] = Op2['Revenue'].map(lambda a: (Proxima_C*randint(0,5))+(Kits_Dragon*randint(0,5))+(Phoenix*randint(0,5))+(Sirius*randint(0,5))+(Aurora*randint(0,2))+(Apollo*randint(0,2))+(Agyrap_S*randint(0,5))+(Anhee_C*randint(0,5)))
Op2.head(10)
Op2.to_excel("Opportunities_final(2017-18).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Accounts(2018-19)<jupyter_code>Ac = pd.read_csv('C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2018-19/Accounts(2018-19).csv', delimiter = ',')                   
# Acc['City'].unique()
Ac['Lead_ID'] = Op['Lead_ID']
Ac['Opportunity_ID'] = Op['Opportunity_ID']
Ac['Full_Name'] = Ld['Full_Name']
Ac['Email_address'] = Op['Email_address']
Ac.head(10)
Ac = Ac[['Lead_ID', 'Opportunity_ID', 'Account_ID', 'Full_Name', 'City', 'Phone', 'Email_address']]
Ac.head(10)
Ac.to_excel("Accounts_final(2018-19).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Accounts(2017-18)<jupyter_code>Ac2 = pd.read_csv("C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2017-18/Accounts(2017-18).csv", delimiter = ',')                   
Ac2['Lead_ID'] = Op2['Lead_ID']
Ac2['Opportunity_ID'] = Op2['Opportunity_ID']
Ac2['Full_Name'] = Ld2['Full_Name']
Ac2['Email_address'] = Op2['Email_address']
Ac2.head(10)
Ac2 = Ac2[['Lead_ID', 'Opportunity_ID', 'Account_ID', 'Full_Name', 'City', 'Phone', 'Email_address']]
Ac2.head(10)
Ac2.to_excel("Accounts_final(2017-18).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Quotes(2018-19)<jupyter_code>Qu = pd.read_csv('C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2018-19/Quotes(2018-19).csv')
Qu['Lead_ID'] = Ac['Lead_ID']
Qu['Opportunity_ID'] = Ac['Opportunity_ID']
Qu['Account_ID'] = Ac['Account_ID']
Qu['Product_Name'] = Op['Product_Name']
Qu['Product_ID'] = Op['Product_ID']
Qu['Product_Category'] = Ld['Product_Category']
Qu['Revenue'] = Op['Revenue']
Qu['Email_address'] = Ac['Email_address']
Qu['Quote_Created_On'] = Op['Opportunity_Close_Date'].map(lambda a: a + pd.DateOffset(days=randint(10,25), hours=randint(0,12), minutes=randint(0,60), seconds=randint(0,60)))
Qu.head(10)<jupyter_output><empty_output><jupyter_text>Quotes(2017-18)<jupyter_code>Qu2 = pd.read_csv('C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2017-18/Quotes(2017-18).csv')
Qu2['Lead_ID'] = Ac2['Lead_ID']
Qu2['Opportunity_ID'] = Ac2['Opportunity_ID']
Qu2['Account_ID'] = Ac2['Account_ID']
Qu2['Product_Name'] = Op2['Product_Name']
Qu2['Product_ID'] = Op2['Product_ID']
Qu2['Product_Category'] = Ld2['Product_Category']
Qu2['Revenue'] = Op2['Revenue']
Qu2['Email_address'] = Ac2['Email_address']
Qu2['Quote_Created_On'] = Op2['Opportunity_Close_Date'].map(lambda a: a + pd.DateOffset(days=randint(10,25), hours=randint(0,12), minutes=randint(0,60), seconds=randint(0,60)))
Qu2.head(10)


Qu.to_excel("Quotes(2018-19).xlsx", index = False)
Qu2.to_excel("Quotes(2017-18).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Orders(2018-19)<jupyter_code>Ord = pd.read_csv('C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2018-19/Orders(2018-19).csv')
Ord['Lead_ID'] = Qu['Lead_ID']
Ord['Opportunity_ID'] = Qu['Opportunity_ID']
Ord['Account_ID'] = Qu['Account_ID']
Ord['Quote_ID'] = Qu['Quote_ID']
Ord['Product_Name'] = Qu['Product_Name']
Ord['Product_Category'] = Qu['Product_Category']
Ord['Revenue'] = Qu['Revenue']
Ord['Email_address'] = Qu['Email_address']
Ord.head(10)<jupyter_output><empty_output><jupyter_text>Orders(2017-18)<jupyter_code>Ord2 = pd.read_csv('C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2017-18/Orders(2017-18).csv')
Ord2['Lead_ID'] = Qu2['Lead_ID']
Ord2['Opportunity_ID'] = Qu2['Opportunity_ID']
Ord2['Account_ID'] = Qu2['Account_ID']
Ord2['Quote_ID'] = Qu2['Quote_ID']
Ord2['Product_Name'] = Qu2['Product_Name']
Ord2['Product_Category'] = Qu2['Product_Category']
Ord2['Revenue'] = Qu2['Revenue']
Ord2['Email_address'] = Qu2['Email_address']
Ord2.head(10)
Ord.to_excel("Orders(2018-19).xlsx", index = False)
Ord2.to_excel("Orders(2017-18).xlsx", index = False)<jupyter_output><empty_output><jupyter_text>Invoices(2018-19)<jupyter_code>Iv = pd.read_csv("C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2018-19/Invoice(2018-19).csv")
Iv['Lead_ID'] = Ord['Lead_ID']
Iv['Opportunity_ID'] = Ord['Opportunity_ID']
Iv['Account_ID'] = Ord['Account_ID']
Iv['Quote_ID'] = Ord['Quote_ID']
Iv['Order_ID'] = Ord['Order_ID']
Iv['Product_Name'] = Ord['Product_Name']
Iv['Product_ID'] = Op['Product_ID']
Iv['Revenue'] = Ord['Revenue']
Iv['Email_address'] = Ord['Email_address']
Iv['Phone'] = Ac['Phone']
Iv.head(10)<jupyter_output><empty_output><jupyter_text>Invoices(2017-18)<jupyter_code>Iv2 = pd.read_csv("C:/Users/<NAME>/Downloads/BIBA/Datasets/Mockaroo/2017-18/Invoice(2017-18).csv")
Iv2['Lead_ID'] = Ord2['Lead_ID']
Iv2['Opportunity_ID'] = Ord2['Opportunity_ID']
Iv2['Account_ID'] = Ord2['Account_ID']
Iv2['Quote_ID'] = Ord2['Quote_ID']
Iv2['Order_ID'] = Ord2['Order_ID']
Iv2['Product_Name'] = Ord2['Product_Name']
Iv2['Product_ID'] = Op2['Product_ID']
Iv2['Revenue'] = Ord2['Revenue']
Iv2['Email_address'] = Ord2['Email_address']
Iv2['Phone'] = Ac2['Phone']
Iv2.head(10)
Iv.to_excel("Invoices(2018-19).xlsx", index = False)
Iv2.to_excel("Invoices(2017-18).xlsx", index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.20316301703163017, 'fraction_numerical': 0.07410786699107867, 'mean_word_length': 7.126029654036244, 'pattern_counts': {'":': 14, '<': 65, '<?xml version=': 0, '>': 65, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '338168', 'license': 'MIT', 'n_tokens_mistral': 4563, 'n_tokens_neox': 4114, 'n_words': 478, 'path': 'Datasets/Data Cleaning(18-19,17-18).ipynb', 'repo_name': 'singh-sudo098/Business_Intelligence-and-Business-Analytics', 'size': '9972'}