{"cells":[{"metadata":{"_uuid":"cb444170fdf313154b61e6bfcfd19aaefff4c896"},"cell_type":"markdown","source":"# **ELO COMPETITION**\nby Ana Maria Cuciuc, Luise Schreiter and Adrian Villegas\n\n\n# **Content of this Kernel**\n\n1. [Introduction](#10)\n2. [Loading Necessary Packages](#1)\n3. [First Data Exploration](#2)\n4. [Closer Look at the Merchants Data](#3)\n5. [Preparing the Data](#4)\n6. [Feature Engineering](#5)\n7. [Merging the Data Sets](#6)\n8. [Second Data Exploration](#7)\n9. [Preparing the Prediction](#8)\n10. [Trying different Prediction Models](#11)\n11. [Ensemble Prediction](#9)\n12. [Project Reflection](#12)"},{"metadata":{"_uuid":"84c520ae37e2b62f8ffbde30f7b16461cd9cccc6"},"cell_type":"markdown","source":"<a id=\"10\"></a> <br>\n# **1. Introduction**\n\nThe Elo competition was chosen as a group project for the data science class from the master program business intelligence and process management. Therefore groups of three worked together to apply used methods from class.\n\nThe goal of this kaggle competition is to predict a loyality score for given credit cards. The loyality score should be based on the card owners activities. Therefore data frames with historical and new transactions were provided as well as a merchants data frame. As some columns are difficult to interpret also a data dictionary was provided. Nevertheless some columns were unexplained and labeled as \"anonymized measure/category\".\n\nThis kernel is the result from the group project. It provides the used code and explanations as well as additional information and reflections. This kernel starts with the loading of the data and a first exploration of it.  Afterwards a closer look at the merchants data table is taken. Then the data is prepared and new features are created. In the next step the data frames are merged. With the new merged data frames  a second data exploration is done as there are a lot of new informations given. Before finally the prediction is done the data gets some final adaptions. After the prediction step is done the kernel is closed with a reflection of the project."},{"metadata":{"_uuid":"e8925b1ff76eb7476bde379a5da59ca3814c8aca"},"cell_type":"markdown","source":"<a id=\"1\"></a> <br>\n# **2. Loading Necessary Packages and the Input Data**\n\nFirst of all necessary modules and packages are imported. As well as some configurations are set. In this kernel the following packages are used: pandas, matplotlib.pyplot, seaborn, numpy, scipy.stats, sklearn.ensemble, warnings, datetime. Most of these packages were already used in class. The warnings package is imported to prevent that warnings are shown in the output. There also *warning.filterwarnings('ignore')* was set as well. The datetime package is a module to manipulate dates and times and it is needed in this kernel to create new features from existing ones. With *sns.set(style='darkgrid', palette='deep')* the seaborn style for graphics is set. The *%matplotlib inline* command belongs to the \"magic functions\" from IPython and is able to render the figure in a notebook. The lightgbm is a package not used in clase it provides the lightgbm model which is used in a lot of kernels in this competition. Therefore it was of interest seeing how it works and therefore it was included."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# Import packages\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport numpy as np\nfrom scipy import stats\nimport warnings\nfrom sklearn.ensemble import GradientBoostingRegressor, RandomForestRegressor\nfrom sklearn.model_selection import train_test_split\nimport datetime\nimport lightgbm as lgb\nfrom IPython.display import Image\nsns.set(style='darkgrid', palette='deep')\nwarnings.filterwarnings('ignore')\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7ec36e9ac008fc0eb0dcae068020b6a9bbd13db6"},"cell_type":"markdown","source":"Afterwards the datasets are loaded. This is done with the pd.read_csv function. Hence afterwards their type is pandas data frame. The following data frames are imported:\n\n* train.csv\n* test.csv\n* merchants.csv\n* new_trans.csv\n* hist_trans.csv"},{"metadata":{"trusted":true,"_uuid":"60bb1b07ec70b60ac919367d71e64445dfe35323"},"cell_type":"code","source":"# Load train and test data\ntrain = pd.read_csv(\"../input/elo-merchant-category-recommendation/train.csv\", parse_dates=[\"first_active_month\"])\ntest = pd.read_csv(\"../input/elo-merchant-category-recommendation/test.csv\", parse_dates=[\"first_active_month\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e51413252b55c1c60e345569ce282f9df251b655"},"cell_type":"code","source":"# Load additional data\nmerchants = pd.read_csv(\"../input/elo-merchant-category-recommendation/merchants.csv\")\nnew_trans = pd.read_csv(\"../input/elo-merchant-category-recommendation/new_merchant_transactions.csv\", \n                        parse_dates=['purchase_date'])\nhist_trans = pd.read_csv(\"../input/elo-merchant-category-recommendation/historical_transactions.csv\", \n                         parse_dates=['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2e9c099bb8267ef168392ce0dad174458424eeca"},"cell_type":"markdown","source":"<a id=\"2\"></a> <br>\n# **3. Data Exploration**\n\nBefore data is processed and prepared for the prediction, first of all a data exploration is executed. Included in this is the size and shape of the different data frames and the heads of the data frames are shown to get a first impression of the different columns. In order to understand all of the columns the provided data dictionary was used. Besides shape and size, this first data exploration focus first on the train data frame and afterwards on the historical transactions and in the end on the new transactions."},{"metadata":{"trusted":true,"_uuid":"3e84205b0eab795854a4015909d22d2ce8e6488f"},"cell_type":"code","source":"# Shape of data frames\nprint(\"Train set size: \", train.shape)\nprint(\"Test set size: \", test.shape)\nprint(\"New Merchant Transactios set size: \", new_trans.shape)\nprint(\"Historical Transaction set size:\", hist_trans.shape)\nprint(\"Merchants set size:\", hist_trans.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"72bb1c61f074ec024113eacb8a30d752e72f1179"},"cell_type":"markdown","source":"In the following the heads of the train and the test data frames are shown. These two data frames have the same columns. The meaning of each column can be found in the data dictionary. Both train and test set contain unique card_ids and some connected informations. Nevertheless the data dictionary does not explain what feature 1, 2 and 3 are really about.\n\nTrain Data Frame:"},{"metadata":{"_kg_hide-output":false,"_kg_hide-input":true,"trusted":true,"_uuid":"2affe77086b35ccd228a749eabd62c1691d39fba"},"cell_type":"code","source":"train.head(5)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b134bb80fdf4d3d7f5a6489db746682b65d7e472"},"cell_type":"markdown","source":"Test Data Frame:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"57e08a79542f538c7337f3c20832e860fcdbb6f7"},"cell_type":"code","source":"test.head(5)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7f7dc34bdc789019dfb3ae0d6b1dd7e5dad9ed49"},"cell_type":"markdown","source":"Now the transaction data frames are taken into focus. These two data frames also have the same columns. Most of them are explained in the data dictionary but also in this case there are some columns that remain unexplained. In these data frames the card_ids aren't unique as there can be more than one transaction per card_id.\n\nNew Transactions Data Frame:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5be83d9cb92b1d3996557b222eb1d5f70b25b222"},"cell_type":"code","source":"new_trans.head(5)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d3b243c1ebeb2a82bc5fb819b453340ed0a2f0c8"},"cell_type":"markdown","source":"Historical Transactions Data Frame:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1cb95bb541a6c7cc32f2833415995f4d46106724"},"cell_type":"code","source":"hist_trans.head(5)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c80671917c6bd0e221b19dcb35bca01148fc3fa2"},"cell_type":"markdown","source":"The last data frame is the merchants data frame. This data frame differs from the other four as there is no direct connection to the card_ids. It will treated more in the next chapter.\n\nMerchants Data Frame:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a2b4f5d5b7ae43f0b126e6e29bac1aa282a627b5"},"cell_type":"code","source":"merchants.head(5)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d25f9448fb1244946587213b13dfbe3cbd3ca05b"},"cell_type":"markdown","source":"As a next step the target variable is explored and plotted. First the variable is described statistically. Different measures for the content of the target column are displayed. Afterwards the target column is plotted."},{"metadata":{"trusted":true,"_uuid":"a18dcb35f1ed36bab225c1657e5dc45ec53b9f16"},"cell_type":"code","source":"# Statistics regarding the target variable\ntrain['target'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d081b3796c84d1549b69a57d1646d331a5a0a92a","scrolled":true},"cell_type":"code","source":"#Target Variable Exploration\n\nfig, (ax1,ax2) = plt.subplots(1,2, figsize=(15,6))\n\n# Left plot\nax1.scatter(x=range(train.shape[0]), y=np.sort(train.target.values), c='r')\nax1.set_ylabel('Loyalty Score')\n\n# Right plot\nax2.hist(train.target, bins=50, color='red')\nax2.set_xlabel('Loyalty Score')\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"84777f166c1255bb01dc3c0bc8991219cf980c52"},"cell_type":"markdown","source":"As the plots above show some outliers, a closer look at the outliers is taken. As the prediction will be based on tree algorithms, the outliers don't have to be dropped."},{"metadata":{"trusted":true,"_uuid":"706b678b1f8fe3af6910423b368b921f26281ad8"},"cell_type":"code","source":"# Calculate number of outliers\noutliers = train[train.target < -20]\nprint(\"Number of outliers {}\".format(outliers.target.count()))\nprint(\"Percentage of the total number of data points {:}%\".format((outliers.target.count()/len(train.target))*100))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9394dcf86f1507d5bb5f4982938bf8627a6db5bc"},"cell_type":"markdown","source":"Now a closer look at the train data frame is taken. The distribution of all three features is plotted. Unfortunately it isn't possible to gain a lot of information from these plots as there is no information about what these features mean. After this data exploration it has been decided to treat them as categorical values."},{"metadata":{"trusted":true,"_uuid":"af285a562f6c26202cee3d8dd6d6da7348bc7f26","scrolled":true},"cell_type":"code","source":"# feature 1\nplt.figure(figsize=(8,4))\nsns.violinplot(x=\"feature_1\", y=train.target, data=train)\nplt.xticks(rotation='vertical')\nplt.xlabel('Feature 1', fontsize=12)\nplt.ylabel('Loyalty score', fontsize=12)\nplt.title(\"Feature 1 distribution\")\nplt.show()\n\n# feature 2\nplt.figure(figsize=(8,4))\nsns.violinplot(x=\"feature_2\", y=train.target, data=train)\nplt.xticks(rotation='vertical')\nplt.xlabel('Feature 2', fontsize=12)\nplt.ylabel('Loyalty score', fontsize=12)\nplt.title(\"Feature 2 distribution\")\nplt.show()\n\n# feature 3\nplt.figure(figsize=(8,4))\nsns.violinplot(x=\"feature_3\", y=train.target, data=train)\nplt.xticks(rotation='vertical')\nplt.xlabel('Feature 3', fontsize=12)\nplt.ylabel('Loyalty score', fontsize=12)\nplt.title(\"Feature 3 distribution\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ac7d6e2b138f1765aeaf492c9be9560a0a3a2db0"},"cell_type":"markdown","source":"In the next step the historical transactions will be taken into focus. Due to the limited RAM, the used outputs were created in another kernel and just imported in this notebook.\nFirst of all it will be checked how many transactions each card_id has."},{"metadata":{"trusted":true,"_uuid":"e34a532e72e216ded783891da84dba658c4adab0"},"cell_type":"code","source":"# Import of the dataset created in a third kernel\ntemp_hist_eda = pd.read_csv(\"../input/eda-project/temp_hist_eda.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e2dd672a6e0fceb0d3f7bb138b6d9cd6bdd056e4"},"cell_type":"code","source":"temp_hist_eda.head().sort_values(by='num_hist_transactions', ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4ce916c64d0fcf7f6ead192ed63b7dd5f366ef71"},"cell_type":"markdown","source":"Next the value of the historical transactions for the cards are related to the loyalty score. Therefore some a boxplot is created."},{"metadata":{"trusted":true,"_uuid":"0d7ee40c9fd89e5f1892facbbc1e746f93d403e7"},"cell_type":"code","source":"Image(\"../input/boxplots/Boxplot_hist.png\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9098daa5aa89050b42e0757ff9d68cd61026dff1"},"cell_type":"markdown","source":"The Boxplot shows that there seems to be an increase of the loyalty score with more valuable historical transactions. Now the new transactions will be analyzed in a similar way as the historical data frame."},{"metadata":{"trusted":true,"_uuid":"c8cf3f0f8cc7184795eb8130e05e1edbc66e7c52"},"cell_type":"code","source":"temp_new_eda = pd.read_csv(\"../input/eda-project/temp_new_eda.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0ab43ec80523e221164dc9538e3fbe20e7544d73"},"cell_type":"code","source":"temp_new_eda.head().sort_values(by='num_merch_transactions', ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2d5599c059a2b679e7f0747f92528c18b9588445"},"cell_type":"markdown","source":"Again Boxplots are created to get more information about the data frame."},{"metadata":{"trusted":true,"_uuid":"ee6b7940e38d3ae991ce15c5e4b2d930c317a767"},"cell_type":"code","source":"Image(\"../input/boxplots/Boxplot_hist.png\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f21a1ab72b7d22bce338fe825418233fbead87b9"},"cell_type":"markdown","source":"It seems the loyalty score decreases as the number of new merchants transactions increases. The last bin presents an exception in this case.\n\nAs a conclusion from this first data exploration it can be said that the data exploration of the test and train data frames is not significant, as there is no information about what the features are about and the other data frames are still not joined to them. Meanwhile it's obvious that the two transaction data frames will be important for the loyalty score, there is no information about the merchants data frame.\n\nAs a last step from the first data exploration the data frames are checked for missing values."},{"metadata":{"trusted":true,"_uuid":"e73feaaff6d970fa39716beb2951654eb5f657fc"},"cell_type":"code","source":"#Checking missing values\ntrain.isnull().any()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b4a6f514366c4cf1b5eb8e1ffc50476d054e2428"},"cell_type":"code","source":"test.isnull().any()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3c4843f260ea22a8854e8c9b3e1510bb570702bd"},"cell_type":"code","source":"hist_trans.isnull().any()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef2de2ba5dc387395ccebff5c7a6a29d13a4f5b7","scrolled":true},"cell_type":"code","source":"new_trans.isnull().any()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eb08d4cf9895912a183d57bbbe759317b1ced5b6"},"cell_type":"code","source":"merchants.isnull().any()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9838e6ac34e7f0d223b4c772a09d98e42c5dd8df"},"cell_type":"markdown","source":"Besides the train data frame all the others are suffering from missing values. This has to be taken care of. In chapter 5 \"Data Preparation\" missing values will be handled."},{"metadata":{"_uuid":"3dec56fb976130f58198b4275a33bc91feccc4d9"},"cell_type":"markdown","source":"<a id=\"3\"></a> <br>\n# **4. Closer Look at the Merchants Data**\n\nIn order to find out if the merchants data has any effect on the loyalty score. The five card_ids with the highest and the lowest scores where filtered from the train data.\n\n**5 highest scores:**\n* C_ID_a4e600deef\n* C_ID_1c8a5b9d44\n* C_ID_b0f1d28bd3\n* C_ID_700c15a07d\n* C_ID_ecc4e2e188\n\n**5 lowest scores:**\n* C_ID_282d394cc6\n* C_ID_ebbf8a7516\n* C_ID_3e35c68b54\n* C_ID_defab7ce82\n* C_ID_fc7b761beb\n\nThese ids where filtered in the train data and patterns in the merchants were searched. The idea was to see if any merchant had special influence on the high or low scores. Also the merchants categories were checked for the same. It resulted that both groups low and high had the same merchants and same categories in their transaction. Hence it can be concluded that there aren't any special merchants or categories that should be taken more into focus. All the other data columns from the merchants data frame provide specific information to each merchant."},{"metadata":{"trusted":true,"_uuid":"606532c44f1fbe3e96e7fa2fd0d6b5853652a47b"},"cell_type":"code","source":"# Filtering data frames regarding highest and lowest top 5\ntop_five = ['C_ID_a4e600deef', 'C_ID_1c8a5b9d44', 'C_ID_b0f1d28bd3', 'C_ID_700c15a07d',\n            'C_ID_ecc4e2e188']\n\nlow_five = ['C_ID_282d394cc6', 'C_ID_ebbf8a7516', 'C_ID_3e35c68b54', 'C_ID_defab7ce82',\n            'C_ID_fc7b761beb']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"56039971f118e9665e6e6286db4799c319c1672a"},"cell_type":"code","source":"# Extracting rows from from historical and new transactions with the filtered card_ids\n\ntop_hist = hist_trans.loc[hist_trans['card_id'].isin(top_five)]\ntop_new = new_trans.loc[new_trans['card_id'].isin(top_five)]\n\nlow_hist = hist_trans.loc[hist_trans['card_id'].isin(low_five)]\nlow_new = new_trans.loc[new_trans['card_id'].isin(top_five)]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2344e3fdaccc745612416672bc3bdcf09000ccd8"},"cell_type":"markdown","source":"Then each of those data frames was once grouped by merchant_id and merchant_category_id. Then the results of lowest and highest score were compared. In this kernel this only will be done as an example on the historical transactions. First it is done for the merchant_id."},{"metadata":{"trusted":true,"_uuid":"8e94f07f9e2b9b5c3f55f873dcc85f60ce512d18"},"cell_type":"code","source":"top_hist.groupby('merchant_id').count().sort_values(by='authorized_flag', ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e28e6d6f14c808db60478a03eb3eb4a296eee10a"},"cell_type":"code","source":"low_hist.groupby('merchant_id').count().sort_values(by='authorized_flag', ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f855a15008bb343f876efc70a5ae517879bcaaac"},"cell_type":"markdown","source":"There is no clear pattern if the low and the high scores are compared. The merchant with the highest count in the high scores also appears in the table from the low scores. Following the same has been executed on the merchant_category_id."},{"metadata":{"trusted":true,"_uuid":"ece53bbdd46bdceeee5e1d56687c1de9852b16b2"},"cell_type":"code","source":"top_hist.groupby('merchant_category_id').count().sort_values(by='authorized_flag', ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"82f4dd866e7d4b8116b237b715a1819569a6b3e7"},"cell_type":"code","source":"low_hist.groupby('merchant_category_id').count().sort_values(by='authorized_flag', ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b926f7e9d1aa60e19797d3a2cd28553d5239991f"},"cell_type":"markdown","source":"Similar to the result from the merchant_id, the same results from merchant_category_id. 705, 307 and 367 appear in both tables. Therfore it was decided to not include the merchants data frames as it only provides additional information regarding the merchants and as there is no specific pattern it was deleted.."},{"metadata":{"trusted":true,"_uuid":"ed15fb490395ba286a8c9235a7fa19744f3b8f5f"},"cell_type":"code","source":"del merchants","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f123dc9f36a655f29bf4a4c9888dc98b868e078a"},"cell_type":"markdown","source":"<a id=\"4\"></a> <br>\n# **5. Preparing the Data**\n\nIn the next step the data is prepared. Within this chapter columns get binarized and missing values are handled. In order to binarize the columns a function was written. In this function *Y* is mapped to 1 and *N* to 0. The columns *authorized_flag* and *category_1* were identified with values to be binarized. The target column is already extracted and stored separately. Nevertheless it is still kept within the data frame in order to use it for testing the different models. In the end categorical values are taken care of."},{"metadata":{"trusted":true,"_uuid":"486ef6663866163573a8f70ef74acaecebc220f3"},"cell_type":"code","source":"# Define binarize function to binarize some columns\ndef binarize(df):\n    for col in ['authorized_flag', 'category_1']:\n        df[col] = df[col].map({'Y':1, 'N':0})\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"818276a1663546ea7b8970fc2bc68c05cdc7ddcd"},"cell_type":"code","source":"# Binarize function is applied to new_trans and hist_trans\nnew_trans = binarize(new_trans)\nhist_trans = binarize(hist_trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9128104c8a7bac5e4fb00eb2ae6a596433d3c27d"},"cell_type":"markdown","source":"As discovered there are missing values in nearly all data frames. Only the train data does not contain any missing values. The merchants data frame has been deleted and hence needs no adapting. Therefore now the other three data frames will be focused. First the test set is covered. The test set only contains missing values in one column."},{"metadata":{"trusted":true,"_uuid":"8c9fd1114814e3a12236a86558d9d29ec3c2ad72"},"cell_type":"code","source":"# Handling missing values in the test set - filtering rows with missing values\nmissing_test = test[test.isnull().any(axis=1)]\nmissing_test","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"51872e1e30e8b891f7570fe1846ab760edaf61ec"},"cell_type":"markdown","source":"There is only one row with a missing value. As it is in the column *first_active_month*, it is checked when the first transaction occured.\nThe card_id of this row is C_ID_c27b4f80f7."},{"metadata":{"trusted":true,"_uuid":"8cc305cc83762faef8589b75f5d73a19f02986b1"},"cell_type":"code","source":"missing_value_test = hist_trans.loc[hist_trans['card_id'].isin(['C_ID_c27b4f80f7'])]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"64fae14c75783495962a2e5c347423a37ae46a67","scrolled":true},"cell_type":"code","source":"missing_value_test.sort_values(by='purchase_date').head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cc0b7741527b25994cf2493f30c69b982d9b9375"},"cell_type":"markdown","source":"The first transaction has the timestamp '2017-03-09'. This timestamp is used to fill the missing value."},{"metadata":{"trusted":true,"_uuid":"3bd887bd8266fb60dc8e4ca165bfa413dbb894c9"},"cell_type":"code","source":"values = {'first_active_month': '2017-03-09'}\ntest = test.fillna(value=values)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3efd947a5d3cbde0f3ade2f50b73cd8aafdfcf2a"},"cell_type":"code","source":"del missing_value_test","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c69b01533435835b7ab05c0dafcba08b29a97515"},"cell_type":"markdown","source":"As the train and test data frames are complete now the focus set on the new and historical transactions. Both data frames have missing values in the same columns as seen in the first data exploration. The affected columns are category_2, category_3 and merchant_id.\nTo fill the gaps in the merchant_id column the most frequented merchant is used. As seen in the chapter focusing on the merchants table, the most frequented one is M_ID_00a6ca8a8a both for the lower scores as well as for the higher scores. For the other two affected columns the most frequently used value is filled in the gaps."},{"metadata":{"trusted":true,"_uuid":"7f6a978e41eab542539f988b12cb29a41a1d2db8"},"cell_type":"code","source":"# Finding most common value to fill in category_2\nhist_trans['category_2'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c58f95260723b5220a25d298079e5f2d86ef439c"},"cell_type":"code","source":"# Finding most common value to fill in category_3\nhist_trans['category_3'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"09c3939c79bfe9a7863535635c0e80a2056b2750"},"cell_type":"code","source":"# Handling missing values in new and historical transactions\nfor df in [hist_trans, new_trans]:\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)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"17b3858ce040f4067f3eb11704ccdcd953f9fbe5"},"cell_type":"markdown","source":"As all missing values are cleared. The categorical values are take care of. The train and test set have three features. As feature_3 only contains 0 and 1 nothing has to be done. For the other two columns the pd.get_dummies function is used."},{"metadata":{"trusted":true,"_uuid":"d884732538806edb90c3ec29528d8f41354de6cc"},"cell_type":"code","source":"# Treating feature_1 and feature_2 as categorical values\ntrain = pd.get_dummies(train, columns=['feature_1', 'feature_2'])\ntest = pd.get_dummies(test, columns=['feature_1', 'feature_2'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b0bd5a7d01c03dd1e5e6b770ab617ee0b0278cad"},"cell_type":"markdown","source":"In the historical and new transaction data frames the columns category_2 and category_3 were identified as categorical values. Therefore again the pd.get_dummies function is used."},{"metadata":{"trusted":true,"_uuid":"415d948171fba46bb791b38042c032b5072351ad"},"cell_type":"code","source":"# Handling categorical values in the new_transaction and historical_transactions\nhist_trans = pd.get_dummies(hist_trans, columns=['category_2','category_3'])\nnew_trans = pd.get_dummies(new_trans, columns=['category_2','category_3'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"28ea11435ea703445546b138119ece6938607b81"},"cell_type":"markdown","source":"To close this chapter the target column gets extracted and stored as an idependent variable."},{"metadata":{"trusted":true,"_uuid":"518e6e1969162032943613bbc5715b7b916dead7"},"cell_type":"code","source":"# Get target column\ntarget = train['target']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c3981e692ef67a2556ecbafa15d52fcb081700b6"},"cell_type":"markdown","source":"<a id=\"5\"></a> <br>\n# **6. Feature Engineering**\n\nThere are five separated data sets available. The next part is used to create new features in the different data sets.\n\nFirst of all the pandas *to_datetime* function is used to convert the column *first_active_month* to datetime. From this afterwards the year and the month are extracted and stored in own columns. The days are extracted as well to calculate the elapsed time that is also stored in its own column. This is applied for the train set as well as for the test set."},{"metadata":{"trusted":true,"_uuid":"628d3b2f6f640948b38d7da1a71f63c7b88b9276"},"cell_type":"code","source":"# Adding columns start_year, start_month and elapsed_time\nfor dataframe in [train, test]:\n    dataframe['first_active_month'] = pd.to_datetime(dataframe['first_active_month'])\n    dataframe['start_year'] = dataframe['first_active_month'].dt.year\n    dataframe['start_month'] = dataframe['first_active_month'].dt.month\n    dataframe['elapsed_time'] = (datetime.datetime.today() - dataframe['first_active_month']).dt.days","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"385a6395c4fd937c7354785c271116ddf6dfe8ce"},"cell_type":"markdown","source":"The next feature that is created is the month difference. This feature is created either for the new transactions as well as for the historical transactions. Therefore a new column is created. To do so the difference from the current date to the purchase date is calculated and then divided by 30. The results are the months that passed since then. The month lag of each card_id is added to the result."},{"metadata":{"trusted":true,"_uuid":"105b449f2f91190041fb2860536489c799b55200"},"cell_type":"code","source":"# Creating a new column on base of the month_lag column\nhist_trans['month_diff'] = ((datetime.datetime.today() - hist_trans['purchase_date'])\n                            .dt.days)//30\nhist_trans['month_diff'] += hist_trans['month_lag']\n\nnew_trans['month_diff'] = ((datetime.datetime.today() - new_trans['purchase_date'])\n                           .dt.days)//30\nnew_trans['month_diff'] += new_trans['month_lag']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"245b67f264efd795fb3e714529b6d0660984770e"},"cell_type":"markdown","source":"The next features are basically just time features for the historical and new transactions that are created from the colum *purchase_date*. Similar to what was done in the beginning to_datetime is used to convert the *purchase_date* column. Afterwards month, year, week day and hour are extracted and added as columns, then another column is added where it is checked whether it was a purchase on a weekend or during the week."},{"metadata":{"trusted":true,"_uuid":"5e53975c4e08b1a246076174cfd4908b9c6f0ac3"},"cell_type":"code","source":"# Adding a new column purchase_month\nfor dataframe in [hist_trans, new_trans]:\n    dataframe['purchase_date'] = pd.to_datetime(dataframe['purchase_date'])\n    dataframe['purchase_month'] = dataframe['purchase_date'].dt.month\n    dataframe['purchase_year'] = dataframe['purchase_date'].dt.year\n    dataframe['purchase_dayofweek'] = dataframe['purchase_date'].dt.dayofweek\n    dataframe['purchase_hour'] = dataframe['purchase_date'].dt.hour\n    dataframe['purchase_weekend'] = (dataframe.purchase_date.dt.weekday >=5).astype(int)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0035d3b079ba7a148eb627964b5d2e9c189104fb"},"cell_type":"markdown","source":"<a id=\"6\"></a> <br>\n# **7. Merging the Data Sets**\n\nAs the tables are still disconnected they have to be merged. As there are different amounts of rows for each card_id, aggregate functions for each column have to be defined in order to group the rows by card_id."},{"metadata":{"trusted":true,"_uuid":"e5c8e0b51bf72f7f8b2ea8a541d7d527b8362b22"},"cell_type":"code","source":"# Defining aggregate functions\naggregate_function = {\n    'authorized_flag': ['sum', 'mean'],\n    'card_id': ['size'],\n    'category_1': ['sum', 'mean'],\n    'category_2_1.0': ['mean', 'sum'],\n    'category_2_2.0': ['mean', 'sum'],\n    'category_2_3.0': ['mean', 'sum'],\n    'category_2_4.0': ['mean', 'sum'],\n    'category_2_5.0': ['mean', 'sum'],\n    'category_3_A': ['mean', 'sum'],\n    'category_3_B': ['mean', 'sum'],\n    'category_3_C': ['mean', 'sum'],\n    'merchant_id': ['nunique'],\n    'merchant_category_id': ['nunique'],\n    'state_id': ['nunique'],\n    'city_id': ['nunique'],\n    'subsector_id': ['nunique'],\n    'purchase_amount': ['sum', 'mean', 'max', 'min', 'std', 'var'],\n    'installments': ['sum', 'mean', 'max', 'min', 'std', 'var'],\n    'month_lag': ['mean', 'max', 'min', 'std', 'var'],\n    'month_diff': ['mean'],\n    'purchase_date': ['max', 'min'],\n    'purchase_month': ['nunique'],\n    'purchase_year': ['nunique'],\n    'purchase_dayofweek': ['nunique'],\n    'purchase_hour': ['nunique'],\n    'purchase_weekend': ['sum', 'mean']\n}","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"42f053a2708643b0b828f26c5ca7dc0051dc1e74"},"cell_type":"markdown","source":"Before the tables can be merged new columns names for the aggregated rows are created. Therefore a function was written that connects the name of the column with hist or new regarding the data frame and the corresponding aggregate function. Then the data frames are grouped by the card_id and afterwards the created names are given to the new columns."},{"metadata":{"trusted":true,"_uuid":"2c5f67f5154c26a2a2e818965be444ed777ccdc2"},"cell_type":"code","source":"def new_columns(name, aggregate_functions):\n    column = []\n    for k in aggregate_function.keys():\n        for agg in aggregate_function[k]:\n            column.append(name + '_' + str(k) + '_' + str(agg))\n    return column\n\nnew_hist_columns = new_columns('hist', aggregate_function)\nnew_new_columns = new_columns('new', aggregate_function)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"35ab11f238fc0ee38292440ed0d0b9a30f5b4c8a"},"cell_type":"code","source":"# grouping the transaction on card_id by the above defined aggregate functions\ngrouped_hist = hist_trans.groupby(['card_id']).agg(aggregate_function)\ngrouped_hist.columns = new_hist_columns\ngrouped_hist.reset_index(drop=False,inplace=True)\ndel hist_trans","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"27c9b24cb941283905141d95304d68833243450b"},"cell_type":"code","source":"# grouping the transaction on card_id by the above defined aggregate functions\ngrouped_new = new_trans.groupby(['card_id']).agg(aggregate_function)\ngrouped_new.columns = new_new_columns\ngrouped_new.reset_index(drop=False,inplace=True)\ndel new_trans","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"93f582dc881a67d92890728eae3f11d864996488"},"cell_type":"markdown","source":"The next step belongs to the feature engineering but it needed the tables to already be grouped by the card_id. Therefore this has been switched to this chapter. Three new features are created for each of the grouped data frames. The columns *purchase_date_diff* is created by the difference from the maximum purchase date and the minimum purchase date. The column *purchase_date_avg* uses the newly created column directly and divides it by the column card_id_size which was created by the grouping. The last column *purchase_date_uptonow* is created by the difference from today's date and the maximum purchase date."},{"metadata":{"trusted":true,"_uuid":"c3e5703ac02bd49a8ae15a02700d1b8280d0efbf"},"cell_type":"code","source":"count = 0\nfor dataframe in [grouped_hist, grouped_new]:\n    if count == 0:\n        x = 'hist'\n        count = 1\n    else:\n        x = 'new'\n    dataframe[x+'_purchase_date_diff'] = (dataframe[x+'_purchase_date_max'] - dataframe[x+'_purchase_date_min']).dt.days\n    dataframe[x+'_purchase_date_avg'] = dataframe[x+'_purchase_date_diff']/dataframe[x+'_card_id_size']\n    dataframe[x+'_purchase_date_uptonow'] = (datetime.datetime.today() - dataframe[x+'_purchase_date_max']).dt.days","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b48cbea47d55846a9c10c64b81b796ed421302c"},"cell_type":"markdown","source":"After grouping the data sets the grouped historical and new data frames are merged on card_id in form of a left join. Again temporary dataframes are removed afterwards. Then a function is used to reduce the memory usage."},{"metadata":{"trusted":true,"_uuid":"04963fddab5012688787170ffa7a1cb737e9e456"},"cell_type":"code","source":"# Join historical transactions into train and test\ntrain = pd.merge(train, grouped_hist, on='card_id', how='left')\ntrain = pd.merge(train, grouped_new, on='card_id', how='left')\ntest = pd.merge(test, grouped_new, on='card_id', how='left')\ntest = pd.merge(test, grouped_hist, on='card_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"380143fbfea739c575278f9bd09eabae46b3d2fb"},"cell_type":"code","source":"# Delete grouped dataframes\ndel grouped_hist\ndel grouped_new","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9f5195879cf541325cbd3112e634d3bdc3f0415a"},"cell_type":"markdown","source":"The following function has been copied from the kernel [Elo World](https://www.kaggle.com/fabiendaniel/elo-world). It is a great function to reduce the memory usage. As this competition handels huge amounts of data it is quite helpful."},{"metadata":{"trusted":true,"_uuid":"5690f2c4d3868f2d7d46b8c7ce8a231a8de6d971"},"cell_type":"code","source":"# reduce_mem_usage was taken over from Elo World\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    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3cc3e9ad21883c13bf300e52812caebedfe7082b"},"cell_type":"code","source":"# Reduce memory usage\ntrain = reduce_mem_usage(train)\ntest = reduce_mem_usage(test)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4c9eff127335ee64d4ffc90565b6379e0f330a0a"},"cell_type":"markdown","source":"<a id=\"7\"></a> <br>\n# **8. Second Data Exploration**\n\nNow that the data frames are merged together there is a new base to explore and connections to be made. To start and get a quick overview a correlation matrix in form of a heatmap is created but no real conclusions are possible as it is includes too much features."},{"metadata":{"trusted":true,"_uuid":"b0fab0470adfecf44de467681bc5ba4ae02b87b7","scrolled":true},"cell_type":"code","source":"# Correlation matrix\ncorrmat = train.corr()\nf, ax = plt.subplots(figsize=(12, 9))\nsns.heatmap(corrmat, vmax=.8, square=True);","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"467c2d46030764a8680844976bdf7a7e35df7803"},"cell_type":"markdown","source":"As the first correlation matrix was unclear a second one is created but this time on a set of chosen columns. The selection has been set randomly"},{"metadata":{"trusted":true,"_uuid":"1fb6fd7b3144119dae2d958100e7c45d14c5351a"},"cell_type":"code","source":"#Correlation matrix on a reduced data frame\ncolumns = ['target', 'hist_month_diff_mean', 'hist_category_1_sum', 'hist_purchase_month_nunique',\n           'hist_category_3_B_sum', 'start_year', 'elapsed_time', 'new_merchant_category_id_nunique',\n           'hist_authorized_flag_mean']\n\ncolormap = plt.cm.RdBu\nplt.figure(figsize=(12,12))\nsns.heatmap(train[columns].corr(), linewidths=0.1, vmax=1.0, vmin=-1., square=True, cmap=colormap, linecolor='white', annot=True)\nplt.title('Pair-wise correlation')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9e3ae4b857bbf93eff777cedfab2ae290d79a74c"},"cell_type":"markdown","source":"There is a strong negative linear relationship between start_year and elapsed_time this can be lead back to the fact that they have been created from the same column.\n\nAnother visualization has been done in form of scatterplots. The columns used were the same as for the previous correlation matrix."},{"metadata":{"trusted":true,"_uuid":"33d0c804267e2a819c7f2dbfe25b3c9591190944","scrolled":true},"cell_type":"code","source":"#scatterplots of the chosen columns\ncolumns = ['target','hist_month_diff_mean','hist_category_1_sum','hist_category_3_B_sum',\n           'start_year','elapsed_time','hist_authorized_flag_mean']\n\nsns.set()\nsns.pairplot(train[columns], size = 2.5)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"78df85e94ce60535e8f2548b324c46bcefa0b8af"},"cell_type":"markdown","source":"The same results can be observed in the visualization in form of the pairplots. The negative correlation is visible. A slight correlation is also observeable between *count_hist_transactions* and *hist_category_3_B_sum*. This is also displayed in the correlation matrix."},{"metadata":{"trusted":true,"_uuid":"0cf7349aee0e0091a006ed6906803166575be08e"},"cell_type":"code","source":"del train['target']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"31dfeeee588f35156ab186cd39341e7b44cf311a"},"cell_type":"markdown","source":"<a id=\"8\"></a> <br>\n# **9. Preparing the prediction**\n\nBefore the prediction of the scores for the test set can be done the train and test set need some final adjustments.\nFirst of all the first the columns *first_active_month* and *card_id* are dropped as they are not relevant for the prediction"},{"metadata":{"trusted":true,"_uuid":"2bed547dcfb0f45d30dd7ce1584248761b5d8b9d","scrolled":false},"cell_type":"code","source":"# Dropping the columns first_active_month and card_id\nx_train = train.drop(['first_active_month', 'card_id'], axis=1)\nx_test = test.drop(['first_active_month', 'card_id'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc0d348ba7b2288ba7f11e4ce07428d124018c48"},"cell_type":"code","source":"x_train.to_csv(\"missing_values_train.csv\", index=False)\nx_test.to_csv(\"missing_values_test.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9abbf5889afb8dbe08ec2e9688e53491b26ba451"},"cell_type":"markdown","source":"As not all card_ids had transactions in the new transaction data frame there are again columns with missing values. These again have to be taken care of. Most of the columns can be filled simply with zero. Nevertheless there are some columns that might be better filled with the mean of the regarding column."},{"metadata":{"trusted":true,"_uuid":"b0fbcd4f181e7b492cef707b3d94406da585949b"},"cell_type":"code","source":"# Define non zero value columns and fill them with the mean\n#non_zero_value_columns = ['new_month_lag_mean', 'new_month_lag_max', 'new_month_lag_min',\n#                          'new_month_lag_std', 'new_month_lag_var', 'new_purchase_date_diff',\n#                          'new_purchase_date_avg', 'new_purchase_date_uptonow']\n#\n#for x in non_zero_value_columns:\n#    x_train[x] = x_train[x].fillna(x_train[x].mean)\n#    x_test[x] = x_test[x].fillna(x_train[x].mean)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dfc27163e745a3bcd514ae5e6fbff82228d0705d"},"cell_type":"code","source":"# Fill the remaining missing values\nx_train = x_train.fillna(0)\nx_test = x_test.fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f79d28152c2c1fd2a3ef5a3f4e217f8a6602e806"},"cell_type":"markdown","source":"As there are some columns in formats that can't be used for the chosen prediction models, a list is filled with the fitting columns."},{"metadata":{"trusted":true,"_uuid":"48895cbc677e982d8424ace9e5242dbe44559f3b"},"cell_type":"code","source":"# Columns to use for train set\ncolumns_to_use = []\nfor c in x_train.columns.values:\n    if c != 'hist_purchase_date_min' and c != 'hist_purchase_date_max'and c != 'new_purchase_date_max' and c != 'new_purchase_date_min':\n        columns_to_use.append(c)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d7bde750f0e5c8a7d1205c5a7b82bfcc466e9fdc"},"cell_type":"markdown","source":"<a id=\"11\"></a> <br>\n# **10. Testing different Prediction Models**\n\nIn the progress of the data science class differentt prediction models were introduced. The following prediction models are going to be tested on a train test split and afterwards the scores are presented in a table:\n* DecisionTreeRegressor\n* RandomForestRegressor\n*  GradientBoostingRegressor\n* LightGBM\n* ElasticNetCV\n* RidgeCV\n* LassoPath\n\nThe first four are based on tree algorithms meanwhile the other three perform linear regression with cross validation.\nA train test split is created and later applied to the different models.\n\nAs the amount of data leads to problem with the RAM this models were executed in a second kernel and afterwards the results were imported in this kernel."},{"metadata":{"trusted":true,"_uuid":"d141829b1bafdd8a1f87dfdda4889918a64d9b1d"},"cell_type":"code","source":"# Train test split\nX_train, X_test, Y_train, Y_test = train_test_split(x_train[columns_to_use], target, test_size=0.3, random_state=42)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"42041cf3528d8fcc9a3eb39d26d909c7ec8e07af"},"cell_type":"code","source":"Y_train = pd.DataFrame(Y_train)\nY_test = pd.DataFrame(Y_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"977a3c39a1a8fe9fbde276214f563dd32a0bf8a7"},"cell_type":"code","source":"# Exporting the prepared data frames to use them in a second kernel\nX_train.to_csv(\"X_train.csv\", index=False)\nY_train.to_csv(\"Y_train.csv\", index=False)\nX_test.to_csv(\"X_test.csv\", index=False)\nY_test.to_csv(\"Y_test.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"522faacfa8b6a0b593f850e5a2b5d890cc33aac4"},"cell_type":"code","source":"# Importing the results from the second kernel\nRMSE_table = pd.read_csv(\"../input/rmse-table/RMSE_table.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"46b1b5e6d9a5014c90be0889649c6de04f3f594b"},"cell_type":"code","source":"RMSE_table","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5929f67668d10a19ecf71d1922942a1406e734ba"},"cell_type":"markdown","source":"**Conclusion**: As can be seen in the table above the LightGBM has the best score closely followed by the GradientBoostingRegressor. Also the RandomForestRegressor gets a similar score. Therefore a combination of tree algorithms was chosen."},{"metadata":{"_uuid":"f49123b0291a300511334d96eaa793e903f72e0f"},"cell_type":"markdown","source":"<a id=\"9\"></a> <br>\n# **11. Ensemble Prediction**\n\nConsidering the results from the trial of the different prediction models it was decided to use an ensemble prediction that means combining the three models LightGBM, RandomForrestRegressor and GradientBoostingRegressor. LightGBM and GradientBoostingRegressor both are boosting tree algorithms, RandomForrestRegressor is a tree algorithm is as well and uses bootstrapping. The used parameters from the LightGBM returned the best result in the previous chapter, therefore the parameters from the GradientBoostingRegressor and RandomForrestRegressor were adapted accordingly.\nFirst both models get trained. The first model is the LightGBM."},{"metadata":{"_uuid":"e01e2573ecbde5d7276da34506edd5435c7b0d95"},"cell_type":"markdown","source":"**LightGBM**\n\nThe parameters are defined and the model is trained."},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"519537ff1ad6e4129036eb08cb6b4059fdb114d4","_kg_hide-output":true},"cell_type":"code","source":"# Dataset definition LGB\ntrain_data = lgb.Dataset(X_train, label=Y_train)\ntest_data = lgb.Dataset(X_test,label=Y_test)\n\nlgb_params = {\"objective\" : \"regression\", \n\"metric\" : \"rmse\",\n\"max_depth\": 8, \n\"min_child_samples\": 100, \n\"reg_alpha\": 1, \n\"reg_lambda\": 1,\n\"num_leaves\" : 64, \n\"learning_rate\" : 0.01,\n\"subsample\" : 0.8, \n\"colsample_bytree\" : 0.8, \n\"verbosity\": -1}\n\n# Model training\nlgb_model = lgb.train(lgb_params,train_data,valid_sets=test_data,num_boost_round=100000,early_stopping_rounds=100)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"149df994dd916d73f34123e37e1c74a7d6617861"},"cell_type":"markdown","source":"**Gradient Boosting Regressor**\n\nAs above the parameters are defined and the model is trained. As said before the parameters got adapted according to the parameters from the LightGBM."},{"metadata":{"trusted":true,"_uuid":"0c6ae2e293403e8687277b2b8cfd7108b480c194","_kg_hide-output":true},"cell_type":"code","source":"boost_reg = GradientBoostingRegressor(n_estimators=500, learning_rate=0.01, subsample=0.8, max_depth=8)\nboost_reg.fit(x_train[columns_to_use], target)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"15e842e43bf37b5deb559860f6d86d5c2547fa39"},"cell_type":"markdown","source":"For the Gradient Boosting Regressor the  relative feature importance is printed out. The features are printed in a descending order."},{"metadata":{"trusted":true,"_uuid":"cc72ff1f50d37ea4f7c7cbefbb6bd250ee231599","_kg_hide-output":false,"_kg_hide-input":false,"scrolled":true},"cell_type":"code","source":"# Plotting the feature_importance\nfeature_importance = boost_reg.feature_importances_\n# make importances relative to max importance\nfeature_importance = 100.0 * (feature_importance / feature_importance.max())\n\nrel_imp = pd.Series(feature_importance, index=x_train[columns_to_use].columns).sort_values(inplace=False, ascending=False)\nprint(rel_imp)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9cf697f3b9e4a7f9579bacf74ea73ac940c2d911"},"cell_type":"markdown","source":"To visualize the printed values, the same information is plotted in a bar chart."},{"metadata":{"trusted":true,"_uuid":"95e714242850d1eec6f4e5a19a3241ed747c79bd"},"cell_type":"code","source":"(pd.Series(feature_importance, index=train[columns_to_use].columns)\n   .nlargest(20)\n   .plot(kind='barh'))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"af896a3ae626ceb59eaa5ace40c9c21f0735945a"},"cell_type":"markdown","source":"**RandomForrestRegressor**\n\nThe model gets trained. As stated above the parameters were adapted to the ones from the lightGBM."},{"metadata":{"trusted":true,"_uuid":"8df40a7936b816c83e21e4b6ef717d67bf435c02"},"cell_type":"code","source":"model_random = RandomForestRegressor(n_estimators=500, max_depth=8)\nmodel_random.fit(x_train[columns_to_use], target)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"354062c6ea0d22beda8c6b0ae8586064f786f197"},"cell_type":"markdown","source":"For all models the predictions are made and then the mean is taken as the final prediction. Nevertheless four different submission files are created in order to be able to compare the results."},{"metadata":{"trusted":true,"_uuid":"eac6b4ce3e3af90018959e77f14ab609903cc012"},"cell_type":"code","source":"# Make predictions based on the RandomForrestRegressor\nrandom_prediction = model_random.predict(x_test[columns_to_use])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c46b14a7a9cc1672186dd4ee2c3ca24656ae4957"},"cell_type":"code","source":"# Make predictions based on the boosting regressor\nboost_prediction = boost_reg.predict(x_test[columns_to_use])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1acb0539049737dcc5947a5a928a62a40badb96c"},"cell_type":"code","source":"# Make predictions based on the lightgbm regressor\nlgb_prediction = lgb_model.predict(x_test[columns_to_use])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"84f16437dbe6f2b0e09c02cf7ccb999e0f41c337"},"cell_type":"code","source":"# Stacked predictions\nprediction = (0.33*boost_prediction) + (0.34*lgb_prediction) + (0.33*random_prediction)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"52daee36e096a80bc626c03467a7fd3d58c41c1c"},"cell_type":"markdown","source":"Finally the submission file is created."},{"metadata":{"trusted":true,"_uuid":"fe8e977a6508f80d8a8308196e0e829c2206f2ad"},"cell_type":"code","source":"# Submission for ensemble prediction\nx_test_id = test['card_id']\nsub_df = pd.DataFrame({\"card_id\":x_test_id.values})\nsub_df[\"target\"] = prediction\nsub_df.to_csv(\"submission_ensemble.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c656a48cd99dd44f09a208c985e982cda890604c"},"cell_type":"code","source":"# Submission file for RandomForrestRegressor\nsub_df = pd.DataFrame({\"card_id\":x_test_id.values})\nsub_df[\"target\"] = random_prediction\nsub_df.to_csv(\"submission_random.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"209f986d1bf7b403f588f52483e6e5b13f87eb8d"},"cell_type":"code","source":"# Submission file for GradientBoostingRegressor\nsub_df = pd.DataFrame({\"card_id\":x_test_id.values})\nsub_df[\"target\"] = boost_prediction\nsub_df.to_csv(\"submission_boost.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2d4e3c3604c1da8f5ff87798f8d88283cea60cf4"},"cell_type":"code","source":"# Submission file for LigthGBM\nsub_df = pd.DataFrame({\"card_id\":x_test_id.values})\nsub_df[\"target\"] = lgb_prediction\nsub_df.to_csv(\"submission.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c9cc8f398b3b8f518f4d1bc50255829d1a330f04"},"cell_type":"markdown","source":"<a id=\"12\"></a> <br>\n# **12. Project Reflection**\n\nThis project was a great opportunity to apply concepts learned in class. Nevertheless there were a lot of struggles along the way. Compared to previous regression problems that were used for class, the elo competition provided a more complicated scenario. This was caused due to having five different data frames instead of just two. Therefore an understanding for the data itself needed to be created for. After the understanding that these different data frames needed to be combined the first real struggle appeared: as the data sets include huge numbers of data all actions on them were computationally expensive. First tries on zeno were unsuccessful as the amount of data got the server to crash. As the availability of the zeno server was generally often limited it was then decided to continue working in a kaggle kernel but also the kaggle kernels sometimes die due to the complete usage of the available RAM.\nThe next obstacle was the prediction model. Initial linear regression models returned poor results, inspiration was searched in kernels from other users and after different approaches it was decided to focus mainly on tree algorithms as they appear to be more suitable.\nThe last barrier was the joining of the tables and the creation of new features. The joining itself wasn't difficult technically but aggregate functions needed to be defined for each column. Creating new features was difficult as it wasn't easy to understand the content of the provided tables and also the existence of the merchants table did not make it easier as it was tried to involve the data but in the end there was no way found to incorporate it in a way that it contributes to the final result.\nAfter having joined all the data the last problem was that a lot of card_ids did not have any new transaction and therefore it resulted in a lot of missing values. Whether to ignore the new transactions or include and fill them needed to be analyzed and in case the filling was chosen it had to be decided with which values. Supprisingly the best result turned out to be just filling the missing values with zero as done in this kernel. Nevertheless there should be a better solution for this."}],"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}