{"cells":[{"metadata":{"trusted":true,"_uuid":"8739bf334575063840793ccbb918f4e2f1427991"},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n%matplotlib inline\nimport os\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nfrom numpy import percentile\nfrom datetime import datetime\nfrom scipy.stats import pearsonr\nprint(os.listdir(\"../input/elo-merchant-category-recommendation\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cf13bb8f092318fa8d5b21e61712df77ad87146b"},"cell_type":"code","source":"os.list_dir('../input')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# Read historical transactions data\nhist_df = pd.read_csv(\"../input/elo-merchant-category-recommendation/historical_transactions.csv\")\nhist_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6c0cf4088b97d70a283753a558bb0b97b0b508c0"},"cell_type":"code","source":"print(len(hist_df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0bd8e7cc3b7cd5a013dce645ef714fedf183bdbc"},"cell_type":"code","source":"hist_df['pur_date']=pd.to_datetime(hist_df['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"930e606348347fbf3c0731cd1b7e002a4dbd1cd0"},"cell_type":"code","source":"hist_df['pur_date'].max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19af64687becddaff4d4b34ff1266e4708bfd869"},"cell_type":"code","source":"hist_df['pur_date'].min()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a719fb4b60521374b5da50286f4aff4259476069"},"cell_type":"code","source":"#Lets take last 3 months historical data\n\ndate1 = datetime.strptime('2017-12-01', '%Y-%m-%d')\ndate2 = datetime.strptime('2018-02-28', '%Y-%m-%d')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eaff538a11d1e462e13855b945620d03dea55875"},"cell_type":"code","source":"hist_df_reduced = hist_df.loc[(hist_df['pur_date']>date1) & (hist_df['pur_date']<date2)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1d33aaf0b2462b016854a140560be50fbb78deae"},"cell_type":"code","source":"hist_df_reduced.to_csv('hist_DecFeb18.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bfacefaca820f6041f2a085b0a55bb0029f5893f"},"cell_type":"code","source":"print(len(hist_df_reduced))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b3b7e74f8f030837ce5bd2ffe201cb265fd8a6e9"},"cell_type":"code","source":"date1 = datetime.strptime('2017-09-01', '%Y-%m-%d')\ndate2 = datetime.strptime('2017-11-30', '%Y-%m-%d')\nhist_df_reduced_SepNov = hist_df.loc[(hist_df['pur_date']>date1) & (hist_df['pur_date']<date2)]\nhist_df_reduced_SepNov.to_csv('hist_SepNov.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc36bff5520764ffbb9d036fb8eb98797e58e1c0"},"cell_type":"code","source":"date1 = datetime.strptime('2017-06-01', '%Y-%m-%d')\ndate2 = datetime.strptime('2017-08-31', '%Y-%m-%d')\nhist_df_reduced_JunAug = hist_df.loc[(hist_df['pur_date']>date1) & (hist_df['pur_date']<date2)]\nhist_df_reduced_JunAug.to_csv('hist_JunAug.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc91d4b40138f880826e82778d9962ff32651d00"},"cell_type":"code","source":"date1 = datetime.strptime('2017-03-01', '%Y-%m-%d')\ndate2 = datetime.strptime('2017-05-31', '%Y-%m-%d')\nhist_df_reduced_MarMay = hist_df.loc[(hist_df['pur_date']>date1) & (hist_df['pur_date']<date2)]\nhist_df_reduced_MarMay.to_csv('hist_MarMay.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"701f1233ea47bb68fddcb6ef0670558060eb63f9"},"cell_type":"code","source":"hist_df_reduced_MarMay.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"786eb0dc076839bec9a766ddab405fdab6633d9e"},"cell_type":"code","source":"date1 = datetime.strptime('2017-01-01', '%Y-%m-%d')\ndate2 = datetime.strptime('2017-02-28', '%Y-%m-%d')\nhist_df_reduced_JanFeb = hist_df.loc[(hist_df['pur_date']>date1) & (hist_df['pur_date']<date2)]\nhist_df_reduced_JanFeb.to_csv('hist_JanFeb.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9557a716f6a05fb9a56ff578cb34b3f0a35e61f6"},"cell_type":"code","source":"hist_df_reduced['month']=hist_df_reduced['pur_date'].dt.to_period('M')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c64101e4412b3d7dbb2a64a48506f77f4a5f7858"},"cell_type":"code","source":"#Lets see the trans vol by month and week\n#hist_df_red_by_month=hist_df_reduced.groupby(['month']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"52a5590273c3c20c070caf9d3bd4026bca9705e9"},"cell_type":"code","source":"#hist_df_red_by_month.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e7ad686f96052ce2be61ec2d9ae9f3a2de483909"},"cell_type":"code","source":"#hist_df_reduced['week']=hist_df_reduced['pur_date'].dt.to_period('W')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b3038ed73d34108b5b6bd862e45132939594f022"},"cell_type":"code","source":"#hist_df_red_by_wk=hist_df_reduced.groupby(['week']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"82e642ce75b65af7317e155108d41971b7f6633c"},"cell_type":"code","source":"#ax = hist_df_red_by_month.plot(kind='bar',figsize=(14,8),fontsize=14)\n#ax.set_title(\"Historical Card Trans Volume By Month\",fontsize=28,fontweight='bold')\n#ax.set_xlabel(\"Month\",fontsize=14,fontweight='bold')\n#ax.set_ylabel(\"Volume of Trans\", fontsize=\"14\",fontweight=\"bold\")\n#ax.tick_params(axis='both',which='major',labelsize=18)\n#ax.tick_params(axis='both',which='minor',labelsize=18)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f38096612ba425971ec57954b930efc15ad915ae"},"cell_type":"code","source":"#Lets look at the transactions by Merchant Category\nTrans_by_MerchantCat = pd.DataFrame(hist_df_reduced.groupby(['merchant_category_id']).size())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"369d33506edfe36f31e789774ab3be59ef9a8446"},"cell_type":"code","source":"Trans_by_MerchantCat = Trans_by_MerchantCat.rename(columns={0: 'TransCt'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7fc6f6cd6a40ab5009755e7678229566740439ad"},"cell_type":"code","source":"print(\"Max of Count of Trans by Merch Cat ID:\"+str(Trans_by_MerchantCat['TransCt'].max().round(2)))\nprint(\"Median of Count of Trans by Merch Cat ID:\"+str(round(Trans_by_MerchantCat['TransCt'].median(),2)))\nprint(\"Mean of Count of Trans by Merch Cat ID:\"+str(round(Trans_by_MerchantCat['TransCt'].mean(),2)))\nprint(\"Min of Count of Trans by Merch Cat ID:\"+str(round(Trans_by_MerchantCat['TransCt'].min(),2)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"28b1c714108857f9a73e4ba9b538962ada6bec44"},"cell_type":"markdown","source":"**We see this is highly skewed and is a wide variation between the vol of Trans for diff Merch Categories, there are Merchant category with 1.5M transactions in last 3 months, as well as some Merchant category with 1 trans. lets explore this a bit further**"},{"metadata":{"trusted":true,"_uuid":"c8de8ef3dd777acb781077156d3b565bfa9977f2"},"cell_type":"code","source":"Trans_by_MerchantCat.sort_values(by='TransCt',ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ff331435721c4d1087890c633800f3c652a73da1"},"cell_type":"code","source":"axarr = Trans_by_MerchantCat['TransCt'].sort_values().plot(kind='bar',figsize=(60,40))\naxarr.set_title(\"Bar chart of count of Trans by Merch Cat Id\",fontsize=72,fontweight='bold')\naxarr.set_xlabel(\"Merch Cat Id\",fontsize=36)\n#axarr[0][0].set_xlim((0,100))\naxarr.set_ylabel(\"Frequency\",fontsize=36)\naxarr.tick_params(axis='both',which='major',labelsize=36)\naxarr.tick_params(axis='both',which='minor',labelsize=36)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e8b53a6e8db9a73e1b2da4798dafb1cee9d990b1"},"cell_type":"code","source":"TransAmt_by_MerchantCat = pd.DataFrame(hist_df_reduced.groupby(['merchant_category_id'])['purchase_amount'].sum())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5de2e9cfcfcdf5282121bfb70356400adc5168be"},"cell_type":"code","source":"TransAmt_by_MerchantCat.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1adda5095985c8063590664a1730685d75d30aec"},"cell_type":"code","source":"print(round(TransAmt_by_MerchantCat['purchase_amount'].max(),2))\nprint(round(TransAmt_by_MerchantCat['purchase_amount'].min(),2))\nprint(round(TransAmt_by_MerchantCat['purchase_amount'].median(),2))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c3772ef9057556aa30a48046272f0d7c314f3c9"},"cell_type":"markdown","source":"**The data shows highly spread out dist by Merch Cat, median is -185 USD, min is -763206 USD**"},{"metadata":{"trusted":true,"_uuid":"fa282ab87d74c69883d0841455f7239d5fd770d7"},"cell_type":"code","source":"axarr = TransAmt_by_MerchantCat['purchase_amount'].sort_values().plot(kind='bar',figsize=(40,40))\naxarr.set_title(\"Bar chart of Amount of Trans by Merch Cat Id\",fontsize=72,fontweight='bold')\naxarr.set_xlabel(\"Merch Cat Id\",fontsize=36)\n#axarr[0][0].set_xlim((0,100))\naxarr.set_ylabel(\"Frequency\",fontsize=36)\naxarr.tick_params(axis='both',which='major',labelsize=36)\naxarr.tick_params(axis='both',which='minor',labelsize=36)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f81f0cd01ecf42e812aa5db81b4f19d2d6913916"},"cell_type":"markdown","source":"**The above shows that the Trans Amt is Negative for half of the Merchant categories. What does this mean, does it mean for these Merchant categories, the Users made payment to the Card, but never purchased !! Need to be explored from merchants.csv**"},{"metadata":{"_uuid":"59d06b0636104c5d0d525ad6d46f365f6a36e41a"},"cell_type":"markdown","source":"**Lets group the transactions by card_id and sum the purchase_amounts**"},{"metadata":{"trusted":true,"_uuid":"03816dfbeb5311475d2517594400430ce58ba8db"},"cell_type":"code","source":"TransAmt_by_Card = pd.DataFrame(hist_df_reduced.groupby(['card_id'])['purchase_amount'].sum())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d0eaccc48cfc57c38d3a444ba78dcadc9700a192"},"cell_type":"code","source":"ax=plt.hist(TransAmt_by_Card['purchase_amount'],bins=5)\nplt.xlim(-2000,150000)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"34ac29bb8d2073245970b6cd7597ffa39eee8322"},"cell_type":"code","source":"print(\"Max amt by Card:\"+str(TransAmt_by_Card.max().round(2)))\nprint(\"Min amt by Card:\"+str(TransAmt_by_Card.min().round(2)))\nprint(\"Median amt by Card:\"+str(TransAmt_by_Card.median().round(2)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c030ad1e641bed0ae5c82a35df368edb46184415"},"cell_type":"code","source":"data = TransAmt_by_Card['purchase_amount']\npercentiles = percentile(data,[10,25,30,50,75,90, 95, 98, 99, 99.95])\nprint(percentiles.round(2))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d95d6f0bae322ced378cf1cb263716b9d8c684f5"},"cell_type":"markdown","source":"**This is a strange result, the 10 percentile value is -46 and 90 percentile value is -1, but max is 134755.**"},{"metadata":{"trusted":true,"_uuid":"ce4909ac13071b0e4200d0e371deb33583d06921"},"cell_type":"code","source":"print(len(TransAmt_by_Card))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6ee8ebb2381580f46bb65879fad434585ffc39f5"},"cell_type":"code","source":"TransAmt_by_Card.sort_values(by='purchase_amount',ascending=False)[:50]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"47c9025d58f9b6a1b6d40cfdbd11ed6b4ff406f7"},"cell_type":"code","source":"TransAmt_by_Card.sort_values(by='purchase_amount',ascending=False)[27000:27100]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5978d1f3800a389a4fe79398212986668563bedd"},"cell_type":"markdown","source":"**Hard to believe but true,  99 percentile of the data is 11 USD or below, above 99.5 percentile mark we get values like 3500 USD going all the way upto max 134755 USD**"},{"metadata":{"trusted":true,"_uuid":"70d3d1e1b54afc094adad31e58cc5400b4a66a6a"},"cell_type":"code","source":"TransAmt_by_Card_red = TransAmt_by_Card.loc[(TransAmt_by_Card['purchase_amount']>=100)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"97dae84230f36890e4bb2e3338e62000d966aaf0"},"cell_type":"code","source":"print(len(TransAmt_by_Card_red))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a43a9d12a97895b1f6488ced6bc179e2f24591d4"},"cell_type":"markdown","source":"**This means only 405 cards have made total transactions worth USD 100 or more in last 3 months!!**"},{"metadata":{"trusted":true,"_uuid":"b635a26157f17cc5991e4472ad872850a319bec2"},"cell_type":"code","source":"ax=plt.hist(TransAmt_by_Card_red['purchase_amount'],bins=100)\nplt.xlim(-2000,25000)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f1fdc2362723400681f34969c18330370426f34b"},"cell_type":"markdown","source":"**Lets explore Trans amount by Merchant Id for a Card id**"},{"metadata":{"trusted":true,"_uuid":"733f8ace5daa3fa8ef9d5accabcbaabb60892cac"},"cell_type":"code","source":"TransAmt_by_CardbyMerch = pd.DataFrame(hist_df_reduced.groupby(['card_id','merchant_id'])['purchase_amount'].sum())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a1f29eda317b265564a00ee0a4250c70cf6c03b3"},"cell_type":"code","source":"data = TransAmt_by_CardbyMerch['purchase_amount']\npercentiles = percentile(data,[10,25,30,50,75,90, 95, 98, 99, 99.95])\nprint(percentiles.round(2))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"33d95a2dbd9a565554bea26fb5a55f94590cfa88"},"cell_type":"markdown","source":"**This is a strange result, 99 percentile of Card-Merch combination is having purch value <= USD 1.77**"},{"metadata":{"_uuid":"b83432ce0da898f4782f8eed11545a7eca2054b4"},"cell_type":"markdown","source":"**Lets look at number of transactions by Card id and Merchant id**"},{"metadata":{"trusted":true,"_uuid":"f92646a4d4d9e5e96fa5618a8d8242a65b80f45b"},"cell_type":"code","source":"TransVol_by_CardbyMerch = pd.DataFrame(hist_df_reduced.groupby(['card_id','merchant_id']).size())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a0c4e1b00469f0467b5d63ef142321ccb2e2cbf1"},"cell_type":"code","source":"data = TransVol_by_CardbyMerch\npercentiles = percentile(data,[10,25,30,50,75,90, 95, 98, 99, 99.95])\nprint(percentiles)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ec6deabd86a2ab8559c0439c11e4b99af00f37ea"},"cell_type":"markdown","source":"**The above shows a strange result. For 90 percentile of the card-Merch combination have vol <=4, and 75 percentile of card-Merch has 2 or less transactions**"},{"metadata":{"trusted":true,"_uuid":"b89978a9f62f27cec22d05d0dab5ebccee45e7bc"},"cell_type":"code","source":"print(len(TransVol_by_CardbyMerch))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"71d62783a615d280f91b5c7f726f7aba14eca464"},"cell_type":"code","source":"data = Trans_by_MerchantCat['TransCt']\npercentiles = percentile(data,[10,25,30,50,75,90])\nprint(percentiles.round())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e20aa637242e7650c362d97056c093fdf0b3ebd3"},"cell_type":"code","source":"Trans_by_MerchantCat_gt25k=Trans_by_MerchantCat['TransCt'].loc[(Trans_by_MerchantCat['TransCt'] > 25000)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06db5d612b3ae762f3e4b56fc71cd1543bc132b6"},"cell_type":"code","source":"len(Trans_by_MerchantCat)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cbdd191eb82d1529d009c0f21a22899b2afba992"},"cell_type":"code","source":"len(Trans_by_MerchantCat_gt25k)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"66a40c306587c6d5530f3bf4828d29449ea0275c"},"cell_type":"code","source":"axarr = Trans_by_MerchantCat_gt25k.sort_values().plot(kind='bar',figsize=(60,40))\naxarr.set_title(\"Bar chart of count of Trans by Merch Cat Id (>25k)\",fontsize=72,fontweight='bold')\naxarr.set_xlabel(\"Merch Cat Id\",fontsize=36)\n#axarr[0][0].set_xlim((0,100))\naxarr.set_ylabel(\"Frequency\",fontsize=36)\naxarr.tick_params(axis='both',which='major',labelsize=36)\naxarr.tick_params(axis='both',which='minor',labelsize=36)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d23a2992c9a817059be6f8541734dd1e2a6bb951"},"cell_type":"code","source":"HighVolMerchCat = Trans_by_MerchantCat_gt25k.index","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"de55686a9e2f4f6f98d375400401afcb5caf4f1c"},"cell_type":"code","source":"HighVolMerchCat","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6cc06c5a294e0b082dad00348dd479a1a8bcd026"},"cell_type":"markdown","source":"**Read train.csv to be combined with Historical and New Merch transactions**"},{"metadata":{"trusted":true,"_uuid":"14a1dd702363fbc9841e6d15ca2e3991011737be"},"cell_type":"code","source":"#read train df\ntrain_df=pd.read_csv('../input/elo-merchant-category-recommendation/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"017a4fb7640aff8c26c8b289e90c65fce16ea5b8"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5d8627c218ee79953908e775965bd7c0cf83a4ce"},"cell_type":"code","source":"print(len(train_df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"79f6a14b61df0a807a728d68ae516f9c0c73659c"},"cell_type":"code","source":"train_df['first_active_month']=pd.to_datetime(train_df['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"61a211a14b68a428e135cf5d8b9b31d5abe83096"},"cell_type":"code","source":"train_df['first_active_month'].min()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3c957c5fb3a8b8c4472d105bf1055a72ba5586f8"},"cell_type":"code","source":"train_df['first_active_month'].max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e34a0920858750390cdf81548f73df4972f10b2a"},"cell_type":"code","source":"print(len(train_df['card_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"875f15b451f3ada8e3caca8a583eca99b884e6d5"},"cell_type":"markdown","source":"**Since the length of unique card ids = length of dataset, we conclude train_df has one row per card id, and hence the loyalty score is per card id**"},{"metadata":{"trusted":true,"_uuid":"9663733e7dfab29a46c030b4bf4f00e244dc0907"},"cell_type":"code","source":"train_df['month']=train_df['first_active_month'].dt.to_period('M')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f927489171d79d7ff227a4eaa76d808323570eec"},"cell_type":"code","source":"train_df_by_month=train_df.groupby(['month']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2434358bf2d870e1f6f4c4ec988358bd565dbe0a"},"cell_type":"code","source":"ax = train_df_by_month.plot(kind='bar',figsize=(14,8),fontsize=14)\nax.set_title(\"Training dataset - Card Trans Volume By First Active Month\",fontsize=28,fontweight='bold')\nax.set_xlabel(\"First Active Month\",fontsize=14,fontweight='bold')\nax.set_ylabel(\"Volume of Trans\", fontsize=\"14\",fontweight=\"bold\")\nax.tick_params(axis='both',which='major',labelsize=18)\nax.tick_params(axis='both',which='minor',labelsize=18)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"03a78aff941a29b4ed28f78bab4d163fce3c6be2"},"cell_type":"code","source":"print(len(train_df['month'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2d9f024e0300dfd99aab2952e48dfb6246d4c00d"},"cell_type":"code","source":"#train_df_by_month_mod = train_df_by_month.loc[train_df_by_month > 250]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5d0de0694f1424f4ec09a8d7268f1c22b3a0e40c"},"cell_type":"code","source":"#first_act_mth_list = train_df_by_month_mod.index","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"792273bd1b310ebeb2a45f4b21b58e28ec64c811"},"cell_type":"code","source":"#first_act_mth_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"28f2d18cfb5bf9d9b607af0cf0d1db6db60ca6f6"},"cell_type":"code","source":"#train_df_red = train_df.loc[train_df.month.isin(first_act_mth_list)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d4cb0a91901d6f3a7f795f2c25a7bb1563db71d7"},"cell_type":"code","source":"#print(len(train_df_red))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"613df4be9f582afe7afb995bc1e785d8e190ced9"},"cell_type":"code","source":"#train_df_red.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4ef542f2b59945c4f74d8709c9f59d21cc7d6d1b"},"cell_type":"markdown","source":"**Lets read New Merchant Transactions and do similar study as in case of Historical Transactions**"},{"metadata":{"trusted":true,"_uuid":"82e0be19553c5677d873b6439f83dc9635202653"},"cell_type":"code","source":"new_merch_df = pd.read_csv(\"../input/elo-merchant-category-recommendation/new_merchant_transactions.csv\")\nnew_merch_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8004d47674606e62da0be83b7ce2537bcc56b963"},"cell_type":"code","source":"new_merch_df['pur_date']=pd.to_datetime(new_merch_df['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"73ff00320472b41816371cab2c2daaf7532b090c"},"cell_type":"code","source":"new_merch_df['pur_date'].max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0a376904dd09dc65a57b68e1226872d884068668"},"cell_type":"code","source":"new_merch_df['pur_date'].min()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d02bec9c9991ffc67ff2375c9a9ad2ba8e6c9043"},"cell_type":"code","source":"new_merch_df['month']=new_merch_df['pur_date'].dt.to_period('M')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"895ec2d14c8181360901231cd90c92359c85efba"},"cell_type":"code","source":"#new_merch_df['wk']=new_merch_df['pur_date'].dt.to_period('W')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"045218cced9703af0a08dc031cb6281e5b00b397"},"cell_type":"code","source":"new_merch_df_by_month = new_merch_df.groupby(['month']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d70717658f42f3c699b70a8f5d5d9736ff742db8"},"cell_type":"code","source":"#new_merch_df_by_wk = new_merch_df.groupby(['wk']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e2db2f4d8beb7c9b0ab33104404839cfbd5a2605"},"cell_type":"code","source":"ax = new_merch_df_by_month.plot(kind='bar',figsize=(14,8),fontsize=14)\nax.set_title(\"New Merchant Card Trans Volume By Month\",fontsize=28,fontweight='bold')\nax.set_xlabel(\"Month\",fontsize=14,fontweight='bold')\nax.set_ylabel(\"Volume of Trans\", fontsize=\"14\",fontweight=\"bold\")\nax.tick_params(axis='both',which='major',labelsize=18)\nax.tick_params(axis='both',which='minor',labelsize=18)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1b54bba5a1b47f80f417af8936e1302e8c44358c"},"cell_type":"code","source":"#Group by Merchant Category Id\nTrans_by_MerchantCat_new = pd.DataFrame(new_merch_df.groupby(['merchant_category_id']).size())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"51e48fe4c0a5fab739032dc1540fc5e52f00404d"},"cell_type":"code","source":"Trans_by_MerchantCat_new = Trans_by_MerchantCat_new.rename(columns={0: 'TransCt'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c835a1db0aed241c19469a71bf123e09771d8ac2"},"cell_type":"code","source":"axarr = Trans_by_MerchantCat_new['TransCt'].sort_values().plot(kind='bar',figsize=(60,40))\naxarr.set_title(\"New Merchant Count of Trans by Merch Cat Id\",fontsize=72,fontweight='bold')\naxarr.set_xlabel(\"Merch Cat Id\",fontsize=36)\n#axarr[0][0].set_xlim((0,100))\naxarr.set_ylabel(\"Frequency\",fontsize=36)\naxarr.tick_params(axis='both',which='major',labelsize=36)\naxarr.tick_params(axis='both',which='minor',labelsize=36)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eaceaafbe3a2e045d9846d3bcca5cd051e46e1d6"},"cell_type":"code","source":"data = Trans_by_MerchantCat_new['TransCt']\npercentiles = percentile(data,[25,30,50,75,90])\nprint(percentiles)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"18b7cf11dc325fcfc806bab8d8a204c7356e8454"},"cell_type":"code","source":"Trans_by_MerchantCat_new_gt5k=Trans_by_MerchantCat_new['TransCt'].loc[(Trans_by_MerchantCat_new['TransCt'] > 5000)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3157361ad30d238f0bf937f4a26176cf7056b1cd"},"cell_type":"code","source":"HighVolMerchCat_new = Trans_by_MerchantCat_new_gt5k.index","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f953c213301570ab55bf520ed9c85c8df6e41e9e"},"cell_type":"code","source":"HighVolMerchCat_new","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"37b54f2694ecff3d5b90203da07dbad400f749bd"},"cell_type":"code","source":"c = set(HighVolMerchCat) & set(HighVolMerchCat_new)  #  & calculates the intersection.\nprint(len(c))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"79f256d425dea782a323bc59ff92cfa333d17120"},"cell_type":"code","source":"HighVolMerchComb = set(HighVolMerchCat) | set(HighVolMerchCat_new)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a34b257cf2ccfc5d9cf0e8c557f52f74a15d5cd9"},"cell_type":"code","source":"print(len(HighVolMerchComb))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"24d3c09ddd2f321db9fed38a1ff4ba51f11f982a"},"cell_type":"code","source":"#new_merch_df_red = new_merch_df.loc[(new_merch_df.merchant_category_id.isin(HighVolMerchCat_new))]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5fd499e474040c044aa5884ac66cb7872f8f0393"},"cell_type":"code","source":"#print(len(new_merch_df_red))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5ddc611660f073b01a5346b30aa442cafc0d876a"},"cell_type":"code","source":"print(len(new_merch_df['card_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c180839a6b170d337b3fab711ff6105bb8de1373"},"cell_type":"code","source":"TransAmt_by_CardnewMerch = pd.DataFrame(new_merch_df.groupby(['card_id','merchant_id'])['purchase_amount'].sum())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5349fe26df423718ca1e77e220608fdf10d09864"},"cell_type":"code","source":"#TransAmt_by_CardnewMerch.hist()\nax=plt.hist(TransAmt_by_CardnewMerch['purchase_amount'],bins=50)\nplt.xlim(-5,25)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b5d79f2889fae1786fa475003b3df71fbd64c34b"},"cell_type":"code","source":"data = TransAmt_by_CardnewMerch['purchase_amount']\npercentiles = percentile(data,[10,25,30,50,75,90, 95, 98, 99, 99.95])\nprint(TransAmt_by_CardnewMerch['purchase_amount'].min())\nprint(TransAmt_by_CardnewMerch['purchase_amount'].max())\nprint(percentiles)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7ba102dd8fe8317fbaf6f22a170d808a2b638848"},"cell_type":"markdown","source":"**Lets create Trans Vol grouped by Card Id and Merchant Id, ie for each card, the number of transactions done at same Merchant**"},{"metadata":{"trusted":true,"_uuid":"1f3474dc538b5a6d1c2b22ff64b80c088316bc85"},"cell_type":"code","source":"TransVol_by_CardnewMerch = pd.DataFrame(new_merch_df.groupby(['card_id','merchant_id']).size())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8fdd59e86e6269a480e28933c965442487f58f7"},"cell_type":"code","source":"TransVol_by_CardnewMerch = TransVol_by_CardnewMerch.rename(columns={0: 'TransCt'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bd455dbcbc8246ee3b18330218a87e1c3102df39"},"cell_type":"code","source":"TransVol_by_CardnewMerch.sort_values(by='TransCt',ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a5b8c8566c0c45442154d58d1b888658d63cd200"},"cell_type":"code","source":"TransVol_by_CardnewMerch = TransVol_by_CardnewMerch.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9bb2ac6a647c0abb3070cfded924f7197f3a9385"},"cell_type":"code","source":"TransVol_by_CardnewMerch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c378b0bb85aa7c5935452cd790660c710ad049d5"},"cell_type":"code","source":"data = TransVol_by_CardnewMerch['TransCt']\npercentiles = percentile(data,[10,25,30,50,75,90, 95, 98, 99, 99.95,99.98])\nprint(TransVol_by_CardnewMerch['TransCt'][0].min())\nprint(TransVol_by_CardnewMerch['TransCt'][0].max())\nprint(percentiles)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d29c27dd57cec3fbc7158a1d26df0df6948fe1e4"},"cell_type":"code","source":"merchant_df = pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3b5754403c91212135c2917b8094400280d32602"},"cell_type":"code","source":"len(merchant_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bcac061ca8aa6b1ab850152954c2e34bc4daf247"},"cell_type":"code","source":"len(merchant_df['merchant_id'].unique())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0b0ec5e18dedec9fb7a96bdfb8648c00eb99d6eb"},"cell_type":"markdown","source":"**This is strange, merchant csv is supposed to be by merchant_id, but there are few duplicates, lets remove the duplicates and take unique values since diff is small**"},{"metadata":{"trusted":true,"_uuid":"d19398a4219e982b9393acf6b3bf4c0ecef2af55"},"cell_type":"code","source":"merchantList=merchant_df['merchant_id'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"314836a7a0099aebff1850fc5695856447da651d"},"cell_type":"code","source":"merchant_df=merchant_df.drop_duplicates(subset=['merchant_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b55536414cc292c1f23c9431c1fa07dc3d78faec"},"cell_type":"code","source":"len(merchant_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a3c92f5ee6764bdf4732a91b938343e878fddb9a"},"cell_type":"code","source":"len(merchant_df['merchant_id'].unique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e48eeef2feec6dba7aebd2def4b95d9154d3d8b6"},"cell_type":"code","source":"merchant_df= merchant_df.reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"44dae4de0ffb07fae2211fa601eb57ca806f3313"},"cell_type":"code","source":"TransVol_by_CardnewMerch = pd.merge(TransVol_by_CardnewMerch, merchant_df, how = 'inner', left_on = ['merchant_id'], right_on=['merchant_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"69f1294deb61a3a7bff6c32241a7adf2f7463291"},"cell_type":"code","source":"TransVol_by_CardnewMerch.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"49c35d613a766cbfef7cd69b6ff9386b45b9263b"},"cell_type":"markdown","source":"**Lets find out how many count of Transactions are done at High Vol Merch Categories**"},{"metadata":{"trusted":true,"_uuid":"21510c7a38b5912ae0ba1380f3414ee7c0afce01"},"cell_type":"code","source":"TransVol_by_CardnewMerchHighVol = TransVol_by_CardnewMerch.loc[(TransVol_by_CardnewMerch.merchant_category_id.isin(HighVolMerchCat_new))]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"43202539b662dbb9e683369243525f5086d14b3c"},"cell_type":"code","source":"len(TransVol_by_CardnewMerch)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7aa12c74ef3ecc671a913590453a386944fc4d28"},"cell_type":"code","source":"len(TransVol_by_CardnewMerchHighVol)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"04693cf172aa0572345e4d9ca366256ed7d830a0"},"cell_type":"code","source":"TransVol_by_CardnewHighVolMerch=TransVol_by_CardnewMerchHighVol.groupby(['card_id']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0cd2b602001edd624f5d5af51f97616c9dfd402"},"cell_type":"code","source":"TransVol_by_CardnewHighVolMerch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f6c7bfd906c52093b8f2c1a136e0b6b0f9601ddf"},"cell_type":"code","source":"TransVol_by_CardnewHighVolMerch=pd.DataFrame(TransVol_by_CardnewHighVolMerch)\nTransVol_by_CardnewHighVolMerch = TransVol_by_CardnewHighVolMerch.reset_index()\nTransVol_by_CardnewHighVolMerch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fe079112a1f667dfa3d8a95d904f16876cddc750"},"cell_type":"code","source":"TransVol_by_CardnewHighVolMerch = TransVol_by_CardnewHighVolMerch.rename(columns={0: 'TransCt'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8cf26b491b1c58473ad39514631403d53c920576"},"cell_type":"code","source":"TransVol_by_CardnewHighVolMerch.sort_values(by='TransCt',ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19d6c09158d8b3cbaf48a700087d20089502dea2"},"cell_type":"code","source":"TransVol_by_CardnewHighVolMerch = TransVol_by_CardnewHighVolMerch.rename(columns={'TransCt': 'NewHighVolMerchTransCt'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"80e0323a69bad00335e38adea5d97d6f2a3c6f39"},"cell_type":"code","source":"TransVol_by_CardnewHighVolMerch.sort_values(by='NewHighVolMerchTransCt',ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9b5a5026c900f0f67ee9ab5695b0ae2e829b8d5f"},"cell_type":"markdown","source":"**Lets group Trans Volume by Card for New Merchants with number of Merchants at which transaction done for each Card**"},{"metadata":{"trusted":true,"_uuid":"7a25c05468e1f0d34449ebed225a302ae189dbe8"},"cell_type":"code","source":"TransVol_by_CardnewMerchCt=TransVol_by_CardnewMerch.groupby(['card_id']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d9d6a2803796f8616d2600bb307bb92e8cb80f9d"},"cell_type":"code","source":"TransVol_by_CardnewMerchCt.sort_values(ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5358f11e442b9118df18ac775c29d162715508d6"},"cell_type":"code","source":"print(TransVol_by_CardnewMerchCt.min())\nprint(TransVol_by_CardnewMerchCt.median())\nprint(TransVol_by_CardnewMerchCt.max())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"caf010967e9418d1cb1a64ff8c0a29a6367d888b"},"cell_type":"code","source":"TransVol_by_CardnewMerchCt = TransVol_by_CardnewMerchCt.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"db8a25433cc22d4e4bda24696bbfdb40d9a33ce0"},"cell_type":"code","source":"TransVol_by_CardnewMerchCt = TransVol_by_CardnewMerchCt.rename(columns={0: 'NewMerchTransCt'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2a7e00b537ae08666d6ee0be51aebd07dceeb9c5"},"cell_type":"code","source":"TransVol_by_CardnewMerchCt.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"63e9513514c7fdb5ac6d4bb97e86588de5eaf408"},"cell_type":"code","source":"#hist_df_reduced_merch = hist_df_reduced.loc[(hist_df_reduced.merchant_category_id.isin(HighVolMerchCat))]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"561e38a04f8a327d20ffc95957e59841dfad139d"},"cell_type":"code","source":"#print(len(hist_df_reduced_merch))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a3e3b32f728de5f3672bf541f6c4dfb9c629e618"},"cell_type":"code","source":"#print(len(hist_df_reduced_merch['card_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6a8a2c1c9734d7110310a2026578376bb2328b3d"},"cell_type":"code","source":"#hist_df_reduced_merch.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c6637e4a7478f998e5767fdedb2e5a9a249a9fef"},"cell_type":"markdown","source":"**Lets explore volume of Transactions by City**"},{"metadata":{"trusted":true,"_uuid":"89874eb6d9d37bdfded2b8a6a258002d0fad21aa"},"cell_type":"code","source":"print(len(hist_df_reduced['city_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3197c03344b5e9e14c05b5d59e23dd56e4173cad"},"cell_type":"code","source":"#Lets group transactions by city\nhist_df_by_city = hist_df_reduced.groupby(['city_id']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"63abcdf2f9d6aa6b43fb7df68ef3e23e79f6431c"},"cell_type":"code","source":"print(hist_df_by_city.max())\nprint(hist_df_by_city.min())\nprint(hist_df_by_city.median())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8716a37cae72112c5ec7b09285576cbfb04a4faa"},"cell_type":"markdown","source":"**Again its skewed data with some city having 1.6M transactions in past 3 months, whereas some city having only 20 transactions**"},{"metadata":{"trusted":true,"_uuid":"966d4286c9e8f5d9505cb49f42df01db595f9e46"},"cell_type":"code","source":"axarr = hist_df_by_city.sort_values().plot(kind='bar',figsize=(60,40))\naxarr.set_title(\"Bar chart of count of Trans by City Id\",fontsize=72,fontweight='bold')\naxarr.set_xlabel(\"City Id\",fontsize=36)\n#axarr[0][0].set_xlim((0,100))\naxarr.set_ylabel(\"Frequency\",fontsize=36)\naxarr.tick_params(axis='both',which='major',labelsize=36)\naxarr.tick_params(axis='both',which='minor',labelsize=36)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b7b064aadd566d01a6a56df4e77535dcd62381b3"},"cell_type":"code","source":"data = hist_df_by_city\npercentiles = percentile(data, [10,25,30,40, 50, 60, 75, 90])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cf6254541e5a6fe495f0c7b03dc64f197511dab1"},"cell_type":"code","source":"print(percentiles)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be10a0270f5a04f6734abf965a02906db54d7543"},"cell_type":"code","source":"#hist_df_by_city=hist_df_by_city.loc[(hist_df_by_city>15000)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0d74f4f453421ab39831ebd9ea327d18d6e56850"},"cell_type":"code","source":"#city_list = hist_df_by_city.index","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"04c13f65d63eec26e9a690cf3292778f33516cc5"},"cell_type":"code","source":"#hist_df_reduced_merch_city = hist_df_reduced_merch.loc[hist_df_reduced_merch.city_id.isin(city_list)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b2ad9633804f7175102ab55b47e4e45765d876e6"},"cell_type":"code","source":"#print(len(hist_df_reduced_merch_city))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f4108ee6e1db948cde68d91464de7009de4643f8"},"cell_type":"code","source":"new_merch_df_city = new_merch_df.groupby(['city_id']).size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06c5ec5c9ed350b6472bd5c18523ab590db65384"},"cell_type":"code","source":"data = new_merch_df_city\npercentiles = percentile(data, [10,25,30,40, 50, 60, 75, 90])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5441bc7a597f4a0ee0dc20eac3eb482814a4928c"},"cell_type":"code","source":"print(percentiles)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8fa3167c42a31177d2a32ee26e4fb0c73c903e2b"},"cell_type":"code","source":"axarr = new_merch_df_city.sort_values().plot(kind='bar',figsize=(60,40))\naxarr.set_title(\"Bar chart of count of Trans by City Id, New Merch\",fontsize=72,fontweight='bold')\naxarr.set_xlabel(\"City Id\",fontsize=36)\n#axarr[0][0].set_xlim((0,100))\naxarr.set_ylabel(\"Frequency\",fontsize=36)\naxarr.tick_params(axis='both',which='major',labelsize=36)\naxarr.tick_params(axis='both',which='minor',labelsize=36)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7976a88a2ff43011a07da11d56b473380e869362"},"cell_type":"code","source":"#new_merch_df_city=new_merch_df_city.loc[(new_merch_df_city>5000)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8f5b237e1ac30ce228c94d098a56e52066d6bb8"},"cell_type":"code","source":"#city_list_new = new_merch_df_city.index","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6fb6f0e42db920a0b20985d218110ec80777b872"},"cell_type":"code","source":"#new_merch_df_city = new_merch_df_red.loc[new_merch_df_red.city_id.isin(city_list_new)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"54e7d94c830563552a671d04564e32d2819e9d6b"},"cell_type":"code","source":"#new_merch_df_city.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b162647528aa08aa9fbdcd81f5341c7439bae8f7"},"cell_type":"code","source":"#en(new_merch_df_city)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"69a54d4778ba02ba2e117aeebe9ca02c0e1e1ed3"},"cell_type":"markdown","source":"**Lets group the transactions by Card summing up the purchase amounts, and getting max, median, std dev also, however we will not use all this in correlation analysis as in prev study we found correlation only with New Merch Max Trans Amt by Card**"},{"metadata":{"trusted":true,"_uuid":"0a565102640fd9d8aecffb8549e94efd6a4e88ee"},"cell_type":"code","source":"new_merch_trans_sum_bycard = new_merch_df.groupby(['card_id'])['purchase_amount'].sum()\nnew_merch_trans_max_bycard = new_merch_df.groupby(['card_id'])['purchase_amount'].max()\nnew_merch_trans_median_bycard = new_merch_df.groupby(['card_id'])['purchase_amount'].median()\nnew_merch_trans_std_bycard = new_merch_df.groupby(['card_id'])['purchase_amount'].std()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0be55f590cb78fbf6ecc9b4b3dea5ea8ba2a2ff5"},"cell_type":"code","source":"new_merch_trans_sum_bycard.sort_values(ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"59c59b43bd64b4784a36acca3db8197532d0cdb8"},"cell_type":"code","source":"hist_trans_sum_bycard = hist_df_reduced.groupby(['card_id'])['purchase_amount'].sum()\nhist_trans_max_bycard = hist_df_reduced.groupby(['card_id'])['purchase_amount'].max()\nhist_trans_median_bycard = hist_df_reduced.groupby(['card_id'])['purchase_amount'].median()\nhist_trans_std_bycard = hist_df_reduced.groupby(['card_id'])['purchase_amount'].std()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"76f7d13a6a9732cf97be42b10fcc948a0e3e30d2"},"cell_type":"code","source":"hist_trans_sum_bycard.sort_values(ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"04680dcf74074da1fdef836c26e08771c3037080"},"cell_type":"code","source":"Card_union = set(new_merch_trans_sum_bycard.index) | set(hist_trans_sum_bycard.index)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"503b3a8dba21e2de904f47a19455062477d9b8be"},"cell_type":"code","source":"len(Card_union)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c8512527c74ebc376fb256dfaf1e6a323267545a"},"cell_type":"code","source":"Card_intersection = set(new_merch_trans_sum_bycard.index) & set(hist_trans_sum_bycard.index)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f02720d4a7efe0fb9b8b50b2f7d3f1c1142bbc90"},"cell_type":"code","source":"len(Card_intersection)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d3046f08361825e3025432401f4e54cad62a49b4"},"cell_type":"code","source":"#new_merch_df_city.to_csv('new_merch_red.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5deafbe9172a0480cf9d74cced822ac450a97b59"},"cell_type":"code","source":"#hist_df_reduced_merch_city.to_csv('hist_trans_red.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"43b99a2f4f5ef86fe9520df0b8bcf3b52fc67538"},"cell_type":"code","source":"#train_df_red.to_csv('train_red.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5ab90eb7b97228c8a0de232a1d27f97c78a27d69"},"cell_type":"code","source":"hist_df_reduced.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d956e03fbd09a0cf1757d25d316794f076be2faf"},"cell_type":"code","source":"hist_df_auth_ct_bycard=hist_df_reduced.groupby('card_id')['authorized_flag'].apply(lambda x: (x=='Y').sum()).reset_index(name='Auth_count')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bd086d54387f438a6d3bc3d399c368395043d8bb"},"cell_type":"code","source":"hist_df_auth_ct_bycard.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bbb61500b4446b0ccdf26d701234051ba639ed5c"},"cell_type":"code","source":"hist_df_ref_ct_bycard=hist_df_reduced.groupby('card_id')['authorized_flag'].apply(lambda x: (x=='N').sum()).reset_index(name='Ref_count')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8dbafe05221f1ef5df8b21b92683ae3e6c446dcd"},"cell_type":"code","source":"hist_df_ref_ct_bycard.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fa7262ee86e550d23fb24572a17ddcb2ee81a581"},"cell_type":"code","source":"print(len(hist_trans_sum_bycard.index))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"18300f23cc6149dc07bd9ee3629b34fe94f11bfd"},"cell_type":"code","source":"print(len(new_merch_trans_sum_bycard.index))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"54a5bd4c7276ca5d6b30213c4c89d35e1743de75"},"cell_type":"markdown","source":"**We ignore the payment amounts now, as correlation analysis in previous iteration didnt give any result**"},{"metadata":{"trusted":true,"_uuid":"48cccacd45a93258b19205e2ea6c755140938dff"},"cell_type":"code","source":"#df_card_all = pd.concat([new_merch_trans_sum_bycard, hist_trans_sum_bycard])\\\n#       .groupby('card_id').sum().reset_index()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7667f97002660891638d54f6cbe3bec71906b7a2"},"cell_type":"code","source":"#df_card_all.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9e3362acbe97c9d04e577f76ba3c910f2ce982ad"},"cell_type":"code","source":"#print(len(df_card_all))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"07e63c61c808aba0429be010a966e3462f5bf62a"},"cell_type":"markdown","source":"****The above is a verification that the operation is ok since the count matches the set union value above****"},{"metadata":{"trusted":true,"_uuid":"57eba41ead717c0884e6c5c5eb5af4fcd0e7e4d2"},"cell_type":"code","source":"new_merch_trans_max_bycard = pd.DataFrame(new_merch_trans_max_bycard)\nnew_merch_trans_median_bycard = pd.DataFrame(new_merch_trans_median_bycard)\nnew_merch_trans_std_bycard = pd.DataFrame(new_merch_trans_std_bycard)\n#new_merch_trans_max_bycard.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f7a23187b30e559eee186b7f1dd14cf03bf2629f"},"cell_type":"code","source":"hist_trans_max_bycard = pd.DataFrame(hist_trans_max_bycard)\nhist_trans_median_bycard = pd.DataFrame(hist_trans_median_bycard)\nhist_trans_std_bycard = pd.DataFrame(hist_trans_std_bycard)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6a5c504bb4de6c1e656169ab23794c17b6420ffd"},"cell_type":"code","source":"new_merch_trans_max_bycard = new_merch_trans_max_bycard.rename(columns={'purchase_amount': 'NewMerch_Max_Amt'})\nnew_merch_trans_median_bycard = new_merch_trans_median_bycard.rename(columns={'purchase_amount': 'NewMerch_Median_Amt'})\nnew_merch_trans_std_bycard = new_merch_trans_std_bycard.rename(columns={'purchase_amount': 'NewMerch_StdDev_Amt'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"237805df35d76158b9bdeb50722384fb43b007ef"},"cell_type":"code","source":"hist_trans_max_bycard = hist_trans_max_bycard.rename(columns={'purchase_amount': 'HistMerch_Max_Amt'})\nhist_trans_median_bycard = hist_trans_median_bycard.rename(columns={'purchase_amount': 'HistMerch_Median_Amt'})\nhist_trans_std_bycard = hist_trans_std_bycard.rename(columns={'purchase_amount': 'HistMerch_StdDev_Amt'})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"388b428baf6fde3f1f94669175949e98720a3eaf"},"cell_type":"markdown","source":"****We ignore sum of installments by card as prev iteration showed no correlation**"},{"metadata":{"trusted":true,"_uuid":"d37638cdf5bb5cece8a4c4c9482ffca10d8ff197"},"cell_type":"code","source":"#hist_instalment_by_card = hist_df_reduced.groupby(['card_id'])['installments'].sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e177c75d9031fd4817132a10fe44ef4792b5b664"},"cell_type":"code","source":"#new_instalment_by_card = new_merch_df.groupby(['card_id'])['installments'].sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eeabe1b91fea13bffc390df3053ad22b843ead82"},"cell_type":"code","source":"#df_card_all_instalment = pd.concat([hist_instalment_by_card, new_instalment_by_card])\\\n#       .groupby('card_id').sum().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e3bd87a62211bff477a81654ad543d3b3245ffec"},"cell_type":"code","source":"#df_card_all_instalment.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"be4b8819db12cca02385b1dd6941b8c7323f323b"},"cell_type":"markdown","source":"**We merge the train dataset with the historical and new merchant transactions data to add purchase amount and installments**"},{"metadata":{"trusted":true,"_uuid":"063f679bf079918fac7b79b5f58cea9286323916"},"cell_type":"code","source":"#train_df_merged = pd.merge(train_df,df_card_all, how = 'inner', left_on = ['card_id'], right_on=['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d3371b402ca55afde734cf05e1a1abd70cbf5645"},"cell_type":"code","source":"#train_df_merged = pd.merge(train_df_merged,df_card_all_instalment, how = 'inner', left_on = ['card_id'], right_on=['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"41a0369c63f060158a528d46f66422208cd70a88"},"cell_type":"code","source":"train_df_merged = pd.merge(train_df,new_merch_trans_max_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a33bad3ec5bbd984b11093fdf893691f230d718d"},"cell_type":"code","source":"train_df_merged = pd.merge(train_df_merged,TransVol_by_CardnewHighVolMerch, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a007786357e250ee97d6844f1cfa8359c8654b60"},"cell_type":"code","source":"train_df_merged = pd.merge(train_df_merged,TransVol_by_CardnewMerchCt, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"82e8ee18c4fba62796246b74e45e6183708ce548"},"cell_type":"code","source":"#train_df_merged = pd.merge(train_df_merged,new_merch_trans_median_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])\n#train_df_merged = pd.merge(train_df_merged,new_merch_trans_std_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"62ba34ec80e7b0e9d1b6c10b7a5cc4b455459d45"},"cell_type":"code","source":"train_df_merged.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0d1722e87a4554904abf54624df8eacd11185be3"},"cell_type":"code","source":"#train_df_merged = pd.merge(train_df_merged,hist_trans_max_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])\n#train_df_merged = pd.merge(train_df_merged,hist_trans_median_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])\n#train_df_merged = pd.merge(train_df_merged,hist_trans_std_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"742c1c7ef36e0e7e322e0085b5cfe7e698dff37f"},"cell_type":"code","source":"train_df_merged.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"511022a4cb8e27e247efe32200142a0a91504fa6"},"cell_type":"code","source":"#train_df_merged = pd.merge(train_df_merged,TransVol_by_CardMerchm, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"827ac2282ef730426d8ffd2a69626c8a0a588f23"},"cell_type":"code","source":"train_df_merged = pd.merge(train_df_merged,hist_df_auth_ct_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"624992db1466a91308ca852a08a2bb072f199f81"},"cell_type":"code","source":"train_df_merged = pd.merge(train_df_merged,hist_df_ref_ct_bycard, how = 'inner', left_on = ['card_id'], right_on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2cb1a69bc8ea9bcbd25563282bbce7bf4d36f986"},"cell_type":"code","source":"train_df_merged.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b650833800a8bb5091b6e80a84ec20ad207eaa1d"},"cell_type":"code","source":"#train_df_merged.to_csv('train_df_merged.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f4b5c849deb78eb8a7b6d95da558af700ca2fca3"},"cell_type":"code","source":"#do some data cleansing to make sure the correlation analysis runs without any error\n#m=train_df_merged[\"NewMerch_StdDev_Amt\"].isnull().any()\n#print(m[m])\n#m=train_df_merged[\"HistMerch_StdDev_Amt\"].isnull().any()\n#print(m[m])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"426921f752e216cbb717364b574a8bc04f80f077"},"cell_type":"code","source":"#train_df_merged[\"NewMerch_StdDev_Amt\"].fillna(train_df_merged[\"NewMerch_StdDev_Amt\"].mean())\n#train_df_merged[\"NewMerch_StdDev_Amt\"] = train_df_merged[\"NewMerch_StdDev_Amt\"].apply(lambda x: x if not pd.isnull(x) else train_df_merged[\"NewMerch_StdDev_Amt\"].mean())\n#m=train_df_merged[\"NewMerch_StdDev_Amt\"].isnull().any()\n#print(m[m])\n#train_df_merged[\"HistMerch_StdDev_Amt\"].fillna(train_df_merged[\"HistMerch_StdDev_Amt\"].mean())\n#train_df_merged[\"HistMerch_StdDev_Amt\"] = train_df_merged[\"HistMerch_StdDev_Amt\"].apply(lambda x: x if not pd.isnull(x) else train_df_merged[\"NewMerch_StdDev_Amt\"].mean())\n#m=train_df_merged[\"HistMerch_StdDev_Amt\"].isnull().any()\n#print(m[m])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2bc7ca4d6dd4a5d2f4fbddb3f291712666566ae0"},"cell_type":"code","source":"#\n#corr, _ = pearsonr(train_df_merged['target'],train_df_merged['installments'])\n#print('Pearsons correlation betweeen target and installments: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be11da32626a43b7dfae0cba1b0d16cd15698379"},"cell_type":"code","source":"#corr, _ = pearsonr(train_df_merged['target'],train_df_merged['purchase_amount'])\n#print('Pearsons correlation betweeen target and sum purchase amount by card id: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bcc8a3b209b007cccc8d521cbae6289c5f619895"},"cell_type":"code","source":"corr, _ = pearsonr(train_df_merged['target'],train_df_merged['NewMerch_Max_Amt'])\nprint('Pearsons correlation betweeen target and New Merch Max puch amt: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0e9d012ef02539883ec77ca466a4db2f1132d4ac"},"cell_type":"code","source":"corr, _ = pearsonr(train_df_merged['target'],train_df_merged['NewMerchTransCt'])\nprint('Pearsons correlation betweeen target and New Merch Count: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"17fd0456eeaf0a7e8a9ed275b54309a00e268ec9"},"cell_type":"code","source":"corr, _ = pearsonr(train_df_merged['target'],train_df_merged['NewHighVolMerchTransCt'])\nprint('Pearsons correlation betweeen target and New Merch Count: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"51169ffec6456568d4ac1d5db90c2e5f91df048b"},"cell_type":"code","source":"corr, _ = pearsonr(train_df_merged['target'],train_df_merged['Auth_count'])\nprint('Pearsons correlation betweeen target and Authorised Trans Count: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3a9f2cab6f3b7995bc2a8b90ac2343bb743e95fc"},"cell_type":"code","source":"corr, _ = pearsonr(train_df_merged['target'],train_df_merged['Ref_count'])\nprint('Pearsons correlation betweeen target and Refused Trans Count: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b9dde4c43c9799ce629dca04c4ccac05c71d3949"},"cell_type":"markdown","source":"**We explore the feature 1 , feature 2 and feature 3 values using bar charts**"},{"metadata":{"trusted":true,"_uuid":"1ec71789f1855232e8c3ca6b24b32590d3720b24"},"cell_type":"code","source":"train_df_merged['feature_1'].value_counts().sort_values().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4d379951958abe4a01ac46b85887e30ceefddbaf"},"cell_type":"code","source":"train_df_merged['feature_2'].value_counts().sort_values().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"98abd5dd1ab1d190aca9a6839a9728be4e7b590f"},"cell_type":"code","source":"train_df_merged['feature_3'].value_counts().sort_values().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4046ee82ba04336a992a8bcac190821bc5bb284b"},"cell_type":"markdown","source":"**We notice these are categorical variables. We now explore correlation between target and the purchase amount and installments**"},{"metadata":{"trusted":true,"_uuid":"40e382f8e7a994a517ff37096eb1710fd42f789d"},"cell_type":"code","source":"train_df_corr = train_df_merged[['NewMerch_Max_Amt','NewMerchTransCt','NewHighVolMerchTransCt','Auth_count','Ref_count','target']]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d19b5ae46c8d52ef44ed2eb4d3f6df63959988a7"},"cell_type":"code","source":"corr=train_df_corr.corr()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9863520f7f7d5f6fcc0d2221fea88218544ca53e"},"cell_type":"code","source":"corr","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0ed94a88f260b12747ea58ea3ee4f2689bd6479"},"cell_type":"code","source":"# Generate a mask for the upper triangle\nmask = np.zeros_like(corr, dtype=np.bool)\nmask[np.triu_indices_from(mask)] = True\n# Set up the matplotlib figure\nf, ax = plt.subplots(figsize=(11, 9))\n# Generate a custom diverging colormap\ncmap = sns.diverging_palette(220, 10, as_cmap=True)\n# Draw the heatmap with the mask and correct aspect ratio\nsns.heatmap(corr, mask=mask, cmap=cmap, vmax=1, center=0,\n            square=True, linewidths=.5, cbar_kws={\"shrink\": .5})\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1222b92ea210ad38814645a75b06d12138d40ea8"},"cell_type":"markdown","source":"**Added NewMerchTransCt and NewHighVolMerchTransCt as two features**"},{"metadata":{"trusted":true,"_uuid":"876299cd959044f3033cec39aee6845f4bbd1f96"},"cell_type":"code","source":"import seaborn as sns\nsns.set(style=\"ticks\")\nsns.pairplot(train_df_merged[['target','feature_1']])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6874488c152dbe726dd90679aa034272093b94ff"},"cell_type":"markdown","source":"**Shows there are few outliers in target (-30), but these occur for all values of feature_1**"},{"metadata":{"trusted":true,"_uuid":"d12e081ce4abba5169c4e27d3fb92a3678081371"},"cell_type":"code","source":"import seaborn as sns\nsns.set(style=\"ticks\")\nsns.pairplot(train_df_merged[['target','feature_2']])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"78948958f3ea8207d9a8bd26d8ca6b6265c67e31"},"cell_type":"markdown","source":"**Feature 2 does not show any correlation with target, the value 3 starts slightly higher, but this can be ignored as there is a huge spread in target value for all three values**"},{"metadata":{"trusted":true,"_uuid":"03987c5cff28eb4c805a8ba5b5a70707ca8e991f"},"cell_type":"code","source":"import seaborn as sns\nsns.set(style=\"ticks\")\nsns.pairplot(train_df_merged[['target','feature_3']])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d2e572c675ee273d461dde70e67bfca5130081df"},"cell_type":"markdown","source":"**Feature 2 also does not show much correlation with target**"},{"metadata":{"trusted":true,"_uuid":"7a7e3d650c5706864e12b5406b25506c5b5d8fec"},"cell_type":"code","source":"train_df_merged['target'].hist()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3f373ed5f00d31349e7b770041c73c17a3929244"},"cell_type":"markdown","source":"**The above shows Outliers in target value, few values at -30, should be ignored**"},{"metadata":{"trusted":true,"_uuid":"a3567554d04a250dd11c6fa127df7999248d7ecb"},"cell_type":"code","source":"ax=plt.hist(train_df_merged['target'],bins=100)\nplt.xlim(-7.5,7.5)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"81bf99e348ba156bdc23896ce1a3cfcc156ba33d"},"cell_type":"code","source":"print(len(train_df_merged.loc[(train_df_merged['target'] < -7)]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"30840684f8d5a98fda4f00c5a93174b30c27b315"},"cell_type":"code","source":"print(len(train_df_merged.loc[(train_df_merged['target'] > 7)]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0228e0b1c3e2b9ec2e3471d2e961a9124b3a43ce"},"cell_type":"code","source":"print(len(train_df_merged))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b5409a4976f84fa5626e8147ca90ad2d419b31c4"},"cell_type":"markdown","source":"**Lets remove the extreme Outliers from target value, keeping it between -7 and +7 and explore the Correlation matrix again**"},{"metadata":{"trusted":true,"_uuid":"aeb62e2124121a911a954ca8cb245686c8a33e19"},"cell_type":"code","source":"train_df_merged = train_df_merged.loc[(train_df_merged['target'] >= -7)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0d9a515fd81c2dc95f2b18345863a6ea29e2412a"},"cell_type":"code","source":"print(len(train_df_merged))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5115883817de7be311cc65d9c950bfc0bb68e9c6"},"cell_type":"code","source":"train_df_merged = train_df_merged.loc[(train_df_merged['target'] <= 7)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"01160f62f4379209052ac7ab2a557a0871ccefb0"},"cell_type":"code","source":"train_df_merged.to_csv('train_df_merged.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aa805486ad38c5161dabcc76f34925f84bde9b82"},"cell_type":"code","source":"print(len(train_df_merged))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8ac068d36829ce3e17c41f4db6240971d337199"},"cell_type":"code","source":"train_df_corr = train_df_merged[['NewMerch_Max_Amt','NewMerchTransCt','NewHighVolMerchTransCt','Auth_count','Ref_count','target']]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"74a4f40b3b1b089ca92ff96f569434c36574062a"},"cell_type":"code","source":"corr=train_df_corr.corr()\ncorr","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0358444cb46d84f078ef0b4cd2957eb114ab91a1"},"cell_type":"code","source":"# Generate a mask for the upper triangle\nmask = np.zeros_like(corr, dtype=np.bool)\nmask[np.triu_indices_from(mask)] = True\n# Set up the matplotlib figure\nf, ax = plt.subplots(figsize=(11, 9))\n# Generate a custom diverging colormap\ncmap = sns.diverging_palette(220, 10, as_cmap=True)\n# Draw the heatmap with the mask and correct aspect ratio\nsns.heatmap(corr, mask=mask, cmap=cmap, vmax=1, center=0,\n            square=True, linewidths=.5, cbar_kws={\"shrink\": .5})\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d7d148913e68628fecb16d03ea3d9d25a254a3f4"},"cell_type":"markdown","source":"**Shows target has a slight negative correlation with NewMerch_Max_ Amt and NewMerchTransCt**"},{"metadata":{"trusted":true,"_uuid":"78e071d5d5bb1c391264d726c5a7769803a21b09"},"cell_type":"code","source":"#merchant_df = pd.read_csv('../input/merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7b5ed8d3bb60b2f92289f153abdfaae0729dad87"},"cell_type":"code","source":"merchant_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0b3549b245fe97ca60f72885da3e7dd4a91dffba"},"cell_type":"code","source":"print(len(merchant_df['merchant_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2d4f43a98aed818458045a23def38f83a2f54d2c"},"cell_type":"code","source":"print(len(merchant_df['merchant_group_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"72872a4f6db4de3dc89188128d72ae60d2297fa7"},"cell_type":"code","source":"print(len(merchant_df['merchant_category_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cb60908364dbe9364d78a41307d02fea46009e7e"},"cell_type":"code","source":"merchant_df['subsector_id'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"59436f99d36565f8be06651ad08dfa1e288a0e65"},"cell_type":"code","source":"merchant_df['category_1'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fb65267a5bacff409dd65c4c8d9509cf93bfbcc0"},"cell_type":"code","source":"merchant_df['most_recent_purchases_range'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0873f7ff1da9fc38ce261ac4de6b3eb70f231176"},"cell_type":"code","source":"merchant_df['most_recent_sales_range'].value_counts()","execution_count":null,"outputs":[]}],"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}