{"cells":[{"metadata":{"_uuid":"7125dfeb60de2012e54855c91dca49f39a699eb1"},"cell_type":"markdown","source":"# Merchant rating by de-anonymizing the data"},{"metadata":{"_uuid":"cbb27550ade3133684d43186c3f470cf5b1e206e"},"cell_type":"markdown","source":"\n### In this notebook:  \n* Anonymized variables revealed.  \n* Merchant rating generated.  "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nfrom matplotlib import pyplot as plt\nimport seaborn as sns\n\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\n#print(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.\n\n# Load merchants data\nmerchants = pd.read_csv(\"../input/merchants.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"# What is category_1 ?\n\nWe will start with`category_1` which will give us more idea about the other anonymized variables"},{"metadata":{"trusted":true,"_uuid":"7324d6c4444e74b7d400befa0340cbca25d929ec"},"cell_type":"code","source":"merchants[merchants['category_1']=='Y'].head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"580894202c3fabda2ec52425ae3a71ebe73ffff4"},"cell_type":"markdown","source":"From observation:\nWhen `category_1` == Y:\n1. `category_4`==Y\n2. `city_id`, `state_id` are - 1\n3. `category_2` is NaN\n\n**Assumption**\n1. `category_1` is for hiding the location or represents an online store  \n2.`category_2` is also related to location eg. country  \n\nThe second assumption is very useful for us. But we need more proof.  \n\nThe following results prove assumption about  `category_2` by showing that for `merchant_id` with multiple observations, `category_2` differs only if `city_id` and `state_id` are different."},{"metadata":{"trusted":true,"_uuid":"286e4a6ded39acafa4465f244946f8ed8e69e318","_kg_hide-input":true},"cell_type":"code","source":"merchant_details_cols = ['merchant_id', 'merchant_group_id', 'merchant_category_id', 'subsector_id', 'category_1', \n                        'category_4', 'city_id', 'state_id', 'category_2']\n\nmerchant_details = merchants[merchant_details_cols]\n\n# Delete duplicates\nmerchant_details = merchant_details.drop_duplicates()\n\nu = merchant_details['merchant_id'].value_counts()\nprint(\"Number of merchant_id with more than 1 observation\", len(u[u.values != 1]))\nmulti_obs_merchant_ids = u[u.values != 1].index\n# get all rows with these merchant_ids from the table\nmulti_observ_table = merchant_details[merchant_details['merchant_id'].isin(multi_obs_merchant_ids)]\nmarchant_id_groups = multi_observ_table.groupby('merchant_id')\n\nfor n in list(marchant_id_groups.groups.keys()):\n    a_merchant_id_group = marchant_id_groups.get_group(n)\n    x = [col for col in a_merchant_id_group.columns if a_merchant_id_group[col].nunique()>1]\n    print(\"ID =\", n, '\\tVariation in columns:', x)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1b95c13f28102c3312651b1291f43f65c3c3d0ec"},"cell_type":"markdown","source":"**`caregory_2` : Country**"},{"metadata":{"_uuid":"0cc0ead8c9866f24f44a52978470978ba26501a6"},"cell_type":"markdown","source":"### Create a Merchant Address variable \n\nFrom the above output, it is evident that a single `merchant_id` can be associated with more than one group, city & state. This is only possible if `merchant_id` is the (mapped/encrypted) used ID of the owner in Elo and an owner can list more than one of this shops in same or different city, state or country in his Elo account.  \n\nThus we are going to create a unique Id for the shop.  \n\nCombine following columns to create a unique address to the shop.\n```merchant_id, merchant_category_id, subsector_id, city_id, state_id, category_2```\nWe cannot use `merchant_group_id` since it is not in history & new transactions tables."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7f74560fcd327d3c89d981f1d14994580d4740c6"},"cell_type":"code","source":"merchants['category_2'] = merchants['category_2'].fillna(0).astype(int)\nmerchants.loc[merchants['city_id'] == -1, 'city_id'] = 0\nmerchants.loc[merchants['state_id'] == -1, 'state_id'] = 0","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"626ff2670ba01cfe6f0f6e1f9587bacb366054d3"},"cell_type":"code","source":"merchant_address_id = merchants['merchant_id'].map(str)+ '_' + \\\n    merchants['merchant_category_id'].map(str) + '_'+ \\\n    merchants['subsector_id'].map(str) + '_'+ \\\n    merchants['city_id'].map(str) + '_'+ \\\n    merchants['state_id'].map(str) + '_'+ \\\n    merchants['category_2'].map(str)\n    \nmerchants['merchant_address_id'] = merchant_address_id","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"0861fc8a42ecc8b141b517ad11cc8bb325d68798"},"cell_type":"code","source":"merchants.drop(['merchant_id', 'merchant_category_id', 'subsector_id', \n                         'city_id', 'state_id', 'category_2'], axis=1, inplace=True)\n\nmerchants.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e9c0ea48066780f22b740b75c358d6d6fd1910d3"},"cell_type":"markdown","source":"# How to use Sales and Purchase Lags?\n\n**`avg_purchases_lag3` : Monthly average of transactions in last 3 months divided by transactions in last active month**  \nIf the reference is the year end, then this will be tha average of transactions in the last 3 months (Oct, Nov, Dec) (say 6 transactions in Oct, 6 in Nov, 6 in Dec, then avg transactions = 6) divided by number of transactions in the last active month (in this case suppose the merchant was active all three month - which is given in variable `active_months_lag3`- so num of transactions in Dec: 6). Here it is 6/6, thus the variable value is 1 (Steady -  no change in last month).   \nConsider another example:    \n\n### Processing to find rate of change\n\n**Avg = 6,  last month=4**   \n6/4 = **1.5**  (This is the value in the variable)  \nWe have to find the change in number of transactions in the last active month with respect to the average. So we need to reverse the fraction.   \n1/1.5 = 0.6666666666666666  \nSince we need to find the change (increased or decreased) we have to subtract the result from 1 (1 indicates 'No change')  \n0.66 - 1 =  -0.3399999999999999  \nSince it is negative, its a reduction in the number of transactions in the last month compared to average. In percentage:  \n33% reduction  \n\n**Avg = 6,  last month = 7**  \n6/7 = **0.8571428571428571**  (This is the value in the variable)  \n1/0.85714 =  1.1666705555685186  \n1.16 -1 = 0.15999999999999992  \nPositive, so increase  \n16% increase  \n\n**Avg = 6,  last month = 15**  \n6/15 = **0.4**  \n1/0.4 =  2.5  \n2.5 - 1 =  1.5 (Positive; 1.5 > 1)  \nmore than 100% increase  \n\n`avg_purchases_lag6` and `avg_purchases_lag6` evaluated over a period of 6 months (second half of the year if Dec is reference) and 12 months (second half of the year if Dec is reference).    \n\nSame applies to sales lag for 3, 6 and 12 months where instead of number of trandsactions, revenue genereated is given.    \n\n### Processing to compensate for not-active months  \n\nactive month lags give the number of months the account was active for the shop.  \n\nWeather or not the merchant was active for all 3 months, `avg_purchases_lag3` & `avg_sales_lag3` are calculated based on the average of the whole 3 months, which will cause error if not compensated.  \n\nMerchant B: Active 3 months  \nm0 = 5, m1 = 6, m2 = 7 -> avg 6   \nb = 6/7 = 0.857143 --> 16% increase  \n\nMerchant A: Active 2 months  \nm0 = 0, m1 = 5, m2 = 7 -> avg 4  \na = 4/7 = 0.57143 -> 75% increase  \nCompensation: a * (3/2) = b  \n\n\n### Rating\n\n`most_recent_sales_range`:\tRange of revenue (monetary units) in last active month --> A > B > C > D > E  \n`most_recent_purchases_range`:\tRange of quantity of transactions in last active month --> A > B > C > D > E    \n\nA rating like the above variables is aimed with 5 values, so that the new rating and these variables can be compared. But unlike it, a integer rating is used so that mathematical operations can be performed.  \n\n**High rating 1 to Low rating 5**   \n\nTransactions: Inf < redu < 0%(steady)<incre< 100% <more than 100%    \nno transactions in last month Inf ---> E -->5   \nreduction x>= -0.99 and x < 0 --> D -->4    \nequal to 0 --> x == 0 --> C -->3  \nincrease x > 0 and x <= 1  ---> B -->2  \nmore than 100% increase x > 1  --> A -->1  \n\nRevenue: NaN or at loss(negative value) < redu < steady < increase < more than 100%    \nno sales or at loss in last month Inf ---> E  \nreduction x>= -0.99 and x < 0 --> D  \nequal to 0 --> x == 0 --> C   \nincrease x > 0 and x <= 1  ---> B  \nmore than 100% increase x > 1  --> A  "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d939f0965a6ec9cbd5168b268eef403a5674dac8"},"cell_type":"code","source":"def rating(x):\n    if np.isfinite(x) and x > 0:\n        x = (1/x) - 1\n        \n        if x>1:\n            r = 1\n        elif x <= 1 and x > 0:\n            r = 2\n        elif x == 0:\n            r = 3\n        elif x < 0 and x >= -1:\n            r = 4\n        else:\n            r = 5\n    else:\n        r = 5\n    return r\n# end","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1d2117a4ecc9c1881f932f77aff2eaf87f7309d4"},"cell_type":"code","source":"merchants['avg_sales_lag3'] = (merchants['avg_sales_lag3'] / merchants['active_months_lag3']) * 3\nmerchants['avg_purchases_lag3'] = (merchants['avg_purchases_lag3'] / merchants['active_months_lag3']) * 3\nmerchants['avg_sales_lag6'] = (merchants['avg_sales_lag6'] / merchants['active_months_lag6']) * 6\nmerchants['avg_purchases_lag6'] = (merchants['avg_purchases_lag6'] / merchants['active_months_lag6']) * 6\nmerchants['avg_sales_lag12'] = (merchants['avg_sales_lag12'] / merchants['active_months_lag12']) * 12\nmerchants['avg_purchases_lag12'] = (merchants['avg_purchases_lag12'] / merchants['active_months_lag12']) * 12\n\n\nmerchants['avg_sales_lag3'] = merchants['avg_sales_lag3'].map(rating)\nmerchants['avg_purchases_lag3'] = merchants['avg_purchases_lag3'].map(rating)\n\nmerchants['avg_sales_lag6'] = merchants['avg_sales_lag6'].map(rating)\nmerchants['avg_purchases_lag6'] = merchants['avg_purchases_lag6'].map(rating)\n\nmerchants['avg_sales_lag12'] = merchants['avg_sales_lag12'].map(rating)\nmerchants['avg_purchases_lag12'] = merchants['avg_purchases_lag12'].map(rating)\n\nmerchants = merchants.drop(['active_months_lag3', 'active_months_lag6', 'active_months_lag12'], axis=1)\n\nmerchants.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c4b1e2927e013e2651d1fcc2433f2da9bd1426fc"},"cell_type":"markdown","source":"Combine the lag columns to create a single rating, each for sales and purchase:"},{"metadata":{"trusted":true,"_uuid":"93903408201eaf02e8aae42f2e2ef2c90bdc538b"},"cell_type":"code","source":"merchants['sales_incrase_range'] = merchants[['avg_sales_lag3', 'avg_sales_lag6', 'avg_sales_lag12']].mean(axis=1)\nmerchants['purchase_incrase_range'] = merchants[['avg_purchases_lag3', 'avg_purchases_lag6', 'avg_purchases_lag12']].mean(axis=1)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"8e512aee63586a8044b45aff0b93660cf4ae4ce4"},"cell_type":"code","source":"merchants['sales_incrase_range'] = merchants['sales_incrase_range'].round()\nmerchants['sales_incrase_range'] = merchants['sales_incrase_range'].astype(int)\n\nmerchants['purchase_incrase_range'] = merchants['purchase_incrase_range'].round()\nmerchants['purchase_incrase_range'] = merchants['purchase_incrase_range'].astype(int)\n\nmerchants = merchants.drop(['avg_sales_lag3', 'avg_sales_lag6', 'avg_sales_lag12'], axis=1)\nmerchants = merchants.drop(['avg_purchases_lag3', 'avg_purchases_lag6', 'avg_purchases_lag12'], axis=1)\n\n# Distribution of categories\nprint(pd.crosstab(merchants[\"sales_incrase_range\"], merchants[\"most_recent_sales_range\"],margins=True))\n\nmerchants.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e4b0e3d7b474fd1e93a4ae07fcc32ea2477546f0"},"cell_type":"markdown","source":"# What is numerical_1 & numerical_2 ?"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"6df0ee65321cae0cf4d5a4c1d5fd130e75969293"},"cell_type":"code","source":"merchants['numerical_2'].plot.line(figsize=(20,8))\n\nprint(\"Data in histogram bins\\n\",np.histogram(merchants['numerical_2'])[0])\nprint(\"Bins\\n\",np.histogram(merchants['numerical_2'])[1])\n\nprint(\"Mean:\", np.mean(merchants['numerical_2']))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cc6d46009070972e8b0f2dbeecf4323ca5192a32"},"cell_type":"markdown","source":"Observation 1. Majority of points in the first bin  \nObservation 2. Outliers with very high values compared to the mean"},{"metadata":{"trusted":true,"_uuid":"0b56791db09b8d5466e52b1435bfdedbe2ccc23c","_kg_hide-input":true},"cell_type":"code","source":"vc_numerical_1 = merchants['numerical_1'].value_counts()\nvc_numerical_2 = merchants['numerical_2'].value_counts()\nprint(\"Value counts of 'numerical_1`\")\nvc_numerical_1.head(20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4403cb93af3f796a2fc8b19446ad784768ff87bf"},"cell_type":"markdown","source":"Observsation 3. Values are not continuous but are in various discrete levels, with many data points with same value"},{"metadata":{"_uuid":"224dffc25ea5022db14f66e6d07c999d8b3157b1"},"cell_type":"markdown","source":"Let's investigate the outliers: Threshold for outliers is rounded to the first bin interval\n\nLet's check how the other variables beheave in case of outliers."},{"metadata":{"trusted":true,"_uuid":"80fe7b8a27bf3cd81c2a360ae5fa66669f07c104"},"cell_type":"code","source":"outliers_numerical_1 = merchants.loc[merchants['numerical_1'] > 20, :]\noutliers_numerical_2 = merchants.loc[merchants['numerical_2'] > 20, :]\noutliers_numerical_1.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"be560facd1cb8202c112f81075438a7c61804f94"},"cell_type":"markdown","source":"Observation 4. Variables `most_recent_sales_range` & `most_recent_purchases_range` mostly have values is A or B. From description, this is very high range of sales and purchase. Most merchants have E or D, similar to very low values of numerical_1 & numerical_2.  \n\nConclusion: `numerical_1` & `numerical_2`are discrete values. The outliers are actually very high ratings which only few merchants have.  \n\nAssumption: `numerical_1` & `numerical_2` are either related to or similar to `most_recent_sales_range` & `most_recent_purchases_range`.  \n\nAction: **Discretize `numerical_1` & `numerical_2` by giving highest rating to maximum value in the variable and least rating to minimum. Following the convention used earlier, 1 is highest rating.**"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"86a0fa2997591e1217186e9ece3f315d77f8cd90"},"cell_type":"code","source":"# Map dict\nvc_1 = merchants['numerical_1'].value_counts()\nvc_1 = vc_1.reset_index()\nvc_1 = vc_1.rename(columns={'index':'sales_rating'})\nvc_1 = vc_1.sort_values(by='sales_rating', ascending=False)\nvc_1 = vc_1.reset_index(drop=True)\nvc_1 = vc_1.drop('numerical_1', axis=1)\nvc_1 = vc_1.reset_index(drop=False)\nvc_1 = vc_1.set_index('sales_rating')\nvc_dict_1 = vc_1.to_dict()\n\nvc_2 = merchants['numerical_2'].value_counts()\nvc_2 = vc_2.reset_index()\nvc_2 = vc_2.rename(columns={'index':'purchase_rating'})\nvc_2 = vc_2.sort_values(by='purchase_rating', ascending=False)\nvc_2 = vc_2.reset_index(drop=True)\nvc_2 = vc_2.drop('numerical_2', axis=1)\nvc_2 = vc_2.reset_index(drop=False)\nvc_2 = vc_2.set_index('purchase_rating')\nvc_dict_2 = vc_2.to_dict()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cb25cf29d8f171f405f563cf147a1ae7b7dc14f3","_kg_hide-input":false},"cell_type":"code","source":"# Map numerical values of numerical_1 & numerical_2 with rating\n# 1 being highest\nmerchants['numerical_1'] = merchants['numerical_1'].map(vc_dict_1['index'])\nmerchants['numerical_2'] = merchants['numerical_2'].map(vc_dict_2['index'])\n\nmerchants.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d9189c0b8ddffd7edd320fb683cf00276b329e59"},"cell_type":"code","source":"# Not found useful\nmerchants = merchants.drop(['category_1', 'category_4'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4304b24257e59dc1485f1ffac2c5c18fd245c392"},"cell_type":"markdown","source":"# Rating for Merchants\n\n### Map to integers\n\n`most_recent_sales_range` & `most_recent_purchases_range` are mapped to integers to perform mathematical operations\n"},{"metadata":{"trusted":true,"_uuid":"47741e548cff5d6050454d9c973e8943b3887f4a"},"cell_type":"code","source":"merchants['most_recent_sales_range'] = merchants['most_recent_sales_range']\\\n                                            .map({'A':1, 'B':2, 'C':3, 'D':4, 'E':5})\nmerchants['most_recent_purchases_range'] = merchants['most_recent_purchases_range']\\\n                                            .map({'A':1, 'B':2, 'C':3, 'D':4, 'E':5})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b5271120b49d8c551a13371a6b7f5914764b80ff"},"cell_type":"markdown","source":"### Combine ratings"},{"metadata":{"trusted":true,"_uuid":"792a073f67e8ab5721ec4025f1e34e20df5e689b"},"cell_type":"code","source":"merchants['recent_activity_range'] = merchants['most_recent_sales_range'] + merchants['most_recent_purchases_range']\nmerchants['increase_in_activity_range'] = merchants['sales_incrase_range'] + merchants['purchase_incrase_range']\nmerchants['numerical_range'] = merchants['numerical_1'] + merchants['numerical_2']\n\nmerchants = merchants.drop(['most_recent_sales_range', 'most_recent_purchases_range',\n                            'sales_incrase_range', 'purchase_incrase_range',\n                            'numerical_1', 'numerical_2'], axis=1)\n\nvc1 = merchants['recent_activity_range'].value_counts()\nvc2 = merchants['increase_in_activity_range'].value_counts()\nvc1.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"80991cab3ccdc4bd94be1fd8f78c66c43b3214cc"},"cell_type":"code","source":"merchants['merchant_rating'] = merchants['recent_activity_range'] + merchants['increase_in_activity_range']\nmerchants = merchants.drop(['recent_activity_range', 'increase_in_activity_range'], axis=1)\n\nmerchants.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aecc333112ed8a366df6abf4f94dc013c4ccbab7"},"cell_type":"markdown","source":"### Delete Duplicate observations\n\nSame merchant id with more than one `numerical_range, merchant_rating or merchant_group_id` \n\nKeep the highest rating and remove the duplicates.  "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"2fda55ceec648408a8b398f648216252287053ee"},"cell_type":"code","source":"u = merchants['merchant_address_id'].value_counts()\nprint(\"Number of merchant_id with more than one observations:\", len(u[u.values > 1]))\nmulti_obs_merchant_ids = u[u.values > 1].index \nmulti_observ_table_merchants = merchants[merchants['merchant_address_id'].isin(multi_obs_merchant_ids)]\nmarchant_id_groups_m = multi_observ_table_merchants.groupby('merchant_address_id')\n\nindices_to_drop = []\ntotal = 0\nfor n in list(marchant_id_groups_m.groups.keys()):\n    a_merchant_id_group_m = marchant_id_groups_m.get_group(n)\n    size_of_group = a_merchant_id_group_m.shape[0]\n    total += size_of_group\n    size_after_dropped = size_of_group\n    x = [col for col in a_merchant_id_group_m.columns if a_merchant_id_group_m[col].nunique()!=1]\n    drop_this = []\n    \n    if not set(x).isdisjoint(['merchant_rating']): # 'merchant_rating' is different\n        id_to_drop = a_merchant_id_group_m[a_merchant_id_group_m['merchant_rating']!=max(a_merchant_id_group_m['merchant_rating'])].index  \n        a_merchant_id_group_m = a_merchant_id_group_m.drop(list(id_to_drop), axis=0)\n        drop_this = drop_this + list(id_to_drop)\n        size_after_dropped = a_merchant_id_group_m.shape[0]\n    \n    if size_after_dropped > 1 and not set(x).isdisjoint(['numerical_range']):\n        id_to_drop = a_merchant_id_group_m[a_merchant_id_group_m['numerical_range']!=max(a_merchant_id_group_m['numerical_range'])].index\n        a_merchant_id_group_m = a_merchant_id_group_m.drop(list(id_to_drop), axis=0)\n        drop_this = drop_this + list(id_to_drop)\n        size_after_dropped = a_merchant_id_group_m.shape[0]\n        \n    if size_after_dropped > 1 and not set(x).isdisjoint(['merchant_group_id']):\n        id_to_drop = a_merchant_id_group_m[a_merchant_id_group_m['merchant_group_id']!=min(a_merchant_id_group_m['merchant_group_id'])].index\n        a_merchant_id_group_m = a_merchant_id_group_m.drop(list(id_to_drop), axis=0)\n        drop_this = drop_this + list(id_to_drop)\n        size_after_dropped = a_merchant_id_group_m.shape[0]\n        \n    #print(\"Key=\", n, \"\\tSize=\", size_of_group, \"\\tDrop=\", drop_this)\n    indices_to_drop = indices_to_drop + drop_this\n# end of for loop\na_merchant_id_group_m = marchant_id_groups_m.get_group('M_ID_9b0ef314cf_400_7_231_9_1') \n    \nmerchants = merchants.drop(indices_to_drop, axis=0)\nmerchants = merchants.reset_index(drop=True)\n    \nprint(\"%d observations for %d merchant ids. %d duplicates deleted\" %(total, \n                                                                     len(list(marchant_id_groups_m.groups.keys())), \n                                                                     len(indices_to_drop)))\nmerchants = merchants.drop(['merchant_group_id'], axis=1)\n\nmerchants.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5423a2f225e5dbfbe8f0c941fa8ce83af8fdbd98"},"cell_type":"markdown","source":"The merchant ids and ratings are converted to a dictionary for mapping the rating based on merchant id in the history and new transaction tables."},{"metadata":{"trusted":true,"_uuid":"9a1064e83185b4cff9ed363e3fc65ba89198804f"},"cell_type":"code","source":"merchants = merchants.set_index('merchant_address_id')\nmerchants_dict = merchants.to_dict()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"041e4d895f92c20509e99d879ecbd86e1b9676ec"},"cell_type":"markdown","source":"## Thank you for reading. :)"}],"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}