{"cells":[{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":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)\nimport matplotlib 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\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"#df_merchants=pd.read_csv(\"../input/merchants.csv\")\ndf_historical_transactions=pd.read_csv(\"../input/historical_transactions.csv\")\ndf_train=pd.read_csv(\"../input/train.csv\")\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"18116a95bc4cdbae0793ef0d1031a65ed71bf318"},"cell_type":"code","source":"df_test=pd.read_csv(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d0cd61c074c59d11f3f03fcc480143df90a2778d"},"cell_type":"markdown","source":"## **Exploration and building features**"},{"metadata":{"_uuid":"08b28ecfcdf89e4ece977990304406e9851a09c9"},"cell_type":"markdown","source":"### **First Active Months: Month of first purchase. Lets see the trend of how many first purchases from beginning**"},{"metadata":{"_uuid":"84d81af694833a867e8988f1306059743752a3d0","trusted":true},"cell_type":"code","source":"df_train.groupby('first_active_month').count()['card_id'].plot(kind='bar',figsize=(40,15))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a0d370c85709b06a9e8027ae0db89ee570a788bf"},"cell_type":"markdown","source":"This shows number of subscribers increasing. As from wikipedia, Elo was founded in 2010 so this shows trend of subscriber growing from that time. It would be interesting to see how many of old customers are still there performing transactions and whats their loyalty score. We will explore this part later on"},{"metadata":{"_uuid":"fc3b784aa75ccb1a8ebb32859466472985423052"},"cell_type":"markdown","source":"\nLets also validate this distribution using test set"},{"metadata":{"_uuid":"331d48c178a19ebf9e35aac179d66afa02508704","trusted":true},"cell_type":"code","source":"df_test.groupby('first_active_month').count()['card_id'].plot(kind='bar',figsize=(40,15))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b03f0f7adbea41b9d82dbcb1ad14e45e33e3fb3f"},"cell_type":"markdown","source":"Distribution looks similar. "},{"metadata":{"_uuid":"56b9024fdfcae3d4122366f700099bd323d53efd"},"cell_type":"markdown","source":"### **Lets look at target variable**"},{"metadata":{"_uuid":"2e885bd76e04893985188ea4c6ee91be5e69dd46","trusted":true},"cell_type":"code","source":"df_train['target'].describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"58013d0f6ebf2b3f085b58652694893fcade771a","trusted":true},"cell_type":"code","source":"df_train.boxplot(column='target', figsize=(20,20))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"032c4dac111c3b89adb9777d5e9c378c6d4441e7"},"cell_type":"markdown","source":"From this plot we can see that there are extreme outliers as well if we specifically see minimum value. Mean is therefore effected by this outlier its greater than median. Distribution is skewed from left side.\n. Distribution is tight. Lets also calculate Interquartile(not effected by outliers) to see the range."},{"metadata":{"_uuid":"05885edd3c98e99ba30f6f8abd693f1c5379051e","trusted":true},"cell_type":"code","source":"df_train['target'].describe()['75%'] - df_train['target'].describe()['25%']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"66c40c87d96b3a4c86e89a815226f167675311c1"},"cell_type":"markdown","source":"So most lies in this range\n"},{"metadata":{"_uuid":"68776f2046d92da4f93a311c08642b6194cbd73c"},"cell_type":"markdown","source":"**Lets also relate first active month with target **"},{"metadata":{"_uuid":"2a3c7c32565d1da903aa458b249ddc5ede74694b","trusted":true},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"da84b2393b93ef6814262c2aa49fd34edfb0d107","trusted":true},"cell_type":"code","source":"df_train.sort_values('first_active_month').groupby(['first_active_month']).mean()['target'].plot(kind='bar',figsize=(20,10))\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0e1c67a441101cc828a2eb2c5c20ebdf9496c3e0"},"cell_type":"markdown","source":"Why lowest score for customers whose first active month is april 2012?? "},{"metadata":{"_uuid":"91934a3ce3f8dfed5e4469b62a81915dc070bbec"},"cell_type":"markdown","source":"#### Lets also check how feature(feature-1,feature-2,feature-3) column relates to target"},{"metadata":{"_uuid":"be44bce3bd2c5e7629a26de9d1763d2b90720012","trusted":true},"cell_type":"code","source":"fig, ax = plt.pyplot.subplots()\n# the size of A4 paper\nfig.set_size_inches(11.7, 8.27)\nfor feature in df_train['feature_1'].unique():\n        sns.distplot(df_train[df_train['feature_1']==feature]['target']);","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8858a58bd673d18daa4db8936b4eb6023f74ed68"},"cell_type":"markdown","source":"Distribution looks same for feature-1 column. There are many near 0.\n\nModel hopefully will be able to relate feature variable and target"},{"metadata":{"_uuid":"a66efe7a93924c1e776dbf243f18b8460c9725a2"},"cell_type":"markdown","source":"Violen Plot allows us to compare in a more better way. Lets plot for feature 1, feature 2 and feature 3"},{"metadata":{"_uuid":"86570d29ebad7c60f5657fbd4312dcd5b940505a","trusted":true},"cell_type":"code","source":"fig, ax = plt.pyplot.subplots()\n# the size of A4 paper\nfig.set_size_inches(11.7, 8.27)\nsns.violinplot(x=\"feature_1\", y=\"target\",  data=df_train, palette=\"muted\",inner=\"points\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b88157d6c4ee1b42ac983d43ac2f40e82fdb6c34","trusted":true},"cell_type":"code","source":"fig, ax = plt.pyplot.subplots()\n# the size of A4 paper\nfig.set_size_inches(11.7, 8.27)\nsns.violinplot(x=\"feature_2\", y=\"target\",  data=df_train, palette=\"muted\",inner=\"points\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"27e44643a578fe97a11c65ed6f89a7bd5f6847a0","trusted":true},"cell_type":"code","source":"fig, ax = plt.pyplot.subplots()\n# the size of A4 paper\nfig.set_size_inches(11.7, 8.27)\nsns.violinplot(x=\"feature_3\", y=\"target\",  data=df_train, palette=\"muted\",inner=\"points\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cf64ef3685d6ffe59e13e03357292d3b9676073b"},"cell_type":"markdown","source":"These all distribution looks similar. Probability of target being near 0 is higher so Model should be able to find relationship here. We will keep this feature"},{"metadata":{"_uuid":"d22eac4d45e34622d32c7863ec1a7477be1d0057"},"cell_type":"markdown","source":"## **Historical Transactions**"},{"metadata":{"_uuid":"a1275b654043e9435d70962dfe5bde9afe3ed30b"},"cell_type":"markdown","source":"##### Lets explore and relate historical transaction with target variable"},{"metadata":{"_uuid":"a717dcd393c62a6e5155ff3b808adb468160f662","trusted":true},"cell_type":"code","source":"df_historical_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"60f4f4f2c91d669387b90bd647a6e54076575f49","trusted":true},"cell_type":"code","source":"print(\"min purchase date\", df_historical_transactions['purchase_date'].min())\nprint(\"max purchase date\", df_historical_transactions['purchase_date'].max())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"af846a53df5b321e5408af159644453365583980"},"cell_type":"markdown","source":"We will use this later to calculate dormancy"},{"metadata":{"trusted":true,"_uuid":"31fb29b73c40ddca10da7661886eb7e8d393bf61"},"cell_type":"code","source":"df_historical_transactions['purchase_date'] = pd.to_datetime(df_historical_transactions['purchase_date'])\nmax_purchase_date=df_historical_transactions['purchase_date'].max()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dbaebf1f1215702df0e41c30c040c29e88dd600d"},"cell_type":"markdown","source":"There are transactions ranging from 2017 to 2018. Lets now check # of transactions and relate with loyalty of customer"},{"metadata":{"_uuid":"a342b7c058f39e76fed428529c5f62b8534ba3b6","trusted":true},"cell_type":"code","source":"g=df_historical_transactions[['card_id']].groupby('card_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"accb328116c999646b6e1c6f5fa78ba18fb5d992"},"cell_type":"code","source":"df_transaction_counts=g.size().reset_index(name='num_transactions')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"01e672c3fbbe3822ed791f97a3e8b8b18ce96cd2"},"cell_type":"markdown","source":"### **Include num_transactions as feature**"},{"metadata":{"_uuid":"e06f18afb7c8b7d04d4b542df9b51d15fa103235","trusted":true},"cell_type":"code","source":"#df_transaction_counts.head()\ndf_train=pd.merge(df_train,df_transaction_counts, on=\"card_id\",how='left')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5434fc84f8de5f5061fbb691969c8c49e8890a42"},"cell_type":"code","source":"df_test=pd.merge(df_test,df_transaction_counts, on=\"card_id\",how='left')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bfa3f71fc87cf8d5a873a8618eaa8eda566c620b","trusted":true},"cell_type":"code","source":"df_train['num_transactions'].describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1aec6c81dc63df58f9b9c625d9303bce1b259aa9"},"cell_type":"markdown","source":"### **Lets bin num transactions and plot to see the relationship with loyalty score**"},{"metadata":{"_uuid":"ee6e9ad8ad9bdaa491b53bec661e7645564d4785","trusted":true},"cell_type":"code","source":"bins=[0,500,1000,1500,2000,2500]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"89962b7436c75c2350211ba65215a9ae93a06a90","trusted":true},"cell_type":"code","source":"df_train['binned_numtransactions']=pd.cut(df_train['num_transactions'],bins)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6894f087f9fff308d5704582c709b38a51ab4872","trusted":true},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"49df8668c1a649a1d504b46762ed42bc21bd8433","trusted":true},"cell_type":"code","source":"fig, ax = plt.pyplot.subplots()\n# the size of A4 paper\nfig.set_size_inches(11.7, 8.27)\nsns.boxplot(x='binned_numtransactions',y='target',data=df_train)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"64af5be43442de0156fa28bfa8972b37284cceee"},"cell_type":"markdown","source":"It seems that some of lower transaction counts has lower loyalty score as can be seen in above distribution. There are  extreme values from negative side for lower transaction counts. Model will be able to find some relationship here"},{"metadata":{"_uuid":"de0260662d84160bd3887bc7eb52e2b3f9df7833"},"cell_type":"markdown","source":"### **Lets now see the trend of purchase date and purchase amount**"},{"metadata":{"_uuid":"03110590453c6612b1d49e1fb51291ce93d78ef0"},"cell_type":"markdown","source":"##### Convert to date and make column YearMonth_Purchase on historical transaction"},{"metadata":{"_uuid":"7916aee5012e4c704873c67bd5307c38ad8d7caa","trusted":true},"cell_type":"code","source":"#this takes more memory (not needed now)\n#df_historical_transactions['YearMonth_Purchase'] = df_historical_transactions['purchase_date'].map(lambda x: 100*x.year + x.month)\n\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6411458e2e38f4462cb956df1dd8aff7101c82e0","trusted":true},"cell_type":"code","source":"#You can plot and check this , there is peak in one particular month, skipping this as it takes memory\n#g=df_historical_transactions[['YearMonth_Purchase','purchase_amount']].groupby('YearMonth_Purchase').mean()\n#g.plot(kind='bar',figsize=(20,10))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2f00543035fc55433a95383236faf6e25734a7f3"},"cell_type":"markdown","source":"Interesting. There is peak in one particular Month: April 2017 . We keep this for later part."},{"metadata":{"_uuid":"97c032931b99c42e50d1e64beab5242b7e008e34"},"cell_type":"markdown","source":"### **Top 20 Merchants with higher purchase amount**"},{"metadata":{"_uuid":"2df5a9ab2084b29990050ff4e9fbb932768af2c7","trusted":true},"cell_type":"code","source":"top_merchants_by_purchaseamount=df_historical_transactions[['merchant_id','purchase_amount']].groupby(by='merchant_id').mean().sort_values(by='purchase_amount',ascending=False).head(20)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c2eb322f1a27ae036da82ce3321d47d6e15f0b10","trusted":true},"cell_type":"code","source":"top_merchants_by_purchaseamount.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2c1df24a005a655c4a9c81fd47e72f67e566d385"},"cell_type":"markdown","source":"Skipping this part, you can check top 20 merchants by month year basis"},{"metadata":{"_uuid":"b877b94a795e5f4385ea51f215f4f289a69875c2","trusted":true},"cell_type":"code","source":" \n#g=df_historical_transactions[df_historical_transactions['merchant_id'].isin(list(top_merchants_by_purchaseamount.index))][['merchant_id','YearMonth_Purchase','purchase_amount']].groupby(['YearMonth_Purchase','merchant_id']).mean()\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7b3740bf7ad4a2e83cab7fa9ade23481deb3a652","trusted":true},"cell_type":"code","source":"#g.unstack()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7a137a554b770810c50dccc6d7918d81ffd57a72"},"cell_type":"markdown","source":"Description based on above output which is commented. \n\nI wanted to create line chart on it but there are many NaN so decided not to create it. However there is one interesting thing to note. We have noticed peak in April 2018 (previous graph) , if we see purchases for merchant with id: M_ID_ee49262ab5, this is influencing a lot. We keep this for later part"},{"metadata":{"_uuid":"e4a1b62d4bfa80c7da0506a318c459425a372f5e"},"cell_type":"markdown","source":"### **Lets add column on training data :favourite merchant, favourite merchant transaction count **"},{"metadata":{"_uuid":"5b5714ba26994ca6f182a268e40b88207d8cabfc","trusted":true},"cell_type":"code","source":"g=df_historical_transactions[['card_id','merchant_id']].groupby(['card_id','merchant_id'])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8be6cddcf1f6d932f9c8603ec5f64d579cf62fb1"},"cell_type":"code","source":"merchantid_counts_percard=g.size()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4b2fcc21da3685e2d347388b4bf8f44eace73ecf"},"cell_type":"code","source":"merchantid_counts_percard=pd.DataFrame(merchantid_counts_percard)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8054193619a67c2c6780af237f634b64dfbf96f"},"cell_type":"code","source":"merchantid_counts_percard.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be25ad6f993795163e0c077274a054888d286637"},"cell_type":"code","source":"merchantid_counts_percard.columns=['num_favourite_merchant']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f86181baf31a0481245a6efc0a4ccb401b3c1109"},"cell_type":"code","source":"merchantid_counts_percard.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bbbb0e57e9f536d90bbfb653fb19f3a636ef0f16"},"cell_type":"code","source":"merchantid_counts_percard=merchantid_counts_percard.sort_values(by='num_favourite_merchant',ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"83f9569c1825a687d4b26f3bad7df158b57fcb66"},"cell_type":"code","source":"merchantid_counts_percard=merchantid_counts_percard.groupby(level=0).head(1).reset_index()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"48315053b10535a0c4e1d9dd72857cd1bf56a5d7"},"cell_type":"code","source":"merchantid_counts_percard.columns=['card_id','favourite_merchant','num_transaction_favourite_merchant']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9e312a3c2c653bf18ec652784a8f1c0c25581d40"},"cell_type":"markdown","source":"### **add Favourite merchant and Favourite merchant count**"},{"metadata":{"trusted":true,"_uuid":"92062f6cb754a2b28beaa88491f7bfbd5e70e906"},"cell_type":"code","source":"\ndf_train=pd.merge( df_train,merchantid_counts_percard,on=\"card_id\",how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2426c4c80a54d2d6d2e2d6b0edc7d5d1e2923e49"},"cell_type":"code","source":"df_test=pd.merge( df_test,merchantid_counts_percard,on=\"card_id\",how='left')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3d2e04fc5c2fbf55d99f4f24b3bfafdef50f488d"},"cell_type":"markdown","source":"### **aggregate Purchase amount and add sum,mean, max, min as feature**"},{"metadata":{"trusted":true,"_uuid":"b233f21be8732d14d5ae32fa5aca909135b5fdcf"},"cell_type":"code","source":"df_historical_transactions['purchase_amount'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aa1fb468e1a68da719f9ae7ce6c2f6ffa7f5572e"},"cell_type":"code","source":"g=df_historical_transactions[['card_id','purchase_amount']].groupby('card_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f1401dc2cb45623462641f5282e297345ce43dc4"},"cell_type":"code","source":"purchaseamount_agg=g.agg(['sum', 'min','max','std','median','mean'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9059592960258dd5d9393736db9734b4e287dd8b"},"cell_type":"code","source":"purchaseamount_agg=purchaseamount_agg.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"67cf7ac7b5d63f848ec3f5e9ae5ec0c174ef5f66"},"cell_type":"code","source":"purchaseamount_agg.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d144bc95892c8adbd07bbf16468ad88c2b5d4d4a"},"cell_type":"code","source":"df_train=pd.merge( df_train,purchaseamount_agg,on=\"card_id\",how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"070b39464c3c723084172a985d71ee493cd66800"},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1c15ef570d0dff8a1b69e36064acfd6c813d5ca3"},"cell_type":"code","source":"df_test=pd.merge( df_test,purchaseamount_agg,on=\"card_id\",how='left')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"572fcbbe5617be7b569273016d7771fd77bcfd1c"},"cell_type":"markdown","source":"### **Transform first_active_month and get year and month**"},{"metadata":{"trusted":true,"_uuid":"d99fce070bdd9fc4ae3190db509eb10d535d80c2"},"cell_type":"code","source":"df_train[\"first_active_month\"]=pd.to_datetime(df_train[\"first_active_month\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a5dac27817efaaaaf31cdb4852993b58866f2e48"},"cell_type":"code","source":"df_test[\"first_active_month\"]=pd.to_datetime(df_test[\"first_active_month\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9cbe8dd5188081354a8deca90fa30bd978cbaeda"},"cell_type":"code","source":"df_train[\"first_active_yr\"]=df_train[\"first_active_month\"].dt.year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b3ce5edf404e8744209c6450ab4c5a929d2d0026"},"cell_type":"code","source":"df_test[\"first_active_yr\"]=df_test[\"first_active_month\"].dt.year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"657222c27b3ca42b5af3d6a96a428f5e12acbc14"},"cell_type":"code","source":"df_train[\"first_active_mon\"]=df_train[\"first_active_month\"].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eed1572e1180953aece5fbab9af9067bca6b439e"},"cell_type":"code","source":"df_test[\"first_active_mon\"]=df_test[\"first_active_month\"].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b795f31ae5531784b8be422cc4c2f43c75e68e37"},"cell_type":"code","source":"len(df_train['favourite_merchant'].unique())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"81dca0cd2c89fb00c1de399ef5856bd83e485bd8"},"cell_type":"markdown","source":"### **Encode Favourite merchant column**"},{"metadata":{"trusted":true,"_uuid":"874ca76b760c95a83aa456ebd9505c5f09edd674"},"cell_type":"code","source":"from sklearn import preprocessing\nle = preprocessing.LabelEncoder()\ndf_train['favourite_merchant'] = le.fit_transform(df_train['favourite_merchant'] )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a3a75c97a64d748f2de807f1f10b8ad7a2ddc802"},"cell_type":"code","source":"le = preprocessing.LabelEncoder()\ndf_test['favourite_merchant'] = le.fit_transform(df_test['favourite_merchant'] )","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c21ceea50dfdc5e1ffac6c824d429173120b1e3f"},"cell_type":"markdown","source":"### **Last Active Month**"},{"metadata":{"trusted":true,"_uuid":"9a3156f864ae1bdd37fe6c79f63f72c7701dc25f"},"cell_type":"code","source":"last_active_month=df_historical_transactions.loc[df_historical_transactions.groupby('card_id').purchase_date.idxmax(),:][['card_id','purchase_date','purchase_amount']]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ba85bc5bd03cb48cfab4f1fcdf24f5d129d69a6e"},"cell_type":"code","source":"last_active_month.columns=['card_id','last_active_purchase_date','last_active_purchase_amount']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0cad52c032972ff9a5b97b3be17d56d2fb97ddb1"},"cell_type":"code","source":"last_active_month.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cb842b234dd5281648308dc87b189e53cde19a8d"},"cell_type":"code","source":"df_train=pd.merge(df_train,last_active_month, on=\"card_id\",how='left')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4be64c4df9eba6f8519686a293811e36d88b100"},"cell_type":"code","source":"df_test=pd.merge(df_test,last_active_month, on=\"card_id\",how='left')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e58dd8c4ce07be8fb5d28bfe10e24a74437a554b"},"cell_type":"code","source":"df_train['last_active_purchase_year']=df_train['last_active_purchase_date'].dt.year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c64a594d47882b54fbc4cfde1fa27a8558002c69"},"cell_type":"code","source":"df_train['last_active_purchase_month']=df_train['last_active_purchase_date'].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5b76b87f222314c04d4ffef4834f647a1dad53e1"},"cell_type":"code","source":"df_train['last_active_purchase_day']=df_train['last_active_purchase_date'].dt.day","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef26bd92eebdba003d44275b82a490e80f1aa1a3"},"cell_type":"code","source":"df_test['last_active_purchase_year']=df_test['last_active_purchase_date'].dt.year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3d90f217aa2df60913d8bbf4562bfee9f3cc82e9"},"cell_type":"code","source":"df_test['last_active_purchase_month']=df_test['last_active_purchase_date'].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2b2e24b4feba68456b8f97cdd8465794b2db0041"},"cell_type":"code","source":"df_test['last_active_purchase_day']=df_test['last_active_purchase_date'].dt.day","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aff78066b38b4755a7a2e25d3ff133a7f12205c8"},"cell_type":"markdown","source":"### **Add Dormancy**"},{"metadata":{"trusted":true,"_uuid":"3d3065715d2608a0a3fc19ba2d362acb625d6df1"},"cell_type":"code","source":"max_purchase_date","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5d6818998c9e8413a14133a7e5fc0c0e5dd564a9"},"cell_type":"code","source":"\ndf_train['dormancy']=[(max_purchase_date-x).days for x in df_train['last_active_purchase_date']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5a122cf0d3345e24653cec444a0fb410d25840eb"},"cell_type":"code","source":"df_test['dormancy']=[(max_purchase_date-x).days for x in df_test['last_active_purchase_date']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"00658e4c7c7cb104273254dc109b8052e3ce5367"},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b946df8edc8701f372b5f472b4718a9c2ef58028"},"cell_type":"code","source":"df_test.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aee2d25d62f3e254fe652b90abe911f46550d461"},"cell_type":"markdown","source":"## **Training Model**"},{"metadata":{"trusted":true,"_uuid":"35b9bd35590f50ab49a7b995e67b4d5f8ce2c520"},"cell_type":"code","source":"\n\nfrom sklearn.model_selection import KFold\nfrom sklearn.metrics import mean_squared_error\nimport lightgbm as lgb","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bbd122cb74375ceaaba3631c6d53cf30d1c802a5"},"cell_type":"code","source":"df_train.columns=['first_active_month','card_id','feature_1','feature_2','feature_3','target','num_transactions',\n                  'binned_numtransactions','favourite_merchant','num_transaction_favourite_merchant',\n                  'sum_purchase_amount','min_purchase_amount','max_purchase_amount',\n                  'std_purchase_amount','median_purchase_amount','mean_purchase_amount',\n                  'first_active_yr','first_active_mon','last_active_purchase_date',\n       'last_active_purchase_amount', 'last_active_purchase_year','last_active_purchase_month', \n                  'last_active_purchase_day', 'dormancy']\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b52f15b2da03580114e5dc13821d19125ea80c4e"},"cell_type":"code","source":"df_test.columns=['first_active_month','card_id','feature_1','feature_2','feature_3',\n                 'num_transactions','favourite_merchant','num_transaction_favourite_merchant',\n                 'sum_purchase_amount','min_purchase_amount','max_purchase_amount',\n                 'std_purchase_amount','median_purchase_amount','mean_purchase_amount','first_active_yr','first_active_mon','last_active_purchase_date',\n            'last_active_purchase_amount', 'last_active_purchase_year',\n           'last_active_purchase_month', 'last_active_purchase_day', 'dormancy']\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"718674da7544322d4e2683c79003ac18ad0f152a"},"cell_type":"code","source":"df_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c2524a04ff8deed5baeaf9cc386c788922742c86"},"cell_type":"code","source":"final_cols=['feature_1','feature_2','feature_3','num_transactions','favourite_merchant','num_transaction_favourite_merchant','sum_purchase_amount',\n            'min_purchase_amount','max_purchase_amount','std_purchase_amount','median_purchase_amount','mean_purchase_amount',\n            'first_active_yr','first_active_mon','last_active_purchase_amount', 'last_active_purchase_year',\n           'last_active_purchase_month', 'last_active_purchase_day', 'dormancy']\ntarget_col=['target']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0e0f345c2f43c9b38fa3d66803c69718e1fe27d8"},"cell_type":"code","source":"\nlgb_params = {\n        \"objective\" : \"regression\",\n        \"metric\" : \"rmse\",\n        \"num_leaves\" : 30,\n        \"min_child_weight\" : 50,\n        \"learning_rate\" : 0.05,\n        \"bagging_fraction\" : 0.7,\n        \"feature_fraction\" : 0.7,\n        \"bagging_frequency\" : 5,\n        \"bagging_seed\" : 2018,\n        \"verbosity\" : -1\n    }","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"88ab11c79aaa84cfc1ebed1f201f76c6875f9742"},"cell_type":"markdown","source":"### **Using 7 K Fold cross validation**"},{"metadata":{"trusted":true,"_uuid":"792a5298fe9bee22a337dbea894957dee5fe84fe"},"cell_type":"code","source":"Folds = KFold(n_splits=7, shuffle=True, random_state=1989)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9068a1af10dddfb1bea3b949be790fcbbf683ad5"},"cell_type":"code","source":"\npred_train = np.zeros(len(df_train))\npred_test = np.zeros(len(df_test))\n\nfeatures_lgb = list(df_train.columns)\nfeature_importance = pd.DataFrame()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e91a3396ca5bfd7708ef0b54ad92da8adb4c0527"},"cell_type":"markdown","source":"### **Prepare Data (X and Y for training)**"},{"metadata":{"trusted":true,"_uuid":"8f7e8c303ce93f4709733c5c516e5f7a6c76faa6"},"cell_type":"code","source":"train_X=df_train[final_cols]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1362c443280c31b0c21a753755a879cd48c2933a"},"cell_type":"code","source":"train_y=df_train[target_col]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be09d2a4282261cf0d9f4458f1a15291ac3394fe"},"cell_type":"code","source":"test_X=df_test[final_cols]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ebb10c464b370a17f0f292434a34bf081eab1ba8"},"cell_type":"code","source":"for fold_, (train_idx, val_idx) in enumerate(Folds.split(df_train)):\n    train_data = lgb.Dataset(train_X.iloc[train_idx], label=train_y.iloc[train_idx])\n    val_data = lgb.Dataset(train_X.iloc[val_idx], label=train_y.iloc[val_idx])\n\n    num_round = 10000\n    model = lgb.train(lgb_params, train_data, num_round, valid_sets = [train_data, val_data], verbose_eval=1000, early_stopping_rounds = 100)\n    pred_train[val_idx] = model.predict(train_X.iloc[val_idx], num_iteration=model.best_iteration)\n\n    pred_test += model.predict(test_X, num_iteration=model.best_iteration) / Folds.n_splits\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1a5a2480a6aa2a0edfa4def1aaca4fe021d83a64"},"cell_type":"code","source":"\nprint(np.sqrt(mean_squared_error(pred_train, df_train[target_col])))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4bac3ce86be7d8393850a6f9dd685f73a152de30"},"cell_type":"code","source":"submit_df = pd.read_csv('../input/sample_submission.csv')\nsubmit_df[\"target\"] = pred_test\nsubmit_df.to_csv(\"submission_baseline_lgb.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b38c54e7fa61bbf6cefedb66033383887509f3fa"},"cell_type":"code","source":"submit_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"97b20ad33d749ad80ab2177b6bea5cdfc178c152"},"cell_type":"code","source":"fig, ax = plt.pyplot.subplots(figsize=(12,10))\nlgb.plot_importance(model, max_num_features=50, height=0.8, ax=ax)\nax.grid(False)\nplt.pyplot.title(\"LGB- Feature Importance\", fontsize=15)\n","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}