{"cells":[{"metadata":{"_uuid":"8cd2b8bae516102fd1447dbe5af93fb3c4a38f9c"},"cell_type":"markdown","source":"# Feature Engineering + Stacking lgb\n**Nguyen Dang Minh, PhD**\n\n* [General functions and parameters](#general_function)\n* [Handling merchant data](#merchant_data)\n* [Handling transaction data](#transaction_data)\n* [Handling training and testing data](#training_data)\n* [Modeling](#modeling)"},{"metadata":{"_uuid":"6abd24c7959dcaaef97e5f52b2c8eb0caf8292f6"},"cell_type":"markdown","source":"***If you are looking for high accuracy submission, you've come to the wrong place!!! There are lots of other kernels out there with much higher leaderboard score. ***\n\n***But if you enjoy exploring, playing with data and try out different ideas, then c'mon in!!!***"},{"metadata":{"_uuid":"1af01b015b0c133b3f1f50ddd1603e9cb1346ae7"},"cell_type":"markdown","source":"## Summary\nIn this note book I will perform feature engineering and stacking ensemble on the [Elo merchant category recommendation](https://www.kaggle.com/c/elo-merchant-category-recommendation). Some part of the codes in this notebook is taken from [this excellent notebook](https://www.kaggle.com/fabiendaniel/elo-world) of [FabienDaniel](https://www.kaggle.com/fabiendaniel)\n\n### General model structure ###\nThere are two layers. The first layer has:\n* 2 lightgbm\n* 1 xgboost\n* 1 catboost\n* 1 dense neural network\n\nThe result of the first layer is fitted to a Lars Regression to give final prediction\n\n### Some experience after several trials\n* Merging news and historical transaction data does not affect the result.\n* Separate transaction data into authorized and un-authorized transaction does help, but very little\n* The root-mean-squared error metrics is very sensitive to extreme case. Thus, in this problem, handling outliers (either by clipping or removing them) make the performance worse\n* Discretizing continuous variables (either by decisition tree or binning) make the performance worse\n* The most significant feature is how recent the customer makes the purchase.\n* Adding some knowledge-based feature such as: weeks before christmast, weekend frequency, etc. does help\n* All new transaction are authorized"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport datetime\nimport gc\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nfrom sklearn.model_selection import StratifiedKFold, cross_val_score, KFold, GridSearchCV\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.tree import DecisionTreeRegressor\nfrom sklearn.preprocessing import StandardScaler\nfrom sklearn.linear_model import Ridge, Lasso, LarsCV, RidgeCV, Lars\nimport warnings\nimport random\nimport datetime\nfrom mlxtend.regressor import StackingCVRegressor\nfrom sklearn.ensemble import AdaBoostRegressor\nfrom sklearn.tree import DecisionTreeRegressor\nimport xgboost as xgb\nimport catboost as cb\nimport scipy\nfrom sklearn.cluster import DBSCAN\nfrom pandas.api.types import is_numeric_dtype\n\n\nfrom keras import Sequential\nfrom keras.layers import Input, Dense, Dropout, BatchNormalization\nfrom keras.callbacks import EarlyStopping, ModelCheckpoint\n\nwarnings.filterwarnings('ignore')\nnp.random.seed(1)\nrandom.seed(1)\nimport time\nimport gc","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d2a5ca4b3bdef8721219199fa32f8aa269b0a0ea","_kg_hide-input":true},"cell_type":"code","source":"from matplotlib import rcParams\nrcParams['figure.figsize'] = (8,4)\nrcParams['font.size'] = 12","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0a5382d4cfe387f3765b83e27cc4fb9b1533586d"},"cell_type":"markdown","source":"<a id='general_function'></a>\n\n## General functions and parameters"},{"metadata":{"trusted":true,"_uuid":"f6cd062118a4b6367524a458e90b41bec8f5a3f8"},"cell_type":"code","source":"DEBUG = False\nREF_DATE = datetime.datetime.strptime('2018-12-31', '%Y-%m-%d')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"df04c695a9e7cd33f607417113cb5087236612c2"},"cell_type":"code","source":"def skip_func(i, p=0.1, debug=DEBUG):\n    if debug == True:\n        return (i>0 and random.random()>p)\n    else:\n        return False","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5ca82b1e416084bd455edc07ed2a0cfd59d3b22f"},"cell_type":"code","source":"def 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":"ec4a0ca3468a03f78c71c23e4731c7e6523df848"},"cell_type":"code","source":"def print_null(df):\n    for col in df:\n        if df[col].isnull().any():\n            print('%s has %.0f null values: %.3f%%'%(col, df[col].isnull().sum(), df[col].isnull().sum()/df[col].count()*100))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"82e1cb8c4d33a65b8e4169d4bfea0ab946733748"},"cell_type":"code","source":"def impute_na(X_train, df, variable):\n    # make temporary df copy\n    temp = df.copy()\n    \n    # extract random from train set to fill the na\n    random_sample = X_train[variable].dropna().sample(temp[variable].isnull().sum(), random_state=0, replace=True)\n    \n    # pandas needs to have the same index in order to merge datasets\n    random_sample.index = temp[temp[variable].isnull()].index\n    temp.loc[temp[variable].isnull(), variable] = random_sample\n    return temp[variable]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b003444f6aacc37eab9c69858a37f62ebaaa5fc6"},"cell_type":"code","source":"# Clipping outliers\ndef clipping_outliers(X_train, df, var):\n    IQR = X_train[var].quantile(0.75)-X_train[var].quantile(0.25)\n    lower_bound = X_train[var].quantile(0.25) - 6*IQR\n    upper_bound = X_train[var].quantile(0.75) + 6*IQR\n    no_outliers = len(df[df[var]>upper_bound]) + len(df[df[var]<lower_bound])\n    print('There are %i outliers in %s: %.3f%%' %(no_outliers, var, no_outliers/len(df)))\n    df[var] = df[var].clip(lower_bound, upper_bound)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f26369ccb7f7357e6da4517ef716654f5dc4a88f"},"cell_type":"markdown","source":"<a id='merchant_data'></a>\n\n## Merchant data\n### Import and overview"},{"metadata":{"trusted":true,"_uuid":"22ab913de44d244afb7d89d3e3e90bffa5bd0423","_kg_hide-input":true},"cell_type":"code","source":"df_merchants = pd.read_csv('../input/merchants.csv', \n                            skiprows=lambda i: skip_func(i,p=1))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1bedbf1d8efabbb125b9c834ee000f8aff96f6a2","_kg_hide-input":true},"cell_type":"code","source":"df_merchants.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"64f93785216fb06fb8dbd7d47f1c27b7cdaf8b38","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"print('Merchant data types')\ndf_merchants.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2b3cb786c1abba32dec2a7947798b6d013ed783b","_kg_hide-input":true},"cell_type":"code","source":"df_merchants = df_merchants.replace([np.inf,-np.inf], np.nan)\nprint('Merchants null')\nprint_null(df_merchants)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d35452b595280d9cb74df585b526d00d66691a1e","_kg_hide-input":true},"cell_type":"code","source":"print('Merchants unique values')\ndf_merchants[['merchant_id','merchant_group_id','merchant_category_id','subsector_id','category_1','most_recent_sales_range','most_recent_purchases_range','merchant_category_id',\n         'active_months_lag3','active_months_lag6','category_4','category_2']].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1bef0df744bce6d17203a5bfe9596f1fb59954d2"},"cell_type":"markdown","source":"### Preprocessing\n\n#### Filling null"},{"metadata":{"_uuid":"f7b6108d5d8484aa05db4916f7369a7d559849e9"},"cell_type":"markdown","source":"Fill `ave_sales` with most frequent values. Fill `category_2` with random sampling from available data\n"},{"metadata":{"trusted":true,"_uuid":"d08a6904f8c3a4684df215e543f04132a8a2a322"},"cell_type":"code","source":"# Average sales null\nnull_cols = ['avg_purchases_lag3','avg_sales_lag3', 'avg_purchases_lag6','avg_sales_lag6','avg_purchases_lag12','avg_sales_lag12']\nfor col in null_cols:\n    df_merchants[col] = df_merchants[col].fillna(df_merchants[col].mean())\n\n# Category 2\ndf_merchants['category_2'] = impute_na(df_merchants, df_merchants, 'category_2')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"23a34d20a24e9663bfa2c39a38cfab4e508e3c8d"},"cell_type":"markdown","source":"#### Discretize and mapping data\n\nAll `avg_sales` and `avg_purchases` data is discretized into 5 categories, following the 5 categories of most recent values"},{"metadata":{"trusted":true,"_uuid":"ad01b085057749da41f34fda9df49ac0a30cc7ec"},"cell_type":"code","source":"# Sales cut\nsales_cut = df_merchants['most_recent_sales_range'].value_counts().sort_values(ascending=False).values\nsales_cut = sales_cut/np.sum(sales_cut)\nfor i in range(1,len(sales_cut)):\n    sales_cut[i] = sales_cut[i]+sales_cut[i-1]\n    \n# Purchases cut\npurchases_cut = df_merchants['most_recent_purchases_range'].value_counts().sort_values(ascending=False).values\npurchases_cut = purchases_cut/np.sum(purchases_cut)\nfor i in range(1,len(purchases_cut)):\n    purchases_cut[i] = purchases_cut[i]+purchases_cut[i-1]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"87a6716978446d2e255f13aa9f900fbc4284d77f"},"cell_type":"code","source":"# Discretize data\ndiscretize_cols = ['avg_purchases_lag3','avg_sales_lag3', 'avg_purchases_lag6','avg_sales_lag6','avg_purchases_lag12','avg_sales_lag12']\n\nfor col in discretize_cols:\n    categories = pd.qcut(df_merchants[col].values,sales_cut, duplicates='raise').categories.format()\n    df_merchants[col], intervals = pd.qcut(df_merchants[col], 5, labels=['A','B','C','D','E'], retbins=True, duplicates='raise')\n    print('Discretize for %s:'%col)\n    print(categories)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2cd487cbe13b23606fae83c94dc372c02763e864"},"cell_type":"code","source":"# Mapping data\ndf_merchants['category_1'] = df_merchants['category_1'].map({'Y':1, 'N':0})\ndf_merchants['category_4'] = df_merchants['category_4'].map({'Y':1, 'N':0})\n\nmap_cols = discretize_cols + ['most_recent_purchases_range', 'most_recent_sales_range']\nfor col in map_cols:\n    df_merchants[col] = df_merchants[col].map({'A':5,'B':4,'C':3,'D':2,'E':1})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f782e6d6faa90bb7448ba71c89839534125b8165"},"cell_type":"code","source":"numeric_cols = ['numerical_1','numerical_2']+map_cols\n\ncolormap = plt.cm.RdBu\nplt.figure(figsize=(12,12))\nsns.heatmap(df_merchants[numeric_cols].astype(float).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":"ec0e3d4f6cbb2d6a96f2ab630bea22fb2a665d0a"},"cell_type":"markdown","source":"#### Handling numerical data"},{"metadata":{"trusted":true,"_uuid":"ec3174176e3a0463d32ba47ef8913bef8fbc6da2"},"cell_type":"code","source":"numerical_cols = ['numerical_1','numerical_2']\nfor col in numerical_cols:\n    df_merchants = clipping_outliers(df_merchants, df_merchants, col)\n    plt.figure()\n    sns.distplot(df_merchants[col])\nprint('Unique values:')\nprint(df_merchants[numerical_cols].nunique())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"03b6965df8308895e2b7a4e82c8d5bbe3fc24107"},"cell_type":"markdown","source":"After clipping outliers, there are only 5 uniques values left in these two columns. Thus, we map them into 3 categories: the lowest: `0`, the middle: `1`, and the extreme: `2`"},{"metadata":{"trusted":true,"_uuid":"6cf70a4eb544db9fdcd399a3d1d88437127fd585"},"cell_type":"code","source":"for col in numerical_cols:\n    b = df_merchants[col].unique()\n    df_merchants[col] = df_merchants[col].apply(lambda x: 0 if x==b[0] else (1 if x in b[1:4] else 2))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"24c27e9e2cf80d4105616805888945c799d1828f"},"cell_type":"code","source":"df_merchants = df_merchants.drop(columns=['avg_purchases_lag3','avg_sales_lag3', 'avg_purchases_lag6','avg_sales_lag6'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5d60b9f284d5e674a63997f92297d7e2a65fedef"},"cell_type":"code","source":"df_merchants = reduce_mem_usage(df_merchants)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"27290971568714fb8ee645e48778c130f37fda91"},"cell_type":"code","source":"# Rename col\nfor col in df_merchants.columns:\n    if col != 'merchant_id':\n        df_merchants = df_merchants.rename(index=str, columns={col:'mer_'+col})","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"<a id='transaction_data'></a>\n\n## Transaction data"},{"metadata":{"_uuid":"aaa81d088e3154a879c10b3ace44d3c97681088f"},"cell_type":"markdown","source":"### Import and merge transaction data"},{"metadata":{"trusted":true,"_uuid":"0ccfe8783a571a7c359b14c1ff8ba3b5533433b4"},"cell_type":"code","source":"df_hist_trans = pd.read_csv('../input/historical_transactions.csv', \n                            skiprows=lambda i: skip_func(i), parse_dates=['purchase_date'])\n#df_new_trans = pd.read_csv('../input/new_merchant_transactions.csv', \n                           #skiprows=lambda i: skip_func(i), parse_dates=['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"090635985c4f4924d70a1070a45b984271b9ce4b"},"cell_type":"code","source":"df_hist_trans['days_to_date'] = ((REF_DATE - df_hist_trans['purchase_date']).dt.days)\ndf_hist_trans['days_to_date'] = df_hist_trans['days_to_date'] #+ df_hist_trans['month_lag']*30\n#df_new_trans['days_to_date'] = ((REF_DATE - df_new_trans['purchase_date']).dt.days)#//30\n#df_trans = pd.concat([df_hist_trans, df_new_trans])\ndf_trans = df_hist_trans\ndf_trans['months_to_date'] = df_trans['days_to_date']//30\ndf_trans = df_trans.drop(columns=['days_to_date'])\n\nif DEBUG == False:\n    del df_hist_trans#, df_new_trans\n    #gc.collect()\n\ndf_trans = reduce_mem_usage(df_trans)\n#df_trans = df_trans.sort_values(by=['purchase_date']).reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"61ad5ca1cdc65f4d802f764249c14a11aece70bc"},"cell_type":"code","source":"df_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"45c2be2de1587b4f5699356a1c4d2c3ac81b604f"},"cell_type":"code","source":"# Merge with merchant data\ndf_trans = pd.merge(df_trans, df_merchants, how='left', left_on='merchant_id', right_on='merchant_id')\n\n#if DEBUG == False:\n    #del df_merchants\n    #gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fd2e3165b9d4f28aa513de2211c5ad43c2bfd137"},"cell_type":"code","source":"df_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"_kg_hide-input":true,"trusted":true,"_uuid":"33f85240bcad48bbdf4f1de169d62b832b8c9a72"},"cell_type":"code","source":"for col in df_trans.columns:\n    if df_trans[col].nunique()<=15:\n        plt.figure()\n        sns.countplot(df_trans[col])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3111086af8aab27e0a71640f8e40eced6e20870e"},"cell_type":"code","source":"print('Null ratio')\nprint_null(df_trans)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d2dfc0b3752287d232f7b7a5eb6976d9dd53421b","_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"#print('Unique values')\n#df_trans[['card_id','city_id','category_1','city_id','category_1','installments','category_3','merchant_id','merchant_category_id',\n         #'month_lag','category_2','state_id','subsector_id','days_to_date']].nunique()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4e3f84e3d9b4330cac70b7ce6abc01c9cb266e47"},"cell_type":"markdown","source":"Some columns are duplicated and can be dropped"},{"metadata":{"trusted":true,"_uuid":"5ccb2304069733e1832d7f6df627604662980c8a"},"cell_type":"code","source":"# Drop duplicate columns\ndf_trans = reduce_mem_usage(df_trans)\ndf_trans = df_trans.drop(columns=['mer_city_id', 'mer_state_id', 'mer_category_1', 'mer_category_2',\n                          'mer_merchant_category_id','mer_subsector_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d61c31f04ff5dddc20d378913a5cd266f9cc3f7f"},"cell_type":"markdown","source":"### Filling null and encoding"},{"metadata":{"trusted":true,"_uuid":"17bbf0744d882d0ebd1c82056a012a7c497a65d9"},"cell_type":"code","source":"# Fill null by most frequent data\ndf_trans['category_2'].fillna(1.0,inplace=True)\ndf_trans['category_3'].fillna('A',inplace=True)\ndf_trans['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n\n# Fill null by random sampling\nnan_cols = df_trans.columns[df_trans.isna().any()].tolist()\nfor col in nan_cols:\n    df_trans[col] = impute_na(df_trans, df_trans, col)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a843ab85c973923a7a7749fbb3d7b377720bb320"},"cell_type":"code","source":"# Encoding\ndf_trans['authorized_flag'] = df_trans['authorized_flag'].map({'Y':1,'N':0})\ndf_trans['category_1'] = df_trans['category_1'].map({'Y':1,'N':0})\ndummies = pd.get_dummies(df_trans[['category_2', 'category_3']], prefix = ['cat_2','cat_3'], columns=['category_2','category_3'])\ndf_trans = pd.concat([df_trans, dummies], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2bc78035711f1ef339cbd037c65198e51cb50b7d"},"cell_type":"code","source":"df_trans.head()\ndf_trans = reduce_mem_usage(df_trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2d1b0acb4fd91fa53eeb6bc66097698d5c875501"},"cell_type":"markdown","source":"### Knowledge-based features\n\n* Weekend or not\n* Hour of the day: categorize into Morning (5 to 12), Afternoon (12 to 17), Evening (17 to 22) and Night (22 to 5) \n* Day of month: categorize into Early (<10), Middle (>10 and <20) and Late (>20)\n* Time to christmas 2017 and time to Black Friday 2017 (purchase amount increase significantly around this time)"},{"metadata":{"trusted":true,"_uuid":"821ec3feb8906405fa6a15150eaa8c7299299332"},"cell_type":"code","source":"df_trans['weekend'] = (df_trans['purchase_date'].dt.weekday >=5).astype(int)\ndf_trans['hour'] = df_trans['purchase_date'].dt.hour\ndf_trans['day'] = df_trans['purchase_date'].dt.day\ndf_trans['weeks_to_Xmas_2017'] = ((pd.to_datetime('2017-12-25') - df_trans['purchase_date']).dt.days//7).apply(lambda x: x if x>=0 and x<=8 else 8)\ndf_trans['weeks_to_BFriday'] = ((pd.to_datetime('2017-11-25') - df_trans['purchase_date']).dt.days//7).apply(lambda x: x if x>=0 and x<=3 else 3)\ndf_trans['price'] = df_trans['purchase_amount'] / df_trans['installments']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"91f84bdbe84b57f3f53360a281a4b3187544e4a9"},"cell_type":"code","source":"# Categorize time\ndef get_session(hour):\n    hour = int(hour)\n    if hour > 4 and hour < 12:\n        return 0\n    elif hour >= 12 and hour < 17:\n        return 1\n    elif hour >= 17 and hour < 21:\n        return 2\n    else:\n        return 3\n    \ndf_trans['hour'] = df_trans['hour'].apply(lambda x: get_session(x))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a5b06b097199a70a3e0a5b5d7d8624c928adcb4c"},"cell_type":"code","source":"# Categorize day\ndef get_day(day):\n    if day <= 10:\n        return 0\n    elif day <=20:\n        return 1\n    else:\n        return 2\n\ndf_trans['day'] = df_trans['day'].apply(lambda x: get_day(x))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9db849954b72f93b8a7959b1f528b99c124c29ae","_kg_hide-output":true,"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"'''\n**Does authorize flag matter?**\n\nIn lots of other kernels, transaction data are splitted into two: authorized transaction and  un-authorized transaction. Let's see if there's a significant impact in doing that\n    \nThere are no obvious difference between authorized and unauthorized transaction, thus we will NOT split transaction data into two separate sets.\n'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e9f499e9677679e65d06d8c1609821133f10d489","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"'''\n# Categorical features\ncompare_cols = ['category_1', 'category_2', 'category_3', 'installments', 'mer_most_recent_sales_range',\n               'mer_most_recent_purchases_range', 'mer_active_months_lag3', 'mer_active_months_lag6', 'mer_active_months_lag12',\n               'mer_category_4', 'weekend','hour']\nfor col in compare_cols:\n    fig = plt.figure()\n    sns.countplot(x=col, hue='authorized_flag', data=df_trans)\n'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5e810697e6a1192cbe6da587d2ab334afb57c117","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"'''\n# Numerical features\ncompare_cols = ['purchase_amount','months_to_date','mer_numerical_1','mer_numerical_2','mer_avg_sales_lag3',\n               'mer_avg_purchases_lag3', 'mer_avg_sales_lag6', 'mer_avg_purchases_lag6', 'mer_avg_sales_lag12',\n               'mer_avg_purchases_lag12']\nfor col in compare_cols:\n    fig = plt.figure()\n    temp_authorized = df_trans[col][df_trans['authorized_flag']==1]\n    temp_unauthorized = df_trans[col][df_trans['authorized_flag']==0]\n    sns.kdeplot(data=np.log(temp_unauthorized), label='unauthorized')\n    sns.kdeplot(data=np.log(temp_authorized), label='authorized')\n    plt.title('log-scale '+col)\n    \nif DEBUG==False:\n    del temp_authorized,temp_unauthorized\n    gc.collect()\n'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bba2531f01f53ae2f751c60cb045bf8cfb8e71ec"},"cell_type":"markdown","source":"### Aggregating features"},{"metadata":{"trusted":true,"_uuid":"c78434b4eb4554db4d076f728c1cc04cc35f0b3e"},"cell_type":"code","source":"def most_frequent(agg_df, df, col):\n    temp = df.groupby('card_id')[col].value_counts().index\n    agg_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5350f99f19e861b96c91e9125f2407defd165f5a"},"cell_type":"code","source":"def most_frequent(x):\n    return x.value_counts().index[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f20380cfdb2c5d7bf9d8b875ca87d38dc6117b9","_kg_hide-input":true},"cell_type":"code","source":"def aggregate_trans(df):\n    agg_func = {\n        'category_1': ['mean'],\n        'cat_2_1.0': ['mean'],\n        'cat_2_2.0': ['mean'],\n        'cat_2_3.0': ['mean'],\n        'cat_2_4.0': ['mean'],\n        'cat_2_5.0': ['mean'],\n        'cat_3_A': ['mean'],\n        'cat_3_B': ['mean'],\n        'cat_3_C': ['mean'],\n        'mer_numerical_1':['nunique','mean','std'],\n        'mer_most_recent_sales_range': ['mean','std'],\n        'mer_most_recent_purchases_range': ['mean','std'],\n        'mer_avg_sales_lag12':['mean','std'],\n        'mer_avg_purchases_lag12':['mean','std'],\n        'mer_active_months_lag12':['nunique'],\n        'merchant_id': ['nunique'],\n        'merchant_category_id': ['nunique'],\n        'state_id': ['nunique'],\n        'city_id': ['nunique'],\n        'subsector_id': ['nunique'],\n        'mer_merchant_group_id': ['nunique'],\n        'installments': ['sum','mean', 'max', 'min', 'std'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'weekend': ['mean', 'std'],\n        'hour': ['mean', 'std'],\n        'day': ['mean', 'std'],\n        'weeks_to_Xmas_2017': ['mean'],\n        'weeks_to_BFriday': ['mean'],\n        'purchase_date': ['count'],\n        'months_to_date': ['mean', 'max', 'min', 'std'],\n        'month_lag': ['mean', 'min', 'max']\n    }\n    # 'authorized_flag': ['mean', 'std'],\n    #'mer_category_4': ['mean'],\n    #'mer_avg_sales_lag6':['nunique', 'mean','std'],\n    #'mer_avg_purchases_lag6':['nunique', 'mean','std'],\n    #'months_to_date': ['mean', 'max', 'min', 'std'],\n    agg_df = df.groupby(['card_id']).agg(agg_func)\n    agg_df.columns = ['_'.join(col)for col in agg_df.columns.values]\n    agg_df.reset_index(inplace=True)\n    return agg_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"528b837e91f111f4edece66c990aa181753707ae","_kg_hide-input":true},"cell_type":"code","source":"def aggregate_per_month(df):\n    agg_func = {\n        'purchase_amount': ['count', 'sum', 'mean', 'min', 'max'],\n        'installments': ['sum', 'mean', 'min', 'max'],\n        'merchant_id': ['nunique'],\n        'state_id': ['nunique'],\n        'merchant_category_id': ['nunique'],\n        'subsector_id': ['nunique']\n    }\n    agg_df = df.groupby(['card_id','months_to_date']).agg(agg_func)\n    agg_df.columns = ['_'.join(col)for col in agg_df.columns.values]\n    agg_df.reset_index(inplace=True)\n    for col in agg_df.columns:\n        if col != 'card_id':\n            agg_df = agg_df.rename(index=str, columns={col:'monthly_'+col})\n    final_group = agg_df.groupby('card_id').agg(['mean', 'std'])\n    final_group.columns = ['_'.join(col)for col in final_group.columns.values]\n    final_group = final_group.drop(columns=['monthly_months_to_date_mean','monthly_months_to_date_std'])\n    final_group.reset_index(inplace=True)\n    return final_group","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5494d7674d09ffeecfa6879685cfc03c07af2e8d"},"cell_type":"markdown","source":"#### Splitting authorize and unthorize data"},{"metadata":{"trusted":true,"_uuid":"81c6efa12b2a627d537480e184ecb3cb644793a2"},"cell_type":"code","source":"\n# To split authorized and un-authorized data\nauth_trans = df_trans.groupby('card_id')['authorized_flag'].mean().reset_index()\ndf_trans_auth = df_trans[df_trans['authorized_flag']==1]\ndf_trans_unauth = df_trans[df_trans['authorized_flag']==0]\nif DEBUG==False:\n    del df_trans\n    gc.collect\n \n# Aggregate\nagg_df_auth = aggregate_trans(df_trans_auth)\nagg_df_auth_permonth = aggregate_per_month(df_trans_auth)\nagg_df_unauth = aggregate_trans(df_trans_unauth)\nagg_df_unauth_permonth = aggregate_per_month(df_trans_unauth)\n\nif DEBUG==False:\n    del df_trans_auth, df_trans_unauth\n    gc.collect\n    \n# Merging\nagg_df_auth = pd.merge(agg_df_auth, agg_df_auth_permonth, how='left', on='card_id')\nagg_df_auth = reduce_mem_usage(agg_df_auth)\nagg_df_unauth = pd.merge(agg_df_unauth, agg_df_unauth_permonth, how='left', on='card_id')\nagg_df_unauth = reduce_mem_usage(agg_df_unauth)\n\n# Replace null\nagg_df_auth = agg_df_auth.replace([np.inf,-np.inf], np.nan)\nagg_df_auth = agg_df_auth.fillna(value=0)\n\nagg_df_unauth = agg_df_unauth.replace([np.inf,-np.inf], np.nan)\nagg_df_unauth = agg_df_unauth.fillna(value=0)\nfor col in agg_df_unauth.columns:\n        if col != 'card_id':\n            agg_df_unauth = agg_df_unauth.rename(index=str, columns={col:'unauthorized_'+col})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"671a860c5f380fb4f68788da6689b36c47973118"},"cell_type":"code","source":"'''\n# Use when not splitting authorize/unauthorize\nagg_df = aggregate_trans(df_trans)\nagg_df_permonth = aggregate_per_month(df_trans)\nauth_trans = df_trans.groupby('card_id')['authorized_flag'].mean().reset_index()\nif DEBUG==False:\n    del df_trans\n    gc.collect\n\nagg_df = pd.merge(agg_df, agg_df_permonth, how='left', on='card_id')\nagg_df = reduce_mem_usage(agg_df)\n\nagg_df = agg_df.replace([np.inf,-np.inf], np.nan)\nprint_null(agg_df)\nagg_df = agg_df.fillna(value=0)\n'''","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8d927f3f9f9538e90c040fdd9c458c009806d55e"},"cell_type":"markdown","source":"### Repeat all previous step with new transaction data"},{"metadata":{"trusted":true,"_uuid":"603c9d8fc005c85fc092ffaaadd847e833b8a85f"},"cell_type":"code","source":"print('Importing new transaction data ...')\ndf_new_trans = pd.read_csv('../input/new_merchant_transactions.csv', \n                           skiprows=lambda i: skip_func(i), parse_dates=['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5722aa6408273d7461b20933b7b3a4909a575d59"},"cell_type":"code","source":"print('Processing new transaction data...')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c0837c520847390818f6eb4edd51b3e169d0d7f7"},"cell_type":"code","source":"#--------------- REDUCE MEM ----------------#\ndf_new_trans['days_to_date'] = ((REF_DATE - df_new_trans['purchase_date']).dt.days)#//30\ndf_trans = df_new_trans\ndel df_new_trans\ngc.collect()\ndf_trans['months_to_date'] = df_trans['days_to_date']//30\ndf_trans = df_trans.drop(columns=['days_to_date'])\ndf_trans = reduce_mem_usage(df_trans)\n\n#------------- MERGE WITH MERCHANT DATA--------------#\ndf_trans = pd.merge(df_trans, df_merchants, how='left', left_on='merchant_id', right_on='merchant_id')\n\nif DEBUG == False:\n    del df_merchants\n    gc.collect()\n\ndf_trans = reduce_mem_usage(df_trans)\ndf_trans = df_trans.drop(columns=['mer_city_id', 'mer_state_id', 'mer_category_1', 'mer_category_2',\n                          'mer_merchant_category_id','mer_subsector_id'])\n\n#------------- FILLING NULL AND ECODING ------------#\ndf_trans['category_2'].fillna(1.0,inplace=True)\ndf_trans['category_3'].fillna('A',inplace=True)\ndf_trans['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n\n# Fill null by random sampling\nnan_cols = df_trans.columns[df_trans.isna().any()].tolist()\nfor col in nan_cols:\n    df_trans[col] = impute_na(df_trans, df_trans, col)\n    \n# Encoding\ndf_trans['authorized_flag'] = df_trans['authorized_flag'].map({'Y':1,'N':0})\ndf_trans['category_1'] = df_trans['category_1'].map({'Y':1,'N':0})\ndummies = pd.get_dummies(df_trans[['category_2', 'category_3']], prefix = ['cat_2','cat_3'], columns=['category_2','category_3'])\ndf_trans = pd.concat([df_trans, dummies], axis=1)\ndf_trans = reduce_mem_usage(df_trans)\n\n#---------------- KNOWLEDGE-BASED FEATURES -----------------#\ndf_trans['weekend'] = (df_trans['purchase_date'].dt.weekday >=5).astype(int)\ndf_trans['hour'] = df_trans['purchase_date'].dt.hour\ndf_trans['day'] = df_trans['purchase_date'].dt.day\ndf_trans['weeks_to_Xmas_2017'] = ((pd.to_datetime('2017-12-25') - df_trans['purchase_date']).dt.days//7).apply(lambda x: x if x>=0 and x<=8 else 8)\ndf_trans['weeks_to_BFriday'] = ((pd.to_datetime('2017-11-25') - df_trans['purchase_date']).dt.days//7).apply(lambda x: x if x>=0 and x<=3 else 3)\ndf_trans['price'] = df_trans['purchase_amount'] / df_trans['installments']\ndf_trans['hour'] = df_trans['hour'].apply(lambda x: get_session(x))\ndf_trans['day'] = df_trans['day'].apply(lambda x: get_day(x))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d0d7e194ade737a1060de053fa3df0ca0c71982f","_kg_hide-input":true},"cell_type":"code","source":"'''\n# Not splitting authorized and un-authorized\n#---------------- AGGREGATING ----------------#\nagg_df_new = aggregate_trans(df_trans)\nagg_df_new_permonth = aggregate_per_month(df_trans)\nauth_trans_new = df_trans.groupby('card_id')['authorized_flag'].mean().reset_index()\nif DEBUG==False:\n    del df_trans\n    gc.collect\n\nagg_df_new = pd.merge(agg_df_new, agg_df_new_permonth, how='left', on='card_id')\nagg_df_new = reduce_mem_usage(agg_df_new)\nagg_df_new = agg_df.replace([np.inf,-np.inf], np.nan)\nprint_null(agg_df_new)\nagg_df_new = agg_df_new.fillna(value=0)\nfor col in agg_df_new.columns:\n        if col != 'card_id':\n            agg_df_new = agg_df_new.rename(index=str, columns={col:'new_'+col})\n            \nfor col in auth_trans_new.columns:\n        if col != 'card_id':\n            auth_trans_new = auth_trans_new.rename(index=str, columns={col:'new_'+col})\n'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4814923929c5102303ea7aca01cbc4b1539ae7d"},"cell_type":"code","source":"#---------------- AGGREGATING ----------------#\n# To split authorized and un-authorized data\nauth_trans_new = df_trans.groupby('card_id')['authorized_flag'].mean().reset_index()\ndf_trans_auth = df_trans[df_trans['authorized_flag']==1]\n#df_trans_unauth = df_trans[df_trans['authorized_flag']==0]\nif DEBUG==False:\n    del df_trans\n    gc.collect\n \n# Aggregate\nagg_df_auth_new = aggregate_trans(df_trans_auth)\nagg_df_auth_permonth_new = aggregate_per_month(df_trans_auth)\n#agg_df_unauth_new = aggregate_trans(df_trans_unauth)\n#agg_df_unauth_permonth_new = aggregate_per_month(df_trans_unauth)\n\nif DEBUG==False:\n    del df_trans_auth\n    gc.collect\n    \n# Merging\nagg_df_auth_new = pd.merge(agg_df_auth_new, agg_df_auth_permonth_new, how='left', on='card_id')\nagg_df_auth_new = reduce_mem_usage(agg_df_auth_new)\n#agg_df_unauth_new = pd.merge(agg_df_unauth_new, agg_df_unauth_permonth_new, how='left', on='card_id')\n#agg_df_unauth_new = reduce_mem_usage(agg_df_unauth_new)\n\n# Replace null\nagg_df_auth_new = agg_df_auth_new.replace([np.inf,-np.inf], np.nan)\nagg_df_auth_new = agg_df_auth_new.fillna(value=0)\n\n#agg_df_unauth_new = agg_df_unauth_new.replace([np.inf,-np.inf], np.nan)\n#agg_df_unauth_new = agg_df_unauth_new.fillna(value=0)\nfor col in agg_df_auth_new.columns:\n        if col != 'card_id':\n            agg_df_auth_new = agg_df_auth_new.rename(index=str, columns={col:'new_'+col})\n            \nfor col in auth_trans_new.columns:\n        if col != 'card_id':\n            auth_trans_new = auth_trans_new.rename(index=str, columns={col:'new_'+col})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f4e2bcc0d7601081c4d73f07102d3dff23639788"},"cell_type":"markdown","source":"<a id='traning_data'></a>\n\n## Training and testing data\n### Import and visualize"},{"metadata":{"trusted":true,"_uuid":"9f14f2ccf4cc6e166bb2e533cfbe618e5bbfccfb","_kg_hide-input":true},"cell_type":"code","source":"df_train = pd.read_csv('../input/train.csv', \n                            skiprows=lambda i: skip_func(i,p=1),parse_dates=['first_active_month'])\ndf_test = pd.read_csv('../input/test.csv',parse_dates=['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ec7018b42d2d5467a036507d1f96c55748bb701d","_kg_hide-input":true},"cell_type":"code","source":"df_test['first_active_month'] = impute_na(df_test, df_train, 'first_active_month')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4be64eda5eb3b603d8593ce7fb1c11d86d115d25","_kg_hide-input":true},"cell_type":"code","source":"cat_cols = ['feature_1','feature_2','feature_3']\nfor col in cat_cols:\n    fig = plt.figure()\n    sns.countplot(df_train[col])\n    plt.title(col)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"209163fd91847676df9d47aea8630d3b7591c1f1","_kg_hide-input":true},"cell_type":"code","source":"num_cols = ['target']\nfor col in num_cols:\n    fig = plt.figure()\n    sns.boxplot(df_train[col])\n    plt.title(col)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f426a34400330fa990abdedb631a1470b4c55a81"},"cell_type":"markdown","source":"### Features engineering"},{"metadata":{"_uuid":"9592f031ba8a2373d70168cbebb442ade1a22c88"},"cell_type":"markdown","source":"#### Detecting outliers\n\nThis code is taken from this [notebook](https://www.kaggle.com/chauhuynh/my-first-kernel-3-699) by [Chau Ngoc Huynh](https://www.kaggle.com/chauhuynh)"},{"metadata":{"trusted":true,"_uuid":"b623f2ab1c4838dfcb4a239950986a53f6db43af"},"cell_type":"code","source":"df_train['outliers'] = 0\ndf_train.loc[df_train['target'] < -30, 'outliers'] = 1\ndf_train['outliers'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c0890f4895f84828893780704791611155e0f09a"},"cell_type":"code","source":"a = df_train[df_train['target']<-30]['target']\nb = df_train[df_train['target']<-10]['target']\ntarget_min = df_train['target'].min()\ndf_min = df_train[df_train['target']==target_min]\n\nprint('There are %i target values smaller than -30' %len(a))\nprint('There are %i target values smaller than -12' %len(b))\nprint('There are %i customers having target value exactly at %.4f' %(len(df_min),target_min))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3d88f23e9739ba569a880d8bb3dc9bdcad6f327a"},"cell_type":"markdown","source":"I have tried to remove, or clip customers having that extreme target value, but it makes the performance worse. Thus, this is not a noise but an important feature. Applying this observation to the test set: all prediction below -10 will be set to -33.2."},{"metadata":{"trusted":true,"_uuid":"af6277663213e5472ffc9c642e9cac9167dc6a6c","_kg_hide-output":true},"cell_type":"code","source":"# Clip outliers\n#df_train['target'] = df_train['target'].clip(-30,20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d9a931e15f4abf24e48c9cba7b1ac67c09507e9b"},"cell_type":"markdown","source":"#### Merging with merchant data"},{"metadata":{"trusted":true,"_uuid":"fd894becaedd336474b53b5915297986a4c6f888"},"cell_type":"code","source":"'''\n# Not splitting authorized data\ndf_train = pd.merge(df_train, agg_df, on='card_id', how='left')\ndf_test = pd.merge(df_test, agg_df, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, agg_df_new, on='card_id', how='left')\ndf_test = pd.merge(df_test, agg_df_new, on='card_id', how='left')\n\nif DEBUG==False:\n    del agg_df_auth, agg_df_unauth\n    gc.collect\n'''\n\n# Splitting authorized data\ndf_train = pd.merge(df_train, agg_df_auth, on='card_id', how='left')\ndf_test = pd.merge(df_test, agg_df_auth, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, agg_df_unauth, on='card_id', how='left')\ndf_test = pd.merge(df_test, agg_df_unauth, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, agg_df_auth_new, on='card_id', how='left')\ndf_test = pd.merge(df_test, agg_df_auth_new, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, auth_trans, on='card_id', how='left')\ndf_test = pd.merge(df_test, auth_trans, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, auth_trans_new, on='card_id', how='left')\ndf_test = pd.merge(df_test, auth_trans_new, on='card_id', how='left')\n\nif DEBUG==False:\n    del agg_df_auth, agg_df_unauth, agg_df_auth_new\n    gc.collect\n\ndf_train['active_months'] = ((REF_DATE - df_train['first_active_month']).dt.days)//30\ndf_train['month_start'] = df_train['first_active_month'].dt.month\ndf_test['active_months'] = ((REF_DATE - df_test['first_active_month']).dt.days)//30\ndf_test['month_start'] = df_test['first_active_month'].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d3fcaea08b1f056760499570447319c762ebd56e"},"cell_type":"code","source":"print_null(df_train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c768cae3fcff1d210f6d8460c0b7dc9db64e5e9e","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"#df_train = pd.get_dummies(df_train, prefix=['feat1','feat2'],columns=['feature_1','feature_2'])\n#df_test = pd.get_dummies(df_test, prefix=['feat1','feat2'],columns=['feature_1','feature_2'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f0dad80fcae0e34cee63384ad8349531b881a62","_kg_hide-input":true},"cell_type":"code","source":"# Get numerical var\nnumerical = [var for var in df_train.columns if df_train[var].dtype!='O']\nprint('There are {} numerical variables'.format(len(numerical)))\n\n# Get discrete var\ndiscrete = []\nfor var in numerical:\n    if len(df_train[var].unique())<8:\n        discrete.append(var)\n        \nprint('There are {} discrete variables'.format(len(discrete)))\n\n# Get continuous var\ncontinuous = [var for var in numerical if var not in discrete and var not in ['card_id', 'first_active_month','target']]\nprint('There are {} continuous variables'.format(len(continuous)))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a4cdb1b03621bb8f8537319e949b7e6a5529e30b"},"cell_type":"markdown","source":"#### Filling null\n"},{"metadata":{"trusted":true,"_uuid":"108e3b41df8199a9eaef5381ffe1acf33a73b375","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"print('Null analysis of training data')\nprint_null(df_train)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"76d0f861b01c745eab20d60b8623682ff9671ed9"},"cell_type":"markdown","source":"Since null data is less than 10%, we apply the following strategies:\n* Continuous variables filled with 0\n* Categorical variables filled with random sampling"},{"metadata":{"trusted":true,"_uuid":"ed3599bb24d37e8ba262674317bc67b41335fed6"},"cell_type":"code","source":"# Detect all null columns\ntrain_null = df_train.columns[df_train.isnull().any()].tolist()\ntest_null = df_test.columns[df_test.isnull().any()].tolist()\n\nin_first = set(train_null)\nin_second = set(test_null)\n\nin_second_but_not_in_first = in_second - in_first\n\nnull_cols = train_null + list(in_second_but_not_in_first)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dae94d88d09ac2af020274ad2585ee51d0064bb5"},"cell_type":"code","source":"# Filling null\nfor col in null_cols:\n    if col in continuous:\n        df_train[col] = df_train[col].fillna(0)#df_train[col].astype(float).mean())\n        df_test[col] = df_test[col].fillna(0)#df_train[col].astype(float).mean())\n    if col in discrete:\n        df_train[col] = impute_na(df_train, df_train, col)\n        df_test[col] = impute_na(df_test, df_train, col)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"861d39aa3d3feeef7338c7e748f91bdfb8c1f4f5"},"cell_type":"markdown","source":"#### Dealing with outliers"},{"metadata":{"trusted":true,"_uuid":"52c45dad75d35e5eef8d1d280387634709dbc5a4","_kg_hide-input":true},"cell_type":"code","source":"# Discretize continuous variable\ndef tree_binariser(X_train, X_test, var):\n    score_ls = []\n\n    for tree_depth in [1,2,3,4]:\n        # call the model\n        tree_model = DecisionTreeRegressor(max_depth=tree_depth)\n\n        # train the model using 3 fold cross validation\n        scores = cross_val_score(tree_model, X_train[var].to_frame(), X_train['target'], cv=5, scoring='neg_mean_squared_error')\n        score_ls.append(np.mean(scores))\n\n    # find depth with smallest mse\n    depth = [1,2,3,4][np.argmax(score_ls)]\n    #print(score_ls, np.argmax(score_ls), depth)\n\n    # transform the variable using the tree\n    tree_model = DecisionTreeRegressor(max_depth=depth)\n    tree_model.fit(X_train[var].to_frame(), X_train['target'])\n    X_train[var] = tree_model.predict(X_train[var].to_frame())\n    #X_val[var] = tree_model.predict(X_val[var].to_frame())\n    X_test[var] = tree_model.predict(X_test[var].to_frame())\n    return X_train, X_test","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bc2d182527c32ea05b62f642e8cfa9f8629f7893"},"cell_type":"code","source":"print('Clipping outliers ...')\n#df_train = clipping_outliers(df_train, df_train, 'target')\n#for col in continuous:\n    #df_train, df_test = tree_binariser(df_train, df_test, col)\n    #df_test = clipping_outliers(df_train,df_test,col)\n    #df_train = clipping_outliers(df_train,df_train,col)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"39e74e85c3e1eea1fe92bba848a24d8d61e49365"},"cell_type":"code","source":"# Scaling\nfeatures = [c for c in df_train.columns if c not in ['card_id', 'first_active_month', 'target', 'outliers']]\nscaler = StandardScaler()\n#df_train[features] = scaler.fit_transform(df_train[features])\n#df_test[features] = scaler.transform(df_test[features])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9dd9aaccdf1e188de99460afe55d58dafe0c8e73","_kg_hide-input":true},"cell_type":"code","source":"if DEBUG==False:\n    df_train.to_csv('Train_final.csv')\n    df_test.to_csv('Test_final.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e5e4863ff0f784a9b918907395c5a6c8f2684d2f"},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"092dbb6a27d0c3ec7d582320f3d688092f76065c"},"cell_type":"markdown","source":"<a id='modeling'></a>\n\n## Modeling\n\nHere we use [out of fold stacking ensemble](https://www.analyticsvidhya.com/blog/2018/06/comprehensive-guide-for-ensemble-models/). The architecture is as followed:\n\n**Layer 1**:\n* 2 lightgbm\n* 1 xgboost\n* 1 catboost\n* 1 dense neural network\n\n**Layer 2**:\n* Lasso regression\n* Ridge regression\n"},{"metadata":{"trusted":true,"_uuid":"fccf6050cb044feae598977cb37afb62f604529c"},"cell_type":"code","source":"df_train = df_train.reset_index(drop=True)\ntarget = df_train['target']\ntrain = df_train.drop(columns=['target'])\ntest = df_test\n#if DEBUG == False:\n    #del df_train, df_test\n    #gc.collect","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c97eb36f6c18eacde14ee15e1d797111df8d8cc5"},"cell_type":"markdown","source":"### First layer\n#### Tree-based model"},{"metadata":{"trusted":true,"_uuid":"883c8336a77e146defbc92ebc4276147af1145cd","_kg_hide-input":true},"cell_type":"code","source":"# List of model to use\nif DEBUG == True:\n    ITERATIONS = 1\nelse:\n    ITERATIONS = 5000\nlgb1 = lgb.LGBMRegressor(num_leaves=111,\n                        max_depth=9,\n                        learning_rate=0.005,\n                        n_estimators=ITERATIONS,\n                        min_child_samples=149,\n                        subsample=0.71,\n                        subsample_freq=1,\n                        feature_fraction=0.75,\n                        reg_lambda=0.26,\n                        random_state=2016,\n                        n_jobs=4,\n                        metrics='rmse')\n\nlgb2 = lgb.LGBMRegressor(num_leaves=35,\n                        max_depth=-1,\n                        learning_rate=0.01,\n                        n_estimators=ITERATIONS,\n                        min_child_samples=27,\n                        subsample=0.9,\n                        subsample_freq=1,\n                        feature_fraction=0.9,\n                        reg_lambda=0.1,\n                        random_state=2017,\n                        n_jobs=4,\n                        metrics='rmse')\n\nxgb1 = xgb.XGBRegressor(max_depth=9,\n                       learning_rate=0.005,\n                       n_estimators=ITERATIONS,\n                       colsample_bytree=0.75,\n                       sub_sample=0.75,\n                       reg_lambda=0.1,\n                       n_jobs=4,\n                       random_state=2018)\n\ncb1 = cb.CatBoostRegressor(iterations=ITERATIONS, learning_rate=0.005, loss_function='RMSE', bootstrap_type='Bernoulli', depth=9, rsm=0.75, subsample=0.75, random_seed=2019, reg_lambda=1)\n\nada1 = AdaBoostRegressor(base_estimator=DecisionTreeRegressor(max_depth=8), n_estimators=ITERATIONS, learning_rate=0.007, loss='square', random_state=2019)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e9b8682c16601ea57fb80535f8cff0ba283ce80d"},"cell_type":"code","source":"if DEBUG==True:\n    N_FOLDS=2\nelse:\n    N_FOLDS=5\nlayer1_models = [lgb1, lgb2, xgb1, cb1]#, ada1]\nlayer1_names = ['lightgbm1', 'lightgbm2', 'xgboost1', 'catboost1']#, 'adaboost1']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3e422ec61d402e712fe983df24b4ecb00fb26057","_kg_hide-input":true},"cell_type":"code","source":"oof_train = np.zeros(shape=(len(train),len(layer1_models)))\noof_test = np.zeros(shape=(len(test),len(layer1_models)))\n\n# Recording results\nlayer1_score = []\nfeature_importance = []","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e52e62067c5e7340d22a69fd0ef93315ea86ff81"},"cell_type":"code","source":"for i in range(len(layer1_models)):\n    feature_importance_df = pd.DataFrame()\n    print('\\n')\n    name = layer1_names[i]\n    model = layer1_models[i]\n    folds = KFold(n_splits=N_FOLDS, shuffle=True, random_state=2019+i)\n    print('Training %s' %name)\n    for fold_, (trn_idx, val_idx) in enumerate(folds.split(train.values, target.values)):\n        print('Fold no %i/%i'%(fold_+1,N_FOLDS))\n        trn_data = train.iloc[trn_idx][features]\n        trn_label = target.iloc[trn_idx]\n        val_data = train.iloc[val_idx][features]\n        val_label = target.iloc[val_idx]\n        if 'ada' in name:\n            model.fit(X=trn_data, y=trn_label)\n        else:\n            model.fit(X=trn_data, y=trn_label,\n                     eval_set=[(trn_data, trn_label), (val_data, val_label)],\n                     verbose=200,\n                     early_stopping_rounds=100)\n\n        oof_train[val_idx,i] = model.predict(val_data)\n        oof_test[:,i] += model.predict(test[features])/N_FOLDS\n        \n        fold_importance_df = pd.DataFrame()\n        fold_importance_df[\"feature\"] = features\n        fold_importance_df[\"importance\"] = model.feature_importances_\n        fold_importance_df[\"fold\"] = fold_ + 1\n        feature_importance_df = pd.concat([feature_importance_df, fold_importance_df], axis=0)\n        \n    score = mean_squared_error(oof_train[:,i], target)**0.5\n    layer1_score.append(score)\n    feature_importance.append(feature_importance_df)\n    print('Training CV score: %.5f' %score)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea66194d015562091f32d6f1c0d5bf89693b7e69"},"cell_type":"code","source":"for i in range(len(layer1_models)):\n    feature_importance_df = feature_importance[i]\n    cols = (feature_importance_df[[\"feature\", \"importance\"]]\n        .groupby(\"feature\")\n        .mean()\n        .sort_values(by=\"importance\", ascending=False)[:25].index)\n\n    best_features = feature_importance_df.loc[feature_importance_df.feature.isin(cols)]\n\n    plt.figure(figsize=(12,12))\n    sns.barplot(x=\"importance\",\n                y=\"feature\",\n                data=best_features.sort_values(by=\"importance\",\n                                               ascending=False))\n    plt.title('%s Features (avg over folds)' % layer1_names[i])\n    plt.tight_layout()\n    plt.savefig('%s_importances.png' % layer1_names[i])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6e8c116f803514c5e401fa8fbd964f84435ab383"},"cell_type":"markdown","source":"#### Neural network"},{"metadata":{"trusted":true,"_uuid":"c3e4f3592cafb0ca9a306d80406948061466281e"},"cell_type":"code","source":"# Preparation\noof_train_nn = np.zeros(shape=(len(train),1))\noof_test_nn = np.zeros(shape=(len(test),1))\nscaler = StandardScaler()\nscaler.fit(train[features])\nX_train = scaler.transform(train.iloc[:][features].values)\nX_test = scaler.transform(test.iloc[:][features].values)\n\nX_train = pd.DataFrame(X_train, index=train[features].index, columns=train[features].columns)\nX_test = pd.DataFrame(X_test, index=test[features].index, columns=test[features].columns)\n\n\nif DEBUG == True:\n    EPOCHS=1\nelse:\n    EPOCHS=30 ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"10c67ec65417f7cf55121c68ac53faf24ea6d430"},"cell_type":"code","source":"def nn_model(input_shape):\n    model = Sequential()\n    model.add(Dense(128, input_dim = input_shape, activation='relu'))\n    model.add(Dropout(0.25))\n    model.add(Dense(128, activation='relu'))\n    model.add(BatchNormalization())\n    model.add(Dropout(0.2))\n    model.add(Dense(1))\n    model.compile(loss='mean_squared_error', optimizer='adam')\n    return model\n\nearly_stop = EarlyStopping(patience=5, verbose=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"18dbcae699e892c1bab619559d1cb1d62f99b572"},"cell_type":"code","source":"for fold_, (trn_idx, val_idx) in enumerate(folds.split(train.values, target.values)):\n    print('Fold no %i/%i'%(fold_+1,N_FOLDS))\n    trn_data = X_train.iloc[trn_idx][features]\n    trn_label = target.iloc[trn_idx]\n    val_data = X_train.iloc[val_idx][features]\n    val_label = target.iloc[val_idx]\n    model = nn_model(trn_data.shape[1])\n    hist = model.fit(trn_data,trn_label,\n                     validation_data = (val_data, val_label),\n                     epochs=EPOCHS, \n                     batch_size=512, \n                     verbose=True, \n                     callbacks=[early_stop])\n    oof_train_nn[val_idx,0] = model.predict(val_data)[:,0]\n    oof_test_nn[:,0] += model.predict(X_test[features])[:,0]/N_FOLDS","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3891d99d7e2e5a289d95927dba620fec8c686014"},"cell_type":"code","source":"score_nn = mean_squared_error(oof_train_nn, target)**0.5\nprint('Training CV score for neural network: %.5f' %score)\nlayer1_names.append('neural_net')\nlayer1_score.append(score_nn)\n\noof_train = np.hstack((oof_train, oof_train_nn))\noof_test = np.hstack((oof_test, oof_test_nn))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b8961f81ec04b90554d5e36d0177cf0645d855a6"},"cell_type":"markdown","source":"#### Layer 1 summary"},{"metadata":{"trusted":true,"_uuid":"f442a72a9eb2c91add955488c3c209e2e14697f7"},"cell_type":"code","source":"# Print first layer result\nlayer1 = pd.DataFrame()\nlayer1['models'] = layer1_names\nlayer1['CV_score'] = layer1_score\nlayer1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"01896a7b9e3031e39f208d552fee686181b6ffa9"},"cell_type":"code","source":"layer1_corr = pd.DataFrame()\nfor i in range(len(layer1_names)):\n    layer1_corr[layer1_names[i]] = oof_train[:,i]\nlayer1_corr['target'] = target\ncolormap = plt.cm.RdBu\nplt.figure(figsize=(12,12))\nsns.heatmap(layer1_corr.astype(float).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":"3d1025058574c71118ff479af923547a4036a3fc"},"cell_type":"markdown","source":"### Second layer"},{"metadata":{"trusted":true,"_uuid":"18dfb0e87854e90ae33f0933af01065062300c23","_kg_hide-input":true},"cell_type":"code","source":"# Setup the model\nridge = Ridge(alpha=0.5)#, fit_intercept=False)\nlasso = Lasso(alpha=0.5)\nlars = Lars(fit_intercept=False, positive=True)\nlayer2_models = [lars]#[ridge]# lasso]\nlayer2_names = ['Lars']#['ridge'] #, 'lasso']\n#params_grid = {'alpha':[0.05,0.1,0.4,1.0]}\n\n# Setup to record result\ntrain_pred = np.zeros(len(train))\ntest_pred = np.zeros(len(test))\n\nlayer2 = pd.DataFrame()\nlayer2['models'] = layer2_names\nlayer2_score = []","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"75059c60d4056e1caf4018938e2d7f7512a6314d"},"cell_type":"code","source":"# For regression\n\nfor i in range(len(layer2_models)):\n    print('\\n')\n    name = layer2_names[i]\n    model = layer2_models[i]\n    print('Training %s' %name)\n    #model, score = do_regressor((oof_train, target), model=model, parameters=params_grid)\n    model.fit(oof_train, target)\n    score = mean_squared_error(model.predict(oof_train), target)**0.5\n    train_pred += model.predict(oof_train)/len(layer2_models)\n    test_pred += model.predict(oof_test)/len(layer2_models)\n    layer2_score.append(score)\n    print('Training score: %.5f' % score)\n\n#layer2['CV score'] = layer2_score\n#layer2\n\nlayer2_coef = pd.DataFrame()\nlayer2_coef['Name'] = layer1_names\nlayer2_coef['Coefficient'] = model.coef_\n#layer2_coef['Coefficient'] = coef\nlayer2_coef","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e41a01c2e2f256ba00678f0bfa3f93cbfe3d33b8"},"cell_type":"code","source":"'''\n# Taking average\ntrain_pred = np.mean(oof_train, axis=1)\ntest_pred = np.mean(oof_test, axis=1)\nprint('Training score: %.5f' %mean_squared_error(train_pred, target)**0.5)\n'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9cfb750ba870534ffbeeed38950fe9c51b6b3e8b"},"cell_type":"code","source":"#np.sum(model.coef_)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"29066ed554eb1dc196760fb8e17fc52e200b75c4"},"cell_type":"code","source":"plt.figure(figsize=(8,5))\nplt.scatter(range(len(test_pred)), np.sort(test_pred))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('Loyalty Score', fontsize=12)\nplt.title('Loyalty score before scaling')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"037b8320701f902bc2ee267984139a77fb6f9ade"},"cell_type":"code","source":"# Refit to the target\ntrain_scaler = StandardScaler()\n#train_scaler.fit(target.values.reshape(-1,1))\n#test_pred = train_scaler.inverse_transform(test_pred.reshape(-1,1))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ff7f363d085cc804fa75c2c7bad38610080fa3a8"},"cell_type":"code","source":"# Pushing min loyalty to -33.2\nfor i in range(len(test_pred)):\n    if test_pred[i]<-12:\n        test_pred[i] = target_min\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b0c8d8acfce5acb8ad10506e4ef1d42bc240bd25"},"cell_type":"markdown","source":"### Data inferences\n\nHere we see the distribution of the most important features and how they are different between highly loyal and highly un-loyal customer"},{"metadata":{"trusted":true,"_uuid":"a4d342bfffbaa0a28cfcdb82fe5046a63bb0156e"},"cell_type":"code","source":"important_features = ['months_to_date_mean', 'months_to_date_min', 'new_months_to_date_mean', 'category_1_mean', 'new_month_lag_mean', 'new_purchase_amount_max',\n                      'feature_1','feature_2', 'cat_2_1.0_mean', 'mer_numerical_1_mean', 'active_months', 'monthly_purchase_amount_max_mean', 'new_monthly_purchase_amount_max_mean']\ndf_important = df_train[important_features+['target']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"737d5cf9c89522031f5672d5212aed0126380182"},"cell_type":"code","source":"low_limit = -8\nhigh_limit = 5\ndef discretize(x):\n    if x<low_limit:\n        return -1\n    if x>high_limit:\n        return 1\n    else:\n        return 0\n    \ndf_important['target'] = df_important['target'].apply(lambda x: discretize(x))\ndf_important = df_important[df_important['target']!=0]\ndf_important = df_important.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ce9189c2dcb78af5beb08ad31ac74f2e90dfda2c"},"cell_type":"code","source":"print('There are %i customers that has highly positive target' % len(df_important[df_important['target']==1]))\nprint('There are %i customers that has highly negative target' % len(df_important[df_important['target']==-1]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6e5a70da4c217467e02759fa4a25c045d275285a"},"cell_type":"code","source":"for col in important_features:\n    if col not in ['feature_1', 'feature_2']:\n        fig=plt.figure(figsize=(8,4))\n        sns.kdeplot(df_important[df_important['target']==1][col], label='highly_positive')\n        sns.kdeplot(df_important[df_important['target']==-1][col], label='highly_negative')\n        #sns.kdeplot(df_important[df_important['target']==0][col], label='neutral')\n        plt.title(col)\n    else:\n        fig=plt.figure(figsize=(8,4))\n        sns.countplot(x=col, hue='target', data=df_important)\n        plt.title(col)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"61f5c9202172b3e6b90fd9bc2c1982219e6ecd64"},"cell_type":"markdown","source":"### Submission"},{"metadata":{"trusted":true,"_uuid":"e6cf79c20304e868c09dfd7d6610fc3eb32853ab"},"cell_type":"code","source":"sub_df = pd.DataFrame({\"card_id\":test[\"card_id\"].values})\nsub_df[\"target\"] = test_pred\nsub_df.to_csv(\"submit.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"70e785327f87a8416fcf8afa29ae1f1c2c14914d"},"cell_type":"code","source":"plt.figure(figsize=(8,5))\nplt.scatter(range(sub_df.shape[0]), np.sort(sub_df['target'].values))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('Loyalty Score', fontsize=12)\nplt.title('Loyalty score after scaling')\nplt.show()","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}