{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"

Elo EDA Prediction

\n\n\n\n# Content\n\n- Introduction \n- Prepare the data analysis \n - Load packages \n - Load the data \n- Data exploration \n - Check for missing data \n - Train and test data \n - Historical transaction data \n - New merchant transaction data \n - Merchant data \n- Feature engineering\n- Model\n- Submission\n- References \n "},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"# Introduction \n\nThis Kernel will take you through the process of analyzing the data to understand the predictive values of various features and the possible correlation between different features, selection of features with predictive value, features engineering to create features with higher predictive value and creation of a baseline model."},{"metadata":{"_uuid":"38a5f2a44c34431374570a6287b80d3573881a71"},"cell_type":"markdown","source":"# Prepare the data analysis \n\n\nBefore starting the analysis, we need to make few preparation: load the packages, load and inspect the data.\n"},{"metadata":{"_uuid":"561e8b71d9dfe7b5e8ef8f9b6fee869be56bdad9"},"cell_type":"markdown","source":"## Load packages\n\nWe load the packages used for the analysis."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"17f3d237beb164bad42338dd1f08ded596123df9"},"cell_type":"code","source":"import os\nimport gc\nimport sys\nimport random\nimport logging\nimport datetime\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\nimport lightgbm as lgb\nfrom plotly import tools\nfrom pathlib import Path\nimport plotly.graph_objs as go\nimport matplotlib.pyplot as plt\nimport plotly.figure_factory as ff\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import StratifiedKFold\nfrom plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\ninit_notebook_mode(connected=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"96de067794b0707377d85a99aa5ad76224cfa105"},"cell_type":"markdown","source":"Go to top \n\n\n## Load the data \n\nLet's see first what data files do we have in the root directory. "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"ebbcca3e3e8186c99389c7f01aea84f3b2a5d5ca"},"cell_type":"code","source":"IS_LOCAL = False\nif(IS_LOCAL):\n PATH=\"../input/elo/\"\nelse:\n PATH=\"../input/\"\nos.listdir(PATH)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"716dd3ff9e8b778932d03b4d47ebe76192d6c247"},"cell_type":"markdown","source":"Let's load the data files."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"441fef4438d031c632842309f4a992289af1c0e6"},"cell_type":"code","source":"train_df=pd.read_csv(PATH+'train.csv')\ntest_df=pd.read_csv(PATH+'test.csv')\nhistorical_trans_df=pd.read_csv(PATH+'historical_transactions.csv')\nnew_merchant_trans_df=pd.read_csv(PATH+'new_merchant_transactions.csv')\nmerchant_df=pd.read_csv(PATH+'merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5f3b1bb3a6208a9f7f69b16ba5275d09388fdcec"},"cell_type":"markdown","source":"Go to top \n\n# Data exploration \n\nLet's check the dataframes created."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a05fd0241ad89f9700eaf7f39f8dd3e19c9f4d49"},"cell_type":"code","source":"print(\"Train: rows:{} cols:{}\".format(train_df.shape[0], train_df.shape[1]))\nprint(\"Test: rows:{} cols:{}\".format(test_df.shape[0], test_df.shape[1]))\nprint(\"Historical trans: rows:{} cols:{}\".format(historical_trans_df.shape[0], historical_trans_df.shape[1]))\nprint(\"New merchant trans: rows:{} cols:{}\".format(new_merchant_trans_df.shape[0], new_merchant_trans_df.shape[1]))\nprint(\"Merchants: rows:{} cols:{}\".format(merchant_df.shape[0], merchant_df.shape[1]))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c725612e04cb6700a1a182bd0fd1c552f731ee11"},"cell_type":"code","source":"train_df.sample(3).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6acc5e0b5c4098edf800cbdc4784b18f73872765"},"cell_type":"code","source":"test_df.sample(3).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"13fcedf55a5cbb8a3dc77fe621e5ab7093ddbfbd"},"cell_type":"code","source":"historical_trans_df.sample(3).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"950365b3aaa12b6dde503d73af2bd26cb63b613c"},"cell_type":"code","source":"new_merchant_trans_df.sample(3).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d13835e528d108d4a625c14a296aeee507378e8d"},"cell_type":"code","source":"merchant_df.sample(3).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"729db19af4fc2be3a9e9d0b2177c81d310446266"},"cell_type":"markdown","source":"Let's start by checking if there are missing data, unlabeled data or data that is inconsistently labeled. \n\n## Check for missing data \n\nLet's create a function that check for missing data in the dataframes."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c6bc56f2dd7de3fedcf28c0291bfa1c0777c74a4"},"cell_type":"code","source":"def missing_data(data):\n total = data.isnull().sum().sort_values(ascending = False)\n percent = (data.isnull().sum()/data.isnull().count()*100).sort_values(ascending = False)\n return pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c57fbde399677bca37914d7ad7bad4d63292fe09"},"cell_type":"markdown","source":"Let's check missing data for all dataframes."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"0a0fe406dece7cb4efa276cb2bf1e99d2f39dec3"},"cell_type":"code","source":"missing_data(train_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bd4106adb5551bbd39e0207e2361da5735770d34"},"cell_type":"code","source":"missing_data(test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6e1837262dd5d1b9ad0571bb8f0fab2d3786e730"},"cell_type":"code","source":"missing_data(historical_trans_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d21a73d8508fc3acba3300320df69f6cd03270dc"},"cell_type":"code","source":"missing_data(new_merchant_trans_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e315b1d7f152a384a5b4a545216793963cfd7b65"},"cell_type":"code","source":"missing_data(merchant_df)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"41d16cad746152ae8f2d2773e559aa37c854e460"},"cell_type":"markdown","source":"## Train and test data \n\nLet's check the distribution of train and test features.\n\nBoth have the same features:\n* card_id; \n* feature1, feature2, feature3; \n* first_active_month; \n\nTrain has also the target value, called **target**. \n\nLet's define few auxiliary functions.\n"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c69adb4246dbea8f01a57ca92b6d62717d18a6af"},"cell_type":"code","source":"def get_categories(data, val):\n tmp = data[val].value_counts()\n return pd.DataFrame(data={'Number': tmp.values}, index=tmp.index).reset_index()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"0ad675066e0fbc53017c3b8969d83104b83c806f"},"cell_type":"code","source":"def get_target_categories(data, val):\n tmp = data.groupby('target')[val].value_counts()\n return pd.DataFrame(data={'Number': tmp.values}, index=tmp.index).reset_index()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"bb5c7fdc600d53da01e220adb8f6f82c3c76971b"},"cell_type":"code","source":"def draw_trace_bar(data_df,color='Blue'):\n trace = go.Bar(\n x = data_df['index'],\n y = data_df['Number'],\n marker=dict(color=color),\n text=data_df['index']\n )\n return trace\n\ndef draw_trace_histogram(data_df,target,color='Blue'):\n trace = go.Histogram(\n y = data_df[target],\n marker=dict(color=color)\n )\n return trace","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a9cbcb262a3bf9d807a33fa776fe9420588d38f2"},"cell_type":"code","source":"def plot_bar(data_df, title, xlab, ylab,color='Blue'):\n trace = draw_trace_bar(data_df, color)\n data = [trace]\n layout = dict(title = title,\n xaxis = dict(title = xlab, showticklabels=True, tickangle=0,\n tickfont=dict(\n size=10,\n color='black'),), \n yaxis = dict(title = ylab),\n hovermode = 'closest'\n )\n fig = dict(data = data, layout = layout)\n iplot(fig, filename='draw_trace')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b441c86124cbf48fb4109078398d59aa99bbe4b5"},"cell_type":"code","source":"def plot_two_bar(data_df1, data_df2, title1, title2, xlab, ylab):\n trace1 = draw_trace_bar(data_df1, color='Blue')\n trace2 = draw_trace_bar(data_df2, color='Lightblue')\n \n fig = tools.make_subplots(rows=1,cols=2, subplot_titles=(title1,title2))\n fig.append_trace(trace1,1,1)\n fig.append_trace(trace2,1,2)\n \n fig['layout']['xaxis'].update(title = xlab)\n fig['layout']['xaxis2'].update(title = xlab)\n fig['layout']['yaxis'].update(title = ylab)\n fig['layout']['yaxis2'].update(title = ylab)\n fig['layout'].update(showlegend=False)\n \n iplot(fig, filename='draw_trace')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"df95a142589697cdbcb21936f26d35d889e2ff0f"},"cell_type":"code","source":"def plot_target_distribution(var):\n hist_data = []\n varall = list(train_df.groupby([var])[var].nunique().index)\n for i, varcrt in enumerate(varall):\n classcrt = train_df[train_df[var] == varcrt]['target']\n hist_data.append(classcrt)\n fig = ff.create_distplot(hist_data, varall, show_hist=False, show_rug=False)\n fig['layout'].update(title='Target variable density plot group by {}'.format(var), xaxis=dict(title='Target'))\n iplot(fig, filename='dist_only')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a2e30bfa92abafbefbf417675c7f1aa69057a16b"},"cell_type":"markdown","source":"Let's show the distribution of **feature_1** for **train** and **test** set."},{"metadata":{"trusted":true,"_uuid":"d45ba46a0f5223e5826a9b0f6f219129f21f19e5"},"cell_type":"code","source":"plot_two_bar(get_categories(train_df,'feature_1'), get_categories(test_df,'feature_1'), \n 'Train data', 'Test data',\n 'Feature 1', 'Number of records')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_uuid":"310fac968f1e86612dfc729379af75f131247508"},"cell_type":"markdown","source":"Let's see the distribution of **target** value groped on **feature_1** values."},{"metadata":{"trusted":true,"_uuid":"f8a652355956ba3c7a2d41e53e913caace4b0fda"},"cell_type":"code","source":"plot_target_distribution('feature_1')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a17662971c231273b43e486cc9356e195f661e6c"},"cell_type":"markdown","source":"Let's see the distribution of **feature_2** for **train** and **test** set."},{"metadata":{"trusted":true,"_uuid":"52022d765bb8066d56a47518274274e09cfe2188","_kg_hide-input":true},"cell_type":"code","source":"plot_two_bar(get_categories(train_df,'feature_2'), get_categories(test_df,'feature_2'), \n 'Train data', 'Test data',\n 'Feature 2', 'Number of records')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8d9b6035584d0072f5b1fcee66ccdf991cc75412"},"cell_type":"markdown","source":"Let's see the distribution of **target** grouped on **feature_2** values."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d33aa3cbd572057e8cfe9427bfa2d9f9e02c4720"},"cell_type":"code","source":"plot_target_distribution('feature_2')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9ff2513533cc0afcfb45e169bc4919bef09926e9"},"cell_type":"markdown","source":"Let's show now the distribution of **feature_3** for **train** and **test**."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"daafda1b5a146570927c6010282303e9e2ae5a19"},"cell_type":"code","source":"plot_two_bar(get_categories(train_df,'feature_3'), get_categories(test_df,'feature_3'), \n 'Train data', 'Test data',\n 'Feature 3', 'Number of records')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"04dfcb5b1010aa98c4395eab570da445aa8c8dde"},"cell_type":"markdown","source":"And let's see also the distribuiton of **target** grouped by values of **feature_3**."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"baa6fef8becc2cd9e83007db7a81fbd82f8b52fe"},"cell_type":"code","source":"plot_target_distribution('feature_3')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c950ca7f89e03b91e69afac686955b709861ace2"},"cell_type":"markdown","source":"Let's plot now the distribution of **first_active_month** from **train** and **test** datasets."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1f06b801e688312d2b64c9d0a09ed4b85fe830e2"},"cell_type":"code","source":"plot_two_bar(get_categories(train_df,'first_active_month'), get_categories(test_df,'first_active_month'), \n 'Train data', 'Test data',\n 'First active month', 'Number of records')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0853713b68ad1529573a77c42186abd606a023d8"},"cell_type":"markdown","source":"## Historical transaction data \n\nLet's check the distribution of historical transaction data features. \n\n**historical_trans_df** is linked with **train_df** and **test_df** by the **card_id** key.\n\nLet's plot **category_1**, **category_2**, **category_3** features distribution.\n"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e68fc39fd44ccdee873d0d0eb8c7f82d675fa5b3"},"cell_type":"code","source":"plot_bar(get_categories(historical_trans_df,'category_1'), \n 'Category 1 distribution', 'Category 1', 'Number of records')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d1b807ae518ec802723ed8ebf9a50fea34870fcc"},"cell_type":"code","source":"plot_bar(get_categories(historical_trans_df,'category_2'), \n 'Category 2 distribution', 'Category 2', 'Number of records','red')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"43074f316f9973d863451c3470c85146496e53ea"},"cell_type":"code","source":"plot_bar(get_categories(historical_trans_df,'category_3'), \n 'Category 3 distribution', 'Category 3', 'Number of records','magenta')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d24951e308c791a22320b13b1d670a11197c26a5"},"cell_type":"markdown","source":"Let's see **city_id**, **merchant_category_id**, **state_id**, **subsector_id**."},{"metadata":{"trusted":true,"_uuid":"3380962db4d288c7411aa6d25f29725e41979030"},"cell_type":"code","source":"plot_bar(get_categories(historical_trans_df,'city_id'), \n 'City ID distribution', 'City ID', 'Number of records','lightblue')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8cf2dc1023443c8b6888336fd98a1f38a07c9f00"},"cell_type":"code","source":"plot_bar(get_categories(historical_trans_df,'merchant_category_id'), \n 'Merchant Cateogory ID distribution', 'Merchant Category ID', 'Number of records','lightgreen')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0b9fa8d1a7454baf7c6f2c29a7c577ae1e32da44"},"cell_type":"code","source":"plot_bar(get_categories(historical_trans_df,'state_id'), \n 'State ID distribution', 'State ID', 'Number of records','brown')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"80840995e38ec4b5c78d958a8b7eea9df5d37677"},"cell_type":"code","source":"plot_bar(get_categories(historical_trans_df,'subsector_id'), \n 'Subsector ID distribution', 'Subsector ID', 'Number of records','orange')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4bccb9df4d3bbcd90b99ff66c44898235fde6900"},"cell_type":"markdown","source":"Let's show the purchase amount grouped by purchase time types.\n\nBefore this, let's extract the date."},{"metadata":{"trusted":true,"_uuid":"079c6f916300179992415b67651cb31c642f257f"},"cell_type":"code","source":"historical_trans_df['purchase_date'] = pd.to_datetime(historical_trans_df['purchase_date'])\nhistorical_trans_df['month'] = historical_trans_df['purchase_date'].dt.month\nhistorical_trans_df['dayofweek'] = historical_trans_df['purchase_date'].dt.dayofweek\nhistorical_trans_df['weekofyear'] = historical_trans_df['purchase_date'].dt.weekofyear","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c89acc592bf59f4cbfbec3d856199b5b450cca46"},"cell_type":"code","source":"def plot_scatter_data(data, xtitle, ytitle, title, color='blue'):\n trace = go.Scatter(\n x = data.index,\n y = data.values,\n name=ytitle,\n marker=dict(\n color=color,\n ),\n mode='lines+markers'\n )\n data = [trace]\n layout = dict(title = title,\n xaxis = dict(title = xtitle), yaxis = dict(title = ytitle),\n )\n fig = dict(data=data, layout=layout)\n iplot(fig, filename='lines')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_uuid":"f948269be5f10071c8b5819492d0631677dbc969"},"cell_type":"markdown","source":"Let's plot the amount of purchase per day of week, week of year and month."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b659e567860fb7efe7b2a3ed60c77467c340208d"},"cell_type":"code","source":"count_all = historical_trans_df.groupby('dayofweek')['purchase_amount'].agg(['sum'])\ncount_all.columns = [\"Total\"]\ncount_all = count_all.sort_index()\nplot_scatter_data(count_all['Total'],'Day of week', 'Total','Total sum of purchase per day of week','green')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"71b479ed27f6901dd0a0cb3c07c1814e85ad1c0d","_kg_hide-input":true},"cell_type":"code","source":"count_all = historical_trans_df.groupby('weekofyear')['purchase_amount'].agg(['sum'])\ncount_all.columns = [\"Total\"]\ncount_all = count_all.sort_index()\nplot_scatter_data(count_all['Total'],'Week of year', 'Total','Total sum of purchase per Week of Year','red')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"60fa6dc9bdc1cccbe24f2dc41a670e96431813d6"},"cell_type":"code","source":"count_all = historical_trans_df.groupby('month')['purchase_amount'].agg(['sum'])\ncount_all.columns = [\"Total\"]\ncount_all = count_all.sort_index()\nplot_scatter_data(count_all['Total'],'Month', 'Total','Total sum of purchase per month','blue')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1b351b0405cb0634e6aa04c4a8e2ef1152c74dd5"},"cell_type":"markdown","source":"## New merchant transaction data \n\nLet's check the distribution of new merchant transaction data features. \n\n**new_merchant_trans_df** is linked with **train_df** and **test_df** by the **card_id** key.\n\nLet's plot **category_1**, **category_2**, **category_3** features distribution."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d0c78e890ff2731de349a47ff7866fde8e81305e"},"cell_type":"code","source":"plot_bar(get_categories(new_merchant_trans_df,'category_1'), \n 'Category 1 distribution', 'Category 1', 'Number of records','gold')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"08d3b38b58d073c8f3767fa23010315aad8f9579"},"cell_type":"code","source":"plot_bar(get_categories(new_merchant_trans_df,'category_2'), \n 'Category 2 distribution', 'Category 2', 'Number of records','tomato')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"bd7be4e8c075be90e223c31627899853cb1d491b"},"cell_type":"code","source":"plot_bar(get_categories(new_merchant_trans_df,'category_3'), \n 'Category 3 distribution', 'Category 3', 'Number of records','magenta')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_uuid":"ea737b35626d7fe0e7fd6409df51082b549d2cef"},"cell_type":"markdown","source":"Let's see city_id, merchant_category_id, state_id, subsector_id."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"30dd0a87a1c5cbfca5316852bbdc54a42e531eea"},"cell_type":"code","source":"plot_bar(get_categories(new_merchant_trans_df,'city_id'), \n 'City ID distribution', 'City ID', 'Number of records','brown')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7a3d9fa180b861dd5f02eb507c514f17782f5bf9"},"cell_type":"code","source":"plot_bar(get_categories(new_merchant_trans_df,'merchant_category_id'), \n 'Merchant category ID distribution', 'Merchant category ID', 'Number of records','green')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3013d414b3c06f5413ff095ff7cf47749cf05163"},"cell_type":"code","source":"plot_bar(get_categories(new_merchant_trans_df,'state_id'), \n 'State ID distribution', 'State ID', 'Number of records','darkblue')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5c355cbf0218a7ac60b796becdb6290d7115384e"},"cell_type":"code","source":"plot_bar(get_categories(new_merchant_trans_df,'subsector_id'), \n 'Subsector ID distribution', 'Subsector ID', 'Number of records','darkgreen')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"579f72c464742cd7107076a8f4fed9f527f79685"},"cell_type":"markdown","source":"Let's show the purchase amount grouped by purchase date types."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"ca6637ab28f0d8500035dd5f5b00d96df2ddba03"},"cell_type":"code","source":"new_merchant_trans_df['purchase_date'] = pd.to_datetime(new_merchant_trans_df['purchase_date'])\nnew_merchant_trans_df['month'] = new_merchant_trans_df['purchase_date'].dt.month\nnew_merchant_trans_df['dayofweek'] = new_merchant_trans_df['purchase_date'].dt.dayofweek\nnew_merchant_trans_df['weekofyear'] = new_merchant_trans_df['purchase_date'].dt.weekofyear","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d0fcef7a483d867e77bf0d3d1f2004200306c8ec"},"cell_type":"code","source":"count_all = new_merchant_trans_df.groupby('month')['purchase_amount'].agg(['sum'])\ncount_all.columns = [\"Total\"]\ncount_all = count_all.sort_index()\nplot_scatter_data(count_all['Total'],'Month', 'Total','Total sum of purchase per month','red')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"365261f20ca5a2375925ec7b9fd99e6b69fb813d"},"cell_type":"code","source":"count_all = new_merchant_trans_df.groupby('dayofweek')['purchase_amount'].agg(['sum'])\ncount_all.columns = [\"Total\"]\ncount_all = count_all.sort_index()\nplot_scatter_data(count_all['Total'],'Day of week', 'Total','Total sum of purchase per day of week','magenta')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"abf089ec11a25627c6dfd9f0e83016fc18a67d1b"},"cell_type":"code","source":"count_all = new_merchant_trans_df.groupby('weekofyear')['purchase_amount'].agg(['sum'])\ncount_all.columns = [\"Total\"]\ncount_all = count_all.sort_index()\nplot_scatter_data(count_all['Total'],'Week of year', 'Total','Total sum of purchase per week of year','darkblue')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a8cbda3b2efc4ac3e3ae811d815a4ddecd19cedd"},"cell_type":"markdown","source":"Let's check the distribution of the purchase amount grouped by various features. We will represent log(purchase_amount + 1)."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"2a6ea19ac7d3d2c8911e60e47ed655535ba2bb7b"},"cell_type":"code","source":"def plot_purchase_amount_distribution(data_df, var):\n hist_data = []\n varall = list(data_df.groupby([var])[var].nunique().index)\n for i, varcrt in enumerate(varall):\n classcrt = np.log(data_df[data_df[var] == varcrt]['purchase_amount'] + 1)\n hist_data.append(classcrt)\n fig = ff.create_distplot(hist_data, varall, show_hist=False, show_rug=False)\n fig['layout'].update(title='Purchase amount (log) variable density plot group by {}'.format(var), xaxis=dict(title='log(purchase_amount + 1)'))\n iplot(fig, filename='dist_only')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e90234250b76a1c5cae8389ddaf682becdee3837"},"cell_type":"code","source":"plot_purchase_amount_distribution(new_merchant_trans_df,'category_1')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"02fdc72bdbc435b4e705a49187aca0e936ba686f"},"cell_type":"code","source":"plot_purchase_amount_distribution(new_merchant_trans_df,'category_2')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d73dc51b407c41758941751bc1bdec496d165d1d"},"cell_type":"code","source":"plot_purchase_amount_distribution(new_merchant_trans_df,'category_3')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"9600285e305f71c1ee3d4bf3e68a954cf0fc546b"},"cell_type":"code","source":"plot_purchase_amount_distribution(new_merchant_trans_df,'state_id')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9c5684b8b2404591d716204fcb68deb2a49c4cd8"},"cell_type":"markdown","source":"## Merchant data \n\nLet's check the distribution of merchant data features. \n\nLet's start with **merchant_category_id**, **subsector_id**.\n"},{"metadata":{"trusted":true,"_uuid":"9eb07837fcd2437f618e2a9d04a275aee277d72a"},"cell_type":"code","source":"merchant_df.head(3)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f15b630b88727893f8832faf153fcd471e2d19d0"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'merchant_category_id'), \n 'Merchant category ID distribution', 'Merchant category ID', 'Number of records','darkblue')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a8c6d78a25bfade1acccf3e365c106d891d8beac"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'subsector_id'), \n 'Subsector ID distribution', 'Subsector ID', 'Number of records','blue')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3e4ee05c3ce685d3b2d7cbd2a471ed897ecfade1"},"cell_type":"markdown","source":"Let's follow with **category_1**, **category_2**, **category_4**."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f508f031ca91ab75142fd1865a785562faf5342d"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'category_1'), \n 'Category 1 distribution', 'Category 1', 'Number of records','lightblue')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"ef14812bc74cf22ce60e7dedf96cb517326e9b7f"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'category_2'), \n 'Category 2 distribution', 'Category 2', 'Number of records','lightgreen')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b723af435973fdb9a5db3a9ff6da2e38fad69ea6"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'category_4'), \n 'Category 4 distribution', 'Category 4', 'Number of records','tomato')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"afbae71a03b9f4f2c350ebb3565d9a432426e294"},"cell_type":"markdown","source":"Let's check **most_recent_sales_range** and **most_recent_purchase_range**[](http://)."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1a0e96b39b09e8b84878c1fb621481b1a53c75e9"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'most_recent_sales_range'), \n 'Most recent sales range distribution', 'Most recent sales range', 'Number of records','red')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d8e692d984d118bd540f02241d93b9426750d4bc"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'most_recent_purchases_range'), \n 'Most recent sales purchases distribution', 'Most recent purchases range', 'Number of records','magenta')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"931629625c39facdad569b1db14bb5eec2929485"},"cell_type":"markdown","source":"Let's look to the **city_id**, **state_id**."},{"metadata":{"trusted":true,"_kg_hide-input":true,"_uuid":"46dac2e1cce80f30c8e2dd8c74c338bc860b93bb"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'city_id'), \n 'City ID distribution', 'City ID', 'Number of records','brown')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c050f7331d63ed7dc3fc2618db46e174eb968596"},"cell_type":"code","source":"plot_bar(get_categories(merchant_df,'state_id'), \n 'State ID distribution', 'State ID', 'Number of records','orange')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0d172e6ff282f5103ab9b988a43c1063e08b5d8b"},"cell_type":"markdown","source":"Let's plot distribution of **numerical_1**, **numerical_2**, **avg_sales_lag3**, **avg_sales_lag6**, **avg_sales_lag12**."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"db9c0a3b9cae9b3882b7936d00c44de8eca08f2c"},"cell_type":"code","source":"def plot_distribution(df,feature,color):\n fig, (ax1, ax2) = plt.subplots(ncols=2, figsize=(12,6))\n s = sns.boxplot(ax = ax1, data = df[feature].dropna(),color=color,showfliers=True)\n s.set_title(\"Distribution of %s (with outliers)\" % feature)\n s = sns.boxplot(ax = ax2, data = df[feature].dropna(),color=color,showfliers=False)\n s.set_title(\"Distribution of %s (no outliers)\" % feature)\n plt.show() ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c24b5de68de7293a6f661335690587ebb833fb9c"},"cell_type":"code","source":"plot_distribution(merchant_df, \"numerical_1\", \"blue\")","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f94c19c34c90b5e0d690d63f029f3fe0eb95feee"},"cell_type":"code","source":"plot_distribution(merchant_df, \"numerical_2\", \"green\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"617f24db75c00570c538289ac6792eda24a5c9a1"},"cell_type":"code","source":"plot_distribution(merchant_df, \"avg_sales_lag3\", \"blue\")","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e316d51f5f90d3f3d0594c0556923c7b1b02beab"},"cell_type":"code","source":"plot_distribution(merchant_df, \"avg_sales_lag6\", \"green\")","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"2582f7a4422b9466190667f72b597474acfbeced"},"cell_type":"code","source":"plot_distribution(merchant_df, \"avg_sales_lag12\", \"green\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"04665daf2b717dbc30d2036b1b7926b7c4330651"},"cell_type":"markdown","source":"# Feature engineering \n\n\nBefore creating the model we will prepare the aggregated features.\n"},{"metadata":{"_uuid":"9d4c9295edd6788871f977af9528551284be22c4"},"cell_type":"markdown","source":"## Utility functions and data cleaning"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"653d8c47b64b51110022c385ac37d2b17562e3f1"},"cell_type":"code","source":"def get_logger():\n FORMAT = '[%(levelname)s]%(asctime)s:%(name)s:%(message)s'\n logging.basicConfig(format=FORMAT)\n logger = logging.getLogger('main')\n logger.setLevel(logging.DEBUG)\n return logger","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"eb291f6d6bbe7797a924b187311741051d04061e"},"cell_type":"code","source":"# reduce memory\ndef reduce_mem_usage(df, verbose=True):\n numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n start_mem = df.memory_usage().sum() / 1024**2\n for col in df.columns:\n col_type = df[col].dtypes\n if col_type in numerics:\n c_min = df[col].min()\n c_max = df[col].max()\n if str(col_type)[:3] == 'int':\n if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n df[col] = df[col].astype(np.int8)\n elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n df[col] = df[col].astype(np.int16)\n elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n df[col] = df[col].astype(np.int32)\n elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n df[col] = df[col].astype(np.int64)\n else:\n if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n df[col] = df[col].astype(np.float16)\n elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n df[col] = df[col].astype(np.float32)\n else:\n df[col] = df[col].astype(np.float64)\n\n end_mem = df.memory_usage().sum() / 1024**2\n print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n\n return df","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3eccbdddda511cb4d4d8d86c34ebf41c5b74380b"},"cell_type":"code","source":"logger = get_logger()\n#process NAs\nlogger.info('Start processing NAs')\n#process NA2 for transactions\nfor df in [historical_trans_df, new_merchant_trans_df]:\n df['category_2'].fillna(1.0,inplace=True)\n df['category_3'].fillna('A',inplace=True)\n df['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n df['installments'].replace(-1, np.nan,inplace=True)\n df['installments'].replace(999, np.nan,inplace=True)\n#define function for aggregation\ndef create_new_columns(name,aggs):\n return [name + '_' + k + '_' + agg for k in aggs.keys() for agg in aggs[k]]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5b9667bccdd3b627519206be0e8a21983e3d52f2"},"cell_type":"code","source":"logger.info('process historical and new merchant datasets')\nfor df in [historical_trans_df, new_merchant_trans_df]:\n df['purchase_date'] = pd.to_datetime(df['purchase_date'])\n df['year'] = df['purchase_date'].dt.year\n df['weekofyear'] = df['purchase_date'].dt.weekofyear\n df['month'] = df['purchase_date'].dt.month\n df['dayofweek'] = df['purchase_date'].dt.dayofweek\n df['weekend'] = (df.purchase_date.dt.weekday >=5).astype(int)\n df['hour'] = df['purchase_date'].dt.hour\n df['authorized_flag'] = df['authorized_flag'].map({'Y':1, 'N':0})\n df['category_1'] = df['category_1'].map({'Y':1, 'N':0}) \n df['category_3'] = df['category_3'].map({'A':0, 'B':1, 'C':2}) \n df['month_diff'] = ((datetime.datetime.today() - df['purchase_date']).dt.days)//30\n df['month_diff'] += df['month_lag']\nlogger.info('new features historical and new merchant datasets')\nfor df in [historical_trans_df, new_merchant_trans_df]:\n df['price'] = df['purchase_amount'] / df['installments']\n df['Christmas_Day_2017']=(pd.to_datetime('2017-12-25')-df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n df['Children_day_2017']=(pd.to_datetime('2017-10-12')-df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n df['Black_Friday_2017']=(pd.to_datetime('2017-11-24') - df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n df['Mothers_Day_2018']=(pd.to_datetime('2018-05-13')-df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n df['month_diff'] = ((datetime.datetime.today() - df['purchase_date']).dt.days)//30\n df['month_diff'] += df['month_lag']\n df['duration'] = df['purchase_amount']*df['month_diff']\n df['amount_month_ratio'] = df['purchase_amount']/df['month_diff']\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"80b8b416d135b5f7000f114e9ea183253e5596f6"},"cell_type":"code","source":"logger.info('reduce memory usage for historical trans')\nhistorical_trans_df = reduce_mem_usage(historical_trans_df)\nlogger.info('reduce memory usage for new merchant trans')\nnew_merchant_trans_df = reduce_mem_usage(new_merchant_trans_df)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5925335e85bce20a2ec2d11f018fa2065924657b"},"cell_type":"markdown","source":"## Process historical transaction data"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b70ba1541d72b38f5981bdf087dccec35c177281"},"cell_type":"code","source":"#define aggregations with historical_trans_df\nlogger.info('Aggregate historical trans')\naggs = {}\n\nfor col in ['subsector_id','merchant_id','merchant_category_id', 'state_id', 'city_id']:\n aggs[col] = ['nunique']\nfor col in ['month', 'hour', 'weekofyear', 'dayofweek']:\n aggs[col] = ['nunique', 'mean', 'min', 'max']\n \naggs['purchase_amount'] = ['sum','max','min','mean','var', 'std']\naggs['installments'] = ['sum','max','min','mean','var', 'std']\naggs['purchase_date'] = ['max','min', 'nunique']\naggs['month_lag'] = ['max','min','mean','var','nunique']\naggs['month_diff'] = ['mean', 'min', 'max', 'var','nunique']\naggs['authorized_flag'] = ['sum', 'mean', 'nunique']\naggs['weekend'] = ['sum', 'mean', 'nunique']\naggs['year'] = ['nunique', 'mean']\naggs['category_1'] = ['sum', 'mean', 'min', 'max', 'nunique', 'std']\naggs['category_2'] = ['sum', 'mean', 'min', 'nunique', 'std']\naggs['category_3'] = ['sum', 'mean', 'min', 'nunique', 'std']\naggs['card_id'] = ['size', 'count']\naggs['Christmas_Day_2017'] = ['mean']\naggs['Children_day_2017'] = ['mean']\naggs['Black_Friday_2017'] = ['mean']\naggs['Mothers_Day_2018'] = ['mean']\n\nfor col in ['category_2','category_3']:\n historical_trans_df[col+'_mean'] = historical_trans_df.groupby([col])['purchase_amount'].transform('mean')\n historical_trans_df[col+'_min'] = historical_trans_df.groupby([col])['purchase_amount'].transform('min')\n historical_trans_df[col+'_max'] = historical_trans_df.groupby([col])['purchase_amount'].transform('max')\n historical_trans_df[col+'_sum'] = historical_trans_df.groupby([col])['purchase_amount'].transform('sum')\n historical_trans_df[col+'_std'] = historical_trans_df.groupby([col])['purchase_amount'].transform('std')\n aggs[col+'_mean'] = ['mean'] \n\nnew_columns = create_new_columns('hist',aggs)\nhistorical_trans_group_df = historical_trans_df.groupby('card_id').agg(aggs)\nhistorical_trans_group_df.columns = new_columns\nhistorical_trans_group_df.reset_index(drop=False,inplace=True)\nhistorical_trans_group_df['hist_purchase_date_diff'] = (historical_trans_group_df['hist_purchase_date_max'] - historical_trans_group_df['hist_purchase_date_min']).dt.days\nhistorical_trans_group_df['hist_purchase_date_average'] = historical_trans_group_df['hist_purchase_date_diff']/historical_trans_group_df['hist_card_id_size']\nhistorical_trans_group_df['hist_purchase_date_uptonow'] = (datetime.datetime.today() - historical_trans_group_df['hist_purchase_date_max']).dt.days\nhistorical_trans_group_df['hist_purchase_date_uptomin'] = (datetime.datetime.today() - historical_trans_group_df['hist_purchase_date_min']).dt.days\n\nlogger.info('reduce memory usage for historical trans')\nhistorical_trans_df = reduce_mem_usage(historical_trans_df)\n\nlogger.info('Completed aggregate historical trans')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"db4ef4a4a87ac8c9fb876aadf678caff97980c1a"},"cell_type":"code","source":"#merge with train, test\ntrain_df = train_df.merge(historical_trans_group_df,on='card_id',how='left')\ntest_df = test_df.merge(historical_trans_group_df,on='card_id',how='left')\n#cleanup memory\ndel historical_trans_group_df; gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bd88b7dca43874818efca2ce12288e5ceb1d33b8"},"cell_type":"markdown","source":"## Process new merchant transaction data"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5bf4ba7872bf5cbc0d4ad5314703e3b2a31a89c7"},"cell_type":"code","source":"#define aggregations with new_merchant_trans_df \nlogger.info('Aggregate new merchant trans')\naggs = {}\nfor col in ['subsector_id','merchant_id','merchant_category_id','state_id', 'city_id']:\n aggs[col] = ['nunique']\nfor col in ['month', 'hour', 'weekofyear', 'dayofweek']:\n aggs[col] = ['nunique', 'mean', 'min', 'max']\n\n \naggs['purchase_amount'] = ['sum','max','min','mean','var','std']\naggs['installments'] = ['sum','max','min','mean','var','std']\naggs['purchase_date'] = ['max','min', 'nunique']\naggs['month_lag'] = ['max','min','mean','var', 'nunique']\naggs['month_diff'] = ['mean', 'max', 'min', 'var','nunique']\naggs['weekend'] = ['sum', 'mean', 'nunique']\naggs['year'] = ['nunique', 'mean']\naggs['category_1'] = ['sum', 'mean', 'min', 'nunique']\naggs['category_2'] = ['sum', 'mean', 'min', 'nunique']\naggs['category_3'] = ['sum', 'mean', 'min', 'nunique']\naggs['card_id'] = ['size', 'count']\naggs['Christmas_Day_2017'] = ['mean']\naggs['Children_day_2017'] = ['mean']\naggs['Black_Friday_2017'] = ['mean']\naggs['Mothers_Day_2018'] = ['mean']\n\nfor col in ['category_2','category_3']:\n new_merchant_trans_df[col+'_mean'] = new_merchant_trans_df.groupby([col])['purchase_amount'].transform('mean')\n new_merchant_trans_df[col+'_min'] = new_merchant_trans_df.groupby([col])['purchase_amount'].transform('min')\n new_merchant_trans_df[col+'_max'] = new_merchant_trans_df.groupby([col])['purchase_amount'].transform('max')\n new_merchant_trans_df[col+'_sum'] = new_merchant_trans_df.groupby([col])['purchase_amount'].transform('sum')\n new_merchant_trans_df[col+'_std'] = new_merchant_trans_df.groupby([col])['purchase_amount'].transform('std')\n aggs[col+'_mean'] = ['mean']\n\nnew_columns = create_new_columns('new_hist',aggs)\nnew_merchant_trans_group_df = new_merchant_trans_df.groupby('card_id').agg(aggs)\nnew_merchant_trans_group_df.columns = new_columns\nnew_merchant_trans_group_df.reset_index(drop=False,inplace=True)\nnew_merchant_trans_group_df['new_hist_purchase_date_diff'] = (new_merchant_trans_group_df['new_hist_purchase_date_max'] - new_merchant_trans_group_df['new_hist_purchase_date_min']).dt.days\nnew_merchant_trans_group_df['new_hist_purchase_date_average'] = new_merchant_trans_group_df['new_hist_purchase_date_diff']/new_merchant_trans_group_df['new_hist_card_id_size']\nnew_merchant_trans_group_df['new_hist_purchase_date_uptonow'] = (datetime.datetime.today() - new_merchant_trans_group_df['new_hist_purchase_date_max']).dt.days\nnew_merchant_trans_group_df['new_hist_purchase_date_uptomin'] = (datetime.datetime.today() - new_merchant_trans_group_df['new_hist_purchase_date_min']).dt.days\n\nlogger.info('reduce memory usage for new merchant trans')\nnew_merchant_trans_df = reduce_mem_usage(new_merchant_trans_df)\n\nlogger.info('Completed aggregate new merchant trans')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e84776d76c50d87f98d576007509184578ba5cf1","_kg_hide-input":true},"cell_type":"code","source":"#merge with train, test\ntrain_df = train_df.merge(new_merchant_trans_group_df,on='card_id',how='left')\ntest_df = test_df.merge(new_merchant_trans_group_df,on='card_id',how='left')\n#clean-up memory\ndel new_merchant_trans_group_df; gc.collect()\ndel historical_trans_df; gc.collect()\ndel new_merchant_trans_df; gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f9c0df24d4c9b3ace337f3a7cbd82f608d708879"},"cell_type":"markdown","source":"## Process train and test data"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b1ba60f1aac5103c114d8ed76cdfaf6cd4287933"},"cell_type":"code","source":"#process train\nlogger.info('Process train - outliers')\ntrain_df['outliers'] = 0\ntrain_df.loc[train_df['target'] < -30, 'outliers'] = 1\noutls = train_df['outliers'].value_counts()\nprint(\"Outliers: {}\".format(outls))\nlogger.info('Process train and test')\n## process both train and test\nfor df in [train_df, test_df]:\n df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n df['dayofweek'] = df['first_active_month'].dt.dayofweek\n df['weekofyear'] = df['first_active_month'].dt.weekofyear\n df['dayofyear'] = df['first_active_month'].dt.dayofyear\n df['quarter'] = df['first_active_month'].dt.quarter\n df['is_month_start'] = df['first_active_month'].dt.is_month_start\n df['month'] = df['first_active_month'].dt.month\n df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n df['hist_first_buy'] = (df['hist_purchase_date_min'] - df['first_active_month']).dt.days\n df['hist_last_buy'] = (df['hist_purchase_date_max'] - df['first_active_month']).dt.days\n df['new_hist_first_buy'] = (df['new_hist_purchase_date_min'] - df['first_active_month']).dt.days\n df['new_hist_last_buy'] = (df['new_hist_purchase_date_max'] - df['first_active_month']).dt.days\n for f in ['hist_purchase_date_max','hist_purchase_date_min','new_hist_purchase_date_max',\\\n 'new_hist_purchase_date_min']:\n df[f] = df[f].astype(np.int64) * 1e-9\n df['card_id_total'] = df['new_hist_card_id_size']+df['hist_card_id_size']\n df['card_id_cnt_total'] = df['new_hist_card_id_count']+df['hist_card_id_count']\n df['purchase_amount_total'] = df['new_hist_purchase_amount_sum']+df['hist_purchase_amount_sum']\n df['purchase_amount_mean'] = df['new_hist_purchase_amount_mean']+df['hist_purchase_amount_mean']\n df['purchase_amount_max'] = df['new_hist_purchase_amount_max']+df['hist_purchase_amount_max']\n\n for f in ['feature_1','feature_2','feature_3']:\n order_label = train_df.groupby([f])['outliers'].mean()\n df[f] = df[f].map(order_label)\n\n df['feature_sum'] = df['feature_1'] + df['feature_2'] + df['feature_3']\n df['feature_mean'] = df['feature_sum']/3\n df['feature_max'] = df[['feature_1', 'feature_2', 'feature_3']].max(axis=1)\n df['feature_min'] = df[['feature_1', 'feature_2', 'feature_3']].min(axis=1)\n df['feature_var'] = df[['feature_1', 'feature_2', 'feature_3']].std(axis=1)\n\n \n##\ntrain_columns = [c for c in train_df.columns if c not in ['card_id', 'first_active_month','target','outliers']]\ntarget = train_df['target']\ndel train_df['target']\nlogger.info('Completed process train')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"663a2832c690abf1c68d5dadb344e35fa75a5fab"},"cell_type":"markdown","source":"# Model "},{"metadata":{"trusted":true,"_uuid":"781469e8eeb49bf4da74ea16faa17a1010b883c2"},"cell_type":"code","source":"#model\n##model params\nlogger.info('Prepare model')\nparam = {'num_leaves': 51,\n 'min_data_in_leaf': 35, \n 'objective':'regression',\n 'max_depth': -1,\n 'learning_rate': 0.008,\n \"boosting\": \"gbdt\",\n \"feature_fraction\": 0.85,\n \"bagging_freq\": 1,\n \"bagging_fraction\": 0.82,\n \"bagging_seed\": 42,\n \"metric\": 'rmse',\n \"lambda_l1\": 0.11,\n \"verbosity\": -1,\n \"nthread\": 4,\n \"random_state\": 2019}\n#prepare fit model with cross-validation\nfolds = StratifiedKFold(n_splits=9, shuffle=True, random_state=2019)\noof = np.zeros(len(train_df))\npredictions = np.zeros(len(test_df))\nfeature_importance_df = pd.DataFrame()\n#run model\nlogger.info('Start running model')\nfor fold_, (trn_idx, val_idx) in enumerate(folds.split(train_df,train_df['outliers'].values)):\n strLog = \"Fold {}\".format(fold_)\n print(strLog)\n trn_data = lgb.Dataset(train_df.iloc[trn_idx][train_columns], label=target.iloc[trn_idx])#, categorical_feature=categorical_feats)\n val_data = lgb.Dataset(train_df.iloc[val_idx][train_columns], label=target.iloc[val_idx])#, categorical_feature=categorical_feats)\n\n num_round = 10000\n clf = lgb.train(param, trn_data, num_round, valid_sets = [trn_data, val_data], verbose_eval=200, early_stopping_rounds = 150)\n oof[val_idx] = clf.predict(train_df.iloc[val_idx][train_columns], num_iteration=clf.best_iteration)\n #feature importance\n fold_importance_df = pd.DataFrame()\n fold_importance_df[\"Feature\"] = train_columns\n fold_importance_df[\"importance\"] = clf.feature_importance()\n fold_importance_df[\"fold\"] = fold_ + 1\n feature_importance_df = pd.concat([feature_importance_df, fold_importance_df], axis=0)\n #predictions\n predictions += clf.predict(test_df[train_columns], num_iteration=clf.best_iteration) / folds.n_splits\n logger.info(strLog)\n \nstrRMSE = \"\".format(np.sqrt(mean_squared_error(oof, target)))\nprint(strRMSE)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cf61b73821749cd67da1714312836a3eea65527d"},"cell_type":"markdown","source":"## Feature importance"},{"metadata":{"trusted":true,"_uuid":"60552b9336ae32d7d9213ff659d64d6d480822ba"},"cell_type":"code","source":"##plot the feature importance\nlogger.info(\"Feature importance plot\")\ncols = (feature_importance_df[[\"Feature\", \"importance\"]]\n .groupby(\"Feature\")\n .mean()\n .sort_values(by=\"importance\", ascending=False)[:1000].index)\nbest_features = feature_importance_df.loc[feature_importance_df.Feature.isin(cols)]\nplt.figure(figsize=(14,26))\nsns.barplot(x=\"importance\", y=\"Feature\", data=best_features.sort_values(by=\"importance\",ascending=False))\nplt.title('LightGBM Features (averaged over folds)')\nplt.tight_layout()\nplt.savefig('lgbm_importances.png')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d514734c59942832dcb66ebce9ccd577bca64e27"},"cell_type":"markdown","source":"# Submission "},{"metadata":{"trusted":true,"_uuid":"679615b8f69a64346d23def0e7c9d9740c738dc7"},"cell_type":"code","source":"##submission\nlogger.info(\"Prepare submission\")\nsub_df = pd.DataFrame({\"card_id\":test_df[\"card_id\"].values})\nsub_df[\"target\"] = predictions\nsub_df.to_csv(\"submission.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9ce6c4f5ed473507f6f3a0044745c7df7ea4ddcc"},"cell_type":"markdown","source":"# References \n\n[1] https://www.kaggle.com/gpreda/elo-world-high-score-without-blending \n[2] https://www.kaggle.com/mfjwr1/simple-lightgbm-without-blending \n"}],"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}