<jupyter_start><jupyter_text>Reading files<jupyter_code>leads1920 = pd.read_excel("Data 19-20/Final Leads ID.xlsx")
leads1819 = pd.read_csv("Data 18-19/Leads 18-19.csv")
leads1718 = pd.read_csv("Data 17-18/Leads 17-18.csv")
accounts1920 = pd.read_excel("Data 19-20/Final Accounts ID.xlsx")
accounts1819 = pd.read_csv("Data 18-19/Accounts 18-19.csv")
accounts1718 = pd.read_csv("Data 17-18/Accounts 17-18.csv")
invoices1920 = pd.read_excel("Data 19-20/Final Invoices ID.xlsx")
invoices1819 = pd.read_csv("Data 18-19/Invoice 18-19.csv")
invoices1718 = pd.read_csv("Data 17-18/Invoice 17-18.csv")
opp1920 = pd.read_excel("Data 19-20/Final Opportunities ID.xlsx")
opp1819 = pd.read_csv("Data 18-19/Opportunities 18-19.csv")
opp1718 = pd.read_csv("Data 17-18/Opportunities 17-18.csv")
products = pd.read_excel("Data 19-20/Final Products ID.xlsx")<jupyter_output><empty_output><jupyter_text>Cleaning Columns<jupyter_code>leads1920.head()
leads1920.drop(['(Do Not Modify) Lead', '(Do Not Modify) Row Checksum', '(Do Not Modify) Modified On'], 
               axis=1, inplace=True)
leads1920.head()
leads1819.head()
leads1718.head()
accounts1920.drop(['(Do Not Modify) Account', '(Do Not Modify) Row Checksum', '(Do Not Modify) Modified On'], 
               axis=1, inplace=True)
# accounts1920.rename(columns = {'LE-605-17249':'Lead_ID'}, 
#                           inplace = True)
accounts1920.head()
accounts1819.head()
accounts1718.head()
opp1920.drop(['(Do Not Modify) Opportunity', '(Do Not Modify) Row Checksum', '(Do Not Modify) Modified On'], 
               axis=1, inplace=True)
opp1920.head()
opp1819.drop(opp1819.columns[[9, 10, 11, 12]],
               axis=1, inplace=True)
opp1819.head()
opp1718.head()
# invoices1920.drop(invoices1920.columns[[0, 1, 2]],
#                axis=1, inplace=True)
invoices1920.rename(columns = {'Invioce_ID':'Invoice_ID'}, 
                          inplace = True)
invoices1920.head()
invoices1819.head()
invoices1718.head()
products.drop(['(Do Not Modify) Product', '(Do Not Modify) Row Checksum', '(Do Not Modify) Modified On'], 
               axis=1, inplace=True)
products.head()<jupyter_output><empty_output><jupyter_text>Selecting columns<jupyter_code>test1 = pd.merge(invoices1920, opp1920, on=['Opp_ID'])
test1.head()
test1.shape
invoices1920.shape
test2 = pd.merge(test1, accounts1920, on=['Account_ID'])
test2.T
test2.shape
test2.columns
test2.drop(['Lead_ID_y', 'Product_ID_y', 'Product Name_y', 
            'Organisation_y', 'Email ID_y', 'Status_y', 'Lead_ID', 
            'Organisation', 'Phone', 'Full Name', 
            'Email ID', 'Status'
           ], 
               axis=1, inplace=True)
test2.T
test2.drop(['Status_x', 'Actual Revenue_y', 'Organisation_x', 'Email ID_x', 
            'Est. Close Date', 'Est. Revenue', 'Rating', 'Probability', 'Opp_ID_y'
           ], 
               axis=1, inplace=True)
test2.columns
test2.City.unique()
test2.rename(columns = {'Lead_ID_x':'Lead_ID',
                        'Opp_ID_x':'Opp_ID',
                        'Product_ID_x':'Product_ID',
                        'Product Name_x':'Product Name',
                        'Actual Revenue_x':'Actual Revenue'
                       }, 
                          inplace = True)
test2.columns
test2.head()
test2.T
test2p = pd.merge(test2, products, on=['Product_ID'])
test2p.columns
test2p.drop(['Product Name_y', 'Status'], 
            axis=1, inplace=True)
test2p.columns
test2p.rename(columns = {'Product Name_x':'Product Name',
                        'Field':'Product Category'
                       }, 
                          inplace = True)
test2p.T<jupyter_output><empty_output><jupyter_text>++++++++++++++++++++++++++++++++++++++++++++++++<jupyter_code>test3 = pd.merge(invoices1819, opp1819, on=['Opp_ID', 'Lead_ID', 'Product_ID'])
test3.T
test4 = pd.merge(test3, accounts1819, on=['Opp_ID', 'Lead_ID', 'Account_ID'])
test4.T
test4.columns
test4.drop(['Product Name_y', 'Organisation_y', 'Email ID_y', 
            'Revenue_y', 'Orgnisation', 'Phone',
            'Full Name', 'Email ID', 'Organisation_x', 'Email ID_x'
           ], 
               axis=1, inplace=True)
test4.columns
test4.rename(columns = {'Product Name_x':'Product Name',
                        'Revenue_x': 'Actual Revenue'
                       }, 
                          inplace = True)
test4.T
test4p = pd.merge(test4, products, on=['Product_ID'])
test4p.columns
test4p.drop(['Product Name_y', 'Status'], 
            axis=1, inplace=True)
test4p.columns
test4p.rename(columns = {'Product Name_x':'Product Name',
                        'Field':'Product Category'
                       }, 
                          inplace = True)
test4p.T<jupyter_output><empty_output><jupyter_text>++++++++++++++++++++++++++++++++++++++++++++++++<jupyter_code>test5 = pd.merge(invoices1718, opp1718, on=['Opp_ID', 'Lead_ID', 'Product_ID'])
test5.T
test6 = pd.merge(test5, accounts1718, on=['Opp_ID', 'Lead_ID', 'Account_ID'])
test6.T
test6.columns
test6.drop(['Oraganisation', 'Email ID_x', 'Organisation', 'Email ID_y', 
            'Revenue_y', 'Orgnisation', 'Phone', 'Product Name_y',
            'Full Name', 'Email ID', 'Status'
           ], 
               axis=1, inplace=True)
test6.columns
test6.rename(columns = {'Product Name_x':'Product Name',
                        'Revenue_x': 'Actual Revenue',
                        'Invioce_ID': 'Invoice_ID'
                       }, 
                          inplace = True)
test6.T
test6p = pd.merge(test6, products, on=['Product_ID'])
test6p.columns
test6p.drop(['Product Name_y', 'Status'], 
            axis=1, inplace=True)
test6p.columns
test6p.rename(columns = {'Product Name_x':'Product Name',
                        'Field':'Product Category'
                       }, 
                          inplace = True)
test6p.T<jupyter_output><empty_output><jupyter_text>++++++++++++++++++++++++++++++++++++++++++++++++  Saving the merged/cleaned files<jupyter_code>test2.to_csv('merged1920.csv', encoding='utf-8', index=False)
test4.to_csv('merged1819.csv', encoding='utf-8', index=False)
test6.to_csv('merged1718.csv', encoding='utf-8', index=False)
test2p.to_csv('merged1920_P.csv', encoding='utf-8', index=False)
test4p.to_csv('merged1819_P.csv', encoding='utf-8', index=False)
test6p.to_csv('merged1718_P.csv', encoding='utf-8', index=False)<jupyter_output><empty_output><jupyter_text>++++++++++++++++++++++++++++++++++++++++++++++++  Importing new files to start looking for data that I need<jupyter_code>merged1718 = pd.read_csv("merged1718.csv")
merged1819 = pd.read_csv("merged1819.csv")
merged1920 = pd.read_csv("merged1920.csv")
df1920.City.unique()
df1819.City.unique()
df1718.City.unique()
df1920.head()
df1718.head()
df1819.head()
products
# Created new series of Cities
Cities1718 = merged1718[['City']].groupby(['City']).size()
Cities1819 = merged1819[['City']].groupby(['City']).size()
Cities1920 = merged1920[['City']].groupby(['City']).size()
Cities1718
dfCities1718 = pd.DataFrame(Cities1718)
dfCities1718.rename(columns = {0:'Total number of Customers'}, 
                          inplace = True)

dfCities1718.reset_index(inplace = True)
dfCities1718
dfCities1718.to_csv('Cities1718.csv', encoding='utf-8', index=False)
dfCities1819 = pd.DataFrame(Cities1819)
dfCities1819.rename(columns = {0:'Total number of Customers'}, 
                          inplace = True)

dfCities1819.reset_index(inplace = True)
dfCities1819
dfCities1819.to_csv('Cities1819.csv', encoding='utf-8', index=False)
Cities1819
Cities1718 + Cities1819
Cities1920
dfCities1920 = pd.DataFrame(Cities1920)
dfCities1920.rename(columns = {0:'Total number of Customers'}, 
                          inplace = True)

dfCities1920.reset_index(inplace = True)
dfCities1920
dfCities1920.to_csv('Cities1920.csv', encoding='utf-8', index=False)
list_of_cities = ['Belfast', 'Maynooth', 'Dublin', 'Newbridge', 'Kilkenny', 'Athlone', 
                  'Balbriggan', 'Tralee', 'Limerick', 'Port Laoise', 'Naas', 'Ennis', 
                  'Carlow', 'Drogheda', 'Cork', 'Waterford', 'Galway', 'Navan', 'Swords', 
                  'Bray', 'Dundalk' ]

In_1718 = [66, 63, 59, 0, 0, 0, 0, 0, 46, 0, 0, 0, 0, 0, 68, 0, 63, 0, 0, 0, 0]
In_1819 = [50, 68, 67, 0, 0, 0, 0, 0, 58, 0, 0, 0, 0, 0, 67, 0, 60, 0, 0, 0, 0]
In_1920 = [0, 0, 40, 26, 26, 25, 24, 25, 33, 39, 30, 46, 34, 30, 23, 28, 25, 22, 32, 33, 29]

list_of_city_customers = list(zip(list_of_cities, In_1718, In_1819, In_1920)) 
list_of_cols = ['City', '2017-2018', '2018-2019', '2019-2020']

City_Wise = pd.DataFrame(list_of_city_customers, columns = list_of_cols) 
City_Wise
City_Wise.to_csv('City_Wise.csv', encoding='utf-8', index=False)
# Extracted Year from date and saved in new column Year
merged1718['Year'] = pd.DatetimeIndex(merged1718['Close Date']).year
merged1819['Year'] = pd.DatetimeIndex(merged1819['Close Date']).year
# Column name is different for 19-20 dataset. Take Care otherwise will give error.
merged1920['Year'] = pd.DatetimeIndex(merged1920['Actual Close Date']).year

# Extracted Year-Month from date and saved in new column YYYYMM
merged1718['YYYYMM'] = pd.DatetimeIndex(merged1718['Close Date']).to_period('M')
merged1819['YYYYMM'] = pd.DatetimeIndex(merged1819['Close Date']).to_period('M')
# Column name is different for 19-20 dataset. Take Care otherwise will give error.
merged1920['YYYYMM'] = pd.DatetimeIndex(merged1920['Actual Close Date']).to_period('M')
Years1718 = merged1718[['Year']].groupby(['Year']).size()
Years1819 = merged1819[['Year']].groupby(['Year']).size()
Years1920 = merged1920[['Year']].groupby(['Year']).size()

YM1718 = merged1718[['YYYYMM']].groupby(['YYYYMM']).size()
YM1819 = merged1819[['YYYYMM']].groupby(['YYYYMM']).size()
YM1920 = merged1920[['YYYYMM']].groupby(['YYYYMM']).size()
months = ['January', 'February', 'March', 'April', 'May', 'June', 
'July', 'August', 'September', 'October', 'November', 'December']
Monthly_Customers_2017 = [0, 0, 0, 0, 0, 0, 0, 0, 0, 86, 72, 53]
Monthly_Customers_2018 = [77, 77, 0, 0, 0, 0, 0, 60, 43, 53, 49, 60]
Monthly_Customers_2019 = [51, 54, 9, 39, 47, 37, 47, 35, 35, 57, 37, 51]
Monthly_Customers_2020 = [51, 32, 40, 47, 6, 0, 0, 0, 0, 0, 0, 0]

column_names = ['Months', 'Customers in 2017', 'Customers in 2018', 'Customers in 2019', 'Customers in 2020']

list_of_monthly_customers = list(zip(months, Monthly_Customers_2017, Monthly_Customers_2018, 
                                     Monthly_Customers_2019, Monthly_Customers_2020)) 

Monthly_Customers = pd.DataFrame(list_of_monthly_customers, columns = column_names) 
Monthly_Customers
Monthly_Customers.to_csv('Monthly_Customers.csv', encoding='utf-8', index=False)
Years1718
Years = ['2017-2018', '2018-2019', '2019-2020']
yearly_Customers = [365, 370, 570]

column_names1 = ['Years', 'Total Number of Customers']

list_of_total_customers = list(zip(Years, yearly_Customers))

Total_Customers = pd.DataFrame(list_of_total_customers, columns = column_names1) 
Total_Customers
Total_Customers.to_csv('Yearly_Customers.csv', encoding='utf-8', index=False)
test_prod = pd.merge(df1920, products, on=['Product_ID'])
test_prod['Field'].unique()
test_prod.T<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1727004558862966, 'fraction_numerical': 0.0876016805220345, 'mean_word_length': 3.900569426193605, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '195274', 'license': 'MIT', 'n_tokens_mistral': 4749, 'n_tokens_neox': 3960, 'n_words': 783, 'path': 'Final Datasets/Customer.ipynb', 'repo_name': 'hiteeshpm/Business_Intelligence_and_Business_Analysis', 'size': '11187'}