{"cells":[{"metadata":{"_uuid":"c24e5776cc4aa12466025c756f1837f6d0e6eff3"},"cell_type":"markdown","source":"## General information\n\nThis kernel is dedicated to EDA of Elo Merchant Category Recommendation competition as well as feature engineering.\n\nIn this dataset we can see clients who use Elo and their transactions. We need to predict the loyalty score for each card_id.\n\nWork in progress.\n\n![](https://storage.googleapis.com/kaggle-competitions/kaggle/10445/logos/thumb76_76.png?t=2018-10-24-17-14-05)"},{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport os\nimport seaborn as sns \nimport matplotlib.pyplot as plt\n%matplotlib inline\nplt.style.use('ggplot')\nimport lightgbm as lgb\nimport xgboost as xgb\nimport time\nimport datetime\n\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import StratifiedKFold, KFold\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.linear_model import Ridge, RidgeCV\nimport gc\nfrom catboost import CatBoostRegressor\n\nimport plotly.offline as py\npy.init_notebook_mode(connected=True)\nimport plotly.graph_objs as go\nimport plotly.tools as tls\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n\npd.set_option('max_colwidth', 500)\npd.set_option('max_columns', 500)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"%%time\ntrain = pd.read_csv('../input/train.csv', parse_dates=['first_active_month'])\ntest = pd.read_csv('../input/test.csv', parse_dates=['first_active_month'])\nsubmission = pd.read_csv('../input/sample_submission.csv')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_uuid":"e7d2cc4221d5da6ad7d9acd81ea0b5dddb8e6289","trusted":true},"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":{"_uuid":"05979744f8371569da70769e6da5f0985dce4466"},"cell_type":"markdown","source":"## Main data exploration\nLet's have a look at data"},{"metadata":{"_uuid":"a9f03d9b87f0edb2645bdd0c3bbd4e47c4e51e2f","trusted":true},"cell_type":"code","source":"e = pd.read_excel('../input/Data_Dictionary.xlsx', sheet_name='train')\ne","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_uuid":"ffc8c37bd610a93a06bb410d9e9112cd742d83ce","trusted":true},"cell_type":"code","source":"train['feature_1'] = train['feature_1'].astype('category')\ntrain['feature_2'] = train['feature_2'].astype('category')\ntrain['feature_3'] = train['feature_3'].astype('category')\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8d58b745566c34c38564a458c5c51dd61c7f5ce7","trusted":true},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"31b986f0a87cd0518f681206a0696d53d1c1d03d"},"cell_type":"markdown","source":"We have a date column, three anonymized categorical columns and target."},{"metadata":{"_uuid":"6cb855bcdd6538fc96570712c7cb8496c135419a"},"cell_type":"markdown","source":"### Features 1, 2, 3"},{"metadata":{"_kg_hide-input":true,"_uuid":"7daf55733e99f9a2490cb0325d6c54f73a0e1fe2","trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (16, 6))\nplt.suptitle('Violineplots for features and target');\nsns.violinplot(x=\"feature_1\", y=\"target\", data=train, ax=ax[0], title='feature_1');\nsns.violinplot(x=\"feature_2\", y=\"target\", data=train, ax=ax[1], title='feature_2');\nsns.violinplot(x=\"feature_3\", y=\"target\", data=train, ax=ax[2], title='feature_3');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a0c7d8cedffdc82ee4dbe8265a47cbe85c30a3d9","trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (16, 6));\ntrain['feature_1'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='feature_1');\ntrain['feature_2'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='feature_2');\ntrain['feature_3'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='feature_3');\nplt.suptitle('Counts of categiories for features');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"682502fc8974ec4f093bd93d705c67bc5bdc8c21"},"cell_type":"markdown","source":"These two plots show an important idea: while different categories of these features could have various counts, the distribution of target is almost the same. This could mean, that these features aren't really good at predicting target - we'll need other features and feature engineering.\nAlso it is worth noticing that mean target values of each catogory of these features is near zero. This could mean that data was sampled from normal distribution."},{"metadata":{"_kg_hide-input":true,"_uuid":"44a80af3c1b1f1a601b5364fe0007921646f4069","trusted":true},"cell_type":"code","source":"test['feature_1'] = test['feature_1'].astype('category')\ntest['feature_2'] = test['feature_2'].astype('category')\ntest['feature_3'] = test['feature_3'].astype('category')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d61aa307d06944cb99dbcc392242f6e3572f358c"},"cell_type":"markdown","source":"### date"},{"metadata":{"_kg_hide-input":true,"_uuid":"64597710f9babb917b974cf44b0a105bb9419029","trusted":true},"cell_type":"code","source":"d1 = train['first_active_month'].value_counts().sort_index()\nd2 = test['first_active_month'].value_counts().sort_index()\ndata = [go.Scatter(x=d1.index, y=d1.values, name='train'), go.Scatter(x=d2.index, y=d2.values, name='test')]\nlayout = go.Layout(dict(title = \"Counts of first active\",\n                  xaxis = dict(title = 'Month'),\n                  yaxis = dict(title = 'Count'),\n                  ),legend=dict(\n                orientation=\"v\"))\npy.iplot(dict(data=data, layout=layout))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"83d3cd04490e0c01954364925f294320594db825"},"cell_type":"markdown","source":"Trends of counts for train and test data are similar, and this is great.\nWhy there is such a sharp decline at the end of the period? I think it was on purpose. Or maybe new cards are taken into account only after fulfilling some conditions. "},{"metadata":{"_uuid":"5c9104e4bfe927de597a5d4ae8386601497444ee"},"cell_type":"markdown","source":"Also there is one line with a missing data in test. I'll fill in with the first data, having the same values of features."},{"metadata":{"_kg_hide-input":true,"_uuid":"7de538f601aeb343699378407bbd2f5bc5d8599b","trusted":true},"cell_type":"code","source":"test.loc[test['first_active_month'].isna(), 'first_active_month'] = test.loc[(test['feature_1'] == 5) & (test['feature_2'] == 2) & (test['feature_3'] == 1), 'first_active_month'].min()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b122c20d0d0ea655c870bd680c6a1b8d667ac00"},"cell_type":"markdown","source":"### target"},{"metadata":{"_uuid":"8518aa54cd20681ad6523fe7594e845d9a8d5b45","trusted":true},"cell_type":"code","source":"plt.hist(train['target']);\nplt.title('Target distribution');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"492d42cc2ec8fa059f500d917d9bd3d64d61529f"},"cell_type":"markdown","source":"This looks really strange!"},{"metadata":{"_uuid":"d4895635e7c1d7badb4b0a44a2e3a17f11a91dbe","scrolled":true,"trusted":true},"cell_type":"code","source":"print('There are {0} samples with target lower than -20.'.format(train.loc[train.target < -20].shape[0]))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3eba2f232449fee34285d5ca8b34da760938c254"},"cell_type":"markdown","source":"And they have 1 unique value: -33.21928095.\nThis seems to be a special case. Maybe it would be reasonable to simply exclude these samples. We'll try later."},{"metadata":{"_uuid":"53922f8b0bed06b45ca0cb7e86d2d7f2bb77f13c"},"cell_type":"markdown","source":"### Feature engineering"},{"metadata":{"_uuid":"8860a423f8210194f87230ed8b128122951ce611","trusted":true},"cell_type":"code","source":"max_date = train['first_active_month'].dt.date.max()\ndef process_main(df):\n    date_parts = [\"year\", \"weekday\", \"month\"]\n    for part in date_parts:\n        part_col = 'first_active_month' + \"_\" + part\n        df[part_col] = getattr(df['first_active_month'].dt, part).astype(int)\n            \n    df['elapsed_time'] = (max_date - df['first_active_month'].dt.date).dt.days\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dbb2fe24f5ec2792adc71edc012635e3edc65b96","trusted":true},"cell_type":"code","source":"train = process_main(train)\ntest = process_main(test)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e1368ad6720235a592b02ceb134fee2081324753"},"cell_type":"markdown","source":"## historical_transactions\nUp to 3 months' worth of historical transactions for each card_id"},{"metadata":{"_uuid":"b5a7d140d2ccfd4c26c6d31e45beba1bb82ec59a","trusted":true},"cell_type":"code","source":"historical_transactions = pd.read_csv('../input/historical_transactions.csv')\ne = pd.read_excel('../input/Data_Dictionary.xlsx', sheet_name='history')\ne","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ede3696d9c49284f731d61e2003b6d12901b663a","trusted":true},"cell_type":"code","source":"print(f'{historical_transactions.shape[0]} samples in data')\nhistorical_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"78bf7fa2ff4b117c618e57c89550f21fae394acf","trusted":true},"cell_type":"code","source":"# let's convert the authorized_flag to a binary value.\nhistorical_transactions['authorized_flag'] = historical_transactions['authorized_flag'].apply(lambda x: 1 if x == 'Y' else 0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dd6854d829cd47af5ca1f64ead34302162e3591d","trusted":true},"cell_type":"code","source":"print(f\"At average {historical_transactions['authorized_flag'].mean() * 100:.4f}% transactions are authorized\")\nhistorical_transactions['authorized_flag'].value_counts().plot(kind='barh', title='authorized_flag value counts');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"77d0bc7516192a093596aada62144b396398f058"},"cell_type":"markdown","source":"#### Cards with lowest and highest percentage of authorized transactions"},{"metadata":{"_uuid":"b71bcd401b4676e68e9d63a10b35261823bf0c47","trusted":true},"cell_type":"code","source":"autorized_card_rate = historical_transactions.groupby(['card_id'])['authorized_flag'].mean().sort_values()\nautorized_card_rate.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5f3f174445643cb89217bbb50643a6ab3d7daa88","trusted":true},"cell_type":"code","source":"autorized_card_rate.tail()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3b51dca37359a83b61de319f3e21b1747374f550"},"cell_type":"markdown","source":"It seems that there are some cards, for which most of transactions were declined. Were this fraud transactions?"},{"metadata":{"_uuid":"9b1557084710171b4f1678beddac984278db62de"},"cell_type":"markdown","source":"### installments"},{"metadata":{"_uuid":"77c6435989bcfcb0a2ea4ce799f6c5084489fba2","trusted":true},"cell_type":"code","source":"historical_transactions['installments'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b80ea624d98fc9a623ca503ce65f9279038abd4"},"cell_type":"markdown","source":"Interesting. Most common number of installments are 0 and 1 which is expected. But -1 and 999 are strange. I think that these values were used to fill in missing values."},{"metadata":{"_uuid":"e7c5b683028e4eb0e5f8630eddd969a3394f4c85","trusted":true},"cell_type":"code","source":"historical_transactions.groupby(['installments'])['authorized_flag'].mean()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"287ed174beb856d10ed027964f723178c5806c91"},"cell_type":"markdown","source":"On the other hand it seems that `999` could mean fraud transactions, considering only 3% of these transactions were approved. One more interesting thing is that the higher the number of installments is, the lower is the approval rate."},{"metadata":{"_uuid":"3a86d52e2adf506ddf1aa5f0896b4d1c92bdb11d","trusted":true},"cell_type":"code","source":"historical_transactions['installments'] = historical_transactions['installments'].astype('category')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d6214bc2c3ab55023afd30b741d2b58cee926fb5","trusted":true},"cell_type":"code","source":"historical_transactions['purchase_date'] = pd.to_datetime(historical_transactions['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"54656298ce661b953b7f44b0ff95270810779626"},"cell_type":"markdown","source":"### purchase_amount\nSadly purchase_amount is normalized. Let's have a look at it nevertheless."},{"metadata":{"_uuid":"9c7bf0f10585eaeb90b6c0b461f369b943e17440","trusted":true},"cell_type":"code","source":"plt.title('Purchase amount distribution.');\nhistorical_transactions['purchase_amount'].plot(kind='hist');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"17f5e16a06e78bfdebfe753aeffd096580413a09","trusted":true},"cell_type":"code","source":"for i in [-1, 0]:\n    n = historical_transactions.loc[historical_transactions['purchase_amount'] < i].shape[0]\n    print(f\"There are {n} transactions with purchase_amount less than {i}.\")\nfor i in [0, 10, 100]:\n    n = historical_transactions.loc[historical_transactions['purchase_amount'] > i].shape[0]\n    print(f\"There are {n} transactions with purchase_amount more than {i}.\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ec7d52ea0e15977fb913904d9c2ca494b7c40e48","trusted":true},"cell_type":"code","source":"plt.title('Purchase amount distribution for negative values.');\nhistorical_transactions.loc[historical_transactions['purchase_amount'] < 0, 'purchase_amount'].plot(kind='hist');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ccd8ca9632bef1b30f474528283c7ef1612ecc34"},"cell_type":"markdown","source":"It seems that almost all transactions have purchase amount in range (-1, 0). Quite a strong normalization and high outliers, which will need to be processed."},{"metadata":{"_uuid":"93c68d7121a83006230a277e0501fc1d9f610b30"},"cell_type":"markdown","source":"### Categories"},{"metadata":{"_uuid":"f1cb7f38250f3023103fa99410f0ade779dc54ec","trusted":true},"cell_type":"code","source":"map_dict = {'Y': 0, 'N': 1}\nhistorical_transactions['category_1'] = historical_transactions['category_1'].apply(lambda x: map_dict[x])\nhistorical_transactions.groupby(['category_1']).agg({'purchase_amount': ['mean', 'std', 'count'], 'authorized_flag': ['mean', 'std']})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4cc2120b43e44b47a821b6e6ae23be1d0236256e","trusted":true},"cell_type":"code","source":"historical_transactions.groupby(['category_2']).agg({'purchase_amount': ['mean', 'std', 'count'], 'authorized_flag': ['mean', 'std']})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2775dc98625d93000dd0f9e432800d3f68e4c1ca","trusted":true},"cell_type":"code","source":"map_dict = {'A': 0, 'B': 1, 'C': 2, 'nan': 3}\nhistorical_transactions['category_3'] = historical_transactions['category_3'].apply(lambda x: map_dict[str(x)])\nhistorical_transactions.groupby(['category_3']).agg({'purchase_amount': ['mean', 'std', 'count'], 'authorized_flag': ['mean', 'std']})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"267b58ab48bce9022961fd083f469aa867e0ece6"},"cell_type":"markdown","source":"All categories are quite different"},{"metadata":{"_uuid":"040874127f458f2fe9473fa6eecce5da209b6d29","trusted":false},"cell_type":"code","source":"for col in ['city_id', 'merchant_category_id', 'merchant_id', 'state_id', 'subsector_id']:\n    print(f\"There are {historical_transactions[col].nunique()} unique values in {col}.\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"53922f8b0bed06b45ca0cb7e86d2d7f2bb77f13c"},"cell_type":"markdown","source":"### Feature engineering"},{"metadata":{"_uuid":"8ec706c5dd0bc14fb05afd2ae613f344d4cf3b56","collapsed":true,"trusted":false},"cell_type":"code","source":"def aggregate_historical_transactions(trans, prefix):\n    trans['purchase_month'] = trans['purchase_date'].dt.month\n    trans['installments'] = trans['installments'].astype(int)\n    trans['month_diff'] = ((datetime.datetime.today() - trans['purchase_date']).dt.days)//30\n    trans['month_diff'] += trans['month_lag']\n    trans.loc[:, 'purchase_date'] = pd.DatetimeIndex(trans['purchase_date']). \\\n                                        astype(np.int64) * 1e-9\n    trans = pd.get_dummies(trans, columns=['category_2', 'category_3'])\n    agg_func = {\n        'authorized_flag': ['sum', 'mean'],\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_1': ['sum', 'mean'],\n        'category_3_2': ['sum', 'mean'],\n        'category_3_3': ['sum', 'mean'],\n        'merchant_id': ['nunique'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'installments': ['sum', 'mean', 'max', 'min', 'std'],\n        'purchase_month': ['mean', 'max', 'min', 'std'],\n        'purchase_date': [np.ptp, 'max', 'min'],\n        'month_lag': ['min', 'max'],\n        'merchant_category_id': ['nunique'],\n        'state_id': ['nunique'],\n        'subsector_id': ['nunique'],\n        'city_id': ['nunique'],\n    }\n    agg_trans = trans.groupby(['card_id']).agg(agg_func)\n    agg_trans.columns = [prefix + '_'.join(col).strip() for col in agg_trans.columns.values]\n    agg_trans.reset_index(inplace=True)\n\n    df = (trans.groupby('card_id')\n          .size()\n          .reset_index(name='{}transactions_count'.format(prefix)))\n\n    agg_trans = pd.merge(df, agg_trans, on='card_id', how='left')\n\n    return agg_trans","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f8ef0a80328da5b89c1604e573801e88b0b94768","collapsed":true,"trusted":false},"cell_type":"code","source":"def aggregate_per_month(history):\n    grouped = history.groupby(['card_id', 'month_lag'])\n    history['installments'] = history['installments'].astype(int)\n    agg_func = {\n            'purchase_amount': ['count', 'sum', 'mean', 'min', 'max', 'std'],\n            'installments': ['count', 'sum', 'mean', 'min', 'max', 'std'],\n            }\n\n    intermediate_group = grouped.agg(agg_func)\n    intermediate_group.columns = ['_'.join(col).strip() for col in intermediate_group.columns.values]\n    intermediate_group.reset_index(inplace=True)\n\n    final_group = intermediate_group.groupby('card_id').agg(['mean', 'std'])\n    final_group.columns = ['_'.join(col).strip() for col in final_group.columns.values]\n    final_group.reset_index(inplace=True)\n    \n    return final_group\n\nfinal_group = aggregate_per_month(historical_transactions) ","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6ba4bd3e8e698428e9e0fd7527b9e50132553783","scrolled":true,"trusted":false},"cell_type":"code","source":"%%time\ndel d1, d2, autorized_card_rate\ngc.collect()\nhistorical_transactions = reduce_mem_usage(historical_transactions)\nhistory = aggregate_historical_transactions(historical_transactions, prefix='hist_')\ngc.collect()\ntrain = pd.merge(train, history, on='card_id', how='left')\ntest = pd.merge(test, history, on='card_id', how='left')\ndel history","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d7d1a5d0969fe9f51b12bd3b0368e4e6563aa464","trusted":false},"cell_type":"code","source":"del historical_transactions\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"89325dfaad592bafb4f8f018f840886276851693"},"cell_type":"markdown","source":"## new_merchant_transactions \nTwo months' worth of data for each card_id containing ALL purchases that card_id made at merchant_ids that were not visited in the historical data."},{"metadata":{"_uuid":"28236873ca4589e3a329c0981bb850db4d821cef","trusted":false},"cell_type":"code","source":"new_merchant_transactions = pd.read_csv('../input/new_merchant_transactions.csv')\ne = pd.read_excel('../input/Data_Dictionary.xlsx', sheet_name='new_merchant_period')\ne","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5ecf310953907e236a92677bdc5a1e39f8e4e15c","trusted":false},"cell_type":"code","source":"print(f'{new_merchant_transactions.shape[0]} samples in data')\nnew_merchant_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6e9ae820b5a8f2ad342ed20344c64f558a7f1cbb","collapsed":true,"trusted":false},"cell_type":"code","source":"# let's convert the authorized_flag to a binary value.\nnew_merchant_transactions['authorized_flag'] = new_merchant_transactions['authorized_flag'].apply(lambda x: 1 if x == 'Y' else 0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b7be1ffd229013a448d85a83626b9f19dee5f79","trusted":false},"cell_type":"code","source":"print(f\"At average {new_merchant_transactions['authorized_flag'].mean() * 100:.4f}% transactions are authorized\")\nnew_merchant_transactions['authorized_flag'].value_counts().plot(kind='barh', title='authorized_flag value counts');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"498e32a8e927d8c9934f259a0991d34f6c6c65cd"},"cell_type":"markdown","source":"In contrast with historical data, **all** transactions here were authorized!"},{"metadata":{"_uuid":"c72ee258b00d5348639011bf77ca3c911825c64b"},"cell_type":"markdown","source":"#### Cards with lowest and highest total purchase amount"},{"metadata":{"_uuid":"39b846232cbe9c7bc25389ffa3a181920bb84170","trusted":false},"cell_type":"code","source":"card_total_purchase = new_merchant_transactions.groupby(['card_id'])['purchase_amount'].sum().sort_values()\ncard_total_purchase.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ebfcab6cca0e861dc6822277929cb7d64cb1417b","trusted":false},"cell_type":"code","source":"card_total_purchase.tail()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"631fb04ce15cf91b1ec6c0264b1bdc9f3cca2d2e"},"cell_type":"markdown","source":"It seems that there are some cards, for which most of transactions were declined. Were this fraud transactions?"},{"metadata":{"_uuid":"cdc52298c2cad176eb422f21f178bb56f6020648"},"cell_type":"markdown","source":"### installments"},{"metadata":{"_uuid":"9b225610081a11c98cf9893e215c71c5840db388","trusted":false},"cell_type":"code","source":"new_merchant_transactions['installments'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"83f28e296027ab97dfd0938dc05f86ead1448e9b"},"cell_type":"markdown","source":"Interesting. Most common number of installments are 0 and 1 which is expected. But -1 and 999 are strange. I think that these values were used to fill in missing values."},{"metadata":{"_uuid":"333c1b2ebe3547182da31dc409835d9ff7fbf28e","trusted":false},"cell_type":"code","source":"new_merchant_transactions.groupby(['installments'])['purchase_amount'].sum()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"299b284f105aeb99ab580a3ea289a7334b663302","collapsed":true,"trusted":false},"cell_type":"code","source":"new_merchant_transactions['installments'] = new_merchant_transactions['installments'].astype('category')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"93f5d61f4ad4a927844f1fcd88153bd5cb9e53ae"},"cell_type":"markdown","source":"### purchase_amount\nSadly purchase_amount is normalized. Let's have a look at it nevertheless."},{"metadata":{"_uuid":"41996818fbc3a212506becdb337b2b760df630ac","trusted":false},"cell_type":"code","source":"plt.title('Purchase amount distribution.');\nnew_merchant_transactions['purchase_amount'].plot(kind='hist');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"eb768f16cd8b83b022dc9865618091ab4fb14013","trusted":false},"cell_type":"code","source":"for i in [-1, 0]:\n    n = new_merchant_transactions.loc[new_merchant_transactions['purchase_amount'] < i].shape[0]\n    print(f\"There are {n} transactions with purchase_amount less than {i}.\")\nfor i in [0, 10, 100]:\n    n = new_merchant_transactions.loc[new_merchant_transactions['purchase_amount'] > i].shape[0]\n    print(f\"There are {n} transactions with purchase_amount more than {i}.\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e24506613dee2c985fe0e017e4591626886b570a","trusted":false},"cell_type":"code","source":"plt.title('Purchase amount distribution for negative values.');\nnew_merchant_transactions.loc[new_merchant_transactions['purchase_amount'] < 0, 'purchase_amount'].plot(kind='hist');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"db375aa01bf1a9a9e63e080ae44f1f745778da12"},"cell_type":"markdown","source":"It seems that almost all transactions have purchase amount in range (-1, 0). Quite a strong normalization and high outliers, which will need to be processed."},{"metadata":{"_uuid":"961bff74a12d9cae466c9968faa08749bffe2725"},"cell_type":"markdown","source":"### Categories"},{"metadata":{"_uuid":"02e778126719327d12225ed5852bd5cf2e1513c6","trusted":false},"cell_type":"code","source":"map_dict = {'Y': 0, 'N': 1}\nnew_merchant_transactions['category_1'] = new_merchant_transactions['category_1'].apply(lambda x: map_dict[x])\nnew_merchant_transactions.groupby(['category_1']).agg({'purchase_amount': ['mean', 'std', 'count']})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1e4e01fa0cdc2b5905258d493d5345344cf958e0","trusted":false},"cell_type":"code","source":"new_merchant_transactions.groupby(['category_2']).agg({'purchase_amount': ['mean', 'std', 'count']})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5d0605ddaacac9202959b07e049ae885fef0a5ba","trusted":false},"cell_type":"code","source":"map_dict = {'A': 0, 'B': 1, 'C': 2, 'nan': 3}\nnew_merchant_transactions['category_3'] = new_merchant_transactions['category_3'].apply(lambda x: map_dict[str(x)])\nnew_merchant_transactions.groupby(['category_3']).agg({'purchase_amount': ['mean', 'std', 'count']})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"91f33d95c6528d677fc600b0afe9d69d4b2afb99"},"cell_type":"markdown","source":"All categories are quite different"},{"metadata":{"_uuid":"e93269923c149e5d51162e28614b0ce22e50a869","trusted":false},"cell_type":"code","source":"for col in ['city_id', 'merchant_category_id', 'merchant_id', 'state_id', 'subsector_id']:\n    print(f\"There are {new_merchant_transactions[col].nunique()} unique values in {col}.\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7164e3d6c4f035f05dcb616f43247a0ac60c236b","collapsed":true,"trusted":false},"cell_type":"code","source":"new_merchant_transactions['purchase_date'] = pd.to_datetime(new_merchant_transactions['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fec4f0ea374bda3e9a6ec174de160b9f0311d653"},"cell_type":"markdown","source":"### Feature engineering"},{"metadata":{"_uuid":"a6256580f0fa2e297506991619c6b4e00cf6c742","collapsed":true,"trusted":false},"cell_type":"code","source":"def aggregate_historical_transactions(trans, prefix):\n    trans['purchase_month'] = trans['purchase_date'].dt.month\n    trans['month_diff'] = ((datetime.datetime.today() - trans['purchase_date']).dt.days)//30\n    trans['month_diff'] += trans['month_lag']\n    trans.loc[:, 'purchase_date'] = pd.DatetimeIndex(trans['purchase_date']).astype(np.int64) * 1e-9\n    trans['installments'] = trans['installments'].astype(int)\n    trans = pd.get_dummies(trans, columns=['category_2', 'category_3'])\n    agg_func = {\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_1': ['sum', 'mean'],\n        'category_3_2': ['sum', 'mean'],\n        'category_3_3': ['sum', 'mean'],\n        'merchant_id': ['nunique'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'installments': ['sum', 'mean', 'max', 'min', 'std'],\n        'purchase_month': ['mean', 'max', 'min', 'std'],\n        'purchase_date': [np.ptp, 'max', 'min'],\n        'month_lag': ['min', 'max'],\n        'merchant_category_id': ['nunique'],\n        'state_id': ['nunique'],\n        'subsector_id': ['nunique'],\n        'city_id': ['nunique'],\n    }\n    agg_trans = trans.groupby(['card_id']).agg(agg_func)\n    agg_trans.columns = [prefix + '_'.join(col).strip() for col in agg_trans.columns.values]\n    agg_trans.reset_index(inplace=True)\n\n    df = (trans.groupby('card_id')\n          .size()\n          .reset_index(name='{}transactions_count'.format(prefix)))\n\n    agg_trans = pd.merge(df, agg_trans, on='card_id', how='left')\n\n    return agg_trans","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"61c4fd628b03892f0cd2be3736c768ef2a466b47","trusted":false},"cell_type":"code","source":"%%time\ngc.collect()\nnew_transactions = reduce_mem_usage(new_merchant_transactions)\nhistory = aggregate_historical_transactions(new_merchant_transactions, prefix='new')\ndel new_merchant_transactions\ngc.collect()\ntrain = pd.merge(train, history, on='card_id', how='left')\ntest = pd.merge(test, history, on='card_id', how='left')\ndel history\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3d86723eb2978f9d52ed2598c99d28cea598ae0a","trusted":false},"cell_type":"code","source":"train = pd.merge(train, final_group, on='card_id')\ntest = pd.merge(test, final_group, on='card_id')\ngc.collect()\ndel final_group","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7b2b364313b419321256f2b248341ef24a2fb93f"},"cell_type":"markdown","source":"## merchants\nAggregate information for each merchant_id"},{"metadata":{"_uuid":"291f812055cebee3cb727ee816e0fff28898f6ad","trusted":false},"cell_type":"code","source":"merchants = pd.read_csv('../input/merchants.csv')\ne = pd.read_excel('../input/Data_Dictionary.xlsx', sheet_name='merchant')\ne","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a4a1749fe64677f4fcb735ecd7abf177303d4e3b","trusted":false},"cell_type":"code","source":"print(f'{merchants.shape[0]} merchants in data')\nmerchants.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"66426cd1a0ac67b80b2eb5c6b5d347458fdf21fd","collapsed":true,"trusted":false},"cell_type":"code","source":"# encoding categories.\nmap_dict = {'Y': 0, 'N': 1}\nmerchants['category_1'] = merchants['category_1'].apply(lambda x: map_dict[x])\nmerchants.loc[merchants['category_2'].isnull(), 'category_2'] = 0\nmerchants['category_4'] = merchants['category_4'].apply(lambda x: map_dict[x])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3c0b4350b6cc7485dee92b06ef82895a72f0fa80","trusted":false},"cell_type":"code","source":"merchants['merchant_category_id'].nunique(), merchants['merchant_group_id'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"27a79091a1d4a8b7ce47b6ccd56c1b723230bc11"},"cell_type":"markdown","source":"### numerical_1"},{"metadata":{"_uuid":"ddb7ce69db6c052e21b3b5c6d64c73ae8d685438","trusted":false},"cell_type":"code","source":"plt.hist(merchants['numerical_1']);\nplt.title('Distribution of numerical_1');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"94aab73543b32634afb1f4cf90cabf6ae16cdd75","trusted":false},"cell_type":"code","source":"np.percentile(merchants['numerical_1'], 95)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f8c40112a928328a4196b891e06d252cd70b61b6"},"cell_type":"markdown","source":"Well, 95% of values are less than 0.1, we'll need to deal with outliers."},{"metadata":{"_uuid":"214672a19d3d27a129bb439e43ee1e83caad3e32","trusted":false},"cell_type":"code","source":"plt.hist(merchants.loc[merchants['numerical_1'] < 0.1, 'numerical_1']);\nplt.title('Distribution of numerical_1 less than 0.1');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"18bade042e02c9efef60fec3a185e026a60e89f1","trusted":false},"cell_type":"code","source":"min_n1 = merchants['numerical_1'].min()\n_ = sum(merchants['numerical_1'] == min_n1) / merchants['numerical_1'].shape[0]\nprint(f'{_ * 100:.4f}% of values in numerical_1 are equal to {min_n1}')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4dedb20eabf065ccdeab7b3ee3e1aab4db91e7fb"},"cell_type":"markdown","source":"In fact more than a half values are equal to minimum value. A very skewered distribution."},{"metadata":{"_uuid":"af3eac6377e29b43e409f3482ba5da79be7ad20b"},"cell_type":"markdown","source":"### Numerical_2"},{"metadata":{"_uuid":"d0b4912cf04e05aae8af47957ab1963433f0c268","trusted":false},"cell_type":"code","source":"plt.hist(merchants['numerical_2']);\nplt.title('Distribution of numerical_2');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9925c1c39789ffe907236dac2e5e6575d370f244","trusted":false},"cell_type":"code","source":"plt.hist(merchants.loc[merchants['numerical_2'] < 0.1, 'numerical_2']);\nplt.title('Distribution of numerical_2 less than 0.1');\nmin_n1 = merchants['numerical_1'].min()\n_ = sum(merchants['numerical_1'] == min_n1) / merchants['numerical_1'].shape[0]\nprint(f'{_ * 100:.4f}% of values in numerical_1 are equal to {min_n1}')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"604f40e5b93e654a26f95b7a8ef4e38fa76ce7ec","trusted":false},"cell_type":"code","source":"(merchants['numerical_1'] != merchants['numerical_2']).sum() / merchants.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"644e77327dc8c80baaf1ce0f3956436e5c8d0a40"},"cell_type":"markdown","source":"These two variables are very similar. In fact for 90% merchants they are the same."},{"metadata":{"_uuid":"3a2cf5b550671e025c86ce6268268a24e041df20"},"cell_type":"markdown","source":"> most_recent_sales_range \tmost_recent_purchases_range \tavg_sales_lag3 \tavg_purchases_lag3 \tactive_months_lag3 \tavg_sales_lag6 \tavg_purchases_lag6 \tactive_months_lag6 \tavg_sales_lag12 \tavg_purchases_lag12 \tactive_months_lag12"},{"metadata":{"_uuid":"c3f1138340c6d205a94843652f08802957efa61d"},"cell_type":"markdown","source":"### most_recent_sales_range"},{"metadata":{"_uuid":"f21d5d6679909ff02f3cf12a912db760c1da5692","trusted":false},"cell_type":"code","source":"merchants['most_recent_sales_range'].value_counts().plot('bar');","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_uuid":"7a8c6874fa42d125d2905fa89508e3249507803d","trusted":false},"cell_type":"code","source":"d = merchants['most_recent_sales_range'].value_counts().sort_index()\ne = merchants.loc[merchants['numerical_2'] < 0.1].groupby('most_recent_sales_range')['numerical_1'].mean()\ndata = [go.Bar(x=d.index, y=d.values, name='counts'), go.Scatter(x=e.index, y=e.values, name='mean numerical_1', yaxis='y2')]\nlayout = go.Layout(dict(title = \"Counts of values in categories of most_recent_sales_range\",\n                        xaxis = dict(title = 'most_recent_sales_range'),\n                        yaxis = dict(title = 'Counts'),\n                        yaxis2=dict(title='mean numerical_1', overlaying='y', side='right')),\n                   legend=dict(orientation=\"v\"))\npy.iplot(dict(data=data, layout=layout))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3951b516781fab96bf86edf667cde14a5fce8dfd"},"cell_type":"markdown","source":"We can see that these ranges have different counts and different mean value of numerical_1 even after removing outliers."},{"metadata":{"_uuid":"db1a98b50b33b9bbff2b95f65e64cc6642a5752a"},"cell_type":"markdown","source":"### most_recent_purchases_range"},{"metadata":{"_uuid":"41899f368e90d8588e6041556f1a9b07a8d90d30","trusted":false},"cell_type":"code","source":"d = merchants['most_recent_purchases_range'].value_counts().sort_index()\ne = merchants.loc[merchants['numerical_2'] < 0.1].groupby('most_recent_purchases_range')['numerical_1'].mean()\ndata = [go.Bar(x=d.index, y=d.values, name='counts'), go.Scatter(x=e.index, y=e.values, name='mean numerical_1', yaxis='y2')]\nlayout = go.Layout(dict(title = \"Counts of values in categories of most_recent_purchases_range\",\n                        xaxis = dict(title = 'most_recent_purchases_range'),\n                        yaxis = dict(title = 'Counts'),\n                        yaxis2=dict(title='mean numerical_1', overlaying='y', side='right')),\n                   legend=dict(orientation=\"v\"))\npy.iplot(dict(data=data, layout=layout))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"621e0b6df1dcf73a95d3709bbbbfce0a60b5489c"},"cell_type":"markdown","source":"These two variables seem to be quite similar."},{"metadata":{"_uuid":"8f6c94741e8c8a8af5fac95ae4452fc238aea33b"},"cell_type":"markdown","source":"### avg_sales_lag"},{"metadata":{"_uuid":"0ea8ea95e2105c11f04b4c3e8f46d2e2261d8594","scrolled":true,"trusted":false},"cell_type":"code","source":"plt.hist(merchants['avg_sales_lag3'].fillna(0));\nplt.hist(merchants['avg_sales_lag6'].fillna(0));\nplt.hist(merchants['avg_sales_lag12'].fillna(0));","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5baa99bfe3dba4906b376966dae3ebf19e4ff550","trusted":false},"cell_type":"code","source":"for col in ['avg_sales_lag3', 'avg_sales_lag6', 'avg_sales_lag12']:\n    print(f'Max value of {col} is {merchants[col].max()}')\n    print(f'Min value of {col} is {merchants[col].min()}')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b034ab4c4a1dea6b394c4fd1790bfb8a5e880774","trusted":false},"cell_type":"code","source":"plt.hist(merchants.loc[(merchants['avg_sales_lag12'] < 3) & (merchants['avg_sales_lag12'] > -10), 'avg_sales_lag12'].fillna(0), label='avg_sales_lag12');\nplt.hist(merchants.loc[(merchants['avg_sales_lag6'] < 3) & (merchants['avg_sales_lag6'] > -10), 'avg_sales_lag6'].fillna(0), label='avg_sales_lag6');\nplt.hist(merchants.loc[(merchants['avg_sales_lag3'] < 3) & (merchants['avg_sales_lag3'] > -10), 'avg_sales_lag3'].fillna(0), label='avg_sales_lag3');\nplt.legend();","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1a52c39bdb6a2d09a4136edc8ddf782463b8c40f"},"cell_type":"markdown","source":"Distribution of these values is quite similar and most values are between 0 and 2."},{"metadata":{"_uuid":"c9d7fb7d16a85714832312000808e59ff4567491"},"cell_type":"markdown","source":"### avg_purchases_lag"},{"metadata":{"_uuid":"68a431b3e60a8d7b02e2b70d18c61eee4324039e","trusted":false},"cell_type":"code","source":"merchants['avg_purchases_lag3'].nlargest()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f45820fd9663e50553fcb583e379e740628ffc1d"},"cell_type":"markdown","source":"We even have infinite values..."},{"metadata":{"_uuid":"d296b90a7f39025dba29d3cf8a2583e42939ed34","collapsed":true,"trusted":false},"cell_type":"code","source":"merchants.loc[merchants['avg_purchases_lag3'] == np.inf, 'avg_purchases_lag3'] = 6000\nmerchants.loc[merchants['avg_purchases_lag6'] == np.inf, 'avg_purchases_lag6'] = 6000\nmerchants.loc[merchants['avg_purchases_lag12'] == np.inf, 'avg_purchases_lag12'] = 6000","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"18394735ef9c94306faabdc31a039d0bb1e97586","scrolled":true,"trusted":false},"cell_type":"code","source":"plt.hist(merchants['avg_purchases_lag3'].fillna(0));\nplt.hist(merchants['avg_purchases_lag6'].fillna(0));\nplt.hist(merchants['avg_purchases_lag12'].fillna(0));","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"05b9a9296e65a54e4d1d81359581d7964216b939","trusted":false},"cell_type":"code","source":"plt.hist(merchants.loc[(merchants['avg_purchases_lag12'] < 4), 'avg_purchases_lag12'].fillna(0), label='avg_purchases_lag12');\nplt.hist(merchants.loc[(merchants['avg_purchases_lag6'] < 4), 'avg_purchases_lag6'].fillna(0), label='avg_purchases_lag6');\nplt.hist(merchants.loc[(merchants['avg_purchases_lag3'] < 4), 'avg_purchases_lag3'].fillna(0), label='avg_purchases_lag3');\nplt.legend();","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0adf8adba0d9909d2597010399c94804cd515eab"},"cell_type":"markdown","source":"For now I won't use merchants data in models."},{"metadata":{"_uuid":"d9f162fe0613a29b2325947b8ea0618f3aa040c5"},"cell_type":"markdown","source":"### Processing data for modelling"},{"metadata":{"_uuid":"883f969ce917e1190bddd2b04c6c70613825a6f7","trusted":false},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"692adebadca5aa3882519ec6825e92798a795898","collapsed":true,"trusted":false},"cell_type":"code","source":"for col in train.columns:\n    if train[col].isna().any():\n        train[col] = train[col].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"05f830d08ae0c12745ec910963ea3a9b21b9e073","collapsed":true,"trusted":false},"cell_type":"code","source":"for col in test.columns:\n    if test[col].isna().any():\n        test[col] = test[col].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9ad2fb3ab3d188c128987254ee2fcf6c8189d053","collapsed":true,"trusted":false},"cell_type":"code","source":"y = train['target']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d138a65fa4cd61ded3d30495082a2a882975072a","collapsed":true,"trusted":false},"cell_type":"code","source":"col_to_drop = ['first_active_month', 'card_id', 'target']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"52fc828a1e73aaef884415508cbc82c778f4a6d5","collapsed":true,"trusted":false},"cell_type":"code","source":"for col in col_to_drop:\n    if col in train.columns:\n        train.drop([col], axis=1, inplace=True)\n    if col in test.columns:\n        test.drop([col], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8456e6c0f38fff673d638e23409e053c3d20b948","collapsed":true,"trusted":false},"cell_type":"code","source":"train['feature_3'] = train['feature_3'].astype(int)\ntest['feature_3'] = test['feature_3'].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5acfc3b898553e172ae7b9d037fb27a73008580b","collapsed":true,"trusted":false},"cell_type":"code","source":"categorical_feats = ['feature_1', 'feature_2']\n\nfor col in categorical_feats:\n    lbl = LabelEncoder()\n    lbl.fit(list(train[col].values.astype('str')) + list(test[col].values.astype('str')))\n    train[col] = lbl.transform(list(train[col].values.astype('str')))\n    test[col] = lbl.transform(list(test[col].values.astype('str')))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2471184203440e0caf0f2792664061b4ea8964a3","trusted":false},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a1d12df02550f63619571b52f01e54f2db148fc6","collapsed":true,"trusted":false},"cell_type":"code","source":"for col in ['newpurchase_amount_max', 'newpurchase_date_max', 'purchase_amount_max_mean']:\n    train[col + '_to_mean'] = train[col] / train[col].mean()\n    test[col + '_to_mean'] = test[col] / test[col].mean()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5475688803fb32f89f210f118d1079adeb46350f"},"cell_type":"markdown","source":"### Basic LGB model"},{"metadata":{"_uuid":"020c7321dafa69a91fe63e8fcc64105efbad0a71","collapsed":true,"trusted":false},"cell_type":"code","source":"X = train\nX_test = test","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"394e909da7b1dcc4a9aa8a861c92728d36070bd8"},"cell_type":"markdown","source":"#### Code for training models"},{"metadata":{"_uuid":"92ced003ab2ef61724280e494170fcb25cfc0272","collapsed":true,"trusted":false},"cell_type":"code","source":"n_fold = 5\nfolds = KFold(n_splits=n_fold, shuffle=True, random_state=11)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c64f57f58ab28cd6f467ba7ebbffa06b11a89170","collapsed":true,"trusted":false},"cell_type":"code","source":"def train_model(X=X, X_test=X_test, y=y, params=None, folds=folds, model_type='lgb', plot_feature_importance=False):\n\n    oof = np.zeros(len(X))\n    prediction = np.zeros(len(X_test))\n    scores = []\n    feature_importance = pd.DataFrame()\n    for fold_n, (train_index, valid_index) in enumerate(folds.split(X)):\n        print('Fold', fold_n, 'started at', time.ctime())\n        X_train, X_valid = X.iloc[train_index], X.iloc[valid_index]\n        y_train, y_valid = y.iloc[train_index], y.iloc[valid_index]\n        \n        if model_type == 'lgb':\n            model = lgb.LGBMRegressor(**params, n_estimators = 20000, nthread = 4, n_jobs = -1)\n            model.fit(X_train, y_train, \n                    eval_set=[(X_train, y_train), (X_valid, y_valid)], eval_metric='rmse',\n                    verbose=1000, early_stopping_rounds=200)\n            \n            y_pred_valid = model.predict(X_valid)\n            y_pred = model.predict(X_test, num_iteration=model.best_iteration_)\n            \n        if model_type == 'xgb':\n            train_data = xgb.DMatrix(data=X_train, label=y_train)\n            valid_data = xgb.DMatrix(data=X_valid, label=y_valid)\n\n            watchlist = [(train_data, 'train'), (valid_data, 'valid_data')]\n            model = xgb.train(dtrain=train_data, num_boost_round=20000, evals=watchlist, early_stopping_rounds=200, verbose_eval=500, params=params)\n            y_pred_valid = model.predict(xgb.DMatrix(X_valid), ntree_limit=model.best_ntree_limit)\n            y_pred = model.predict(xgb.DMatrix(X_test), ntree_limit=model.best_ntree_limit)\n            \n        if model_type == 'rcv':\n            model = RidgeCV(alphas=(0.1, 1.0, 10.0, 100.0), scoring='neg_mean_squared_error', cv=3)\n            model.fit(X_train, y_train)\n            print(model.alpha_)\n\n            y_pred_valid = model.predict(X_valid)\n            score = mean_squared_error(y_valid, y_pred_valid) ** 0.5\n            print(f'Fold {fold_n}. RMSE: {score:.4f}.')\n            print('')\n            \n            y_pred = model.predict(X_test)\n            \n        if model_type == 'cat':\n            model = CatBoostRegressor(iterations=20000,  eval_metric='RMSE', **params)\n            model.fit(X_train, y_train, eval_set=(X_valid, y_valid), cat_features=[], use_best_model=True, verbose=False)\n\n            y_pred_valid = model.predict(X_valid)\n            y_pred = model.predict(X_test)\n        \n        oof[valid_index] = y_pred_valid.reshape(-1,)\n        scores.append(mean_squared_error(y_valid, y_pred_valid) ** 0.5)\n        \n        prediction += y_pred    \n        \n        if model_type == 'lgb':\n            # feature importance\n            fold_importance = pd.DataFrame()\n            fold_importance[\"feature\"] = X.columns\n            fold_importance[\"importance\"] = model.feature_importances_\n            fold_importance[\"fold\"] = fold_n + 1\n            feature_importance = pd.concat([feature_importance, fold_importance], axis=0)\n\n    prediction /= n_fold\n    \n    print('CV mean score: {0:.4f}, std: {1:.4f}.'.format(np.mean(scores), np.std(scores)))\n    \n    if model_type == 'lgb':\n        feature_importance[\"importance\"] /= n_fold\n        if plot_feature_importance:\n            cols = feature_importance[[\"feature\", \"importance\"]].groupby(\"feature\").mean().sort_values(\n                by=\"importance\", ascending=False)[:50].index\n\n            best_features = feature_importance.loc[feature_importance.feature.isin(cols)]\n\n            plt.figure(figsize=(16, 12));\n            sns.barplot(x=\"importance\", y=\"feature\", data=best_features.sort_values(by=\"importance\", ascending=False));\n            plt.title('LGB Features (avg over folds)');\n        \n            return oof, prediction, feature_importance\n        return oof, prediction\n    \n    else:\n        return oof, prediction","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"13ad0a532f097c08d27accbc55eabd123123a5b6","collapsed":true,"trusted":false},"cell_type":"code","source":"params = {'num_leaves': 54,\n         'min_data_in_leaf': 79,\n         'objective': 'regression',\n         'max_depth': 7,\n         'learning_rate': 0.018545526395058548,\n         \"boosting\": \"gbdt\",\n         \"feature_fraction\": 0.8354507676881442,\n         \"bagging_freq\": 3,\n         \"bagging_fraction\": 0.8126672064208567,\n         \"bagging_seed\": 11,\n         \"metric\": 'rmse',\n         \"lambda_l1\": 0.1,\n         \"verbosity\": -1,\n         'min_child_weight': 5.343384366323818,\n         'reg_alpha': 1.1302650970728192,\n         'reg_lambda': 0.3603427518866501,\n         'subsample': 0.8767547959893627,}","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0a1f01d46dba0b4f124ebd5c438e6bd90f004104","trusted":false},"cell_type":"code","source":"oof_lgb, prediction_lgb, feature_importance = train_model(params=params, model_type='lgb', plot_feature_importance=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"350ffcd85728da326f36e1bcdb9d0523cda3d31e","collapsed":true,"trusted":false},"cell_type":"code","source":"submission['target'] = prediction_lgb\nsubmission.to_csv('lgb.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0ab141df9f759efea8152fed1bfe127c2b63cdaf","trusted":false},"cell_type":"code","source":"xgb_params = {'eta': 0.01, 'max_depth': 10, 'subsample': 0.8, 'colsample_bytree': 0.8, \n          'objective': 'reg:linear', 'eval_metric': 'rmse', 'silent': True, 'nthread': 4}\noof_xgb, prediction_xgb = train_model(params=xgb_params, model_type='xgb')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5fea76e67aba21f187fa551c7908a3ac492dabfb","collapsed":true,"trusted":false},"cell_type":"code","source":"submission['target'] = prediction_xgb\nsubmission.to_csv('xgb.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"727f6f11db1810bec7fdaddb48109f820209cb2f","trusted":false},"cell_type":"code","source":"oof_rcv, prediction_rcv = train_model(params=None, model_type='rcv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f88ef4761db632f697a86d910c930c387193f5a4","collapsed":true,"trusted":false},"cell_type":"code","source":"submission['target'] = prediction_rcv\nsubmission.to_csv('rcv.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"16a18c14edc11582b7b3742ce46e5e256292ac9d","trusted":false},"cell_type":"code","source":"cat_params = {'learning_rate': 0.01,\n              'depth': 9,\n              'l2_leaf_reg': 10,\n              'bootstrap_type': 'Bernoulli',\n              #'metric_period': 500,\n              'od_type': 'Iter',\n              'od_wait': 50,\n              'random_seed': 11,\n              'allow_writing_files': False}\noof_cat, prediction_cat = train_model(params=cat_params, model_type='cat')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"30d520bb28340e3d193b105de92849434d103983","collapsed":true,"trusted":false},"cell_type":"code","source":"submission['target'] = (prediction_lgb + prediction_xgb + prediction_rcv + prediction_cat) / 4\nsubmission.to_csv('blend.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a5c8a3c35ca5cb91539c2e1e862d1d9f0e959fac","collapsed":true,"trusted":false},"cell_type":"code","source":"train_stack = np.vstack([oof_lgb, oof_xgb, oof_rcv, oof_cat]).transpose()\ntrain_stack = pd.DataFrame(train_stack)\ntest_stack = np.vstack([prediction_lgb, prediction_xgb, prediction_rcv, prediction_cat]).transpose()\ntest_stack = pd.DataFrame(test_stack)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"15d28fe442b6371a3e9c763dd3138d64f94ba547","trusted":false},"cell_type":"code","source":"oof_lgb_stack, prediction_lgb_stack = train_model(X=train_stack, X_test=test_stack, params=params, model_type='lgb')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bb1484c2110e18dde571ea0030130932e215927c","collapsed":true,"trusted":false},"cell_type":"code","source":"sample_submission = pd.read_csv('../input/sample_submission.csv')\nsample_submission['target'] = prediction_lgb_stack\nsample_submission.to_csv('stacker_lgb.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f9b1c5a3efe5cdff7b016f10f4dbd559479ccbc1","trusted":false},"cell_type":"code","source":"oof_rcv_stack, prediction_rcv_stack = train_model(X=train_stack, X_test=test_stack, params=None, model_type='rcv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"718ba315ffc8161f030cd94f5cb421775de4396c","collapsed":true,"trusted":false},"cell_type":"code","source":"sample_submission = pd.read_csv('../input/sample_submission.csv')\nsample_submission['target'] = prediction_rcv_stack\nsample_submission.to_csv('stacker_rcv.csv', index=False)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.3"}},"nbformat":4,"nbformat_minor":1}