{"cells":[{"metadata":{"_uuid":"914f5ef8778e311c1408053297341c115ce2fc2a"},"cell_type":"markdown","source":"### In this kernel I have utilized some features from merchants.csv. This is a first effort, and I will work on this over time. \n### Have not added features from merchants.csv to the historical transactions dataframe since I was running out of memory everytime. "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import os\nimport json\nimport time\nimport numpy as np\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\nimport pandas as pd\nfrom pandas.io.json import json_normalize\nimport matplotlib.pyplot as plt\nimport seaborn as sns\ncolor = sns.color_palette()\n\n%matplotlib inline\n\nimport datetime\nimport gc","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c6ad53545404bd02f915a6a5d071b026ac6f662"},"cell_type":"code","source":"from sklearn.model_selection import KFold\nfrom sklearn import model_selection, preprocessing, metrics\nfrom sklearn import model_selection\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.linear_model import Ridge, RidgeCV, Lasso\nfrom sklearn.ensemble import RandomForestRegressor\n\nimport lightgbm as lgb\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6331001efcab95dbb9a03d1d68fb94d69f2cc6fc"},"cell_type":"markdown","source":"The ideas below, using the elapsed_time as a feature etc.. come from a number of different kernels, such as:\n* https://www.kaggle.com/peterhurford/you-re-going-to-want-more-categories-lb-3-737\n* https://www.kaggle.com/sudalairajkumar/simple-exploration-notebook-elo\n* https://www.kaggle.com/fabiendaniel/elo-world\n"},{"metadata":{"trusted":true,"_uuid":"7dc6427e26a2afca1b83453eced59d4d0c91c9b9"},"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":"bbc5d220fe5243fdc81478b0b515aa2412bd6603"},"cell_type":"code","source":"df_train = pd.read_csv('../input/train.csv', parse_dates=['first_active_month'])\ndf_test = pd.read_csv('../input/test.csv', parse_dates=['first_active_month'])\n\ndf_train[\"month\"] = df_train[\"first_active_month\"].dt.month\ndf_test[\"month\"] = df_test[\"first_active_month\"].dt.month\n\ndf_train[\"year\"] = df_train[\"first_active_month\"].dt.year\ndf_test[\"year\"] = df_test[\"first_active_month\"].dt.year\n\ndf_train['elapsed_time'] = (datetime.date(2018, 2, 1) - df_train['first_active_month'].dt.date).dt.days\ndf_test['elapsed_time'] = (datetime.date(2018, 2, 1) - df_test['first_active_month'].dt.date).dt.days\n\n\ndf_train = pd.get_dummies(df_train, columns=['feature_1', 'feature_2'])\ndf_test = pd.get_dummies(df_test, columns=['feature_1', 'feature_2'])\n\ndf_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9219e5f5bab1f69e8907af2208ee2bc6721d7ca8"},"cell_type":"code","source":"df_merch = pd.read_csv('../input/merchants.csv')\ndf_merch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"efe6c9b3f4f7058fe2a83bfea726db17598b0aeb"},"cell_type":"code","source":"df_hist = pd.read_csv('../input/historical_transactions.csv')\ndf_hist.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"65c72b8b75656b10842a5ba41d77e93b7e0414fc"},"cell_type":"code","source":"df_new = pd.read_csv('../input/new_merchant_transactions.csv')\ndf_new.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"457295ac000fdd6272355c05fedb1d3709949ef3"},"cell_type":"code","source":"df_merch['category_1'] = df_merch['category_1'].map({'Y': 1, 'N': 0})\ndf_merch['category_4'] = df_merch['category_4'].map({'Y': 1, 'N': 0})\ndf_merch = pd.get_dummies(df_merch,columns=['category_2','most_recent_sales_range','most_recent_purchases_range'])\ndf_merch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1bbb5f13933de6805f0b7968d19ce560ea3e33f6"},"cell_type":"code","source":"df_hist = reduce_mem_usage(df_hist)\ndf_new = reduce_mem_usage(df_new)\ndf_merch = reduce_mem_usage(df_merch)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cd5b48059f80f989759f762d09f80cd714292eea"},"cell_type":"markdown","source":"First let us aggregate by merchant_id for merchants.csv, then we will join by 'merchant_id' to the new and hist dataframes"},{"metadata":{"trusted":true,"_uuid":"e932efffe313ab202ff0a51244ed289f8f470f73"},"cell_type":"code","source":"def aggregate_transactions_merchants(df, prefix):\n    \n    agg_func = {\n        'numerical_1': ['sum', 'mean'],\n        'numerical_2': ['sum', 'mean'],\n        'category_1': ['mean'],\n        'category_4': ['mean'],\n        'category_2_1.0': ['mean'],\n        'category_2_2.0': ['mean'],\n        'category_2_3.0': ['mean'],\n        'category_2_4.0': ['mean'],\n        'category_2_5.0': ['mean'],\n        'most_recent_sales_range_A': ['mean'],\n        'most_recent_sales_range_B': ['mean'],\n        'most_recent_sales_range_C': ['mean'],\n        'most_recent_sales_range_D': ['mean'],\n        'most_recent_sales_range_E': ['mean'],\n        'most_recent_purchases_range_A': ['mean'],\n        'most_recent_purchases_range_B': ['mean'],\n        'most_recent_purchases_range_C': ['mean'],\n        'most_recent_purchases_range_D': ['mean'],\n        'most_recent_purchases_range_E': ['mean'],\n        'merchant_group_id': ['nunique'],\n        'merchant_category_id': ['nunique'],\n        'avg_sales_lag3': ['sum','mean'],\n        'avg_purchases_lag3': ['sum','mean'],\n        'active_months_lag3': ['sum', 'mean'],\n        'avg_sales_lag6': ['sum','mean'],\n        'avg_purchases_lag6': ['sum','mean'],\n        'active_months_lag6': ['sum', 'mean'],\n        'avg_sales_lag12': ['sum','mean'],\n        'avg_purchases_lag12': ['sum','mean'],\n        'active_months_lag12': ['sum', 'mean'],\n    }\n    agg_df = df.groupby(['merchant_id']).agg(agg_func)\n    agg_df.columns = [prefix + '_'.join(col).strip() \n                           for col in agg_df.columns.values]\n    agg_df.reset_index(inplace=True)\n    \n    df = (df.groupby('merchant_id')\n          .size()\n          .reset_index(name='{}transactions_count'.format(prefix)))\n    \n    agg_df = pd.merge(df, agg_df, on='merchant_id', how='left')\n    \n    return agg_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2799c872c5f06709ce5364b89831e1ecb58ec443"},"cell_type":"code","source":"df_merch = aggregate_transactions_merchants(df_merch, prefix='merch_')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2bb321682c672c1d5517f0fdce3d15e373182a3b"},"cell_type":"code","source":"df_merch = reduce_mem_usage(df_merch)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"825fc348e2c08d26dc088e3c9e145556afc6d979"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9b695d85beeb4f941d19bda952b79cccb10fa51e"},"cell_type":"code","source":"# df_hist = reduce_mem_usage(pd.merge(df_hist, df_merch, on='merchant_id',how='left'))\n# Leaving this out for now since memory can't handle it","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"731f5e7793aca30b60ea7da3047f7c40b5c65dee"},"cell_type":"code","source":"df_new = pd.merge(df_new, df_merch, on='merchant_id',how='left')\ndf_new = reduce_mem_usage(df_new)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"848305b8874e321894fe45ba79b3e1e90b99b608"},"cell_type":"markdown","source":"Now to aggregate grouping by 'card_id' for the new and hist datasets"},{"metadata":{"trusted":true,"_uuid":"8cbaed1d30ecbfb3b78f50cea55cc1a1a9c9f41e"},"cell_type":"code","source":"def aggregate_transactions_hist_new(df, prefix):  \n    df.loc[:, 'purchase_date'] = pd.DatetimeIndex(df['purchase_date']).\\\n                                      astype(np.int64) * 1e-9\n    \n    agg_func = {\n        'authorized_flag': ['sum', 'mean'],\n        'category_1': ['mean'],\n        'category_2_1.0': ['mean'],\n        'category_2_2.0': ['mean'],\n        'category_2_3.0': ['mean'],\n        'category_2_4.0': ['mean'],\n        'category_2_5.0': ['mean'],\n        'category_3_A': ['mean'],\n        'category_3_B': ['mean'],\n        'category_3_C': ['mean'],\n        'merchant_id': ['nunique'],\n        'city_id': ['nunique'],\n        'state_id': ['nunique'],\n        'subsector_id': ['nunique'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'installments': ['sum', 'mean', 'max', 'min', 'std'],\n        'purchase_date': [np.ptp],\n        'month_lag': ['min', 'max'],\n        \n    }\n    agg_df = df.groupby(['card_id']).agg(agg_func)\n    agg_df.columns = [prefix + '_'.join(col).strip() \n                           for col in agg_df.columns.values]\n    agg_df.reset_index(inplace=True)\n    \n    df = (df.groupby('card_id')\n          .size()\n          .reset_index(name='{}transactions_count'.format(prefix)))\n    \n    agg_df = pd.merge(df, agg_df, on='card_id', how='left')\n    \n    return agg_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fba0621711051fb2e87d8a04f288e0fcb06f9281"},"cell_type":"code","source":"df_hist = pd.get_dummies(df_hist, columns=['category_2', 'category_3'])\ndf_hist['authorized_flag'] = df_hist['authorized_flag'].map({'Y': 1, 'N': 0})\ndf_hist['category_1'] = df_hist['category_1'].map({'Y': 1, 'N': 0})\n\ndf_hist = aggregate_transactions_hist_new(df_hist, prefix='hist_')\n\n\ndf_hist = reduce_mem_usage(df_hist)\n\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4699af17fa7e5310fb85a3f7160eed958b3ba0e9"},"cell_type":"code","source":"df_train = pd.merge(df_train, df_hist, on='card_id',how='left')\ndf_test = pd.merge(df_test, df_hist, on='card_id',how='left')\n\ndf_train = reduce_mem_usage(df_train)\ndf_test = reduce_mem_usage(df_test)\n\nprint(df_train.shape, df_test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9f3e00a706d48dc87e0f0a177dda1e6e4ff04061"},"cell_type":"code","source":"df_new = pd.get_dummies(df_new, columns=['category_2', 'category_3'])\ndf_new['authorized_flag'] = df_new['authorized_flag'].map({'Y': 1, 'N': 0})\ndf_new['category_1'] = df_new['category_1'].map({'Y': 1, 'N': 0})\n\ndf_new = aggregate_transactions_hist_new(df_new, prefix='new_')\n\ndf_new = reduce_mem_usage(df_new)\n\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e69437068bf19f49a38d6eafd1bea4680dc3275b"},"cell_type":"code","source":"df_train = pd.merge(df_train, df_new, on='card_id',how='left')\ndf_test = pd.merge(df_test, df_new, on='card_id',how='left')\n\ndf_train = reduce_mem_usage(df_train)\ndf_test = reduce_mem_usage(df_test)\n\nprint(df_train.shape, df_test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7ca261b17772e028bfbcbdf26dd44f7dd75f9006"},"cell_type":"code","source":"target = df_train['target']\ncols_to_drop = ['card_id', 'first_active_month', 'target']\nuse_cols = [c for c in df_train.columns if c not in cols_to_drop]\nfeatures = list(df_train[use_cols].columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"53b52839bda5b4293366e8aef325a352e85d00ce"},"cell_type":"code","source":"lgb_params = {'num_leaves': 100,\n         'min_data_in_leaf': 30, \n         'objective':'regression',\n         'max_depth': 10,\n         'learning_rate': 0.05,\n         \"boosting\": \"gbdt\",\n         \"feature_fraction\": 0.8,\n         \"bagging_freq\": 1,\n         \"bagging_fraction\": 0.9,\n         \"bagging_seed\": 11,\n         \"metric\": 'rmse',\n         \"lambda_l1\": 0.2,\n         \"verbosity\": -1}\n\nfolds = KFold(n_splits=5, shuffle=True, random_state=15)\noof_lgb = np.zeros(len(df_train))\npredictions_lgb = np.zeros(len(df_test))\n\nfor fold_, (trn_idx, val_idx) in enumerate(folds.split(df_train.values, target.values)):\n    print('-')\n    print(\"Fold {}\".format(fold_ + 1))\n    trn_data = lgb.Dataset(df_train.iloc[trn_idx][features], label=target.iloc[trn_idx])\n    val_data = lgb.Dataset(df_train.iloc[val_idx][features], label=target.iloc[val_idx])\n\n    num_round = 10000\n    clf = lgb.train(lgb_params, trn_data, num_round, valid_sets = [trn_data, val_data], verbose_eval=250, early_stopping_rounds=100)\n    oof_lgb[val_idx] = clf.predict(df_train.iloc[val_idx][features], num_iteration=clf.best_iteration)\n    predictions_lgb += clf.predict(df_test[features], num_iteration=clf.best_iteration) / folds.n_splits","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"435973f3f60ab20c43ddb5d4355a20d1c6704faa"},"cell_type":"code","source":"validation_score = np.sqrt(mean_squared_error(target, oof_lgb))\nvalidation_score","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"87d5585b5bede60b5b03d730b525b654c759cc65"},"cell_type":"code","source":"df_submission = pd.DataFrame({\"card_id\": df_test[\"card_id\"].values})\ndf_submission[\"target\"] = predictions_lgb\ndf_submission.to_csv(\"submission.csv\", index=False)","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}