{"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-07-09T13:12:03.199598Z","iopub.execute_input":"2022-07-09T13:12:03.200427Z","iopub.status.idle":"2022-07-09T13:12:03.237602Z","shell.execute_reply.started":"2022-07-09T13:12:03.200282Z","shell.execute_reply":"2022-07-09T13:12:03.236295Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Exploratory data analysis of AMEX Default Prediction Train Data using Dask\n\n\n#### This notebook analyzes the train data in the following areas:\n- general information:\n    - file size, number of samples, number of features, data type of each feature.\n- statistics\n    - missing values: features with too many missing values should be dropped.\n    - for categorical features: distribution of samples in each category.\n    - numeric features: distribution of data .\n      - plot out histgraph of both the original data and log tranformed data\n      - plot the log transformed data will help the check if a feature will benefit from log transformation\n      \n      \n#### key notes:\n- train label file has `458,913` rows for `458,913` unique customer ID\n- train data file has `5,531,451` rows for `458,913` unique customer ID\n- `customer_ID` is the key to connect train label and data\n- in train data:\n - there are a total of `190` columns, including key column `'customer_ID'` and date column `'S_2'`\n - a `customer_ID`  has at most 13 rows of data\n","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport gc\nimport copy\nimport os\nimport sys\n\nfrom pathlib import Path\nfrom datetime import datetime, date, time, timedelta\nfrom dateutil import relativedelta\n\nimport pyarrow.parquet as pq\nimport pyarrow as pa\n\nimport dask.dataframe as dd","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:03.240208Z","iopub.execute_input":"2022-07-09T13:12:03.240681Z","iopub.status.idle":"2022-07-09T13:12:04.086569Z","shell.execute_reply.started":"2022-07-09T13:12:03.240628Z","shell.execute_reply":"2022-07-09T13:12:04.085394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import warnings\nwarnings.filterwarnings(\"ignore\")\n\npd.options.display.max_rows = 100\npd.options.display.max_columns = 100\n\n\nimport pytorch_lightning as pl\nrandom_seed=1234\npl.seed_everything(random_seed)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:04.088141Z","iopub.execute_input":"2022-07-09T13:12:04.089089Z","iopub.status.idle":"2022-07-09T13:12:17.026377Z","shell.execute_reply.started":"2022-07-09T13:12:04.089039Z","shell.execute_reply":"2022-07-09T13:12:17.025237Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nchunksize = 100\ntrain_file = '/kaggle/input/amex-default-prediction/train_data.csv'\nwith pd.read_csv(train_file, chunksize=chunksize) as reader:\n    for i, chunk in enumerate(reader):\n        break","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:17.029426Z","iopub.execute_input":"2022-07-09T13:12:17.030498Z","iopub.status.idle":"2022-07-09T13:12:17.074172Z","shell.execute_reply.started":"2022-07-09T13:12:17.030461Z","shell.execute_reply":"2022-07-09T13:12:17.073152Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunk.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:17.075740Z","iopub.execute_input":"2022-07-09T13:12:17.076369Z","iopub.status.idle":"2022-07-09T13:12:17.109545Z","shell.execute_reply.started":"2022-07-09T13:12:17.076335Z","shell.execute_reply":"2022-07-09T13:12:17.108716Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_cols = chunk.columns.tolist()\n#cat feats based on information from this page: https://www.kaggle.com/competitions/amex-default-prediction/data\ncat_feats = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\nprint(len(all_cols)), print(all_cols)\nprint(len(cat_feats)), print(cat_feats)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:17.110555Z","iopub.execute_input":"2022-07-09T13:12:17.111094Z","iopub.status.idle":"2022-07-09T13:12:17.120204Z","shell.execute_reply.started":"2022-07-09T13:12:17.111062Z","shell.execute_reply":"2022-07-09T13:12:17.119062Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### load the train parquet file","metadata":{}},{"cell_type":"code","source":"%%time\ntrain_labels = pd.read_csv('/kaggle/input/amex-default-prediction/train_labels.csv')\nprint(train_labels.shape)\ndisplay(train_labels.head(2))","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:17.121939Z","iopub.execute_input":"2022-07-09T13:12:17.122665Z","iopub.status.idle":"2022-07-09T13:12:17.961284Z","shell.execute_reply.started":"2022-07-09T13:12:17.122617Z","shell.execute_reply":"2022-07-09T13:12:17.960077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:17.962663Z","iopub.execute_input":"2022-07-09T13:12:17.962996Z","iopub.status.idle":"2022-07-09T13:12:18.027799Z","shell.execute_reply.started":"2022-07-09T13:12:17.962966Z","shell.execute_reply":"2022-07-09T13:12:18.026714Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels['customer_ID'].nunique()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:18.030383Z","iopub.execute_input":"2022-07-09T13:12:18.031171Z","iopub.status.idle":"2022-07-09T13:12:18.257520Z","shell.execute_reply.started":"2022-07-09T13:12:18.031131Z","shell.execute_reply":"2022-07-09T13:12:18.256206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## calcuate the na cnt","metadata":{}},{"cell_type":"code","source":"%%time\ntrain_file = '/kaggle/input/amex-train-20220706/train.parquet'","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:18.262836Z","iopub.execute_input":"2022-07-09T13:12:18.263172Z","iopub.status.idle":"2022-07-09T13:12:18.269103Z","shell.execute_reply.started":"2022-07-09T13:12:18.263143Z","shell.execute_reply":"2022-07-09T13:12:18.267918Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"raw","source":"%%time\ndf = pd.read_parquet(train_file, columns=['customer_ID'], engine='pyarrow')","metadata":{"execution":{"iopub.status.busy":"2022-07-06T15:05:34.032269Z","iopub.execute_input":"2022-07-06T15:05:34.032789Z","iopub.status.idle":"2022-07-06T15:05:37.100386Z","shell.execute_reply.started":"2022-07-06T15:05:34.032748Z","shell.execute_reply":"2022-07-06T15:05:37.098732Z"}}},{"cell_type":"code","source":"%%time\ndf = dd.read_parquet(train_file, columns=['customer_ID'], engine='pyarrow')\ntotal_cnt = df.count().compute().values[0]\nunique_cnt = df.compute().nunique().values[0]\nprint(total_cnt, unique_cnt)\n\ndel df\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:18.271075Z","iopub.execute_input":"2022-07-09T13:12:18.271733Z","iopub.status.idle":"2022-07-09T13:12:23.903823Z","shell.execute_reply.started":"2022-07-09T13:12:18.271685Z","shell.execute_reply":"2022-07-09T13:12:23.902460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ndata_types = []\nfor c in all_cols:\n    df = dd.read_parquet(train_file, columns=[c], engine='pyarrow')\n    data_types.append([c, df.dtypes.values[0], df.isna().sum().compute().values[0], df.nunique().compute().values[0]])\n    \n    del df\n    gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:12:23.905523Z","iopub.execute_input":"2022-07-09T13:12:23.905975Z","iopub.status.idle":"2022-07-09T13:18:11.482147Z","shell.execute_reply.started":"2022-07-09T13:12:23.905931Z","shell.execute_reply":"2022-07-09T13:18:11.480980Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nacnt = pd.DataFrame(data_types, columns=['feat', 'dtype', 'na_cnt', 'nunique'])\nnacnt['na_pct'] = 100*nacnt['na_cnt']/total_cnt","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.483780Z","iopub.execute_input":"2022-07-09T13:18:11.485010Z","iopub.status.idle":"2022-07-09T13:18:11.494420Z","shell.execute_reply.started":"2022-07-09T13:18:11.484960Z","shell.execute_reply":"2022-07-09T13:18:11.493216Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nacnt.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.495505Z","iopub.execute_input":"2022-07-09T13:18:11.495927Z","iopub.status.idle":"2022-07-09T13:18:11.514599Z","shell.execute_reply.started":"2022-07-09T13:18:11.495886Z","shell.execute_reply":"2022-07-09T13:18:11.513291Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \nnacnt.to_csv('nacnt.csv', sep='|', index=False) #save data into a csv file for later use","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.516725Z","iopub.execute_input":"2022-07-09T13:18:11.517606Z","iopub.status.idle":"2022-07-09T13:18:11.533501Z","shell.execute_reply.started":"2022-07-09T13:18:11.517554Z","shell.execute_reply":"2022-07-09T13:18:11.532348Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nacnt['dtype'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.535011Z","iopub.execute_input":"2022-07-09T13:18:11.535800Z","iopub.status.idle":"2022-07-09T13:18:11.543705Z","shell.execute_reply.started":"2022-07-09T13:18:11.535764Z","shell.execute_reply":"2022-07-09T13:18:11.542707Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nacnt['na_pct'].hist(bins=50)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.545214Z","iopub.execute_input":"2022-07-09T13:18:11.545538Z","iopub.status.idle":"2022-07-09T13:18:11.855544Z","shell.execute_reply.started":"2022-07-09T13:18:11.545508Z","shell.execute_reply":"2022-07-09T13:18:11.854252Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### base on unique number of values per feature to potential categorical feature list\n\n- `D_87` : has only one unique value but most samples have missing values. thus this feature should not be considered as categorical features and will be dropped.\n- `B_31`:  has no missing value and only two possible values. this feature will be considered as a categorical feature","metadata":{}},{"cell_type":"code","source":"nacnt[nacnt['nunique']<=100]","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.857496Z","iopub.execute_input":"2022-07-09T13:18:11.858213Z","iopub.status.idle":"2022-07-09T13:18:11.874147Z","shell.execute_reply.started":"2022-07-09T13:18:11.858159Z","shell.execute_reply":"2022-07-09T13:18:11.872796Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"potential_cat_feats = nacnt[nacnt['nunique']<=100]['feat'].values.tolist()\nprint(potential_cat_feats)\nprint(set(potential_cat_feats)-set(cat_feats))","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.875933Z","iopub.execute_input":"2022-07-09T13:18:11.876404Z","iopub.status.idle":"2022-07-09T13:18:11.884902Z","shell.execute_reply.started":"2022-07-09T13:18:11.876359Z","shell.execute_reply":"2022-07-09T13:18:11.884205Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### missing values\n\n- features with too much missing values will be dropped\n- `>=80%` samples missing value: 23 features\n - `missing80 = ['D_87', 'D_88', 'D_108', 'D_111', 'D_110', 'B_39', 'D_73', 'B_42', 'D_134', 'D_135', 'D_136', 'D_137', 'D_138', 'R_9', 'B_29', 'D_106', 'D_132', 'D_49', 'R_26', 'D_76', 'D_66', 'D_42', 'D_142']`\n- `>=50%` samples missing value: 30 features\n - `missing50 = ['D_87', 'D_88', 'D_108', 'D_111', 'D_110', 'B_39', 'D_73', 'B_42', 'D_134', 'D_135', 'D_136', 'D_137', 'D_138', 'R_9', 'B_29', 'D_106', 'D_132', 'D_49', 'R_26', 'D_76', 'D_66', 'D_42', 'D_142', 'D_53', 'D_82', 'D_50', 'B_17', 'D_105', 'D_56', 'S_9']`\n- `>=20%` samples missing value: 34 features\n - `missing20 = ['D_87', 'D_88', 'D_108', 'D_111', 'D_110', 'B_39', 'D_73', 'B_42', 'D_134', 'D_135', 'D_136', 'D_137', 'D_138', 'R_9', 'B_29', 'D_106', 'D_132', 'D_49', 'R_26', 'D_76', 'D_66', 'D_42', 'D_142', 'D_53', 'D_82', 'D_50', 'B_17', 'D_105', 'D_56', 'S_9', 'D_77', 'D_43', 'S_27', 'D_46']`\n- `>=10%` samples missing value: 39 fatures\n - `missing10 = ['D_87', 'D_88', 'D_108', 'D_111', 'D_110', 'B_39', 'D_73', 'B_42', 'D_134', 'D_135', 'D_136', 'D_137', 'D_138', 'R_9', 'B_29', 'D_106', 'D_132', 'D_49', 'R_26', 'D_76', 'D_66', 'D_42', 'D_142', 'D_53', 'D_82', 'D_50', 'B_17', 'D_105', 'D_56', 'S_9', 'D_77', 'D_43', 'S_27', 'D_46', 'S_7', 'S_3', 'D_62', 'D_48', 'D_61']`","metadata":{}},{"cell_type":"code","source":"nacnt.sort_values(by='na_pct', ascending=False, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.886017Z","iopub.execute_input":"2022-07-09T13:18:11.886652Z","iopub.status.idle":"2022-07-09T13:18:11.897374Z","shell.execute_reply.started":"2022-07-09T13:18:11.886619Z","shell.execute_reply":"2022-07-09T13:18:11.896519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nacnt.head(20)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.898580Z","iopub.execute_input":"2022-07-09T13:18:11.899697Z","iopub.status.idle":"2022-07-09T13:18:11.920867Z","shell.execute_reply.started":"2022-07-09T13:18:11.899645Z","shell.execute_reply":"2022-07-09T13:18:11.919764Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(nacnt[nacnt['na_pct']>=80].shape)\nmissing80 = nacnt[nacnt['na_pct']>=80]['feat'].values.tolist()\nprint('number of features with >=80% samples missing values: ', len(missing80))\nprint(missing80)\nnacnt[nacnt['na_pct']>=80]","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.922502Z","iopub.execute_input":"2022-07-09T13:18:11.922847Z","iopub.status.idle":"2022-07-09T13:18:11.941454Z","shell.execute_reply.started":"2022-07-09T13:18:11.922817Z","shell.execute_reply":"2022-07-09T13:18:11.940516Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(nacnt[nacnt['na_pct']>=50].shape)\nmissing50 = nacnt[nacnt['na_pct']>=50]['feat'].values.tolist()\nprint('number of features with >=50% samples missing values: ', len(missing50))\nprint(missing50)\nnacnt[nacnt['na_pct']>=50]","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.942657Z","iopub.execute_input":"2022-07-09T13:18:11.943685Z","iopub.status.idle":"2022-07-09T13:18:11.967810Z","shell.execute_reply.started":"2022-07-09T13:18:11.943651Z","shell.execute_reply":"2022-07-09T13:18:11.967023Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(nacnt[nacnt['na_pct']>=20].shape)\nmissing20 = nacnt[nacnt['na_pct']>=20]['feat'].values.tolist()\nprint('number of features with >=20% samples missing values: ', len(missing20))\nprint(missing20)\nnacnt[nacnt['na_pct']>=20]","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.969104Z","iopub.execute_input":"2022-07-09T13:18:11.969720Z","iopub.status.idle":"2022-07-09T13:18:11.995304Z","shell.execute_reply.started":"2022-07-09T13:18:11.969687Z","shell.execute_reply":"2022-07-09T13:18:11.994147Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(nacnt[nacnt['na_pct']>=10].shape)\nmissing10 = nacnt[nacnt['na_pct']>=10]['feat'].values.tolist()\nprint('number of features with >=10% samples missing values: ', len(missing10))\nprint(missing10)\nnacnt[nacnt['na_pct']>=10]","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:11.996860Z","iopub.execute_input":"2022-07-09T13:18:11.998524Z","iopub.status.idle":"2022-07-09T13:18:12.022694Z","shell.execute_reply.started":"2022-07-09T13:18:11.998474Z","shell.execute_reply":"2022-07-09T13:18:12.021749Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## categorical features","metadata":{}},{"cell_type":"code","source":"cat_feats = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:12.025388Z","iopub.execute_input":"2022-07-09T13:18:12.025898Z","iopub.status.idle":"2022-07-09T13:18:12.032624Z","shell.execute_reply.started":"2022-07-09T13:18:12.025861Z","shell.execute_reply":"2022-07-09T13:18:12.031587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nfor c in cat_feats:\n    df = dd.read_parquet(train_file, columns=[c], engine='pyarrow')\n    print(c, '-'*100)\n    print(df[c].nunique().compute())\n    display(df[c].value_counts().compute().sort_index())\n    del df\n    gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:12.033958Z","iopub.execute_input":"2022-07-09T13:18:12.035353Z","iopub.status.idle":"2022-07-09T13:18:21.370683Z","shell.execute_reply.started":"2022-07-09T13:18:12.035302Z","shell.execute_reply":"2022-07-09T13:18:21.369383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### object features\n\n- `customer_ID`: customer id. \n- `S_2`: date\n- `D_63, D_64`: categorical features\n","metadata":{}},{"cell_type":"code","source":"print(nacnt[nacnt['dtype']=='object']['feat'].values)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:21.376021Z","iopub.execute_input":"2022-07-09T13:18:21.376367Z","iopub.status.idle":"2022-07-09T13:18:21.384142Z","shell.execute_reply.started":"2022-07-09T13:18:21.376335Z","shell.execute_reply":"2022-07-09T13:18:21.382852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nfor c in nacnt[nacnt['dtype']=='object']['feat'].values:\n    df = dd.read_parquet(train_file, columns=[c], engine='pyarrow')\n    print(c, '-'*100)\n    display(df.head(5))\n    \n    if c=='customer_ID':\n        t = df[c].value_counts().compute()\n        display(t.value_counts())\n        display(df[df['customer_ID']=='8034aa3a67acb152f472bd8036f4c579b559d046ba12d7a911d27abd1c4b080b'].head(20))\n        \n    del df\n    gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:21.385746Z","iopub.execute_input":"2022-07-09T13:18:21.386063Z","iopub.status.idle":"2022-07-09T13:18:31.696041Z","shell.execute_reply.started":"2022-07-09T13:18:21.386033Z","shell.execute_reply":"2022-07-09T13:18:31.694973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### int features\n\n- only one feature is int type: `B_31`\n- this feature should be treated as categorical feature as well","metadata":{}},{"cell_type":"code","source":"print(nacnt[nacnt['dtype']=='int32']['feat'].values)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:31.697817Z","iopub.execute_input":"2022-07-09T13:18:31.698603Z","iopub.status.idle":"2022-07-09T13:18:31.707448Z","shell.execute_reply.started":"2022-07-09T13:18:31.698552Z","shell.execute_reply":"2022-07-09T13:18:31.706341Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nfor c in nacnt[nacnt['dtype']=='int32']['feat'].values:\n    df = dd.read_parquet(train_file, columns=[c], engine='pyarrow')\n    display(df[c].value_counts().compute().sort_index())\n    \n    del df\n    gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:31.708706Z","iopub.execute_input":"2022-07-09T13:18:31.709482Z","iopub.status.idle":"2022-07-09T13:18:32.127573Z","shell.execute_reply.started":"2022-07-09T13:18:31.709438Z","shell.execute_reply":"2022-07-09T13:18:32.126405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### float features","metadata":{}},{"cell_type":"code","source":"nacnt.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:32.129023Z","iopub.execute_input":"2022-07-09T13:18:32.129380Z","iopub.status.idle":"2022-07-09T13:18:32.141167Z","shell.execute_reply.started":"2022-07-09T13:18:32.129349Z","shell.execute_reply":"2022-07-09T13:18:32.140116Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nacnt[(nacnt['dtype']=='float32') & (nacnt['nunique']>100) & (nacnt['na_pct']<20)]","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:32.142477Z","iopub.execute_input":"2022-07-09T13:18:32.142772Z","iopub.status.idle":"2022-07-09T13:18:32.163528Z","shell.execute_reply.started":"2022-07-09T13:18:32.142745Z","shell.execute_reply":"2022-07-09T13:18:32.162530Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(cat_feats)\ncat_feats = cat_feats + ['B_31']\nprint(cat_feats)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:32.164834Z","iopub.execute_input":"2022-07-09T13:18:32.165131Z","iopub.status.idle":"2022-07-09T13:18:32.171096Z","shell.execute_reply.started":"2022-07-09T13:18:32.165105Z","shell.execute_reply":"2022-07-09T13:18:32.170201Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"float_feats = nacnt[nacnt['dtype']=='float32']['feat'].values.tolist()\nprint(len(float_feats))\nfloat_feats = list((set(float_feats) - set(cat_feats)) - set(missing20) )\nprint(len(float_feats))\nprint(float_feats)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:32.172644Z","iopub.execute_input":"2022-07-09T13:18:32.172989Z","iopub.status.idle":"2022-07-09T13:18:32.184212Z","shell.execute_reply.started":"2022-07-09T13:18:32.172957Z","shell.execute_reply":"2022-07-09T13:18:32.183344Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n#features with negative values will not have the log transform plot\nimport matplotlib.pyplot as plt\nimport math\n\nfloat_stats = []\n\n\nncols = 4\nnrows = math.ceil(len(float_feats)*2/ncols)\n# nrows = math.ceil(50*2/ncols)\n# ncols = 4\n# nrows = math.ceil(10*2/ncols)\n#fig, axes = plt.subplots(nrows=nrows, ncols=ncols)\nfig, axes = plt.subplots(nrows=nrows, ncols=ncols, figsize=(20, 360))\n\nfor i, c in enumerate(float_feats):\n    df = dd.read_parquet(train_file, columns=[c], engine='pyarrow')\n    \n    #--calculate the stats ----------\n    item = [c, df[c].compute().min(), df[c].compute().max(), df[c].compute().mean(), df[c].compute().std(), df[c].compute().median(), df[c].compute().skew()]\n    for q in [0.05, 0.15, 0.25, 0.75, 0.85, 0.95]:\n        item.append(df[c].compute().quantile(q=q))\n\n    float_stats.append(item)\n    \n    #--plot the graph------------------\n    df[c].compute().plot(ax=axes[i//2, (i%2)*2], kind='hist', bins=50)\n    axes[i//2, (i%2)*2].set_title(f'{c}')\n    \n    if df.compute().min().values[0]>0:\n        df[c].compute().transform(np.log).plot(ax=axes[i//2,(i%2)*2 + 1], kind='hist', bins=50)\n        axes[i//2,(i%2)*2 + 1].set_title(f'log of {c}')\n\n    del df\n    gc.collect()\n","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:18:32.185615Z","iopub.execute_input":"2022-07-09T13:18:32.185939Z","iopub.status.idle":"2022-07-09T13:34:12.567018Z","shell.execute_reply.started":"2022-07-09T13:18:32.185910Z","shell.execute_reply":"2022-07-09T13:34:12.566206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"float_stats_df = pd.DataFrame(data=float_stats, \n                              columns=['feat', 'min', 'max', 'mean', 'std', 'median', 'skew', \n                                       'quantile0.05', 'quantile0.15', 'quantile0.25', 'quantile0.75', 'quantile0.85', 'quantile0.95'])","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:34:12.568337Z","iopub.execute_input":"2022-07-09T13:34:12.568798Z","iopub.status.idle":"2022-07-09T13:34:12.575280Z","shell.execute_reply.started":"2022-07-09T13:34:12.568768Z","shell.execute_reply":"2022-07-09T13:34:12.574332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"float_stats_df","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:34:12.576329Z","iopub.execute_input":"2022-07-09T13:34:12.577169Z","iopub.status.idle":"2022-07-09T13:34:12.612582Z","shell.execute_reply.started":"2022-07-09T13:34:12.577130Z","shell.execute_reply":"2022-07-09T13:34:12.611818Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"float_stats_df.to_csv('float_stats.csv', sep='|', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-09T13:34:12.613555Z","iopub.execute_input":"2022-07-09T13:34:12.614415Z","iopub.status.idle":"2022-07-09T13:34:12.623913Z","shell.execute_reply.started":"2022-07-09T13:34:12.614379Z","shell.execute_reply":"2022-07-09T13:34:12.622975Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}