{"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":"markdown","source":"## AMEX Default Competition - Feature Engineering\n\n\n- This notebook does the following:\n\n    - aggregating data by customer_ID\n        - get the min, max, mean, std, first, last for each numeric features\n            - then create a feature that checks if the last number if with 1.5 standard deviation of mean\n        - get the unique count for categorical features \n\n    \n- the raw data used for this notebook is generated by:\n    - Process Amex Train Data to Parquet Format: https://www.kaggle.com/code/xxxxyyyy80008/process-amex-train-data-to-parquet-format\n    - datasets can be accessed here:\n        - train file: https://www.kaggle.com/datasets/xxxxyyyy80008/amex-train-20220706\n        - test file: https://www.kaggle.com/datasets/xxxxyyyy80008/amex-test-20020706\n    \n- this notebook also used some insights from this notebook:\n     - AMEX - Train Data EDA - Dask for Fast Analysis: https://www.kaggle.com/code/xxxxyyyy80008/amex-train-data-eda-dask-for-fast-analysis\n     \n- the data output of this notebook can be accessed here:\n     - train data: https://www.kaggle.com/datasets/xxxxyyyy80008/amex-agg-data-rev2\n   \n\n","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-07-12T01:15:15.180928Z","iopub.execute_input":"2022-07-12T01:15:15.181312Z","iopub.status.idle":"2022-07-12T01:15:15.192646Z","shell.execute_reply.started":"2022-07-12T01:15:15.181281Z","shell.execute_reply":"2022-07-12T01:15:15.190939Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport gc\nimport copy\nimport os\nimport sys\n\nfrom pathlib import Path\nfrom datetime import datetime, date, time, timedelta\nfrom dateutil import relativedelta\n\nimport pyarrow.parquet as pq\nimport pyarrow as pa\n\nimport dask.dataframe as dd","metadata":{"execution":{"iopub.status.busy":"2022-07-12T01:15:51.923302Z","iopub.execute_input":"2022-07-12T01:15:51.923683Z","iopub.status.idle":"2022-07-12T01:15:51.930788Z","shell.execute_reply.started":"2022-07-12T01:15:51.923654Z","shell.execute_reply":"2022-07-12T01:15:51.929647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import warnings\nwarnings.filterwarnings(\"ignore\")\n\npd.options.display.max_rows = 100\npd.options.display.max_columns = 100\n\n\nimport pytorch_lightning as pl\nrandom_seed=1234\npl.seed_everything(random_seed)","metadata":{"execution":{"iopub.status.busy":"2022-07-12T01:15:53.802471Z","iopub.execute_input":"2022-07-12T01:15:53.803432Z","iopub.status.idle":"2022-07-12T01:16:07.203670Z","shell.execute_reply.started":"2022-07-12T01:15:53.803365Z","shell.execute_reply":"2022-07-12T01:16:07.202611Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## aggregate by customer id","metadata":{}},{"cell_type":"code","source":"all_cols = ['customer_ID', 'S_2', 'P_2', 'D_39', 'B_1', 'B_2', 'R_1', 'S_3', 'D_41', 'B_3', 'D_42', 'D_43', 'D_44', 'B_4', 'D_45', 'B_5', 'R_2', 'D_46', 'D_47', 'D_48', 'D_49', 'B_6', 'B_7', 'B_8', 'D_50', 'D_51', 'B_9', 'R_3', 'D_52', 'P_3', 'B_10', 'D_53', 'S_5', 'B_11', 'S_6', 'D_54', 'R_4', 'S_7', 'B_12', 'S_8', 'D_55', 'D_56', 'B_13', 'R_5', 'D_58', 'S_9', 'B_14', 'D_59', 'D_60', 'D_61', 'B_15', 'S_11', 'D_62', 'D_63', 'D_64', 'D_65', 'B_16', 'B_17', 'B_18', 'B_19', 'D_66', 'B_20', 'D_68', 'S_12', 'R_6', 'S_13', 'B_21', 'D_69', 'B_22', 'D_70', 'D_71', 'D_72', 'S_15', 'B_23', 'D_73', 'P_4', 'D_74', 'D_75', 'D_76', 'B_24', 'R_7', 'D_77', 'B_25', 'B_26', 'D_78', 'D_79', 'R_8', 'R_9', 'S_16', 'D_80', 'R_10', 'R_11', 'B_27', 'D_81', 'D_82', 'S_17', 'R_12', 'B_28', 'R_13', 'D_83', 'R_14', 'R_15', 'D_84', 'R_16', 'B_29', 'B_30', 'S_18', 'D_86', 'D_87', 'R_17', 'R_18', 'D_88', 'B_31', 'S_19', 'R_19', 'B_32', 'S_20', 'R_20', 'R_21', 'B_33', 'D_89', 'R_22', 'R_23', 'D_91', 'D_92', 'D_93', 'D_94', 'R_24', 'R_25', 'D_96', 'S_22', 'S_23', 'S_24', 'S_25', 'S_26', 'D_102', 'D_103', 'D_104', 'D_105', 'D_106', 'D_107', 'B_36', 'B_37', 'R_26', 'R_27', 'B_38', 'D_108', 'D_109', 'D_110', 'D_111', 'B_39', 'D_112', 'B_40', 'S_27', 'D_113', 'D_114', 'D_115', 'D_116', 'D_117', 'D_118', 'D_119', 'D_120', 'D_121', 'D_122', 'D_123', 'D_124', 'D_125', 'D_126', 'D_127', 'D_128', 'D_129', 'B_41', 'B_42', 'D_130', 'D_131', 'D_132', 'D_133', 'R_28', 'D_134', 'D_135', 'D_136', 'D_137', 'D_138', 'D_139', 'D_140', 'D_141', 'D_142', 'D_143', 'D_144', 'D_145']\n\nid_feats = ['customer_ID']\ndate_col =  'S_2'\ncat_feats = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68', 'B_31']\nmissing20 = ['D_87', 'D_88', 'D_108', 'D_111', 'D_110', 'B_39', 'D_73', 'B_42', 'D_134', 'D_135', 'D_136', 'D_137', 'D_138', 'R_9', 'B_29', 'D_106', 'D_132', 'D_49', 'R_26', 'D_76', 'D_66', 'D_42', 'D_142', 'D_53', 'D_82', 'D_50', 'B_17', 'D_105', 'D_56', 'S_9', 'D_77', 'D_43', 'S_27', 'D_46']\nfloat_feats = ['R_17', 'B_40', 'R_27', 'S_18', 'B_13', 'B_33', 'R_20', 'S_6', 'R_23', 'R_16', 'B_24', 'D_125', 'D_44', 'D_91', 'D_71', 'P_2', 'B_15', 'D_103', 'S_12', 'D_144', 'D_123', 'D_94', 'D_70', 'D_39', 'P_4', 'S_23', 'R_12', 'S_5', 'D_72', 'B_27', 'B_6', 'D_89', 'D_143', 'D_80', 'B_3', 'B_28', 'R_11', 'B_14', 'B_1', 'D_124', 'D_109', 'B_25', 'B_36', 'B_5', 'B_18', 'D_61', 'R_13', 'B_37', 'S_7', 'D_104', 'B_26', 'B_4', 'R_6', 'D_133', 'B_21', 'S_19', 'D_115', 'R_18', 'D_45', 'D_69', 'S_24', 'D_84', 'S_17', 'B_12', 'D_52', 'R_24', 'D_127', 'R_14', 'D_113', 'D_83', 'D_141', 'B_10', 'S_22', 'D_96', 'R_15', 'S_25', 'D_54', 'D_60', 'D_59', 'S_11', 'R_8', 'D_74', 'R_4', 'D_118', 'D_62', 'B_7', 'S_15', 'B_2', 'R_28', 'S_26', 'D_119', 'D_86', 'D_81', 'D_93', 'R_3', 'B_16', 'B_9', 'D_107', 'D_78', 'D_140', 'S_13', 'B_11', 'D_47', 'R_1', 'D_55', 'R_22', 'D_102', 'D_112', 'D_131', 'B_32', 'R_10', 'R_7', 'R_19', 'D_41', 'D_130', 'B_23', 'R_5', 'D_121', 'B_19', 'P_3', 'B_8', 'D_79', 'D_122', 'S_3', 'R_25', 'D_92', 'D_58', 'D_51', 'B_41', 'S_8', 'B_22', 'D_139', 'R_2', 'D_48', 'D_145', 'D_129', 'B_20', 'S_16', 'S_20', 'D_128', 'D_75', 'D_65', 'R_21']\nlog_feats= ['B_13', 'S_5', 'B_27', 'B_3', 'B_5', 'B_18', 'B_4', 'D_115', 'D_45', 'D_60', 'D_118', 'R_28', 'S_26', 'D_119', 'B_11', 'D_102']\n","metadata":{"execution":{"iopub.status.busy":"2022-07-12T01:16:07.205740Z","iopub.execute_input":"2022-07-12T01:16:07.206620Z","iopub.status.idle":"2022-07-12T01:16:07.231128Z","shell.execute_reply.started":"2022-07-12T01:16:07.206589Z","shell.execute_reply":"2022-07-12T01:16:07.230306Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(float_feats) + len(cat_feats) + len(missing20), len(all_cols)\nset(all_cols)-set(float_feats + cat_feats + missing20), len(set(float_feats + cat_feats + missing20))","metadata":{"execution":{"iopub.status.busy":"2022-07-12T01:16:07.232610Z","iopub.execute_input":"2022-07-12T01:16:07.233221Z","iopub.status.idle":"2022-07-12T01:16:07.269658Z","shell.execute_reply.started":"2022-07-12T01:16:07.233189Z","shell.execute_reply":"2022-07-12T01:16:07.268600Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ntrain_file = '/kaggle/input/amex-train-20220706/train.parquet'\n\ntest_file = '/kaggle/input/amex-test-20020706/amex_test_20220706.parquet'","metadata":{"execution":{"iopub.status.busy":"2022-07-12T01:16:07.271881Z","iopub.execute_input":"2022-07-12T01:16:07.272209Z","iopub.status.idle":"2022-07-12T01:16:07.281871Z","shell.execute_reply.started":"2022-07-12T01:16:07.272180Z","shell.execute_reply":"2022-07-12T01:16:07.280856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Train file processing","metadata":{}},{"cell_type":"code","source":"%%time\nagg_files = []\nstats = []\nfor c in set(float_feats + missing20):\n    df = dd.read_parquet(train_file, columns=['customer_ID', 'S_2',c], engine='pyarrow')\n    x = df.compute().sort_values(by='S_2', ascending=True).groupby(\"customer_ID\").agg({c: ['min', 'max', 'mean', 'std', 'first','last']})\n    x.columns = [f'{c1}|{c2}' for c1, c2 in x.columns]\n    \n    x[f'{c}_mean2std'] = (x[f'{c}|last'] >= (x[f'{c}|mean']-1.5*x[f'{c}|std'])) & (x[f'{c}|last'] <= (x[f'{c}|mean']+1.5*x[f'{c}|std']))\n    x[f'{c}_mean2std'] = x[f'{c}_mean2std'].astype(int)\n\n    pq.write_table(pa.Table.from_pandas(x[[f'{c}|min', f'{c}|max', f'{c}|mean', f'{c}|last',f'{c}_mean2std']]), \n                   f'{c}.parquet', compression = 'GZIP')\n    agg_files.append(f'{c}.parquet')\n    \n    #--calculate the stats ------------------------------------------------------------------\n\n    cc_ = f'{c}|last'\n    item = [c, cc_, x[cc_].min(), x[cc_].max(), x[cc_].mean(), x[cc_].std(), x[cc_].median(), x[cc_].skew(), x[cc_].kurtosis()]\n\n    cc_ = f'{c}|mean'\n    item.extend([cc_, x[cc_].min(), x[cc_].max(), x[cc_].mean(), x[cc_].std(), x[cc_].median(), x[cc_].skew(), x[cc_].kurtosis()])\n\n    stats.append(item)","metadata":{"execution":{"iopub.status.busy":"2022-07-12T01:22:37.765046Z","iopub.execute_input":"2022-07-12T01:22:37.765469Z","iopub.status.idle":"2022-07-12T02:36:09.871839Z","shell.execute_reply.started":"2022-07-12T01:22:37.765435Z","shell.execute_reply":"2022-07-12T02:36:09.869161Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stats_cols = ['feat']\nstats_cols.extend([f'last_{c}' for c in ['feat', 'min', 'max', 'mean', 'std', 'median', 'skew', 'kurtosis']])\nstats_cols.extend([f'mean_{c}' for c in ['feat', 'min', 'max', 'mean', 'std', 'median', 'skew', 'kurtosis']])\n\nstats_df = pd.DataFrame(stats, columns= stats_cols )\n\n\nstats_df.to_csv('train_agg_stats_rev2.csv', sep='|', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-12T02:43:49.072699Z","iopub.execute_input":"2022-07-12T02:43:49.073168Z","iopub.status.idle":"2022-07-12T02:43:49.095896Z","shell.execute_reply.started":"2022-07-12T02:43:49.073131Z","shell.execute_reply":"2022-07-12T02:43:49.095026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_feats.index(c), len(cat_feats)","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:03:39.713855Z","iopub.execute_input":"2022-07-12T03:03:39.714491Z","iopub.status.idle":"2022-07-12T03:03:39.722222Z","shell.execute_reply.started":"2022-07-12T03:03:39.714409Z","shell.execute_reply":"2022-07-12T03:03:39.721302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nfor c in cat_feats:\n    df = dd.read_parquet(train_file, columns=['customer_ID', c], engine='pyarrow')\n    x0 = df.compute().groupby(\"customer_ID\")[c].value_counts().to_frame()\n    x0=x0.unstack().fillna(0)\n    x0.columns = [f'{c0}={c1}' for c0, c1 in x0.columns]\n    \n    df = dd.read_parquet(train_file, columns=['customer_ID', 'S_2',c], engine='pyarrow')\n    x1 = df.compute().sort_values(by='S_2', ascending=True).groupby(\"customer_ID\").agg({c: ['nunique','last']})\n    x1.columns = [f'{c1}|{c2}' for c1, c2 in x1.columns]\n    \n    x = x0.merge(x1, left_index=True, right_index=True, how='left')\n\n    pq.write_table(pa.Table.from_pandas(x), f'{c}.parquet', compression = 'GZIP')\n    agg_files.append(f'{c}.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:07:30.070411Z","iopub.execute_input":"2022-07-12T03:07:30.070854Z","iopub.status.idle":"2022-07-12T03:13:25.552415Z","shell.execute_reply.started":"2022-07-12T03:07:30.070818Z","shell.execute_reply":"2022-07-12T03:13:25.551210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def cal_days(v):\n    m0 = v['S_2=min']\n    m1 = v['S_2=max']\n    if m1 is np.nan:\n        m1 = m0\n    \n    return (datetime.strptime(m1, '%Y-%m-%d') - datetime.strptime(m0, '%Y-%m-%d')).days\n\n        ","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:14:20.687840Z","iopub.execute_input":"2022-07-12T03:14:20.688255Z","iopub.status.idle":"2022-07-12T03:14:20.694573Z","shell.execute_reply.started":"2022-07-12T03:14:20.688221Z","shell.execute_reply":"2022-07-12T03:14:20.693525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nfor c in ['S_2']:\n    df = dd.read_parquet(train_file, columns=['customer_ID', c], engine='pyarrow')\n    x = df.compute().groupby(\"customer_ID\").agg({c: ['min', 'max', 'count']})\n    x.columns = [f'{c1}={c2}' for c1, c2 in x.columns]   \n    \n    days = []\n    for _, row in x.iterrows():\n        days.append(cal_days(row))\n    x['days']=days\n    \n    pq.write_table(pa.Table.from_pandas(x), f'{c}.parquet', compression = 'GZIP')\n    agg_files.append(f'{c}.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:20:51.942085Z","iopub.execute_input":"2022-07-12T03:20:51.943032Z","iopub.status.idle":"2022-07-12T03:22:57.812725Z","shell.execute_reply.started":"2022-07-12T03:20:51.942995Z","shell.execute_reply":"2022-07-12T03:22:57.811633Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"raw","source":"%%time\ndf = dd.read_parquet(train_file, columns=['customer_ID'], engine='pyarrow')\nx = df.compute().groupby(\"customer_ID\").size().to_frame()\n\npq.write_table(pa.Table.from_pandas(x), f'customer_ID.parquet', compression = 'GZIP')\nagg_files.append(f'customer_ID.parquet')","metadata":{}},{"cell_type":"markdown","source":"## combine all files","metadata":{}},{"cell_type":"code","source":"len(agg_files), agg_files[:1], len(set(agg_files))","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:22:57.814637Z","iopub.execute_input":"2022-07-12T03:22:57.815118Z","iopub.status.idle":"2022-07-12T03:22:57.821966Z","shell.execute_reply.started":"2022-07-12T03:22:57.815086Z","shell.execute_reply":"2022-07-12T03:22:57.820952Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg_files = list(set(agg_files))","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:22:57.823302Z","iopub.execute_input":"2022-07-12T03:22:57.823881Z","iopub.status.idle":"2022-07-12T03:22:57.834108Z","shell.execute_reply.started":"2022-07-12T03:22:57.823850Z","shell.execute_reply":"2022-07-12T03:22:57.833041Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.read_csv('/kaggle/input/amex-default-prediction/train_labels.csv')\n# df = df[['customer_ID']].copy(deep=True)\n\nfor i, file in enumerate(agg_files):\n    df_ = pd.read_parquet(file).reset_index()\n    df = df.merge(df_,on=['customer_ID'], how='left')\n        \n    del df_\n    gc.collect()\n        \nprint(i)\npq.write_table(pa.Table.from_pandas(df), f'agg_train_all_rev2.parquet', compression = 'GZIP')\ndel df\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:24:03.263759Z","iopub.execute_input":"2022-07-12T03:24:03.264173Z","iopub.status.idle":"2022-07-12T03:32:51.826455Z","shell.execute_reply.started":"2022-07-12T03:24:03.264137Z","shell.execute_reply":"2022-07-12T03:32:51.825311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(agg_files)","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:35:52.334345Z","iopub.execute_input":"2022-07-12T03:35:52.334998Z","iopub.status.idle":"2022-07-12T03:35:52.344887Z","shell.execute_reply.started":"2022-07-12T03:35:52.334944Z","shell.execute_reply":"2022-07-12T03:35:52.344003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for file in agg_files:\n    Path(file).unlink()","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:35:53.462435Z","iopub.execute_input":"2022-07-12T03:35:53.462802Z","iopub.status.idle":"2022-07-12T03:35:53.801794Z","shell.execute_reply.started":"2022-07-12T03:35:53.462772Z","shell.execute_reply":"2022-07-12T03:35:53.800718Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"files = next(os.walk('.'))[2]\nfiles","metadata":{"execution":{"iopub.status.busy":"2022-07-12T03:35:55.902736Z","iopub.execute_input":"2022-07-12T03:35:55.903420Z","iopub.status.idle":"2022-07-12T03:35:55.911730Z","shell.execute_reply.started":"2022-07-12T03:35:55.903385Z","shell.execute_reply":"2022-07-12T03:35:55.910462Z"},"trusted":true},"execution_count":null,"outputs":[]}]}