{"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_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"

Elo Merchant Category Recommendation :

","metadata":{"id":"udn5diOPRIHf"}},{"cell_type":"markdown","source":"

Business Problem/Problem Statement :

\n\n> Elo Merchant Category Recommendation is a Kaggle competition which is provided by Elo. As a payment Brand, providing offer promotions and discounts with merchants is a good marketing strategy . Elo needs to keep their customers so loyalty of the customers towards the brand is crucial. For Example, a customer using the Elo card with diverse merchants for a long time, this signifies the user's loyalty is high. To keep the customer as a subscriber, Elo can run different promotional campaign’s targets towards customers with the customer’s favorite or frequently used merchants. These personalized reward programs are planned by the owners of the company to retain existing customers and attract new customers. So, the frequency of using their payment brand should increase. Basically, These programs make the customer’s choice more strongly towards the usage of Elo. The Problem is to find a metric which has to reflect the cardholder’s loyalty with Elo payment brand. Here we have the loyalty score which is a numerical score calculated 2 months after the historical and evaluation period. Elo uses it for their business decision about their promotional campaign.\n\n\n

Dataset Overview :

\n\nThe datasets are largely anonymized, and the meaning of the features are not elaborated. External data is allowed.\n\nThe problem has 5 datasets.\n\n> **train.csv :** It has 6 features, first_active_month, card-id, feature1, feature2, feature3 and target\n\n> **test.csv :** The test set has the same features as the train set without targets\n\n> **historical_transactions.csv :** Contains up to 3 months worth of historical transactions for each card_id\n\n> **merchants.csv :** Contains the transactions at new merchants(merchant_ids that this particular card_id has not yet visited) over a period of two months.\n\n> **new_merchant_transactions.csv :** Two months’ worth of data for each card_id containing ALL purchases that card_id made at merchant_ids that were not visited in the historical data\n\nIn all these datasets, no text data/feature is present. We only have categorical and numerical features. Additionally, by looking at historical_transactions.csv and new_merchant_transactions.csv, we can find that the historical transactions are the transactions occurred before the \"reference date\" and new merchant transactions - the ones that occurred after the reference date (according to the 'month_lag' field, which is generously described as \"month lag to reference date\").\n\n

Mapping the real-world problem to Machine Learning problem :

\n\n> In terms of Machine Learning, we need a metric to measure up the customer's loyalty.A certain loyalty score is assigned for each of the card_id present in train data.\n\n>**Input Features —** Cardholder’s Purchase history, usage time etc.\n\n>**Target Variable —** Loyalty Score\n\n>The Loyalty Score is the target variable for which the Machine Learning Model should be built to predict. **What is loyalty?** According to the Data_Dictionary.xlsx, **loyalty is a numerical score calculated 2 months after historical and evaluation period.** The Loyalty score depends on many aspects of the customers. The purchase history, usage time, merchant’s diversity, etc. Loyalty scores are real-numbers, It directly gives us the intuition that we have to go for a supervised machine learning regression model to solve this problem where features are as our input in train data and output is real number value which is our predicted loyalty score.\n\n

Real-world constraints :

\n\n> The constraint is that the data which has been provided is not real-customer data. The Provided data is Anonymous and simulated, I think this is due to privacy and legal constraints. Simulated data sometimes has an artificially induced bias which will affect the prediction model performance. We have to deal with this specifically.\n\n

Performance Metric :

\n\n> Root mean square error(RMSE) is used to evaluate our predictions with actual loyalty score. We want our predicted loyalty score close to the actual score. So we need to have a lower RMSE score. This gives us the knowledge that on the basis of input features how close our model makes the predictions as compared to actual predictions.\n","metadata":{"id":"WjHqt3AVRIHh"}},{"cell_type":"markdown","source":"**My understanding of the problem :**\n\n* Based on the data in historical_transactions.csv, Elo picked new mechants to recommend for each card holder.\n* The date when Elo began providing recommentations is called the 'reference date'.\n* The recommended mechant data is not provided (so we don't figure out the recommendation algorithm Elo uses).\n* After the reference date, for each card Elo gathered transaction history for all new merchants that appeared on the card.\n* By comparing each card's new merchant activity and the secret list of the merchants recommended by Elo, the loyalty score was calculated.\n* **The goal is to evaluate Elo's recommendation algorithm by trying to predict in which cases it's going to work well (yielding a high loyalty score) and in which cases - not (yielding a low loyalty score).**","metadata":{"id":"PyCBkKH-aksN"}},{"cell_type":"markdown","source":"**Reduce the memory usage :**","metadata":{"id":"wJu97-OSC1qC"}},{"cell_type":"code","source":"## Reference: https://www.kaggle.com/rinnqd/reduce-memory-usage\n\ndef reduce_memory_usage(df, verbose=True):\n '''\n This function reduces the memory sizes of dataframe by changing the datatypes of the columns.\n Parameters\n df - DataFrame whose size to be reduced\n verbose - Boolean, to mention the verbose required or not.\n '''\n numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n start_mem = df.memory_usage().sum() / 1024**2\n for col in df.columns:\n col_type = df[col].dtypes\n if col_type in numerics:\n c_min = df[col].min()\n c_max = df[col].max()\n if str(col_type)[:3] == 'int':\n if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n df[col] = df[col].astype(np.int8)\n elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n df[col] = df[col].astype(np.int16)\n elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n df[col] = df[col].astype(np.int32)\n elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n df[col] = df[col].astype(np.int64)\n else:\n c_prec = df[col].apply(lambda x: np.finfo(x).precision).max()\n if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max and c_prec == np.finfo(np.float16).precision:\n df[col] = df[col].astype(np.float16)\n elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max and c_prec == np.finfo(np.float32).precision:\n df[col] = df[col].astype(np.float32)\n else:\n df[col] = df[col].astype(np.float64)\n end_mem = df.memory_usage().sum() / 1024**2\n if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n return df","metadata":{"id":"Tiiadbd6DATL","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Import all the libraries :**","metadata":{"id":"a1ejOBdfRIHk"}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport warnings\nimport matplotlib\nimport matplotlib.pyplot as plt\nimport datetime\nimport seaborn as sns\nsns.set_style(\"whitegrid\")\nimport warnings \nwarnings.simplefilter(\"ignore\")\nfrom statsmodels.stats.outliers_influence import variance_inflation_factor","metadata":{"id":"a0aazA45RIHm","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Loading Data :**\n","metadata":{"id":"Lnwl2qLqS-fl"}},{"cell_type":"code","source":"train_data = pd.read_csv('../input/elo-merchant-category-recommendation/train.csv')\ntest_data = pd.read_csv('../input/elo-merchant-category-recommendation/test.csv')\nhistorical_data = pd.read_csv('../input/elo-merchant-category-recommendation/historical_transactions.csv')\nnewmerchant_data = pd.read_csv('../input/elo-merchant-category-recommendation/new_merchant_transactions.csv')\nmerchants_data = pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv')","metadata":{"id":"1DjRBpmES6g3","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Reduce memory usage of data :**","metadata":{"id":"fdfMGf3KTzXy"}},{"cell_type":"code","source":"train_data = reduce_memory_usage(train_data)\ntest_data = reduce_memory_usage(test_data)\nhistorical_data = reduce_memory_usage(historical_data)\nnewmerchant_data = reduce_memory_usage(newmerchant_data)\nmerchants_data = reduce_memory_usage(merchants_data)","metadata":{"id":"U1wPGpLfTwAa","outputId":"aaeab50b-5338-40cb-85f4-267f9199cbf4","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Exploring the train and test data files :

","metadata":{"id":"ABWGki1H6vJM"}},{"cell_type":"code","source":"# As our first file is excel file we have to read it with the excel command of pandas.\ndata_dictionary=pd.read_excel('../input/elo-merchant-category-recommendation/Data Dictionary.xlsx')\ndata_dictionary","metadata":{"id":"JUa3xmlORIHo","outputId":"06467807-1a7e-41bb-de36-f72461da80ba","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* This DataDictionary file have the description of all the features in Description column which were included in train.csv.\n\n* From second row we have columns which have the description of all the columns in our data and third row tell us about the card_id and third one is about the first_active_month which tell us about the month and year of purchase of products.\n\n* feature_1, feature_2, feature_3 has categorical value which is in row fourth,fifth,and sixth.\n\n* last row tells us about the prediction on the basis of these features which is known as target column. or we can say loyalty score which is calculated after the two months.","metadata":{"id":"CG17BrtARIHp"}},{"cell_type":"code","source":"print('The number of rows in train_data is:',train_data.shape[0])\nprint('The number of rows in test_data is:',test_data.shape[0])\nplt.bar([0,1],[train_data.shape[0],test_data.shape[0]])\nplt.xticks([0,1],['train_rows','test_rows'])","metadata":{"id":"a53tV2-yRIHq","outputId":"ed3f0fac-2908-4be0-f182-8acd9dfa126c","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_data.head()","metadata":{"id":"PujuFJNFRIHr","outputId":"1d60fc85-47fd-4cb8-a7f3-c6012cc1f3de","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_data.head()","metadata":{"id":"pt97htPpRIHr","outputId":"c3bc9753-836f-4968-ba54-a2a230cec7fe","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_data.info()\nprint(\"********************************************************************\")\ntest_data.info()","metadata":{"id":"29nzghCmRIHs","outputId":"f13484a5-ded8-4c1e-d786-8b6395c9a47c","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Obsaervations :**\n\n* The main data train has 6 values. 'first_active_month', 'card_id', 'feature_1', 'feature_2', 'feature_3', 'target'.\n* first_active_month : This is active_month for card_id. \n* feature_1,2,3 : it is key important but hidden meaning.\n* target : Loyalty numerical score calculated 2 months after historical and evaluation period\n* We can infer that both the data have same columns and overall same structure. So, We will explore both data simultaneously.\n","metadata":{"id":"IVyRhgtjRIHt"}},{"cell_type":"markdown","source":"**Missing values in train and test data :\n(Check for nan values in the whole train and test data)**","metadata":{"id":"gdREgDzyRIHt"}},{"cell_type":"code","source":"train_data.isna().any()","metadata":{"id":"OkpdGONWRIHt","outputId":"3a7b2db2-cb0e-4835-95a3-ddc6d6bdf48f","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** In train Data there is no nan values for any features in train data","metadata":{"id":"K0r45B4lRIHu"}},{"cell_type":"code","source":"test_data.isna().any()","metadata":{"id":"ctdhOSY6RIIC","outputId":"a7184b94-2f31-4988-e800-aa3b922032bc","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_data[test_data['first_active_month'].isna()]","metadata":{"id":"WP7zctWsRIID","outputId":"f5492a3d-b3d1-46d4-e724-62db644df5ad","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :**\nIn the Test Data, there is one row with 'first_active_month' as nan value. Since it is test data we have to impute the value.","metadata":{"id":"4rbmAw0iRIIE"}},{"cell_type":"markdown","source":"**Feature comparison in train and test data features :**","metadata":{"id":"2omwD0ebRIIE"}},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (15, 5));\ntrain_data['feature_1'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='feature_1');\ntrain_data['feature_2'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='feature_2');\ntrain_data['feature_3'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='feature_3');\nplt.suptitle('Counts of categories for train features');\n\nfig, ax = plt.subplots(1, 3, figsize = (15, 5));\ntest_data['feature_1'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='feature_1');\ntest_data['feature_2'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='feature_2');\ntest_data['feature_3'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='feature_3');\nplt.suptitle('Counts of categories for test features');","metadata":{"id":"05KYCGYjRIIO","outputId":"43a42dd3-e97e-4a34-d067-16f0a3a2e7d3","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* We can see from above plots that test and train data are distributed similarly.\n* feature_1, feature_2, feature_3, all are categorical variables\n* feature_1 has 5 unique values\n* feature_2 has 3 unique values\n* feature_3 is a binary column","metadata":{"id":"-OzETm0IRIIP"}},{"cell_type":"markdown","source":"**Anonymised Features Analysis : feature_1, feature_2, feature_3**\n\n**checking distributions with target :**","metadata":{"id":"7ttj5M4mRIIP"}},{"cell_type":"code","source":"plt.figure(figsize=(20,5))\nplt.subplot(131)\nsns.kdeplot(x ='target',data = train_data,hue = 'feature_1',palette='rainbow')\nplt.title('distribution of target over different categories of Feature_1')\nplt.subplot(132)\nsns.kdeplot(x ='target',data = train_data,hue = 'feature_2',palette='Dark2_r')\nplt.title('distribution of target over different categories of Feature_2')\nplt.subplot(133)\nsns.kdeplot(x ='target',data = train_data,hue = 'feature_3',palette='Dark2_r')\nplt.title('distribution of target over different categories of Feature_3')\nplt.show()","metadata":{"id":"Pv3SDeF6RIIQ","outputId":"780d9c10-88fe-434a-8347-f8a67d6d86d1","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** \n\nThe above two plots show a key point : \n\n* while different categories of these features could have various counts, the distribution of target is almost the same. This could mean, that these features aren't really good at predicting target - we'll need other features and feature engineering. Also it is worth noticing that mean target values of each catogory of these features is near zero. This could mean that data was sampled from normal distribution.\n\n**Note:** The same information can be gathered by using box-plot and violin-plot, I have tried all of them. Here, I use kdeplot as I found it more visually appealing. In further analysis I have used Box-plot more often.","metadata":{"id":"r6kJblypRIIQ"}},{"cell_type":"markdown","source":"**let's see Target column seperately :**","metadata":{"id":"xEXLjUXBz3cQ"}},{"cell_type":"code","source":"train_data['target'].describe()","metadata":{"id":"ifk4NZF-RIIG","outputId":"f700673b-25fa-4ae9-f30d-b4cd1db29014","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Plotting the pdf of target variable\nsns.kdeplot(train_data['target'])\nplt.title(\"PDF of Target\")\nplt.show()","metadata":{"id":"rKlr4FQwWDyM","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation:** The target value is almost normally distributed with bunch of outlier value near -30. This distribution indicates that the target value is normalized with pre-decided mean and standard deviation.\n\nThis outlier value of the target is a value which needs more look into the feature EDA to understand cause of it.","metadata":{"id":"3vc9yk_0WKpH"}},{"cell_type":"markdown","source":"**Analyze the outliers :**\n\n","metadata":{"id":"cV3-vUFUWR3n"}},{"cell_type":"code","source":"loyality_score = train_data['target']\nax = loyality_score.plot.hist(bins=20, figsize=(6, 5))\n_ = ax.set_title(\"target histogram\")\nplt.show()\n\nfig, axs = plt.subplots(1,2, figsize=(12, 5))\n_ = loyality_score[loyality_score > 10].plot.hist(ax=axs[0])\n_ = axs[0].set_title(\"target histogram for values greater than 10\")\n_ = loyality_score[loyality_score < -10].plot.hist(ax=axs[1])\n_ = axs[1].set_title(\"target histogram for values less than -10\")\nplt.show()\n","metadata":{"id":"_71V2JpPRIIH","outputId":"8784b21d-c5c2-4912-bcfd-5605e1063404","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* Values range from -33.2 to 17.9\n\n* -33 seems like an outlier as can be seen in the 3rd plot\n\n* other values less than -10 also seem like outliers due to very less in number\n\n* All values above 10 are also looking like outliers","metadata":{"id":"UJD2QyK8RIII"}},{"cell_type":"code","source":"target_sign = loyality_score.apply(lambda x: 0 if x <= 0 else 1)\ntarget_sign.value_counts()","metadata":{"id":"Qi5C-4CtRIIJ","outputId":"c077d3f9-9988-4898-c597-4fa6ecbce540","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** Negative and positive target values are almost in the same proportion","metadata":{"id":"VylT8G_pRIIJ"}},{"cell_type":"code","source":"outliers_in_target= train_data.loc[(train_data['target']< -10) | (train_data['target']>10)]\nprint(' The number of outliers in the data is:',outliers_in_target.shape[0])\nnon_outliers_in_target= train_data.loc[(train_data['target'] >=-10) & (train_data['target']<=10)]\nprint(' The number of non-outliers in the data is:',non_outliers_in_target.shape[0])","metadata":{"id":"GIxf_uSvRIIK","outputId":"e691244c-d2ae-4fe6-e70f-ca1ea73551c2","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Outliers comparison with the feature of target :","metadata":{"id":"kghWmbSTWgv9"}},{"cell_type":"code","source":"plt.figure(figsize=[16,9])\nplt.suptitle('Outlier vs. non-outlier feature distributions', fontsize=20, y=1.1)\n\nfor num, col in enumerate(['feature_1', 'feature_2', 'feature_3', 'target']):\n if col is not 'target':\n plt.subplot(2, 3, num+1)\n non_outlier = non_outliers_in_target[col].value_counts() / non_outliers_in_target.shape[0]\n plt.bar(non_outlier.index, non_outlier, label=('non-outliers'), align='edge', width=-0.3, edgecolor=[0.2]*3,color=['teal'])\n outlier = outliers_in_target[col].value_counts() / outliers_in_target.shape[0]\n plt.bar(outlier.index, outlier, label=('outliers'), align='edge', width=0.3, edgecolor=[0.2]*3,color=['brown'])\n plt.title(col)\n plt.legend()\n\nplt.tight_layout()\nplt.show()","metadata":{"id":"tZp-PSWbWRZJ","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* We can see There are only slight differences between outliers and non-outliers, but they don't seem to be that big and they certainly can't explain the difference between the target values, at least based on the features in the train dataset. It means the card_id's having outliers as loyality score having pretty much similar properties to the regular ones.\n\n* Outliers could be one of the main purposes of this competition. May be those represent fraud or credit default etc. i.e. they are important. The target variable is normally distributed, and outliers seem to be purposely introduced in the loyalty formula. \n\n* As noted in multiple threads over kaggle, more than half of the RMSE is due to the outliers with loyalty scores of ~ -33. They strongly mention, If we try to replace these outliers with the median, retrain the model and submit, we will\nfind our leaderboard score WORSE than if we keep the outliers at their original values. Impute any values will significantly affect the RMSE score for test set. So, imputations have been excluded. This tells us that outliers are included in the test set. Furthermore, given the magnitude of the impact of outliers on the RMSE score, Our focus should be on predicting those outliers as accurately as possible.\n\n* For mitigating the impact of outliers, We can make the outliers as a binary feature whether card's target value is outliers or not. So that while training our model can learn that given entry has target score as outlier or not and use this information while predicting loyality score.","metadata":{"id":"MoZusVaiXPkA"}},{"cell_type":"markdown","source":"**Analysis of feature First_active_month :**","metadata":{"id":"NM7vpCSiRIIQ"}},{"cell_type":"markdown","source":"Distribution of first_active_month across years :","metadata":{"id":"GDH44ijXsL0O"}},{"cell_type":"code","source":"year_train = train_data['first_active_month'].value_counts().sort_index()\nyear_test = test_data['first_active_month'].value_counts().sort_index()\nax = year_train.plot(figsize=(10, 5))\nax = year_test.plot(figsize=(10, 5))\n_ = ax.set_xticklabels(range(2010, 2020))\n_ = ax.set_title(\"Distribution across years\")\n_ = ax.legend(['train', 'test'])","metadata":{"id":"khsd-9KkRIIR","outputId":"548af790-2598-495d-e6c5-a8d41d30e109","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** Years range from 2011 to 2018. But, Most of the data lies in the years ranging from 2016 to 2018 and trends of counts for train and test data are similar.","metadata":{"id":"M5YF7ydQRIIR"}},{"cell_type":"markdown","source":"Distribution of first_active_month across months :","metadata":{"id":"Wql2UNyDsa5E"}},{"cell_type":"code","source":"train_data[\"month\"] = train_data['first_active_month'].str.split(\"-\").str[1]\ntrain_data.head()","metadata":{"id":"m4WOOqSh8lu6","outputId":"4563ecd9-396c-4639-cd19-de16496722e0","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp = train_data['month'].value_counts().sort_index()\nax = temp.plot()\n_ = ax.set_xticklabels(range(-1, 15, 2))\n_ = ax.set_title(\"Distribution across months\")","metadata":{"id":"hluPldyGryey","outputId":"2e729c35-fb1d-4d30-fe04-2a9757e486ac","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** Last 6 months (July to December) has relatively more data than first 6 months (January to June).","metadata":{"id":"naVOZ2SJRIIV"}},{"cell_type":"markdown","source":"**First_active_month Vs Target variable :**","metadata":{"id":"ENA-mIVBRIIX"}},{"cell_type":"code","source":"train_data['first_active_month'] = pd.to_datetime(train_data['first_active_month'],\n format='%Y-%m')","metadata":{"id":"kGhZVUAZRIIW","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.lineplot(x = train_data['first_active_month'], y= train_data['target'])\nplt.title(\"Distribution of target over first_active_month\")\nplt.show()","metadata":{"id":"7tUdyZehRIIX","outputId":"dfa4c63b-e378-4cca-f498-924309309f72","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* The above plot reveals that the target variable (loyalty score) behaves like a damping frequency plot. And it is mentioned in the Buisness problem that the target score is calcuated with the recent year transactions.\n\n* Older Card's: The cards which have first active month from 2012 to 2015.\n\n* new card's: The cards which have first active month from 2015 to 2018.\n\n* The Older card's have large number of transactions which affects the target towards the negative value. and the new card's have transactions which affects the target towards positive value.\n\nSo, I think the type of transactions by the newer card's is different from the older card's which helps in increase the loyalty Score.\n\n","metadata":{"id":"_qixIUMHRIIX"}},{"cell_type":"markdown","source":"**Correlation between variables : Variance Inflation Factor**","metadata":{"id":"ng78t9auKeKe"}},{"cell_type":"code","source":"#Finding Correlation between variables of train_data features\nselected_columns = ['feature_1','feature_2','feature_3']\ndata_frame = train_data[selected_columns]\n\nvif = pd.DataFrame()\nvif[\"VIF Factor\"] = [variance_inflation_factor(data_frame.iloc[:,:].values, i) for i in range(data_frame.shape[1])]\nvif[\"features\"] = data_frame.columns\nvif","metadata":{"id":"mTh-54awORHz","outputId":"d07e67c6-464e-4ae8-a3e4-f80c56e1e2f4","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Finding Correlation between variables of test_data features\nselected_columns = ['feature_1','feature_2','feature_3']\ndata_frame = test_data[selected_columns]\n\nvif = pd.DataFrame()\nvif[\"VIF Factor\"] = [variance_inflation_factor(data_frame.iloc[:,:].values, i) for i in range(data_frame.shape[1])]\nvif[\"features\"] = data_frame.columns\nvif","metadata":{"id":"P14xqDh9CW1Z","outputId":"9a688da3-0aaa-4592-db6b-a19bae8b4fe7","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** \n\n* The VIF values for all the three features are well under 10. So, there is no problem of multicollinearity in the train data and test data.\n* Also VIF values are very near to 0, that interprates fetures are not at all correlated.","metadata":{"id":"bsnwMxIjRIId"}},{"cell_type":"markdown","source":"

Exploring the historical_transactions and new_merchant_transactions data files :

","metadata":{"id":"VXItVYRKRIIe"}},{"cell_type":"code","source":"data_dictionary = pd.read_excel('../input/elo-merchant-category-recommendation/Data_Dictionary.xlsx', sheet_name='history')\ndata_dictionary","metadata":{"id":"VD4gxy7URIIe","outputId":"b3b81d5b-a7aa-4d03-a3d1-de0c553c2f25","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndata_dictionary = pd.read_excel('../input/elo-merchant-category-recommendation/Data_Dictionary.xlsx', sheet_name='new_merchant_period')\ndata_dictionary","metadata":{"id":"Wywh9fQcRIIf","outputId":"0c381563-e2ae-4746-f14e-39c155310c5c","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** After going through Data Dictionary.xlsx, We can infer that both the data have same columns and overall same structure. So, We will Explore both data side by side.","metadata":{"id":"ILRaFfSVRIIf"}},{"cell_type":"code","source":"print(f'{historical_data.shape[0]} rows in data\\n')\nhistorical_data.head()","metadata":{"id":"c8eLDwlgRIIg","outputId":"7dc9f1fa-14b9-42d5-cf14-6c466d166e14","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f'{newmerchant_data.shape[0]} rows in data\\n')\nnewmerchant_data.head()","metadata":{"id":"1ry56I4_RIIj","outputId":"475b11ad-190b-4f79-862d-a3a42e0188c3","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"historical_data.info()","metadata":{"id":"ipSihUT9RIIg","outputId":"1b5a5235-ff8d-4623-f0f7-b8cb98ed5a5a","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"newmerchant_data.info()","metadata":{"id":"S49nv8PbRIIg","outputId":"6f62cba1-a389-4b33-c6ae-f1680d53d283","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\nWe can see that there are:\n\n* 6 features type ID: card_id, merchant_category_id, subsector_id, merchant_id, city_id, state_id\n\n* 2 features type integer/counter: month_lag, installments\n\n* 1 feature type numerical: purchase_amount\n\n* 1 feature type date: purchase_date\n\n* 4 features type categorical: authorized_flag, category_3, category_1, category_2","metadata":{"id":"RG0eFlWRRIIh"}},{"cell_type":"code","source":"historical_data.isna().any()","metadata":{"id":"ksJKQh16RIIj","outputId":"845d10ae-069c-4cee-e168-f4e8fab44a95","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"newmerchant_data.isna().any()","metadata":{"id":"ijrfqyR9RIIk","outputId":"bcf4af76-45cb-4d9a-fefc-78e9999d3e6e","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** Both historical_transaction and new_merchant_transaction have Nan values in same columns which are : merchand_id, category_2, category_3.","metadata":{"id":"R_xNQXCVRIIk"}},{"cell_type":"markdown","source":"**Analysis of Category Features : category_1,category_2 and category_3**","metadata":{"id":"263JX8joRIIk"}},{"cell_type":"code","source":"print('Value counts for category features of Historical Transactions :\\n')\nprint(historical_data['category_1'].value_counts())\nprint('*****************************')\nprint(historical_data['category_2'].value_counts())\nprint('*****************************')\nprint(historical_data['category_3'].value_counts())\n\nprint('\\nValue counts for category features of New merchant Transactions :\\n')\nprint(newmerchant_data['category_1'].value_counts())\nprint('*****************************')\nprint(newmerchant_data['category_2'].value_counts())\nprint('*****************************')\nprint(newmerchant_data['category_3'].value_counts())\n\n","metadata":{"id":"gaxepnUZ9hOW","outputId":"6a00bb48-3958-45a5-941d-2e611f94054f","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (15, 5));\nhistorical_data['category_1'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='category_1');\nhistorical_data['category_2'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='category_2');\nhistorical_data['category_3'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='category_3');\nplt.suptitle('Counts for category features of Historical Transactions New merchant Transactions');\n\n\nfig, ax = plt.subplots(1, 3, figsize = (15, 5));\nnewmerchant_data['category_1'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='category_1');\nnewmerchant_data['category_2'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='category_2');\nnewmerchant_data['category_3'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='category_3');\nplt.suptitle('Counts for category features of New merchant Transactions');","metadata":{"id":"aBmrNignRIIk","outputId":"ca03803e-b98f-49e9-9237-e849689195a6","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** The distribution of these three category features are almost \nidentical in historical and new transactions.This shows these Category feature represent the inately charcterstics of the transactions which is constant over the period.So, these features can be an importance feature in the decision function on final model.","metadata":{"id":"I2gRu3kGRIIl"}},{"cell_type":"markdown","source":"**Distrbution of target over categorical features :**\n\n**Note :** The train.csv file only has the target value, which is the feature we are gonna predict with models build in the future But, transactions data don't have the target values in it for each card_id's. By merging the \"target\" feature with the transactions data will help in Data analysis to fully understand different fetures in transactional dataFrame.","metadata":{"id":"WBcgIDHFRIIl"}},{"cell_type":"code","source":"# merging target value of card_id for each transction in historical_transactions Data\nhistorical_data = pd.merge(historical_data, train_data[['card_id','target']], how = 'outer', on = 'card_id')\n\n# merging target value of card_id for each transction in new_merchants_transactions Data\nnewmerchant_data = pd.merge(newmerchant_data, train_data[['card_id','target']], how = 'outer', on = 'card_id')","metadata":{"id":"S4yzLugoRIIl","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"historical_data.head()","metadata":{"id":"7pKzB1bi-nSD","outputId":"bdefeffe-7f6d-4807-d6b5-af6239467f33","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"newmerchant_data.head()","metadata":{"id":"v4yEPR0V-75o","outputId":"9612069d-4a38-455d-8fe5-08b991c2bf89","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize = (20,10))\nplt.subplot(231)\nsns.kdeplot(x ='target',data = historical_data,hue = 'category_1',palette='Dark2_r')\nplt.title(\"Distribution of target over Category_1 in historical data\")\nplt.subplot(232)\nsns.kdeplot(x ='target',data = historical_data,hue = 'category_2',palette='Dark2_r')\nplt.title(\"Distribution of target over Category_2 in historical data\")\nplt.subplot(233)\nsns.kdeplot(x ='target',data = historical_data,hue = 'category_3',palette='rainbow')\nplt.title(\"Distribution of target over Category_3 in historical data\")\nplt.subplot(234)\nsns.kdeplot(x ='target',data = newmerchant_data,hue = 'category_1',palette='Dark2_r')\nplt.title(\"Distribution of target over Category_1 in new_merchent data\")\nplt.subplot(235)\nsns.kdeplot(x ='target',data = newmerchant_data,hue = 'category_2',palette='Dark2_r')\nplt.title(\"Distribution of target over Category_2 in new_merchent data\")\nplt.subplot(236)\nsns.kdeplot(x ='target',data = newmerchant_data,hue = 'category_3',palette='rainbow')\nplt.title(\"Distribution of target over Category_3 in new_merchent data\")\nplt.tight_layout()\nplt.show()","metadata":{"id":"hCociuMURIIm","outputId":"9693b647-3e7c-43ea-b509-a0ea48d3bbc8","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* These three category features doesn't explicity help to differentiate the target Score(Loyalty Score). Every category have outliers in each of the sub_categories. And Almost all the category have Same IQR range.\n\n* These anonymous features doesn't reveal any important info for further feature engineering of these categories.\n\n**Note:** The same information can be gathered by using box-plot and violin-plot, I have tried all of them. Here, I use kdeplot as I found it more visually appealing. In further analysis I have used Box-plot more often.\n","metadata":{"id":"GoES7aa4RIIm"}},{"cell_type":"markdown","source":"**Authorized Flag Feature Analysis :**","metadata":{"id":"pVOZpPiiRIIn"}},{"cell_type":"code","source":"print('Value counts for Authorized Flag of Historical Transactions :')\nprint(historical_data['authorized_flag'].value_counts())\nprint('*************************************************************')\nprint('Value counts for Authorized Flag of New Merchant Transactions :')\nprint(newmerchant_data['authorized_flag'].value_counts())\n\n#barplot for the authorized_flag feature\nfig, ax = plt.subplots(1, 2, figsize = (12, 5));\nhistorical_data['authorized_flag'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='\\nauthorized_flag(historical_transactions)');\nnewmerchant_data['authorized_flag'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='\\n authorized_flag(new_merchant_transactions)');","metadata":{"id":"2CWQu4Hf5Eo7","outputId":"1bd45941-c948-448f-c262-59615c712772","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* The new transactions have no \"N\" category in authorized_flag. This historical transactions have both \"Y\" and \"N\".\n\n* The authorized_flag 'Y' if approved, 'N' if denied - whether the transaction is approved or Denied.\n\n* If we calculate percentage of authorized transaction in historical transaction. At average 91.3545% transactions are authorized.\n\n* This feature is an important feature for predicting the Loyalty score. because, if the card's transactions are approved most of time, there is a great chance the cards can have high Loyalty Score","metadata":{"id":"-_0lKueBRIIn"}},{"cell_type":"markdown","source":"Distributions of target over authorized flag :","metadata":{"id":"-M_z9pbNRIIo"}},{"cell_type":"code","source":"plt.figure(figsize = (14,5))\nplt.subplot(121)\nsns.boxplot(y = 'target',x= 'authorized_flag', data = historical_data)\nplt.title(\"Distrbutions of target over authorized flag(historical_transactions)\")\nplt.subplot(122)\nsns.boxplot(y = 'target',x= 'authorized_flag', data = newmerchant_data)\nplt.title(\"Distrbutions of target over authorized flag(new_merchant_transactions)\")\nplt.tight_layout()\nplt.show()","metadata":{"id":"mD-WvkgrRIIo","outputId":"c5528b11-e551-47be-e2b2-812769daaa0e","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** \n\n* The authorized Flag also doesn't give a suspectble change in the IQR range between authorized and un_authorized transactions.\n\n* Even for the un_authorized transactions card users have Same IQR. Because of the many transactions by an user, these un_authorized doesn't have much effect.\n\n* But this categorical features also should be included using response coding.","metadata":{"id":"7mjgtD-wRIIo"}},{"cell_type":"markdown","source":"**Analysis of installments feature :**","metadata":{"id":"1H385Y9aRIIr"}},{"cell_type":"code","source":"print('Quantile values for installments in Historical Transaction :')\nprint('25th Percentile :',historical_data['installments'].quantile(0.25))\nprint('50th Percentile :',historical_data['installments'].quantile(0.50))\nprint('75th Percentile :',historical_data['installments'].quantile(0.75))\nprint('100th Percentile :',historical_data['installments'].quantile(1))\nprint('\\n******************************************************************\\n')\nprint('Quantile values for installments in New Merchant Transaction :')\nprint('25th Percentile :',newmerchant_data['installments'].quantile(0.25))\nprint('50th Percentile :',newmerchant_data['installments'].quantile(0.50))\nprint('75th Percentile :',newmerchant_data['installments'].quantile(0.75))\nprint('100th Percentile :',newmerchant_data['installments'].quantile(1))","metadata":{"id":"bIpfuruFRIIr","outputId":"76f2b42d-e121-49af-cd5d-3c99e11465db","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Distribution of target over installment feature :","metadata":{"id":"Ndqj-yL9RIIr"}},{"cell_type":"code","source":"plt.figure(figsize=(14,5))\nplt.subplot(121)\nsns.boxplot(y='target',x= 'installments', data = historical_data)\nplt.title(\"Distrbutions of target over installments(historical_transactions)\")\nplt.subplot(122)\nsns.boxplot(y='target',x = 'installments', data = newmerchant_data)\nplt.title(\"Distrbutions of target over installments(new_merchant_transactions)\")\nplt.tight_layout()\nplt.show()","metadata":{"id":"cT251YafRIIs","outputId":"d768180c-2d08-4f28-b68d-8b27ff68f15b","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** The installments also have outliers, these outliers should be taken care in data preprocessing. In historical_transactions and new_merchants_transactions the 75% of installments are below 1. So, most of the payments through the cards are instant payments or short term installments.","metadata":{"id":"FCoD_NRSRIIs"}},{"cell_type":"markdown","source":"**Analysis of purchase_amount feature :**","metadata":{"id":"Y0ykAqgHRIIp"}},{"cell_type":"code","source":"print('Quantile values for purchase amount in Historical Transaction :')\nprint('25th Percentile :',historical_data['purchase_amount'].quantile(0.25))\nprint('50th Percentile :',historical_data['purchase_amount'].quantile(0.50))\nprint('75th Percentile :',historical_data['purchase_amount'].quantile(0.75))\nprint('100th Percentile :',historical_data['purchase_amount'].quantile(1))\nprint('\\n******************************************************************\\n')\nprint('Quantile values for purchase amount in New Merchant Transaction :')\nprint('25th Percentile :',newmerchant_data['purchase_amount'].quantile(0.25))\nprint('50th Percentile :',newmerchant_data['purchase_amount'].quantile(0.50))\nprint('75th Percentile :',newmerchant_data['purchase_amount'].quantile(0.75))\nprint('100th Percentile :',newmerchant_data['purchase_amount'].quantile(1))","metadata":{"id":"twnJ6GOxRIIp","outputId":"a8c9c681-261f-48b0-990a-6e7accd97cf7","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** The IQR range value is very small. And there is one outlier which have 6010603.9717525. These outlier can skew the final model performance. purchase_amount is normalized. Let's have a look at it nevertheless.","metadata":{"id":"M4MCmPt7RIIp"}},{"cell_type":"code","source":"plt.figure(figsize = (13,5))\nplt.subplot(121)\nplt.title('Purchase amount(Historical Transaction)');\nhistorical_data['purchase_amount'].plot(kind='hist');\nplt.subplot(122)\nplt.title('Purchase amount(NewMerchant Transaction)');\nnewmerchant_data['purchase_amount'].plot(kind='hist');","metadata":{"id":"SRWhc42FRIIp","outputId":"ac64203d-7e2f-4c07-d192-69e955f24d58","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('For purchase_amount in Historical transactions :')\nfor i in [-1, 0]:\n n = historical_data.loc[historical_data['purchase_amount'] < i].shape[0]\n print(f\"There are {n} transactions with purchase_amount less than {i}.\")\nfor i in [0, 10, 100]:\n n = historical_data.loc[historical_data['purchase_amount'] > i].shape[0]\n print(f\"There are {n} transactions with purchase_amount more than {i}.\")\n \nprint('\\n******************************************************************\\n')\nprint('For purchase_amount in New Merchant transactions :')\nfor i in [-1, 0]:\n n = newmerchant_data.loc[newmerchant_data['purchase_amount'] < i].shape[0]\n print(f\"There are {n} transactions with purchase_amount less than {i}.\")\nfor i in [0, 10, 100]:\n n = newmerchant_data.loc[newmerchant_data['purchase_amount'] > i].shape[0]\n print(f\"There are {n} transactions with purchase_amount more than {i}.\")","metadata":{"id":"ju-XeAl0RIIq","outputId":"b379e744-1972-4e43-c9ee-516dd0b0b2e2","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** As we can see the major chunk of transactions has purchase_amount less than 0. let us see Purchase amount distribution for negative values.","metadata":{"id":"Vg2-P0jYRIIq"}},{"cell_type":"code","source":"plt.figure(figsize = (14,5))\nplt.subplot(121)\nplt.title(' Negative purchase_amount distribution(Historical)');\nhistorical_data.loc[historical_data['purchase_amount'] < 0, 'purchase_amount'].plot(kind='hist');\nplt.subplot(122)\nplt.title('Negative purchase_amount distribution(New Merchant)');\nnewmerchant_data.loc[newmerchant_data['purchase_amount'] < 0, 'purchase_amount'].plot(kind='hist');","metadata":{"id":"kQJKv9CXRIIq","outputId":"0fa917e0-0d54-4978-8bbc-4d9562214277","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** It seems that almost all transactions have purchase amount in range (-1, 0). Quite a strong normalization and high outliers, which will need to be processed.","metadata":{"id":"aouPEB3-RIIr"}},{"cell_type":"markdown","source":"Now, let's see purchase_amount feature over target variable :\n\n","metadata":{"id":"88c_gZSfXm7h"}},{"cell_type":"code","source":"#There is one outlier which have value 6010603.9717525. So, I remove that for EDA part.\nhistorical_data = historical_data[historical_data['purchase_amount'] != 6010603.9717525]","metadata":{"id":"l1FVmi9xXrUh","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(12,5))\nplt.subplot(121)\nsns.scatterplot(data=historical_data, x=\"purchase_amount\", y=\"target\")\nplt.title(\"purchase_amount(historical_transaction) over target\")\nplt.subplot(122)\nsns.scatterplot(data=newmerchant_data, x=\"purchase_amount\", y=\"target\")\nplt.title(\"purchase_amount(newmerchant_transaction) over target\")\nplt.tight_layout()\nplt.show()","metadata":{"id":"wErUAjH5XxuN","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* One key observation here is, Most of the outliers in target having value around -30 are having very less purchase amount.\n* With the increase in purchase amount customer become more loyal, as target score increases.","metadata":{"id":"VMyHBcP2Xmv5"}},{"cell_type":"markdown","source":"**Analysis of feature Month_lag :**","metadata":{"id":"70w1LSM7RIIt"}},{"cell_type":"code","source":"plt.figure(figsize = (13,5))\nplt.subplot(121)\nplt.title('Month lag(Historical Transaction)');\nhistorical_data['month_lag'].plot(kind='hist');\nplt.subplot(122)\nplt.title('Month lag(NewMerchant Transaction)');\nnewmerchant_data['month_lag'].plot(kind='hist');","metadata":{"id":"vDcpjdq-RIIt","outputId":"75c5de66-9024-4181-eee2-bc16eab41354","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Distribution of target over month_lag feature :","metadata":{"id":"tr5WwjsZAHh7"}},{"cell_type":"code","source":"plt.figure(figsize = (14,5))\nplt.subplot(121)\nsns.boxplot(y= 'target',x= 'month_lag', data = historical_data)\nplt.title(\"Distrbutions of target over month_lag(historical_transactions)\")\n\nplt.subplot(122)\nsns.boxplot(y= 'target',x= 'month_lag', data = newmerchant_data)\nplt.title(\"Distrbutions of target over month_lag(historical_transactions)\")\nplt.tight_layout()\nplt.show()","metadata":{"id":"Awt3xvqtRIIv","outputId":"3d21f0c4-a75b-4253-aeff-168a6db4a612","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** \n\n* The Month_lag gives important info to predict the loyalty score. For a Purchase in installments, how many months the card lags from the actual end date of installment is the month_lag feature.\n\n* The historical_transactions have month_lags from 0 to 13. which means the cards with transactions in histortical_transactions data have lag of installments from 0 to 13. But, the new_merchant_transactions have month_lag 1 and 2 only.\n\n* This again proves the difference in the transactions type between the historical and new merchants.","metadata":{"id":"1w1-CzINRIIv"}},{"cell_type":"markdown","source":"**Analysis of feture purchase_date :**","metadata":{"id":"jlNI3u1cRIIv"}},{"cell_type":"markdown","source":"At first, we convert purchase_date to datetime format :","metadata":{"id":"wGn_wPcVRIIw"}},{"cell_type":"code","source":"historical_data['purchase_date'] = pd.to_datetime(historical_data['purchase_date'],\n format='%Y-%m-%d %H:%M:%S')\nnewmerchant_data['purchase_date'] = pd.to_datetime(newmerchant_data['purchase_date'],\n format='%Y-%m-%d %H:%M:%S')","metadata":{"id":"iyL-jlJvRIIw","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Number of transactions vs Year :","metadata":{"id":"OgSbEjLARsv2"}},{"cell_type":"code","source":"#barplot for the Number of transactions vs Year\nfig, ax = plt.subplots(1, 2, figsize = (14, 5));\nhistorical_data['purchase_date'].dt.year.value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='\\n Transactions Vs Year(histortical_transactions)');\nnewmerchant_data['purchase_date'].dt.year.value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='\\n Transactions Vs Year(new_merchant transactions)');\n\n\nprint('Year-Wise Percentage distribution of purchase_date(Historical-Transaction) :')\nprint(historical_data['purchase_date'].dt.year.value_counts(normalize = True)*100)\nprint('\\nYear-Wise Percentage distribution of purchase_date(NewMerchant-Transaction) :')\nprint(newmerchant_data['purchase_date'].dt.year.value_counts(normalize = True)*100)","metadata":{"id":"FO7U1sE-CB2n","outputId":"6cdbbe3b-d2df-4197-a89c-2e6cd215e013","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* In historical_transactions, The transactions with respect to year 2017 is way more (~82%) than transactions in 2018 (18%). \n\n* But, In new_merchant_transactions, transactions with respect to 2018 is way more (~85%) than transactions in 2018 (15%).\n\n* Then we can say, new_merchant_transactions are the recent year transactions. This is the reason for the disparity in the purchase amount and installment features.","metadata":{"id":"oWVa5g6SRIIw"}},{"cell_type":"markdown","source":"Number of transactions vs Week","metadata":{"id":"zfbYF9ECRm3I"}},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 2, figsize = (15, 5));\nhistorical_data['purchase_date'].dt.dayofweek.value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='Transactions Vs dayofweek(histortical_transactions)');\nnewmerchant_data['purchase_date'].dt.dayofweek.value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='Transactions Vs dayofweek(new_merchant_transactions)');","metadata":{"id":"ELAn8mf8SQtp","outputId":"ca7ce5ea-54d9-42fc-d127-b03e22fc9e8a","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Distribution of target over dayofweek :","metadata":{"id":"EDWm6l71HVq7"}},{"cell_type":"code","source":"plt.figure(figsize=(14,5))\nplt.subplot(121)\nsns.boxplot(y = historical_data['target'], x = historical_data['purchase_date'].dt.dayofweek)\nplt.xticks(range(0,7),labels=['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'])\nplt.xlabel('Days of week')\nplt.title(\"Distribution of target over dayofweek(histortical_transactions)\")\n\nplt.subplot(122)\nsns.boxplot(y = historical_data['target'], x = newmerchant_data['purchase_date'].dt.dayofweek)\nplt.xticks(range(0,7),labels=['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'])\nplt.xlabel('Days of week')\nplt.title(\"Distribution of target over dayofweek(new_merchant_transactions)\")\nplt.tight_layout()\nplt.show()","metadata":{"id":"4Z5MMCleRIIx","outputId":"b4da1c26-91d3-4ef3-f720-a5d9c1d2cb26","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Number of transactions vs hour","metadata":{"id":"B5Xy6s_aRbwS"}},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 2, figsize = (15, 5));\nhistorical_data['purchase_date'].dt.hour.value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='Transactions Vs hour(histortical_transactions)');\nnewmerchant_data['purchase_date'].dt.hour.value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='Transactions Vs hour(new_merchant_transactions)');","metadata":{"id":"KRYzxGR0PSd1","outputId":"74db0d81-3d40-4f00-e6b2-33e379177485","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Distribution of target over hour :","metadata":{"id":"EqR_NBK-HFcL"}},{"cell_type":"code","source":"plt.figure(figsize=(15,5))\nplt.subplot(121)\nsns.boxplot(y = historical_data['target'], x = historical_data['purchase_date'].dt.hour)\nplt.xlabel('Hour')\nplt.xticks(range(0,24))\nplt.title(\"Distribution of target over hour(histortical_transactions)\")\n\nplt.subplot(122)\nsns.boxplot(y = historical_data['target'], x = newmerchant_data['purchase_date'].dt.hour)\nplt.xlabel('Hour')\nplt.xticks(range(0,24))\nplt.title(\"Distribution of target over hour(new_merchant_transactions)\")\nplt.tight_layout()\nplt.show()\n","metadata":{"id":"Up1a0KSYRIIx","outputId":"87359542-48f2-4ab2-e1e3-30b0fb3bc4ae","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* From the distribution of both weekly and hourly transactions count, these transactions have not much difference in their distributions.\n\n* Since, the data given in the problem is a generated data and not a real time data. The distribution of the transactions over the purchase date is similar.\n\n* But, the type of transactions differs from historical and new_merchants in terms of purchase_amount,month_lag and installments.\n\n* By checking the number of merchants are in both historical and new_merchants transactions, we can get exclusive informations of the merchants.","metadata":{"id":"vhGXPDHgRIIy"}},{"cell_type":"markdown","source":"**Let's create a feature called Number of transactions for each card_id and see - How it impacts target variable ?**\n\nNumber of Transactions feature is not explicitly given in any of the file but we can derive it with some hacks :","metadata":{"id":"D6pIeWt_pHol"}},{"cell_type":"code","source":"#For historical transaction\ng = historical_data[['card_id']].groupby('card_id')\ndf_transaction_counts = g.size().reset_index(name='num_transactions')\nhistorical_data = pd.merge(historical_data ,df_transaction_counts, on=\"card_id\",how='left')\nhistorical_data.head()","metadata":{"id":"uB1hMVRA323N","outputId":"e2f630c6-0b24-4b1a-8363-ba935a259001","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"historical_data['num_transactions'].describe()","metadata":{"id":"RszZR_rANxCX","outputId":"fbdf69a7-2292-433c-9702-ba54a061bd89","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#For New Merchant transaction\ng = newmerchant_data[['card_id']].groupby('card_id')\ndf_transaction_counts = g.size().reset_index(name='num_transactions')\nnewmerchant_data = pd.merge(newmerchant_data ,df_transaction_counts, on=\"card_id\",how='left')\nnewmerchant_data.head()","metadata":{"id":"3LPGsfTzpGgW","outputId":"e0bc5835-b6e9-4497-8646-860c91166c6e","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"newmerchant_data['num_transactions'].describe()","metadata":{"id":"M2hDPkdCO16w","outputId":"e2af3b24-3599-4cf1-dbeb-0f209423041a","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(12,5))\nplt.subplot(121)\nsns.scatterplot(data=historical_data, x=\"num_transactions\", y=\"target\")\nplt.title(\"Number of transactions(historical_transaction) VS target\")\nplt.subplot(122)\nsns.scatterplot(data=newmerchant_data, x=\"num_transactions\", y=\"target\")\nplt.title(\"Number of Transactions(newmerchant_transaction) VS target\")\nplt.tight_layout()\nplt.show()","metadata":{"id":"tZhj5upNfdFC","outputId":"c65e099c-714f-41d8-8173-5c7cc1c622a2","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* One key observation here is, Most of the outliers in target having value around -30 are having very less no of transactions.\n* With increase in no of transactions customer become more loyal, as target score increases.","metadata":{"id":"eMyJyjdt95Wu"}},{"cell_type":"markdown","source":"**Correlation between variables : Variance Inflation Factor**","metadata":{"id":"oKPHANHCbhE_"}},{"cell_type":"code","source":"selected_columns = ['category_2','month_lag','purchase_amount','state_id','subsector_id', 'installments']\ndata_frame = newmerchant_data[selected_columns]\n\ndata_frame = data_frame.dropna()\n\nvif = pd.DataFrame()\nvif[\"VIF Factor\"] = [variance_inflation_factor(data_frame.iloc[:,:].values, i) for i in range(data_frame.shape[1])]\nvif[\"features\"] = data_frame.columns\nvif","metadata":{"id":"N-qbcMjBJ_Ss","outputId":"64d5f9d6-d8ec-4d0e-90af-bf01be83cb9a","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"All values are under 10, let's add some more features and again we'll calculate the VIF :\n\n\n","metadata":{"id":"Bi_vtrTYLDUq"}},{"cell_type":"code","source":"Dict = {'A':1,'B':2,'C':3}\nDict1 = {'Y':1,'N':0}\n\nselected_columns = ['authorized_flag','category_3','category_2','month_lag','purchase_amount','state_id','subsector_id', 'installments']\ndata_frame = newmerchant_data[selected_columns]\ndata_frame['category_3'] = data_frame['category_3'].map(Dict)\ndata_frame['authorized_flag'] = data_frame['authorized_flag'].map(Dict1)\n\ndata_frame = data_frame.dropna()\n\nvif = pd.DataFrame()\nvif[\"VIF Factor\"] = [variance_inflation_factor(data_frame.iloc[:,:].values, i) for i in range(data_frame.shape[1])]\nvif[\"features\"] = data_frame.columns\nvif","metadata":{"id":"XJYpygregZfI","outputId":"918bf4b4-b787-4bd5-8884-0fec136ce3d9","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* The value for the authorized flag is somewhat higher, it is around 32 which indicates possible correlation. So this variable needs further investigation.\n\n* Other than the authorized flag the remaining variables doesn't look correlated. They are well under 2.","metadata":{"id":"TTXyFIzFRIIz"}},{"cell_type":"markdown","source":"

Exploring the Merchant Data :

\n\n","metadata":{"id":"jPWXcMhZRIIz"}},{"cell_type":"code","source":"data_dictionary = pd.read_excel('../input/elo-merchant-category-recommendation/Data_Dictionary.xlsx', sheet_name='merchant')\ndata_dictionary","metadata":{"id":"DvNMlL7wRIIz","outputId":"3aea4bee-0c82-411b-8e27-2f51ee32b213","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merchants_data.head()","metadata":{"id":"rfKLFZC4RIIz","outputId":"05795d23-420a-4102-f957-3c9d5332a56a","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merchants_data.info()","metadata":{"id":"QvQcFgBARII0","outputId":"a6d08418-0e90-4fbf-99ec-c569c463821e","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merchants_data.isna().any()","metadata":{"id":"1xIhqTqdRII0","outputId":"bcaf6ad7-bb67-4649-9b1a-335db63f80e3","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** Merchant data has missing values in columns : avg_sales_lag3, avg_sales_lag6 and avg_sales_lag12 ","metadata":{"id":"2UlevBw9RII0"}},{"cell_type":"markdown","source":"**Analysis of Numerical features : numerical_1 and numerical_2**","metadata":{"id":"zqvHbkdiRII1"}},{"cell_type":"code","source":"print('Quantile values for numeric_1 in Transaction data:')\nprint('25th Percentile :',merchants_data['numerical_1'].quantile(0.25))\nprint('50th Percentile :',merchants_data['numerical_1'].quantile(0.50))\nprint('75th Percentile :',merchants_data['numerical_1'].quantile(0.75))\nprint('100th Percentile :',merchants_data['numerical_1'].quantile(1))\nprint('\\n******************************************************************\\n')\nprint('Quantile values for numeric_2 in Transaction data:')\nprint('25th Percentile :',merchants_data['numerical_2'].quantile(0.25))\nprint('50th Percentile :',merchants_data['numerical_2'].quantile(0.50))\nprint('75th Percentile :',merchants_data['numerical_2'].quantile(0.75))\nprint('100th Percentile :',merchants_data['numerical_2'].quantile(1))","metadata":{"id":"PjYxBupKRII2","outputId":"f8a93c22-7393-4a6b-89e2-fbe3fb35714f","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** I think the Distribution of numerical_1 and numerical_2 featurs are almost identical, because three qunatiles have identical values.","metadata":{"id":"fBfsB-f6RII2"}},{"cell_type":"code","source":"plt.figure(figsize=(12,5) )\nplt.subplot(121)\nsns.kdeplot(np.log10(merchants_data['numerical_1']),shade=True)\nplt.title(\"PDF of numerical_1 in LogScale\")\nplt.xlabel('log(numerical_1)')\nplt.subplot(122)\nsns.kdeplot(np.log10(merchants_data['numerical_2']),shade=True)\nplt.title(\"PDF of numerical_2 in LogScale\")\nplt.xlabel('log(numerical_2)')\nplt.tight_layout()\nplt.show()","metadata":{"id":"hqc_xaDQrAFa","outputId":"e76ced5a-4d44-4975-f64c-667da0b1dc61","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** After plotting PDF, it is very clear that both the features have same distribution, may be they are duplicates of each other.\n\n**Note :** The values for numeric_1 and numeric_2 are mostly -ve and very near to zero. So, I preferred LogScale for analysis.","metadata":{"id":"ke9LcwUA4IQj"}},{"cell_type":"markdown","source":"**Analysis of the three anonymized category features : category_1,category_2 and category_4**","metadata":{"id":"dkLyz7J6RII0"}},{"cell_type":"code","source":"print('Value counts for category features of Merchants data :\\n')\nprint(merchants_data['category_1'].value_counts())\nprint('******************************')\nprint(merchants_data['category_2'].value_counts())\nprint('******************************')\nprint(merchants_data['category_4'].value_counts())\n\nfig, ax = plt.subplots(1, 3, figsize = (15, 5));\nmerchants_data['category_1'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='category_1');\nmerchants_data['category_2'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='category_2');\nmerchants_data['category_4'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='category_3');\nplt.suptitle('Counts for category features of Merchants_data');\n \n","metadata":{"id":"Hfr9itTaRII1","outputId":"2d4f2d24-3867-41fc-cf2f-0c3838b0f059","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** These are anonymous categories, which can represent some properties of the merchants, which is still unclear after merging with the transactions data it can reveal more info.","metadata":{"id":"kU3lcmi8RII1"}},{"cell_type":"markdown","source":"**Analysis of feture most_recent_sales_range and most_recent_purchases_range :**","metadata":{"id":"K5n1fqHGRII6"}},{"cell_type":"code","source":"print(merchants_data['most_recent_sales_range'].value_counts())\nprint('*******************************************')\nprint(merchants_data['most_recent_purchases_range'].value_counts())\n\nfig, ax = plt.subplots(1, 2, figsize = (10, 5));\nmerchants_data['most_recent_sales_range'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='most_recent_sales_range');\nmerchants_data['most_recent_purchases_range'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='most_recent_purchases_range');","metadata":{"id":"qsPEX0Y1RII6","outputId":"4e07c57d-ef5f-4b1d-ba60-6138f225ff0a","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :**\n\n* Both the features have very similar distributions.\n\n* The sales range in last active month is a categorical feature with \"A\",\"B\",\"C\",\"D\",\"E\". after observing the trend from graph we can say Range of revenue (monetary units) is in order E > D > C > B > A.\n\n* The Bar Plot shows there are many merchants with revenue range of \"E\" than other ranges.\n\n* And also, Bar Plot shows there are many merchants with purchase quantity range of \"E\" than other ranges.\n\n* The sales range and purchase range can be used in aggregated to know the card_id's most visited merchants in the final features for training.","metadata":{"id":"EgW0PfpfRII7"}},{"cell_type":"markdown","source":"**Analysis of Sales Average features : avg_sales_lag3,avg_sales_lag3,avg_sales_lag3,avg_purchases_lag6,avg_purchases_lag6 and avg_purchases_lag6**\n","metadata":{"id":"hL3BmsZiRII2"}},{"cell_type":"code","source":"print('Quantile values for avg_sales_lag3 in Transaction data:')\nprint('25th Percentile :',merchants_data['avg_sales_lag3'].quantile(0.25))\nprint('50th Percentile :',merchants_data['avg_sales_lag3'].quantile(0.50))\nprint('75th Percentile :',merchants_data['avg_sales_lag3'].quantile(0.75))\nprint('100th Percentile :',merchants_data['avg_sales_lag3'].quantile(1))\nprint('\\n******************************************************************\\n')\nprint('Quantile values for avg_sales_lag6 in Transaction data:')\nprint('25th Percentile :',merchants_data['avg_sales_lag6'].quantile(0.25))\nprint('50th Percentile :',merchants_data['avg_sales_lag6'].quantile(0.50))\nprint('75th Percentile :',merchants_data['avg_sales_lag6'].quantile(0.75))\nprint('100th Percentile :',merchants_data['avg_sales_lag6'].quantile(1))\nprint('Quantile values for numeric_1 in Transaction data6:')\nprint('\\n******************************************************************\\n')\nprint('Quantile values for avg_sales_lag12 in Transaction data:')\nprint('25th Percentile :',merchants_data['avg_sales_lag12'].quantile(0.25))\nprint('50th Percentile :',merchants_data['avg_sales_lag12'].quantile(0.50))\nprint('75th Percentile :',merchants_data['avg_sales_lag12'].quantile(0.75))\nprint('100th Percentile :',merchants_data['avg_sales_lag12'].quantile(1))","metadata":{"id":"QOoiTXX3RII3","outputId":"ea6dfab0-6990-449c-f601-01092cd7a487","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Statistical insights for avg_purchases_lag3 in Transaction data:')\nprint(merchants_data['avg_purchases_lag3'].describe())\nprint('\\n******************************************************************\\n')\nprint('Statistical insights for avg_purchases_lag6 in Transaction data:')\nprint(merchants_data['avg_purchases_lag6'].describe())\nprint('\\n******************************************************************\\n')\nprint('Statistical insights for avg_purchases_lag12 in Transaction data:')\nprint(merchants_data['avg_purchases_lag12'].describe())","metadata":{"id":"BZuwFwcVRII3","outputId":"63a806ba-aed8-4503-a99e-430c02bab377","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** There are outliers with the value inf in each of these columns, we have to deal with it. For EDA part, I am removing the corrosponding rows with the inf values in the columns avg_purchases_lag3,avg_purchases_lag6,avg_purchases_lag12. We will se what else we can do with these outliers in preprocessing part.","metadata":{"id":"MW9MVL2GRII4"}},{"cell_type":"code","source":"merchants_data = merchants_data[merchants_data['avg_purchases_lag3'] != np.inf]\nmerchants_data = merchants_data[merchants_data['avg_purchases_lag6'] != np.inf]\nmerchants_data = merchants_data[merchants_data['avg_purchases_lag12'] != np.inf]","metadata":{"id":"3ODZn-fcRII4","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(20,10))\nplt.subplot(231)\nsns.kdeplot(np.log10(merchants_data['avg_sales_lag3']),shade=True)\nplt.title(\"PDF of avg_sales_lag3 in LogScale\")\nplt.xlabel('log(avg_sales_lag3)')\nplt.subplot(232)\nsns.kdeplot(np.log10(merchants_data['avg_sales_lag6']),shade=True)\nplt.title(\"PDF of avg_sales_lag6 in LogScale\")\nplt.xlabel('log(avg_sales_lag6)')\nplt.subplot(233)\nsns.kdeplot(np.log10(merchants_data['avg_sales_lag12']),shade=True)\nplt.title(\"PDF of avg_sales_lag12 in LogScale\")\nplt.xlabel('log(avg_sales_lag12)')\nplt.subplot(234)\nsns.kdeplot(np.log10(merchants_data['avg_purchases_lag3']),shade=True)\nplt.title(\"PDF of avg_purchases_lag3 in LogScale\")\nplt.xlabel('log(avg_purchases_lag3)')\nplt.subplot(235)\nsns.kdeplot(np.log10(merchants_data['avg_purchases_lag6']),shade=True)\nplt.title(\"PDF of avg_purchases_lag6 in LogScale\")\nplt.xlabel('log(avg_purchases_lag6)')\nplt.subplot(236)\nsns.kdeplot(np.log10(merchants_data['avg_purchases_lag12']),shade=True)\nplt.title(\"PDF of avg_purchases_lag12 in LogScale\")\nplt.xlabel('log(avg_purchases_lag12)')\nplt.tight_layout()\nplt.show()","metadata":{"id":"DoPeRxH06FSD","outputId":"be4933a3-c905-48c0-a4ea-aae11063970d","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** \n\n* The average purchases and sales across 3,6 and 12 months are distributed near 1.\n\n* And, there are outliers in all the average sales and purchases. These features gives info about the merchants but not about the card_id's. The information about the merchants have to cumulated for each card_id's.\n\n**Note :** The values for All the sales features listed above are mostly surrounded very near to 1. So, I preferred LogScale for analysis.","metadata":{"id":"OPAlZ7ysRII5"}},{"cell_type":"markdown","source":"**Quantity of active months : Analysis of features(active_months_lag3,active_months_lag6 and active_months_lag3) :**","metadata":{"id":"e7qFvYzxRII5"}},{"cell_type":"code","source":"print(merchants_data['active_months_lag3'].value_counts())\nprint('**********************************')\nprint(merchants_data['active_months_lag6'].value_counts())\nprint('**********************************')\nprint(merchants_data['active_months_lag12'].value_counts())\n\nfig, ax = plt.subplots(1, 3, figsize = (15, 5));\nmerchants_data['active_months_lag3'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='active_months_lag3');\nmerchants_data['active_months_lag6'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='active_months_lag6');\nmerchants_data['active_months_lag12'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='active_months_lag12');\nplt.suptitle('Counts of Active month lags');","metadata":{"id":"FBhvua_ARII6","outputId":"543df8dc-8e5f-458d-b9c0-b86e82beec53","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observations :** The active months features are greatly skewed and doesn't provide any vital information about the cards.","metadata":{"id":"aTt19IOURII6"}},{"cell_type":"markdown","source":"**Correlation between variables : Variance Inflation Factor**","metadata":{"id":"BBGbGPBsRII7"}},{"cell_type":"code","source":"selected_columns = ['numerical_1', 'numerical_2','category_2','avg_sales_lag3','avg_sales_lag6','avg_sales_lag12','avg_purchases_lag3','avg_purchases_lag6','avg_purchases_lag12','active_months_lag3','active_months_lag6','active_months_lag12']\ndata_frame = merchants_data[selected_columns]\ndata_frame = data_frame.dropna()\n\nvif = pd.DataFrame()\nvif[\"VIF Factor\"] = [variance_inflation_factor(data_frame.iloc[:,:].values, i) for i in range(data_frame.shape[1])]\nvif[\"features\"] = data_frame.columns\nvif","metadata":{"id":"k1pl4mVuHXp4","outputId":"10ffd0a4-b0f5-4f84-b145-b33be61667a1","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation :** Looks like there are variables which are heavily correlated like active_months_lag6, avg_purchase_lag6 and avg_sales_lag_6 and avg_purchase_lag12 and as we seen before the numerical_1 and numerical_2 have similar values and distributions and they are correlated.\n\n","metadata":{}},{"cell_type":"markdown","source":"**TOTAL OBSERVATIONS :**\n\n1) Target variable i.e. Loyalty scores are real-numbers, It directly gives us the intuition that we have to go for a supervised machine learning regression model to solve this problem.\n\n2) The data files are train, test, new_merchant, merchant and historical transactions. but datasets are largely anonymized, and the meaning of the features are not elaborated.\n\n3) The dimensionality of train and test data is very less. That clearly shows that the information provided is not sufficient for training. As only three features have been given in the train file which seems to be not sufficient to make good predictions. More features must be added to this with the help of domain knowledge and the business problem given.\n\n4) Distribution of both the train and test are almost identical. So there is no time based splitting in the make over of the data. And, it assures for prediction of the test data.\n\n5) The target variable is normally distributed but, there are outliers which seems to be accumulated around -30.\n\n6) Data is not complete as nan values are present in the merchants, historical and new merchants transactions, so these missing values must be imputed for better predciton.\n\n7) One-hot encoding/response coding of categorical features should be done for better prediction. The categorical features present across dataset are large in number than numerical features. \n\n8) Merchants data have high number of correlated features in it as compared to other data files. This is suggested by the calcuation of the VIF Scores \n\n9) The time features can reveal the inherent property of the transactions and the transactions are time dependent, the engineered features from the features like puchase_date will be useful in prediction.\n\n10) In the historical transactions data theres is this feature called authorized_flag count which indicates whether the transaction is authorized or not. There is very less number of transactions which is not authorized. Considering this flag features as a seperater in the feature engineering can results can give better predction.\n\nAt the End of the Exploration of the transactions, merchants and train data, the given features of transactions are not big factor for the caculation of the target Score.\n\nThere exist an aggregrated or engineered feature or features which can be helpful in predicting the target Score.\n\nWith the different feature engineering techniques and market research techniques we have to produce the new feaures which may or may not be very useful in the predcition model.\n\nBy implementing the major feature engineering ideas we have to produce features and build model upon it.","metadata":{}}]}