{"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":"# Feature Engineering: Transform Functions\n\nAnother powerfull tool offered by pandas is transformation of feature. The goal of this notebook is to show what transform approaches can offer on top of agregation functions.\n\n**This Notebook is part of a serie built for the AMEX competition:**\n- [Base Feature engineering](https://www.kaggle.com/code/lucasmorin/amex-feature-engineering-base)\n- [Baseline lgbm](https://www.kaggle.com/code/lucasmorin/amex-lgbm-features-eng)\n- [Feature Engineering 2: aggregation function](https://www.kaggle.com/code/lucasmorin/amex-feature-engineering-2-aggreg-functions)\n- [Feature Engineering 3: transformation function](https://www.kaggle.com/code/lucasmorin/amex-feature-engineering-3-transform-functions)\n\n**With associated Data Sets:**\n- [Base Feature engineering](https://www.kaggle.com/datasets/lucasmorin/amex-base-fe)\n- [Feature Engineering 2 - aggregation function](https://www.kaggle.com/datasets/lucasmorin/amex-fe2)\n- [Feature Engineering 3 - transform function](https://www.kaggle.com/datasets/lucasmorin/amex-fe3)\n\n\n**Please make sure to upvote everything you use / find interesting / usefull**","metadata":{"papermill":{"duration":0.016481,"end_time":"2021-09-05T16:32:48.105499","exception":false,"start_time":"2021-09-05T16:32:48.089018","status":"completed"},"tags":[]}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport warnings","metadata":{"papermill":{"duration":1.11019,"end_time":"2021-09-05T16:32:49.233491","exception":false,"start_time":"2021-09-05T16:32:48.123301","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-07-29T14:45:35.800047Z","iopub.execute_input":"2022-07-29T14:45:35.800525Z","iopub.status.idle":"2022-07-29T14:45:35.829581Z","shell.execute_reply.started":"2022-07-29T14:45:35.800423Z","shell.execute_reply":"2022-07-29T14:45:35.828602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Pandas transform\n\nTransform allows for direct transformation of values (mutation). It is generally usefull to create new features, such as ranks and perform imputation work, such as missing value / target imputation. The general idea is to use the pandas groupby on a categorical features and perform the transformation. This allows to build features that depends on multiple rows. ","metadata":{}},{"cell_type":"markdown","source":"# Pandas base function\n\nFor a base exemple we can compare to aggregating function:","metadata":{}},{"cell_type":"code","source":"df = pd.DataFrame({'group':[1,1,2,2],'values':[4,1,1,2],'values2':[0,1,1,2]})\ndf","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:45:35.966420Z","iopub.execute_input":"2022-07-29T14:45:35.966808Z","iopub.status.idle":"2022-07-29T14:45:35.992449Z","shell.execute_reply.started":"2022-07-29T14:45:35.966774Z","shell.execute_reply":"2022-07-29T14:45:35.991439Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.groupby('group').mean()","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:45:36.092140Z","iopub.execute_input":"2022-07-29T14:45:36.092639Z","iopub.status.idle":"2022-07-29T14:45:36.116334Z","shell.execute_reply.started":"2022-07-29T14:45:36.092597Z","shell.execute_reply":"2022-07-29T14:45:36.115105Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.groupby('group').transform('mean')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:45:36.198770Z","iopub.execute_input":"2022-07-29T14:45:36.199148Z","iopub.status.idle":"2022-07-29T14:45:36.216511Z","shell.execute_reply.started":"2022-07-29T14:45:36.199118Z","shell.execute_reply":"2022-07-29T14:45:36.215536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We get the same value, the main difference is that we get as much lines as the initial df. The aggregation could be performed with a groupby/add then mapped, but transform is more practical.\nThis practicality helps realise different stuff easily.","metadata":{}},{"cell_type":"markdown","source":"# Getting data","metadata":{}},{"cell_type":"markdown","source":"Public and private are split to avoid leakage.\n(Note: This is a best practice for ML, you might want a bit of leakage to climb the LB).","metadata":{}},{"cell_type":"code","source":"%%time\n# separating public and private\n\ntest = pd.read_parquet('../input/amex-data-integer-dtypes-parquet-format/test.parquet')\n\ndate_max = pd.to_datetime(test[['customer_ID','S_2']].drop_duplicates(subset=['customer_ID'], keep='last').set_index('customer_ID').S_2)\ndate_max.hist();\nID_Public = date_max[date_max<'2019-07-01'].index.to_list()\nID_Private = date_max[~(date_max<'2019-07-01')].index.to_list()\n\ndel test","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:45:36.303509Z","iopub.execute_input":"2022-07-29T14:45:36.304147Z","iopub.status.idle":"2022-07-29T14:46:21.324284Z","shell.execute_reply.started":"2022-07-29T14:45:36.304108Z","shell.execute_reply":"2022-07-29T14:46:21.322933Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\ntrain = pd.read_pickle('../input/amex-feature-engineering-base/train_data_agg.pkl')\ntest = pd.read_pickle('../input/amex-feature-engineering-base/test_data_agg.pkl')\n\npublic = test[test.index.isin(ID_Public)]\nprivate = test[test.index.isin(ID_Private)]\n\ndel test","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:46:21.326635Z","iopub.execute_input":"2022-07-29T14:46:21.327008Z","iopub.status.idle":"2022-07-29T14:47:03.910301Z","shell.execute_reply.started":"2022-07-29T14:46:21.326977Z","shell.execute_reply":"2022-07-29T14:47:03.909018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Main Features","metadata":{}},{"cell_type":"code","source":"main_features = [f'B_{i}' for i in [11,14,17]]+['D_39','D_131']+[f'S_{i}' for i in [16,23]]+['P_2','P_3']\ncat_features = ['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']\n\nmain_features_last = [f+'_last' for f in main_features]\ncat_features_last = [f+'_last' for f in cat_features]","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:03.912105Z","iopub.execute_input":"2022-07-29T14:47:03.912732Z","iopub.status.idle":"2022-07-29T14:47:03.919453Z","shell.execute_reply.started":"2022-07-29T14:47:03.912684Z","shell.execute_reply":"2022-07-29T14:47:03.918642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = train[main_features_last+cat_features_last]\npublic = public[main_features_last+cat_features_last]\nprivate = private[main_features_last+cat_features_last]","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:03.921712Z","iopub.execute_input":"2022-07-29T14:47:03.922313Z","iopub.status.idle":"2022-07-29T14:47:04.927975Z","shell.execute_reply.started":"2022-07-29T14:47:03.922279Z","shell.execute_reply":"2022-07-29T14:47:04.926897Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import gc\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:04.929194Z","iopub.execute_input":"2022-07-29T14:47:04.929501Z","iopub.status.idle":"2022-07-29T14:47:05.125225Z","shell.execute_reply.started":"2022-07-29T14:47:04.929474Z","shell.execute_reply":"2022-07-29T14:47:05.124031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Mean by categories","metadata":{}},{"cell_type":"code","source":"def agg_mean_by_cat(df):\n    df_list = []\n    for c in cat_features_last:\n        df_agg = df[main_features_last].groupby(df[c]).transform('mean')\n        df_agg.columns = [f+'_mean_by_'+c for f in df_agg.columns]\n        df_list.append(df_agg.astype('float16'))\n    return pd.concat(df_list,axis=1).astype('float16')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:05.126767Z","iopub.execute_input":"2022-07-29T14:47:05.127835Z","iopub.status.idle":"2022-07-29T14:47:05.137212Z","shell.execute_reply.started":"2022-07-29T14:47:05.127790Z","shell.execute_reply":"2022-07-29T14:47:05.136254Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Global (pct) rank ","metadata":{}},{"cell_type":"code","source":"def agg_global_rank(df):\n    df_rank = df[main_features_last].transform('rank')\n    df_rank.columns = [s+'_global_rank' for s in df_rank.columns]\n    return (df_rank/len(df)).astype('float16')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:48:15.893683Z","iopub.execute_input":"2022-07-29T14:48:15.894852Z","iopub.status.idle":"2022-07-29T14:48:15.901013Z","shell.execute_reply.started":"2022-07-29T14:48:15.894806Z","shell.execute_reply":"2022-07-29T14:48:15.899465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# (Pct) Rank by categories","metadata":{}},{"cell_type":"code","source":"def agg_pct_rank_by_cat(df):\n    df_list = []\n    for c in cat_features_last:\n        df_agg = df[main_features_last].groupby(df[c]).transform('rank')/df[main_features_last].groupby(df[c]).transform('count')\n        df_agg.columns = [f+'_pct_rank_by_'+c for f in df_agg.columns]\n        df_list.append(df_agg.astype('float16'))\n\n    return pd.concat(df_list,axis=1).astype('float16')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:05.152044Z","iopub.execute_input":"2022-07-29T14:47:05.153262Z","iopub.status.idle":"2022-07-29T14:47:05.162844Z","shell.execute_reply.started":"2022-07-29T14:47:05.153216Z","shell.execute_reply":"2022-07-29T14:47:05.162040Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Standardize by categories\n\nWe can use lambda functions for agregation. This allow to perform somewhat complex operations easily.","metadata":{}},{"cell_type":"code","source":"def agg_standardize_by_cat(df):\n    df_list = []\n    for c in cat_features_last:\n        df_agg = df[main_features_last].groupby(df[c]).transform(lambda x: (x - np.nanmean(x)) / np.nanstd(x))\n        df_agg.columns = [f+'_standardized_by_'+c for f in df_agg.columns]\n        df_list.append(df_agg.astype('float16'))\n\n    return pd.concat(df_list,axis=1).astype('float16')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:51:26.346864Z","iopub.execute_input":"2022-07-29T14:51:26.347564Z","iopub.status.idle":"2022-07-29T14:51:26.353859Z","shell.execute_reply.started":"2022-07-29T14:51:26.347521Z","shell.execute_reply":"2022-07-29T14:51:26.352954Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Target by categories\n\nThis allows for target encoding. However the application to tests require saving the encoded target. So it is in fact better to use a groupy+agg then map the result. ","metadata":{}},{"cell_type":"code","source":"train['target'] = pd.read_csv('../input/amex-default-prediction/train_labels.csv').target.values","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:05.183559Z","iopub.execute_input":"2022-07-29T14:47:05.184603Z","iopub.status.idle":"2022-07-29T14:47:06.223242Z","shell.execute_reply.started":"2022-07-29T14:47:05.184550Z","shell.execute_reply":"2022-07-29T14:47:06.222311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_list_train = []\ndf_list_public = []\ndf_list_private = []\n\nfor c in cat_features_last:\n    to_impute = train['target'].groupby(train[c]).agg('mean')\n    df_list_train.append(train[c].map(to_impute).astype('float16'))\n    df_list_public.append(public[c].map(to_impute).astype('float16'))\n    df_list_private.append(private[c].map(to_impute).astype('float16'))\n\ndef save_target(df_list,name):\n    df_agg = pd.concat(df_list,axis=1).astype('float16')\n    df_agg.columns = ['target_mean_by_'+c for f in df_agg.columns]\n    df_agg.to_pickle(name+'_target_impute.p')\n\nsave_target(df_list_train,'train')\nsave_target(df_list_public,'public')\nsave_target(df_list_private,'private')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:06.224459Z","iopub.execute_input":"2022-07-29T14:47:06.224772Z","iopub.status.idle":"2022-07-29T14:47:09.100288Z","shell.execute_reply.started":"2022-07-29T14:47:06.224744Z","shell.execute_reply":"2022-07-29T14:47:09.099332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Prepare all data","metadata":{}},{"cell_type":"code","source":"def prepare_df(df, name):\n    df1 = agg_mean_by_cat(df)\n    df1.to_pickle(name+'_mean_by_cat.p')\n    if name=='train':\n        print('mean_by_cat')\n        display(df1)\n        display(df1.describe())\n    del df1\n    df2 = agg_global_rank(df)\n    df2.to_pickle(name+'_rank.p')\n    if name=='train':\n        print('global rank')\n        display(df2)\n        display(df2.describe())\n    del df2\n    df3 = agg_pct_rank_by_cat(df)\n    df3.to_pickle(name+'_rank_by_cat.p')\n    if name=='train':\n        print('rank by cat')\n        display(df3)\n        display(df3.describe())\n    del df3\n    df4 = agg_standardize_by_cat(df)\n    df4.to_pickle(name+'_standardize_by_cat.p')\n    if name=='train':\n        print('standardize by cat')\n        display(df4)\n        display(df4.describe())\n    del df4","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:09.101612Z","iopub.execute_input":"2022-07-29T14:47:09.102013Z","iopub.status.idle":"2022-07-29T14:47:09.110336Z","shell.execute_reply.started":"2022-07-29T14:47:09.101983Z","shell.execute_reply":"2022-07-29T14:47:09.109180Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"prepare_df(train, 'train')\ndel train\nprepare_df(public, 'public')\ndel public\nprepare_df(private, 'private')\ndel private","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:11.522914Z","iopub.status.idle":"2022-07-29T14:47:11.523453Z","shell.execute_reply.started":"2022-07-29T14:47:11.523168Z","shell.execute_reply":"2022-07-29T14:47:11.523193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for c in ['rank_by_cat','standardize_by_cat','mean_by_cat','rank','target_impute']:\n    \n    public_name = 'public_'+c+'.p'\n    private_name = 'private_'+c+'.p'\n    \n    test = pd.concat([pd.read_pickle(public_name),pd.read_pickle(private_name)])\n    test.to_pickle('test_'+c+'.p')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T14:47:11.526155Z","iopub.status.idle":"2022-07-29T14:47:11.526965Z","shell.execute_reply.started":"2022-07-29T14:47:11.526657Z","shell.execute_reply":"2022-07-29T14:47:11.526685Z"},"trusted":true},"execution_count":null,"outputs":[]}]}