{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import datetime\nimport gc\nimport lightgbm as lgb\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport os\nimport pandas as pd\nimport seaborn as sns\nimport time\nimport warnings\n\nfrom contextlib import contextmanager\nfrom pandas.core.common import SettingWithCopyWarning\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import KFold, StratifiedKFold\n\nwarnings.simplefilter(action='ignore', category=SettingWithCopyWarning)\nwarnings.simplefilter(action='ignore', category=FutureWarning)\n\nFEATS_EXCLUDED = ['first_active_month', 'target', 'card_id', 'outliers',\n                  'hist_purchase_date_max', 'hist_purchase_date_min', 'hist_card_id_size',\n                  'new_purchase_date_max', 'new_purchase_date_min', 'new_card_id_size',\n                  'OOF_PRED', 'month_0']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dbe6fcaf479d24e4a07c5271d0201f75e31a113f"},"cell_type":"code","source":"#★★★ビットコインのデータフレームを返す\ndef additional_data():\n    df_add_data = pd.read_csv('../input/btcusd01/btcusd.csv')\n    df_add_data['ds'] = pd.to_datetime(df_add_data['ds'])\n    df_add_data['y2'] = (df_add_data['y'] - df_add_data['y'].shift(1) )/df_add_data['y'].shift(1)\n    df_add_data['y2'].fillna(0.0,inplace=True)\n    df_add_data['5MA'] = df_add_data['y'].rolling(5).mean()\n    df_add_data['5MA_diff'] = (df_add_data['y'] - df_add_data['5MA']) / df_add_data['5MA']\n    df_add_data['5std'] = df_add_data['y'].rolling(5).std()\n    df_add_data['5BBD'] = ((df_add_data['y'] - df_add_data['5MA']) / df_add_data['5std']).round(2)\n    df_add_data['15MA'] = df_add_data['y'].rolling(15).mean()\n    df_add_data['15MA_diff'] = (df_add_data['y'] - df_add_data['15MA']) / df_add_data['15MA']\n    df_add_data['15std'] = df_add_data['y'].rolling(15).std()\n    df_add_data['15BBD'] = ((df_add_data['y'] - df_add_data['15MA']) / df_add_data['15std']).round(2)\n    df_add_data['30MA'] = df_add_data['y'].rolling(30).mean()\n    df_add_data['30MA_diff'] = (df_add_data['y'] - df_add_data['30MA']) / df_add_data['30MA']\n    df_add_data['30std'] = df_add_data['y'].rolling(30).std()\n    df_add_data['30BBD'] = ((df_add_data['y'] - df_add_data['30MA']) / df_add_data['30std']).round(2)\n    df_add_data['60MA'] = df_add_data['y'].rolling(60).mean()\n    df_add_data['60MA_diff'] = (df_add_data['y'] - df_add_data['60MA']) / df_add_data['60MA']\n    df_add_data['60std'] = df_add_data['y'].rolling(60).std()\n    df_add_data['60BBD'] = ((df_add_data['y'] - df_add_data['60MA']) / df_add_data['60std']).round(2)\n\n    return df_add_data\n\n#★★★マーチャントデータのデータフレームを返す\ndef merchant_data():\n    df_merchant = pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv')\n    df_merchant['category_1'] = df_merchant['category_1'].map({'Y': 1, 'N': 0}).astype(int)\n    df_merchant['category_4'] = df_merchant['category_4'].map({'Y': 1, 'N': 0}).astype(int)\n    df_merchant['most_recent_sales_range'] = df_merchant['most_recent_sales_range'].map({'A':4, 'B':3, 'C':2, 'D':1, 'E':0}).astype(int)\n    df_merchant['most_recent_purchases_range'] = df_merchant['most_recent_purchases_range'].map({'A':4, 'B':3, 'C':2, 'D':1, 'E':0}).astype(int)\n    df_merchant['avg_sales_lag3'] = df_merchant['avg_sales_lag3'].fillna(0)\n    df_merchant['avg_sales_lag6'] = df_merchant['avg_sales_lag6'].fillna(0)\n    df_merchant['avg_sales_lag12'] = df_merchant['avg_sales_lag12'].fillna(0)\n    df_merchant['category_2'] = df_merchant['category_2'].fillna(-1)\n    \n    df_merchant.columns = [('field_'+ c) if c != 'merchant_id' else 'merchant_id' for c in df_merchant.columns ]#結合用にIDだけはそのままで、他は識別子を付ける\n    return df_merchant","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8143cde3a492467e7a6853af2460e313a568f9a"},"cell_type":"code","source":"@contextmanager\ndef timer(title):\n    t0 = time.time()\n    yield\n    print(\"{} - done in {:.0f}s\".format(title, time.time() - t0))\n\n# rmse\ndef rmse(y_true, y_pred):\n    return np.sqrt(mean_squared_error(y_true, y_pred))\n\n# One-hot encoding for categorical columns with get_dummies\ndef one_hot_encoder(df, nan_as_category = True):\n    original_columns = list(df.columns)\n    categorical_columns = [col for col in df.columns if df[col].dtype == 'object']\n    df = pd.get_dummies(df, columns= categorical_columns, dummy_na= nan_as_category)\n    new_columns = [c for c in df.columns if c not in original_columns]\n    return df, new_columns\n    \n# Display/plot feature importance\ndef display_importances(feature_importance_df_):\n    cols = feature_importance_df_[[\"feature\", \"importance\"]].groupby(\"feature\").mean().sort_values(by=\"importance\", ascending=False)[:40].index\n    best_features = feature_importance_df_.loc[feature_importance_df_.feature.isin(cols)]\n\n    plt.figure(figsize=(8, 10))\n    sns.barplot(x=\"importance\", y=\"feature\", data=best_features.sort_values(by=\"importance\", ascending=False))\n    plt.title('LightGBM Features (avg over folds)')\n    plt.tight_layout()\n    plt.savefig('lgbm_importances.png')\n\n# reduce memory\ndef 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\n    end_mem = df.memory_usage().sum() / 1024**2\n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"89078623804505f0df1c59a0a6799c51ec65025b"},"cell_type":"code","source":"# preprocessing train & test\ndef train_test(num_rows=None):\n\n    # load csv\n    train_df = pd.read_csv('../input/elo-merchant-category-recommendation/train.csv', index_col=['card_id'], nrows=num_rows)\n    test_df = pd.read_csv('../input/elo-merchant-category-recommendation/test.csv', index_col=['card_id'], nrows=num_rows)\n    \n\n    print(\"Train samples: {}, test samples: {}\".format(len(train_df), len(test_df)))\n\n    # outlier\n    train_df['outliers'] = 0\n    train_df.loc[train_df['target'] < -30, 'outliers'] = 1\n\n    # set target as nan\n    test_df['target'] = np.nan\n\n    # merge\n    df = train_df.append(test_df)\n\n    del train_df, test_df\n    gc.collect()\n\n    # to datetime\n    df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n    \n    # datetime features\n    df['quarter'] = df['first_active_month'].dt.quarter\n    df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n\n    df['days_feature1'] = df['elapsed_time'] * df['feature_1']\n    df['days_feature2'] = df['elapsed_time'] * df['feature_2']\n    df['days_feature3'] = df['elapsed_time'] * df['feature_3']\n\n    df['days_feature1_ratio'] = df['feature_1'] / df['elapsed_time']\n    df['days_feature2_ratio'] = df['feature_2'] / df['elapsed_time']\n    df['days_feature3_ratio'] = df['feature_3'] / df['elapsed_time']\n\n    # one hot encoding\n    df, cols = one_hot_encoder(df, nan_as_category=False)\n\n    for f in ['feature_1','feature_2','feature_3']:\n        order_label = df.groupby([f])['outliers'].mean()\n        df[f] = df[f].map(order_label)\n\n    df['feature_sum'] = df['feature_1'] + df['feature_2'] + df['feature_3']\n    df['feature_mean'] = df['feature_sum']/3\n    df['feature_max'] = df[['feature_1', 'feature_2', 'feature_3']].max(axis=1)\n    df['feature_min'] = df[['feature_1', 'feature_2', 'feature_3']].min(axis=1)\n    df['feature_var'] = df[['feature_1', 'feature_2', 'feature_3']].std(axis=1)\n\n    \n    df_add_data = additional_data()#★★★ビットコインのデータフレームを用意\n    df_add_data = df_add_data.rename(columns={'ds': 'first_active_month'})#★★★下記の結合のため、カラムの名前を変更\n    df = df.reset_index().merge( df_add_data, on='first_active_month', how='left').set_index('card_id')#★★★ビットコインのデータを訓練データに結合\n    del df_add_data;gc.collect()#★★★メモリ節約のため、手動でガベージコレクション。\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"00010ed3ce0e9c17167b3da59cbea805c2550ddf"},"cell_type":"code","source":"# preprocessing historical transactions\ndef historical_transactions(num_rows=None):\n    # load csv\n    hist_df = pd.read_csv('../input/elo-merchant-category-recommendation/historical_transactions.csv', nrows=num_rows)\n\n    # fillna\n    hist_df['category_2'].fillna(1.0,inplace=True)\n    hist_df['category_3'].fillna('A',inplace=True)\n    hist_df['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n    hist_df['installments'].replace(-1, np.nan,inplace=True)\n    hist_df['installments'].replace(999, np.nan,inplace=True)\n\n    # trim\n    hist_df['purchase_amount'] = hist_df['purchase_amount'].apply(lambda x: min(x, 0.8))\n\n    # Y/N to 1/0\n    hist_df['authorized_flag'] = hist_df['authorized_flag'].map({'Y': 1, 'N': 0}).astype(int)\n    hist_df['category_1'] = hist_df['category_1'].map({'Y': 1, 'N': 0}).astype(int)\n    hist_df['category_3'] = hist_df['category_3'].map({'A':0, 'B':1, 'C':2})\n\n    # datetime features\n    hist_df['purchase_date'] = pd.to_datetime(hist_df['purchase_date'])\n    hist_df['month'] = hist_df['purchase_date'].dt.month\n    hist_df['day'] = hist_df['purchase_date'].dt.day\n    hist_df['ds'] = hist_df['purchase_date'].dt.date#★★★参考にしたカーネルでは日付のカラムが無かったため、結合用に追加    \n    hist_df['hour'] = hist_df['purchase_date'].dt.hour\n    hist_df['weekofyear'] = hist_df['purchase_date'].dt.weekofyear\n    hist_df['weekday'] = hist_df['purchase_date'].dt.weekday\n    hist_df['weekend'] = (hist_df['purchase_date'].dt.weekday >=5).astype(int)\n    \n    \n    df_add_data = additional_data()#★★★ビットコインのデータフレームを用意\n    df_add_data['ds'] = df_add_data['ds'].dt.date#★★★下記の結合のため、'ds'カラムを用意\n    hist_df = pd.merge(hist_df, df_add_data, on='ds', how='left')#★★★ビットコインのデータをhistデータに結合\n    del df_add_data;gc.collect()#★★★メモリ節約のため、手動でガベージコレクション。\n    \n\n    # additional features\n    hist_df['price'] = hist_df['purchase_amount'] / hist_df['installments']\n\n    #Christmas : December 25 2017\n    hist_df['Christmas_Day_2017']=(pd.to_datetime('2017-12-25')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n    #Mothers Day: May 14 2017\n    hist_df['Mothers_Day_2017']=(pd.to_datetime('2017-06-04')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n    #fathers day: August 13 2017\n    hist_df['fathers_day_2017']=(pd.to_datetime('2017-08-13')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n    #Childrens day: October 12 2017\n    hist_df['Children_day_2017']=(pd.to_datetime('2017-10-12')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n    #Valentine's Day : 12th June, 2017\n    hist_df['Valentine_Day_2017']=(pd.to_datetime('2017-06-12')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n    #Black Friday : 24th November 2017\n    hist_df['Black_Friday_2017']=(pd.to_datetime('2017-11-24') - hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n\n    #2018\n    #Mothers Day: May 13 2018\n    hist_df['Mothers_Day_2018']=(pd.to_datetime('2018-05-13')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n\n    hist_df['month_diff'] = ((datetime.datetime.today() - hist_df['purchase_date']).dt.days)//30\n    hist_df['month_diff'] += hist_df['month_lag']\n\n    # additional features\n    hist_df['duration'] = hist_df['purchase_amount']*hist_df['month_diff']\n    hist_df['amount_month_ratio'] = hist_df['purchase_amount']/hist_df['month_diff']\n\n    # reduce memory usage\n    hist_df = reduce_mem_usage(hist_df)\n\n    col_unique =['subsector_id', 'merchant_id', 'merchant_category_id']\n    col_seas = ['month', 'hour', 'weekofyear', 'weekday', 'day']\n\n    aggs = {}\n    for col in col_unique:\n        aggs[col] = ['nunique']\n\n    for col in col_seas:\n        aggs[col] = ['nunique', 'mean', 'min', 'max']\n\n    aggs['purchase_amount'] = ['sum','max','min','mean','var','skew']\n    aggs['installments'] = ['sum','max','mean','var','skew']\n    aggs['purchase_date'] = ['max','min']\n    aggs['month_lag'] = ['max','min','mean','var','skew']\n    aggs['month_diff'] = ['max','min','mean','var','skew']\n    aggs['authorized_flag'] = ['mean']\n    aggs['weekend'] = ['mean'] # overwrite\n    aggs['weekday'] = ['mean'] # overwrite\n    aggs['day'] = ['nunique', 'mean', 'min'] # overwrite\n    aggs['category_1'] = ['mean']\n    aggs['category_2'] = ['mean']\n    aggs['category_3'] = ['mean']\n    aggs['card_id'] = ['size','count']\n    aggs['price'] = ['sum','mean','max','min','var']\n    aggs['Christmas_Day_2017'] = ['mean']\n    aggs['Mothers_Day_2017'] = ['mean']\n    aggs['fathers_day_2017'] = ['mean']\n    aggs['Children_day_2017'] = ['mean']\n    aggs['Valentine_Day_2017'] = ['mean']\n    aggs['Black_Friday_2017'] = ['mean']\n    aggs['Mothers_Day_2018'] = ['mean']\n    aggs['duration']=['mean','min','max','var','skew']\n    aggs['amount_month_ratio']=['mean','min','max','var','skew']\n    aggs['y'] = ['sum','max','min','mean','var']#★★★ビットコインの終値価格\n\n    for col in ['category_2','category_3']:\n        hist_df[col+'_mean'] = hist_df.groupby([col])['purchase_amount'].transform('mean')\n        hist_df[col+'_min'] = hist_df.groupby([col])['purchase_amount'].transform('min')\n        hist_df[col+'_max'] = hist_df.groupby([col])['purchase_amount'].transform('max')\n        hist_df[col+'_sum'] = hist_df.groupby([col])['purchase_amount'].transform('sum')\n        aggs[col+'_mean'] = ['mean']\n\n    hist_df = hist_df.reset_index().groupby('card_id').agg(aggs)\n\n    # change column name\n    hist_df.columns = pd.Index([e[0] + \"_\" + e[1] for e in hist_df.columns.tolist()])\n    hist_df.columns = ['hist_'+ c for c in hist_df.columns]\n\n    hist_df['hist_purchase_date_diff'] = (hist_df['hist_purchase_date_max']-hist_df['hist_purchase_date_min']).dt.days\n    hist_df['hist_purchase_date_average'] = hist_df['hist_purchase_date_diff']/hist_df['hist_card_id_size']\n    hist_df['hist_purchase_date_uptonow'] = (datetime.datetime.today()-hist_df['hist_purchase_date_max']).dt.days\n    hist_df['hist_purchase_date_uptomin'] = (datetime.datetime.today()-hist_df['hist_purchase_date_min']).dt.days\n\n    # reduce memory usage\n    hist_df = reduce_mem_usage(hist_df)\n\n    return hist_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"58a075a4245eee07c8a34d2a1f8439caecb0d711"},"cell_type":"code","source":"# preprocessing new_merchant_transactions\ndef new_merchant_transactions(num_rows=None):\n    # load csv\n    new_merchant_df = pd.read_csv('../input/elo-merchant-category-recommendation/new_merchant_transactions.csv', nrows=num_rows)\n    \n    # fillna\n    new_merchant_df['category_2'].fillna(1.0,inplace=True)\n    new_merchant_df['category_3'].fillna('A',inplace=True)\n    new_merchant_df['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n    new_merchant_df['installments'].replace(-1, np.nan,inplace=True)\n    new_merchant_df['installments'].replace(999, np.nan,inplace=True)\n\n    # trim\n    new_merchant_df['purchase_amount'] = new_merchant_df['purchase_amount'].apply(lambda x: min(x, 0.8))\n\n    # Y/N to 1/0\n    new_merchant_df['authorized_flag'] = new_merchant_df['authorized_flag'].map({'Y': 1, 'N': 0}).astype(int)\n    new_merchant_df['category_1'] = new_merchant_df['category_1'].map({'Y': 1, 'N': 0}).astype(int)\n    new_merchant_df['category_3'] = new_merchant_df['category_3'].map({'A':0, 'B':1, 'C':2}).astype(int)\n\n    # datetime features\n    new_merchant_df['purchase_date'] = pd.to_datetime(new_merchant_df['purchase_date'])\n    new_merchant_df['month'] = new_merchant_df['purchase_date'].dt.month\n    new_merchant_df['day'] = new_merchant_df['purchase_date'].dt.day\n    \n    new_merchant_df['ds'] = new_merchant_df['purchase_date'].dt.date#★★★参考にしたカーネルでは日付のカラムが無かったため、結合用に追加      \n\n    new_merchant_df['hour'] = new_merchant_df['purchase_date'].dt.hour\n    new_merchant_df['weekofyear'] = new_merchant_df['purchase_date'].dt.weekofyear\n    new_merchant_df['weekday'] = new_merchant_df['purchase_date'].dt.weekday\n    new_merchant_df['weekend'] = (new_merchant_df['purchase_date'].dt.weekday >=5).astype(int)\n    \n    df_add_data = additional_data()#★★★ビットコインのデータフレームを用意\n    df_add_data['ds'] = df_add_data['ds'].dt.date#★★★  下記の結合のため、'ds'カラムを用意\n    new_merchant_df = pd.merge(new_merchant_df, df_add_data, on='ds', how='left')#★★★ビットコインのデータをnew_merchantデータに結合\n    del df_add_data;gc.collect()#★★★メモリ節約のため、手動でガベージコレクション。\n    \n    merchant_df = merchant_data()#★★★マーチャントデータのデータフレームを用意\n    new_merchant_df = pd.merge(new_merchant_df,merchant_df, on='merchant_id', how='left')#★★★マーチャントデータをnew_merchantデータに結合\n    del merchant_df;gc.collect()#★★★メモリ節約のため、手動でガベージコレクション。\n    \n\n    # additional features\n    new_merchant_df['price'] = new_merchant_df['purchase_amount'] / new_merchant_df['installments']\n\n    #Christmas : December 25 2017\n    new_merchant_df['Christmas_Day_2017']=(pd.to_datetime('2017-12-25')-new_merchant_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n    #Childrens day: October 12 2017\n    new_merchant_df['Children_day_2017']=(pd.to_datetime('2017-10-12')-new_merchant_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n    #Black Friday : 24th November 2017\n    new_merchant_df['Black_Friday_2017']=(pd.to_datetime('2017-11-24') - new_merchant_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n\n    #Mothers Day: May 13 2018\n    new_merchant_df['Mothers_Day_2018']=(pd.to_datetime('2018-05-13')-new_merchant_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n\n    new_merchant_df['month_diff'] = ((datetime.datetime.today() - new_merchant_df['purchase_date']).dt.days)//30\n    new_merchant_df['month_diff'] += new_merchant_df['month_lag']\n\n    # additional features\n    new_merchant_df['duration'] = new_merchant_df['purchase_amount']*new_merchant_df['month_diff']\n    new_merchant_df['amount_month_ratio'] = new_merchant_df['purchase_amount']/new_merchant_df['month_diff']\n\n    # reduce memory usage\n    new_merchant_df = reduce_mem_usage(new_merchant_df)\n    \n    col_unique =['subsector_id', 'merchant_id', 'merchant_category_id','field_merchant_group_id','field_city_id']\n    col_seas = ['month', 'hour', 'weekofyear', 'weekday', 'day']\n\n    aggs = {}\n    for col in col_unique:\n        aggs[col] = ['nunique']\n\n    for col in col_seas:\n        aggs[col] = ['nunique', 'mean', 'min', 'max']\n\n    aggs['purchase_amount'] = ['sum','max','min','mean','var','skew']\n    aggs['installments'] = ['sum','max','mean','var','skew']\n    aggs['purchase_date'] = ['max','min']\n    aggs['month_lag'] = ['max','min','mean','var','skew']\n    aggs['month_diff'] = ['mean','var','skew']\n    aggs['weekend'] = ['mean']\n    aggs['month'] = ['mean', 'min', 'max']\n    aggs['weekday'] = ['mean', 'min', 'max']\n    aggs['category_1'] = ['mean']\n    aggs['category_2'] = ['mean']\n    aggs['category_3'] = ['mean']\n    aggs['card_id'] = ['size','count']\n    aggs['price'] = ['mean','max','min','var']\n    aggs['Christmas_Day_2017'] = ['mean']\n    aggs['Children_day_2017'] = ['mean']\n    aggs['Black_Friday_2017'] = ['mean']\n    aggs['Mothers_Day_2018'] = ['mean']\n    aggs['duration']=['mean','min','max','var','skew']\n    aggs['amount_month_ratio']=['mean','min','max','var','skew']\n    aggs['y'] = ['sum','max','min','mean','var']#★★★ビットコインの終値価格\n\n    #マーチャントデータの統計量\n    #★★★★★★★★★★★★★★★★★★★★★★★★\n    aggs['field_category_1'] = ['mean']\n    aggs['field_category_2'] = ['mean']\n    aggs['field_category_4'] = ['mean']\n    aggs['field_numerical_1'] = ['mean']\n    aggs['field_numerical_2'] = ['mean']\n    aggs['field_most_recent_sales_range'] = ['mean','max','min']\n    aggs['field_most_recent_purchases_range'] = ['mean','max','min']\n    aggs['field_avg_sales_lag3'] = ['mean','max','min']\n    aggs['field_avg_purchases_lag3'] = ['mean','max','min']\n    aggs['field_active_months_lag3'] = ['mean','max','min']\n    aggs['field_avg_sales_lag6'] = ['mean','max','min']\n    aggs['field_avg_purchases_lag6'] = ['mean','max','min']\n    aggs['field_active_months_lag6'] = ['mean','max','min']\n    aggs['field_avg_sales_lag12'] = ['mean','max','min']\n    aggs['field_avg_purchases_lag12'] = ['mean','max','min']\n    aggs['field_active_months_lag12'] = ['mean','max','min']\n    #★★★★★★★★★★★★★★★★★★★★★★★★\n    \n    for col in ['category_2','category_3']:\n        new_merchant_df[col+'_mean'] = new_merchant_df.groupby([col])['purchase_amount'].transform('mean')\n        new_merchant_df[col+'_min'] = new_merchant_df.groupby([col])['purchase_amount'].transform('min')\n        new_merchant_df[col+'_max'] = new_merchant_df.groupby([col])['purchase_amount'].transform('max')\n        new_merchant_df[col+'_sum'] = new_merchant_df.groupby([col])['purchase_amount'].transform('sum')\n        aggs[col+'_mean'] = ['mean']\n\n    new_merchant_df = new_merchant_df.reset_index().groupby('card_id').agg(aggs)\n\n    # change column name\n    new_merchant_df.columns = pd.Index([e[0] + \"_\" + e[1] for e in new_merchant_df.columns.tolist()])\n    new_merchant_df.columns = ['new_'+ c for c in new_merchant_df.columns]\n\n    new_merchant_df['new_purchase_date_diff'] = (new_merchant_df['new_purchase_date_max']-new_merchant_df['new_purchase_date_min']).dt.days\n    new_merchant_df['new_purchase_date_average'] = new_merchant_df['new_purchase_date_diff']/new_merchant_df['new_card_id_size']\n    new_merchant_df['new_purchase_date_uptonow'] = (datetime.datetime.today()-new_merchant_df['new_purchase_date_max']).dt.days\n    new_merchant_df['new_purchase_date_uptomin'] = (datetime.datetime.today()-new_merchant_df['new_purchase_date_min']).dt.days\n\n    # reduce memory usage\n    new_merchant_df = reduce_mem_usage(new_merchant_df)\n\n    return new_merchant_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"537d287c6ba83fe712a545a29fe3f1911f0166ea"},"cell_type":"code","source":"# additional features\ndef additional_features(df):\n    df['hist_first_buy'] = (df['hist_purchase_date_min'] - df['first_active_month']).dt.days\n    df['hist_last_buy'] = (df['hist_purchase_date_max'] - df['first_active_month']).dt.days\n    df['new_first_buy'] = (df['new_purchase_date_min'] - df['first_active_month']).dt.days\n    df['new_last_buy'] = (df['new_purchase_date_max'] - df['first_active_month']).dt.days\n\n    date_features=['hist_purchase_date_max','hist_purchase_date_min',\n                   'new_purchase_date_max', 'new_purchase_date_min']\n\n    for f in date_features:\n        df[f] = df[f].astype(np.int64) * 1e-9\n\n    df['card_id_total'] = df['new_card_id_size']+df['hist_card_id_size']\n    df['card_id_cnt_total'] = df['new_card_id_count']+df['hist_card_id_count']\n    df['card_id_cnt_ratio'] = df['new_card_id_count']/df['hist_card_id_count']\n    df['purchase_amount_total'] = df['new_purchase_amount_sum']+df['hist_purchase_amount_sum']\n    df['purchase_amount_mean'] = df['new_purchase_amount_mean']+df['hist_purchase_amount_mean']\n    df['purchase_amount_max'] = df['new_purchase_amount_max']+df['hist_purchase_amount_max']\n    df['purchase_amount_min'] = df['new_purchase_amount_min']+df['hist_purchase_amount_min']\n    df['purchase_amount_ratio'] = df['new_purchase_amount_sum']/df['hist_purchase_amount_sum']\n    df['month_diff_mean'] = df['new_month_diff_mean']+df['hist_month_diff_mean']\n    df['month_diff_ratio'] = df['new_month_diff_mean']/df['hist_month_diff_mean']\n    df['month_lag_mean'] = df['new_month_lag_mean']+df['hist_month_lag_mean']\n    df['month_lag_max'] = df['new_month_lag_max']+df['hist_month_lag_max']\n    df['month_lag_min'] = df['new_month_lag_min']+df['hist_month_lag_min']\n    df['category_1_mean'] = df['new_category_1_mean']+df['hist_category_1_mean']\n    df['installments_total'] = df['new_installments_sum']+df['hist_installments_sum']\n    df['installments_mean'] = df['new_installments_mean']+df['hist_installments_mean']\n    df['installments_max'] = df['new_installments_max']+df['hist_installments_max']\n    df['installments_ratio'] = df['new_installments_sum']/df['hist_installments_sum']\n    df['price_total'] = df['purchase_amount_total'] / df['installments_total']\n    df['price_mean'] = df['purchase_amount_mean'] / df['installments_mean']\n    df['price_max'] = df['purchase_amount_max'] / df['installments_max']\n    df['duration_mean'] = df['new_duration_mean']+df['hist_duration_mean']\n    df['duration_min'] = df['new_duration_min']+df['hist_duration_min']\n    df['duration_max'] = df['new_duration_max']+df['hist_duration_max']\n    df['amount_month_ratio_mean']=df['new_amount_month_ratio_mean']+df['hist_amount_month_ratio_mean']\n    df['amount_month_ratio_min']=df['new_amount_month_ratio_min']+df['hist_amount_month_ratio_min']\n    df['amount_month_ratio_max']=df['new_amount_month_ratio_max']+df['hist_amount_month_ratio_max']\n    df['new_CLV'] = df['new_card_id_count'] * df['new_purchase_amount_sum'] / df['new_month_diff_mean']\n    df['hist_CLV'] = df['hist_card_id_count'] * df['hist_purchase_amount_sum'] / df['hist_month_diff_mean']\n    df['CLV_ratio'] = df['new_CLV'] / df['hist_CLV']\n\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bbee0d4b42b59c827f2e67993401c5ca3a5031e4"},"cell_type":"code","source":"# LightGBM GBDT with KFold or Stratified KFold\ndef kfold_lightgbm(train_df, test_df, num_folds, stratified = False, debug= False,FileName = 'submission'):\n    print(\"Starting LightGBM. Train shape: {}, test shape: {}\".format(train_df.shape, test_df.shape))\n\n    # Cross validation model\n    if stratified:\n        folds = StratifiedKFold(n_splits= num_folds, shuffle=True, random_state=326)\n    else:\n        folds = KFold(n_splits= num_folds, shuffle=True, random_state=326)\n\n    # Create arrays and dataframes to store results\n    oof_preds = np.zeros(train_df.shape[0])\n    sub_preds = np.zeros(test_df.shape[0])\n    feature_importance_df = pd.DataFrame()\n    feats = [f for f in train_df.columns if f not in FEATS_EXCLUDED]\n\n    # k-fold\n    for n_fold, (train_idx, valid_idx) in enumerate(folds.split(train_df[feats], train_df['outliers'])):\n        train_x, train_y = train_df[feats].iloc[train_idx], train_df['target'].iloc[train_idx]\n        valid_x, valid_y = train_df[feats].iloc[valid_idx], train_df['target'].iloc[valid_idx]\n\n        # set data structure\n        lgb_train = lgb.Dataset(train_x,\n                                label=train_y,\n                                free_raw_data=False)\n        lgb_test = lgb.Dataset(valid_x,\n                               label=valid_y,\n                               free_raw_data=False)\n\n        # params optimized by optuna\n        params ={\n                'task': 'train',\n                'boosting': 'goss',\n                'objective': 'regression',\n                'metric': 'rmse',\n                'learning_rate': 0.01,\n                'subsample': 0.9855232997390695,\n                'max_depth': 7,\n                'top_rate': 0.9064148448434349,\n                'num_leaves': 63,\n                'min_child_weight': 41.9612869171337,\n                'other_rate': 0.0721768246018207,\n                'reg_alpha': 9.677537745007898,\n                'colsample_bytree': 0.5665320670155495,\n                'min_split_gain': 9.820197773625843,\n                'reg_lambda': 8.2532317400459,\n                'min_data_in_leaf': 21,\n                'verbose': -1,\n                'seed':int(2**n_fold),\n                'bagging_seed':int(2**n_fold),\n                'drop_seed':int(2**n_fold)\n                }\n\n        reg = lgb.train(\n                        params,\n                        lgb_train,\n                        valid_sets=[lgb_train, lgb_test],\n                        valid_names=['train', 'test'],\n                        num_boost_round=10000,\n                        early_stopping_rounds= 200,\n                        verbose_eval=100\n                        )\n\n        oof_preds[valid_idx] = reg.predict(valid_x, num_iteration=reg.best_iteration)\n        sub_preds += reg.predict(test_df[feats], num_iteration=reg.best_iteration) / folds.n_splits\n\n        fold_importance_df = pd.DataFrame()\n        fold_importance_df[\"feature\"] = feats\n        fold_importance_df[\"importance\"] = np.log1p(reg.feature_importance(importance_type='gain', iteration=reg.best_iteration))\n        fold_importance_df[\"fold\"] = n_fold + 1\n        feature_importance_df = pd.concat([feature_importance_df, fold_importance_df], axis=0)\n        print('Fold %2d RMSE : %.6f' % (n_fold + 1, rmse(valid_y, oof_preds[valid_idx])))\n        del reg, train_x, train_y, valid_x, valid_y\n        gc.collect()\n\n    # display importances\n    display_importances(feature_importance_df)\n\n    if not debug:\n        # save submission file\n        test_df.loc[:,'target'] = sub_preds\n        test_df = test_df.reset_index()\n        test_df[['card_id', 'target']].to_csv(FileName, index=False)\n    \n    return feature_importance_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5f1c724bafe483b26fa5597152624d4fd3c78b91"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c9c3641ffcde63be07e9ae6a9f91ef10ee3df226"},"cell_type":"code","source":"#以下、実処理","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f052ad968092da441a65b07fb5b43838e4656e76"},"cell_type":"code","source":"num_rows = None#10000# if debug else None\ndf = train_test(num_rows)\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"49855c39b429a8664621e636b228ae476eed30e5"},"cell_type":"code","source":"hist = historical_transactions(num_rows)\nhist.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"75a41181f2050f232fe0b078a06cd3aef367ae90"},"cell_type":"code","source":"df = pd.merge(df, hist, on='card_id', how='outer')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c5eec5b2298d2f75257e425c678fb4d3b20707eb"},"cell_type":"code","source":"merch = new_merchant_transactions(num_rows)\nmerch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc66485b8e380edc3884d3f3fe78cb38cdabfbf0"},"cell_type":"code","source":"df = pd.merge(df, merch , on='card_id', how='outer')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f84ae1e77cade30880bb5407236f4b27af3d391"},"cell_type":"code","source":"df = additional_features(df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a05e13ad7f213505af463e8072be953071dae4cf"},"cell_type":"code","source":"train_df = df[df['target'].notnull()]\ntest_df = df[df['target'].isnull()]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc7619ff4c495a3dc1956e2210fdf380ebf5ee11"},"cell_type":"code","source":"del df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d1f0216960bbdb5081305619b7f7390a8d010e3"},"cell_type":"code","source":"debug = False\nbest_features = kfold_lightgbm(train_df, test_df, num_folds=11, stratified=False, debug=debug,FileName='submission_1')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9b9246668faadd85d42e32c43581bd32db559f53"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e48190d1d815104ba7d90269c0ab1a52071b7562"},"cell_type":"code","source":"#★★★重要度の低い特徴量を抽出\nbest_data = best_features.sort_values(by=\"importance\",ascending=False)\nremove_label = best_data.tail(500)\nremove_label.tail(10)\n\nremove_columns = [c for c in remove_label[\"feature\"] ]\nremove_columns = set(remove_columns)\nremove_columns = list(remove_columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8b3eb7d3aed27cbd13f1b5f4888bcb9379c02621"},"cell_type":"code","source":"#★★★重要度の低い特徴量を削除\ntrain_df_new = train_df.drop(remove_columns,axis=1)\ntest_df_new = test_df.drop(remove_columns,axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"02a8408f377eb7ed375c67fea4bbcf6fe3e82a4c"},"cell_type":"code","source":"#★★★改めて再検証\nkfold_lightgbm(train_df_new, test_df_new, num_folds=11, stratified=False, debug=debug,FileName='submission_2')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f74b8786137561ee6608afe6daebbcaf05a305d0"},"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}