{"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":"# 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-05-31T05:36:25.815716Z","iopub.execute_input":"2022-05-31T05:36:25.816363Z","iopub.status.idle":"2022-05-31T05:36:25.847425Z","shell.execute_reply.started":"2022-05-31T05:36:25.816229Z","shell.execute_reply":"2022-05-31T05:36:25.84651Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import dask.dataframe as dd\nimport gc\nimport tensorflow as tf\nprint(tf.__version__)","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:25.90114Z","iopub.execute_input":"2022-05-31T05:36:25.901974Z","iopub.status.idle":"2022-05-31T05:36:31.811648Z","shell.execute_reply.started":"2022-05-31T05:36:25.901934Z","shell.execute_reply":"2022-05-31T05:36:31.810507Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.set_option('display.max_columns', 500)\npd.set_option('display.max_rows', 500)\npd.set_option('display.float_format', lambda x: '%.5f' % x)","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:31.813639Z","iopub.execute_input":"2022-05-31T05:36:31.81448Z","iopub.status.idle":"2022-05-31T05:36:31.819184Z","shell.execute_reply.started":"2022-05-31T05:36:31.814437Z","shell.execute_reply":"2022-05-31T05:36:31.818036Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df = pd.read_csv('../input/amex-default-prediction/train_data.csv', nrows=10)\ntrain_labels = pd.read_csv('../input/amex-default-prediction/train_labels.csv',)\ntest_df = pd.read_csv('../input/amex-default-prediction/test_data.csv', nrows=10)","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:31.820846Z","iopub.execute_input":"2022-05-31T05:36:31.821525Z","iopub.status.idle":"2022-05-31T05:36:32.97343Z","shell.execute_reply.started":"2022-05-31T05:36:31.82148Z","shell.execute_reply":"2022-05-31T05:36:32.972495Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:32.975389Z","iopub.execute_input":"2022-05-31T05:36:32.975738Z","iopub.status.idle":"2022-05-31T05:36:33.058018Z","shell.execute_reply.started":"2022-05-31T05:36:32.975709Z","shell.execute_reply":"2022-05-31T05:36:33.056975Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dtypes_df = train_df.dtypes.to_frame().reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:33.059644Z","iopub.execute_input":"2022-05-31T05:36:33.060058Z","iopub.status.idle":"2022-05-31T05:36:33.066837Z","shell.execute_reply.started":"2022-05-31T05:36:33.060019Z","shell.execute_reply":"2022-05-31T05:36:33.065871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Convert `float64` --> `float16` and category cols to `int8` and str types to decrease ram use usage","metadata":{}},{"cell_type":"code","source":"dtype_dict = {}","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:33.067763Z","iopub.execute_input":"2022-05-31T05:36:33.068186Z","iopub.status.idle":"2022-05-31T05:36:33.077658Z","shell.execute_reply.started":"2022-05-31T05:36:33.068158Z","shell.execute_reply":"2022-05-31T05:36:33.07684Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\nfor col in cat_cols:\n    if train_df[col].dtype == \"float64\":\n        dtype_dict[col] = \"int8\" # category to \n    else:\n        dtype_dict[col] = str\n    ","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:33.078938Z","iopub.execute_input":"2022-05-31T05:36:33.07942Z","iopub.status.idle":"2022-05-31T05:36:33.092551Z","shell.execute_reply.started":"2022-05-31T05:36:33.079342Z","shell.execute_reply":"2022-05-31T05:36:33.091653Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in train_df.columns:\n    \n    if train_df[col].dtype == \"float64\":\n        dtype_dict[col] = \"float16\"\n    \n        ","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:33.093541Z","iopub.execute_input":"2022-05-31T05:36:33.094601Z","iopub.status.idle":"2022-05-31T05:36:33.111323Z","shell.execute_reply.started":"2022-05-31T05:36:33.094567Z","shell.execute_reply":"2022-05-31T05:36:33.110261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Load whole data with predefined data types","metadata":{}},{"cell_type":"code","source":"train_df = pd.read_csv('../input/amex-default-prediction/train_data.csv', dtype=dtype_dict)","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:36:33.113869Z","iopub.execute_input":"2022-05-31T05:36:33.114444Z","iopub.status.idle":"2022-05-31T05:42:17.778012Z","shell.execute_reply.started":"2022-05-31T05:36:33.114411Z","shell.execute_reply":"2022-05-31T05:42:17.776898Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:17.780755Z","iopub.execute_input":"2022-05-31T05:42:17.781122Z","iopub.status.idle":"2022-05-31T05:42:17.961543Z","shell.execute_reply.started":"2022-05-31T05:42:17.78109Z","shell.execute_reply":"2022-05-31T05:42:17.960182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df['S_2'] = pd.to_datetime(train_df['S_2'])","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:17.963424Z","iopub.execute_input":"2022-05-31T05:42:17.964134Z","iopub.status.idle":"2022-05-31T05:42:18.846594Z","shell.execute_reply.started":"2022-05-31T05:42:17.964097Z","shell.execute_reply":"2022-05-31T05:42:18.84558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:18.847753Z","iopub.execute_input":"2022-05-31T05:42:18.848141Z","iopub.status.idle":"2022-05-31T05:42:19.006471Z","shell.execute_reply.started":"2022-05-31T05:42:18.848111Z","shell.execute_reply":"2022-05-31T05:42:19.005748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols = []\nfor col in train_df.columns:\n    if col != \"S_2\" and col not in cat_cols:\n        num_cols.append(col)","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:19.007606Z","iopub.execute_input":"2022-05-31T05:42:19.008233Z","iopub.status.idle":"2022-05-31T05:42:19.018335Z","shell.execute_reply.started":"2022-05-31T05:42:19.008193Z","shell.execute_reply":"2022-05-31T05:42:19.017589Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Lets see target variable distribution","metadata":{}},{"cell_type":"code","source":"train_labels['customer_ID'] = train_labels['customer_ID'].astype(str)\ntrain_labels['target'] = train_labels['target'].astype(\"int8\") # to decrease ram usage","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:19.020633Z","iopub.execute_input":"2022-05-31T05:42:19.021523Z","iopub.status.idle":"2022-05-31T05:42:19.073728Z","shell.execute_reply.started":"2022-05-31T05:42:19.021448Z","shell.execute_reply":"2022-05-31T05:42:19.073046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels['customer_ID'].nunique()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:19.074751Z","iopub.execute_input":"2022-05-31T05:42:19.075145Z","iopub.status.idle":"2022-05-31T05:42:19.257398Z","shell.execute_reply.started":"2022-05-31T05:42:19.075119Z","shell.execute_reply":"2022-05-31T05:42:19.256482Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:19.258491Z","iopub.execute_input":"2022-05-31T05:42:19.258809Z","iopub.status.idle":"2022-05-31T05:42:19.419818Z","shell.execute_reply.started":"2022-05-31T05:42:19.258781Z","shell.execute_reply":"2022-05-31T05:42:19.418906Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels.target.value_counts().plot(kind='bar')","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:19.421195Z","iopub.execute_input":"2022-05-31T05:42:19.421682Z","iopub.status.idle":"2022-05-31T05:42:19.644641Z","shell.execute_reply.started":"2022-05-31T05:42:19.421652Z","shell.execute_reply":"2022-05-31T05:42:19.643728Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Unique Customers","metadata":{}},{"cell_type":"code","source":"train_df.customer_ID.nunique() # same as labels df","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:19.645964Z","iopub.execute_input":"2022-05-31T05:42:19.646412Z","iopub.status.idle":"2022-05-31T05:42:20.496921Z","shell.execute_reply.started":"2022-05-31T05:42:19.646366Z","shell.execute_reply":"2022-05-31T05:42:20.495908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Lets Aggregate Numerical columns for each customer","metadata":{}},{"cell_type":"code","source":"num_cols_grp_df = train_df.groupby('customer_ID')[num_cols].agg(['mean', 'std', 'min', 'max'])","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:42:20.500327Z","iopub.execute_input":"2022-05-31T05:42:20.500647Z","iopub.status.idle":"2022-05-31T05:43:20.483855Z","shell.execute_reply.started":"2022-05-31T05:42:20.50062Z","shell.execute_reply":"2022-05-31T05:43:20.482898Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols_grp_df.columns = num_cols_grp_df.columns.to_flat_index()\nnum_cols_grp_df = num_cols_grp_df.reset_index()\nnew_num_cols = ['customer_ID']\nfor col in num_cols_grp_df.columns[1:]:\n    new_num_cols.append(\"_\".join(col))\nnum_cols_grp_df.columns = new_num_cols\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:20.485141Z","iopub.execute_input":"2022-05-31T05:43:20.485472Z","iopub.status.idle":"2022-05-31T05:43:21.128731Z","shell.execute_reply.started":"2022-05-31T05:43:20.485443Z","shell.execute_reply":"2022-05-31T05:43:21.127628Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols_grp_df['customer_ID'] = num_cols_grp_df['customer_ID'].astype(str)","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:21.130446Z","iopub.execute_input":"2022-05-31T05:43:21.1309Z","iopub.status.idle":"2022-05-31T05:43:21.178149Z","shell.execute_reply.started":"2022-05-31T05:43:21.130858Z","shell.execute_reply":"2022-05-31T05:43:21.177251Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols_grp_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:21.179456Z","iopub.execute_input":"2022-05-31T05:43:21.180517Z","iopub.status.idle":"2022-05-31T05:43:21.415067Z","shell.execute_reply.started":"2022-05-31T05:43:21.180476Z","shell.execute_reply":"2022-05-31T05:43:21.414335Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols_grp_df = train_df.groupby('customer_ID')[cat_cols].agg(['count', 'last', 'nunique'])","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:21.416047Z","iopub.execute_input":"2022-05-31T05:43:21.416742Z","iopub.status.idle":"2022-05-31T05:43:31.100303Z","shell.execute_reply.started":"2022-05-31T05:43:21.416709Z","shell.execute_reply":"2022-05-31T05:43:31.098574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols_grp_df.columns = cat_cols_grp_df.columns.to_flat_index()\ncat_cols_grp_df = cat_cols_grp_df.reset_index()\nnew_cat_cols = ['customer_ID']\nfor col in cat_cols_grp_df.columns[1:]:\n    new_cat_cols.append(\"_\".join(col))\ncat_cols_grp_df.columns = new_cat_cols\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:31.102987Z","iopub.execute_input":"2022-05-31T05:43:31.103568Z","iopub.status.idle":"2022-05-31T05:43:31.39408Z","shell.execute_reply.started":"2022-05-31T05:43:31.103517Z","shell.execute_reply":"2022-05-31T05:43:31.393193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols_grp_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:31.395348Z","iopub.execute_input":"2022-05-31T05:43:31.396003Z","iopub.status.idle":"2022-05-31T05:43:31.419969Z","shell.execute_reply.started":"2022-05-31T05:43:31.395959Z","shell.execute_reply":"2022-05-31T05:43:31.418944Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in cat_cols_grp_df.columns:\n    if col == \"customer_ID\":\n        cat_cols_grp_df[col] = cat_cols_grp_df[col].astype(str)\n    if cat_cols_grp_df[col].dtype == \"int64\":\n        cat_cols_grp_df[col] = cat_cols_grp_df[col].astype(\"int8\")","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:31.421486Z","iopub.execute_input":"2022-05-31T05:43:31.422841Z","iopub.status.idle":"2022-05-31T05:43:31.774958Z","shell.execute_reply.started":"2022-05-31T05:43:31.422786Z","shell.execute_reply":"2022-05-31T05:43:31.773981Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols_grp_df.shape, cat_cols_grp_df.shape, train_labels.shape","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:31.776537Z","iopub.execute_input":"2022-05-31T05:43:31.7769Z","iopub.status.idle":"2022-05-31T05:43:31.783006Z","shell.execute_reply.started":"2022-05-31T05:43:31.776872Z","shell.execute_reply":"2022-05-31T05:43:31.782116Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols_grp_df = num_cols_grp_df.sort_values(by='customer_ID')\ncat_cols_grp_df = cat_cols_grp_df.sort_values(by='customer_ID')\ntrain_labels = train_labels.sort_values(by='customer_ID')","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:31.786417Z","iopub.execute_input":"2022-05-31T05:43:31.78677Z","iopub.status.idle":"2022-05-31T05:43:35.69713Z","shell.execute_reply.started":"2022-05-31T05:43:31.786739Z","shell.execute_reply":"2022-05-31T05:43:35.696284Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"final_df = pd.concat([cat_cols_grp_df, num_cols_grp_df.drop(['customer_ID'], axis=1), train_labels.drop(['customer_ID'], axis=1)], axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:35.698477Z","iopub.execute_input":"2022-05-31T05:43:35.698918Z","iopub.status.idle":"2022-05-31T05:43:42.437166Z","shell.execute_reply.started":"2022-05-31T05:43:35.698878Z","shell.execute_reply":"2022-05-31T05:43:42.436193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(num_cols_grp_df.isna().sum(axis=0).sort_values(ascending=False) > 450000).sum()","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:42.438328Z","iopub.execute_input":"2022-05-31T05:43:42.438643Z","iopub.status.idle":"2022-05-31T05:43:43.878108Z","shell.execute_reply.started":"2022-05-31T05:43:42.438615Z","shell.execute_reply":"2022-05-31T05:43:43.876763Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* There are 29 columns which have  more than 450000 null values","metadata":{}},{"cell_type":"code","source":"final_df.to_pickle(\"./train_agg.pkl\", compression=\"gzip\")","metadata":{"execution":{"iopub.status.busy":"2022-05-31T05:43:43.879641Z","iopub.execute_input":"2022-05-31T05:43:43.880292Z","iopub.status.idle":"2022-05-31T05:47:05.936653Z","shell.execute_reply.started":"2022-05-31T05:43:43.880171Z","shell.execute_reply":"2022-05-31T05:47:05.935599Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}