text: 
  # coding: utf-8
  
  # # Elo world 
  # 
  # In this kernel, I build a LGBM model that aggregates the `new_merchant_transactions.csv` and `historical_transactions.csv` tables to the main train table. New features are built by successive grouping on`card_id` and `month_lag`, in order to recover some information from the time serie.
  # 
  # During the competition, I took into account the enlightments provided by others kernels, and included a few features that appeared to be important. In particular, I closely looked at the following kernels (ordered by release time):
  # 1.  [You're Going to Want More Categories [LB 3.737] by Peter Hurford](https://www.kaggle.com/peterhurford/you-re-going-to-want-more-categories-lb-3-737)
  # 2. [EloDA with Feature Engineering and Stacking by Bojan Tunguz](https://www.kaggle.com/tunguz/eloda-with-feature-engineering-and-stacking)
  # 3. [A Closer Look at Date Variables by Robin Denz](https://www.kaggle.com/denzo123/a-closer-look-at-date-variables)
  # 4. [LGB + FE (LB 3.707) by Konrad Banachewicz](https://www.kaggle.com/konradb/lgb-fe-lb-3-707)
  # 5. [My first kernel (3.699) by Chau Ngoc Huynh](https://www.kaggle.com/chauhuynh/my-first-kernel-3-699/)
  # 
  # ## Notebook  Content
  # 1. [Loading the data](#1)
  # 1. [Feature engineering](#2)
  # 1. [Training the model](#3)
  # 1. [Feature importance](#4)
  # 1. [Submission](#5)
  
  # In[ ]:
  
  import numpy as np 
  import pandas as pd 
  import matplotlib.pyplot as plt
  import seaborn as sns
  import lightgbm as lgb
  from sklearn.model_selection import KFold
  import warnings
  import time
  import sys
  import datetime
  import matplotlib.pyplot as plt
  import seaborn as sns
  from sklearn.metrics import mean_squared_error
  warnings.simplefilter(action='ignore', category=FutureWarning)
  pd.set_option('display.max_columns', 500)
  
  
  # <a id="1"></a> <br>
  # ## 1. Loading the data
  # 
  # First, we load the `new_merchant_transactions.csv` and `historical_transactions.csv`. In practice, these two files contain the same variables and the difference between the two tables only concern the position with respect to a reference date.  Also, booleans features are made numeric:
  
  # In[ ]:
  
  def reduce_mem_usage(df, verbose=True):
      numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']
      start_mem = df.memory_usage().sum() / 1024**2    
      for col in df.columns:
          col_type = df[col].dtypes
          if col_type in numerics:
              c_min = df[col].min()
              c_max = df[col].max()
              if str(col_type)[:3] == 'int':
                  if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:
                      df[col] = df[col].astype(np.int8)
                  elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:
                      df[col] = df[col].astype(np.int16)
                  elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:
                      df[col] = df[col].astype(np.int32)
                  elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:
                      df[col] = df[col].astype(np.int64)  
              else:
                  if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:
                      df[col] = df[col].astype(np.float16)
                  elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:
                      df[col] = df[col].astype(np.float32)
                  else:
                      df[col] = df[col].astype(np.float64)    
      end_mem = df.memory_usage().sum() / 1024**2
      if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))
      return df
  
  
  # In[ ]:
  
  new_transactions = pd.read_csv('../input/new_merchant_transactions.csv',
                                 parse_dates=['purchase_date'])
  
  historical_transactions = pd.read_csv('../input/historical_transactions.csv',
                                        parse_dates=['purchase_date'])
  
  def binarize(df):
      for col in ['authorized_flag', 'category_1']:
          df[col] = df[col].map({'Y':1, 'N':0})
      return df
  
  historical_transactions = binarize(historical_transactions)
  new_transactions = binarize(new_transactions)
  
  
  # We then load the main files, formatting the dates and extracting the target:
  
  # In[ ]:
  
  def read_data(input_file):
      df = pd.read_csv(input_file)
      df['first_active_month'] = pd.to_datetime(df['first_active_month'])
      df['elapsed_time'] = (datetime.date(2018, 2, 1) - df['first_active_month'].dt.date).dt.days
      return df
  #_________________________________________
  train = read_data('../input/train.csv')
  test = read_data('../input/test.csv')
  
  target = train['target']
  del train['target']
  
  
  # <a id="2"></a> <br>
  # ## Feature engineering
  # Following [Chau Ngoc Huynh's kernel](https://www.kaggle.com/chauhuynh/my-first-kernel-3-699/), I add the following features:
  
  # In[ ]:
  
  historical_transactions['month_diff'] = ((datetime.datetime.today() - historical_transactions['purchase_date']).dt.days)//30
  historical_transactions['month_diff'] += historical_transactions['month_lag']
  
  new_transactions['month_diff'] = ((datetime.datetime.today() - new_transactions['purchase_date']).dt.days)//30
  new_transactions['month_diff'] += new_transactions['month_lag']
  
  
  # In[ ]:
  
  historical_transactions[:5]
  
  
  # In[ ]:
  
  historical_transactions = pd.get_dummies(historical_transactions, columns=['category_2', 'category_3'])
  new_transactions = pd.get_dummies(new_transactions, columns=['category_2', 'category_3'])
  
  historical_transactions = reduce_mem_usage(historical_transactions)
  new_transactions = reduce_mem_usage(new_transactions)
  
  agg_fun = {'authorized_flag': ['mean']}
  auth_mean = historical_transactions.groupby(['card_id']).agg(agg_fun)
  auth_mean.columns = ['_'.join(col).strip() for col in auth_mean.columns.values]
  auth_mean.reset_index(inplace=True)
  
  authorized_transactions = historical_transactions[historical_transactions['authorized_flag'] == 1]
  historical_transactions = historical_transactions[historical_transactions['authorized_flag'] == 0]
  
  
  # First, following [Robin Denz](https://www.kaggle.com/denzo123/a-closer-look-at-date-variables) analysis, I define a few dates features:
  
  # In[ ]:
  
  historical_transactions['purchase_month'] = historical_transactions['purchase_date'].dt.month
  authorized_transactions['purchase_month'] = authorized_transactions['purchase_date'].dt.month
  new_transactions['purchase_month'] = new_transactions['purchase_date'].dt.month
  
  
  # Then I define two functions that aggregate the info contained in these two tables. The first function aggregates the function by grouping on `card_id`:
  
  # In[ ]:
  
  def aggregate_transactions(history):
      
      history.loc[:, 'purchase_date'] = pd.DatetimeIndex(history['purchase_date']).                                      astype(np.int64) * 1e-9
      
      agg_func = {
      'category_1': ['sum', 'mean'],
      'category_2_1.0': ['mean'],
      'category_2_2.0': ['mean'],
      'category_2_3.0': ['mean'],
      'category_2_4.0': ['mean'],
      'category_2_5.0': ['mean'],
      'category_3_A': ['mean'],
      'category_3_B': ['mean'],
      'category_3_C': ['mean'],
      'merchant_id': ['nunique'],
      'merchant_category_id': ['nunique'],
      'state_id': ['nunique'],
      'city_id': ['nunique'],
      'subsector_id': ['nunique'],
      'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],
      'installments': ['sum', 'mean', 'max', 'min', 'std'],
      'purchase_month': ['mean', 'max', 'min', 'std'],
      'purchase_date': [np.ptp, 'min', 'max'],
      'month_lag': ['mean', 'max', 'min', 'std'],
      'month_diff': ['mean']
      }
      
      agg_history = history.groupby(['card_id']).agg(agg_func)
      agg_history.columns = ['_'.join(col).strip() for col in agg_history.columns.values]
      agg_history.reset_index(inplace=True)
      
      df = (history.groupby('card_id')
            .size()
            .reset_index(name='transactions_count'))
      
      agg_history = pd.merge(df, agg_history, on='card_id', how='left')
      
      return agg_history
  
  
  # In[ ]:
  
  history = aggregate_transactions(historical_transactions)
  history.columns = ['hist_' + c if c != 'card_id' else c for c in history.columns]
  history[:5]
  
  
  # In[ ]:
  
  authorized = aggregate_transactions(authorized_transactions)
  authorized.columns = ['auth_' + c if c != 'card_id' else c for c in authorized.columns]
  authorized[:5]
  
  
  # In[ ]:
  
  new = aggregate_transactions(new_transactions)
  new.columns = ['new_' + c if c != 'card_id' else c for c in new.columns]
  new[:5]
  
  
  # The second function first aggregates on the two variables `card_id` and `month_lag`. Then a second grouping is performed to aggregate over time:
  
  # In[ ]:
  
  def aggregate_per_month(history):
      grouped = history.groupby(['card_id', 'month_lag'])
  
      agg_func = {
              'purchase_amount': ['count', 'sum', 'mean', 'min', 'max', 'std'],
              'installments': ['count', 'sum', 'mean', 'min', 'max', 'std'],
              }
  
      intermediate_group = grouped.agg(agg_func)
      intermediate_group.columns = ['_'.join(col).strip() for col in intermediate_group.columns.values]
      intermediate_group.reset_index(inplace=True)
  
      final_group = intermediate_group.groupby('card_id').agg(['mean', 'std'])
      final_group.columns = ['_'.join(col).strip() for col in final_group.columns.values]
      final_group.reset_index(inplace=True)
      
      return final_group
  #___________________________________________________________
  final_group =  aggregate_per_month(authorized_transactions) 
  final_group[:10]
  
  
  # In[ ]:
  
  def successive_aggregates(df, field1, field2):
      t = df.groupby(['card_id', field1])[field2].mean()
      u = pd.DataFrame(t).reset_index().groupby('card_id')[field2].agg(['mean', 'min', 'max', 'std'])
      u.columns = [field1 + '_' + field2 + '_' + col for col in u.columns.values]
      u.reset_index(inplace=True)
      return u
  
  
  # In[ ]:
  
  additional_fields = successive_aggregates(new_transactions, 'category_1', 'purchase_amount')
  additional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'installments', 'purchase_amount'),
                                              on = 'card_id', how='left')
  additional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'city_id', 'purchase_amount'),
                                              on = 'card_id', how='left')
  additional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'category_1', 'installments'),
                                              on = 'card_id', how='left')
  
  
  # <a id="3"></a> <br>
  # ## 3. Training the model
  # We now train the model with the features we previously defined. A first step consists in merging all the dataframes:
  
  # In[ ]:
  
  train = pd.merge(train, history, on='card_id', how='left')
  test = pd.merge(test, history, on='card_id', how='left')
  
  train = pd.merge(train, authorized, on='card_id', how='left')
  test = pd.merge(test, authorized, on='card_id', how='left')
  
  train = pd.merge(train, new, on='card_id', how='left')
  test = pd.merge(test, new, on='card_id', how='left')
  
  train = pd.merge(train, final_group, on='card_id', how='left')
  test = pd.merge(test, final_group, on='card_id', how='left')
  
  train = pd.merge(train, auth_mean, on='card_id', how='left')
  test = pd.merge(test, auth_mean, on='card_id', how='left')
  
  train = pd.merge(train, additional_fields, on='card_id', how='left')
  test = pd.merge(test, additional_fields, on='card_id', how='left')
  
  
  # In[ ]:
  
  # cols = [c for c in train if c.startswith('hist')]
  # train.loc[train['hist_transactions_count'].isnull(), cols] = 0
  # test.loc[test['hist_transactions_count'].isnull(), cols] = 0
  
  # cols = [c for c in train if c.startswith('new')]
  # train.loc[train['new_transactions_count'].isnull(), cols] = 0
  # test.loc[test['new_transactions_count'].isnull(), cols] = 0
  
  
  # In[ ]:
  
  # cols = [c for c in train if c.endswith('std')]
  # for c in cols:
  #     train.loc[train[c].isnull(), c] = 0
  #     test.loc[test[c].isnull(), c] = 0
  
  
  # In[ ]:
  
  # train['transactions_ratio'] = train['new_transactions_count'] / train['hist_transactions_count']
  # test['transactions_ratio'] = test['new_transactions_count'] / test['hist_transactions_count']
  
  
  # In[ ]:
  
  # hist_columns = [(c, c.replace('new', 'auth')) for c in train.columns if 'hist' in c]
  # for c in hist_columns:
  #     col_name = 'ratio_{}_{}'.format(c[0], c[1])
  #     train[col_name] = train[c[0]] / train[c[1]]
  #     test[col_name] = test[c[0]] / test[c[1]]
  
  
  # In[ ]:
  
  test.to_csv('test.csv')
  train['target'] = target
  train.to_csv('train.csv')
  del train['target']
  
  
  # and to define the features we want to keep to train the model. For that purpose, I use the results obtained in the [Selecting features kernel](https://www.kaggle.com/fabiendaniel/selecting-features/notebook):
  
  # In[ ]:
  
  # unimportant_features = [
  #     'auth_category_2_1.0_mean',
  #     'auth_category_2_2.0_mean',
  #     'auth_category_2_3.0_mean',
  #     'auth_category_2_5.0_mean',
  #     'hist_category_2_3.0_mean',
  #     'hist_category_2_4.0_mean',
  #     'hist_category_2_5.0_mean',
  #     'hist_category_3_A_mean',
  #     'hist_installments_min',
  #     'hist_installments_std',
  #     'hist_month_lag_std',
  #     'hist_purchase_amount_max',
  #     'hist_purchase_month_max',
  #     'hist_purchase_month_min',
  #     'hist_purchase_month_std',
  #     'installments_min_mean',
  #     'new_category_2_1.0_mean',
  #     'new_category_2_2.0_mean',
  #     'new_category_2_3.0_mean',
  #     'new_category_2_5.0_mean',
  #     'new_city_id_nunique',
  #     'new_installments_std',
  #     'new_state_id_nunique',
  #     'purchase_amount_mean_mean'
  # ]
  features = [c for c in train.columns if c not in ['card_id', 'first_active_month']]
  #features = [f for f in features if f not in unimportant_features]
  categorical_feats = ['feature_2', 'feature_3']
  
  
  # We then set the hyperparameters of the LGBM model, these parameters are obtained by an [bayesian optimization done in another kernel](https://www.kaggle.com/fabiendaniel/hyperparameter-tuning/edit):
  
  # In[ ]:
  
  param = {'num_leaves': 111,
           'min_data_in_leaf': 149, 
           'objective':'regression',
           'max_depth': 9,
           'learning_rate': 0.005,
           "boosting": "gbdt",
           "feature_fraction": 0.7522,
           "bagging_freq": 1,
           "bagging_fraction": 0.7083 ,
           "bagging_seed": 11,
           "metric": 'rmse',
           "lambda_l1": 0.2634,
           "random_state": 133,
           "verbosity": -1}
  
  
  # We now train the model. Here, we use a standard KFold split of the dataset in order to validate the results and to stop the training. Interstingly, during the writing of this kernel, the model was enriched adding new features, which improved the CV score. **The variations observed on the CV were found to be quite similar to the variations on the LB**: it seems that the current competition won't give us headaches to define the correct validation scheme:
  
  # In[ ]:
  
  folds = KFold(n_splits=5, shuffle=True, random_state=15)
  oof = np.zeros(len(train))
  predictions = np.zeros(len(test))
  start = time.time()
  feature_importance_df = pd.DataFrame()
  
  for fold_, (trn_idx, val_idx) in enumerate(folds.split(train.values, target.values)):
      print("fold n°{}".format(fold_))
      trn_data = lgb.Dataset(train.iloc[trn_idx][features],
                             label=target.iloc[trn_idx],
                             categorical_feature=categorical_feats
                            )
      val_data = lgb.Dataset(train.iloc[val_idx][features],
                             label=target.iloc[val_idx],
                             categorical_feature=categorical_feats
                            )
  
      num_round = 10000
      clf = lgb.train(param,
                      trn_data,
                      num_round,
                      valid_sets = [trn_data, val_data],
                      verbose_eval=100,
                      early_stopping_rounds = 200)
      
      oof[val_idx] = clf.predict(train.iloc[val_idx][features], num_iteration=clf.best_iteration)
      
      fold_importance_df = pd.DataFrame()
      fold_importance_df["feature"] = features
      fold_importance_df["importance"] = clf.feature_importance()
      fold_importance_df["fold"] = fold_ + 1
      feature_importance_df = pd.concat([feature_importance_df, fold_importance_df], axis=0)
      
      predictions += clf.predict(test[features], num_iteration=clf.best_iteration) / folds.n_splits
  
  print("CV score: {:<8.5f}".format(mean_squared_error(oof, target)**0.5))
  
  
  # <a id="4"></a> <br>
  # ## 4. Feature importance
  # Finally, we can have a look at the features that were used by the model:
  
  # In[ ]:
  
  cols = (feature_importance_df[["feature", "importance"]]
          .groupby("feature")
          .mean()
          .sort_values(by="importance", ascending=False)[:1000].index)
  
  best_features = feature_importance_df.loc[feature_importance_df.feature.isin(cols)]
  
  plt.figure(figsize=(14,25))
  sns.barplot(x="importance",
              y="feature",
              data=best_features.sort_values(by="importance",
                                             ascending=False))
  plt.title('LightGBM Features (avg over folds)')
  plt.tight_layout()
  plt.savefig('lgbm_importances.png')
  
  
  # <a id="5"></a> <br>
  # ## 5. Submission
  # Now, we just need to prepare the submission file:
  
  # In[ ]:
  
  sub_df = pd.DataFrame({"card_id":test["card_id"].values})
  sub_df["target"] = predictions
  sub_df.to_csv("submit.csv", index=False)
  
  
  # In[ ]:
  
  
  
  
  # In[ ]:
  
  
  
  
  # In[ ]:
  
  
  
  