{"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":"## Short summary of this notebook\n\n   * In this notebook, I'm using the dataset provided by RADDAR (https://www.kaggle.com/datasets/raddar/amex-data-integer-dtypes-parquet-format).\n    \n   * EDA with this large amount of data is very, very difficult to do when you have only 16GB of RAM available for each notebook. Even when using Dask to store a large dataset (or, better to say, a large amount of tasks related to a dataset), it is difficult to perform all the tasks in only one notebook. This way, each piece of information is in different VERSIONS of this notebook. Until this version, I think that the only interesting things I did were:\n    \n   * (A) In version 10, Pearson's correlation was calculated for the numeric (\"continuous\") variables. Since , for the \"continuous\" features, the cardinality is enormous, I don't think it makes sense to compare this variables with the int8 and int16 ones. So, in a future version, I'll calculate the Spearman correlation coefficient with all the \"low cardinality\" features, i.e. int8 and int16 features.\n    \n   * (B) In version 10, I made some intra-groups plots of correlation. The link to the original notebook from where I took the code is commented above the plots.\n    \n   * (C) In version 11, Spearman's correlation was calculated for the numeric (\"continuous\") variables. Although some folks already made some correlation analysis (see, for example : https://www.kaggle.com/competitions/amex-default-prediction/discussion/328885 and https://www.kaggle.com/competitions/amex-default-prediction/discussion/330710 ), I think that some non-linear analysis of correlation is essential. \n    \n   *  (D) In both versions 10 and 11, I listed the 50 most positive correlated pairs and the 50 most negative correlated pairs.\n    \n   * (E) In version 13 I made some plots of the 20 most (positive) correlated pairs, both linear and non-linear (there were some intersection between both, so we have less non-linear plots). The aim here is to recognize patterns and do some work to deanonimize features and do some feature engineering IN THE FUTURE, in the same way as the winners of IEEE Credit Card Fraud Competition. In version 14, I've tried to improve the aesthetics of the plots.\n   \n   * (F) In version 15 I made some the same plots for the 20 most negatively correlated pairs, both linear and non-linear.\n   \n   * (G) In version 16, I investigated some float type variables that maybe are ordinal/binary.\n   \n   * (H) In version 17, I made a Spearman's correlation study for the categorical/ordinal/binary variables.","metadata":{}},{"cell_type":"markdown","source":"## Part 1: Imports, reading data, type of column data, transforming S_2 and customer_ID","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)\nimport dask.dataframe as dd\nimport dask\nfrom time import time\n#import datetime\nimport seaborn as sns\nimport random\nimport gc #Coletor de lixo\nimport matplotlib.pyplot as plt\nimport matplotlib.style as mplstyle #https://matplotlib.org/stable/gallery/style_sheets/style_sheets_reference.html\nplt.rcParams['agg.path.chunksize'] = 20000\n%matplotlib inline\nmplstyle.use(['dark_background', 'ggplot']) #https://matplotlib.org/stable/users/explain/performance.html\ngc.enable()\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-06-23T15:25:12.808480Z","iopub.execute_input":"2022-06-23T15:25:12.809123Z","iopub.status.idle":"2022-06-23T15:25:14.623493Z","shell.execute_reply.started":"2022-06-23T15:25:12.809058Z","shell.execute_reply":"2022-06-23T15:25:14.622225Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#The data below came from here: https://www.kaggle.com/datasets/raddar/amex-data-integer-dtypes-parquet-format\npdf = dd.read_parquet('/kaggle/input/amex-data-integer-dtypes-parquet-format/train.parquet', split_row_groups = True)\ntest = dd.read_parquet('/kaggle/input/amex-data-integer-dtypes-parquet-format/test.parquet')\ntarget = dd.read_csv('/kaggle/input/amex-default-prediction/train_labels.csv')","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:14.629033Z","iopub.execute_input":"2022-06-23T15:25:14.629477Z","iopub.status.idle":"2022-06-23T15:25:14.904120Z","shell.execute_reply.started":"2022-06-23T15:25:14.629446Z","shell.execute_reply":"2022-06-23T15:25:14.903067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Columns dtypes\nfor col in pdf.columns:\n    print(f\"{col} \\t {pdf[col].dtype}\")","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:14.905425Z","iopub.execute_input":"2022-06-23T15:25:14.905775Z","iopub.status.idle":"2022-06-23T15:25:14.950218Z","shell.execute_reply.started":"2022-06-23T15:25:14.905743Z","shell.execute_reply":"2022-06-23T15:25:14.948944Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Last Month Dataset\n\n* All the correlation analysis was made, without loss of generality, for a fixed month (the last one of the training dataset). We don't have any reason to believe that the general correlation between features will change much between different months.","metadata":{}},{"cell_type":"code","source":"#Changing S_2 to datetime\npdf['S_2'] = dd.to_datetime(pdf['S_2'])","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:14.952835Z","iopub.execute_input":"2022-06-23T15:25:14.953331Z","iopub.status.idle":"2022-06-23T15:25:14.998209Z","shell.execute_reply.started":"2022-06-23T15:25:14.953283Z","shell.execute_reply":"2022-06-23T15:25:14.997083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Getting the month and year of the last month of the training dataset\nactual_date = pdf['S_2'].compute()\nmax_date = actual_date.max()\nmax_month = max_date.month\nmax_year = max_date.year\n\ndel actual_date\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:14.999639Z","iopub.execute_input":"2022-06-23T15:25:15.000374Z","iopub.status.idle":"2022-06-23T15:25:50.500211Z","shell.execute_reply.started":"2022-06-23T15:25:15.000329Z","shell.execute_reply":"2022-06-23T15:25:50.499320Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Taking only the last month of data\npdf = pdf[pdf.S_2 >= pd.to_datetime(f'{max_year}-{max_month}')]","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:50.501457Z","iopub.execute_input":"2022-06-23T15:25:50.502126Z","iopub.status.idle":"2022-06-23T15:25:50.511973Z","shell.execute_reply.started":"2022-06-23T15:25:50.502083Z","shell.execute_reply":"2022-06-23T15:25:50.510441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"{max_year}-{max_month}\")","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:50.513381Z","iopub.execute_input":"2022-06-23T15:25:50.513776Z","iopub.status.idle":"2022-06-23T15:25:50.529450Z","shell.execute_reply.started":"2022-06-23T15:25:50.513734Z","shell.execute_reply":"2022-06-23T15:25:50.528465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Separating numeric and categorical columns","metadata":{}},{"cell_type":"code","source":"features = list(pdf.columns)\nfor non_feature in ['customer_ID', 'S_2']:\n    features.remove(non_feature)\n\n#Note that we made a CHOICE in this notebook: since the features are anonymized, we decided to \n#treat the low-cardinality ones as categorical and the high-cardinality ones as numerical\ncat_features = [feature for feature in features if pdf[feature].dtype != 'float32']\ncat_features[:5]","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:50.531189Z","iopub.execute_input":"2022-06-23T15:25:50.531580Z","iopub.status.idle":"2022-06-23T15:25:50.575616Z","shell.execute_reply.started":"2022-06-23T15:25:50.531545Z","shell.execute_reply":"2022-06-23T15:25:50.574661Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_features = [col for col in features if col not in cat_features]\nnum_features[:5]","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:50.576765Z","iopub.execute_input":"2022-06-23T15:25:50.577202Z","iopub.status.idle":"2022-06-23T15:25:50.582692Z","shell.execute_reply.started":"2022-06-23T15:25:50.577171Z","shell.execute_reply":"2022-06-23T15:25:50.581981Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# REDUCE DTYPE FOR CUSTOMER. Taken from here: https://www.kaggle.com/code/cdeotte/xgboost-starter-0-793\nhex_to_int2 = lambda x: int(x[-16:], 16)\nfor df in [pdf,test,target]:\n    df['customer_ID'] = df['customer_ID'].apply(hex_to_int2, meta = (df['customer_ID'], 'i8'))\ntest['S_2'] = dd.to_datetime(test['S_2'])","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:25:50.583732Z","iopub.execute_input":"2022-06-23T15:25:50.584196Z","iopub.status.idle":"2022-06-23T15:25:50.705545Z","shell.execute_reply.started":"2022-06-23T15:25:50.584166Z","shell.execute_reply":"2022-06-23T15:25:50.704662Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Correlations - ordinal/categorical features\n\n* Disclaimer\n\nWe don't know which of the int columns has a scale that is at least ordinal. For some of them, the calculations in this section may not make sense at all. We think that, for this variables, there's a extremely low probability that a high Spearman's correlation will appear by chance - since they were encoded as integers.","metadata":{}},{"cell_type":"code","source":"#The general lines of code below were taken from here: https://seaborn.pydata.org/examples/many_pairwise_correlations.html\nsns.set_theme(style=\"white\")\ninicio = time()\nordinal_correlation = pdf[cat_features].compute().corr(method = 'spearman')\nfinal = time()\n\nprint(f\"Tempo para calcular a matriz de correlação, em segundos: {(final-inicio):.3f}s\")\n\n\n# Generate a mask for the upper triangle\nmask = np.triu(np.ones_like(ordinal_correlation, dtype=bool))","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:27:22.048534Z","iopub.execute_input":"2022-06-23T15:27:22.048990Z","iopub.status.idle":"2022-06-23T15:28:25.757607Z","shell.execute_reply.started":"2022-06-23T15:27:22.048953Z","shell.execute_reply":"2022-06-23T15:28:25.756273Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#The most correlated pairs, taken from here: https://www.kaggle.com/code/datark1/american-express-eda\nunstacked = ordinal_correlation.unstack()\nunstacked = unstacked.sort_values(ascending=False, kind=\"quicksort\").drop_duplicates()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:28:25.759728Z","iopub.execute_input":"2022-06-23T15:28:25.760841Z","iopub.status.idle":"2022-06-23T15:28:25.774510Z","shell.execute_reply.started":"2022-06-23T15:28:25.760784Z","shell.execute_reply":"2022-06-23T15:28:25.772783Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unstacked.head(50)","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:28:41.253887Z","iopub.execute_input":"2022-06-23T15:28:41.254318Z","iopub.status.idle":"2022-06-23T15:28:41.265489Z","shell.execute_reply.started":"2022-06-23T15:28:41.254287Z","shell.execute_reply":"2022-06-23T15:28:41.264685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unstacked.tail(50)","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:28:41.471379Z","iopub.execute_input":"2022-06-23T15:28:41.472493Z","iopub.status.idle":"2022-06-23T15:28:41.481179Z","shell.execute_reply.started":"2022-06-23T15:28:41.472450Z","shell.execute_reply":"2022-06-23T15:28:41.479962Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Part 1a: correlations between risk variables\n\nAll the code below (from parts 1a,1b,1c,1d and 1e) was taken from: https://www.kaggle.com/code/datark1/american-express-eda\n","metadata":{}},{"cell_type":"code","source":"cols_to_show = [c for c in pdf.columns if (c.startswith('R')) and c in cat_features]\n#corr=pdf[cols_to_show].compute().corr(method = 'spearman')\ncorr = ordinal_correlation.loc[cols_to_show, cols_to_show]\nmask=np.triu(np.ones_like(corr))[1:,:-1]\ncorr=corr.iloc[1:,:-1].copy()\n\nfig, ax = plt.subplots(figsize=(30,30))   \nsns.heatmap(corr, mask=mask, vmin=-1, vmax=1, center=0, annot=True, fmt='.2f', \n            cmap='coolwarm', annot_kws={'fontsize':10,'fontweight':'bold'}, cbar=False)\nax.tick_params(left=False,bottom=False)\nax.set_xticklabels(ax.get_xticklabels(), rotation=45, horizontalalignment='right',fontsize=12)\nax.set_yticklabels(ax.get_yticklabels(), fontsize=12)\nplt.title('Correlations between Risk Variables\\n', fontsize=16)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:32:32.912747Z","iopub.execute_input":"2022-06-23T15:32:32.913145Z","iopub.status.idle":"2022-06-23T15:32:34.552012Z","shell.execute_reply.started":"2022-06-23T15:32:32.913115Z","shell.execute_reply":"2022-06-23T15:32:34.550778Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Part 1b: correlation between spend variables","metadata":{}},{"cell_type":"code","source":"cols_to_show = [c for c in pdf.columns if (c.startswith('S')) and c in cat_features]\n#corr=pdf[cols_to_show].compute().corr(method = 'spearman')\ncorr = ordinal_correlation.loc[cols_to_show, cols_to_show]\nmask=np.triu(np.ones_like(corr))[1:,:-1]\ncorr=corr.iloc[1:,:-1].copy()\n\nfig, ax = plt.subplots(figsize=(15,15))   \nsns.heatmap(corr, mask=mask, vmin=-1, vmax=1, center=0, annot=True, fmt='.2f', \n            cmap='coolwarm', annot_kws={'fontsize':10,'fontweight':'bold'}, cbar=False)\nax.tick_params(left=False,bottom=False)\nax.set_xticklabels(ax.get_xticklabels(), rotation=45, horizontalalignment='right',fontsize=12)\nax.set_yticklabels(ax.get_yticklabels(), fontsize=12)\nplt.title('Correlations between Spend Variables\\n', fontsize=16)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:32:40.063066Z","iopub.execute_input":"2022-06-23T15:32:40.064181Z","iopub.status.idle":"2022-06-23T15:32:40.347354Z","shell.execute_reply.started":"2022-06-23T15:32:40.064131Z","shell.execute_reply":"2022-06-23T15:32:40.346273Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Part 1c: correlation between delinquency variables","metadata":{}},{"cell_type":"code","source":"cols_to_show = [c for c in pdf.columns if (c.startswith('D')) and c in cat_features]\n#corr=pdf[cols_to_show].compute().corr(method = 'spearman')\ncorr = ordinal_correlation.loc[cols_to_show, cols_to_show]\nmask=np.triu(np.ones_like(corr))[1:,:-1]\ncorr=corr.iloc[1:,:-1].copy()\n\nfig, ax = plt.subplots(figsize=(23,23))   \nsns.heatmap(corr, mask=mask, vmin=-1, vmax=1, center=0, annot=True, fmt='.2f', \n            cmap='coolwarm', annot_kws={'fontsize':10,'fontweight':'bold'}, cbar=False)\nax.tick_params(left=False,bottom=False)\nax.set_xticklabels(ax.get_xticklabels(), rotation=45, horizontalalignment='right',fontsize=12)\nax.set_yticklabels(ax.get_yticklabels(), fontsize=12)\nplt.title('Correlations between Delinquency Variables\\n', fontsize=16)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:35:25.888885Z","iopub.execute_input":"2022-06-23T15:35:25.889338Z","iopub.status.idle":"2022-06-23T15:35:33.233100Z","shell.execute_reply.started":"2022-06-23T15:35:25.889305Z","shell.execute_reply":"2022-06-23T15:35:33.231928Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Part 1d: correlation between balance variables","metadata":{}},{"cell_type":"code","source":"cols_to_show = [c for c in pdf.columns if (c.startswith('B')) and c in cat_features]\n#corr=pdf[cols_to_show].compute().corr(method = 'spearman')\ncorr = ordinal_correlation.loc[cols_to_show, cols_to_show]\nmask=np.triu(np.ones_like(corr))[1:,:-1]\ncorr=corr.iloc[1:,:-1].copy()\n\nfig, ax = plt.subplots(figsize=(15,15))   \nsns.heatmap(corr, mask=mask, vmin=-1, vmax=1, center=0, annot=True, fmt='.2f', \n            cmap='coolwarm', annot_kws={'fontsize':10,'fontweight':'bold'}, cbar=False)\nax.tick_params(left=False,bottom=False)\nax.set_xticklabels(ax.get_xticklabels(), rotation=45, horizontalalignment='right',fontsize=12)\nax.set_yticklabels(ax.get_yticklabels(), fontsize=12)\nplt.title('Correlations between Balance Variables\\n', fontsize=16)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:33:48.144998Z","iopub.execute_input":"2022-06-23T15:33:48.146676Z","iopub.status.idle":"2022-06-23T15:33:48.652111Z","shell.execute_reply.started":"2022-06-23T15:33:48.146620Z","shell.execute_reply":"2022-06-23T15:33:48.650985Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Part 1e: correlation between payment variables","metadata":{}},{"cell_type":"code","source":"cols_to_show = [c for c in pdf.columns if (c.startswith('P')) and c in cat_features]","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:34:23.984214Z","iopub.execute_input":"2022-06-23T15:34:23.984670Z","iopub.status.idle":"2022-06-23T15:34:23.990564Z","shell.execute_reply.started":"2022-06-23T15:34:23.984632Z","shell.execute_reply":"2022-06-23T15:34:23.989462Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cols_to_show","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:34:28.490803Z","iopub.execute_input":"2022-06-23T15:34:28.491216Z","iopub.status.idle":"2022-06-23T15:34:28.498292Z","shell.execute_reply.started":"2022-06-23T15:34:28.491183Z","shell.execute_reply":"2022-06-23T15:34:28.496939Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Part 2: creating aggregated features and constructing a new dataset from them","metadata":{}},{"cell_type":"code","source":"#pdf = pdf.sort_values(['customer_ID','S_2'])\n#test = test.sort_values(['customer_ID','S_2'])\n#target = target.sort_values('customer_ID')","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.120163Z","iopub.status.idle":"2022-06-23T15:26:53.120523Z","shell.execute_reply.started":"2022-06-23T15:26:53.120366Z","shell.execute_reply":"2022-06-23T15:26:53.120382Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Aggregating train variables","metadata":{}},{"cell_type":"code","source":"#The code below was taken from here: https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n\n#train_num_agg = pdf.groupby(\"customer_ID\")[num_features].agg(['mean', 'std', 'min', 'max', 'last'])\n#train_num_agg.columns = ['_'.join(x) for x in train_num_agg.columns]\n#train_cat_agg = pdf.groupby(\"customer_ID\")[cat_features].agg(['count', 'last'])\n#train_cat_agg.columns = ['_'.join(x) for x in train_cat_agg.columns]\n#train_target = (target.groupby(\"customer_ID\").tail(1).set_index('customer_ID', drop=True).sort_index()[\"target\"])\n","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.121642Z","iopub.status.idle":"2022-06-23T15:26:53.122154Z","shell.execute_reply.started":"2022-06-23T15:26:53.121941Z","shell.execute_reply":"2022-06-23T15:26:53.121965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#for col in train_num_agg.columns:\n#train_num_agg[col] = train_num_agg[col].astype('float32')\n#for col in train_cat_agg.columns:\n#if col[-5:] == 'count':\n#train_cat_agg[col] = train_cat_agg[col].astype('int8')","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.123666Z","iopub.status.idle":"2022-06-23T15:26:53.124069Z","shell.execute_reply.started":"2022-06-23T15:26:53.123907Z","shell.execute_reply":"2022-06-23T15:26:53.123925Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train_num_agg","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.125378Z","iopub.status.idle":"2022-06-23T15:26:53.125731Z","shell.execute_reply.started":"2022-06-23T15:26:53.125562Z","shell.execute_reply":"2022-06-23T15:26:53.125577Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train_cat_agg","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.126947Z","iopub.status.idle":"2022-06-23T15:26:53.127289Z","shell.execute_reply.started":"2022-06-23T15:26:53.127126Z","shell.execute_reply":"2022-06-23T15:26:53.127141Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#target","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.128260Z","iopub.status.idle":"2022-06-23T15:26:53.128590Z","shell.execute_reply.started":"2022-06-23T15:26:53.128436Z","shell.execute_reply":"2022-06-23T15:26:53.128451Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#target['target'] = target['target'].astype('int8')","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.129513Z","iopub.status.idle":"2022-06-23T15:26:53.129883Z","shell.execute_reply.started":"2022-06-23T15:26:53.129684Z","shell.execute_reply":"2022-06-23T15:26:53.129722Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train = dd.concat([train_num_agg, train_cat_agg, target], axis=1, ignore_unknown_divisions = True)","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.131063Z","iopub.status.idle":"2022-06-23T15:26:53.131402Z","shell.execute_reply.started":"2022-06-23T15:26:53.131246Z","shell.execute_reply":"2022-06-23T15:26:53.131263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#del train_num_agg, train_cat_agg, target","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.132640Z","iopub.status.idle":"2022-06-23T15:26:53.133035Z","shell.execute_reply.started":"2022-06-23T15:26:53.132864Z","shell.execute_reply":"2022-06-23T15:26:53.132883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train.to_parquet(\"train_agg.parquet\", compression=\"gzip\")","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.134495Z","iopub.status.idle":"2022-06-23T15:26:53.134891Z","shell.execute_reply.started":"2022-06-23T15:26:53.134681Z","shell.execute_reply":"2022-06-23T15:26:53.134717Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.136153Z","iopub.status.idle":"2022-06-23T15:26:53.136665Z","shell.execute_reply.started":"2022-06-23T15:26:53.136477Z","shell.execute_reply":"2022-06-23T15:26:53.136495Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#del train, pdf\n#gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.137887Z","iopub.status.idle":"2022-06-23T15:26:53.138250Z","shell.execute_reply.started":"2022-06-23T15:26:53.138079Z","shell.execute_reply":"2022-06-23T15:26:53.138103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Aggregating test variables ","metadata":{}},{"cell_type":"code","source":"#The code below was taken from here: https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n\n#test_num_agg = test.groupby(\"customer_ID\")[num_features].agg(['mean', 'std', 'min', 'max', 'last'])\n#test_num_agg.columns = ['_'.join(x) for x in test_num_agg.columns]\n#test_cat_agg = test.groupby(\"customer_ID\")[cat_features].agg(['count', 'last'])\n#test_cat_agg.columns = ['_'.join(x) for x in test_cat_agg.columns]\n#train_target = (target.groupby(\"customer_ID\").tail(1).set_index('customer_ID', drop=True).sort_index()[\"target\"])\n","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.139151Z","iopub.status.idle":"2022-06-23T15:26:53.139479Z","shell.execute_reply.started":"2022-06-23T15:26:53.139323Z","shell.execute_reply":"2022-06-23T15:26:53.139339Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#for col in test_num_agg.columns:\n#test_num_agg[col] = test_num_agg[col].astype('float32')\n#for col in test_cat_agg.columns:\n#if col[-5:] == 'count':\n#test_cat_agg[col] = test_cat_agg[col].astype('int8')","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.140656Z","iopub.status.idle":"2022-06-23T15:26:53.141066Z","shell.execute_reply.started":"2022-06-23T15:26:53.140891Z","shell.execute_reply":"2022-06-23T15:26:53.140909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#test = dd.concat([test_num_agg, test_cat_agg], axis=1, ignore_unknown_divisions = True)\n#del test_num_agg, test_cat_agg\n#gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.142410Z","iopub.status.idle":"2022-06-23T15:26:53.142783Z","shell.execute_reply.started":"2022-06-23T15:26:53.142590Z","shell.execute_reply":"2022-06-23T15:26:53.142606Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#test.to_parquet(\"test_agg.parquet\", compression=\"gzip\")","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.143762Z","iopub.status.idle":"2022-06-23T15:26:53.144118Z","shell.execute_reply.started":"2022-06-23T15:26:53.143954Z","shell.execute_reply":"2022-06-23T15:26:53.143970Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Trying to keep only the last month without groupby. Taken from: https://www.kaggle.com/competitions/amex-default-prediction/discussion/327361\n#pdf.drop_duplicates(subset=['customer_ID'], keep='last').drop(['S_2'], axis='columns').compute()","metadata":{"execution":{"iopub.status.busy":"2022-06-23T15:26:53.145325Z","iopub.status.idle":"2022-06-23T15:26:53.145813Z","shell.execute_reply.started":"2022-06-23T15:26:53.145605Z","shell.execute_reply":"2022-06-23T15:26:53.145628Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}