{"cells":[{"metadata":{"_uuid":"a2e2311e4edfc354ada92d7f0c105e16408ef977"},"cell_type":"markdown","source":" Packages\n First, let's load a few useful Python packages. This section will keep growing in subsequent versions of this EDA."},{"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)\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nfrom sklearn.linear_model import Ridge\nfrom sklearn.model_selection import KFold\nfrom sklearn import model_selection, preprocessing, metrics\nimport warnings\nimport datetime\nimport time\nimport gc\nfrom tqdm import tqdm\nfrom scipy.stats import describe\n%matplotlib inline\nfrom sklearn.model_selection import StratifiedKFold\nfrom sklearn.metrics import mean_squared_error\nimport xgboost as xgb\nwarnings.filterwarnings(\"ignore\")\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":{"trusted":true,"_uuid":"bc6c4e26ab481b57f402be36cd60cfdab286ea48"},"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":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"#Loading Train and Test Data\ndf_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\"])\nprint(\"{} observations and {} features in train set.\".format(df_train.shape[0],df_train.shape[1]))\nprint(\"{} observations and {} features in test set.\".format(df_test.shape[0],df_test.shape[1]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"28c4d4b7d0429d23a211e1f067903632e9cdf26b"},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4bf5c61d13d9e4a52940982f547ae33f546ec45b"},"cell_type":"code","source":"df_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fde606ccb8d7b0723e8ff11bf0498c519fca7f6a"},"cell_type":"code","source":"df_train.target.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6ce5b7d2061017239f66c27a8c13d686fd5dd1ee"},"cell_type":"code","source":"df_train[\"month\"] = df_train[\"first_active_month\"].dt.month\ndf_test[\"month\"] = df_test[\"first_active_month\"].dt.month\ndf_train[\"year\"] = df_train[\"first_active_month\"].dt.year\ndf_test[\"year\"] = df_test[\"first_active_month\"].dt.year\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\ndf_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fb14b8fc57d911e1296bc0ca3e918a3f1ac0d582"},"cell_type":"code","source":"# df_train = pd.get_dummies(df_train, columns=['feature_1', 'feature_2'])\n# df_test = pd.get_dummies(df_test, columns=['feature_1', 'feature_2'])\n# df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2667b7f4b67f17477ae0a02c8a50fec1d40548a9"},"cell_type":"code","source":"# df_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"feb8ad0acb12362134ea1c2b2b7c2817e709d454"},"cell_type":"code","source":"df_hist_trans = pd.read_csv('../input/historical_transactions.csv')\ndf_new_merchant_trans = pd.read_csv('../input/new_merchant_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"df0df5a2557afaac176af7e5618b97e6bc9cf88a"},"cell_type":"code","source":"# df_hist_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"558b286bb4afb28d6d38d12f3f29ad2708789a0f"},"cell_type":"code","source":"# df_new_merchant_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"807bff808d2cd01d148848027fbb170290de9ea8"},"cell_type":"code","source":"def binarize(df):\n    for col in ['authorized_flag', 'category_1']:\n        df[col] = df[col].map({'Y':1, 'N':0})\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"87c7e58990bd31775b10bbe9c27c8fac065b1b49"},"cell_type":"code","source":"df_hist_trans = binarize(df_hist_trans)\ndf_new_merchant_trans = binarize(df_new_merchant_trans)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bca3d6fe2e17e127e27af0224ba28bbca76cba88"},"cell_type":"code","source":"df_hist_trans['purchase_date'] = pd.to_datetime(df_hist_trans['purchase_date'])\ndf_hist_trans['month_diff'] = ((datetime.datetime.today() - df_hist_trans['purchase_date']).dt.days)//30\ndf_hist_trans['month_diff'] += df_hist_trans['month_lag']\n\ndf_new_merchant_trans['purchase_date'] = pd.to_datetime(df_new_merchant_trans['purchase_date'])\ndf_new_merchant_trans['month_diff'] = ((datetime.datetime.today() - df_new_merchant_trans['purchase_date']).dt.days)//30\ndf_new_merchant_trans['month_diff'] += df_new_merchant_trans['month_lag']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ca23fb1f962d3e669779ce77b7b97f7ae15e3e33"},"cell_type":"code","source":"df_hist_trans[:5]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"61c0ec2c99a9f9d49099cac1e41c55f02516108f"},"cell_type":"code","source":"df_hist_trans = pd.get_dummies(df_hist_trans, columns=['category_2', 'category_3'])\ndf_new_merchant_trans = pd.get_dummies(df_new_merchant_trans, columns=['category_2', 'category_3'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7092c22201bc18bc4da86a9bb908c4b94f0d4fa3"},"cell_type":"code","source":"df_hist_trans = reduce_mem_usage(df_hist_trans)\ndf_new_merchant_trans = reduce_mem_usage(df_new_merchant_trans)\n\nagg_fun = {'authorized_flag': ['mean']}\nauth_mean = df_hist_trans.groupby(['card_id']).agg(agg_fun)\nauth_mean.columns = ['_'.join(col).strip() for col in auth_mean.columns.values]\nauth_mean.reset_index(inplace=True)\n\nauthorized_transactions = df_hist_trans[df_hist_trans['authorized_flag'] == 1]\nhistorical_transactions = df_hist_trans[df_hist_trans['authorized_flag'] == 0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5fef4a084930ac1ea5b3ebbc3e74a6c2488648fd"},"cell_type":"code","source":"historical_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bdbf4d4a8c236236e313ca0519dc0fcd5e270d0f"},"cell_type":"code","source":"historical_transactions['purchase_month'] = historical_transactions['purchase_date'].dt.month\nauthorized_transactions['purchase_month'] = authorized_transactions['purchase_date'].dt.month\ndf_new_merchant_trans['purchase_month'] = df_new_merchant_trans['purchase_date'].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"47c77e46b8d5329c9cc38d0c58b021c8695a53b8"},"cell_type":"code","source":"def aggregate_transactions(history):\n    \n    history.loc[:, 'purchase_date'] = pd.DatetimeIndex(history['purchase_date']).\\\n                                      astype(np.int64) * 1e-9\n    \n    agg_func = {\n    'category_1': ['sum', '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    'merchant_category_id': ['nunique'],\n    'state_id': ['nunique'],\n    'city_id': ['nunique'],\n    'subsector_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, 'min', 'max'],\n    'month_lag': ['mean', 'max', 'min', 'std'],\n    'month_diff': ['mean']\n    }\n    \n    agg_history = history.groupby(['card_id']).agg(agg_func)\n    agg_history.columns = ['_'.join(col).strip() for col in agg_history.columns.values]\n    agg_history.reset_index(inplace=True)\n    \n    df = (history.groupby('card_id')\n          .size()\n          .reset_index(name='transactions_count'))\n    \n    agg_history = pd.merge(df, agg_history, on='card_id', how='left')\n    \n    return agg_history","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"98e95b313a640a4d4ee19da7fecd6ebdb1c7e360"},"cell_type":"code","source":"history = aggregate_transactions(historical_transactions)\nhistory.columns = ['hist_' + c if c != 'card_id' else c for c in history.columns]\nhistory[:5]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c5015f21482b87770c46e882594d024ed6898100"},"cell_type":"code","source":"authorized = aggregate_transactions(authorized_transactions)\nauthorized.columns = ['auth_' + c if c != 'card_id' else c for c in authorized.columns]\nauthorized[:5]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5fcbdffeafc2d82fd1e3a3e8b983e494a0787497"},"cell_type":"code","source":"new = aggregate_transactions(df_new_merchant_trans)\nnew.columns = ['new_' + c if c != 'card_id' else c for c in new.columns]\nnew[:5]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0789e0b39e474c31e3b4b5c31c9aafe9c62d615b"},"cell_type":"code","source":"def aggregate_per_month(history):\n    grouped = history.groupby(['card_id', 'month_lag'])\n\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(authorized_transactions) \nfinal_group[:10]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5816fb2b5603e10751e80c721f836fabd92d0cf0"},"cell_type":"code","source":"def successive_aggregates(df, field1, field2):\n    t = df.groupby(['card_id', field1])[field2].mean()\n    u = pd.DataFrame(t).reset_index().groupby('card_id')[field2].agg(['mean', 'min', 'max', 'std'])\n    u.columns = [field1 + '_' + field2 + '_' + col for col in u.columns.values]\n    u.reset_index(inplace=True)\n    return u","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"89f101a476ff37cdac73588a19fb4d3c598d6cbd"},"cell_type":"code","source":"additional_fields = successive_aggregates(df_new_merchant_trans, 'category_1', 'purchase_amount')\nadditional_fields = additional_fields.merge(successive_aggregates(df_new_merchant_trans, 'installments', 'purchase_amount'),\n                                            on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(df_new_merchant_trans, 'city_id', 'purchase_amount'),\n                                            on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(df_new_merchant_trans, 'category_1', 'installments'),\n                                            on = 'card_id', how='left')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7fbe00097f855095c38e6559b2332d35201fcd20"},"cell_type":"code","source":"# We now train the model with the features we previously defined. A first step consists in merging all the dataframes:","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"15c0693f5c2d7049b35b72030af38824ea86e78a"},"cell_type":"code","source":"df_train = pd.merge(df_train, history, on='card_id', how='left')\ndf_test = pd.merge(df_test, history, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, authorized, on='card_id', how='left')\ndf_test = pd.merge(df_test, authorized, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, new, on='card_id', how='left')\ndf_test = pd.merge(df_test, new, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, final_group, on='card_id', how='left')\ndf_test = pd.merge(df_test, final_group, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, auth_mean, on='card_id', how='left')\ndf_test = pd.merge(df_test, auth_mean, on='card_id', how='left')\n\ndf_train = pd.merge(df_train, additional_fields, on='card_id', how='left')\ndf_test = pd.merge(df_test, additional_fields, on='card_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8fe5eb93afabaa72ef2e9d0f6c333a28f566768c"},"cell_type":"code","source":"target = df_train['target']\ndrops = ['card_id', 'first_active_month', 'target']\nuse_cols = [c for c in df_train.columns if c not in drops]\nfeatures = list(df_train[use_cols].columns)\ndf_train[features].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea6e8076c06dec4b7d8c6d7c5fdceb71a0c52083"},"cell_type":"code","source":"param = {'num_leaves': 50,\n         'min_data_in_leaf': 30, \n         'objective':'regression',\n         'max_depth': -1,\n         'learning_rate': 0.005,\n         \"boosting\": \"gbdt\",\n         \"feature_fraction\": 0.9,\n         \"bagging_freq\": 1,\n         \"bagging_fraction\": 0.9,\n         \"bagging_seed\": 11,\n         \"metric\": 'rmse',\n         \"lambda_l1\": 0.1,\n         \"verbosity\": -1}\n\nfolds = KFold(n_splits=5, shuffle=True, random_state=15)\noof = np.zeros(len(df_train))\npredictions = 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(param, trn_data, num_round, valid_sets = [trn_data, val_data], verbose_eval=100, early_stopping_rounds=100)\n    oof[val_idx] = clf.predict(df_train.iloc[val_idx][features], num_iteration=clf.best_iteration)\n    predictions += clf.predict(df_test[features], num_iteration=clf.best_iteration) / folds.n_splits","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef274c5ef0d18fc2ba854b5580aaf98208738128"},"cell_type":"code","source":"np.save('oof', oof)\nnp.save('predictions', predictions)\nprint(\"CV score: {:<8.5f}\".format(mean_squared_error(oof, target)**0.5))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d282d37f25d2bf9e1ecc4fb1d4f4fb9f47019ff6"},"cell_type":"code","source":"from sklearn.metrics import mean_squared_error\nval_score = np.sqrt(mean_squared_error(target, oof))\nval_score","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3967042ed22f0df42517fac09bb80e23d99e099a"},"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(12,10))\nlgb.plot_importance(clf, max_num_features=30, height=0.5, ax=ax, title='Feature importance', xlabel='Feature importance', ylabel='Features')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d0a1b178913820618fc51e7698a4d2a2f9f895f"},"cell_type":"code","source":"sorted(list(zip(clf.feature_importance(), features)), reverse=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"81ae42f9b267d0cf5267884d8edb735484ca2849"},"cell_type":"code","source":"df_sub = pd.DataFrame({\"card_id\":df_test[\"card_id\"].values})\ndf_sub[\"target\"] = predictions\ndf_sub.to_csv(\"sub_val_{}.csv\".format(val_score), index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d81dd6fb5452571ea650b3db5e43f5e07b07aba7"},"cell_type":"code","source":"df_sub.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d617e59078799dc0736304f36c148871d752a2e"},"cell_type":"code","source":"df_sub.to_csv(\"submission.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1674606d8386f7a32525b51f819741f0c5c6f516"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d20e3e7c6e0c5bfb52efc080423a38bf6d01ee0f"},"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}