{"cells":[{"metadata":{"_uuid":"f233b3df-4330-476a-b456-221793bcd9b6","_cell_guid":"19815069-ebad-406b-bf58-a49860e813ae","trusted":true,"collapsed":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport pandas as pd\nimport seaborn as sns\nimport datetime\nfrom scipy import stats as s\nfrom sklearn.model_selection import train_test_split\nimport statistics\n\n# Any results you write to the current directory are saved as output.\npd.set_option('display.max_columns', None)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Merchant Data Exploration"},{"metadata":{"trusted":true},"cell_type":"code","source":"merchantsDataset = pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Make sure that there is no double merchant id with different informations\nlen(merchantsDataset.merchant_id.unique()) == len(merchantsDataset)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#If there are duplicate rows, lets drop it and maintain just one of it\nduplicated_index = [i for i,x in enumerate(merchantsDataset.merchant_id.duplicated(keep='first')) if x==True]\nmerchantsDataset = merchantsDataset.drop(index=duplicated_index)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Now, all of the merchant ids are unique.\n# Now check for the Nan values\nmerchantsNanValues = {col : merchantsDataset[col].isna().sum()/len(merchantsDataset) \n                      for col in merchantsDataset.columns}\n\n#Create a mean sales and mode val for categorical feat based on subsector to fill the nan value\nmerchantFillValues = merchantsDataset.groupby('subsector_id').agg({'avg_sales_lag12':'mean', 'avg_sales_lag3':'mean',\n                                                                   'avg_sales_lag6':'mean', 'category_2': s.mode})\nmerchantFillValues.reset_index(drop=False,inplace=True)\nmerchantFillValues.columns = ['subsector_id', 'avg_sales_lag12_mean', 'avg_sales_lag3_mean', 'avg_sales_lag6_mean', 'category_2_mode'] ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Fill the missing values with mean values and mode\nmerchantsDataset = merchantsDataset.merge(merchantFillValues, on='subsector_id', how='left')\n\nmerchantsDataset['avg_sales_lag12'].fillna(merchantsDataset['avg_sales_lag12_mean'], inplace=True)\nmerchantsDataset['avg_sales_lag6'].fillna(merchantsDataset['avg_sales_lag6_mean'], inplace=True)\nmerchantsDataset['avg_sales_lag3'].fillna(merchantsDataset['avg_sales_lag3_mean'], inplace=True)\nmerchantsDataset['category_2'].fillna(merchantsDataset['category_2_mode'], inplace=True)\nmerchantsDataset = merchantsDataset.drop(merchantFillValues.columns, axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Shift the value so the min val is 1\nmerchantsDataset.avg_sales_lag3 = merchantsDataset.avg_sales_lag3 + abs(min(merchantsDataset.avg_sales_lag3)) +1\nmerchantsDataset.avg_sales_lag6 = merchantsDataset.avg_sales_lag6 + abs(min(merchantsDataset.avg_sales_lag6)) +1\nmerchantsDataset.avg_sales_lag12 = merchantsDataset.avg_sales_lag12 + abs(min(merchantsDataset.avg_sales_lag12)) +1","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Hist Transaction Data Exploration"},{"metadata":{"trusted":true},"cell_type":"code","source":"histTransaction = pd.read_csv('../input/elohisttransaction-fortunejr/histTransactions.csv', delimiter='\\t')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Convert to datetime\nhistTransaction.purchase_date = pd.to_datetime(histTransaction.purchase_date)\n\n#Map categorical feat into binary val\nhistTransaction.authorized_flag = histTransaction.authorized_flag.map({'Y':1, 'N':0})\nhistTransaction.category_1 = histTransaction.category_1.map({'Y':1, 'N':0})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Check for nan values\nhistTransactionNanValues = {col: histTransaction[col].isna().sum()/len(histTransaction) \n                      for col in histTransaction.columns}\n\nhistTransaction = histTransaction[histTransaction.merchant_id.notna()]\n\n#Fill missing feat with mode val\nhistTransaction['category_2'].fillna(1.0,inplce=True)\nhistTransaction['category_3'].fillna('A',inplace=True)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Shift the purchase amount into positive values\nlowestPurchase = abs(min(histTransaction.purchase_amount))\nhistTransaction.purchase_amount = histTransaction.purchase_amount + lowestPurchase +1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Categorize purchase amount into 3 months lag, 6 months lag, and 12 months lag\nhistTransGroup = histTransaction.groupby(['card_id', 'merchant_id','month_lag', 'authorized_flag'], \n                                         observed=True).agg({'purchase_amount': ['sum', 'count'],\n                                                             'category_3': pd.Series.mode,\n                                                             'category_2': pd.Series.mode})\n\ndel(histTransaction)\nhistTransGroup.reset_index(drop=False, inplace=True)\nhistTransGroup.columns = ['card_id', 'merchant_id', 'month_lag', 'authorized_flag', 'purchase_amount_sum',\n                          'purchase_count', 'category_3', 'category_2']\n\n#Create lagging indicators\nhistTransGroup['3_months_lag'] = [True if x >=-3 else False for x in histTransGroup['month_lag']]\nhistTransGroup['6_months_lag'] = [True if x >=-6 else False for x in histTransGroup['month_lag']]\nhistTransGroup['12_months_lag'] = [True if x >=-12 else False for x in histTransGroup['month_lag']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Sum those three months lag purchase\nhistTransGroup_3MonthsSum = histTransGroup.groupby(['card_id', 'merchant_id','authorized_flag', '3_months_lag'])['purchase_amount_sum',\n                                                                                                                  'purchase_count'].sum()\nhistTransGroup_3MonthsSum.reset_index(drop=False, inplace=True)\nhistTransGroup_3MonthsSum = histTransGroup_3MonthsSum[histTransGroup_3MonthsSum['3_months_lag']!=False]\nhistTransGroup_3MonthsSum.drop('3_months_lag', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Sum those six months lag purchase\nhistTransGroup_6MonthsSum = histTransGroup.groupby(['card_id', 'merchant_id','authorized_flag', '6_months_lag'])['purchase_amount_sum',\n                                                                                                                  'purchase_count'].sum()\nhistTransGroup_6MonthsSum.reset_index(drop=False, inplace=True)\nhistTransGroup_6MonthsSum = histTransGroup_6MonthsSum[histTransGroup_6MonthsSum['6_months_lag']!=False]\nhistTransGroup_6MonthsSum.drop('6_months_lag', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Sum those twelve months lag purchase\nhistTransGroup_12MonthsSum = histTransGroup.groupby(['card_id', 'merchant_id','authorized_flag', '12_months_lag']).agg({'purchase_amount_sum':'sum',\n                                                                                                                        'purchase_count':'sum',\n                                                                                                                      'category_3':pd.Series.mode,\n                                                                                                                      'category_2':pd.Series.mode})\nhistTransGroup_12MonthsSum.reset_index(drop=False, inplace=True)\nhistTransGroup_12MonthsSum.drop('12_months_lag', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Merge those lag summation into one dataframe\n\nhistTransaction = histTransGroup_12MonthsSum.merge(histTransGroup_6MonthsSum, how='left',\n                                                  on=['card_id', 'merchant_id', 'authorized_flag'])\n\ndel(histTransGroup_12MonthsSum, histTransGroup_6MonthsSum)\n\nhistTransaction = histTransaction.merge(histTransGroup_3MonthsSum, how='left',\n                                      on=['card_id', 'merchant_id', 'authorized_flag'])\n\ndel(histTransGroup_3MonthsSum)\n\nhistTransaction.columns = ['card_id', 'merchant_id', 'authorized_flag', 'purchase_amount_sum_12',\n       'purchase_count_12', 'category_3', 'category_2', 'purchase_amount_sum_6',\n       'purchase_count_6', 'purchase_amount_sum_3', 'purchase_count_3']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Use authorized transactions only\nhistTransaction = histTransaction[histTransaction.authorized_flag==1]\nhistTransaction.drop('authorized_flag', axis=1, inplace=True)\n\n# Fill nan values with 0\nhistTransaction.fillna(0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Combine History Transaction Dataset with Merchant Information Dataset"},{"metadata":{"trusted":true,"collapsed":true},"cell_type":"code","source":"histTransaction = histTransaction.merge(merchantsDataset[['merchant_id', 'avg_sales_lag3', \n                                                        'avg_purchases_lag3','avg_sales_lag6', \n                                                        'avg_purchases_lag6', 'avg_sales_lag12', \n                                                        'avg_purchases_lag12']], \n                                      how='left', on='merchant_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Normalize the individual purchase with the outlet sales\nhistTransaction.purchase_amount_sum_3 = histTransaction.purchase_amount_sum_3/histTransaction.avg_sales_lag3\nhistTransaction.purchase_amount_sum_6 = histTransaction.purchase_amount_sum_6/histTransaction.avg_sales_lag6\nhistTransaction.purchase_amount_sum_12 = histTransaction.purchase_amount_sum_3/histTransaction.avg_sales_lag12\nhistTransaction.purchase_count_3 = histTransaction.purchase_count_3/histTransaction.avg_purchases_lag3\nhistTransaction.purchase_count_6 = histTransaction.purchase_count_6/histTransaction.avg_purchases_lag6\nhistTransaction.purchase_count_12 = histTransaction.purchase_count_12/histTransaction.avg_purchases_lag12\n\nhistTransaction.drop(['avg_sales_lag3','avg_purchases_lag3','avg_sales_lag6',\n                       'avg_purchases_lag6', 'avg_sales_lag12', \n                       'avg_purchases_lag12'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"collapsed":true},"cell_type":"code","source":"#Sum the normalize purchase based on the individual (card)\nhistTransaction1 = histTransaction.groupby('card_id').agg({'purchase_amount_sum_12':'sum',\n                                                        'purchase_count_12':'sum',\n                                                        'purchase_amount_sum_6':'sum',\n                                                        'purchase_count_6':'sum',\n                                                        'purchase_amount_sum_3':'sum',\n                                                        'purchase_count_3':'sum',\n                                                        'category_3':pd.Series.mode,\n                                                        'category_2':pd.Series.mode})\nhistTransaction1.reset_index(drop=False, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"histTransaction.to_csv('./histTransactions.csv', sep='\\t')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Train Dataset Exploration and Combine"},{"metadata":{"trusted":true},"cell_type":"code","source":"trainDataset = pd.read_csv('../input/elo-merchant-category-recommendation/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Check for nan values\ntrainDatasetNanVal = {col:trainDataset[col].isna().sum()\n                      for col in trainDataset.columns}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Convert first active month datetime into n months\ntrainDataset.first_active_month = pd.to_datetime(trainDataset.first_active_month)\nref_date = datetime.datetime(2018,2, 28)\ntrainDataset.first_active_month = (ref_date-trainDataset.first_active_month)/30\ntrainDataset.first_active_month = trainDataset.first_active_month.dt.days","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"collapsed":true},"cell_type":"code","source":"#Merge historic transation into train dataset\ntrainDataset = trainDataset.merge(histTransaction, how='left', on='card_id')\n\ntrainDataset.category_3 = trainDataset.category_3.map(({'A':2, 'B':1, 'C':0}))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Seperate target and train dataset\ntarget = trainDataset['target']\ntrainDataset.drop(['target', 'card_id'], axis=1, inplace=True)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}