{"cells":[{"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 \nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nfrom sklearn.model_selection import KFold\nimport warnings\nimport time\nimport sys\nimport datetime\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nfrom sklearn.metrics import mean_squared_error\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\"))\npd.set_option('display.max_columns', 100)\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":"merchants = pd.read_csv('../input/merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d52845edcc6b0f7cc4515f396cfe8b6d256890d9"},"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":"c656b97cbbc716563aee73ecf992785fce1fa20d"},"cell_type":"code","source":"def agg_by_categoryid(merchants):\n    agg_func = {\n        'merchant_id': ['nunique'],\n        'merchant_group_id': ['nunique'],\n        'subsector_id':['nunique'],\n        'numerical_1':['sum'],\n        'numerical_2':['sum'],\n        'category_1': ['nunique'],\n        'most_recent_sales_range': ['nunique'],\n        'most_recent_purchases_range' : ['nunique'],\n        'avg_sales_lag3' : ['sum'],\n        'avg_purchases_lag3': ['sum'],\n        'active_months_lag3': ['sum', 'mean'],\n        'avg_sales_lag6' : ['sum'],\n        'avg_purchases_lag6' : ['sum'],\n        'active_months_lag6': ['sum', 'mean'],\n        'avg_sales_lag12' : ['sum'],\n        'avg_purchases_lag12' : ['sum'],\n        'active_months_lag12': ['sum', 'mean'],\n        'category_4': ['nunique'],\n        'city_id': ['nunique'],\n        'state_id': ['nunique'],\n        'category_2': ['mean']   \n\n    }\n   \n    merchant_category = merchants.groupby(['merchant_category_id']).agg(agg_func)\n    merchant_category.columns = ['_'.join(col).strip() for col in merchant_category.columns.values]\n    merchant_category.reset_index(inplace=True)\n   \n    return merchant_category\n\nmerchant_category = agg_by_categoryid(merchants)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"deb490fc26ccb39884d69a4df415c964b6f70c8f"},"cell_type":"code","source":"def read_data(input_file):\n    df = pd.read_csv(input_file)\n    df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n    df['elapsed_time'] = (datetime.date(2018, 2, 1) - df['first_active_month'].dt.date).dt.days\n    return df\n#_________________________________________\ntrain = read_data('../input/train.csv')\ntest = read_data('../input/test.csv')\n\ntarget = train['target']\ndel train['target']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"532e2d68a3396be258c48a9c0d43ef26c93e70d3"},"cell_type":"code","source":"new_transactions = pd.read_csv('../input/new_merchant_transactions.csv',\n                               parse_dates=['purchase_date'])\n\nhistorical_transactions = pd.read_csv('../input/historical_transactions.csv',\n                                      parse_dates=['purchase_date'])\n\ndef binarize(df):\n    for col in ['authorized_flag', 'category_1']:\n        df[col] = df[col].map({'Y':1, 'N':0})\n    return df\n\nhistorical_transactions = binarize(historical_transactions)\nnew_transactions = binarize(new_transactions)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"09eb7229e145fab5c21591f6c6aa56eb0fc47626"},"cell_type":"code","source":"historical_transactions['month_diff'] = ((datetime.datetime.today() - historical_transactions['purchase_date']).dt.days)//30\nhistorical_transactions['month_diff'] += historical_transactions['month_lag']\n\nnew_transactions['month_diff'] = ((datetime.datetime.today() - new_transactions['purchase_date']).dt.days)//30\nnew_transactions['month_diff'] += new_transactions['month_lag']\n\nhistorical_transactions = pd.get_dummies(historical_transactions, columns=['category_2', 'category_3'])\nnew_transactions = pd.get_dummies(new_transactions, columns=['category_2', 'category_3'])\n\nhistorical_transactions = reduce_mem_usage(historical_transactions)\nnew_transactions = reduce_mem_usage(new_transactions)\n\nagg_fun = {'authorized_flag': ['mean']}\nauth_mean = historical_transactions.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 = historical_transactions[historical_transactions['authorized_flag'] == 1]\nhistorical_transactions = historical_transactions[historical_transactions['authorized_flag'] == 0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5c511d773753344658428bf79ca1fe62539d98aa"},"cell_type":"code","source":"historical_transactions[:5]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"18710c9817dbd2d483cb2503017c71bbd9d5b939"},"cell_type":"code","source":"historical_transactions['purchase_month'] = historical_transactions['purchase_date'].dt.month\nauthorized_transactions['purchase_month'] = authorized_transactions['purchase_date'].dt.month\nnew_transactions['purchase_month'] = new_transactions['purchase_date'].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"47b0a7e4dcf2b06af79bc28edc0ed086995329c3"},"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\n\n\nhistory = aggregate_transactions(historical_transactions)\nhistory.columns = ['hist_' + c if c != 'card_id' else c for c in history.columns]\nhistory[:5]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"669f2e3f24cbde5283a849f69a9a1fa1cd13053f"},"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":"14fb65b32ce4816f136db0b308c9ee7f838ed833"},"cell_type":"code","source":"new = aggregate_transactions(new_transactions)\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":"5f34e0426098a5deb652ed058719b689417f2cd2"},"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":"e669619c74e25eb09faa898402c0e14a3c7ff0ca"},"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\n\nadditional_fields = successive_aggregates(new_transactions, 'category_1', 'purchase_amount')\nadditional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'installments', 'purchase_amount'),\n                                            on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'city_id', 'purchase_amount'),\n                                            on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'category_1', 'installments'),\n                                            on = 'card_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bfc71ba8fe33f2cc244bf3af482961d6a0775dda"},"cell_type":"code","source":"authorized","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fdaf571a0a1af42306017c4ddb1feb310195c3ae"},"cell_type":"code","source":"train = pd.merge(train, history, on='card_id', how='left')\ntest = pd.merge(test, history, on='card_id', how='left')\n\ntrain = pd.merge(train, authorized, on='card_id', how='left')\ntest = pd.merge(test, authorized, on='card_id', how='left')\n\ntrain = pd.merge(train, new, on='card_id', how='left')\ntest = pd.merge(test, new, on='card_id', how='left')\n\ntrain = pd.merge(train, final_group, on='card_id', how='left')\ntest = pd.merge(test, final_group, on='card_id', how='left')\n\ntrain = pd.merge(train, auth_mean, on='card_id', how='left')\ntest = pd.merge(test, auth_mean, on='card_id', how='left')\n\ntrain = pd.merge(train, additional_fields, on='card_id', how='left')\ntest = pd.merge(test, additional_fields, on='card_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d7c0c7153a61076574dcbc7f3d6c07835a4ecca3"},"cell_type":"code","source":"merchant_category.head(1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"abe95c01e627cf8b2e7baf8f73c63c194ce3961a"},"cell_type":"code","source":"merchant_category = merchant_category.rename(columns = {'merchant_category_id': 'hist_merchant_category_id_nunique'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8698d1e8fff85d14b964c12b94d3629af89398de"},"cell_type":"code","source":"merchant_category.head(2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"105429cb7b46519ef20aee0b19e49e9d641be6c2"},"cell_type":"code","source":"train = pd.merge(train, merchant_category, on='hist_merchant_category_id_nunique', how = 'left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"74b166c3b92a257a6e6d980fb1bdf0953b208f3a"},"cell_type":"code","source":"test = pd.merge(test, merchant_category, on='hist_merchant_category_id_nunique', how = 'left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"daaf93eda127357183be7c809d71fbde97d645e4"},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"55e49d894dbc07fa2930dfc4da526435430d78e6"},"cell_type":"code","source":"train = train.drop(['merchant_id_nunique', 'subsector_id_nunique'], axis =1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1258cc7af2081ed0a6749902a30877991af312cc"},"cell_type":"code","source":"test = test.drop(['merchant_id_nunique', 'subsector_id_nunique'], axis =1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"67ae29c42ad0f0a4e7089ce1046583bde3ae3a3e"},"cell_type":"code","source":"features = [c for c in train.columns if c not in ['card_id', 'first_active_month']]\ncategorical_feats = ['feature_2', 'feature_3']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0790a1e0e1b003d1d47453339e0164eeb0ebe625"},"cell_type":"code","source":"features","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6d37bbfb99727d6af4b59e1bd6bcdbce64ba47a5"},"cell_type":"code","source":"param = {'num_leaves': 111,\n         'min_data_in_leaf': 149, \n         'objective':'regression',\n         'max_depth': 9,\n         'learning_rate': 0.005,\n         \"boosting\": \"gbdt\",\n         \"feature_fraction\": 0.7522,\n         \"bagging_freq\": 1,\n         \"bagging_fraction\": 0.7083 ,\n         \"bagging_seed\": 11,\n         \"metric\": 'rmse',\n         \"lambda_l1\": 0.2634,\n         \"random_state\": 133,\n         \"verbosity\": -1}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ebb72687b4ea59105306792674636095a6fe5fa0"},"cell_type":"code","source":"folds = KFold(n_splits=5, shuffle=True, random_state=15)\noof = np.zeros(len(train))\npredictions = np.zeros(len(test))\nstart = time.time()\nfeature_importance_df = pd.DataFrame()\n\nfor fold_, (trn_idx, val_idx) in enumerate(folds.split(train.values, target.values)):\n    print(\"fold n°{}\".format(fold_))\n    trn_data = lgb.Dataset(train.iloc[trn_idx][features],\n                           label=target.iloc[trn_idx],\n                           categorical_feature=categorical_feats\n                          )\n    val_data = lgb.Dataset(train.iloc[val_idx][features],\n                           label=target.iloc[val_idx],\n                           categorical_feature=categorical_feats\n                          )\n\n    num_round = 10000\n    clf = lgb.train(param,\n                    trn_data,\n                    num_round,\n                    valid_sets = [trn_data, val_data],\n                    verbose_eval=100,\n                    early_stopping_rounds = 200)\n    \n    oof[val_idx] = clf.predict(train.iloc[val_idx][features], num_iteration=clf.best_iteration)\n    \n    fold_importance_df = pd.DataFrame()\n    fold_importance_df[\"feature\"] = features\n    fold_importance_df[\"importance\"] = clf.feature_importance()\n    fold_importance_df[\"fold\"] = fold_ + 1\n    feature_importance_df = pd.concat([feature_importance_df, fold_importance_df], axis=0)\n    \n    predictions += clf.predict(test[features], num_iteration=clf.best_iteration) / folds.n_splits\n\nprint(\"CV score: {:<8.5f}\".format(mean_squared_error(oof, target)**0.5))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9f40049774b469333f60da64ac2d606f673fc590"},"cell_type":"code","source":"cols = (feature_importance_df[[\"feature\", \"importance\"]]\n        .groupby(\"feature\")\n        .mean()\n        .sort_values(by=\"importance\", ascending=False)[:1000].index)\n\nbest_features = feature_importance_df.loc[feature_importance_df.feature.isin(cols)]\n\nplt.figure(figsize=(14,25))\nsns.barplot(x=\"importance\",\n            y=\"feature\",\n            data=best_features.sort_values(by=\"importance\",\n                                           ascending=False))\nplt.title('LightGBM Features (avg over folds)')\nplt.tight_layout()\nplt.savefig('lgbm_importances.png')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f990424fa920f86194afd4a6d5e7642dd8851d9"},"cell_type":"code","source":"sub_df = pd.DataFrame({\"card_id\":test[\"card_id\"].values})\nsub_df[\"target\"] = predictions\nsub_df.to_csv(\"submit.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"29a34d73e76de4bf18991596bbbe5477256d1f9f"},"cell_type":"code","source":"!ls","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bd5cd8757aca3371be8f0cf326efac80df305447"},"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}