{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import os\nimport gc\nimport glob\nimport tqdm\nimport numpy as np\nimport pandas as pd","metadata":{"execution":{"iopub.status.busy":"2022-07-10T08:57:04.921970Z","iopub.execute_input":"2022-07-10T08:57:04.922892Z","iopub.status.idle":"2022-07-10T08:57:04.928521Z","shell.execute_reply.started":"2022-07-10T08:57:04.922839Z","shell.execute_reply":"2022-07-10T08:57:04.927346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_data = pd.read_parquet('../input/amex-data-integer-dtypes-parquet-format/train.parquet')\ntrain_labels = pd.read_csv('../input/amex-default-prediction/train_labels.csv')\ntest_data = pd.read_parquet('../input/amex-data-integer-dtypes-parquet-format/test.parquet')\nsubmission = pd.read_csv('../input/amex-default-prediction/sample_submission.csv')","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-07-10T09:14:17.208298Z","iopub.execute_input":"2022-07-10T09:14:17.209129Z","iopub.status.idle":"2022-07-10T09:15:08.744835Z","shell.execute_reply.started":"2022-07-10T09:14:17.209077Z","shell.execute_reply":"2022-07-10T09:15:08.743016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# I shall use only these columns\nuse = ['customer_ID',\n       'S_2',\n       'P_2',\n       'D_39',\n       'B_3',\n       'B_4',\n       'B_5',\n       'D_46',\n       'R_1',\n       'S_3',\n       'D_42',\n       'B_1',\n       'B_11',\n       'P_3',\n       'D_44',\n       'D_48',\n       'B_9',\n       'D_121',\n       'S_23',\n       'S_5',\n       'S_12',\n       'D_49',\n       'B_37',\n       'D_62',\n       'B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68','B_31', 'D_87']","metadata":{"execution":{"iopub.status.busy":"2022-07-10T09:29:48.799600Z","iopub.execute_input":"2022-07-10T09:29:48.800151Z","iopub.status.idle":"2022-07-10T09:29:48.809069Z","shell.execute_reply.started":"2022-07-10T09:29:48.800108Z","shell.execute_reply":"2022-07-10T09:29:48.808057Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_data = train_data[use]","metadata":{"execution":{"iopub.status.busy":"2022-07-10T09:13:04.627928Z","iopub.execute_input":"2022-07-10T09:13:04.628422Z","iopub.status.idle":"2022-07-10T09:13:05.521889Z","shell.execute_reply.started":"2022-07-10T09:13:04.628379Z","shell.execute_reply":"2022-07-10T09:13:05.520726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_data = test_data[use]","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(train_data.shape, train_labels.shape)\nprint(test_data.shape, submission.shape)","metadata":{"execution":{"iopub.status.busy":"2022-07-10T08:31:29.141681Z","iopub.execute_input":"2022-07-10T08:31:29.142038Z","iopub.status.idle":"2022-07-10T08:31:29.147589Z","shell.execute_reply.started":"2022-07-10T08:31:29.142008Z","shell.execute_reply":"2022-07-10T08:31:29.146479Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"bin_cols = ['B_31', 'D_87']\ncat_cols = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\nnum_cols = list(set(train_data.columns)-set(cat_cols+['S_2', 'customer_ID']))\nint8_num_cols = list(set(train_data.dtypes[train_data.dtypes==np.int8].axes[0]) - set(cat_cols))\nint16_num_cols = list(set(train_data.dtypes[train_data.dtypes==np.int16].axes[0]) - set(cat_cols))\nfloat32_num_cols = list(set(train_data.dtypes[train_data.dtypes==np.float32].axes[0]) - set(cat_cols))\n\ndef last_2(series):\n    return series.values[-2] if len(series.values)>=2 else -127\n\ndef last_3(series):\n    return series.values[-3] if len(series.values)>=3 else -127\n\n\nprint(\"We have {} Categorical features and {} Numerical features\".format(len(cat_cols), len(num_cols)))","metadata":{"execution":{"iopub.status.busy":"2022-07-10T08:31:29.151062Z","iopub.execute_input":"2022-07-10T08:31:29.151898Z","iopub.status.idle":"2022-07-10T08:31:29.166902Z","shell.execute_reply.started":"2022-07-10T08:31:29.151851Z","shell.execute_reply":"2022-07-10T08:31:29.165607Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"### Encode customer ids(https://www.kaggle.com/competitions/amex-default-prediction/discussion/328054)\n\ntrain_data['customer_ID'] = train_data['customer_ID'].apply(lambda x: int(x[-16:], 16)).astype(np.int64)\ntrain_labels['customer_ID'] = train_labels['customer_ID'].apply(lambda x: int(x[-16:], 16)).astype(np.int64)\ntest_data['customer_ID'] = test_data['customer_ID'].apply(lambda x: int(x[-16:], 16)).astype(np.int64)\nsubmission['customer_ID'] = submission['customer_ID'].apply(lambda x: int(x[-16:], 16)).astype(np.int64)","metadata":{"execution":{"iopub.status.busy":"2022-07-10T08:31:29.169206Z","iopub.execute_input":"2022-07-10T08:31:29.169866Z","iopub.status.idle":"2022-07-10T08:31:42.13935Z","shell.execute_reply.started":"2022-07-10T08:31:29.169811Z","shell.execute_reply":"2022-07-10T08:31:42.138142Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# DATE RELATED FEATURES(S_2)","metadata":{}},{"cell_type":"code","source":"def take_first_col(series):\n    return series.values[0]\n\ndef prepare_date_features(df):\n    ### Drop all other columns except the S_2 and customer_ID(cat_cols, num_cols)\n    df = df.drop(cat_cols+num_cols, axis=1)\n    \n    ### Converting S_2 column to datetime column\n    df['S_2'] = pd.to_datetime(df['S_2'])\n\n    ### How many rows of records does each customer has?\n    df['rec_len'] = df[['customer_ID', 'S_2']].groupby(by=['customer_ID'])['S_2'].transform('count')\n\n    ### Encode the 1st statement and the last statement time\n    df['S_2_first'] = df[['customer_ID', 'S_2']].groupby(by=['customer_ID'])['S_2'].transform('min')\n    df['S_2_last'] = df[['customer_ID', 'S_2']].groupby(by=['customer_ID'])['S_2'].transform('max')\n\n    ### For how long(days) the customer is receiving the statements\n    df['S_2_period'] = (df[['customer_ID', 'S_2']].groupby(by=['customer_ID'])['S_2'].transform('max') - df[['customer_ID', 'S_2']].groupby(by=['customer_ID'])['S_2'].transform('min')).dt.days\n\n    ### Days Between 2 statements \n    df['days_between_statements'] = df[['customer_ID', 'S_2']].sort_values(by=['customer_ID', 'S_2']).groupby(by=['customer_ID'])['S_2'].transform('diff').dt.days\n    df['days_between_statements'] = df['days_between_statements'].fillna(0)\n    df['days_between_statements_mean'] = df[['customer_ID', 'days_between_statements']].sort_values(by=['customer_ID', 'days_between_statements']).groupby(by=['customer_ID']).transform('mean')\n    df['days_between_statements_std'] = df[['customer_ID', 'days_between_statements']].sort_values(by=['customer_ID', 'days_between_statements']).groupby(by=['customer_ID']).transform('std')\n    df['days_between_statements_max'] = df[['customer_ID', 'days_between_statements']].sort_values(by=['customer_ID', 'days_between_statements']).groupby(by=['customer_ID']).transform('max')\n    df['days_between_statements_min'] = df[['customer_ID', 'days_between_statements']].sort_values(by=['customer_ID', 'days_between_statements']).groupby(by=['customer_ID']).transform('min')\n\n    ### https://www.kaggle.com/code/lucasmorin/amex-lgbm-features-eng/notebook\n    df['S_2'] = (df['S_2_last']-df['S_2']).dt.days\n\n    ### Difference between S_2_last(max) and S_2_last \n    df['S_2_last_diff'] = (df['S_2_last'].max()-df['S_2_last']).dt.days\n\n    ### Difference between S_2_first(min) and S_2_first \n    df['S_2_first_diff'] = (df['S_2_first'].min()-df['S_2_first']).dt.days\n\n    ### Get the (day,month,year) and drop the S_2_first because we can't directly use them\n    df['S_2_first_dd'] = df['S_2_first'].dt.day\n    df['S_2_first_mm'] = df['S_2_first'].dt.month\n    df['S_2_first_yy'] = df['S_2_first'].dt.year\n    \n    df['S_2_last_dd'] = df['S_2_last'].dt.day\n    df['S_2_last_mm'] = df['S_2_last'].dt.month\n    df['S_2_last_yy'] = df['S_2_last'].dt.year\n    \n    agg_df = df.groupby(by=['customer_ID']).agg({'S_2':['last', last_2, last_3],\n                                                 'days_between_statements':['last', last_2, last_3]})\n    agg_df.columns = [i+'_'+j for i in ['S_2', 'days_between_statements'] for j in ['last', 'last_2', 'last_3']]\n    df = df.groupby(by=['customer_ID']).agg(take_first_col)\n    df = df.merge(agg_df, how='inner', left_index=True, right_index=True)\n    df = df.drop(['S_2', 'days_between_statements', 'S_2_first', 'S_2_last_x'], axis=1)\n\n    return df ","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## NUMERICAL FEATURES(num_cols)","metadata":{}},{"cell_type":"code","source":"def prepare_numerical_features(df):\n    for num_c in list(num_cols):\n        col_dtype = df[num_c].dtype\n        df[num_c] = df[num_c].fillna(df[num_c].mean())\n        df[num_c] = df[num_c].astype(col_dtype)\n    \n    df['S_2'] = pd.to_datetime(df['S_2'])\n    df = df.sort_values(by=['customer_ID', 'S_2'])\n    ### Drop cat columns and S_2 so that you only have num features and customer_ID\n    df = df.drop(cat_cols+['S_2'], axis=1)\n    num_feature_list = ['min', 'max', 'mean', 'std', 'last', last_2, last_3]\n    \n    df_float32_agg = df[['customer_ID']+float32_num_cols].groupby(by=['customer_ID']).agg(num_feature_list).astype(np.float32)\n    df_float32_agg.columns = [str(c[0])+'_'+str(c[1]) for c in df_float32_agg.columns]\n    \n    df_int_agg = df[['customer_ID']+int8_num_cols+int16_num_cols].groupby(by=['customer_ID']).agg(num_feature_list).astype(np.float16)\n    df_int_agg.columns = [str(c[0])+'_'+str(c[1]) for c in df_int_agg.columns]\n    \n    #df_agg = df.groupby(by=['customer_ID']).agg(num_feature_list).astype(np.float32)\n    #df_agg.columns = [str(c[0])+'_'+str(c[1]) for c in df_agg.columns]\n    df_agg = df_float32_agg.merge(df_int_agg, left_index=True, right_index=True)\n    df_agg[[ii+'_last' for ii in int8_num_cols]] = df_agg[[ii+'_last' for ii in int8_num_cols]].astype(np.int8)\n    df_agg[[ii+'_last_2' for ii in int8_num_cols]] = df_agg[[ii+'_last_2' for ii in int8_num_cols]].astype(np.int8)\n    df_agg[[ii+'_last_3' for ii in int8_num_cols]] = df_agg[[ii+'_last_3' for ii in int8_num_cols]].astype(np.int8)\n    \n    del df, df_float32_agg, df_int_agg\n    gc.collect()\n    return df_agg","metadata":{"execution":{"iopub.status.busy":"2022-07-10T08:31:42.141832Z","iopub.execute_input":"2022-07-10T08:31:42.142313Z","iopub.status.idle":"2022-07-10T08:31:42.154444Z","shell.execute_reply.started":"2022-07-10T08:31:42.142266Z","shell.execute_reply":"2022-07-10T08:31:42.153445Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## CATEGORICAL FEATURES(cat_cols+bin_cols)","metadata":{}},{"cell_type":"code","source":"### https://www.kaggle.com/code/lucasmorin/amex-feature-engineering\ndef prepare_cat_features(df):\n    remove = ['customer_ID']\n\n    agg_dict_num = {}\n    agg_dict_cat = {}\n\n    mean_diff = lambda x: np.nanmean(np.diff(x.values))\n    mean_diff.__name__ = 'mean_diff'\n\n    for c in df.columns:\n        if c not in remove:\n            if c not in cat_cols+bin_cols:\n                agg_dict_num[c] = ['mean','std','min','max','last', last_2, last_3]\n            else:\n                agg_dict_cat[c] = ['nunique', ] \n    \n    df.loc[:,cat_cols+bin_cols] = df.loc[:,cat_cols+bin_cols].astype(str)\n    df_agg = df.groupby('customer_ID').agg(agg_dict_cat)\n    df_agg.columns = [str(c[0])+'_'+str(c[1]) for c in df_agg.columns]\n    df_list = []\n    for c in cat_cols+bin_cols:\n        df_cat = df.groupby(['customer_ID',c])[c].count()\n        df_cat = df_cat.unstack()\n        df_cat.columns = [df_cat.columns.name + '_' + c for c in df_cat.columns]\n        df_cat = df_cat.fillna(0)\n        df_list.append(df_cat)\n    df_out = pd.concat([df_agg]+df_list, axis=1)\n    df_out = df_out.fillna(np.nanmean(df_out))\n    \n    del df\n    gc.collect()\n    return df_out","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Scaling","metadata":{}},{"cell_type":"code","source":"from sklearn.preprocessing import StandardScaler, MinMaxScaler\ndef standardize(train_df, test_df):\n    scaler = StandardScaler()\n    scaler.fit(train_df)\n    train_df = scaler.transform(train_df)\n    test_df = scaler.transform(test_df)\n    return train_df, test_df\n\ndef minmax(train_df, test_df):\n    scaler = MinMaxScaler()\n    scaler.fit(train_df)\n    train_df = scaler.transform(train_df)\n    test_df = scaler.transform(test_df)\n    return train_df, test_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**After we have all the features defined it's time to split the data(both train and test) and apply the transformations and then save them to pickle files.\n\n## Applying on Train Data","metadata":{}},{"cell_type":"code","source":"# drf_df =prepare_date_features(train_data)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drf_df.to_pickle(\"drf_df.pkl\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drf_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# nf_df = prepare_numerical_features(train_data)","metadata":{"execution":{"iopub.status.busy":"2022-07-10T08:31:42.699764Z","iopub.execute_input":"2022-07-10T08:31:42.700725Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# nf_df.to_pickle(\"nf_df.pkl\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# nf_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Applying on Train Data","metadata":{}},{"cell_type":"code","source":"%%time\nfrom sklearn.model_selection import StratifiedKFold\n\nkf = StratifiedKFold(n_splits=5)\ntrain_5_fold_splits = []\nfor train_index, val_index in kf.split(train_labels['customer_ID'], train_labels['target']):\n    print(train_labels.iloc[val_index]['target'].value_counts())\n    train_5_fold_splits.append(train_labels.iloc[val_index]['customer_ID'])\n\ntrain_labels = train_labels.set_axis(train_labels['customer_ID'])\ntrain_labels = train_labels.drop(['customer_ID'], axis=1)\n\nfor (i,ids) in enumerate(train_5_fold_splits):\n    print(i, len(ids))\n    train_data_part = train_data[train_data.customer_ID.isin(ids)].sort_values(by=['customer_ID'])\n    y = train_labels.loc[ids]['target']\n    np.save(\"train_y_{}.npy\".format(i), y)\n    train_data_time = prepare_date_features(train_data_part).sort_values(by=['customer_ID'])\n    train_data_num = prepare_numerical_features(train_data_part).sort_values(by=['customer_ID'])\n    train_data_cat = prepare_cat_features(train_data_part).sort_values(by=['customer_ID'])\n    assert list(train_data_time.axes[0])==list(train_data_num.axes[0])==list(train_data_cat.axes[0])\n    ### Save to Pickle\n    train_data_time.merge(train_data_cat, left_index=True, right_index=True).merge(train_data_num, left_index=True, right_index=True).to_pickle('train_data_{}.pkl'.format(i))\n\n    del train_data_time, train_data_num, train_data_cat, train_data_part, y\n    gc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Applying on Test Data","metadata":{}},{"cell_type":"code","source":"%%time\n\n# https://stackoverflow.com/questions/2130016/splitting-a-list-into-n-parts-of-approximately-equal-length\ndef split(a, n):\n    k, m = divmod(len(a), n)\n    return (a[i*k+min(i, m):(i+1)*k+min(i+1, m)] for i in range(n))\n\ntest_split_ids = split(test_data.customer_ID.unique(),25)\n\nfor (i,ids) in enumerate(test_split_ids):\n    print(i, len(ids))\n    test_data_part = test_data[test_data.customer_ID.isin(ids)].sort_values(by=['customer_ID'])\n    \n    test_data_time = prepare_date_features(test_data_part).sort_values(by=['customer_ID'])\n    test_data_num = prepare_numerical_features(test_data_part).sort_values(by=['customer_ID'])\n    test_data_cat = prepare_cat_features(test_data_part).sort_values(by=['customer_ID'])\n    assert list(test_data_part.axes[0])==list(test_data_part.axes[0])==list(test_data_part.axes[0])\n    ### Save to Pickle\n    test_data_time.merge(test_data_cat, left_index=True, right_index=True).merge(test_data_num, left_index=True, right_index=True).to_pickle('test_data_{}.pkl'.format(i))\n\n    del test_data_time, test_data_num, test_data_cat, test_data_part\n    gc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# [Reference](https://www.kaggle.com/code/susnato/amex-data-preprocesing-feature-engineering)","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}