{"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 numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport os\nimport gc\nimport pickle\n\n\nimport matplotlib.pyplot as plt","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-09T15:30:37.052339Z","iopub.execute_input":"2022-08-09T15:30:37.052958Z","iopub.status.idle":"2022-08-09T15:30:37.083990Z","shell.execute_reply.started":"2022-08-09T15:30:37.052843Z","shell.execute_reply":"2022-08-09T15:30:37.082891Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ntrain_df = pd.read_pickle(\"../input/amex-tabular-train-data-v3/train_data_agg.pkl\")\ntrain_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:30:37.086603Z","iopub.execute_input":"2022-08-09T15:30:37.087555Z","iopub.status.idle":"2022-08-09T15:30:56.998260Z","shell.execute_reply.started":"2022-08-09T15:30:37.087495Z","shell.execute_reply":"2022-08-09T15:30:56.997192Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_features = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\nbin_features = ['B_31', 'D_87']\nnumeric_features = [colname for colname in train_df.columns if colname not in cat_features+bin_features+['customer_ID', 'S_2', 'target']]\n\nprint(len(numeric_features))","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:30:57.000286Z","iopub.execute_input":"2022-08-09T15:30:57.000888Z","iopub.status.idle":"2022-08-09T15:30:57.009134Z","shell.execute_reply.started":"2022-08-09T15:30:57.000851Z","shell.execute_reply":"2022-08-09T15:30:57.007876Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def divide_int(s, v):\n    s = s.apply(lambda x: -1 if x==-1 else x/v)\n    s = s.astype(np.float32)\n    return s","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:30:57.010779Z","iopub.execute_input":"2022-08-09T15:30:57.011207Z","iopub.status.idle":"2022-08-09T15:30:57.029660Z","shell.execute_reply.started":"2022-08-09T15:30:57.011170Z","shell.execute_reply":"2022-08-09T15:30:57.028228Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"div_map={\n    'B_4': 78,\n    'B_16':12,\n    'B_20': 17,\n    'B_22': 2,\n    \n    'D_39': 34,\n    'D_44': 8,\n    'D_49': 71,\n    'D_51': 3,\n    \n    'D_59': 48,\n    'D_65': 38,\n    'D_70': 4,\n    'D_72': 3,\n    \n    \n    'D_74':4,\n    'D_75':15,\n    'D_78':2,\n    'D_79':2,\n    'D_80':5,\n    \n    'D_82': 2,\n    'D_84': 2,\n    'D_89': 9,\n    'D_91': 2,\n    \n    'D_106': 23,\n    'D_107': 3,\n    'D_111': 2,\n    'D_113': 5,\n    \n    'D_122': 7,\n    'D_124': 22,\n    'D_136': 4,\n    'D_138': 2,\n    \n    'D_145': 11,\n    'R_3': 10,\n    'R_5': 2,\n    'R_9': 6,\n    \n    'R_11': 2,\n    'R_13': 31,\n    'R_16': 2,\n    'R_17': 35,\n    'R_18': 31,\n    \n    'R_26': 28,\n    'S_11': 25,\n    'S_15': 10,\n    \n    'B_19': 100,\n    'S_13': 1034,\n    'S_8': 3166\n}","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:30:57.035122Z","iopub.execute_input":"2022-08-09T15:30:57.035533Z","iopub.status.idle":"2022-08-09T15:30:57.053658Z","shell.execute_reply.started":"2022-08-09T15:30:57.035499Z","shell.execute_reply":"2022-08-09T15:30:57.052453Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_df = []\nfor colname in train_df.columns:\n    colprefix = '_'.join(colname.split(\"_\")[:2])\n    flag=True\n    for cat_featname in cat_features+bin_features:\n        if colprefix == cat_featname:\n            flag=False\n            break\n    \n    if not flag:\n        continue\n    \n    s = train_df[colname]\n    na_percent = len(s[s.isna()])/len(s)\n    na_df.append({\n        'colname': colname,\n        'na_percent': na_percent\n    })\nna_df = pd.DataFrame.from_dict(na_df)\nna_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:30:57.055130Z","iopub.execute_input":"2022-08-09T15:30:57.055500Z","iopub.status.idle":"2022-08-09T15:30:58.822021Z","shell.execute_reply.started":"2022-08-09T15:30:57.055467Z","shell.execute_reply":"2022-08-09T15:30:58.820675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for k,v in div_map.items():\n    print(k+\"_mean\", \"before --> \", train_df[k+\"_mean\"].max())\n\n    train_df[k+\"_mean\"] = divide_int(train_df[k+\"_mean\"], v)\n    train_df[k+\"_std\"] = divide_int(train_df[k+\"_std\"], v)\n    train_df[k+\"_last\"] = divide_int(train_df[k+\"_last\"], v)\n    train_df[k+\"_min\"] = divide_int(train_df[k+\"_min\"], v)\n    train_df[k+\"_max\"] = divide_int(train_df[k+\"_max\"], v)\n    train_df[k+\"_lag1\"] = divide_int(train_df[k+\"_lag1\"], v)\n    train_df[k+\"_lag_mean\"] = divide_int(train_df[k+\"_lag_mean\"], v)\n\n    print(k+\"_mean\", \"after --> \", train_df[k+\"_mean\"].max())","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:30:58.823691Z","iopub.execute_input":"2022-08-09T15:30:58.824075Z","iopub.status.idle":"2022-08-09T15:31:51.611656Z","shell.execute_reply.started":"2022-08-09T15:30:58.824042Z","shell.execute_reply":"2022-08-09T15:31:51.610558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stat_df=[]\nfor colname in train_df.columns:\n    colprefix = '_'.join(colname.split(\"_\")[:2])\n    flag=True\n    for cat_featname in cat_features+bin_features:\n        if colprefix == cat_featname:\n            flag=False\n            break\n    if not flag:\n        continue\n    s = train_df[colname]\n    s = s[s.isna()==False]\n    stat_df.append({\n        'colname': colname,\n        'min_value': s.min(),\n        'median_value': s.median(),\n        'q01': np.quantile(s, .01),\n        \n        'q10': np.quantile(s, .1),\n        'q90': np.quantile(s, .90),\n        'q95': np.quantile(s, .95),\n        'q99': np.quantile(s, .99),\n        'max_value': s.max()\n    })\n\nstat_df = pd.DataFrame.from_dict(stat_df)\nstat_df = stat_df.merge(na_df)\nstat_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:39:29.121075Z","iopub.execute_input":"2022-08-09T15:39:29.121493Z","iopub.status.idle":"2022-08-09T15:40:07.738879Z","shell.execute_reply.started":"2022-08-09T15:39:29.121457Z","shell.execute_reply":"2022-08-09T15:40:07.737253Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stat_df['r1'] = stat_df['q95'].div(stat_df['q90'])\nstat_df['r2'] = stat_df['q99'].div(stat_df['q95'])\nstat_df['r3'] = stat_df['max_value'].div(stat_df['q99'])\n\nstat_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:40:16.592685Z","iopub.execute_input":"2022-08-09T15:40:16.593122Z","iopub.status.idle":"2022-08-09T15:40:16.618272Z","shell.execute_reply.started":"2022-08-09T15:40:16.593077Z","shell.execute_reply":"2022-08-09T15:40:16.616606Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1 = stat_df[(stat_df.r1!=np.inf) &\n              (stat_df.r2!=np.inf) &\n              (stat_df.r1>0) & \n              (stat_df.r2>0) &\n              (stat_df.r2 >= 2+stat_df.r1)\n            ].copy()\n\ndf2 = stat_df[(stat_df.r2!=np.inf) &\n              (stat_df.r3!=np.inf) &\n              (stat_df.r2>0) & \n              (stat_df.r3>0) &\n              (stat_df.r3 >= 2+stat_df.r2) &\n              (stat_df.colname.isin(df1.colname)==False)\n            ].copy()\n\nprint(df1.shape, df2.shape)","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:40:18.232430Z","iopub.execute_input":"2022-08-09T15:40:18.233460Z","iopub.status.idle":"2022-08-09T15:40:18.247431Z","shell.execute_reply.started":"2022-08-09T15:40:18.233423Z","shell.execute_reply":"2022-08-09T15:40:18.246067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1['clip_min_value'] = df1.min_value - 2*np.abs(df1.q01)\ndf1['clip_max_value'] = 3*df1.q95\n\ndf2['clip_min_value'] = df2.min_value - 2*np.abs(df2.q01)\ndf2['clip_max_value'] = 3*df2.q99\n\ndf = pd.concat([df1, df2], axis=0)\nprint(df.shape)\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:40:20.040556Z","iopub.execute_input":"2022-08-09T15:40:20.040932Z","iopub.status.idle":"2022-08-09T15:40:20.072499Z","shell.execute_reply.started":"2022-08-09T15:40:20.040901Z","shell.execute_reply":"2022-08-09T15:40:20.071515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"remain_df = stat_df[(stat_df.colname.isin(df.colname)==False)].copy()\ndf3 = remain_df[(remain_df.r3>=10) & (remain_df.max_value>2)  & (remain_df.r3!=np.inf)].copy()\nprint(df3.shape)\n\ndf3.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:40:33.986679Z","iopub.execute_input":"2022-08-09T15:40:33.987103Z","iopub.status.idle":"2022-08-09T15:40:34.015386Z","shell.execute_reply.started":"2022-08-09T15:40:33.987068Z","shell.execute_reply":"2022-08-09T15:40:34.014178Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df3['clip_min_value'] = df3.min_value - 2*np.abs(df3.q01)\ndf3['clip_max_value'] = 3*df3.q99\n\ndf3[['min_value', 'q90', 'q95', 'q99', 'max_value', 'clip_min_value', 'clip_max_value']]","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:40:38.260044Z","iopub.execute_input":"2022-08-09T15:40:38.260461Z","iopub.status.idle":"2022-08-09T15:40:38.288382Z","shell.execute_reply.started":"2022-08-09T15:40:38.260417Z","shell.execute_reply":"2022-08-09T15:40:38.287482Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.concat([df, df3], axis=0)\ndf = df[['colname', 'clip_min_value', 'clip_max_value']].copy()\nprint(df.shape)\n\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:40:54.791824Z","iopub.execute_input":"2022-08-09T15:40:54.792213Z","iopub.status.idle":"2022-08-09T15:40:54.811101Z","shell.execute_reply.started":"2022-08-09T15:40:54.792180Z","shell.execute_reply":"2022-08-09T15:40:54.809886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:32:33.655017Z","iopub.execute_input":"2022-08-09T15:32:33.656377Z","iopub.status.idle":"2022-08-09T15:32:33.664306Z","shell.execute_reply.started":"2022-08-09T15:32:33.656322Z","shell.execute_reply":"2022-08-09T15:32:33.662980Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"clip_map = {}\nfor _, row in df.iterrows():\n    colname = row.colname\n    clip_min_value = row.clip_min_value\n    clip_max_value = row.clip_max_value\n    clip_map[colname]={}\n    \n    clip_map[colname]['min_value'] = clip_min_value\n    clip_map[colname]['max_value'] = clip_max_value","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:32:33.666264Z","iopub.execute_input":"2022-08-09T15:32:33.667068Z","iopub.status.idle":"2022-08-09T15:32:33.733265Z","shell.execute_reply.started":"2022-08-09T15:32:33.667012Z","shell.execute_reply":"2022-08-09T15:32:33.731153Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"with open(\"nn_clip_map.pkl\", 'wb') as file:\n    pickle.dump(clip_map, file)","metadata":{"execution":{"iopub.status.busy":"2022-08-09T15:32:33.735790Z","iopub.execute_input":"2022-08-09T15:32:33.737013Z","iopub.status.idle":"2022-08-09T15:32:33.743181Z","shell.execute_reply.started":"2022-08-09T15:32:33.736952Z","shell.execute_reply":"2022-08-09T15:32:33.741999Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}