{"cells":[{"metadata":{"trusted":true,"_uuid":"e46c3cd122952077f541e11b49d9a2953939b63c"},"cell_type":"markdown","source":"**Elo Merchant Category Recommendation**\n\nCompetition description:\n\n> Imagine being hungry in an unfamiliar part of town and getting restaurant recommendations served up, based on your personal preferences, at just the right moment. The recommendation comes with an attached discount from your credit card provider for a local place around the corner!\n> \n> Right now, Elo, one of the largest payment brands in Brazil, has built partnerships with merchants in order to offer promotions or discounts to cardholders. But do these promotions work for either the consumer or the merchant? Do customers enjoy their experience? Do merchants see repeat business? Personalization is key.\n> \n> Elo has built machine learning models to understand the most important aspects and preferences in their customers’ lifecycle, from food to shopping. But so far none of them is specifically tailored for an individual or profile. This is where you come in.\n> \n> In this competition, Kagglers will **develop algorithms to identify and serve the most relevant opportunities to individuals, by uncovering signal in customer loyalty**. Your input will improve customers’ lives and help Elo reduce unwanted campaigns, to create the right experience for customers."},{"metadata":{"_uuid":"5bf11274e9677b30f355a7d4deee740f8d3a7bd1"},"cell_type":"markdown","source":"When I read this description (and looked at the data provided), I found it  pretty confusing (or maybe I'm just not used to this). So, let's try to figure out what exactly is going on in this competition and what we are predicting.\n\nFirstly, from the competition description, we need to \"develop algorithms to identify and serve the most relevant opportunities to individuals, by uncovering signal in customer loyalty\". \n\nWhat is loyalty? According to the Data_Dictionary.xlsx, **loyalty is a numerical score calculated 2 months after historical and evaluation period**.  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**So, here is my understanding of the situation:**\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* **Our 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":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":false,"_kg_hide-output":false},"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 numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport matplotlib.pyplot as plt\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint('List of files:')\nprint(os.listdir(\"../input\"))\ndata_folder = '../input'\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cf693acb29d5c4e6b3360d6d0b6885ea54bfc941"},"cell_type":"markdown","source":"**Let's compare the data distribution in test.csv and train.csv.**\n\ntrain.csv and test.csv column descriptions:\n\n**card_id:**\tUnique card identifier\n\n**first_active_month:**\t 'YYYY-MM', month of first purchase\n\n**feature_1:**\tAnonymized card categorical feature\n\n**feature_2:**\tAnonymized card categorical feature\n\n**feature_3:**\tAnonymized card categorical feature\n\n**target:** Loyalty numerical score calculated 2 months after historical and evaluation period!\n\nWell, that's not very informative. Let's look at the sizes of these tables:","attachments":{}},{"metadata":{"trusted":true,"_uuid":"d7f8f2723609d173c87f00d34aa009fd00ac01c1","_kg_hide-input":false},"cell_type":"code","source":"train = pd.read_csv(os.path.join(data_folder, 'train.csv'))\ntest = pd.read_csv(os.path.join(data_folder, 'test.csv'))\n\nplt.figure(figsize=[4,3])\nplt.bar([0, 1], [train.shape[0], test.shape[0]], edgecolor=[0.2]*3, color=(1,0,0,0.5))\nplt.xticks([0,1], ['train rows', 'test rows'], fontsize=13)\nplt.title('Number of rows in train.csv and test.csv', fontsize=15)\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"35e43e6d3568c65e90e428347273e847c5ba36c1"},"cell_type":"markdown","source":"And feature distributions:"},{"metadata":{"trusted":true,"_uuid":"ed7757d96fdd3ff6ae2028bb98c3dc8c7805c29b","_kg_hide-input":false},"cell_type":"code","source":"%matplotlib inline\n\nplt.figure(figsize=[15,5])\nplt.suptitle('Feature distributions in train.csv and test.csv', fontsize=20, y=1.1)\nfor num, col in enumerate(['feature_1', 'feature_2', 'feature_3', 'target']):\n    plt.subplot(2, 4, num+1)\n    if col is not 'target':\n        v_c = train[col].value_counts() / train.shape[0]\n        plt.bar(v_c.index, v_c, label=('train'), align='edge', width=-0.3, edgecolor=[0.2]*3)\n        v_c = test[col].value_counts() / test.shape[0]\n        plt.bar(v_c.index, v_c, label=('test'), align='edge', width=0.3, edgecolor=[0.2]*3)\n        plt.title(col)\n        plt.legend()\n    else:\n        plt.hist(train[col], bins = 100)\n        plt.title(col)\n    plt.tight_layout()\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8aaf298cc0e977c0c6a946bf873ce5b924580ef9"},"cell_type":"markdown","source":"Looks like the test and train data are distributed similarly. Additionally, there are some outliers in the target column. Let's take a look at them:"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"14bdc96fafea36a2ff7c4b23d42d9a3eb6fc7351"},"cell_type":"code","source":"outliers = train.loc[train['target'] < -30]\nnon_outliers = train.loc[train['target'] >= -30]\nprint('{:d} outliers found (target < -30)'.format(outliers.shape[0]))\n\nplt.figure(figsize=[10,5])\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        v_c = non_outliers[col].value_counts() / non_outliers.shape[0]\n        plt.bar(v_c.index, v_c, label=('non-outliers'), align='edge', width=-0.3, edgecolor=[0.2]*3)\n        v_c = outliers[col].value_counts() / outliers.shape[0]\n        plt.bar(v_c.index, v_c, label=('outliers'), align='edge', width=0.3, edgecolor=[0.2]*3)\n        plt.title(col)\n        plt.legend()\n\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c8d63e9c30554c7c177ee8bbd1a306293a72ca4e"},"cell_type":"markdown","source":"There are some slignt 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's probably a good idea to remove them, unless we can find differences between outliers and non-outliers in other tables that will allow us to detect them."},{"metadata":{"_uuid":"1ca576b19deab7c29f4189a8f344804ff75b0404"},"cell_type":"markdown","source":"Correlation coefficients for all variables in train.csv:"},{"metadata":{"trusted":true,"_uuid":"dc2dff80cf2dc0c4be0263dc5f8a74c3376bc140","_kg_hide-input":false},"cell_type":"code","source":"corrs = np.abs(train.corr())\nnp.fill_diagonal(corrs.values, 0)\nplt.figure(figsize=[5,5])\nplt.imshow(corrs, cmap='plasma', vmin=0, vmax=1)\nplt.colorbar(shrink=0.7)\nplt.xticks(range(corrs.shape[0]), list(corrs.columns))\nplt.yticks(range(corrs.shape[0]), list(corrs.columns))\nplt.title('Correlations between target and user\\'s features', fontsize=17)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fd8567fc824b4b0d1575108822291d26e048364b"},"cell_type":"markdown","source":"The correlations between the target and any of the features (so, don't use linear models for precition) are quite low, but the features 1 and 3 are somewhat decently correlated. Let's take a look at the scatter plots:"},{"metadata":{"trusted":true,"_uuid":"02abe8a8372b27f965456b0ec036c412be57d873","_kg_hide-input":false},"cell_type":"code","source":"from pandas.plotting import scatter_matrix\nselect_cols = ['feature_1', 'feature_2', 'feature_3', 'target']\nscatter_matrix(train[select_cols], figsize=[10,10])\nplt.suptitle('Pair-wise scatter plots for columns in train.csv', fontsize=15)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"48fceffea53fc35779ac0a3668af074ebcce697a"},"cell_type":"markdown","source":"Again, not a lot of useful information. The only thing I can see is different target variances for different values of the feature_1 , but it's most likely due to the different amount of data corresponding to each feature.\n\nNow, let take a look at the merchants. From the dataset's describtion:\n\n**merchant_id:**\tUnique merchant identifier\n\n**merchant_group_id:**\tMerchant group (anonymized )\n\n**merchant_category_id:**\tUnique identifier for merchant category (anonymized )\n\n**subsector_id:**\tMerchant category group (anonymized )\n\n**numerical_1:**\tanonymized measure\n\n**numerical_2:**\tanonymized measure\n\n**category_1:**\tanonymized category\n\n**most_recent_sales_range:**\tRange of revenue (monetary units) in last active month --> A > B > C > D > E\n\n**most_recent_purchases_range:**\tRange of quantity of transactions in last active month --> A > B > C > D > E\n\n**avg_sales_lag3:**\tMonthly average of revenue in last 3 months divided by revenue in last active month\n\n**avg_purchases_lag3:**\tMonthly average of transactions in last 3 months divided by transactions in last active \nmonth\n\n**active_months_lag3:**\tQuantity of active months within last 3 months\n\n**avg_sales_lag6:**\tMonthly average of revenue in last 6 months divided by revenue in last active month\n\n**avg_purchases_lag6:**\tMonthly average of transactions in last 6 months divided by transactions in last active \nmonth\n\n**active_months_lag6:**\tQuantity of active months within last 6 months\n\n**avg_sales_lag12:**\tMonthly average of revenue in last 12 months divided by revenue in last active month\n\n**avg_purchases_lag12:**\tMonthly average of transactions in last 12 months divided by transactions in last active month\n\n**active_months_lag12:**\tQuantity of active months within last 12 months\n\n**category_4:**\tanonymized category\n\n**city_id:**\tCity identifier (anonymized )\n\n**state_id:**\tState identifier (anonymized )\n\n**category_2:**\tanonymized category!","attachments":{}},{"metadata":{"trusted":true,"_uuid":"4166b8f48337d8be02396b568ed8a3bcf473cf9a"},"cell_type":"code","source":"merchants = pd.read_csv(os.path.join(data_folder, 'merchants.csv'))\n# replacing inf values with nan\nmerchants.replace([-np.inf, np.inf], np.nan, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"15ca7ac553742c48d8bbb47f83b2c92b963386cf"},"cell_type":"markdown","source":"Several columns in merchants.csv have outliers that squeeze most of the data into one bin (you can check it yourself, I'm not showing the raw data for the sake of saving space). Let's fix that by removing those outliers:"},{"metadata":{"trusted":true,"_uuid":"65d9923fc418594b6f254f2c45de7ffb06eb0c73"},"cell_type":"code","source":"clean_merchants = merchants.loc[(merchants['numerical_1'] < 0.1) &\n                               (merchants['numerical_2'] < 0.1) &\n                               (merchants['avg_sales_lag3'] < 5) &\n                               (merchants['avg_purchases_lag3'] < 5) &\n                               (merchants['avg_sales_lag6'] < 10) &\n                               (merchants['avg_purchases_lag6'] < 10) &\n                               (merchants['avg_sales_lag12'] < 10) &\n                               (merchants['avg_purchases_lag12'] < 10)]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9d26d21912d5dac076254a41f782f9f5e3d86d73"},"cell_type":"markdown","source":"Now, let's look at column histograms:"},{"metadata":{"trusted":true,"_uuid":"313b098cea88b527336114a145a2d6de127ea359","_kg_hide-input":false},"cell_type":"code","source":"cat_cols = ['active_months_lag6','active_months_lag3','most_recent_sales_range', 'most_recent_purchases_range','category_1','active_months_lag12','category_4', 'category_2']\nnum_cols = ['numerical_1', 'numerical_2','merchant_group_id','merchant_category_id','avg_sales_lag3', 'avg_purchases_lag3', 'subsector_id', 'avg_sales_lag6', 'avg_purchases_lag6', 'avg_sales_lag12', 'avg_purchases_lag12']\n\nplt.figure(figsize=[15, 15])\nplt.suptitle('Merchants table histograms', y=1.02, fontsize=20)\nncols = 4\nnrows = int(np.ceil((len(cat_cols) + len(num_cols))/4))\nlast_ind = 0\nfor col in sorted(list(clean_merchants.columns)):\n    #print('processing column ' + col)\n    if col in cat_cols:\n        last_ind += 1\n        plt.subplot(nrows, ncols, last_ind)\n        vc = clean_merchants[col].value_counts()\n        x = np.array(vc.index)\n        y = vc.values\n        inds = np.argsort(x)\n        x = x[inds].astype(str)\n        y = y[inds]\n        plt.bar(x, y, color=(0, 0, 0, 0.7))\n        plt.title(col, fontsize=15)\n    if col in num_cols:\n        last_ind += 1\n        plt.subplot(nrows, ncols, last_ind)\n        clean_merchants[col].hist(bins = 50, color=(0, 0, 0, 0.7))\n        plt.title(col, fontsize=15)\n    plt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"28c80a8b9c722d65c7e18ef8ed081329dd11e1d2"},"cell_type":"markdown","source":"From the histograms you can see several things:\n* merchant_group_ids are sorted in the descending order\n* match_category_id and subsector_id are not sorted\n* numerical_1 and numerical_2 (ironically) seem to represent more of a categorical value as they take a discrete set of values"},{"metadata":{"trusted":true,"_uuid":"c6ab0e03c73e6563ec4859d051b051b3de687915","_kg_hide-output":false,"_kg_hide-input":false,"scrolled":true},"cell_type":"code","source":"# converting category names to numbers, so these columns\n# can participate in the correlation coefficient heat map\nclean_merchants['most_recent_purchases_range'].replace({'A':4, 'B':3, 'C':2, 'D':1, 'E':0}, inplace=True)\nclean_merchants['most_recent_sales_range'].replace({'A':4, 'B':3, 'C':2, 'D':1, 'E':0}, inplace=True)\nclean_merchants['category_1'].replace({'N':0, 'Y':1}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b930a25f19bcbf0b88ef70da41397faf910cfdc8"},"cell_type":"markdown","source":"Now, let's look at correlations between columns in merchants.csv:"},{"metadata":{"trusted":true,"_uuid":"5bfb79144e5fd9c4a5e9e5973b5bf3adee32e859","_kg_hide-input":false},"cell_type":"code","source":"corrs = np.abs(clean_merchants.corr())\nordered_cols = (corrs).sum().sort_values().index\nnp.fill_diagonal(corrs.values, 0)\nplt.figure(figsize=[10,10])\nplt.imshow(corrs.loc[ordered_cols, ordered_cols], cmap='plasma', vmin=0, vmax=1)\nplt.colorbar(shrink=0.7)\nplt.xticks(range(corrs.shape[0]), list(ordered_cols), rotation=90)\nplt.yticks(range(corrs.shape[0]), list(ordered_cols))\nplt.title('Heat map of coefficients of correlation between merchant\\'s features', fontsize=17)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c34ff9ec585e57b195dc7e34f9c2a895bf1690f2"},"cell_type":"markdown","source":"The heat map above reveals some relationships:\n* numerical_1 and numerical_2 are highly correlated\n* Unsurprisingly, avg_sales and avg_purchases within the last 3, 6, and 12 months are highly correlated\n* mechant_group_id is loosely correlated with numerical_1, 2, city_id, and sales statistics. Interestingly, merchant_category_id shows little correlation with merchant_group_id, city_id, or really anything else.\n* category_1 is loosely correlated with the merchant's location (city_id and state_id)\n* category_1 and category_2 are never not NaNs at the same time"},{"metadata":{"_uuid":"1922336716fd620b6e53b9d1c69a7eb07d63920e"},"cell_type":"markdown","source":"Let's take a closer look at the groups of correlated variables:"},{"metadata":{"_uuid":"3327295f0b2c0be719bdea269b5408f4401e41f4"},"cell_type":"markdown","source":"Group 1:"},{"metadata":{"trusted":true,"_uuid":"89b7035751f5ed08ebb19fa6de1173d65b027da9","_kg_hide-input":false},"cell_type":"code","source":"scatter_matrix(clean_merchants[ordered_cols[-6:]], figsize=[15,15])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b76d44953ca37fbf82ba49d8cc1dde2842ccc5f6"},"cell_type":"markdown","source":"Nothing too interesting here, looks like regular coorelated values. One interesting thing to look at is the average sales and purchases within the last 3, 6, and 12 months:\n"},{"metadata":{"_kg_hide-input":false,"trusted":true,"_uuid":"de699a6f316567ab99cd42f1adeab61a706488e9"},"cell_type":"code","source":"x = np.array([12, 6, 3]).astype(str)\nsales_rates = clean_merchants[['avg_sales_lag3', 'avg_sales_lag6', 'avg_sales_lag12']].mean().values\npurchase_rates = clean_merchants[['avg_purchases_lag3', 'avg_purchases_lag6', 'avg_purchases_lag12']].mean().values\nplt.bar(x, sales_rates, width=0.3, align='edge', label='average sales', edgecolor=[0.2]*3)\nplt.bar(x, purchase_rates, width=-0.3, align='edge', label='average purchases', edgecolor=[0.2]*3)\nplt.legend()\nplt.title('Avergage sales and number of purchases\\nover the last 12, 6, and 3 months', fontsize=17)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3d6ad145adfeeb80100d9b21b802cd6676e0771f"},"cell_type":"markdown","source":"It seems like businesses' sales grow over time, which is good.\n\nGroup 2:"},{"metadata":{"trusted":true,"_uuid":"6a7e584be37ce10d23eff6490cb5b3c74b698d5e"},"cell_type":"code","source":"scatter_matrix(clean_merchants[ordered_cols[-14:-8]], figsize=[15,15])\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"739f54b5a544c73ec0b4ad0adb3ea2970f5cc896"},"cell_type":"markdown","source":"Now, this is interesting.\n* numerical_1 serves as the upper limit for numerical_2. \n* both numerical_1 and numerical_2 increase with the amount of active months\n* active_months_lag 3 and 6 are basically truncated versions of active_months_12, so they can be dropped. \n* It seems that the merchants with less than 12 active months are the ones that only recently opened their business. I don't see any merchants that ran out of business (they would show higher lag12 values compared to the lag6 and lag3 when lag6 < 6 and lag3 < 3)\n\nGroup 3:"},{"metadata":{"trusted":true,"_uuid":"5cbb14ca8d2a824579db6cd5d6450ac9eb5803a6"},"cell_type":"code","source":"scatter_matrix(merchants[ordered_cols[0:6]], figsize=[10,10])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d16dec304a2afe490ba3ee4f978d30b82f181926"},"cell_type":"markdown","source":"Not much interesting in this last group.\n\nLet's look at the new merchants' transactions. \"New\" here means new for the customer, i.e. the customer has never purchased anything from those vendors during the period corresponding to the data in historical_transactions.csv.\n\n**Dataset description:**\n\n**card_id:**\tCard identifier\n\n**month_lag:**\tmonth lag to reference date\n\n**purchase_date:**\tPurchase date\n\n**authorized_flag:**\tY' if approved, 'N' if denied\n\n**category_3:**\tanonymized category\n\n**installments:**\tnumber of installments of purchase\n\n**category_1:**\tanonymized category\n\n**merchant_category_id:**\tMerchant category identifier (anonymized )\n\n**subsector_id:**\tMerchant category group identifier (anonymized )\n\n**merchant_id:**\tMerchant identifier (anonymized)\n\n**purchase_amount:**\tNormalized purchase amount\n\n**city_id:**\tCity identifier (anonymized )\n\n**state_id:**\tState identifier (anonymized )\n\n**category_2:**\tanonymized category","attachments":{}},{"metadata":{"trusted":true,"_uuid":"16180ea9eddc12c73f98461994c79089b73c4bae"},"cell_type":"code","source":"new_merch = pd.read_csv(os.path.join(data_folder, 'new_merchant_transactions.csv'))\nnew_merch.info(verbose=True, null_counts=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ff0f258fb3ef8019f372a2ef68fcc8dbc0a553ef"},"cell_type":"code","source":"# converting purchase time string into datetime\nfrom datetime import datetime\nnew_merch['purchase_date'] = new_merch['purchase_date'].apply(lambda x: datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bf24ec5b92a5270bb88b42c137552b5763135f77"},"cell_type":"code","source":"# drawing histograms for each column\nfiltered_new_merch = new_merch.loc[new_merch['purchase_amount'] < 1]\ncat_cols = ['authorized_flag', 'category_1', 'installments','category_3', 'month_lag','category_2', 'subsector_id']\nnum_cols = ['purchase_amount', 'purchase_date', 'merchant_category_id', 'subsector_id']\n\nplt.figure(figsize=[15, 10])\nplt.suptitle('New merchant transaction info', y=1.02, fontsize=20)\nncols = 4\nnrows = int(np.ceil((len(cat_cols) + len(num_cols))/4))\nlast_ind = 0\nfor col in sorted(list(filtered_new_merch.columns)):\n    #print('processing column ' + col)\n    if col in cat_cols:\n        last_ind += 1\n        plt.subplot(nrows, ncols, last_ind)\n        vc = filtered_new_merch[col].value_counts()\n        x = np.array(vc.index)\n        y = vc.values\n        inds = np.argsort(x)\n        x = x[inds].astype(str)\n        y = y[inds]\n        plt.bar(x, y, color=(0, 0, 0, 0.7))\n        plt.title(col, fontsize=15)\n        plt.xticks(rotation=90)\n    if col in num_cols:\n        last_ind += 1\n        plt.subplot(nrows, ncols, last_ind)\n        filtered_new_merch[col].hist(bins = 50, color=(0, 0, 0, 0.7))\n        plt.title(col, fontsize=15)\n        plt.xticks(rotation=90)\n    plt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":false,"_kg_hide-input":false,"trusted":true,"_uuid":"a8e554baf4bb46c6e1262dca43866efd51090c5a"},"cell_type":"code","source":"# converting category_1 and category_3 values to numeric ones, so we can use then in scatter plots and correlation coefficients\nfiltered_new_merch['category_3'].replace({'A':0, 'B':1, 'C':2}, inplace=True)\nfiltered_new_merch['category_1'].replace({'N':0, 'Y':1}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8525d832c2301ed99af393bd4ab473bfa0152e27"},"cell_type":"markdown","source":"* All transactions in this table are authorized, so we can safely drop this column.\n* Based on the purchase_date column, the reference date is different for different cards, but most of them are in February - March 2018.\n* All purchases here were made within 2 months after the reference date\n\nFinally, let's look at the historical transactions. This table has exactly the same columns as the new merchant transactions:"},{"metadata":{"trusted":true,"_uuid":"6703a3ead71c55dc3049a2513eee1d5cfc640590"},"cell_type":"code","source":"trns_history = pd.read_csv(os.path.join(data_folder, 'historical_transactions.csv'))\ntrns_history.info(verbose=True, null_counts=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"296a551dd9fe9cfda27fd23faa45aedc636dc905"},"cell_type":"code","source":"trns_history['purchase_date'] = trns_history['purchase_date'].apply(lambda x: datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"27fc79aef5169bd03eb0dcc529df0d93b83cdd81","_kg_hide-input":false},"cell_type":"code","source":"filtered_trns_history = trns_history.loc[trns_history['purchase_amount'] < 1]\ncat_cols = ['authorized_flag', 'category_1', 'installments','category_3', 'month_lag','category_2', 'subsector_id']\nnum_cols = ['purchase_amount', 'purchase_date', 'merchant_category_id', 'subsector_id']\n\nplt.figure(figsize=[15, 10])\nplt.suptitle('Transaction history info', y=1.02, fontsize=20)\nncols = 4\nnrows = int(np.ceil((len(cat_cols) + len(num_cols))/4))\nlast_ind = 0\nfor col in sorted(list(filtered_trns_history.columns)):\n    #print('processing column ' + col)\n    if col in cat_cols:\n        last_ind += 1\n        plt.subplot(nrows, ncols, last_ind)\n        vc = filtered_trns_history[col].value_counts()\n        x = np.array(vc.index)\n        y = vc.values\n        inds = np.argsort(x)\n        x = x[inds].astype(str)\n        y = y[inds]\n        plt.bar(x, y, color=(0, 0, 0, 0.7))\n        plt.title(col, fontsize=15)\n        plt.xticks(rotation=90)\n    if col in num_cols:\n        last_ind += 1\n        plt.subplot(nrows, ncols, last_ind)\n        filtered_trns_history[col].hist(bins = 50, color=(0, 0, 0, 0.7))\n        plt.title(col, fontsize=15)\n        plt.xticks(rotation=90)\n    plt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fa8deee259ea7cba5e5996ef10b6cf137355fac7"},"cell_type":"markdown","source":"* unlike in new nerchants' transactions, some transactions here were not authorized\n* installments column either has bugs or is normalized (since it contains -1 and 999 values)\n* all transactions here were made before the reference date (month_lag <= 0)"},{"metadata":{"trusted":true,"_uuid":"125b45d9fce1c70a6a5b113b659540d773cb6d71"},"cell_type":"markdown","source":"To be continued..."}],"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}