{"cells":[{"metadata":{"_uuid":"a34014cde76c3337e25f72d7a6b464212a8c4a06"},"cell_type":"markdown","source":"### Import all libraries"},{"metadata":{"trusted":true,"_uuid":"ecf9330eb0d5f36b04f1c3794e3b481f457f8eff"},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\n# import matplotlib.pyplot as plt\n# import seaborn as sns\nimport datetime as dt\nimport gc\n# from dateutil.relativedelta import relativedelta\nimport time\n# from tqdm import tqdm\n# from scipy import stats\nfrom statsmodels.stats import proportion\nfrom sklearn.preprocessing import MinMaxScaler, StandardScaler\n# from bayes_opt import BayesianOptimization\nimport lightgbm as lgb\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import f1_score, accuracy_score, mean_absolute_error, mean_squared_error\n\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5f812b4aaa8aa589a60e6cd7ed11fd3a8a5b07e5"},"cell_type":"markdown","source":"### Defining some helper functions"},{"metadata":{"trusted":false,"_uuid":"f2babe45c34ed5bb7850b1581635a34616f83ae5"},"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":false,"_uuid":"a608e03693383a62647a2b027f1a3c735de0428c"},"cell_type":"code","source":"# def percentile99(x):\n# return x[x<=np.percentile(x, 99)]\ndef get_mode(x):\n try:\n return int(x.mode())\n except:# in cases, when threre are only two values - just pick the second one\n return x.values[0]\n\ndef groupby(df, groupby_clmns, agg_clmns, agg_func, rel_calc=False, multigroup=False):\n if not agg_clmns:\n groupby = df.groupby(groupby_clmns).agg(agg_func).reset_index()\n else:\n groupby = df.groupby(groupby_clmns)[agg_clmns].agg(agg_func).reset_index()\n \n if multigroup:\n groupby1 = groupby.groupby(groupby_clmns[0])[list(list(zip(*agg_func))[0])].agg(sum).reset_index()\n groupby1.rename(columns={key[0]: key[0]+'sum' for key in agg_func}, inplace=True)\n groupby = merge_two_df(groupby, groupby1, groupby_clmns[0], groupby_clmns[0])\n# print(groupby.head())\n for agg_func_clmn in agg_func:\n groupby[agg_func_clmn[0]] = groupby[agg_func_clmn[0]]/groupby[agg_func_clmn[0]+'sum']\n# print(groupby.head())\n groupby = groupby[list(groupby_clmns) + list(list(zip(*agg_func))[0])]\n \n if rel_calc:\n for agg_func_clmn in agg_func:\n groupby[agg_func_clmn[0]] = groupby[agg_func_clmn[0]]/groupby[agg_func_clmn[0]].sum()\n \n return groupby\n\n\n# def auth_mean_calculation(df_hist_trans):\n# def bern_conf_interv(vls, true_p=0.91354, mth='agresti_coull'):#possible methods = ['normal', 'agresti_coull', 'beta', 'wilson', 'jeffreys', 'binom_test']\n# positive = np.sum(vls)\n# n = len(vls)\n# if n<10:\n# interval = proportion.proportion_confint(positive, n, method=mth)\n# return interval[0] + true_p * (interval[1] - interval[0])\n# else:\n# return np.mean(vls)\n\n# gr = df_hist_trans.groupby('card_id')['authorized_flag'].agg(bern_conf_interv).reset_index()\n# return gr\n \n\ndef merge_two_df(df1, df2, left_on, right_on):\n return pd.merge(df1, df2, how='left', left_on=left_on, right_on=right_on)\n\n# def save_df(filename='../data/new_hist.csv', drop_clmns=['date_min_dif', 'purchase_date_month_label']):\n# # df_hist_trans.drop(['date_min_dif', 'purchase_date_month_label'], axis=1)\n# with open(filename, 'w') as f:\n# if not drop_clmns:\n# df_hist_trans.to_csv(f, index=False, header=True)\n# else:\n# df_hist_trans.drop(drop_clmns, axis=1).to_csv(f, index=False, header=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"edf90fd66aab20bdb54484a9585f6f451d946d07"},"cell_type":"code","source":"# for me, the loading takes about 50 sec\ndf_hist_trans = pd.read_csv(\"../data/historical_transactions.csv\")\n\n# make a little preprocessing\ndf_hist_trans['authorized_flag'] = df_hist_trans['authorized_flag'].map({'Y': 1, 'N': 0})\ndf_hist_trans['purchase_date'] = pd.to_datetime(df_hist_trans['purchase_date'])\ndf_hist_trans['category_1'] = df_hist_trans['category_1'].map({'Y': 1, 'N': 0})\ndf_hist_trans['category_2'].fillna(-1, inplace=True)\ndf_hist_trans['category_3'].fillna('Z', inplace=True)\ndf_hist_trans['category_3'] = df_hist_trans['category_3'].map({'A': 0, 'B': 1, 'C': 2, 'Z': -1})\ndf_hist_trans['date_min_dif'] = (df_hist_trans['purchase_date'] - df_hist_trans['purchase_date'].min()).dt.days\n\n# Here, I delete the first five symbols from card_id and merchant_id, because they are the same and in order to save a little bit memory.\n# Do it for all *.csv files - train, test, merchants, etc. And before making a submit I just add that five symbols to card_id submit file.\n\ndf_hist_trans['card_id'] = df_hist_trans['card_id'].str[5:]\ndf_hist_trans['merchant_id'] = df_hist_trans['merchant_id'].str[5:]\n\ndf_new_merchant_hist_trans = reduce_mem_usage(df_hist_trans)\n\ngc.collect();\n# df_hist_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"a8163151023de9403fd47f8541105b57c275923e"},"cell_type":"code","source":"# If we check the data after some preprocessing steps, we can notice that there are 138481 NaN merchant_id. \n# Also, there are some NaN merchant_id in new_merchant_transactions.csv\n# In such a way we can do one of the following things:\n# - delete these rows. But that would cause to the loss of information.\n# - replace with some new specific value - '000000000' for example. But in that case, we would not be able to use information from merchants.csv\n# - replace with the most familier merhcant_id. For me, that case looks like a little bit more apropriate than the previous one.\n\ndf_hist_trans.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"51d4efa480010bf74e5154913e2a275d06d8d18a"},"cell_type":"markdown","source":"### We can impute NaN merchant_id with the most familier merhcant_id using 1-Nearest Neighbor model. "},{"metadata":{"trusted":false,"_uuid":"11df3d3a6bd2b7699f601850aa9d32096e822731"},"cell_type":"code","source":"from sklearn.neighbors import NearestNeighbors\nfrom sklearn.preprocessing import StandardScaler\n\ndef merchant_id_impute(df):\n agg_func = {\n 'purchase_amount': np.median,\n 'category_3': lambda x: get_mode(x),\n 'city_id': lambda x: get_mode(x),\n 'state_id': lambda x: get_mode(x),\n 'subsector_id': lambda x: get_mode(x), \n 'merchant_category_id': lambda x: get_mode(x),\n 'date_min_dif': np.mean,\n }\n temp = df[~df['merchant_id'].isnull()].groupby(['merchant_id']).agg(agg_func).reset_index()\n \n scaler = StandardScaler()\n \n #build and fit 1-NearestNeighbors model\n clmns = ['subsector_id', 'merchant_category_id',\n 'purchase_amount', 'date_min_dif', \n 'category_3', 'city_id', 'state_id']\n knnImputer = NearestNeighbors(n_neighbors=1)\n knnImputer.fit(scaler.fit_transform(temp[clmns].values))\n\n # predict for NaN merchants\n # knnImputer.kneighbors returns distance to the nearest neighbour and index of the nearest neighbour. We need only index\n index = knnImputer.kneighbors(scaler.transform(df[df['merchant_id'].isnull()][clmns].values))[1]\n \n #impute NaN merchants with predicted nearest neighbours\n df.loc[df['merchant_id'].isnull(), 'merchant_id'] = temp.iloc[index.reshape(-1)]['merchant_id'].values\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"4b8f98d713b0900f4c0a89a04e5c8284f1ce4696"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"c06af79e64bfe5c8c8e8c3a8bcf803c49678348e"},"cell_type":"code","source":"merchant_id_impute(df_hist_trans)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"e0d7a31ce1ab659d8e9c5100a7ad4e10765aa5df"},"cell_type":"code","source":"# right after merhcnat_id NaN imputation we can notice that there are not NaN values at all\ndf_hist_trans.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5bdd24bca53dad0718e67177e921ae86c0b14d01"},"cell_type":"markdown","source":"### Let's do the same things for new_merchant_transactions.csv"},{"metadata":{"trusted":false,"_uuid":"75646fd11c9dd63fee446aad6b46a9b374e4cf97"},"cell_type":"code","source":"df_new_merchant_hist_trans = pd.read_csv(\"../data/new_merchant_transactions.csv\")\n# In new_merchant_transactions.csv all transactions are authorized.\n\n# Some default prerocessing\ndf_new_merchant_hist_trans['purchase_date'] = pd.to_datetime(df_new_merchant_hist_trans['purchase_date'])\ndf_new_merchant_hist_trans['category_1'] = df_new_merchant_hist_trans['category_1'].map({'Y': 1, 'N': 0})\ndf_new_merchant_hist_trans['category_2'].fillna(-1, inplace=True)\ndf_new_merchant_hist_trans['category_3'].fillna('Z', inplace=True)\ndf_new_merchant_hist_trans['category_3'] = df_new_merchant_hist_trans['category_3'].map({'A': 0, 'B': 1, 'C': 2, 'Z': -1})\ndf_new_merchant_hist_trans['date_min_dif'] = (df_new_merchant_hist_trans['purchase_date'] - df_new_merchant_hist_trans['purchase_date'].min()).dt.days\n\ndf_new_merchant_hist_trans['card_id'] = df_new_merchant_hist_trans['card_id'].str[5:]\ndf_new_merchant_hist_trans['merchant_id'] = df_new_merchant_hist_trans['merchant_id'].str[5:]\n\n# i think that we can even drop authrized column from that dataframe, because the values is constant\ndf_new_merchant_hist_trans.drop('authorized_flag', axis=1, inplace=True)\n\n# impute NaN merchant_id\nmerchant_id_impute(df_new_merchant_hist_trans)\n\ndf_new_merchant_hist_trans = reduce_mem_usage(df_new_merchant_hist_trans)\ngc.collect();","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a05afbe5cbd450742e929eff6f4c824c96855272"},"cell_type":"markdown","source":"## Loading train and test"},{"metadata":{"trusted":false,"_uuid":"9ed4cbbc2f5b9657c72ae9a2cccc6a6a90521d19"},"cell_type":"code","source":"def fix_first_active_date(df1, df2):\n # Since some card_id 'first_active_date' featues are later than coresponding for that card_id purchase_date,\n # we can replace that first_active_dates with the coresponding for that card_id min purchase_date.\n # In such a way we fix that kinda \"noise\" or data mismatches\n ddd = merge_two_df(df1.groupby('card_id')['purchase_date'].min().reset_index(), df2[['card_id', 'first_active_date']], ['card_id'], ['card_id'])\n ddd.dropna(inplace=True)\n ddd['fault'] = ddd['purchase_date']150\ntrain[train['outlier']==1]['first_active_date_elapsed_day'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"cb4c0e01d2a00642845f227f864f1c56977ea511"},"cell_type":"code","source":"print(f\"quantity of non_outliers in train based on train['first_active_date_elapsed_day']<=150 condition: {train[train['first_active_date_elapsed_day']<=150].shape[0]},\\nquantity of non_outliers in test based on test['first_active_date_elapsed_day']<=150 condition: {test[test['first_active_date_elapsed_day']<=150].shape[0]}\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"c705476df3456e81bc831e63a94062d4e2f4a797"},"cell_type":"code","source":"df_hist_trans = merge_two_df(df_hist_trans, train[['card_id', 'outlier']], ['card_id'], ['card_id'])\n\n_ = df_hist_trans[df_hist_trans['card_id'].isin(train['card_id'])].groupby(['card_id', 'merchant_id'])['outlier'].max().reset_index()\n_ = merge_two_df(_, _.groupby('merchant_id')['outlier'].mean().reset_index().rename(columns={'outlier': 'outlier_merch_mean'}), ['merchant_id'], ['merchant_id'])\ntrain = merge_two_df(train, _.groupby('card_id')['outlier_merch_mean'].mean().reset_index(), ['card_id'], ['card_id'])\ndel _\ngc.collect()\ntrain.corr()['outlier']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"31d2bb5ab5dce146ce6fd6b4690df841a7aeaf63"},"cell_type":"markdown","source":"### Here, we can look that 'outlier_merch_mean' feature has pretty good corelation with outlier. You can also believe me on the word, or check yourself that different time-based features have relatively good corelation (about 0.05-0.10 on module (abs)), comparing to any other features that i found (these features have max 0.03 corelation value on module (abs)). \n### Using features from new_merchants_transactions.csv doesn't bring any perceptible benefit to outlier prediction, but you can always use your own features. \n### This is the main notes about outliers i have found during EDA."},{"metadata":{"_uuid":"98fdefc04ffdd232434b1fa4e049c7b3e895105f"},"cell_type":"markdown","source":"# Now let's do some feature engineering"},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"97764451d5dc0fd773c4d9468b0d0d033054f3c5"},"cell_type":"code","source":"# purchase_amount_rel - purchase_amount comparing with the other purchases in the same merchant_category.\n# That feature is scaled in range of (0; 1];\n# On my personal opinion that feature helps to avoid some outliers in original purchase_amount.\n# That feature has very very little impact on the outlier prediction, but nonethelss i used it and probably that feature can bring a little LB improvement in predicting the target with regression\n# Calculation of that feature takes about 2100 seconds\n# The higher 'subshape' values - the more precise and accurate 'purchase_amount_rel' values\n# If you get Memory Error - decrease 'subshape' value. That would also speed up calculation and cause to rougher values. \n# I have 16Gb RAM memory and subshape = 10000 for me is optimal - relativly fast and precise.\ndf_hist_trans['purchase_amount_rel'] = 0\ndf_hist_trans['merchant_category_id_count'] = 0\nsubshape = 10000\ntm = time.time()\n\nfor glbl_cnt, merchant_category_id in enumerate(df_hist_trans['merchant_category_id'].unique()):#326 categories\n indx = df_hist_trans[df_hist_trans['merchant_category_id']==merchant_category_id].index.values\n vls = df_hist_trans.loc[indx, 'purchase_amount'].values\n shape = indx.shape[0]\n if shape>subshape:\n subvls = vls[np.random.choice(len(vls), size=subshape, replace=False)]\n df_hist_trans.loc[indx, 'merchant_category_id_count'] = shape\n print('counter:', glbl_cnt, 'merchant_category_id:', merchant_category_id, 'shape of merch_category:', shape, 'number of chunks:', max(shape//subshape, 1))\n cnt = 0\n for lcl_vls in np.array_split(vls, max(shape//subshape, 1)):\n if shape>subshape:\n df_hist_trans.loc[indx[cnt : cnt + lcl_vls.shape[0]], 'purchase_amount_rel'] = (lcl_vls.reshape(-1, 1)>=subvls).sum(axis=1)/subshape\n else:\n df_hist_trans.loc[indx[cnt : cnt + lcl_vls.shape[0]], 'purchase_amount_rel'] = (lcl_vls.reshape(-1, 1)>=vls).sum(axis=1)/shape\n cnt += lcl_vls.shape[0]\n \nprint(time.time()-tm)\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"67dc69809456abaa963b1a0ac67090aff4571183"},"cell_type":"code","source":"def extract_date_features(df_hist_trans, train_df, test_df):\n tm = time.time()\n agg_func = [\n ('merchant_category_id_last_visited_ref_date_day_diff', lambda x: (dt.datetime(2018, 3, 1)-x.max()).days),\n ]\n# df = groupby(df_hist_trans, ['merchant_category_id', 'card_id'], 'purchase_date', agg_func)\n df = df_hist_trans.groupby(['merchant_category_id', 'card_id'])['purchase_date'].agg(agg_func).reset_index()\n print('merchant_category_id_last_visited_ref_date_day_diff passed', time.time()-tm)\n \n df = merge_two_df(df, train_df[['card_id', 'first_active_date_elapsed_day']], ['card_id'], ['card_id'])\n df = merge_two_df(df, test_df[['card_id', 'first_active_date_elapsed_day']], ['card_id'], ['card_id'])\n df.loc[df[(df['first_active_date_elapsed_day_x'].isnull()) & (~df['first_active_date_elapsed_day_y'].isnull())].index, 'first_active_date_elapsed_day_x'] = \\\n df.loc[df[(df['first_active_date_elapsed_day_x'].isnull()) & (~df['first_active_date_elapsed_day_y'].isnull())].index, 'first_active_date_elapsed_day_y']\n df.drop('first_active_date_elapsed_day_y', axis=1, inplace=True)\n df.rename(columns={'first_active_date_elapsed_day_x': 'first_active_date_elapsed_day'}, inplace=True)\n agg_func = [\n ('last_visited_ref_date_day_diff', lambda x: (dt.datetime(2018, 3, 1)-x.max()).days)\n ]\n# last_visited_ref_date_day_diff = groupby(df_hist_trans, ['card_id'], 'purchase_date', agg_func)\n last_visited_ref_date_day_diff = df_hist_trans.groupby(['card_id'])['purchase_date'].agg(agg_func).reset_index()\n print('last_visited_ref_date_day_diff passed', time.time()-tm)\n df = merge_two_df(df, last_visited_ref_date_day_diff, ['card_id'], ['card_id'])\n \n df['last_vis_merch_last_vis_day_diff'] = df['merchant_category_id_last_visited_ref_date_day_diff'] - df['last_visited_ref_date_day_diff']\n return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"55e5e75595784749904403d00377d00e4c72e453"},"cell_type":"code","source":"#that takes about 700 seconds for me\ntemp = extract_date_features(df_hist_trans, train, test)\ndf_hist_trans = merge_two_df(df_hist_trans, temp, ['merchant_category_id', 'card_id'], ['merchant_category_id', 'card_id'])\ndel temp\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7e34a446ef7df1a166577ed6a2b20d61f27dd6a3"},"cell_type":"code","source":"lastShape = df_hist_trans.shape\n# leave only 'first_active_date_elapsed_day'>150 card_id transactions\n# cond = (df_hist_trans['card_id'].isin(train[train['first_active_date_elapsed_day']>150]['card_id'])) | \\\n# (df_hist_trans['card_id'].isin(test[test['first_active_date_elapsed_day']>150]['card_id']))\n# df_hist_trans = df_hist_trans[cond].reset_index(drop=True)#.copy()\n# df_hist_trans['isTrain'] = (df_hist_trans['card_id'].isin(train[train['first_active_date_elapsed_day']>150]['card_id']))*1\ndf_hist_trans['isTrain'] = (df_hist_trans['card_id'].isin(train['card_id']))*1\n\n# add from train and test 'first_active_date_elapsed_day' features into our transaction dataframe\ndf_hist_trans = merge_two_df(df_hist_trans, train[['card_id', 'first_active_date_elapsed_day']], ['card_id'], ['card_id'])\ndf_hist_trans = merge_two_df(df_hist_trans, test[['card_id', 'first_active_date_elapsed_day']], ['card_id'], ['card_id'])\ndf_hist_trans.loc[df_hist_trans['first_active_date_elapsed_day_x'].isnull(), 'first_active_date_elapsed_day_x'] = df_hist_trans.loc[df_hist_trans['first_active_date_elapsed_day_x'].isnull(), 'first_active_date_elapsed_day_y'].values\ndf_hist_trans.drop('first_active_date_elapsed_day_y', axis=1, inplace=True)\ndf_hist_trans.rename(columns={'first_active_date_elapsed_day_x': 'first_active_date_elapsed_day'}, inplace=True)\n# fill NaN outliers from test with default value\ndf_hist_trans['outlier'].fillna(-1, inplace=True)\ngc.collect()\nprint(lastShape, '->', df_hist_trans.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"763e40f1bd3cb7b68f0a3878841189f6a3f53969"},"cell_type":"code","source":"#save if right now your memory usage is pretty high in order to prevent recalculation in case of memory overflow during further calculation\nwith open('../data/temp_df_transaction_history.csv', 'w') as f:\n df_hist_trans.to_csv(f, header=True, index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"0fce3a26d06bee9a8f75d17ea729e217a681d7e0"},"cell_type":"code","source":"df_hist_trans = pd.read_csv(\"../data/temp_df_transaction_history.csv\")\n\n# make a little preprocessing\ndf_hist_trans['purchase_date'] = pd.to_datetime(df_hist_trans['purchase_date'])\ndf_hist_trans = reduce_mem_usage(df_hist_trans)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"3e2e7348d54d6e9a02fac099474f2e18c3940384"},"cell_type":"code","source":"# for me it took about 9100 seconds\nagg_func = {\n 'outlier': max,\n 'isTrain': max,\n 'purchase_amount': np.median,\n 'purchase_amount_rel': np.mean,\n 'merchant_category_id_count': len,\n 'city_id': lambda x: get_mode(x),\n 'state_id': lambda x: get_mode(x),\n 'installments': lambda x: get_mode(x),\n 'category_3': lambda x: get_mode(x),\n 'merchant_category_id': lambda x: get_mode(x),\n 'subsector_id': lambda x: get_mode(x),\n 'merchant_category_id_last_visited_ref_date_day_diff': min,\n 'last_visited_ref_date_day_diff': min,\n 'last_vis_merch_last_vis_day_diff': min,\n 'first_active_date_elapsed_day': max,\n 'purchase_date': lambda x: (dt.datetime(2018, 3, 1) - x.max()).days, \n}\n\ntm = time.time()\nttt = df_hist_trans[df_hist_trans['first_active_date_elapsed_day']>150].groupby(['card_id', 'merchant_id']).agg(agg_func).reset_index()\nttt.rename(columns={'merchant_category_id_count': 'trans_count'}, inplace=True)\nprint(time.time()-tm)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"b0f33813991eeb2045db6eac39f24741e7873fa6"},"cell_type":"code","source":"ttt.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"987c92b4b291c4d5c5740a488f63fa1e3f488a59"},"cell_type":"code","source":"# defining only test merchant_id\nonlyTestMerch = list(set(ttt[ttt['card_id'].isin(test[test['first_active_date_elapsed_day']>150]['card_id'])]['merchant_id'].unique()) - \\\n set(ttt[ttt['card_id'].isin(train[train['first_active_date_elapsed_day']>150]['card_id'])]['merchant_id'].unique()))\n# defining only train merchant_id\nonlyTrainMerch = list(set(ttt[ttt['card_id'].isin(train[train['first_active_date_elapsed_day']>150]['card_id'])]['merchant_id'].unique()) - \\\n set(ttt[ttt['card_id'].isin(test[test['first_active_date_elapsed_day']>150]['card_id'])]['merchant_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"8eeb666e03758314d082fafaf651b9465b0e123d"},"cell_type":"code","source":"old_merch = merch[merch['merchant_id'].isin(ttt['merchant_id'].unique())][['merchant_id', 'merchant_category_id',\n 'subsector_id', 'numerical_1', \n 'most_recent_sales_range', 'most_recent_purchases_range',\n 'avg_sales_lag3', 'avg_purchases_lag3', 'active_months_lag3',\n 'avg_sales_lag6', 'avg_purchases_lag6', 'active_months_lag6',\n 'avg_sales_lag12', 'avg_purchases_lag12', 'active_months_lag12',\n 'category_4', 'city_id', 'state_id']].reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"2b1b87e20f8885e49e33ea8bc6307ee0dbb0b0fb"},"cell_type":"code","source":"old_merch['TestOrTrain'] = 0\nold_merch.loc[old_merch['merchant_id'].isin(onlyTestMerch), 'TestOrTrain'] = -1\nold_merch.loc[old_merch['merchant_id'].isin(onlyTrainMerch), 'TestOrTrain'] = 1","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"d280d0315a45a126af1c7fc8b8decccd84477651"},"cell_type":"code","source":"old_merch['TestOrTrain'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7e07b88bc7844b69da4b5663328bb3ed6899f06a"},"cell_type":"code","source":"old_merch = merge_two_df(old_merch, ttt.groupby('merchant_id')['card_id'].nunique().reset_index().rename(columns={'card_id': 'merch_card_id_nunique'}), ['merchant_id'], ['merchant_id'])\nold_merch = merge_two_df(old_merch, ttt[ttt['isTrain']==1].groupby('merchant_id')['outlier'].mean().reset_index().rename(columns={'outlier': 'merch_outlier_mean'}), ['merchant_id'], ['merchant_id'])\nold_merch['merch_outlier_mean'] = old_merch['merch_outlier_mean'].fillna(-1)\nold_merch['merch_card_id_nunique'] = old_merch['merch_card_id_nunique'].fillna(-1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"fbd25360edcd333d392ac25953cbb3465fa1a309"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e1e29447a973c5f8c4d949a524c58af5d555d770"},"cell_type":"markdown","source":"## Unfortunately, tring to run all cells above consequently and run a cell below after that, cause a memory overflow for me. However i tested all the code below in my another ugly coding notebook. After waiting for some hours, my RAM memory usage in the notebook is comressed in a magic way (i have no any idea about it)."},{"metadata":{"trusted":false,"_uuid":"9b7f9484bce94981d7ac94a546b642c7198bb7aa"},"cell_type":"code","source":"# define condition for picking only potential outlier card_id - 'first_active_date_elapsed_day'>150\ncondition = df_hist_trans['first_active_date_elapsed_day']>150\n# define native city_id and state_id - city and state with the most frequent number of purchases\nnative_city_state = df_hist_trans[condition].groupby(['card_id'])[['city_id', 'state_id']].agg(lambda x: mode_func(x)).reset_index().rename(columns={'city_id': 'native_city', 'state_id': 'native_state'})\nttt = merge_two_df(ttt, native_city_state, ['card_id'], ['card_id'])\ndel native_city_state;\n\n# quantity of uniquely visited 'city_id' and 'state_id' for each card_id\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby('card_id')[['city_id', 'state_id']].nunique().reset_index().rename(columns={'city_id': 'card_city_nunique', 'state_id': 'card_state_nunique'}), ['card_id'], ['card_id'])\n# quantity of uniquely visited 'city_id' for each card_id per state_id\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['card_id', 'state_id'])[['city_id']].nunique().reset_index().rename(columns={'city_id': 'card_state_city_nunique'}), ['card_id', 'state_id'], ['card_id', 'state_id'])\n\n# quantity of unique merchant in a specific merchant_category_id in a specific city_id\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['city_id', 'merchant_category_id'])['merchant_id'].nunique().reset_index().rename(columns={'merchant_id': 'native_city_merch_cat_merch_nunique'}), ['native_city', 'merchant_category_id'], ['city_id', 'merchant_category_id'])\n# quantity of unique merchant in a specific merchant_category_id in a specific state_id\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['state_id', 'merchant_category_id'])['merchant_id'].nunique().reset_index().rename(columns={'merchant_id': 'native_state_merch_cat_merch_nunique'}), ['native_state', 'merchant_category_id'], ['state_id', 'merchant_category_id'])\nttt.drop(['city_id_y', 'state_id_y'], axis=1, inplace=True)\nttt.rename(columns={'city_id_x': 'city_id', 'state_id_x': 'state_id'}, inplace=True)\n\n# quantity of unique merchant in a specific subsector_id in a specific city_id\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['city_id', 'subsector_id'])['merchant_id'].nunique().reset_index().rename(columns={'merchant_id': 'native_city_subsector_merch_nunique'}), ['native_city', 'subsector_id'], ['city_id', 'subsector_id'])\n# quantity of unique merchant in a specific subsector_id in a specific state_id\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['state_id', 'subsector_id'])['merchant_id'].nunique().reset_index().rename(columns={'merchant_id': 'native_state_subsector_merch_nunique'}), ['native_state', 'subsector_id'], ['state_id', 'subsector_id'])\nttt.drop(['city_id_y', 'state_id_y'], axis=1, inplace=True)\nttt.rename(columns={'city_id_x': 'city_id', 'state_id_x': 'state_id'}, inplace=True)\n\n# number of unique cities visited in a specific merchant_category_id for each card_id\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['card_id', 'merchant_category_id'])['city_id'].nunique().reset_index().rename(columns={'city_id': 'card_merch_cat_city_nunique'}), ['card_id', 'merchant_category_id'], ['card_id', 'merchant_category_id'])\n# number of unique cities visited in a specific merchant_category_id for each card_id in the native state\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['card_id', 'state_id', 'merchant_category_id'])['city_id'].nunique().reset_index().rename(columns={'city_id': 'card_state_merch_cat_city_nunique'}), ['card_id', 'native_state', 'merchant_category_id'], ['card_id', 'state_id', 'merchant_category_id'])\nttt.drop(['state_id_y'], axis=1, inplace=True)\nttt.rename(columns={'state_id_x': 'state_id'}, inplace=True)\n\nttt['isNative_city'] = (ttt['city_id'] == ttt['native_city']) * 1\nttt['isNative_state'] = (ttt['state_id'] == ttt['native_state']) * 1\n\n# tm = time.time()\n# days difference since the last visit for a specific merch for earch card_id\n# for me it takes about 1100 seconds\nagg_func = [\n ('merch_card_last_date_diff', lambda x: (dt.datetime(2018, 3, 1) - x.max()).days)\n]\n\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['card_id', 'merchant_id'])['purchase_date'].agg(agg_func).reset_index(), ['card_id', 'merchant_id'], ['card_id', 'merchant_id'])\n# print(time.time()-tm)\n\nagg_func = [\n ('merch_last_date_diff_min', min),\n ('merch_last_date_diff_max', max),\n ('merch_last_date_diff_mean', np.mean),\n ('merch_last_date_diff_median', np.median),\n]\n\nold_merch = merge_two_df(old_merch, ttt.groupby(['merchant_id'])['merch_card_last_date_diff'].agg(agg_func).reset_index(), ['merchant_id'], ['merchant_id'])\n\nagg_func = [\n ('merchant_category_id_card_id_purchase_amount_rel_mean', np.mean),\n ('merchant_category_id_card_id_purchase_amount_rel_median', np.median),\n ('merchant_category_id_card_id_purchase_amount_count', len),\n]\n\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['merchant_category_id', 'card_id'])['purchase_amount_rel'].agg(agg_func).reset_index(), ['merchant_category_id', 'card_id'], ['merchant_category_id', 'card_id'])\n\nagg_func = [\n ('merchant_category_id_card_id_purchase_amount_abs_mean', np.mean),\n ('merchant_category_id_card_id_purchase_amount_abs_median', np.median),\n]\n\nttt = merge_two_df(ttt, df_hist_trans[condition].groupby(['merchant_category_id', 'card_id'])['purchase_amount'].agg(agg_func).reset_index(), ['merchant_category_id', 'card_id'], ['merchant_category_id', 'card_id'])\ngc.collect()\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"0a16f9393f00840a714950e36c7a4907c392556e"},"cell_type":"code","source":"# fillna for ['native_city_merch_cat_merch_nunique', 'native_state_merch_cat_merch_nunique', 'native_city_subsector_merch_nunique', 'native_state_subsector_merch_nunique', 'card_state_merch_cat_city_nunique']\n# ttt.fillna(0, inplace=True)\nttt[['native_city_merch_cat_merch_nunique',\n 'native_state_merch_cat_merch_nunique',\n 'native_city_subsector_merch_nunique',\n 'native_state_subsector_merch_nunique', 'card_state_merch_cat_city_nunique']] = ttt[['native_city_merch_cat_merch_nunique',\n 'native_state_merch_cat_merch_nunique',\n 'native_city_subsector_merch_nunique',\n 'native_state_subsector_merch_nunique', 'card_state_merch_cat_city_nunique']].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"42420854fe6c7983fc2381ea6d68445a73133d59"},"cell_type":"code","source":"# calculating isTrain_mean is necessary to weighting samples while fitting for prediction outlier_merch_mean\n# for example, we calculated outlier_merch_mean for one merchant_id - 2 outliers from train, 9 card_id from train and 1 card from test\n# in that example outlier_merch_mean = 2/(9 + 1) = 0.2\n# but we don't know whether card from test is outlier or not\n# so we can only suppose that final outlier_merch_mean in this specific example would be either 0.2 (if test card is not outlier) or 0.3 (if test card is outlier)\n# so our outlier_merch_mean = 0.2 with weight = 9/10 - 9 cards from train and total 10 cards (train + test)\n# isTrain_mean - is out outlier_merch_mean weight\n_ = ttt.groupby(['merchant_id'])['isTrain'].mean().reset_index().rename(columns={'isTrain': 'isTrain_mean'})\nttt = merge_two_df(ttt, _, ['merchant_id'], ['merchant_id'])\nold_merch = merge_two_df(old_merch, _, ['merchant_id'], ['merchant_id'])\ndel _; gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"857b3c32ca6c59e234223243fe379f80b618daed"},"cell_type":"code","source":"old_merch = merge_two_df(old_merch, old_merch.groupby('merchant_category_id')['merchant_id'].nunique().reset_index().rename(columns={'merchant_id': 'merch_cat_merch_nunique'}), ['merchant_category_id'], ['merchant_category_id'])\nold_merch = merge_two_df(old_merch, old_merch.groupby(['city_id', 'merchant_category_id'])['merchant_id'].nunique().reset_index().rename(columns={'merchant_id': 'merch_cat_city_merch_nunique'}), ['city_id', 'merchant_category_id'], ['city_id', 'merchant_category_id'])\nold_merch = merge_two_df(old_merch, old_merch.groupby(['state_id', 'merchant_category_id'])['merchant_id'].nunique().reset_index().rename(columns={'merchant_id': 'merch_cat_state_merch_nunique'}), ['state_id', 'merchant_category_id'], ['state_id', 'merchant_category_id'])\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"f1633d56a45376702a54a4f923b6221a335884a6"},"cell_type":"code","source":"old_merch.fillna(0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c5325f98eb994ba0bbcae7c15f6941b9a9969ac4"},"cell_type":"markdown","source":"# lgb regression"},{"metadata":{"trusted":false,"_uuid":"457a7326303acfcf4952eb7b356fc3cf766e6937"},"cell_type":"code","source":"# if all test card_ids are non_outliers\nold_merch['merch_outlier_mean_min'] = old_merch['merch_outlier_mean']*old_merch['isTrain_mean']*old_merch['merch_card_id_nunique']/old_merch['merch_card_id_nunique']\n# if all test card_ids are outliers\nold_merch['merch_outlier_mean_max'] = ((1-old_merch['isTrain_mean'])*old_merch['merch_card_id_nunique'] + old_merch['merch_outlier_mean']*old_merch['isTrain_mean']*old_merch['merch_card_id_nunique'])/old_merch['merch_card_id_nunique']\n# mean between 'merch_outlier_mean_min' and 'merch_outlier_mean_max'\nold_merch['merch_outlier_mean_mean'] = (old_merch['merch_outlier_mean_min'] + old_merch['merch_outlier_mean_max'])/2.0","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"8fa18708bc6aef4ad8d441eaab983b65e75fc761"},"cell_type":"code","source":"old_merch[['merch_outlier_mean_min', 'merch_outlier_mean_max', 'merch_outlier_mean_mean', 'isTrain_mean']].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"d72d14ea398a41cd5d53cc1a691b60fb32e45167"},"cell_type":"code","source":"old_merch.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"a64b68fe3228f9e69729e61582cc3b6786b7fc83"},"cell_type":"code","source":"clmns = ['numerical_1',\n 'most_recent_sales_range', 'most_recent_purchases_range',\n 'avg_sales_lag3', 'avg_purchases_lag3', #'active_months_lag3',\n 'avg_sales_lag6', 'avg_purchases_lag6', #'active_months_lag6',\n 'avg_sales_lag12', 'avg_purchases_lag12',# 'active_months_lag12',\n 'category_4', 'city_id', 'state_id', \n 'merch_card_id_nunique',\n 'merch_last_date_diff_min', 'merch_last_date_diff_max',\n 'merch_last_date_diff_mean', 'merch_last_date_diff_median',\n 'merch_cat_merch_nunique', 'merch_cat_city_merch_nunique',\n 'merch_cat_state_merch_nunique', \n# 'merch_outlier_mean_min', 'merch_outlier_mean_max',\n# 'merch_outlier_mean_mean', \n ]\nclmns_weight = clmns + ['isTrain_mean']\n\ntest_size = 0.2\nrandom_state = 241\ncond = (old_merch['isTrain_mean']>0)# pick all merchants, which have been visited at least by one card_id from train\nx_train, x_test, y_train, y_test = train_test_split(old_merch[cond][clmns_weight], old_merch[cond]['merch_outlier_mean'], test_size=test_size, random_state=random_state)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"4757af471314150b36292b95aed0a3bb4703aea9"},"cell_type":"code","source":"# cat_features = ['most_recent_sales_range', 'most_recent_purchases_range', \n# 'active_months_lag3', 'active_months_lag6', 'active_months_lag12', \n# 'category_4']\n\ntrain_ds = lgb.Dataset(x_train[clmns], y_train, weight=x_train['isTrain_mean'])#, categorical_feature=cat_features)\ntest_ds = lgb.Dataset(x_test[clmns], y_test, weight=x_test['isTrain_mean'])#, categorical_feature=cat_features)\nnum_leaves = 90\nmin_data_in_leaf = 30\nparams = {\n 'objective' :'regression_l1',#regression_l2\n 'bagging_fraction': 0.5, 'feature_fraction': 0.9, 'learning_rate': 0.1, \n 'max_bin': 100, 'min_data_in_leaf': 30, 'num_leaves': 90,\n 'boosting_type' : 'gbdt',\n 'metric': 'l1'#l2_root, l2\n }\nnum_boost = 450\nclf = lgb.train(params, train_ds, num_boost, valid_sets=[test_ds], verbose_eval=25, early_stopping_rounds=50)\n\npred = clf.predict(x_test[clmns])\nprint('TEST MAE:', round(mean_absolute_error(y_test, pred), 8), 'MSE:', round(mean_squared_error(y_test, pred), 8))\npred = clf.predict(x_train[clmns])\nprint('TRAIN MAE:', round(mean_absolute_error(y_train, pred), 8), 'MSE:', round(mean_squared_error(y_train, pred), 8))","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"abda6da207007874d5ceeb29f39419913f9ff2c5"},"cell_type":"code","source":"clf.save_model('../models/merch_outlier_mean_lgb.model')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"1151a7bf8f5a3fa0fe8c2b9211141d49c5ede47a"},"cell_type":"code","source":"feature_imp = pd.DataFrame(sorted(zip(clf.feature_importance(), clmns)), columns=['Value','Feature']).sort_values(by='Value', ascending=False)\nfeature_imp.head(30)\n# clf.feature_importance(), clmns","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7febfee4bbfd0c60c269fe6bf61e273c36a14e51"},"cell_type":"code","source":"old_merch['merch_outlier_mean_pred'] = clf.predict(old_merch[clmns])\n# control that our predictions wouldn't go out of min and max values for a specific merchant_id\nold_merch['merch_outlier_mean_pred_fixed'] = np.minimum(np.maximum(old_merch['merch_outlier_mean_pred'].values, old_merch['merch_outlier_mean_min'].values), old_merch['merch_outlier_mean_max'].values)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"4e2c7e4655d673dd80a2f725e4a273fa2a1c657f"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"37708b923b8f97a8d59d74b7bd7a9be97b5a2f89"},"cell_type":"code","source":"'number of merchants that have been visited at least by one outlier according to the model:', old_merch[old_merch['merch_outlier_mean_pred_fixed']>0].shape[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"37ecc7152fb3129c6815f43ad39e071ae81f59f7"},"cell_type":"markdown","source":"# lgb transaction isOutlier"},{"metadata":{"trusted":false,"_uuid":"40f7bd5528589676cf5f4a2556f42f784229fd71"},"cell_type":"code","source":"ttt.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"16254643579d125e22d54c75e4580776f7bf72ec"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"a2a55b00e0daa8b2223bec82ed25536eefcf8db4"},"cell_type":"code","source":"ttt = merge_two_df(ttt, old_merch[['merchant_id', 'numerical_1',\n 'most_recent_sales_range', 'most_recent_purchases_range',\n 'avg_sales_lag3', 'avg_purchases_lag3', 'active_months_lag3',\n 'avg_sales_lag6', 'avg_purchases_lag6', 'active_months_lag6',\n 'avg_sales_lag12', 'avg_purchases_lag12', 'active_months_lag12',\n 'category_4', 'merch_last_date_diff_min', 'merch_last_date_diff_max',\n 'merch_last_date_diff_mean', 'merch_last_date_diff_median', \n 'merch_outlier_mean_min', 'merch_outlier_mean_max',\n 'merch_outlier_mean_mean', 'merch_outlier_mean_pred',\n 'merch_outlier_mean_pred_fixed']], ['merchant_id'], ['merchant_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"cd748c31da41098a81203036ca12b42826354be5"},"cell_type":"code","source":"ttt = merge_two_df(ttt, train[['card_id', 'feature_1', 'feature_2', 'feature_3']], ['card_id'], ['card_id'])\nttt = merge_two_df(ttt, test[['card_id', 'feature_1', 'feature_2', 'feature_3']], ['card_id'], ['card_id'])\nttt.loc[ttt['feature_1_x'].isnull(), ['feature_1_x', 'feature_2_x', 'feature_3_x']] = ttt.loc[ttt['feature_1_x'].isnull(), ['feature_1_y', 'feature_2_y', 'feature_3_y']].values\nttt.drop(['feature_1_y', 'feature_2_y', 'feature_3_y'], axis=1, inplace=True)\nttt.rename(columns={'feature_1_x': 'feature_1', 'feature_2_x': 'feature_2', 'feature_3_x': 'feature_3'}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"8c2bf4dd600de8be8d9ef3a640535af428b33014"},"cell_type":"code","source":"\nclmns = ['purchase_amount',\n 'purchase_amount_rel', 'installments', 'category_3',\n 'merchant_category_id_last_visited_ref_date_day_diff',\n 'last_visited_ref_date_day_diff', 'last_vis_merch_last_vis_day_diff',\n 'first_active_date_elapsed_day', \n# 'city_id', 'state_id', \n 'card_city_nunique', 'card_state_nunique',\n 'card_state_city_nunique', #'merchant_category_id', 'subsector_id',\n# 'native_city', 'native_state', \n 'native_city_merch_cat_merch_nunique',\n 'native_state_merch_cat_merch_nunique',\n 'native_city_subsector_merch_nunique',\n 'native_state_subsector_merch_nunique', 'card_merch_cat_city_nunique',\n 'card_state_merch_cat_city_nunique', \n# 'isTrain_mean',\n 'merch_card_last_date_diff',\n 'merchant_category_id_card_id_purchase_amount_rel_mean',\n 'merchant_category_id_card_id_purchase_amount_rel_median',\n 'merchant_category_id_card_id_purchase_amount_count',\n 'merchant_category_id_card_id_purchase_amount_abs_mean',\n 'merchant_category_id_card_id_purchase_amount_abs_median',\n #'most_recent_sales_range', 'most_recent_purchases_range',\n 'category_4', 'merch_last_date_diff_max',\n 'merch_last_date_diff_mean', 'merch_last_date_diff_median',\n# 'merch_outlier_mean_min', \n# 'merch_outlier_mean_max',\n# 'merch_outlier_mean_mean',\n# 'merch_outlier_mean_pred',\n 'merch_outlier_mean_pred_fixed', \n 'feature_1', 'feature_2', 'feature_3']\n\nprint(len(clmns))\n# cat_features = ['category_4', 'feature_1', 'feature_2', 'feature_3']\n\nrandom_state = 241\ntest_size = 0.2\ncnd = (ttt['isTrain']==1) & (ttt['merch_outlier_mean_pred_fixed']>0)\nx_train, x_test, y_train, y_test = train_test_split(ttt[cnd][clmns], ttt[cnd]['outlier'], test_size=test_size, random_state=random_state)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"9b3c617ff27382d25b66c024ea743460ed709857"},"cell_type":"code","source":"train_ds = lgb.Dataset(x_train, y_train)#, categorical_feature=cat_features)\ntest_ds = lgb.Dataset(x_test, y_test)#, categorical_feature=cat_features)\nnum_leaves = 150\nmin_data_in_leaf = 150\nparams = {\n 'objective' :'binary',\n 'learning_rate': 0.1,\n 'max_bin': 50,\n# 'max_depth': 10,\n 'num_leaves' : num_leaves,\n 'min_data_in_leaf': min_data_in_leaf,\n 'feature_fraction': 0.64, \n 'bagging_fraction': 0.8, \n 'bagging_freq': 1,\n 'num_threads': 3,\n 'lambda_l1': 1,\n 'lambda_l2': 1,\n 'boosting_type' : 'gbdt',\n 'metric': 'binary_logloss'#l2_root, l2\n }\n\n# you can increase num_boost parameter, if you want, because the model doesn't stop fitting on 250 iteration for me\nnum_boost = 250\nclf = lgb.train(params, train_ds, num_boost, valid_sets=[test_ds], verbose_eval=25, early_stopping_rounds=30)\n\npred = np.round(clf.predict(x_test))\nprint('TEST f1:', f1_score(pred, y_test), 'accuracy:', accuracy_score(y_test, pred))\ndel pred;\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"30013820306a7a4aec5d970bf13faf19a3a30db8"},"cell_type":"code","source":"clf.save_model('../models/card_merch_outlier_lgb.model')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"e4e4f709481cef61634bb682f09e8ca6b7d8cac1"},"cell_type":"code","source":"feature_imp = pd.DataFrame(sorted(zip(clf.feature_importance(), clmns)), columns=['Value','Feature']).sort_values(by='Value', ascending=False)\nfeature_imp.tail(31)\n# clf.feature_importance(), clmns","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7a28fe12550f0fe54aacbdab54c77292b76de811"},"cell_type":"code","source":"cond = (ttt['merch_outlier_mean_pred_fixed']>0)\nttt['isOutlier_pred'] = 0\nttt.loc[cond, 'isOutlier_pred'] = clf.predict(ttt[cond][clmns])\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b45cbaa8a03d2e600fd3e071b135376d4a50d89"},"cell_type":"markdown","source":"# lgb by train"},{"metadata":{"trusted":false,"_uuid":"3b791ac33413b6e384137640b16bb2d01ff382da"},"cell_type":"code","source":"agg_func = {\n 'merchant_category_id': 'nunique',\n 'subsector_id': 'nunique',\n 'purchase_amount': ['mean', ('median', np.median), ],\n 'purchase_amount_rel': 'median',\n 'trans_count': ['sum', ('merch_count', len), 'mean'],\n 'installments': 'median',\n 'category_3': 'median',\n 'merchant_category_id_last_visited_ref_date_day_diff': 'mean',\n 'last_visited_ref_date_day_diff': 'mean',\n 'last_vis_merch_last_vis_day_diff': 'mean',\n 'card_city_nunique': max, \n 'card_state_nunique': max,\n 'card_state_city_nunique': max,\n 'numerical_1': 'mean',\n 'most_recent_sales_range': 'median',\n 'most_recent_purchases_range': 'median',\n 'numerical_1': 'mean',\n 'category_4': 'mean',\n 'merch_outlier_mean_min': 'mean', \n 'merch_outlier_mean_max': 'mean',\n 'merch_outlier_mean_mean': 'mean', \n 'merch_outlier_mean_pred': 'mean',\n 'merch_outlier_mean_pred_fixed': 'mean',\n 'isOutlier_pred': ['min', 'max', 'mean', 'median']\n}\n_ = ttt.groupby('card_id').agg(agg_func).reset_index()\n_.columns = [\"_\".join(x) if x[1] else x[0] for x in _.columns.ravel()]\ntrain = merge_two_df(train, _, ['card_id'], ['card_id'])\ntest = merge_two_df(test, _, ['card_id'], ['card_id'])\n_.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"a3e5dbce35bf912d5fadb6609e9c677e387a3a82"},"cell_type":"code","source":"train.corr()['outlier']","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"2db0f7617b181bd15a2ae88bd8d4815137707b56"},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"792d6c6e9df4b7828d0b2f73e7c4a3b495d1f7ab"},"cell_type":"code","source":"clmns = ['feature_1', 'feature_2', 'feature_3',\n 'first_active_date_elapsed_day', \n 'isOutlier_pred_min', 'isOutlier_pred_max', 'isOutlier_pred_mean',\n 'isOutlier_pred_median', 'merchant_category_id_nunique',\n 'subsector_id_nunique', 'purchase_amount_mean',\n 'purchase_amount_median', 'purchase_amount_rel_median',\n 'trans_count_sum', 'trans_count_merch_count', 'trans_count_mean',\n 'installments_median', 'category_3_median',\n 'merchant_category_id_last_visited_ref_date_day_diff_mean',\n 'last_visited_ref_date_day_diff_mean',\n 'last_vis_merch_last_vis_day_diff_mean', 'card_city_nunique_max',\n 'card_state_nunique_max', 'card_state_city_nunique_max',\n 'numerical_1_mean', 'most_recent_sales_range_median',\n 'most_recent_purchases_range_median', 'category_4_mean',\n# 'merch_outlier_mean_min_mean', 'merch_outlier_mean_max_mean',\n# 'merch_outlier_mean_mean_mean', 'merch_outlier_mean_pred_mean',\n 'merch_outlier_mean_pred_fixed_mean']\n\n# clmns = [\n# 'isOutlier_pred0_mean', 'isOutlier_pred0_median',\n# 'merch_outlier_mean_pred_fixed',\n# 'feature_1', 'feature_2', 'feature_3', \n# # 'first_active_date_elapsed_day', \n# # 'merch_outlier_mean_pred_fixed_mean',\n# # 'merch_outlier_mean', 'merch_outlier_sum',\n# # 'merch_outlier_max'\n# ]\n\nrandom_state = 241\ntest_size = 0.3\nx_train, x_test, y_train, y_test = train_test_split(train[clmns], train['outlier'], test_size=test_size, random_state=random_state)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"30e9760893ef6508e9a72f3f5f3bf215aeb49856"},"cell_type":"code","source":"train_ds = lgb.Dataset(x_train, y_train)#, categorical_feature=cat_features)\ntest_ds = lgb.Dataset(x_test, y_test)#, categorical_feature=cat_features)\nnum_leaves = 50\nmin_data_in_leaf = 50\nparams = {\n 'objective' :'binary',\n 'learning_rate': 0.05,\n 'max_bin': 50,\n 'max_depth': 5,\n 'num_leaves' : num_leaves,\n 'min_data_in_leaf': min_data_in_leaf,\n# 'feature_fraction': 0.64, \n# 'bagging_fraction': 0.8, \n# 'bagging_freq': 1,\n# 'device': 'gpu',\n 'lambda_l1': 5,\n 'lambda_l2': 5,\n# 'min_gain_to_split': .1,\n 'boosting_type' : 'gbdt',\n 'metric': 'binary_logloss'#l2_root, l2\n }\nnum_boost = 350\nclf = lgb.train(params, train_ds, num_boost, valid_sets=[test_ds], verbose_eval=25, early_stopping_rounds=30)\n\npred = np.round(clf.predict(x_test))\nprint('TEST f1:', f1_score(pred, y_test), 'accuracy:', accuracy_score(y_test, pred), 'number of bad predicted samples:', (1-accuracy_score(y_test, pred))*y_test.shape[0], 'out of', y_test.shape[0])\nprint('number of outliers in validation set:', y_test.value_counts().values[1])\npred = np.round(clf.predict(x_train))\nprint('TRAIN f1:', f1_score(pred, y_train), 'accuracy:', accuracy_score(y_train, pred), 'number of bad predicted samples:', (1-accuracy_score(y_train, pred))*y_train.shape[0], 'out of', y_test.shape[0])\nprint('number of outliers in training set:', y_train.value_counts().values[1])\n\ncond = train['outlier']==1\n# threshold = .7\npred = np.round(clf.predict(train[cond][clmns]))\nprint('bad classified outliers in all TRAIN dataset:', (1-accuracy_score(train[cond]['outlier'], pred))*train[cond].shape[0])\n \ncond = train['outlier']==0\npred = np.round(clf.predict(train[cond][clmns]))\nprint('bad classified good cards in all TRAIN dataset:', (1-accuracy_score(train[cond]['outlier'], pred))*train[cond].shape[0])\n","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"13b680293a8b13b26dbb4f4aa4a1e59961869b97"},"cell_type":"code","source":"feature_imp = pd.DataFrame(sorted(zip(clf.feature_importance(), clmns)), columns=['Value','Feature']).sort_values(by='Value', ascending=False)\nfeature_imp.tail(34)\n# clf.feature_importance(), clmns","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"25ed7cf0420124311e2a37f86a89470b95a54317"},"cell_type":"code","source":"#distribution of predicted outliers in TEST.csv\npd.Series(np.round(clf.predict(test[clmns]))).value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"3bb137e7424e49444d93d5054f27a86253302273"},"cell_type":"code","source":"#distribution of predicted outliers in TEST.csv\npd.Series(np.round(clf.predict(test[clmns]))).value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5299496d68d6d643b2b5cd29d14820f33f6a063d"},"cell_type":"markdown","source":"### If we look at the predicted probability distribution, we can notice that our model is very confident about it's own predictions on validation set as on training set. However, for test, our model predicts too low number of outliers and combining outlier-prediction model with the regression model, trained whether with or without outliers, doesn't improve LB score at all (score is much worse actually). Probably there is some leakage in my validation set, but actually i don't know where. I think that I have a leakage in predicting merch_outlier_mean, but i thought that after adding weights for samples based on isTrain_mean I would be able to get rid of that leakage and make my CV close to LB. Now i still think that the main reason of such difference between my CV and LB in prediction of merch_outlier_mean (suposing that train and test have the same outlier distribution), but I don't know how to make CV in this competion for that concrete example. Hope you would be smarter than me and be able to predict outliers properly.\n### If you have any questions or ideas, found some laekage, got some errors in my code (but not a memory overflow), or have some ideas about optimizing my code (for example memory optimization of merging 2 dataframes (or dataframe and group object) or any speeding up the calculation tips), then don't be shy and leave all your comments below under this kernel! If you found this kernel kinda usefull, just upvote it - i would very appreciate it)"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.6"}},"nbformat":4,"nbformat_minor":1}