{"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":"# **Important Note: Some line of codes that take long time to execute are commented. So please uncomment as per your requirements.**","metadata":{}},{"cell_type":"markdown","source":"# **Setup**","metadata":{}},{"cell_type":"code","source":"import dask.dataframe as dd","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-07-06T10:19:40.524972Z","iopub.execute_input":"2022-07-06T10:19:40.525547Z","iopub.status.idle":"2022-07-06T10:19:43.788496Z","shell.execute_reply.started":"2022-07-06T10:19:40.525436Z","shell.execute_reply":"2022-07-06T10:19:43.787318Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def count_missing(ddf):\n    ncount = 0\n    for c in ddf.columns:\n        ncount = ncount + len(train_parquet[c]) - train_parquet[c].count().compute()\n    return ncount\n\ndef fill_missing(ddf):\n    for c in ddf.columns:\n        if ddf[c].dtype == 'float64':\n            ddf[c] = ddf[c].astype('float64').fillna(0.0)\n    return ddf","metadata":{"execution":{"iopub.status.busy":"2022-07-06T10:19:53.024713Z","iopub.execute_input":"2022-07-06T10:19:53.025077Z","iopub.status.idle":"2022-07-06T10:19:53.032235Z","shell.execute_reply.started":"2022-07-06T10:19:53.025047Z","shell.execute_reply":"2022-07-06T10:19:53.031285Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Read Large CSV**","metadata":{}},{"cell_type":"code","source":"train_data_path = '../input/amex-default-prediction/train_data.csv'\n\ntrain_data = dd.read_csv(train_data_path, storage_options={'anon': True}, assume_missing=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T10:19:58.085494Z","iopub.execute_input":"2022-07-06T10:19:58.085841Z","iopub.status.idle":"2022-07-06T10:19:58.160837Z","shell.execute_reply.started":"2022-07-06T10:19:58.085809Z","shell.execute_reply":"2022-07-06T10:19:58.159957Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Convert to Parquet**","metadata":{}},{"cell_type":"code","source":"name_function = lambda x: f\"data-{x}.parquet\"\n\ntrain_data.to_parquet('./train_parquet/', name_function=name_function)  \n","metadata":{"execution":{"iopub.status.busy":"2022-07-06T10:20:03.755085Z","iopub.execute_input":"2022-07-06T10:20:03.755529Z","iopub.status.idle":"2022-07-06T10:25:15.582845Z","shell.execute_reply.started":"2022-07-06T10:20:03.75549Z","shell.execute_reply":"2022-07-06T10:25:15.581788Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Read Parquet Data**","metadata":{}},{"cell_type":"code","source":"train_parquet_path = './train_parquet/data-0.parquet'\n\ntrain_parquet = dd.read_parquet(train_parquet_path)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:12:26.640134Z","iopub.execute_input":"2022-07-06T11:12:26.640491Z","iopub.status.idle":"2022-07-06T11:12:26.711406Z","shell.execute_reply.started":"2022-07-06T11:12:26.640461Z","shell.execute_reply":"2022-07-06T11:12:26.710413Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Get Parquet Data Information**","metadata":{}},{"cell_type":"code","source":"train_parquet.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:09:31.902674Z","iopub.execute_input":"2022-07-06T11:09:31.903346Z","iopub.status.idle":"2022-07-06T11:09:31.91133Z","shell.execute_reply.started":"2022-07-06T11:09:31.903309Z","shell.execute_reply":"2022-07-06T11:09:31.910297Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Get Data Features**","metadata":{}},{"cell_type":"code","source":"train_parquet.columns","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:09:37.289593Z","iopub.execute_input":"2022-07-06T11:09:37.290106Z","iopub.status.idle":"2022-07-06T11:09:37.301861Z","shell.execute_reply.started":"2022-07-06T11:09:37.290056Z","shell.execute_reply":"2022-07-06T11:09:37.30087Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Get Single Feature**","metadata":{}},{"cell_type":"code","source":"train_parquet['customer_ID'].head(10)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:09:44.525422Z","iopub.execute_input":"2022-07-06T11:09:44.525769Z","iopub.status.idle":"2022-07-06T11:09:44.578139Z","shell.execute_reply.started":"2022-07-06T11:09:44.52574Z","shell.execute_reply":"2022-07-06T11:09:44.577279Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Get Multiple Features**","metadata":{}},{"cell_type":"code","source":"train_parquet[['customer_ID', 'S_2', 'S_3']].head(10)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:09:48.77384Z","iopub.execute_input":"2022-07-06T11:09:48.774694Z","iopub.status.idle":"2022-07-06T11:09:48.814438Z","shell.execute_reply.started":"2022-07-06T11:09:48.774657Z","shell.execute_reply":"2022-07-06T11:09:48.813427Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Get Feature List by Category**","metadata":{}},{"cell_type":"code","source":"delinquency_features = [col for col in train_parquet if col.startswith('D_')]\n\nspend_features = [col for col in train_parquet if col.startswith('S_')]\n\npayment_features = [col for col in train_parquet if col.startswith('P_')]\n\nbalance_features = [col for col in train_parquet if col.startswith('B_')]\n\nrisk_features = [col for col in train_parquet if col.startswith('R_')]\n","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:09:53.60893Z","iopub.execute_input":"2022-07-06T11:09:53.609625Z","iopub.status.idle":"2022-07-06T11:09:53.616565Z","shell.execute_reply.started":"2022-07-06T11:09:53.609585Z","shell.execute_reply":"2022-07-06T11:09:53.615343Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"delinquency_features:\",len(delinquency_features))\nprint(\"spend_features:\",len(spend_features))\nprint(\"payment_features:\",len(payment_features))\nprint(\"balance_features:\",len(balance_features))\nprint(\"risk_features:\",len(risk_features))","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:09:58.71659Z","iopub.execute_input":"2022-07-06T11:09:58.716967Z","iopub.status.idle":"2022-07-06T11:09:58.723771Z","shell.execute_reply.started":"2022-07-06T11:09:58.716932Z","shell.execute_reply":"2022-07-06T11:09:58.722444Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Analyze Payment Features**","metadata":{}},{"cell_type":"code","source":"train_parquet[payment_features]","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:00.734361Z","iopub.execute_input":"2022-07-06T11:10:00.734832Z","iopub.status.idle":"2022-07-06T11:10:00.785906Z","shell.execute_reply.started":"2022-07-06T11:10:00.734787Z","shell.execute_reply":"2022-07-06T11:10:00.784971Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#missing values in payment features\n#payment_ncount = count_missing(train_parquet[payment_features])\n#print('Missing Values in Payment Features:',payment_ncount)\n","metadata":{"execution":{"iopub.status.busy":"2022-07-06T01:04:47.935899Z","iopub.status.idle":"2022-07-06T01:04:47.936256Z","shell.execute_reply.started":"2022-07-06T01:04:47.936052Z","shell.execute_reply":"2022-07-06T01:04:47.936075Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#fill missing values \n#if payment_ncount > 0:\ntrain_parquet[payment_features]= fill_missing(train_parquet[payment_features])","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:06.910578Z","iopub.execute_input":"2022-07-06T11:10:06.910943Z","iopub.status.idle":"2022-07-06T11:10:07.007092Z","shell.execute_reply.started":"2022-07-06T11:10:06.910895Z","shell.execute_reply":"2022-07-06T11:10:07.006095Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Analyze Spend Features**","metadata":{}},{"cell_type":"code","source":"train_parquet[spend_features]","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:11.820949Z","iopub.execute_input":"2022-07-06T11:10:11.822031Z","iopub.status.idle":"2022-07-06T11:10:11.869168Z","shell.execute_reply.started":"2022-07-06T11:10:11.821981Z","shell.execute_reply":"2022-07-06T11:10:11.867983Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#missing values in spend features\n#spend_ncount = count_missing(train_parquet[spend_features])\n#print('Missing Values in Spend Features:',spend_ncount)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T01:04:47.940958Z","iopub.status.idle":"2022-07-06T01:04:47.941477Z","shell.execute_reply.started":"2022-07-06T01:04:47.941256Z","shell.execute_reply":"2022-07-06T01:04:47.941285Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#fill missing values \n#if spend_ncount > 0:\ntrain_parquet[spend_features]= fill_missing(train_parquet[spend_features])","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:19.550022Z","iopub.execute_input":"2022-07-06T11:10:19.550882Z","iopub.status.idle":"2022-07-06T11:10:20.382379Z","shell.execute_reply.started":"2022-07-06T11:10:19.550812Z","shell.execute_reply":"2022-07-06T11:10:20.380976Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#convert date to numeric\ntrain_parquet['S_2'] = train_parquet['S_2'].str.replace('-','').astype('int64')","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:24.150839Z","iopub.execute_input":"2022-07-06T11:10:24.151822Z","iopub.status.idle":"2022-07-06T11:10:24.195045Z","shell.execute_reply.started":"2022-07-06T11:10:24.151772Z","shell.execute_reply":"2022-07-06T11:10:24.194166Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_parquet['S_2'].head(10)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:12:40.575464Z","iopub.execute_input":"2022-07-06T11:12:40.576355Z","iopub.status.idle":"2022-07-06T11:12:40.595947Z","shell.execute_reply.started":"2022-07-06T11:12:40.576317Z","shell.execute_reply":"2022-07-06T11:12:40.594828Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Analyze Risk Features**","metadata":{}},{"cell_type":"code","source":"train_parquet[risk_features]","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:32.683978Z","iopub.execute_input":"2022-07-06T11:10:32.684836Z","iopub.status.idle":"2022-07-06T11:10:32.727188Z","shell.execute_reply.started":"2022-07-06T11:10:32.684798Z","shell.execute_reply":"2022-07-06T11:10:32.726272Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#missing values in spend features\n#risk_ncount = count_missing(train_parquet[risk_features])\n#print('Missing Values in Risk Features:',risk_ncount)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T01:04:47.948095Z","iopub.status.idle":"2022-07-06T01:04:47.948657Z","shell.execute_reply.started":"2022-07-06T01:04:47.948449Z","shell.execute_reply":"2022-07-06T01:04:47.948473Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#fill missing values \n#if risk_ncount > 0:\ntrain_parquet[risk_features]= fill_missing(train_parquet[risk_features])","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:37.875004Z","iopub.execute_input":"2022-07-06T11:10:37.875381Z","iopub.status.idle":"2022-07-06T11:10:38.85576Z","shell.execute_reply.started":"2022-07-06T11:10:37.87535Z","shell.execute_reply":"2022-07-06T11:10:38.854696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Analyze Balance Features**","metadata":{}},{"cell_type":"code","source":"train_parquet[balance_features]","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:44.776469Z","iopub.execute_input":"2022-07-06T11:10:44.776941Z","iopub.status.idle":"2022-07-06T11:10:44.856117Z","shell.execute_reply.started":"2022-07-06T11:10:44.776874Z","shell.execute_reply":"2022-07-06T11:10:44.855169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#missing values in spend features\n#balance_ncount = count_missing(train_parquet[balance_features])\n#print('Missing Values in Balance Features:',balance_ncount)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T01:04:47.95381Z","iopub.status.idle":"2022-07-06T01:04:47.954234Z","shell.execute_reply.started":"2022-07-06T01:04:47.954022Z","shell.execute_reply":"2022-07-06T01:04:47.954046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#fill missing values \n#if balance_ncount > 0:\ntrain_parquet[balance_features]= fill_missing(train_parquet[balance_features])","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:50.787371Z","iopub.execute_input":"2022-07-06T11:10:50.787722Z","iopub.status.idle":"2022-07-06T11:10:52.295727Z","shell.execute_reply.started":"2022-07-06T11:10:50.787693Z","shell.execute_reply":"2022-07-06T11:10:52.294788Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Analyze Delinquency Features**","metadata":{}},{"cell_type":"code","source":"train_parquet[delinquency_features]","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:10:58.702436Z","iopub.execute_input":"2022-07-06T11:10:58.702803Z","iopub.status.idle":"2022-07-06T11:10:58.811615Z","shell.execute_reply.started":"2022-07-06T11:10:58.702771Z","shell.execute_reply":"2022-07-06T11:10:58.810738Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#missing values in spend features\n#delinquency_ncount = count_missing(train_parquet[delinquency_features])\n#print('Missing Values in Delinquency Features:',delinquency_ncount)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T01:04:47.958736Z","iopub.status.idle":"2022-07-06T01:04:47.959059Z","shell.execute_reply.started":"2022-07-06T01:04:47.958885Z","shell.execute_reply":"2022-07-06T01:04:47.958908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#fill missing values \n#if delinquency_ncount > 0:\ntrain_parquet[delinquency_features]= fill_missing(train_parquet[delinquency_features])","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:11:03.546599Z","iopub.execute_input":"2022-07-06T11:11:03.546991Z","iopub.status.idle":"2022-07-06T11:11:08.778183Z","shell.execute_reply.started":"2022-07-06T11:11:03.546957Z","shell.execute_reply":"2022-07-06T11:11:08.777257Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Analyze Categorical Features**","metadata":{}},{"cell_type":"code","source":"categorical_features = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\n\ntrain_parquet[categorical_features]['B_38'].head(10)","metadata":{"execution":{"iopub.status.busy":"2022-07-06T11:11:11.708888Z","iopub.execute_input":"2022-07-06T11:11:11.709868Z","iopub.status.idle":"2022-07-06T11:11:22.279081Z","shell.execute_reply.started":"2022-07-06T11:11:11.709816Z","shell.execute_reply":"2022-07-06T11:11:22.27798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Save Parquet**","metadata":{}},{"cell_type":"markdown","source":"* Missing values are filled with 0\n* S_2 date column is converted to numeric","metadata":{}},{"cell_type":"code","source":"train_parquet.to_parquet('./train_parquet/', name_function=name_function)  ","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2022-07-06T11:11:47.958097Z","iopub.execute_input":"2022-07-06T11:11:47.958536Z","iopub.status.idle":"2022-07-06T11:11:52.136941Z","shell.execute_reply.started":"2022-07-06T11:11:47.9585Z","shell.execute_reply":"2022-07-06T11:11:52.135901Z"},"trusted":true},"execution_count":null,"outputs":[]}]}