{"cells":[{"metadata":{"trusted":false},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\n%matplotlib inline\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nfrom datetime import datetime\nfrom sklearn.model_selection import train_test_split\nfrom sklearn import metrics\nfrom sklearn.metrics import confusion_matrix\nfrom sklearn.model_selection import KFold\nfrom sklearn.model_selection import cross_val_score\nfrom sklearn.linear_model import LogisticRegression\nfrom sklearn.tree import DecisionTreeClassifier\nfrom sklearn.neighbors import KNeighborsClassifier\nfrom sklearn.discriminant_analysis import LinearDiscriminantAnalysis\nfrom sklearn.naive_bayes import GaussianNB\nfrom sklearn.svm import SVC\nfrom sklearn import svm, tree, linear_model, neighbors, naive_bayes, ensemble, discriminant_analysis, gaussian_process\nfrom sklearn.linear_model import LogisticRegression\nfrom sklearn.metrics import classification_report\nfrom sklearn.metrics import confusion_matrix\nfrom sklearn.metrics import accuracy_score\nimport pandas.util.testing as tm","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# reading offer details\noffers = pd.read_csv('offers.csv.gz', compression='gzip')\nprint(offers.shape)\noffers.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#checking how many unique values are there in each column\noffers.nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Checking how the data looks in this table.\nfig, axes = plt.subplots(2,2, figsize=(15,6))\noffers.groupby('company')['offer'].count().sort_values().plot(kind='bar', title='Company vs. No. of Offers', grid=True, ax=axes[0,0])\noffers.groupby('brand')['offer'].count().sort_values().plot(kind='bar', title='Brand vs. No. of Offers', grid=True, ax=axes[0,1])\noffers.groupby('category')['offer'].count().sort_values().plot(kind='bar', title='Category vs. No. of Offers', grid=True, ax=axes[1,0])\noffers.groupby('offer')['offervalue'].sum().sort_values().plot(kind='bar', title='Offer vs. Offer Value', grid=True, ax=axes[1,1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# since a combination of company, brand and category uniquely identifies a product, \n# I want to see if product has many offers or just one.\noffers.groupby(['company','brand','category'])['offer'].size().sort_values().plot(kind='bar', title='Product vs. No. of Offers', grid=True, figsize=(20,5))","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Checking how many offers need a minimum quantity of more than 2\nprint(offers.quantity.value_counts())","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#this table shows that there are 10 products which have duplicate offers. I mean that the minimum purchase quantity\n#required and. offer value is same for duplicate offers. THEY MAY BE COMBINED TOGETHER.\noffers.groupby(['company','brand','category'])['offer','quantity','offervalue'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#reading customer history data when the offer was given to them\ntrainHistory = pd.read_csv('trainHistory.csv.gz', compression='gzip')\ntrainHistory['offerdate'] = pd.to_datetime(trainHistory['offerdate'])\nprint(trainHistory.shape)\ntrainHistory.sort_values(by='repeattrips', ascending=False).head(1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"print('Ending date: ',trainHistory.offerdate.max())\nprint('Starting date: ',trainHistory.offerdate.min())\nprint('Offers were given in ', trainHistory.offerdate.max() - trainHistory.offerdate.min(), ' period')\n#So the offers were given in a 60 days period from March 1 2013 to April 30 2013","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#there are no null values in the dataset\ntrainHistory.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#This shows that out of 37 offers in the offer table, there are 24 offers in trainHistory table.\ntrainHistory.nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Now I wanna check how many trips were made by each customer first. \n#This shows that the max trips are 2124 by a customer. That seem to be an outlier.\ntrainHistory.repeattrips.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"trainHistory[trainHistory.repeattrips > 100]\n#This shows that there are 3 potential outliers for number of trips","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Let's see of the given customers, how may are repeaters\n#Let's see how many customers are offered a particular offer\n#How many chains are in each market\n#How many customers repeated after given a particular offer\nfig, axes = plt.subplots(2,2, figsize=(15,10))\ntrainHistory.groupby('offer')['id'].count().plot(kind='bar', title='Offers vs. Customer Count', grid=True, ax=axes[0,0])\ntrainHistory.repeater.value_counts().plot(kind='pie', title='% Repeat Customers', ax=axes[0,1], autopct='%1.1f%%', textprops=dict(color=\"black\"))\ntrainHistory.groupby('market')['chain'].count().sort_values().plot(kind='bar', title='Chain Count in each Market', grid=True, ax=axes[1,0])\ntrainHistory.groupby(['offer','repeater']).size().unstack().plot(kind='bar', title='Offer vs. Repeaters', grid=True, ax=axes[1,1], stacked=True)\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#merging trainHistory and Offers table to see how many customer repeated for a particular product (company, brand, category)\nhistory = trainHistory.merge(offers, on='offer')\nprint(history.shape)\nhistory.head(1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"fig, axes = plt.subplots(2,2, figsize=(15,10))\nhistory.groupby(['company','repeater']).size().unstack().plot(kind='bar', title='Company vs. Repeaters', grid=True, stacked=True, ax=axes[0,0])\nhistory.groupby(['brand','repeater']).size().unstack().plot(kind='bar', title='Brand vs. Repeaters', grid=True, stacked=True, ax=axes[0,1])\nhistory.groupby(['category','repeater']).size().unstack().plot(kind='bar', title='Category vs. Repeaters', grid=True, stacked=True, ax=axes[1,0])\nhistory.groupby(['company','brand','category','repeater']).size().unstack().plot(kind='bar', title='Product vs. Repeaters', grid=True, stacked=True, ax=axes[1,1])\nplt.tight_layout()\n#Companies ending with 7979, 9383, 0383 have higher % of repeat customers than others\n#Brand 6732, 6926, 28840 have the higher % repeat customers than others\n#Catogories 2119, 9909 have higher % of repeat customers than other\n#When I combine all of them together to see exactly which product is getting higher repeat customers than average\n#(7979,6732,9909), (9383,6926,2119) comes out to be the best ones. This shows is the same pattern as we saw\n#individually in Company, Brand and Category ","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"fig, ax =plt.subplots(1, figsize=(20,5))\nsns.boxplot(x = history.offer, y = history.repeattrips, palette=\"Set3\")\nplt.tight_layout()\n#The 3 outliers are the same that we saw above. \n#So it would make sense to remove them from this plot and see how it looks","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"subset = history[history.repeater == 't']\nfig, ax =plt.subplots(1, figsize=(20,5))\nsns.boxplot(x = subset.offer, y = subset.repeattrips)\nplt.ylim(-1, 15)\nplt.tight_layout()\n#This shows that out of those who are repeaters, Offer 044,052,329,501  have median value = 2, rest all have 1.","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"fig, ax =plt.subplots(0, figsize=(20,5))\nsubset.boxplot(column=['repeattrips'], by=['company','brand','category'], figsize=(20,10), rot=90, medianprops=dict(linestyle='-', linewidth=4))\nplt.ylim(-1, 15)\nplt.tight_layout(pad=5)\n#This shows that out of all the unique products that are on offer, 4 of them ahave median repeat trips = 2,\n#rest all of them have 1.\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Reading testHistory dataset where I have to predict if a person is going to be a repeat customer or not\ntestHistory = pd.read_csv('testHistory.csv.gz', compression='gzip')\ntestHistory.offerdate = pd.to_datetime(testHistory.offerdate)\nprint(testHistory.shape)\ntestHistory.head(3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#I should now see how much overlap is there in id, chain, market and offer to see how much variation is there\n#Check if there are any common customers in train and test data\ncolumns = ['id','chain','market','offer']\nfor c in columns:\n    train = trainHistory[c]\n    test = testHistory[c]\n    print('Common', c, len(set(train).intersection(set(test))))","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"print('Ending date: ',testHistory.offerdate.max())\nprint('Starting date: ',testHistory.offerdate.min())\nprint('Offers were given in ', testHistory.offerdate.max() - testHistory.offerdate.min(), ' period')\n#So the offers were given in a 91 days period from May 1 2013 to July 31 2013","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"fig, axes = plt.subplots(2,2, figsize=(15,5))\ntrainHistory.groupby('offer')['id'].count().sort_values().plot(kind='bar', title='Training [Offers vs. Customer Count]', grid=True, ax=axes[0,0])\ntestHistory.groupby('offer')['id'].count().sort_values().plot(kind='bar', title='Test [Offers vs. Customer Count]', grid=True, ax=axes[0,1])\ntrainHistory.groupby('market')['chain'].count().sort_values().plot(kind='bar', title='Training [Chain Count in each Market]', grid=True, ax=axes[1,0])\ntestHistory.groupby('market')['chain'].count().sort_values().plot(kind='bar', title='Test [Chain Count in each Market]', grid=True, ax=axes[1,1])\nplt.tight_layout()\n# #Not much correlation between training & test dataset in terms of offers and markets","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Since the dataset is too large, I am interested to learn about only those products that are on offer based on the\n#offer table\n\n#To be able to do that, I need to find all products on offer in both training and test datasets.\n#Merging testHistory table with offers\npred_history = testHistory.merge(offers, on='offer')\npred_history.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# The following commented code is to reduce the transactions dataset and get only those transactions where\n# either of 'company', 'brand' or 'category' is on offer","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# comp_tmp = history.company\n# comp_tmp1 = pred_history.company\n# comp_tmp = comp_tmp.append(comp_tmp1)\n# comp_tmp = comp_tmp.unique()\n# comp_tmp = set(comp_tmp)\n\n# brand_tmp = history.brand\n# brand_tmp1 = pred_history.brand\n# brand_tmp = brand_tmp.append(brand_tmp1)\n# brand_tmp = brand_tmp.unique()\n# brand_tmp = set(brand_tmp)\n\n# cat_tmp = history.category\n# cat_tmp1 = pred_history.category\n# cat_tmp = cat_tmp.append(cat_tmp1)\n# cat_tmp = cat_tmp.unique()\n# cat_tmp = set(cat_tmp)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#import modin.pandas as mpd","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# transactions = pd.DataFrame(columns=['id','chain','dept','category','company','brand','date','productsize','productmeasure','purchasequantity','purchaseamount'])\n# chunk_size = 2 * (10 ** 7)\n# unique_set_tr = set()\n# count = 0\n# for chunk in mpd.read_csv('transactions.csv.gz', compression = 'gzip', chunksize = chunk_size,\n#                         engine='c'):\n#     internal_transactions = chunk.loc[(chunk['company'].isin(comp_tmp)) & \n#                                       (chunk['brand'].isin(brand_tmp)) & \n#                                       (chunk['category'].isin(cat_tmp))]\n#     internal_transactions.to_csv('transactions_offer_'+str(count)+'.csv')\n#     count = count + 1\n#     print(count)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# import glob\n# import pandas as pd\n# extension = 'csv'\n# all_filenames = [i for i in glob.glob('*.{}'.format(extension))]\n# offer_transactions = pd.concat([pd.read_csv(f) for f in all_filenames ])\n# #export to csv\n# offer_transactions.to_csv( \"combined_csv.csv\", index=False, encoding='utf-8-sig')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# offer_transactions = pd.read_csv('offer_transactions.csv')\noffer_transactions = pd.read_csv('combined_csv.csv')\noffer_transactions.date = pd.to_datetime(offer_transactions.date)\nprint(offer_transactions.shape)\noffer_transactions.head(1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"print('Ending date: ', offer_transactions.date.max())\nprint('Starting date: ', offer_transactions.date.min())\nprint('Transactions are from ', offer_transactions.date.max() - offer_transactions.date.min(), ' period')\n#So the transaction history is given in a 513 days period from March 2 2012 to July 28 2013","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# Feature Engineering: To add meaningful data from reduced transactions table into the training & test dataset\n#adding 9 features in PART A and 27 features in PART B, as explained in the presentation","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# features = ['company','brand','category']\n# for f in features:\n#     #FEATURE TYPE 1\n#     feature = pd.DataFrame(offer_transactions.groupby(['id',f]).size())\n#     feature.columns = [f+'_count']\n#     feature = feature.reset_index()\n#     #adding the feature in training data\n#     #No. of transactions done for each company, brand and category\n#     history = pd.merge(history, feature, left_on=['id',f], right_on=['id',f], how='left')\n#     history.fillna({f+'_count': 0}, inplace=True)\n#     #adding the featue in test data\n#     pred_history = pd.merge(pred_history, feature, left_on=['id',f], right_on=['id',f], how='left')\n#     pred_history.fillna({f+'_count': 0}, inplace=True)\n    \n#     #FEATURE TYPE 2\n#     #Total quantity purchased for each company, brand and category\n#     feature = pd.DataFrame(offer_transactions.groupby(['id',f])['purchasequantity'].sum())\n#     feature = feature.rename({'purchasequantity': f+'_purchasequantity'}, axis=1)\n#     feature = feature.reset_index()\n#     #adding the feature in training data\n#     history = pd.merge(history, feature, left_on=['id',f], right_on=['id',f], how='left')\n#     history.fillna({f+'_purchasequantity': 0}, inplace=True)\n#     #adding the featue in test data\n#     pred_history = pd.merge(pred_history, feature, left_on=['id',f], right_on=['id',f], how='left')\n#     pred_history.fillna({f+'_purchasequantity': 0}, inplace=True)\n    \n#     #FEATURE TYPE 3\n#     #Total amount purchased for each company, brand and category\n#     feature = pd.DataFrame(offer_transactions.groupby(['id',f])['purchaseamount'].sum())\n#     feature = feature.rename({'purchaseamount': f+'_purchaseamount'}, axis=1)\n#     feature = feature.reset_index()\n#     #adding the feature in training data\n#     history = pd.merge(history, feature, left_on=['id',f], right_on=['id',f], how='left')\n#     history.fillna({f+'_purchaseamount': 0}, inplace=True)\n#     #adding the featue in test data\n#     pred_history = pd.merge(pred_history, feature, left_on=['id',f], right_on=['id',f], how='left')\n#     pred_history.fillna({f+'_purchaseamount': 0}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# #Adding FEATURE SET 4\n# #How many times, quantity and amount was spent on a unique combination of company, brand & category\n# feature = pd.DataFrame(offer_transactions.groupby(['id','company','brand','category']).size())\n# feature.columns = ['product_count']\n# feature = feature.reset_index()\n# #adding the feature in training data\n# history = pd.merge(history, feature, left_on=['id','company','brand','category'], right_on=['id','company','brand','category'], how='left')\n# history.fillna({'product_count': 0}, inplace=True)\n# #adding the featue in test data\n# pred_history = pd.merge(pred_history, feature, left_on=['id','company','brand','category'], right_on=['id','company','brand','category'], how='left')\n# pred_history.fillna({'product_count': 0}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# features = ['purchasequantity','purchaseamount']\n# for f in features:\n#     feature = pd.DataFrame(offer_transactions.groupby(['id','company','brand','category'])[f].sum())\n#     feature.columns = ['product_'+f]\n#     feature = feature.reset_index()\n#     #adding the feature in training data\n#     #No. of transactions done for each company, brand and category\n#     history = pd.merge(history, feature, left_on=['id','company','brand','category'], right_on=['id','company','brand','category'], how='left')\n#     history.fillna({'product_'+f: 0}, inplace=True)\n#     #adding the featue in test data\n#     pred_history = pd.merge(pred_history, feature, left_on=['id','company','brand','category'], right_on=['id','company','brand','category'], how='left')\n#     pred_history.fillna({'product_'+f: 0}, inplace=True)\n\n# history.to_csv ('history_4_features.csv', index = False, header=True)\n# pred_history.to_csv ('pred_history_4_features.csv', index = False, header=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#reading feature rich files\nhistory = pd.read_csv('history_4_features.csv')\npred_history = pd.read_csv('pred_history_4_features.csv')\nhistory.offerdate = pd.to_datetime(history.offerdate)\npred_history.offerdate = pd.to_datetime(pred_history.offerdate)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# from datetime import datetime, timedelta\n# #Adding FEATURE SET 5 for training data\n# #5.1 No. of transaction of customer for a company 7, 30, 60 days before receiving the offer \n# #5.2 No. of transaction of customer for a brand 7, 30, 60 days before receiving the offer\n# #5.3 No. of transaction of customer for a category 7, 30, 60 days before receiving the offer\n# columns = ['company','brand','category']\n# timeframes = [7, 30, 60]\n# dates = history.offerdate.unique()\n# dataframeMap = {}\n# for date in dates:\n#     date = pd.to_datetime(date)\n#     for timeframe in timeframes:\n#         iddf = offer_transactions.loc[(offer_transactions.date < date) & \n#                                       (offer_transactions.date >= date - timedelta(days=timeframe))]\n#         for column in columns:\n#             newColumnName = column+'_cnt_'+str(timeframe)  \n            \n#             if newColumnName not in history.columns:\n#                 history[newColumnName] = np.nan          \n#             count_f = pd.DataFrame(iddf.groupby(['id', column]).size())\n#             count_f = count_f.reset_index()\n#             count_f = count_f.rename(columns={0: column+'_cnt_'+str(timeframe)})\n#             count_f['offerdate'] = date\n#             #adding features to training data\n#             history = history.merge(count_f, how='left', on=['id', column, 'offerdate'])\n#             history[newColumnName+'_x'] = history[newColumnName+'_x'].fillna(history[newColumnName+'_y'])\n#             history = history.drop(newColumnName+'_y', axis=1)\n#             history = history.rename(columns={newColumnName+'_x': newColumnName})\n#             adding features to test data\n        \n# history.to_csv ('history_f5a.csv', index = False, header=True)\n            ","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# from datetime import datetime, timedelta\n# #Adding FEATURE SET 5 for test data\n# #5.1 No. of transaction of customer for a company 7, 30, 60 days before receiving the offer \n# #5.2 No. of transaction of customer for a brand 7, 30, 60 days before receiving the offer\n# #5.3 No. of transaction of customer for a category 7, 30, 60 days before receiving the offer\n# columns = ['company','brand','category']\n# timeframes = [7, 30, 60]\n# dates = pred_history.offerdate.unique()\n# count = 0\n# dataframeMap = {}\n# for date in dates:\n#     date = pd.to_datetime(date)\n#     for timeframe in timeframes:\n#         iddf = offer_transactions.loc[(offer_transactions.date < date) & \n#                                       (offer_transactions.date >= date - timedelta(days=timeframe))]\n#         for column in columns:\n#             newColumnName = column+'_cnt_'+str(timeframe)  \n            \n#             if newColumnName not in pred_history.columns:\n#                 pred_history[newColumnName] = np.nan          \n#             count_f = pd.DataFrame(iddf.groupby(['id', column]).size())\n#             count_f = count_f.reset_index()\n#             count_f = count_f.rename(columns={0: column+'_cnt_'+str(timeframe)})\n#             count_f['offerdate'] = date\n#             #adding features to test data\n#             pred_history = pred_history.merge(count_f, how='left', on=['id', column, 'offerdate'])    \n#             pred_history[newColumnName+'_x'] = pred_history[newColumnName+'_x'].fillna(pred_history[newColumnName+'_y'])\n#             pred_history = pred_history.drop(newColumnName+'_y', axis=1)\n#             pred_history = pred_history.rename(columns={newColumnName+'_x': newColumnName})\n#         count = count + 1\n#         print(count)\n        \n# pred_history.to_csv ('pred_history_f5a.csv', index = False, header=True)\n            ","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# from datetime import datetime, timedelta\n# #Adding FEATURE SET 5 for training data\n# #5.4 Total quantity purchased by a customer for a company 7, 30, 60 days before receiving the offer \n# #5.5 Total quantity purchased by a customer for a brand 7, 30, 60 days before receiving the offer\n# #5.6 Total quantity purchased by a customer for a category 7, 30, 60 days before receiving the offer\n# columns = ['company','brand','category']\n# timeframes = [7, 30, 60]\n# dates = history.offerdate.unique()\n# # history1 = history.copy(deep=True)\n# count = 0\n# dataframeMap = {}\n# for date in dates:\n#     date = pd.to_datetime(date)\n#     for timeframe in timeframes:\n#         iddf = offer_transactions.loc[(offer_transactions.date < date) & \n#                                       (offer_transactions.date >= date - timedelta(days=timeframe))]\n#         for column in columns:\n#             newColumnName = column+'_qty_'+str(timeframe)  \n#             if newColumnName not in history.columns:\n#                 print('in if loop', newColumnName)\n#                 history[newColumnName] = np.nan          \n#             qty_f = pd.DataFrame(iddf.groupby(['id', column])['purchasequantity'].sum())\n#             qty_f = qty_f.reset_index()\n#             qty_f = qty_f.rename(columns={'purchasequantity': column+'_qty_'+str(timeframe)})\n#             qty_f['offerdate'] = date\n            \n#             history = history.merge(qty_f, how='left', on=['id', column, 'offerdate'])\n#             history[newColumnName+'_x'] = history[newColumnName+'_x'].fillna(history[newColumnName+'_y'])\n#             history = history.drop(newColumnName+'_y', axis=1)\n#             history = history.rename(columns={newColumnName+'_x': newColumnName})\n           \n        \n#         count = count + 1\n#         print(count)\n        \n# history.to_csv ('history_f5b.csv', index = False, header=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# from datetime import datetime, timedelta\n# #Adding FEATURE SET 5\n# #Adding FEATURE SET 5 for training data\n# #5.4 Total quantity purchased by a customer for a company 7, 30, 60 days before receiving the offer \n# #5.5 Total quantity purchased by a customer for a brand 7, 30, 60 days before receiving the offer\n# #5.6 Total quantity purchased by a customer for a category 7, 30, 60 days before receiving the offer\n# columns = ['company','brand','category']\n# timeframes = [7, 30, 60]\n# dates = pred_history.offerdate.unique()\n# # history1 = history.copy(deep=True)\n# count = 0\n# dataframeMap = {}\n# for date in dates:\n#     date = pd.to_datetime(date)\n#     for timeframe in timeframes:\n#         iddf = offer_transactions.loc[(offer_transactions.date < date) & \n#                                       (offer_transactions.date >= date - timedelta(days=timeframe))]\n#         for column in columns:\n#             newColumnName = column+'_qty_'+str(timeframe)  \n#             if newColumnName not in pred_history.columns:\n#                 print('in if loop', newColumnName)\n#                 pred_history[newColumnName] = np.nan          \n#             qty_f = pd.DataFrame(iddf.groupby(['id', column])['purchasequantity'].sum())\n#             qty_f = qty_f.reset_index()\n#             qty_f = qty_f.rename(columns={'purchasequantity': column+'_qty_'+str(timeframe)})\n#             qty_f['offerdate'] = date\n    \n#             pred_history = pred_history.merge(qty_f, how='left', on=['id', column, 'offerdate'])\n#             pred_history[newColumnName+'_x'] = pred_history[newColumnName+'_x'].fillna(pred_history[newColumnName+'_y'])\n#             pred_history = pred_history.drop(newColumnName+'_y', axis=1)\n#             pred_history = pred_history.rename(columns={newColumnName+'_x': newColumnName})\n        \n#         count = count + 1\n#         print(count)\n        \n# pred_history.to_csv ('pred_history_f5b.csv', index = False, header=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# from datetime import datetime, timedelta\n# #Adding FEATURE SET 5 for training data\n# #5.7 Total amount spent by a customer for a company 7, 30, 60 days before receiving the offer \n# #5.8 Total amount spent by a customer for a brand 7, 30, 60 days before receiving the offer\n# #5.9 Total amount spent by a customer for a category 7, 30, 60 days before receiving the offer\n# columns = ['company','brand','category']\n# timeframes = [7, 30, 60]\n# dates = history.offerdate.unique()\n# # history1 = history.copy(deep=True)\n# count = 0\n# dataframeMap = {}\n# for date in dates:\n#     date = pd.to_datetime(date)\n#     for timeframe in timeframes:\n#         iddf = offer_transactions.loc[(offer_transactions.date < date) & \n#                                       (offer_transactions.date >= date - timedelta(days=timeframe))]\n#         for column in columns:\n#             newColumnName = column+'_amt_'+str(timeframe)  \n            \n#             if newColumnName not in history.columns:\n#                 history[newColumnName] = np.nan          \n#             amt_f = pd.DataFrame(iddf.groupby(['id', column])['purchaseamount'].sum())\n#             amt_f = amt_f.reset_index()\n#             amt_f = amt_f.rename(columns={'purchaseamount': column+'_amt_'+str(timeframe)})\n#             amt_f['offerdate'] = date\n            \n#             history = history.merge(amt_f, how='left', on=['id', column, 'offerdate'])\n#             history[newColumnName+'_x'] = history[newColumnName+'_x'].fillna(history[newColumnName+'_y'])\n#             history = history.drop(newColumnName+'_y', axis=1)\n#             history = history.rename(columns={newColumnName+'_x': newColumnName})\n            \n#         count = count + 1\n#         print(count)\n        \n# history.to_csv ('history_f5c.csv', index = False, header=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# #Adding FEATURE SET 5 for test data\n# #5.7 Total amount spent by a customer for a company 7, 30, 60 days before receiving the offer \n# #5.8 Total amount spent by a customer for a brand 7, 30, 60 days before receiving the offer\n# #5.9 Total amount spent by a customer for a category 7, 30, 60 days before receiving the offer\n# columns = ['company','brand','category']\n# timeframes = [7, 30, 60]\n# dates = pred_history.offerdate.unique()\n# # history1 = history.copy(deep=True)\n# count = 0\n# dataframeMap = {}\n# for date in dates:\n#     date = pd.to_datetime(date)\n#     for timeframe in timeframes:\n#         iddf = offer_transactions.loc[(offer_transactions.date < date) & \n#                                       (offer_transactions.date >= date - timedelta(days=timeframe))]\n#         for column in columns:\n#             newColumnName = column+'_amt_'+str(timeframe)  \n            \n#             if newColumnName not in pred_history.columns:\n#                 pred_history[newColumnName] = np.nan          \n#             amt_f = pd.DataFrame(iddf.groupby(['id', column])['purchaseamount'].sum())\n#             amt_f = amt_f.reset_index()\n#             amt_f = amt_f.rename(columns={'purchaseamount': column+'_amt_'+str(timeframe)})\n#             amt_f['offerdate'] = date\n            \n#             pred_history = pred_history.merge(amt_f, how='left', on=['id', column, 'offerdate'])\n#             pred_history[newColumnName+'_x'] = pred_history[newColumnName+'_x'].fillna(pred_history[newColumnName+'_y'])\n#             pred_history = pred_history.drop(newColumnName+'_y', axis=1)\n#             pred_history = pred_history.rename(columns={newColumnName+'_x': newColumnName})\n            \n#         count = count + 1\n#         print(count)\n        \n# pred_history.to_csv ('pred_history_f5c.csv', index = False, header=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Reading files with all the features\nhistory = pd.read_csv('history_f5c.csv')\npred_history = pd.read_csv('pred_history_f5c.csv')\nhistory = history.fillna(0)\npred_history = pred_history.fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Creating DUMMY VARIABLES for market, offer, chain, company, brand, category\ndummies = ['market', 'offer', 'chain', 'company', 'brand', 'category']\nfor d in dummies:\n    #adding dummies to train data\n    dummy = pd.get_dummies(history[d], prefix=d, prefix_sep='_', drop_first=True)\n    history = history.merge(dummy, left_index=True, right_index=True)\n    #adding dummies to test data\n    dummy = pd.get_dummies(pred_history[d], prefix=d, prefix_sep='_', drop_first=True)\n    pred_history = pred_history.merge(dummy, left_index=True, right_index=True)\n\npred_history_id = pd.DataFrame(pred_history.id)\n\n#deleting columns that are not required in training & test dataset\ncolumns = ['id','chain','offer','market','prod_id','offerdate','quantity','category','brand','company']\nfor x in columns:\n    if x in history.columns:\n        history = history.drop([x], axis = 1)\n    if x in pred_history.columns:\n        pred_history = pred_history.drop([x], axis = 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Now adding those columns to trainHistory & pred_history table which are not common to both\ncommon_cols = set(history.columns) & set(pred_history)\nprint('Common column length: ', len(common_cols))\nprint('trainHistory column length: ', len(history.columns))\nprint('testHistory column length: ', len(pred_history.columns))\nto_add_in_train = set(pred_history.columns) - common_cols\nprint('Columns to be added in trainHistory: ', to_add_in_train)\nprint('Columns to be added in trainHistory: ', len(to_add_in_train))\nto_add_in_test = set(history.columns) - common_cols\nprint('Columns to be added in testHistory: ', to_add_in_test)\nprint('Columns to be added in testHistory: ', len(to_add_in_test))","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"for column in to_add_in_train:\n    history[column] = 0\nprint('History length: ', len(history.columns))\nfor column in to_add_in_test:\n    pred_history[column] = 0\nprint('pred_history length: ', len(pred_history.columns))\nprint('Common columns: ', len(set(history.columns) & set(pred_history.columns)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"history.repeater.replace(['t','f'],[1,0],inplace=True)\nhistory.repeater = history.repeater.astype('int')\nX = history.drop(['repeater', 'repeattrips'], axis = 1)\ny = pd.DataFrame(history.repeater)\nX.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Data Modeling","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#First thing is to split the data into training and testing, to make sure we don't overfit the model\nX_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.25, random_state=123, stratify=history.repeater)\nprint('Shape of training data: ', X_train.shape, y_train.shape)\nX_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"print('Shape of test data: ', X_test.shape, y_test.shape)\nX_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Running Logistic Regression\nmodel = LogisticRegression(max_iter=100000)\nmodel.fit(X_train, y_train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"predicted_y = model.predict(X_train)\nprint('Training Results')\nprint('Accuracy Score: ', accuracy_score(y_train, predicted_y))\nprint('Classification Report:')\nprint(classification_report(y_train, predicted_y))\nprint('Confusion Matrix:')\nconfusion_matrix(y_train, predicted_y)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"predicted_y = model.predict(X_test)\nprint('Validation Results')\nprint('Accuracy Score: ', accuracy_score(y_test, predicted_y))\nprint('Classification Report:')\nprint(classification_report(y_test, predicted_y))\nprint('Confusion Matrix:')\nconfusion_matrix(y_test, predicted_y)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Spot Check analysis\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n\nmodels = []\nmodels.append(('LogisticRegression', LogisticRegression()))\nmodels.append(('LinearDiscriminantAnalysis', LinearDiscriminantAnalysis()))\nmodels.append(('KNeighborsClassifier', KNeighborsClassifier()))\nmodels.append(('DecisionTreeClassifier', DecisionTreeClassifier()))\nmodels.append(('GaussianNB', GaussianNB()))\nmodels.append(('AdaBoostClassifier', ensemble.AdaBoostClassifier()))\nmodels.append(('BaggingClassifier', ensemble.BaggingClassifier()))\nmodels.append(('ExtraTreesClassifier', ensemble.ExtraTreesClassifier()))\nmodels.append(('GradientBoostingClassifier', ensemble.GradientBoostingClassifier()))\nmodels.append(('RandomForestClassifier', ensemble.RandomForestClassifier()))\nmodels.append(('PassiveAggressiveClassifier', linear_model.PassiveAggressiveClassifier()))\nmodels.append(('RidgeClassifierCV', linear_model.RidgeClassifierCV()))\nmodels.append(('SGDClassifier', linear_model.SGDClassifier()))\nmodels.append(('Perceptron', linear_model.Perceptron()))\nmodels.append(('BernoulliNB', naive_bayes.BernoulliNB()))\nmodels.append(('GaussianNB', naive_bayes.GaussianNB())) \n\nseed = 5\nresults = []\nnames = []\n\n# store predictions\nfrom sklearn.model_selection import cross_val_predict\nfor name, model in models:\n    kfold = KFold(n_splits=3, random_state=seed, shuffle=True)\n    # store the metrics\n    cv_results = cross_val_score(model, X_train, y_train, cv=kfold, scoring='accuracy')\n    results.append(cv_results)\n    names.append(name)\n    message = \"%s %s = %f %s = (%f)\" % (name, 'Mean', cv_results.mean(), 'Standard Deviation', cv_results.std())\n    print(message)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"#Model improvement using hyper tunning parameters\nfrom sklearn.model_selection import GridSearchCV\nfrom sklearn.pipeline import make_pipeline\nfrom sklearn.pipeline import Pipeline\nfrom sklearn.preprocessing import StandardScaler\n# Set grid search params\nmax_depth = [3, 5, 7]\nlearning_rate = [0.1, 0.15]\nrandom_state = [10, 20, 30]\n\ngrid_params_GRA = [{ \n    'max_depth': max_depth,\n    'learning_rate': learning_rate,\n    'random_state': random_state\n    }]\n\nclf = make_pipeline(StandardScaler(), \n                    GridSearchCV(ensemble.GradientBoostingClassifier(),\n                                 param_grid=grid_params_GRA,\n                                 cv=2,\n                                 refit=True))\n\nclf.fit(X_train, y_train)\npredicted_y = clf.predict(X_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"print('Validation Results')\nprint('Accuracy Score: ', accuracy_score(y_test, predicted_y))\nprint('Classification Report:')\nprint(classification_report(y_test, predicted_y))\nprint('Confusion Matrix:')\nconfusion_matrix(y_test, predicted_y)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"from sklearn.metrics import roc_curve\nfrom sklearn.metrics import roc_auc_score\nGBR_roc_auc = roc_auc_score(y_test, clf.predict(X_test))\nfpr, tpr, thresholds = roc_curve(y_test, clf.predict_proba(X_test)[:,1])\nplt.figure()\nplt.plot(fpr, tpr, label='Gradient Boost Classifier (area = %0.2f)' % GBR_roc_auc)\nplt.plot([0, 1], [0, 1],'r--')\nplt.xlim([0.0, 1.0])\nplt.ylim([0.0, 1.05])\nplt.xlabel('False Positive Rate')\nplt.ylabel('True Positive Rate')\nplt.title('Gradient Boosting Classifier ROC Curve')\nplt.legend(loc=\"lower right\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"pred_history = pred_history.drop(['repeater','repeattrips'], axis=1)\npredictions = clf.predict(pred_history)\npredictions = pd.DataFrame(predictions)\npredictions = predictions.rename({0: 'repeatProbability'}, axis=1)\npredictions.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"predictions.repeatProbability.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"pred_history_id = pd.DataFrame(pred_history_id)\nfinalPredictions = pd.merge(pred_history_id, predictions, left_index=True, right_index=True)\nfinalPredictions.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"finalPredictions.to_csv ('finalPredictions.csv', index = False, header=True)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.3"}},"nbformat":4,"nbformat_minor":4}