{"cells":[{"metadata":{"_uuid":"908956b65b7871816988c588b27ac4ba8b85edfe"},"cell_type":"markdown","source":"### This heavily borrows from Chau Ngoc Huynh's  https://www.kaggle.com/chauhuynh/my-first-kernel-3-699. I try to create some features using *workalendar*, which was suggested on Bojan Tunguz's post (https://www.kaggle.com/c/elo-merchant-category-recommendation/discussion/74052) by Kjetil Åmdal-Sævik. \n\n### As a first cursory effort, I've created new features in the train and test sets based on the number of working days (in the Brazilian calendar) between the first_active_month and the 8 major national holidays.  "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport datetime\nfrom datetime import date, datetime\nimport gc\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nfrom sklearn.model_selection import StratifiedKFold\nfrom sklearn.metrics import mean_squared_error\nimport warnings\n\nimport workalendar\nfrom workalendar.america import Brazil\n\nwarnings.filterwarnings('ignore')\nnp.random.seed(4590)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"df_train = pd.read_csv('../input/train.csv')\ndf_test = pd.read_csv('../input/test.csv')\ndf_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":"520b71064293a20e0f2a379dad0acc274374a3c4"},"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":"44198b73053869f8dfc083204c592fe9193f239c"},"cell_type":"code","source":"%%time\ndf_train = reduce_mem_usage(df_train)\ndf_test = reduce_mem_usage(df_test)\ndf_hist_trans = reduce_mem_usage(df_hist_trans)\ndf_new_merchant_trans = reduce_mem_usage(df_new_merchant_trans)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"71f89a3b8a93b2f2feb2cd0a45f860cde33687be"},"cell_type":"code","source":"%%time\nfor df in [df_hist_trans,df_new_merchant_trans]:\n    df['category_2'].fillna(1.0,inplace=True)\n    df['category_3'].fillna('A',inplace=True)\n    df['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dda90662d05e22310dd713df106ea07f4b8bccfc"},"cell_type":"code","source":"%%time\ndef get_new_columns(name,aggs):\n    return [name + '_' + k + '_' + agg for k in aggs.keys() for agg in aggs[k]]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5a8a999a7af28d3746de650cd9f48b4b24037d73"},"cell_type":"code","source":"%%time\ncal = Brazil()\nfor yr in [2011,2012,2013,2014,2015,2016,2017]:\n    print(yr,cal.holidays(yr))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eff94dfde04e78f7e6054194896f57c117bcbfe9"},"cell_type":"code","source":"%%time\ncal.holidays(2013)[1]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b72764c2f70d05fa46a858a1215424b9d638b3de"},"cell_type":"markdown","source":"### As a first effort, for every year, we want to calculate the number of working days between the purchase date and the 8 major holidays -- \n* New years day -- (year,1,1) \n* Tiradentes day -- (year,4,21)\n* Labour day-- (year,5,1)\n* Independence day -- (year,9,7)\n* Our lady of aparecida day -- (year,10,12)\n* All souls day -- (year,11,2)\n* Republic day -- (year,11,15)\n* Christmas day (year,12,25)"},{"metadata":{"trusted":true,"_uuid":"7c91d3b9e9dbaff01962b0facbace75705a9ce18"},"cell_type":"code","source":"%%time\nfor df in [df_hist_trans,df_new_merchant_trans]:\n    df['purchase_date'] = pd.to_datetime(df['purchase_date'])\n#     df['date'] = df['purchase_date'].dt.date\n    df['year'] = df['purchase_date'].dt.year\n    df['weekofyear'] = df['purchase_date'].dt.weekofyear\n    df['month'] = df['purchase_date'].dt.month\n    df['dayofweek'] = df['purchase_date'].dt.dayofweek\n    df['weekend'] = (df.purchase_date.dt.weekday >=5).astype(int)\n    df['hour'] = df['purchase_date'].dt.hour\n    df['authorized_flag'] = df['authorized_flag'].map({'Y':1, 'N':0})\n    df['category_1'] = df['category_1'].map({'Y':1, 'N':0}) \n    df['month_diff'] = ((datetime.today() - df['purchase_date']).dt.days)//30\n    df['month_diff'] += df['month_lag']\n    # These are the 8 added features, calculating the no of working days between the date of purchase and each of the 8 standard Brailian holidays\n\n#     df['day_diff1'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[0][0])) # have to make this less clunky, write a function\n#     df['day_diff2'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[1][0]))\n#     df['day_diff3'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[2][0]))\n#     df['day_diff4'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[3][0]))\n#     df['day_diff5'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[4][0]))\n#     df['day_diff6'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[5][0]))\n#     df['day_diff7'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[6][0]))\n#     df['day_diff8'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[7][0]))\n\n#     df['purchase_date'] = pd.to_datetime(df['purchase_date'])\n#     df['date'] = df['purchase_date'].dt.date\n#     df['year'] = df['purchase_date'].dt.year\n#     df['weekofyear'] = df['purchase_date'].dt.weekofyear\n#     df['month'] = df['purchase_date'].dt.month\n#     df['dayofweek'] = df['purchase_date'].dt.dayofweek\n#     df['weekend'] = (df.purchase_date.dt.weekday >=5).astype(int)\n#     df['hour'] = df['purchase_date'].dt.hour\n#     df['authorized_flag'] = df['authorized_flag'].map({'Y':1, 'N':0})\n#     df['category_1'] = df['category_1'].map({'Y':1, 'N':0}) \n#     df['month_diff'] = ((datetime.today() - df['purchase_date']).dt.days)//30\n#     df['month_diff'] += df['month_lag']\n#     df['day_diff1'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(x.year)[0][0]))\n#                                        #cal.get_working_days_delta(df['date'],cal.holidays(2018)[0][0]) #df['date'] - cal.holidays(2018)[0][0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ddf1d5bb0ade2b22b0f072c208c1506ea64503ea"},"cell_type":"code","source":"aggs = {}\nfor col in ['month','hour','weekofyear','dayofweek','year','subsector_id','merchant_id','merchant_category_id']:\n    aggs[col] = ['nunique']\n\naggs['purchase_amount'] = ['sum','max','min','mean','var']\naggs['installments'] = ['sum','max','min','mean','var']\naggs['purchase_date'] = ['max','min']\naggs['month_lag'] = ['max','min','mean','var']\naggs['month_diff'] = ['mean']\naggs['authorized_flag'] = ['sum', 'mean']\naggs['weekend'] = ['sum', 'mean']\naggs['category_1'] = ['sum', 'mean']\naggs['card_id'] = ['size']\n\nfor col in ['category_2','category_3']:\n    df_hist_trans[col+'_mean'] = df_hist_trans.groupby([col])['purchase_amount'].transform('mean')\n    aggs[col+'_mean'] = ['mean']    \n\nnew_columns = get_new_columns('hist',aggs)\ndf_hist_trans_group = df_hist_trans.groupby('card_id').agg(aggs)\ndf_hist_trans_group.columns = new_columns\ndf_hist_trans_group.reset_index(drop=False,inplace=True)\ndf_hist_trans_group['hist_purchase_date_diff'] = (df_hist_trans_group['hist_purchase_date_max'] - df_hist_trans_group['hist_purchase_date_min']).dt.days\ndf_hist_trans_group['hist_purchase_date_average'] = df_hist_trans_group['hist_purchase_date_diff']/df_hist_trans_group['hist_card_id_size']\ndf_hist_trans_group['hist_purchase_date_uptonow'] = (datetime.today() - df_hist_trans_group['hist_purchase_date_max']).dt.days\ndf_train = df_train.merge(df_hist_trans_group,on='card_id',how='left')\ndf_test = df_test.merge(df_hist_trans_group,on='card_id',how='left')\n\ndel df_hist_trans_group;\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f7f5625db40db4395374991124fb796c9decd60b"},"cell_type":"code","source":"%%time\naggs = {}\nfor col in ['month','hour','weekofyear','dayofweek','year','subsector_id','merchant_id','merchant_category_id']:\n    aggs[col] = ['nunique']\naggs['purchase_amount'] = ['sum','max','min','mean','var']\naggs['installments'] = ['sum','max','min','mean','var']\naggs['purchase_date'] = ['max','min']\naggs['month_lag'] = ['max','min','mean','var']\naggs['month_diff'] = ['mean']\naggs['weekend'] = ['sum', 'mean']\naggs['category_1'] = ['sum', 'mean']\naggs['card_id'] = ['size']\n\nfor col in ['category_2','category_3']:\n    df_new_merchant_trans[col+'_mean'] = df_new_merchant_trans.groupby([col])['purchase_amount'].transform('mean')\n    aggs[col+'_mean'] = ['mean']\n    \nnew_columns = get_new_columns('new_hist',aggs)\ndf_hist_trans_group = df_new_merchant_trans.groupby('card_id').agg(aggs)\ndf_hist_trans_group.columns = new_columns\ndf_hist_trans_group.reset_index(drop=False,inplace=True)\ndf_hist_trans_group['new_hist_purchase_date_diff'] = (df_hist_trans_group['new_hist_purchase_date_max'] - df_hist_trans_group['new_hist_purchase_date_min']).dt.days\ndf_hist_trans_group['new_hist_purchase_date_average'] = df_hist_trans_group['new_hist_purchase_date_diff']/df_hist_trans_group['new_hist_card_id_size']\ndf_hist_trans_group['new_hist_purchase_date_uptonow'] = (datetime.today() - df_hist_trans_group['new_hist_purchase_date_max']).dt.days\ndf_train = df_train.merge(df_hist_trans_group,on='card_id',how='left')\ndf_test = df_test.merge(df_hist_trans_group,on='card_id',how='left')\n\ndel df_hist_trans_group;\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a075cc90ab1322829e4fad3ff39fce307c5db93c"},"cell_type":"code","source":"\ndel df_hist_trans;\ngc.collect()\n\ndel df_new_merchant_trans;\ngc.collect()\n\ndf_train.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6f3182aeac0c3bf7a061a1b9e25e859f25fee9b5"},"cell_type":"code","source":"df_train['outliers'] = 0\ndf_train.loc[df_train['target'] < -30, 'outliers'] = 1\ndf_train['outliers'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e9f61512c195c66a75dfe220072c5b2d860b78a3"},"cell_type":"code","source":"# Dealing with the one nan in df_test.first_active_month a bit arbitrarily for now\ndf_test.loc[df_test['first_active_month'].isna(),'first_active_month'] = df_test.iloc[11577]['first_active_month']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ce2082fc1fb0e3f8f7d27fc166aa7a8351b65504"},"cell_type":"code","source":"for df in [df_train,df_test]:\n    df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n    df['dayofweek'] = df['first_active_month'].dt.dayofweek\n    df['weekofyear'] = df['first_active_month'].dt.weekofyear\n    df['month'] = df['first_active_month'].dt.month\n    df['elapsed_time'] = (datetime.today() - df['first_active_month']).dt.days\n    df['hist_first_buy'] = (df['hist_purchase_date_min'] - df['first_active_month']).dt.days\n    df['new_hist_first_buy'] = (df['new_hist_purchase_date_min'] - df['first_active_month']).dt.days\n    for f in ['hist_purchase_date_max','hist_purchase_date_min','new_hist_purchase_date_max',\\\n                     'new_hist_purchase_date_min']:\n        df[f] = df[f].astype(np.int64) * 1e-9\n    df['card_id_total'] = df['new_hist_card_id_size']+df['hist_card_id_size']\n    df['purchase_amount_total'] = df['new_hist_purchase_amount_sum']+df['hist_purchase_amount_sum']\n    \n    df['date'] = df['first_active_month'].dt.date\n    \n     # These are the 8 added features, calculating the no of working days between the first active month and each of the 8 standard Brailian holidays\n        \n    df['day_diff1'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[0][0])) # have to make this less clunky, write a function\n    df['day_diff2'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[1][0]))\n    df['day_diff3'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[2][0]))\n    df['day_diff4'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[3][0]))\n    df['day_diff5'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[4][0]))\n    df['day_diff6'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[5][0]))\n    df['day_diff7'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[6][0]))\n    df['day_diff8'] = df['date'].apply(lambda x: cal.get_working_days_delta(x,cal.holidays(int(x.year))[7][0]))\n    \n    df.drop(['date'],axis=1,inplace=True)\n    \nfor f in ['feature_1','feature_2','feature_3']:\n    order_label = df_train.groupby([f])['outliers'].mean()\n    df_train[f] = df_train[f].map(order_label)\n    df_test[f] = df_test[f].map(order_label)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a93c5976d3b395ba8ff0d1002b8075be3e914c54"},"cell_type":"code","source":"df_train = reduce_mem_usage(df_train)\ndf_test = reduce_mem_usage(df_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c4f20f27679889542acfd60d1f1ac381b201ac43"},"cell_type":"code","source":"df_train_columns = [c for c in df_train.columns if c not in ['card_id', 'first_active_month','target','outliers']]\ntarget = df_train['target']\ndel df_train['target']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d2e0c522184dcaae9b19f89d516847ac59e1f552"},"cell_type":"code","source":"from sklearn import grid_search\n# Create parameters to search\ngridParams = {\n    'learning_rate': [0.005],\n    'n_estimators': [40],\n    'num_leaves': [6,8,12,16],\n    'boosting_type' : ['gbdt'],\n    'objective' : ['binary'],\n    'random_state' : [501], # Updated from 'seed'\n    'colsample_bytree' : [0.65, 0.66],\n    'subsample' : [0.7,0.75],\n    'reg_alpha' : [1,1.2],\n    'reg_lambda' : [1,1.2,1.4],\n    }\n\n# Create classifier to use. Note that parameters have to be input manually\n# not as a dict!\nmdl = lgb.LGBMClassifier(boosting_type= 'gbdt',\n          objective = 'binary',\n          n_jobs = 3, # Updated from 'nthread'\n          silent = True,\n          max_depth = params['max_depth'],\n          max_bin = params['max_bin'],\n          subsample_for_bin = params['subsample_for_bin'],\n          subsample = params['subsample'],\n          subsample_freq = params['subsample_freq'],\n          min_split_gain = params['min_split_gain'],\n          min_child_weight = params['min_child_weight'],\n          min_child_samples = params['min_child_samples'],\n          scale_pos_weight = params['scale_pos_weight'])\n\n# Create the grid\ngrid = GridSearchCV(, gridParams,\n                    verbose=0,\n                    cv=4,\n                    n_jobs=2)\n# Run the grid\ngrid.fit(allTrainData, allTrainLabels)\n\n# Print the best parameters found\nprint(grid.best_params_)\nprint(grid.best_score_)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c9bbc95244978b519d94131907b547c2b6c94191"},"cell_type":"code","source":"param = {'num_leaves': 31,\n         'min_data_in_leaf': 30, \n         'objective':'regression',\n         'max_depth': -1,\n         'learning_rate': 0.01,\n         \"min_child_samples\": 20,\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         \"nthread\": 4,\n         \"random_state\": 4590}\nfolds = StratifiedKFold(n_splits=5, shuffle=True, random_state=4590)\noof = np.zeros(len(df_train))\npredictions = np.zeros(len(df_test))\nfeature_importance_df = pd.DataFrame()\n\nfor fold_, (trn_idx, val_idx) in enumerate(folds.split(df_train,df_train['outliers'].values)):\n    print(\"fold {}\".format(fold_))\n    trn_data = lgb.Dataset(df_train.iloc[trn_idx][df_train_columns], label=target.iloc[trn_idx])#, categorical_feature=categorical_feats)\n    val_data = lgb.Dataset(df_train.iloc[val_idx][df_train_columns], label=target.iloc[val_idx])#, categorical_feature=categorical_feats)\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][df_train_columns], num_iteration=clf.best_iteration)\n    \n    fold_importance_df = pd.DataFrame()\n    fold_importance_df[\"Feature\"] = df_train_columns\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(df_test[df_train_columns], num_iteration=clf.best_iteration) / folds.n_splits\n\nnp.sqrt(mean_squared_error(oof, target))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"40b64481054fa71e692829c7039eccceb31b77fe"},"cell_type":"code","source":"cols = (feature_importance_df[[\"Feature\", \"importance\"]]\n        .groupby(\"Feature\")\n        .mean()\n        .sort_values(by=\"importance\", ascending=False)[:500].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()\n# plt.savefig('lgbm_importances.png')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5aa9a8cbe586424712f418eb34182910ed82bcb5"},"cell_type":"markdown","source":"### So most of the newly created features don't rank particularly high as far as feature importances go, but I will continue to work on this... "},{"metadata":{"trusted":true,"_uuid":"355e9c24949b8e5d677fe5a2f117228c3310dab6"},"cell_type":"code","source":"sub_df = pd.DataFrame({\"card_id\":df_test[\"card_id\"].values})\nsub_df[\"target\"] = predictions\nsub_df.to_csv(\"submission.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"58c9a5445698e42dfbd9548695290487a2ce171a"},"cell_type":"markdown","source":"### Haven't had time to think deeply about how *workalendar*  might be used but there is definitely potential, and I hope this starts a discussion"},{"metadata":{"trusted":true,"_uuid":"e4a55780edb95483bfac0d8119ea4d344a8461ff"},"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}