{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dd3822d4cbfb8f50c7f12ca8a079828dbc7c910e"},"cell_type":"markdown","source":"Import required libraries"},{"metadata":{"trusted":true,"_uuid":"16eee3b4bf519538911061aa3da42d888f6664b9"},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport seaborn as sns\nsns.set()\nimport matplotlib.pyplot as plt\nimport datetime as dt\n\nimport warnings\nwarnings.filterwarnings('ignore')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1e013de3f304c69fe4f075212db8d47778acd246"},"cell_type":"markdown","source":"### Load the datasets."},{"metadata":{"trusted":true,"_uuid":"fa4dafc5a0cea38bd564bf6126e025276b2632d5"},"cell_type":"code","source":"merchants = pd.read_csv('../input/merchants.csv')\nhistorical_transactions = pd.read_csv('../input/historical_transactions.csv')\nnew_merchant_transactions = pd.read_csv('../input/new_merchant_transactions.csv')\ntrain = pd.read_csv('../input/train.csv', parse_dates = ['first_active_month'])\ntest = pd.read_csv('../input/test.csv', parse_dates = ['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c26d6f7e540c588205199ed3a015b72bff9979e7"},"cell_type":"code","source":"print(\"Historical_transactions   :\", historical_transactions.shape)\nprint(\"Merchants                 :\", merchants.shape)\nprint(\"New_merchant_transactions :\", new_merchant_transactions.shape)\nprint(\"Train set                 :\", train.shape)\nprint(\"Test set                  :\", test.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"553ec42beff343d060c8ed9acc7d78a7287a0ac1"},"cell_type":"markdown","source":"We will first explore our train and test dataset and mainly the target variable."},{"metadata":{"_uuid":"a4a7d7ac0970e1b8e02ec4b6f5ee0209b9475558"},"cell_type":"markdown","source":"### Explore train dataset"},{"metadata":{"trusted":true,"_uuid":"1567b2c8d7604c4c0596fc36433f43b3a710a379"},"cell_type":"code","source":"print(pd.read_excel('../input/Data_Dictionary.xlsx', sheet_name = 'train'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"3888ecc9c5dbde68262aafd3000c5731287b7da4"},"cell_type":"code","source":"print('target:', pd.read_excel('../input/Data_Dictionary.xlsx', sheet_name = 'train')['Unnamed: 1'][7])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4e503c3e76e18c81dbbf40a6201fdc0ebb0f1322"},"cell_type":"markdown","source":"So our target variable is the loyalty score (numerical). Though it is not cleared how it is calculated. We will look into it further."},{"metadata":{"trusted":true,"_uuid":"8a009cfa02e23f720746890a4a2f5badaf64bd0f"},"cell_type":"code","source":"train.head(2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c817cf006b5f3596c8d611327667f12626a2727c"},"cell_type":"code","source":"train.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9a83d47351c1e79819c39db59225a6fcbafbe014"},"cell_type":"markdown","source":"Interestingly our target variable has max value of -33.xx and max of 17.xx. But they are not distributed normally. 25th to 72th quartile value lies in (+-)0.xx values. Lets look at them visually."},{"metadata":{"trusted":true,"_uuid":"bff03d696a8b39811b46e4f638748ad1378baa5c","scrolled":false},"cell_type":"code","source":"plt.figure(figsize = (10,6))\nsns.distplot(train.target)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"042ccda1263c98b6ee70448ab3cecd2418078739"},"cell_type":"code","source":"plt.figure(figsize = (10,5))\nplt.scatter(train.index, np.sort(train.target.values))\nplt.xlabel('Index')\nplt.ylabel('Loyalty Score')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f6f60004ef64479a5cea8bb9efb096c5ea3cccc1"},"cell_type":"markdown","source":"So, the distribution seems normal. Though it contains an unexpected value of -33.xx. Lets see how much."},{"metadata":{"trusted":true,"_uuid":"2c30b8557c2b420158039ce4bd261240708e1347"},"cell_type":"code","source":"print((train.target == -33.21928095).sum())\nprint((train.target == -33.21928095).sum()*100 / len(train.target))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2f4507e702441089eb45902cb37a64bcc359424b"},"cell_type":"markdown","source":"1% of data. We will look into it further. Lets look at the other variables in the train data.\n\nWe will look if there is something which Feature_x variables can find for the target variable."},{"metadata":{"trusted":true,"_uuid":"08eb47b6a1ca424ebb2e9c97bbc9ffd6ee33e479"},"cell_type":"code","source":"plt.figure(figsize = (14,6))\nsns.boxplot('feature_1', 'target', data = train)\nplt.title('Feature_1 distribution (loyalty score)')\nplt.show()\n\nplt.figure(figsize = (12,6))\nsns.boxplot('feature_2', 'target', data = train)\nplt.title('Feature_2 distribution (loyalty score)')\nplt.show()\n\nplt.figure(figsize = (8,4))\nsns.boxplot('feature_3', 'target', data = train)\nplt.title('Feature_3 distribution (loyalty score)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"55a3a92fc1910f24c16cbdbaca9c25a0e119fbfb"},"cell_type":"markdown","source":"Looks like Feature_x variables are not very good or helpful estimators for target variable. Here arises another question why not dig more our target variable. Loyalty score with negative value. There is definately some calculations behind the true loyalty score and to be it seems like an exponential or logarithmic function behind this due to -33.xx."},{"metadata":{"_uuid":"90dc1d4422dd345517ed876b7d5713c1a445fd99"},"cell_type":"markdown","source":"After search I found this great kernel by @raddar Sir. Link to kernel : https://www.kaggle.com/raddar/towards-de-anonymizing-the-data-some-insights. Target value was revealed as 10^(target(log10(2))) which is simply 2**target."},{"metadata":{"trusted":true,"_uuid":"f96a782cb15cfe01f164f0cee37e84fb03649d43"},"cell_type":"code","source":"train['new_target'] = 2**train.target","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b31ea34caae3bf2e85233a98968a770a6ab3d3e4"},"cell_type":"code","source":"train.new_target.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0122eb8cc5b813b050c4f009ece135237daf3124"},"cell_type":"code","source":"plt.figure(figsize = (10,6))\nsns.distplot(train.new_target)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06c0637945eaa067e62cf9c6ed09f2689d4f07fe"},"cell_type":"code","source":"train.sort_values(by = 'new_target').head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"229c7c8584b5d5c9f655140678f3c7c8a2bfdf11"},"cell_type":"code","source":"train.sort_values(by = 'new_target').tail(5)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"200f7dca9a74633b0de8b00c261815e5121341d4"},"cell_type":"markdown","source":"Looking at graph, I got some confused. But its clear now. Only 0.02% card_ids have loyalty score of 0.xx and thats expected (I expected even more than this).\n\nSo, target variable is set. Remeber we will have to reverse the calculation after the prediction in the test set.\n\nLets again plot Feature_x distribution with the new_target variable"},{"metadata":{"trusted":true,"_uuid":"8a53414856ed1ce6058b5d715260b98160a4033e"},"cell_type":"code","source":"plt.figure(figsize = (14,6))\nsns.boxplot('feature_1', 'new_target', data = train)\nplt.title('Feature_1 distribution (loyalty score)')\nplt.show()\n\nplt.figure(figsize = (12,6))\nsns.boxplot('feature_2', 'new_target', data = train)\nplt.title('Feature_2 distribution (loyalty score)')\nplt.show()\n\nplt.figure(figsize = (8,4))\nsns.boxplot('feature_3', 'new_target', data = train)\nplt.title('Feature_3 distribution (loyalty score)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"444f84faffccaeb19f10e0c6192c4a8fdde85c77"},"cell_type":"markdown","source":"Results are not great but yes, difference can be seen between each category of each variable. Thus, now, Feature_x variable will help some how for prediction in the test set.\n\nNow, we are left with only one varible to explore i.e., first_active_month which gives us the customer first month of purchase."},{"metadata":{"trusted":true,"_uuid":"e655267f78a010fc1c3dcc4ed8068a06c4617528"},"cell_type":"code","source":"train['year'] = train['first_active_month'].dt.year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"806cbcff7faeb73ea966f58c5f0bc8c2a44e87b4"},"cell_type":"code","source":"plt.figure(figsize = (8,6))\nplt.scatter(train.year, train.new_target, alpha = 0.5)\nplt.xlabel('First Active Year')\nplt.ylabel('Loyalty Score (new_target)')\nplt.title('Loyalty Score vs First Active Year')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a81233e1060b7551a4463391841ed7d794241879"},"cell_type":"markdown","source":"So, loyalty score are high for the 2016 and 2017 first active users. \n\n Lets look if the distribution of train and test dataset based on first_active_month is same or different."},{"metadata":{"trusted":true,"_uuid":"f340c9fa5baf2b99f09393c075a879728c8f2dd0"},"cell_type":"code","source":"month_count = train.first_active_month.dt.date.value_counts().sort_index()\n\nplt.figure(figsize = (14,6))\nsns.barplot(month_count.index, month_count.values, color = 'r')\nplt.xticks(rotation = 'vertical')\nplt.title('Train data distribution based on first active month')\nplt.show()\n\nplt.figure(figsize = (14,6))\nmonth_count = test.first_active_month.dt.date.value_counts().sort_index()\nsns.barplot(month_count.index, month_count.values, color = 'g')\nplt.xticks(rotation = 'vertical')\nplt.title('Test data distribution based on first active month')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fec6fd2049a3f29d264efbf3eb377b399e088776"},"cell_type":"markdown","source":"Yes, the distribution is same enough."},{"metadata":{"_uuid":"39e97db425d3175fd1ebcb092dd9b247e0e34466"},"cell_type":"markdown","source":"### New Merchant Transactions & Historical Transactions"},{"metadata":{"_uuid":"2c04d2d72d559e02829312f07c83b87bdb85f31f"},"cell_type":"markdown","source":"new_merchant_tranactions and historical transactions have same variables. Thus, I will only explore and work on new_merchant_transactions and will update the manipulation in the historical transactions."},{"metadata":{"trusted":true,"_uuid":"8de1a10d0087495853c8505da85a23792912e3db"},"cell_type":"code","source":"print(pd.read_excel('../input/Data_Dictionary.xlsx', sheet_name = 'new_merchant_period'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7edc52de671102a38d2c713f55aeebcf71bebf91"},"cell_type":"code","source":"new_merchant_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aa7fb92cd76016dc1bd2ad61e747e7aa57bef51e"},"cell_type":"markdown","source":"**purchase_amount**\n\nPurchase amount should be positive but here it is not. Again, some sort of calculation is required to extract original purchase amount.\n\nAgain the kernel by [@raddar](http://kaggle.com/raddar) sir gave some magic numbers for transforming the purchase amount. But, I was not sure how this number came. Thanks to [@CPMP](http://kaggle.com/cpmpml) sir. His kernel helped to find pattern from the data and transform them using codes. Link to the kernel: https://www.kaggle.com/cpmpml/raddar-magic-explained-a-bit/\n\n"},{"metadata":{"trusted":true,"_uuid":"3fae382e68256aa5f57608169e84df38dd24036f"},"cell_type":"code","source":"hist_new = pd.concat((historical_transactions, new_merchant_transactions), ignore_index = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"21983278c1a01a6590761452e64cc773049b0f21"},"cell_type":"code","source":"hist_new.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3465cfd268a48a30fb4baafe7d794b7402fc618d"},"cell_type":"code","source":"hist_new.purchase_amount.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"516692485bc6f957a0b2d28d05a404aa21e2aaf9"},"cell_type":"markdown","source":"Make purchase amount non-negative."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"5e721b2ce884be8ce20e885a556eb83e6bcce6c5"},"cell_type":"code","source":"hist_new['new_amount'] = hist_new.purchase_amount - hist_new.purchase_amount.min()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"355cfc417dad04b781e2a7624811f4378df626c5"},"cell_type":"code","source":"hist_new.new_amount.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4720ff7cea0b5731617575a78addef742a18b713"},"cell_type":"markdown","source":"Look at unique values we got after the new_amount"},{"metadata":{"trusted":true,"_uuid":"ca50da66e2f4a0b895040a9f4ce2ae2eaf9d3118"},"cell_type":"code","source":"np.sort(hist_new.new_amount.unique())[:10]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"46992c06e6fd6dd88b4783056332e7f227392843"},"cell_type":"markdown","source":"There looks a pattern here"},{"metadata":{"trusted":true,"_uuid":"2e1a6e6f822078512474968f33b9f9071c36a765"},"cell_type":"code","source":"np.diff(np.sort(hist_new.new_amount.unique()))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dd1d954590ea439184463490d71acdcdf490dfa4"},"cell_type":"markdown","source":"Let do this steps in a proper way"},{"metadata":{"trusted":true,"_uuid":"d930b1b4bdef4ec0744e5154a7faf6ad9ff49bc6"},"cell_type":"code","source":"hist_new_sorted = hist_new.groupby('new_amount').new_amount.first().to_frame().reset_index(drop=True)\nhist_new_sorted['delta'] = hist_new_sorted.new_amount.diff()\nhist_new_sorted[hist_new_sorted.delta >= 2e-5].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"b69c1ba82bfa3876d315120501d992408f0bd22f"},"cell_type":"code","source":"hist_new_sorted[1:52623].delta.mean()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"741857dba2e1441d57c847bf0099d067cc404422"},"cell_type":"code","source":"hist_new['new_amount'] = np.round(hist_new['new_amount'] / (100 * hist_new_sorted[1:52623].delta.mean()), 2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ab69e32fa350aefcf66032333b66e14c2ed31630"},"cell_type":"code","source":"hist_new.new_amount.value_counts().head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f8a30fcc172269c99e9eead837c166482f6290e3"},"cell_type":"code","source":"historical_transactions = hist_new[:29112361]\nnew_merchant_transactions = hist_new[29112361:]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ed39154edc871c068257d7098d49b1bd15692d33"},"cell_type":"code","source":"historical_transactions.shape, new_merchant_transactions.shape","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1b6cac24b72e068b78f0b93ddba482ee468fd217"},"cell_type":"markdown","source":"# References \n> https://www.kaggle.com/raddar/towards-de-anonymizing-the-data-some-insights\n\n> https://www.kaggle.com/sudalairajkumar/simple-exploration-notebook-elo\n\n> https://www.kaggle.com/cpmpml/raddar-magic-explained-a-bit/"},{"metadata":{"trusted":true,"_uuid":"57b41d153f82259776aa1418b51751d413b603e4"},"cell_type":"markdown","source":"Thank you for reading. If you liked my kernel or was helpful to you kindly upvote it. Thanks!"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}