{"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 pandas as pd\n\ndef read_data(cols):\n    \n    print('Reading data...')\n    \n    df = pd.read_parquet('../input/amex-data-integer-dtypes-parquet-format/train.parquet', columns=cols)\n    \n    # simplify cus_id\n    unique_cus_ids = df.customer_ID.unique()\n    assignment     = dict(zip(unique_cus_ids, list(range(len(unique_cus_ids)))))\n    df.customer_ID = df.customer_ID.apply(lambda x: assignment[x]).astype('int32')\n    \n    print('shape of data:', df.shape)\n    \n    return df","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-04T22:16:36.063473Z","iopub.execute_input":"2022-08-04T22:16:36.064077Z","iopub.status.idle":"2022-08-04T22:16:36.072638Z","shell.execute_reply.started":"2022-08-04T22:16:36.064031Z","shell.execute_reply":"2022-08-04T22:16:36.071159Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# How to extract ALL information from categorial features?","metadata":{}},{"cell_type":"markdown","source":"What are categorial features? Well, features, which contain numbers, which together do not represent some distribution but beloinging to some group. \n\nA categorial features contains categories like - 1,2,3,4,5. Imagine the default rate among those categories is distributed 0.1, 0.2, 0.3, 0.4, 0,5 respectivly. In this case you could treat this feature actually as a numerical feature. Tree algorithms will be able to find a good cutoff to seperate one group from the other. \n\nBut what if the default rate distribution is 0.5, 0.1, 0.4, 0.3, 0.2 respectivly? - All algorithms I know are not able to make sense of of auch a feature by itself. You need to tell the algorihtm to handel such a feature. XGBoost, LGBM and CatBoost are all able to handle such features, if you indicate them as categorial. \n\nHowever, consider our situation. In most notebboks I saw people only using 'last' to aggregate the information given in the data. But the last categorial state of a customer ignores the history of the customer with respect to the feature. How can you include the information beyond last? \n\nThe answer is take the mean of transformed cat-variables by corresponding default rates.","metadata":{}},{"cell_type":"markdown","source":"After my EDA, I have found the following vfeatures to be categorial - besides the one provided in the competion describtion.","metadata":{}},{"cell_type":"code","source":"cat_features = ['B_30', 'B_38', 'D_117', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68', 'B_33', 'D_92', 'D_103', 'D_114', 'D_116', 'D_129', 'D_139', 'D_140', 'D_143', 'D_51', 'B_16', 'B_22', 'D_72', 'D_78', 'D_79', 'R_9', 'D_82', 'D_107', 'D_122', 'D_125']","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:16:41.459817Z","iopub.execute_input":"2022-08-04T22:16:41.460240Z","iopub.status.idle":"2022-08-04T22:16:41.466671Z","shell.execute_reply.started":"2022-08-04T22:16:41.460205Z","shell.execute_reply":"2022-08-04T22:16:41.465587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# read only categorial  features\ndata  = read_data(['customer_ID'] + cat_features)\n# read targets\ntarget = pd.read_csv('../input/amex-default-prediction/train_labels.csv', usecols=['target'])","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:16:43.575061Z","iopub.execute_input":"2022-08-04T22:16:43.575993Z","iopub.status.idle":"2022-08-04T22:16:51.102084Z","shell.execute_reply.started":"2022-08-04T22:16:43.575951Z","shell.execute_reply":"2022-08-04T22:16:51.100851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can see, that indeed those variables are discrete and if you do a little further analysis, you will find that the discrete numbers correspond to different default rates as in the example above.","metadata":{}},{"cell_type":"code","source":"for cat in cat_features:\n    \n    print(cat, data[cat].unique())","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:16:51.104087Z","iopub.execute_input":"2022-08-04T22:16:51.104442Z","iopub.status.idle":"2022-08-04T22:16:52.131079Z","shell.execute_reply.started":"2022-08-04T22:16:51.104411Z","shell.execute_reply":"2022-08-04T22:16:52.129827Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We need to:\n\n    (1) for every feature\n    \n        (1.1) calculate values related to the default rate of the given category states\n\n        (1.2) assign a those values from (1.1) to the corresponding categories\n        \n    (2) Take the mean of each customer for each of the categorial features","metadata":{}},{"cell_type":"code","source":"for cat in cat_features:\n\n    for cat_state in data[cat].unique():\n        \n        # Default rate asociated to categorial state\n        rep = target.loc[data.loc[data[cat] == cat_state, 'customer_ID'].unique()].mean().values[0]\n        # Replace categorial state by its default rate\n        data.loc[data[cat] == cat_state, cat] = rep","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:16:55.447505Z","iopub.execute_input":"2022-08-04T22:16:55.448019Z","iopub.status.idle":"2022-08-04T22:17:17.626895Z","shell.execute_reply.started":"2022-08-04T22:16:55.447973Z","shell.execute_reply":"2022-08-04T22:17:17.625832Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Instead category state we have now default rate asscociated with the given state! cool!","metadata":{}},{"cell_type":"code","source":"data.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:17:17.629253Z","iopub.execute_input":"2022-08-04T22:17:17.630247Z","iopub.status.idle":"2022-08-04T22:17:17.670540Z","shell.execute_reply.started":"2022-08-04T22:17:17.630198Z","shell.execute_reply":"2022-08-04T22:17:17.669108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now let's aggregate the information","metadata":{}},{"cell_type":"code","source":"aggs = data.groupby('customer_ID').agg('mean')\naggs.columns = [x + '_cat_mean' for x in aggs.columns] ","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:17:21.851140Z","iopub.execute_input":"2022-08-04T22:17:21.851655Z","iopub.status.idle":"2022-08-04T22:17:25.617233Z","shell.execute_reply.started":"2022-08-04T22:17:21.851612Z","shell.execute_reply":"2022-08-04T22:17:25.615962Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There you go! amazing new features, which contain information on how much risk a customer had over time with respect to a given categorial feature!","metadata":{}},{"cell_type":"code","source":"aggs.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:17:25.619277Z","iopub.execute_input":"2022-08-04T22:17:25.619627Z","iopub.status.idle":"2022-08-04T22:17:25.648244Z","shell.execute_reply.started":"2022-08-04T22:17:25.619596Z","shell.execute_reply":"2022-08-04T22:17:25.647118Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"One more thing. Those features represent averaged rates. Thefore you can even sum all those features up ;)","metadata":{}},{"cell_type":"code","source":"aggs['cat_sum'] = aggs.sum(axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-04T22:17:30.129223Z","iopub.execute_input":"2022-08-04T22:17:30.129629Z","iopub.status.idle":"2022-08-04T22:17:30.191773Z","shell.execute_reply.started":"2022-08-04T22:17:30.129598Z","shell.execute_reply":"2022-08-04T22:17:30.189979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"'cat_sum' scores always very high in my permutation importance :)","metadata":{}},{"cell_type":"markdown","source":"A similar approach for binary features can be found in my previous notebook! <br>\nhttps://www.kaggle.com/code/gzguevara/compute-new-features-based-on-risk-variables","metadata":{}}]}