{"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":"# **Introduction**\n\nThis notebook is a guide for beginners to get them started on this competition. It is the first part of a two part notebook series.\n\n> **NOTE:** I, myself am a beginner to data science and this was the first project I worked on after playing around with the Titanic dataset and related getting started guides. The methods I introduce here are based on how I circumvented the RAM limitations and produced a model that generated submittable predictions.  My goal was purely to get a submission rather than to get the best submission hence, experienced Kagglers will find my methods rudimentary and inefficient. However, I am still publishing this notebook to assist those in the same experience level as me get started with this competition, gain some experience, have some fun and get going on this new journey.\n\nAppreciative to suggestions, tips, advice or guides to help me improve, especially on EDA.","metadata":{}},{"cell_type":"markdown","source":"**Importing Modules and Creating Basic Constants**","metadata":{}},{"cell_type":"code","source":"import vaex  # for working with out-of-core data (data that cannot fit into RAM)\nimport numpy as np\nimport pandas as pd\nimport os","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-07T23:19:14.922690Z","iopub.execute_input":"2022-08-07T23:19:14.923190Z","iopub.status.idle":"2022-08-07T23:19:16.720067Z","shell.execute_reply.started":"2022-08-07T23:19:14.923152Z","shell.execute_reply":"2022-08-07T23:19:16.718306Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"INPUT_DIR = '/kaggle/input/amex-default-prediction/'\nWORKING_DIR = '/kaggle/working/'\nCHUNK_SIZE = 1_000_000  # number of rows to read at a time; due to limited memory","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:19:16.722331Z","iopub.execute_input":"2022-08-07T23:19:16.723022Z","iopub.status.idle":"2022-08-07T23:19:16.729824Z","shell.execute_reply.started":"2022-08-07T23:19:16.722973Z","shell.execute_reply":"2022-08-07T23:19:16.728511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Function: CSV to HDF5**\n\nThis function converts .csv files to .hdf5 in chunks of 1,000,000 rows at a time. Since RAM limitations do not allow us to create the entire HDF5 file at once, we convert each chunk to a separate HDF5 file and combine the individual files to a single HDF5 file for utilizaton later. The separate chunked files are removed to keep our working folder clean and to save storage space.","metadata":{}},{"cell_type":"code","source":"def convert_hdf5(src, dst):\n    iter_csv = pd.read_csv(src, iterator=True, chunksize=CHUNK_SIZE)  # for iterating over csv in chunks\n\n    # save hdf5 file for each chunk\n    files = []\n    for i, chunk in enumerate(iter_csv):\n        vaex_df = vaex.from_pandas(chunk, copy_index=False)\n        vaex_df.export_hdf5(dst[:-5] + '_' + str(i) + '.hdf5', progress=True)\n        files.append(dst[:-5] + '_' + str(i) + '.hdf5')\n    \n    print('\\nJoining files...')\n    vaex.concat( [vaex.open(i) for i in files] ).export_hdf5(dst, progress=True)  # combine individual hdf5 files\n    \n    # remove working files\n    for i in files:\n        os.remove(i)\n    \n    print('HDF5 created:', dst)","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:19:16.732067Z","iopub.execute_input":"2022-08-07T23:19:16.732947Z","iopub.status.idle":"2022-08-07T23:19:16.744243Z","shell.execute_reply.started":"2022-08-07T23:19:16.732894Z","shell.execute_reply":"2022-08-07T23:19:16.742969Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Convert Training Data to HDF5**\n\nHere we use our previously defined function to convert our .csv train data and labels to .hdf5 for Vaex.","metadata":{}},{"cell_type":"code","source":"print('Processing training data')\nconvert_hdf5('/kaggle/input/amex-default-prediction/train_data.csv', WORKING_DIR + 'train_data.hdf5')\n\nprint('\\n\\nProcessing training labels\\n')\nconvert_hdf5('/kaggle/input/amex-default-prediction/train_labels.csv', WORKING_DIR + 'train_labels.hdf5')","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:19:16.749536Z","iopub.execute_input":"2022-08-07T23:19:16.750206Z","iopub.status.idle":"2022-08-07T23:27:52.847553Z","shell.execute_reply.started":"2022-08-07T23:19:16.750146Z","shell.execute_reply":"2022-08-07T23:27:52.845030Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Basic Preprocessing**\n\nRead train data and labels.","metadata":{}},{"cell_type":"code","source":"train_data = vaex.open(WORKING_DIR + 'train_data.hdf5')\ntrain_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:28:39.970510Z","iopub.execute_input":"2022-08-07T23:28:39.971266Z","iopub.status.idle":"2022-08-07T23:28:40.599793Z","shell.execute_reply.started":"2022-08-07T23:28:39.971211Z","shell.execute_reply":"2022-08-07T23:28:40.598395Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels = vaex.open(WORKING_DIR + 'train_labels.hdf5')\ntrain_labels.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:28:53.909592Z","iopub.execute_input":"2022-08-07T23:28:53.910050Z","iopub.status.idle":"2022-08-07T23:28:53.929023Z","shell.execute_reply.started":"2022-08-07T23:28:53.910013Z","shell.execute_reply":"2022-08-07T23:28:53.927886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Add labels to train data.","metadata":{}},{"cell_type":"code","source":"train_data = train_data.join(train_labels, how='inner', left_on ='customer_ID', right_on='customer_ID')","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:28:58.125107Z","iopub.execute_input":"2022-08-07T23:28:58.125534Z","iopub.status.idle":"2022-08-07T23:29:00.228842Z","shell.execute_reply.started":"2022-08-07T23:28:58.125498Z","shell.execute_reply":"2022-08-07T23:29:00.227838Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Getting Customer's Latest Transaction**\n\nAfter some very basic analysis, I realized that the training data contains multiple transactions (rows) for the same customer. I have chosen to use the latest transaction for each customer for my model which was also recommended in a lot of discussion threads. To achieve this, I group the data by customer_ID and get the latest date (max of column S_2, determined by looking through the columns and some data). ","metadata":{}},{"cell_type":"code","source":"train_data['S_2_date'] = train_data['S_2'].astype('datetime64[ns]')  # for easily finding latest date\nlatest = train_data.groupby(by='customer_ID').agg({'S_2_date': 'max'})\n\nlatest.export_csv('/kaggle/working/latest_matcher.csv')\nlatest = vaex.read_csv('/kaggle/working/latest_matcher.csv', copy_index=False)  # using the latest variable directly caused an issue with the S_2 column when joining below","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:29:03.250825Z","iopub.execute_input":"2022-08-07T23:29:03.251610Z","iopub.status.idle":"2022-08-07T23:29:09.564557Z","shell.execute_reply.started":"2022-08-07T23:29:03.251543Z","shell.execute_reply":"2022-08-07T23:29:09.563282Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Add column to join on and create final training dataframe.","metadata":{}},{"cell_type":"code","source":"train_data['latest_matcher'] = train_data['customer_ID'] + train_data['S_2']\nlatest['latest_matcher'] = latest['customer_ID'] + latest['S_2_date']\n\nfinal_data = train_data.join(latest, how='inner', left_on ='latest_matcher', right_on='latest_matcher', rsuffix='right')","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:29:12.514012Z","iopub.execute_input":"2022-08-07T23:29:12.514420Z","iopub.status.idle":"2022-08-07T23:29:14.756148Z","shell.execute_reply.started":"2022-08-07T23:29:12.514386Z","shell.execute_reply":"2022-08-07T23:29:14.754829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Remove extra working columns.","metadata":{}},{"cell_type":"code","source":"final_data.drop(['S_2_date', 'latest_matcher', 'customer_IDright', 'S_2_dateright', 'latest_matcherright'], inplace=True)\n\nprint('Rows: {}, \\nColumns: {}'.format(final_data.shape[0], final_data.shape[1]))","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:29:20.244986Z","iopub.execute_input":"2022-08-07T23:29:20.246219Z","iopub.status.idle":"2022-08-07T23:29:20.260172Z","shell.execute_reply.started":"2022-08-07T23:29:20.246148Z","shell.execute_reply":"2022-08-07T23:29:20.258733Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The data should fit into memory now therefore, .csv file will suffice.","metadata":{}},{"cell_type":"code","source":"final_data.export_csv('/kaggle/working/latest_transact.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:29:22.950643Z","iopub.execute_input":"2022-08-07T23:29:22.951807Z","iopub.status.idle":"2022-08-07T23:32:09.799457Z","shell.execute_reply.started":"2022-08-07T23:29:22.951760Z","shell.execute_reply":"2022-08-07T23:32:09.797789Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Cleanup**","metadata":{}},{"cell_type":"code","source":"os.remove('latest_matcher.csv')\n\n# can keep if needed\nos.remove('train_data.hdf5')\nos.remove('train_labels.hdf5')","metadata":{"execution":{"iopub.status.busy":"2022-08-07T23:33:59.941625Z","iopub.execute_input":"2022-08-07T23:33:59.942158Z","iopub.status.idle":"2022-08-07T23:33:59.983730Z","shell.execute_reply.started":"2022-08-07T23:33:59.942115Z","shell.execute_reply":"2022-08-07T23:33:59.981608Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Hope the guide was helpful to someone out there. Have a lovely day/evening!","metadata":{}}]}