{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"# An attempt to impute missing merchant_id\n\nIf you looked at the transactions table you would notice some of the transactions having `merchant_id` missing. This by itself is very confusing (how would Elo not know that?). However, there is a neat trick which can resolve some of the missing information by cross referencing historic/new transactions!"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\npd.set_option('display.float_format', '{:.10f}'.format)\n\ntrain = pd.read_csv('../input/train.csv')\nhistorical_transactions = pd.read_csv('../input/historical_transactions.csv').fillna('')\nnew_merchant_transactions = pd.read_csv('../input/new_merchant_transactions.csv').fillna('')\n\nhistorical_transactions['purchase_amount'] = np.round(historical_transactions['purchase_amount'] / 0.00150265118 + 497.06,2)\nnew_merchant_transactions['purchase_amount'] = np.round(new_merchant_transactions['purchase_amount'] / 0.00150265118 + 497.06,2)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"64e727618013bd7ddd7b5fdc8f4954240a242bb0"},"cell_type":"markdown","source":"The missing merchant_id problem at first sight does not look too significant:"},{"metadata":{"trusted":true,"_uuid":"c275434ab21fcabd0d659a5f1670d8df8c1e3933"},"cell_type":"code","source":"missing = historical_transactions[historical_transactions['merchant_id']==''].shape[0]\ntotal = historical_transactions.shape[0]\nprint(f\"Missing: {missing}, total: {total}, missing ratio: {missing/total}\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cfe6f61095d3c1f809cff9a366e715ab815aeab3"},"cell_type":"markdown","source":"However, if we looked at last months the situation is a little bit different - there are about 2% transactions from unknown merchants in the last month, which may be significant if we were able to deal with that somehow!"},{"metadata":{"trusted":true,"_uuid":"d79560f58fb4ea102b4f289fe0b48a75004b5b73"},"cell_type":"code","source":"missing = historical_transactions[(historical_transactions['merchant_id']=='')\\\n                                  & (historical_transactions['month_lag']==0)].shape[0]\ntotal = historical_transactions[(historical_transactions['month_lag']==0)].shape[0]\nprint(f\"Missing: {missing}, total: {total}, missing ratio: {missing/total}\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2733777ae2c048ad0d9dad484f7e2bf0ba7bb468"},"cell_type":"markdown","source":"# Approaching the problem by example"},{"metadata":{"_uuid":"22ea628601c84e0d097b5f3fa5579781db93433c"},"cell_type":"markdown","source":"First, let's start off with a very obvious example of `card_id = C_ID_d57e4ddab0`."},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"35fe50b5caaff4f5cce707cdde31cfabcc02c437"},"cell_type":"code","source":"historical_transactions[historical_transactions['card_id']=='C_ID_d57e4ddab0'].sort_values('purchase_date').tail(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"66b4b9b1ce64f4eb6b474b74ed7d5483b31bc95a"},"cell_type":"code","source":"new_merchant_transactions[new_merchant_transactions['card_id']=='C_ID_d57e4ddab0'].sort_values('purchase_date')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"02597cb9fe20b8346457d01c2ec9b65340a8e389"},"cell_type":"markdown","source":"As you can see, the last \"unknown\" transaction from `historical_transactions` share the same transaction property (even transaction day of month) as the single transaction from `new_merchant_transactions`. Now this is where you can make the assumption, that these in fact are the transactions from the very same merchant! \n\nThis assumption can either be correct and wrong (I am not claiming it is right!)\n\nGiven this assumption we can try to reference all the missing merchants in `historical_transactions` with merchants from `new_merchant_transactions`!\n\nLet's create a dictionary, where we have a unique merchant assigned to the combination of all the fields in the transactions table:"},{"metadata":{"trusted":true,"_uuid":"180073c7118355d59ae3bae3941e81abfb0ae523"},"cell_type":"code","source":"fields = ['card_id','city_id','category_1','installments','category_3',\\\n          'merchant_category_id','category_2','state_id','subsector_id']\nnew_merchants = new_merchant_transactions[fields + ['merchant_id']].drop_duplicates()\nnew_merchants = new_merchants.loc[new_merchants['merchant_id']!='']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"459789d9722d16bb9558b0d4b777963952029c17"},"cell_type":"code","source":"# take only unique merchants for the `fields` combination\nuq_new_merchants = new_merchants.groupby(fields)['merchant_id'].count().reset_index(name = 'n_merchants')\nuq_new_merchants = uq_new_merchants.loc[uq_new_merchants['n_merchants']==1]\nuq_new_merchants = uq_new_merchants.merge(new_merchants, on = fields)\nuq_new_merchants.drop('n_merchants', axis=1, inplace=True)\n\n# rename the merchant_id so we can join it more easily later on\nuq_new_merchants.columns = fields + ['imputed_merchant_id']\n\nuq_new_merchants.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d2e1f175282e3a6b7a95e85851596612fd9378b9"},"cell_type":"markdown","source":"At this point we have a unique assignment for `fields` to a `merchant_id`. All we need to do at this point, use this information for `historical_transactions` table."},{"metadata":{"trusted":true,"_uuid":"b4a3a052e69ddfe55c5b01682ef964d5b3ecfecf"},"cell_type":"code","source":"historical_transactions = historical_transactions.merge(uq_new_merchants, on = fields, how = 'left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5693bc0dcc00d8a223c1e95afda4598c0090f96e"},"cell_type":"code","source":"# make the actual imputation for the merchant_id field\nhistorical_transactions.loc[(historical_transactions['merchant_id']=='') & (~pd.isnull(historical_transactions['imputed_merchant_id'])), 'merchant_id'] = \\\nhistorical_transactions.loc[(historical_transactions['merchant_id']=='') & (~pd.isnull(historical_transactions['imputed_merchant_id'])), 'imputed_merchant_id']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c5e3e197091074526f06283fa061aeda88512581"},"cell_type":"markdown","source":"At this point we are finished - we imputed the missing `merchant_id` fields with some unique `merchant_id` from `new_transactions_table`. Again, it is a speculation that this is the correct approach!\n\nLet's look how our example `card_id = C_ID_d57e4ddab0` looks now:"},{"metadata":{"trusted":true,"_uuid":"d61571c853578829662110f93ac6bfec69d1d956"},"cell_type":"code","source":"historical_transactions[historical_transactions['card_id']=='C_ID_d57e4ddab0'].sort_values('purchase_date').tail(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f15c9077551b62d7e559d8c7ac348d6e0a0824b4"},"cell_type":"markdown","source":"Let's see how the statistics of missing `merchant_id` looks now:"},{"metadata":{"trusted":true,"_uuid":"182fa287096609377f8c5b822205321aedd479f6"},"cell_type":"code","source":"missing = historical_transactions[(historical_transactions['merchant_id']=='')\\\n                                  & (historical_transactions['month_lag']==0)].shape[0]\ntotal = historical_transactions[(historical_transactions['month_lag']==0)].shape[0]\nprint(f\"Missing: {missing}, total: {total}, missing ratio: {missing/total}\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"efadd758e421a3594e4ff171fd5948e7b97daa12"},"cell_type":"markdown","source":"So we were able to reduce the missing merchant information of last `month_lag` from 1.9% to 1.6% - that is roughly 15% filled in transactions!"},{"metadata":{"_uuid":"e0981c60f79175a78cd8c8ca10313549abeca663"},"cell_type":"markdown","source":"# tl;dr\n\nWith some assumptions it is possible to impute some of the missing `merchant_id` historic information using the information from the future. \n\nThis might be useful when calculating features such as number of unique `merchant_id`, average `purhcase_amount` per `merchant_id`, `merchant_id` patterns, etc.\n\n## Hope you enjoyed the content - upvote if you found this useful!"}],"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}