{"cells":[{"metadata":{"_uuid":"3de46720298ccb3d2fe5c5d0fea5a930df37ede4"},"cell_type":"markdown","source":"**This notebook does detailed predictive modeling with Grid search of parameters, deep Learning and checks RMSE and R2 score on Train data for comparison**"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","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)\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.linear_model import LinearRegression\nfrom sklearn.model_selection import StratifiedShuffleSplit\nfrom numpy import percentile\nimport matplotlib.pyplot as plt\nfrom sklearn.preprocessing import LabelBinarizer\nfrom sklearn.preprocessing import MinMaxScaler\nfrom sklearn.model_selection import GridSearchCV\nfrom sklearn.metrics import r2_score\nfrom sklearn.ensemble import RandomForestRegressor\nfrom sklearn.tree import DecisionTreeRegressor\nfrom sklearn.ensemble import GradientBoostingRegressor\nfrom keras.models import Sequential\nfrom keras.layers import Dense   \nfrom keras import optimizers\nimport tensorflow as tf\nfrom xgboost import XGBRegressor\nfrom sklearn.linear_model import ElasticNet\nfrom sklearn.decomposition import PCA\nfrom sklearn.linear_model import Ridge\nfrom sklearn.linear_model import Lasso\n\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":{"trusted":true,"_uuid":"176c1475306f1ee11013cf75755addfc3a60ebdd"},"cell_type":"code","source":"train_df_stratMerchCatlt2 = pd.read_csv(\"../input/exploring-relationships-merch-cat-stratified/train_df_strat_MerchCat_lt2.csv\")\ntrain_df_stratMerchCatgt2 = pd.read_csv(\"../input/exploring-relationships-merch-cat-stratified/train_df_strat_MerchCat_gt2.csv\")\ntrain_df_stratMerchCat0_2 = pd.read_csv(\"../input/exploring-relationships-merch-cat-stratified/train_df_strat_MerchCat_0_2.csv\")\ntrain_df_stratMerchCat2_0 = pd.read_csv(\"../input/exploring-relationships-merch-cat-stratified/train_df_strat_MerchCat_2_0.csv\")\ntrain_df_stratMerchCatlt15 = pd.read_csv(\"../input/exploring-relationships-merch-cat-stratified/train_df_strat_MerchCat_lt15.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7e8d6b30f5f3ec6bb9f1ecea34f7030f7358fce9"},"cell_type":"code","source":"train_df_stratMerchGrplt2 = pd.read_csv(\"../input/fork-of-exploring-relationships-merch-grp-strat/train_df_strat_MerchGrp_lt2.csv\")\ntrain_df_stratMerchGrpgt2 = pd.read_csv(\"../input/fork-of-exploring-relationships-merch-grp-strat/train_df_strat_MerchGrp_gt2.csv\")\ntrain_df_stratMerchGrp0_2 = pd.read_csv(\"../input/fork-of-exploring-relationships-merch-grp-strat/train_df_strat_MerchGrp_0_2.csv\")\ntrain_df_stratMerchGrp2_0 = pd.read_csv(\"../input/fork-of-exploring-relationships-merch-grp-strat/train_df_strat_MerchGrp_2_0.csv\")\ntrain_df_stratMerchGrplt15 = pd.read_csv(\"../input/fork-of-exploring-relationships-merch-grp-strat/train_df_strat_MerchGrp_lt15.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e66b8ed1ba0cfcc61d0dfc0641edeb32413155d7"},"cell_type":"code","source":"train_df_TransP995=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatP99.5'].sum().reset_index()\ntrain_df_AmtP995=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatP99.5'].sum().reset_index()\ntrain_df_TransP99=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatP99'].sum().reset_index()\ntrain_df_AmtP99=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatP99'].sum().reset_index()\ntrain_df_TransP98=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatP98'].sum().reset_index()\ntrain_df_AmtP98=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatP98'].sum().reset_index()\ntrain_df_TransP95=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatP95'].sum().reset_index()\ntrain_df_AmtP95=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatP95'].sum().reset_index()\ntrain_df_TransP90=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatP90'].sum().reset_index()\ntrain_df_AmtP90=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatP90'].sum().reset_index()\ntrain_df_TransP75=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatP75'].sum().reset_index()\ntrain_df_AmtP75=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatP75'].sum().reset_index()\ntrain_df_TransP50=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatP50'].sum().reset_index()\ntrain_df_AmtP50=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatP50'].sum().reset_index()\ntrain_df_TransPlt50=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['TransCtbyMerchCatPlt50'].sum().reset_index()\ntrain_df_AmtPlt50=pd.concat([train_df_stratMerchCatlt2,train_df_stratMerchCatgt2,train_df_stratMerchCat0_2,train_df_stratMerchCat2_0,train_df_stratMerchCatlt15]).groupby(['card_id'])['AmtbyMerchCatPlt50'].sum().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8111ed6d799fb187fd689967ff223bf6dcb2d9b1"},"cell_type":"code","source":"train_df_AmtGrpP98=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['AmtbyMerchGrpP98'].sum().reset_index()\ntrain_df_AmtGrpP95=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['AmtbyMerchGrpP95'].sum().reset_index()\ntrain_df_AmtGrpP90=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['AmtbyMerchGrpP90'].sum().reset_index()\ntrain_df_AmtGrpP75=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['AmtbyMerchGrpP75'].sum().reset_index()\ntrain_df_AmtGrpP50=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['AmtbyMerchGrpP50'].sum().reset_index()\ntrain_df_AmtGrpPlt50=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['AmtbyMerchGrpPlt50'].sum().reset_index()\ntrain_df_TransGrpP98=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['TransCtbyMerchGrpP98'].sum().reset_index()\ntrain_df_TransGrpP95=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['TransCtbyMerchGrpP95'].sum().reset_index()\ntrain_df_TransGrpP90=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['TransCtbyMerchGrpP90'].sum().reset_index()\ntrain_df_TransGrpP75=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['TransCtbyMerchGrpP75'].sum().reset_index()\ntrain_df_TransGrpP50=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['TransCtbyMerchGrpP50'].sum().reset_index()\ntrain_df_TransGrpPlt50=pd.concat([train_df_stratMerchGrplt2,train_df_stratMerchGrpgt2,train_df_stratMerchGrp0_2,train_df_stratMerchGrp2_0,train_df_stratMerchGrplt15]).groupby(['card_id'])['TransCtbyMerchGrpPlt50'].sum().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4b2a7e6f037c612d81d5ba3de3dc6c69f7f82d4e"},"cell_type":"code","source":"train_df=pd.read_csv('../input/elo-merchant-category-recommendation/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"304d91266974ac63857145365b0ff988252c0ba4"},"cell_type":"code","source":"train_df_merged = train_df.merge(train_df_TransP995, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtP995, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransP99, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtP99, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransP98, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtP98, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransP95, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtP95, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransP90, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtP90, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransP75, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtP75, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransP50, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtP50, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransPlt50, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtPlt50, how='left', on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fb3f420f5f86556e4d4e2292cc8521f30ff5f806"},"cell_type":"code","source":"train_df_merged = train_df_merged.merge(train_df_AmtGrpP98, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtGrpP95, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtGrpP90, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtGrpP75, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtGrpP50, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_AmtGrpPlt50, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransGrpP98, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransGrpP95, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransGrpP90, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransGrpP75, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransGrpP50, how='left', on = ['card_id'])\ntrain_df_merged = train_df_merged.merge(train_df_TransGrpPlt50, how='left', on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d0853caceed8cdf3d9ba15b46548eb6f883eea26"},"cell_type":"code","source":"len(train_df_merged)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fe5cd47a15df571a303c4c9887d5388ffffbf0d8"},"cell_type":"code","source":"train_df_comb = pd.read_csv('../input/exploring-relationships-summing-attribs/train_df_comb.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b6867ad37dbed9637006768c72a1ee54ba3d7052"},"cell_type":"code","source":"train_df_merged = train_df_merged.merge(train_df_comb[['card_id','month_lag','purchase_amount','age_of_card','installments','avg_sales_lag3','avg_purchases_lag3','active_months_lag3','avg_sales_lag6','avg_purchases_lag6','active_months_lag6','avg_sales_lag12','avg_purchases_lag12','active_months_lag12']], how='left', on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cd1788e94fa131945c13c6cce566cd8f72f06df6"},"cell_type":"code","source":"train_df_merged.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aeb7ad0486fedfc3e8bc0c982baff3b4bd742e5d"},"cell_type":"code","source":"test_df_merged = pd.read_csv(\"../input/exploring-relationships-merch-cat-stratified/test_df_strat_MerchCat.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cb7382bba9d702f4ad209deac2a50a183b1c379a"},"cell_type":"code","source":"test_df_merged_MerchGrp = pd.read_csv(\"../input/fork-of-exploring-relationships-merch-grp-strat/test_df_strat_MerchGrp.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"026c34e59f6be03aa0c792b47c1dad04528c0c5d"},"cell_type":"code","source":"test_df_comb = pd.read_csv('../input/exploring-relationships-summing-attribs/test_df_comb.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fff33b88969fc565c2ad95feddde05090372d3d4"},"cell_type":"code","source":"test_df_merged = test_df_merged.merge(test_df_comb[['card_id','month_lag','purchase_amount','age_of_card','installments','avg_sales_lag3','avg_purchases_lag3','active_months_lag3','avg_sales_lag6','avg_purchases_lag6','active_months_lag6','avg_sales_lag12','avg_purchases_lag12','active_months_lag12']], how='left', on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8634063ce8b0cb28c4893598631f5043e0915e31"},"cell_type":"code","source":"test_df_merged = test_df_merged.merge(test_df_merged_MerchGrp[['card_id','AmtbyMerchGrpP98','AmtbyMerchGrpP95','AmtbyMerchGrpP90','AmtbyMerchGrpP75','AmtbyMerchGrpP50','AmtbyMerchGrpPlt50','TransCtbyMerchGrpP98','TransCtbyMerchGrpP95','TransCtbyMerchGrpP90','TransCtbyMerchGrpP75','TransCtbyMerchGrpP50','TransCtbyMerchGrpPlt50']], how='left', on = ['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dcdebba331f72e9a6d8dcd66a8cc276a949e0731"},"cell_type":"code","source":"print(len(test_df_merged))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a05c95f698d54f219c9999c71aba547e1cf2a7d6"},"cell_type":"code","source":"test_df_merged.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b0bbf3529f654e96ba920e2bb83a650dedeafcc7"},"cell_type":"code","source":"def extract_features(df):\n    df['log_avg_sales_lag3']=np.log(1+df['avg_sales_lag3']).round(2)\n    df['log_avg_purchases_lag3']=np.log(1+df['avg_purchases_lag3']).round(2)\n    df['log_avg_sales_lag6']=np.log(1+df['avg_sales_lag6']).round(2)\n    df['log_avg_purchases_lag6']=np.log(1+df['avg_purchases_lag6']).round(2)\n    df['log_avg_sales_lag12']=np.log(1+df['avg_sales_lag12']).round(2)\n    df['log_avg_purchases_lag12']=np.log(1+df['avg_purchases_lag12']).round(2)\n    #df['TransCtMerchCat']=np.log((1+train_df_merged['TransCtbyMerchCatP99.5']*train_df_merged['TransCtbyMerchCatP99']*train_df_merged['TransCtbyMerchCatP98']*train_df_merged['TransCtbyMerchCatP95']*train_df_merged['TransCtbyMerchCatP90']*train_df_merged['TransCtbyMerchCatP75']*train_df_merged['TransCtbyMerchCatP50']*train_df_merged['TransCtbyMerchCatPlt50'])).round(2)\n    #df['TransCtMerchGrp']=np.log((1+train_df_merged['TransCtbyMerchGrpP98']*train_df_merged['TransCtbyMerchGrpP95']*train_df_merged['TransCtbyMerchGrpP90']*train_df_merged['TransCtbyMerchGrpP75']*train_df_merged['TransCtbyMerchGrpP50']*train_df_merged['TransCtbyMerchGrpPlt50'])).round(2)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"51f5b4316cc3bee8fe231614415206be3c1bf90b"},"cell_type":"code","source":"train_df_merged[train_df_merged==np.inf]=np.nan\n#train_df_merged.fillna(0, inplace=True)\ntrain_df_merged['avg_sales_lag6'].fillna(train_df_merged['avg_sales_lag6'].max(), inplace=True)\ntrain_df_merged['avg_purchases_lag6'].fillna(train_df_merged['avg_purchases_lag6'].max(), inplace=True)\ntrain_df_merged['avg_sales_lag12'].fillna(train_df_merged['avg_sales_lag12'].max(), inplace=True)\ntrain_df_merged['avg_purchases_lag12'].fillna(train_df_merged['avg_purchases_lag12'].max(), inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c99768f64210ba6f961bf7b6e82285750b6d3ab6"},"cell_type":"code","source":"test_df_merged[test_df_merged==np.inf]=np.nan\n#test_df_merged.fillna(0, inplace=True)\ntest_df_merged['avg_sales_lag6'].fillna(test_df_merged['avg_sales_lag6'].max(), inplace=True)\ntest_df_merged['avg_purchases_lag6'].fillna(test_df_merged['avg_purchases_lag6'].max(), inplace=True)\ntest_df_merged['avg_sales_lag12'].fillna(test_df_merged['avg_sales_lag12'].max(), inplace=True)\ntest_df_merged['avg_purchases_lag12'].fillna(test_df_merged['avg_purchases_lag12'].max(), inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f05e1833e8c18e0c5644583df757171b18fa7f5e"},"cell_type":"code","source":"train_df_merged = extract_features(train_df_merged)\ntest_df_merged = extract_features(test_df_merged)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0572cab172a8072ca7fa02738978f3660493cc30"},"cell_type":"code","source":"from numpy import percentile\ndata = train_df_merged['age_of_card']\npercentiles = percentile(data,[25,50,75,90])\nprint(percentiles.round(2))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"906cdba0879163e0799ebb79b66d8b5b7fc75f36"},"cell_type":"code","source":"train_df_merged.loc[(train_df_merged.age_of_card >=780)  , 'Age_cat'] = 1\ntrain_df_merged.loc[(train_df_merged.age_of_card >=513) & (train_df_merged.age_of_card < 780)  , 'Age_cat'] = 2\ntrain_df_merged.loc[(train_df_merged.age_of_card >=321) & (train_df_merged.age_of_card < 513)  , 'Age_cat'] = 3\ntrain_df_merged.loc[(train_df_merged.age_of_card >=206) & (train_df_merged.age_of_card < 321) , 'Age_cat'] = 4\ntrain_df_merged.loc[(train_df_merged.age_of_card <206)  , 'Age_cat'] = 5","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4dbc2bd0a2d1147b01a08867576027c28d21e02f"},"cell_type":"code","source":"train_df_merged.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1bcf9df3835dc7e2ee9f7b29f682cc4944c24bfa"},"cell_type":"code","source":"train_df_merged[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95', 'TransCtbyMerchCatP90', 'TransCtbyMerchCatP75','TransCtbyMerchCatP50', 'TransCtbyMerchCatPlt50','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95', 'AmtbyMerchCatP90', 'AmtbyMerchCatP75',\n       'AmtbyMerchCatP50', 'AmtbyMerchCatPlt50','purchase_amount','age_of_card','active_months_lag3','active_months_lag6','active_months_lag12','installments','month_lag','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'AmtbyMerchGrpP75','AmtbyMerchGrpP50', 'AmtbyMerchGrpPlt50','TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'TransCtbyMerchGrpP75','TransCtbyMerchGrpP50', 'TransCtbyMerchGrpPlt50']]=train_df_merged[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95', 'TransCtbyMerchCatP90', 'TransCtbyMerchCatP75','TransCtbyMerchCatP50', 'TransCtbyMerchCatPlt50','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95', 'AmtbyMerchCatP90', 'AmtbyMerchCatP75',\n       'AmtbyMerchCatP50', 'AmtbyMerchCatPlt50','purchase_amount','age_of_card','active_months_lag3','active_months_lag6','active_months_lag12','installments','month_lag','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'AmtbyMerchGrpP75','AmtbyMerchGrpP50', 'AmtbyMerchGrpPlt50','TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'TransCtbyMerchGrpP75','TransCtbyMerchGrpP50', 'TransCtbyMerchGrpPlt50']].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d76df3e64e3866218227237ceb28fdf709b64f1d"},"cell_type":"code","source":"test_df_merged[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95', 'TransCtbyMerchCatP90', 'TransCtbyMerchCatP75','TransCtbyMerchCatP50', 'TransCtbyMerchCatPlt50','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95', 'AmtbyMerchCatP90', 'AmtbyMerchCatP75',\n       'AmtbyMerchCatP50', 'AmtbyMerchCatPlt50','purchase_amount','age_of_card','active_months_lag3','active_months_lag6','active_months_lag12','installments','month_lag','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'AmtbyMerchGrpP75','AmtbyMerchGrpP50', 'AmtbyMerchGrpPlt50','TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'TransCtbyMerchGrpP75','TransCtbyMerchGrpP50', 'TransCtbyMerchGrpPlt50']]=test_df_merged[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95', 'TransCtbyMerchCatP90', 'TransCtbyMerchCatP75','TransCtbyMerchCatP50', 'TransCtbyMerchCatPlt50','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95', 'AmtbyMerchCatP90', 'AmtbyMerchCatP75',\n       'AmtbyMerchCatP50', 'AmtbyMerchCatPlt50','purchase_amount','age_of_card','active_months_lag3','active_months_lag6','active_months_lag12','installments','month_lag','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'AmtbyMerchGrpP75','AmtbyMerchGrpP50', 'AmtbyMerchGrpPlt50','TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'TransCtbyMerchGrpP75','TransCtbyMerchGrpP50', 'TransCtbyMerchGrpPlt50']].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e6442a7f24e93edf95c244ef09ef0cddaeb6b5a8"},"cell_type":"code","source":"#from sklearn.model_selection import train_test_split\n#x_train, x_test, y_train, y_test = train_test_split(X, y, test_size=0.10, random_state=0)\nsplit = StratifiedShuffleSplit(n_splits=1,test_size=0.1,random_state=42)\n\nfor train_index, test_index in split.split(train_df_merged,train_df_merged[\"Age_cat\"]):\n    strat_train_set = train_df_merged.iloc[train_index]\n    strat_test_set = train_df_merged.iloc[test_index]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"04af567773202489100e8624a4d7b89951116add"},"cell_type":"code","source":"#'TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95', 'TransCtbyMerchCatP90', 'TransCtbyMerchCatP75','TransCtbyMerchCatP50', 'TransCtbyMerchCatPlt50','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95', 'AmtbyMerchCatP90', 'AmtbyMerchCatP75','AmtbyMerchCatP50', 'AmtbyMerchCatPlt50','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'AmtbyMerchGrpP75','AmtbyMerchGrpP50', 'AmtbyMerchGrpPlt50','TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'TransCtbyMerchGrpP75','TransCtbyMerchGrpP50', 'TransCtbyMerchGrpPlt50','month_lag','installments','log_avg_sales_lag3','log_avg_purchases_lag3','log_avg_sales_lag6','log_avg_purchases_lag6','log_avg_sales_lag12','log_avg_purchases_lag12','active_months_lag3','active_months_lag6','active_months_lag12','purchase_amount','age_of_card'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"60c1c9c4e2936ddb33ce1bf9a749b03f6a030cbc"},"cell_type":"code","source":"x_train = strat_train_set[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'month_lag','installments','log_avg_sales_lag3','log_avg_purchases_lag3','log_avg_sales_lag6','log_avg_purchases_lag6','active_months_lag3','active_months_lag6','purchase_amount','age_of_card']]\ny_train = strat_train_set[['target']]\n\nx_test = strat_test_set[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'month_lag','installments','log_avg_sales_lag3','log_avg_purchases_lag3','log_avg_sales_lag6','log_avg_purchases_lag6','active_months_lag3','active_months_lag6','purchase_amount','age_of_card']]\ny_test = strat_test_set[['target']]\n\nx = train_df_merged[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'month_lag','installments','log_avg_sales_lag3','log_avg_purchases_lag3','log_avg_sales_lag6','log_avg_purchases_lag6','active_months_lag3','active_months_lag6','purchase_amount','age_of_card']]\ny = train_df_merged[['target']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"35444e760edd71e5b47fcb7acda0b6c2c55499cb"},"cell_type":"code","source":"from scipy.stats import pearsonr\nX_corr = train_df_merged[['AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'AmtbyMerchGrpP75','AmtbyMerchGrpP50', 'AmtbyMerchGrpPlt50','target']]\ncorr=X_corr.corr()\ncorr","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5568805b062f067086bad955d5b87576d104fab5"},"cell_type":"code","source":"import seaborn as sns\nimport matplotlib.pyplot as plt\n# 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":{"trusted":true,"_uuid":"52fdeaa3f01cb7824a82eb5209ce70f636466a52"},"cell_type":"code","source":"train_df_merged['derived'] = np.log(1+train_df_merged['TransCtbyMerchCatP99.5']*train_df_merged['TransCtbyMerchCatP99']*train_df_merged['TransCtbyMerchCatP98'])\nprint(train_df_merged['derived'].max())\ncorr, _ = pearsonr(train_df_merged['target'],train_df_merged['derived'])\nprint('Pearsons correlation betweeen target and Amt: %.3f' % corr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5dd0de19a60254dfc0f3e1f09a3ac286634efff8"},"cell_type":"code","source":"train_df_merged[\"feature_3\"].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19b05de67468750cffee26fbe49319fc7b878ba9"},"cell_type":"code","source":"def gen_cat_feature(df,X):\n    test_feat_1 = df[\"feature_1\"]\n    encoder_feat_1 = LabelBinarizer()\n    encoder_feat_1.fit(test_feat_1)\n    test_feat_1_1hot = encoder_feat_1.transform(test_feat_1)\n    X = np.concatenate((X,test_feat_1_1hot),axis=1)\n\n    test_feat_2 = df[\"feature_2\"]\n    encoder_feat_2 = LabelBinarizer()\n    encoder_feat_2.fit(test_feat_2)\n    test_feat_2_1hot = encoder_feat_2.transform(test_feat_2)\n    X = np.concatenate((X,test_feat_2_1hot),axis=1)\n\n    test_feat_3 = df[\"feature_3\"]\n    encoder_feat_3 = LabelBinarizer()\n    encoder_feat_3.fit(test_feat_3)\n    test_feat_3_1hot = encoder_feat_3.transform(test_feat_3)\n    X = np.concatenate((X,test_feat_3_1hot),axis=1)\n    return X","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e25ee637c718b683dae0c27c2da10cfd0e21f231"},"cell_type":"code","source":"#print(x_train.shape)\n#x_train = gen_cat_feature(strat_train_set,x_train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3b21cae53b506497f0d820939f4752322afb7b9c"},"cell_type":"code","source":"#x = gen_cat_feature(train_df_merged,x)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c0da4b14979c0f472a23f80cc23b2582d7ebfef9"},"cell_type":"code","source":"#x_test = gen_cat_feature(strat_test_set,x_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4ce82a3c1c17c5f192d240ab9c00d6d52f1cee8d"},"cell_type":"code","source":"print(x_train.shape)\nprint(x_test.shape)\nprint(x.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"334c02b68fac39de0fb0ee361153c7b819ec4d80"},"cell_type":"code","source":"x_train = np.array(x_train)\nx_train = np.delete(x_train,[20,21],1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc323ca46b8a90a6d26994d6719d3d237be2561d"},"cell_type":"code","source":"#x_test = np.delete(x_test,[19,36,37,41,42,43,44,45,46,47,48,49],1)\n#x_test = np.delete(x_test,[10,19,22,23,25],1)\nx_test = np.array(x_test)\nx_test = np.delete(x_test,[20,21],1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"997ebaeca917f6367e176fb8a9ea1588631789a2"},"cell_type":"code","source":"print(x_train.shape)\nprint(x_test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8dd00d93ae6b1a8d11db883038845ca441926010"},"cell_type":"code","source":"#x = np.delete(x,[19,36,37,41,42,43,44,45,46,47,48,49],1)\n#x = np.delete(x,[10,19,22,23,25],1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"926fdbd797be9f567774fde6cb6fc97da4aedc73"},"cell_type":"code","source":"model = XGBRegressor(n_estimators=250,max_depth=5) \nmodel.fit(x_train, y_train)\n#model.fit(x, y)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f018a9734c81b8b9a73414438712bfa2968adabf"},"cell_type":"code","source":"y_pred = model.predict(x_train)\nmse = mean_squared_error(y_train, y_pred)\nrmse = np.sqrt(mse)\nprint(\"RMSE on train data:\"+str(rmse))\ny_pred = model.predict(x_test)\nmse = mean_squared_error(y_test, y_pred)\nrmse = np.sqrt(mse)\nprint(\"RMSE on test data:\"+str(rmse))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4387785f69c18369f1bfeed79bc9c3ed3eccdf58"},"cell_type":"code","source":"print(model.feature_importances_)\n# plot\nplt.bar(range(len(model.feature_importances_)), model.feature_importances_)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"62d1fe87ebe735d0448362d0268c24b07fabcc47"},"cell_type":"code","source":"test_X =  test_df_merged[['TransCtbyMerchCatP99.5','TransCtbyMerchCatP99','TransCtbyMerchCatP98','TransCtbyMerchCatP95','AmtbyMerchCatP99.5','AmtbyMerchCatP99','AmtbyMerchCatP98','AmtbyMerchCatP95','AmtbyMerchGrpP98','AmtbyMerchGrpP95', 'AmtbyMerchGrpP90', 'TransCtbyMerchGrpP98','TransCtbyMerchGrpP95', 'TransCtbyMerchGrpP90', 'month_lag','installments','log_avg_sales_lag3','log_avg_purchases_lag3','log_avg_sales_lag6','log_avg_purchases_lag6','active_months_lag3','active_months_lag6','purchase_amount','age_of_card']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"12069acc347f1641ecce4e1da34d14a9c5da6598"},"cell_type":"code","source":"#test_X = gen_cat_feature(test_df_merged,test_X)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9d16b29e6d8dcdd9954e93a764f3c16514be8363"},"cell_type":"code","source":"#test_X = np.delete(test_X,[19,36,37,41,42,43,44,45,46,47,48,49],1)\ntest_X = np.array(test_X)\ntest_X = np.delete(test_X,[20,21],1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f66dade5bbf738fad4db64bb9177b9b2d5a12e27"},"cell_type":"code","source":"print(test_X.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"56d23decba1664b7049ae3ae34b768aac349ea2a"},"cell_type":"code","source":"predictions = model.predict(test_X)\npredictions = pd.DataFrame(predictions)\npredictions = predictions.rename(columns={0: 'target'})\nsub_df = pd.concat([test_df_merged['card_id'],predictions],axis=1)\nprint(len(sub_df))\nsub_df.to_csv('submission.csv',index=False)","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}