{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import gc\nimport glob\nimport os\nimport time\n\nimport matplotlib.pyplot as plt\n%matplotlib inline\n\nimport numpy as np\nimport pandas as pd\nimport lightgbm as lgb\n\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import KFold, StratifiedKFold\n\ngc.enable()\n\npd.options.display.max_rows = 96\npd.options.display.max_columns = 128","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4a6c924bf0c7e234d984649bf3b639da8a724197"},"cell_type":"markdown","source":"### Available inputs: \n\n\nFor this competition, dictionary containing information about dataset is available.\nThat's helpful for feature engineering, as it provides a possible direction of engineering for each feature.\nOne thing to keep in mind is that this set was created _artificially_:\n**_All data is simulated and fictitious, and is not real customer data_**\n\nKernel environment has it's memory and speed constraints, therefore `historical_transactions.csv` file will not be used, as it's the biggest one.\nWe will base our workflow on remaining set of files."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"input_dir = '../input/'\ninput_files = sorted(glob.glob(input_dir + '*'))\n\ninput_files","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1e22db23aa3fafcebbeec3e91c439cf7d035bd22"},"cell_type":"markdown","source":"### Data loading:"},{"metadata":{"trusted":true,"_uuid":"7e4c81970728aa99c2653da2fa1cc26e81822dec"},"cell_type":"code","source":"%%time\n\ntrain = pd.read_csv('../input/train.csv', parse_dates=['first_active_month'])\ntest = pd.read_csv('../input/test.csv', parse_dates=['first_active_month'])\nsample_submission = pd.read_csv('../input/sample_submission.csv')\n\n\nmerchant = pd.read_csv('../input/merchants.csv')\nnew_merchant = pd.read_csv('../input/new_merchant_transactions.csv')\n# historical = pd.read_csv('../input/historical_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f6469d41ce85720eb7503a2b238d7c9023016f2d"},"cell_type":"markdown","source":"### Quick look at train and test data:\n\n- `card_id` is the ID of card, some of information from other DFs can be merged by those\n- only 3 features (anonymized) + information about month, according to the description: 'YYYY-MM', month of first purchase\n- `train.csv` contains target, which is the feature we will try to predict. This one is defined as: Loyalty numerical score calculated 2 months after historical and evaluation period"},{"metadata":{"trusted":true,"_uuid":"c92afbe92471587b5bbb2253735a7325359bd71c"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d0feda72400e5635c36930168109f835844d8c22"},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ff6dc8684414abba6d7657b55c52078bb478c5ad"},"cell_type":"markdown","source":"### Quick look at other tables - _merchant_:\n\n\n- This table gives a richer set of features for possible exploration and feature engineering\n- Some of those contain NaN values (worth exploring!)\n- Some features contain already averaged information, for example _avg_sales_lag3 - Monthly average of revenue in last 3 months divided by revenue in last active month_\n- There is no card_id to merge this DF to train/test set"},{"metadata":{"trusted":true,"_uuid":"3e0d10192a0990342ad9c15cbb7ffefe23b3c542"},"cell_type":"code","source":"merchant.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7469164d17b996c53a02b26460827628f65fd00b"},"cell_type":"markdown","source":"### Quick look at other tables - _new merchant_:\n\n\n- Amount of features in this table is somewhere between train and merchant DFs\n- Good information is that it contains both `merchant_id` and `card_id`, to this may be a mean to connect `train`, `test` and `merchant` DFs"},{"metadata":{"trusted":true,"_uuid":"ed093e29eaf71ea4ab1ad3d07081d18fef67c067"},"cell_type":"code","source":"new_merchant.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dea5bc372b8f1c82ba0f566a81a2e0c313d396e3"},"cell_type":"markdown","source":"### NaN structure:\n\n- no NaN in train and test, that's good\n- some NaNs in both merchants DF, especially `category_2` feature."},{"metadata":{"trusted":true,"_uuid":"919bae2f6e389ff6f273d4cbf1ad163d92f21d27"},"cell_type":"code","source":"print('Train NaN:\\n\\n{}\\n'.format(np.sum(pd.isnull(train))))\nprint('Test NaN:\\n\\n{}\\n'.format(np.sum(pd.isnull(test))))\nprint('Merchant NaN:\\n\\n{}\\n'.format(np.sum(pd.isnull(merchant))))\nprint('New Merchant NaN:\\n\\n{}\\n'.format(np.sum(pd.isnull(new_merchant))))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5bbc2680819bab161e59ab62bf7db759c1116e1c"},"cell_type":"markdown","source":"### Let's check how many of those occur in both and how many do not:\n\nWe must start with filling missing values in `merchant_id` with some value without meaning, like `NoID`."},{"metadata":{"trusted":true,"_uuid":"fa55643a0d58db37da6bf72ef7554179d253f388"},"cell_type":"code","source":"new_merchant = new_merchant.dropna(subset=['merchant_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06d7b946c792a025526e2c96ee96a45f73a8e725"},"cell_type":"code","source":"merchant_id_num = len(merchant['merchant_id'].unique())\nnew_merchant_id_num = len(new_merchant['merchant_id'].unique())\nmerchant_id_intersect = len(np.intersect1d(new_merchant.merchant_id, merchant.merchant_id))\n\nprint('Merchant IDs: {}'.format(merchant_id_num))\nprint('New merchant IDs: {}'.format(new_merchant_id_num))\nprint('Merchants ID intersection: {}'.format(merchant_id_intersect))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d4b9dd0522518e7077d434ecd09a9251e795e98a"},"cell_type":"markdown","source":"After checking the intersection, we know that all merchants from new_merchant DF are covered, so this won't be an issue when merging with main train/test DF."},{"metadata":{"_uuid":"392f14afb3bda824951178acc632f65ddb3f2976"},"cell_type":"markdown","source":"### card_id intersection:"},{"metadata":{"trusted":true,"_uuid":"ca50162a6ba0570d6108c1109074dab02dccfaca"},"cell_type":"code","source":"train_card_id_num = len(train['card_id'].unique())\ntest_card_id_num = len(test['card_id'].unique())\ntrain_card_id_intersect = len(np.intersect1d(new_merchant.card_id, train.card_id))\ntest_card_id_intersect = len(np.intersect1d(new_merchant.card_id, test.card_id))\n\nprint('train card IDs: {}'.format(train_card_id_num))\nprint('test card IDs: {}'.format(test_card_id_num))\nprint('train card IDs intersection: {}'.format(train_card_id_intersect))\nprint('test card IDs intersection: {}'.format(test_card_id_intersect))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"33b82337b5711b88492bb4f80c1047c4110a26bd"},"cell_type":"code","source":"train_id_frac = train_card_id_intersect / train_card_id_num\ntest_id_frac = test_card_id_intersect / test_card_id_num\n\nprint('train frac: {:.3f}, test frac: {:.3f}'.format(train_id_frac, test_id_frac))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d44624b5f9d04f5d73f0afab7f7cab67186402e0"},"cell_type":"markdown","source":"Coverage of train and test card IDs in new_merchant is almost the same, 89%."},{"metadata":{"_uuid":"9e60f1f5e9fe7507adf847608db1258efb061439"},"cell_type":"markdown","source":"### Feature engineering:\n\n\n### 1. Feature encoding\n\nIn order to know, which features must be encoded (to numerical values), let's create a reusable function to get different types of columns based on their data type. This may not work in 100% but will cover most of the cases.\nManual check is always worth a few minutes."},{"metadata":{"trusted":true,"_uuid":"0172eda8cd88ba709b07316e718a89e62dcc5e26"},"cell_type":"code","source":"# Get columns of each type\ndef get_column_types(df):\n\n    categorical_columns = [\n        col for col in df.columns if df[col].dtype == 'object']\n    categorical_columns_int = [\n        col for col in df.columns if df[col].dtype == 'int']\n    numerical_columns = [\n        col for col in df.columns if df[col].dtype == 'float']\n\n    categorical_columns = [\n        x for x in categorical_columns if 'id' not in x]\n    categorical_columns_int = [\n        x for x in categorical_columns_int if 'id' not in x]\n\n    return categorical_columns, categorical_columns_int, numerical_columns\n\n\n# Rename columns after grouping for easy merge and access\ndef rename_columns(df):\n    \n    df.columns = pd.Index(['{}{}'.format(\n        c[0], c[1].upper()) for c in df.columns.tolist()])\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a37ef911a94c1d38656a328be6e263b1f79dc77f"},"cell_type":"code","source":"merchant_cat_feats, merchant_catint_feats, merchant_num_feats = get_column_types(merchant)\n\nprint('Categorical features to encode: {}'.format(merchant_cat_feats))\nprint('\\nCategorical int features: {}'.format(merchant_catint_feats))\nprint('\\nNumerical features: {}'.format(merchant_num_feats))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"09aa5cecd2858ac3e4da2b45f41d5e7a7c7fd5f9"},"cell_type":"code","source":"new_merchant_cat_feats, new_merchant_catint_feats, new_merchant_num_feats = get_column_types(new_merchant)\n\nprint('Categorical features to encode: {}'.format(new_merchant_cat_feats))\nprint('\\nCategorical int features: {}'.format(new_merchant_catint_feats))\nprint('\\nNumerical features: {}'.format(new_merchant_num_feats))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ddf17ac0295db760a629c14acd0622de62b280d8"},"cell_type":"code","source":"# Let's create set of aggregates, which will be used for features grouping.\n# One for categorical and one for numerical features.\n\naggs_num_basic = ['mean', 'min', 'max', 'sum']\naggs_cat_basic = ['mean', 'sum', 'count']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"266dca187fe95223db1eff203973ee8eaa16a966"},"cell_type":"code","source":"# Encode string features to numbers:\n# If encoding train and test separately, remember to keep the features mapping between the two!\n\nfor c in new_merchant_cat_feats:\n    print('Encoding: {}'.format(c))\n    new_merchant[c] = pd.factorize(new_merchant[c])[0]\n    \nfor c in merchant_cat_feats:\n    print('Encoding: {}'.format(c))\n    merchant[c] = pd.factorize(merchant[c])[0]\n    \nnew_merchant","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7acc31f27a3da907e0b6be51fe51461b783a988a"},"cell_type":"markdown","source":"### Group merchant data by merchant_id:"},{"metadata":{"trusted":true,"_uuid":"d1e25d8aaf1dc933c5cb652b57f335af463a5f68"},"cell_type":"code","source":"merchant_card_id_cat = merchant.groupby(['merchant_id'])[merchant_cat_feats].agg(aggs_cat_basic)\nmerchant_card_id_num = merchant.groupby(['merchant_id'])[merchant_num_feats].agg(aggs_num_basic)\n\nmerchant_card_id_cat = rename_columns(merchant_card_id_cat)\nmerchant_card_id_num = rename_columns(merchant_card_id_num)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"446993d9f7b6476dbf7265403a01f7c351a3e6b7"},"cell_type":"code","source":"merchant_card_id_cat.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"756552e6be843da929188086d1d3a73cd6f3896b"},"cell_type":"markdown","source":"### join merchant features with new_merchant:"},{"metadata":{"trusted":true,"_uuid":"f1bb798dcaec9738e7324a21f90296fdbb602d1a"},"cell_type":"code","source":"new_merchant_ = new_merchant.set_index('merchant_id').join(merchant_card_id_cat, how='left')\nnew_merchant_ = new_merchant_.join(merchant_card_id_num, how='left')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"912321936fd0c739174068ae6a227a476f078bdd"},"cell_type":"markdown","source":"### Group new_merchant data by card_id:"},{"metadata":{"trusted":true,"_uuid":"065086b2ce45485ca43b25283bb1c22f6e41c8c0"},"cell_type":"code","source":"_, new_merchant_catint_feats2, new_merchant_num_feats2 = get_column_types(new_merchant_)\n\nprint('\\nCategorical int features: {}'.format(new_merchant_catint_feats2))\nprint('\\nNumerical features: {}'.format(new_merchant_num_feats2))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8a5b189f261a1993576364b4379f3ea34dd7bd6d"},"cell_type":"code","source":"new_merchant_card_id_cat = new_merchant_.groupby(['card_id'])[new_merchant_catint_feats2].agg(aggs_cat_basic)\nnew_merchant_card_id_num = new_merchant_.groupby(['card_id'])[new_merchant_num_feats2].agg(aggs_num_basic)\n\nnew_merchant_card_id_cat = rename_columns(new_merchant_card_id_cat)\nnew_merchant_card_id_num = rename_columns(new_merchant_card_id_num)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9ea4c8ca3891de6c811545ff93edf6fbe2f0dbad"},"cell_type":"markdown","source":"### join new_merchant with train/test by card_id:"},{"metadata":{"trusted":true,"_uuid":"4a3a34a95cbe810ae52be149d7b4ee48af56df24"},"cell_type":"code","source":"train_ = train.set_index('card_id').join(new_merchant_card_id_cat, how='left')\ntrain_ = train_.join(new_merchant_card_id_num, how='left')\n\ntest_ = test.set_index('card_id').join(new_merchant_card_id_cat, how='left')\ntest_ = test_.join(new_merchant_card_id_num, how='left')\n\n\ndel train, test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f670d1b7ff87598ef9632ed08812832055be69ef"},"cell_type":"markdown","source":"### Prepare for training:"},{"metadata":{"trusted":true,"_uuid":"8f980294bcff0508649d48ac46a56b96c543587d"},"cell_type":"code","source":"y = train_.target\nX = train_.drop(['target'], axis=1)\nX_test = test_.copy()\n\n\nfeatures_to_remove = ['first_active_month']\n\nX = X.drop(features_to_remove, axis=1)\nX_test = X_test.drop(features_to_remove, axis=1)\n\n\n# Assert that set of features is the same for both train and test DFs:\nassert np.all(X.columns == X_test.columns)\n\n\ndel train_, test_\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d17f571ee30358ca97be2265193b13578f7be979"},"cell_type":"markdown","source":"### check NaN structure of new features:"},{"metadata":{"trusted":true,"_uuid":"245143d8f789cf77a89e14c6c972b5388ddc51b7"},"cell_type":"code","source":"np.sum(pd.isnull(X)) / X.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc5cfffd558978ea6317e808acef7f24f2906118"},"cell_type":"code","source":"np.sum(pd.isnull(X_test)) / X_test.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"32183c745a2f2afbc1eb47f086e893412487eb74"},"cell_type":"markdown","source":"### KFold LGB model training:"},{"metadata":{"trusted":true,"_uuid":"db1b434181effcfa0bc6dbd0aaf208a0885f93a3"},"cell_type":"code","source":"# KFold splits\nkf = KFold(n_splits=5, shuffle=True, random_state=1337)\n# Column names:\ntrain_cols = X.columns.tolist()\n\n\n# LGB model parameters:\nparams = {'learning_rate': 0.03,\n          'boosting': 'gbdt', \n          'objective': 'regression', \n          'metric': 'rmse',\n          'num_leaves': 64,\n          'min_data_in_leaf': 6,\n          'max_bin': 255,\n          'bagging_fraction': 0.7,\n          'lambda_l2': 1e-4,\n          'max_depth': 12,\n          'seed': 1337,\n          'nthreads': 6}\n\n\n# Placeholders for out-of-fold predictions\noof_val = np.zeros((X.shape[0]))\noof_test = np.zeros((5, X_test.shape[0]))\n\n\ni = 0 # Placeholder for fold indexing\nfor tr, val in kf.split(X, y):\n    \n    print('Fold: {}'.format(i + 1))\n    \n    # Split into training and validation part\n    X_tr, y_tr = X.iloc[tr, :], y.iloc[tr]\n    X_val, y_val = X.iloc[val, :], y.iloc[val]\n    \n    # Create Dataset objects for lgb model\n    dtrain = lgb.Dataset(X_tr.values, y_tr.values, feature_name=train_cols)\n    dvalid = lgb.Dataset(X_val.values, y_val.values,\n                         feature_name=train_cols, reference=dtrain)\n    \n    # Train model\n    lgb_model = lgb.train(params, dtrain, \n                      num_boost_round=1000, \n                      valid_sets=(dvalid,), \n                      valid_names=('valid',), \n                      verbose_eval=25, \n                      early_stopping_rounds=20)\n    \n    # Save predictions for each fold\n    oof_val[val] = lgb_model.predict(X_val)\n    oof_test[i, :] = lgb_model.predict(X_test)\n    \n    i += 1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bf8ce30a4ef91c6b002e298a26b29fcc2256401b"},"cell_type":"code","source":"# Check RMSE for training set:\nvalid_rmse = mean_squared_error(y, oof_val) ** .5\n\nprint('Valid RMSE: {:.4f}'.format(valid_rmse))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2dbb254f5a02b5924ceac93205107ca241ebfacc"},"cell_type":"markdown","source":"### Prepare submission:"},{"metadata":{"trusted":true,"_uuid":"8fe7c416762a51319e023e63925ebc07a2e94af3"},"cell_type":"code","source":"# Average test predcitions across folds:\ntest_preds = oof_test.mean(axis=0)\n\n# Create submission:\nsample_submission['target'] = test_preds\nsample_submission.to_csv(\"submission_trial.csv\", index=False)\nsample_submission.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"576e88294400cc053f779896806828483a3fc47f"},"cell_type":"code","source":"","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}