{"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":"# Convert amex data to feather format, staying under 16GB memory ceiling\n\nThis notebook shows how this conversion can be done, running successfully in a default kaggle instance (which has a maximum of 16 GB memory)\n\nThis is done by chunking the read of the csv file, converting each chunk to more memory efficient data types, writing each chunk to a feather file, and later loading all of the chunks into a single data frame and saving them again.","metadata":{}},{"cell_type":"markdown","source":"# Imports","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport time\nimport datetime\nimport gc","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:04:30.375142Z","iopub.execute_input":"2022-06-16T19:04:30.375687Z","iopub.status.idle":"2022-06-16T19:04:30.390337Z","shell.execute_reply.started":"2022-06-16T19:04:30.375588Z","shell.execute_reply":"2022-06-16T19:04:30.389505Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Define data frame conversion\nThis routine sets all floating point values to half precision (float16), encodes categorical columns as category type, converts the date format to an integer (measuring number of days since 1/1/2000), and converts the customer_ID to a 64 bit integer. I separately verified that no customer ID's in the data conver to the same 64 bit integer.","metadata":{}},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"def customer_ID_hash(customer_ID):\n    # convert customer ID to 64 bit integer\n    return np.int64([int(s[49:],16) for s in customer_ID])\ndef convert_data_frame(df):\n    conv_d={}\n    # convert floating point to 16 bit\n    for n,col in enumerate(df.columns):\n        if df[col].dtype==np.float64:\n            out_type=np.float16\n        else:\n            out_type=df[col].dtype\n        conv_d[col]=out_type\n\n    categories = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\n    for col in categories:\n        if col in df.columns:\n            conv_d[col]='category'\n    # B_31 is binary, represent as uint8\n    if 'B_31' in df.columns:\n        conv_d['B_31']=np.uint8    \n\n    dfr=df.astype(conv_d)\n    \n    if 'S_2' in df.columns:\n        dfr.S_2=((pd.to_datetime(df.S_2)-datetime.datetime(2000,1,1)).view(int)//(1000000000*3600*24)).astype('int16')\n    if 'customer_ID' in df.columns:\n        dfr.customer_ID=customer_ID_hash(df.customer_ID)\n    return dfr\n","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:04:30.556095Z","iopub.execute_input":"2022-06-16T19:04:30.556730Z","iopub.status.idle":"2022-06-16T19:04:30.565706Z","shell.execute_reply.started":"2022-06-16T19:04:30.556689Z","shell.execute_reply":"2022-06-16T19:04:30.564724Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Convert to feather chunks\n\nThis code loads a csv file (using chunking), converts each chunk to a feather file and writes it to disk.","metadata":{}},{"cell_type":"code","source":"# chunk\ndef convert_and_write_feather_chunks(csvfile,stub='tmp'):\n    tstart=time.time()\n    with pd.read_csv(csvfile,chunksize=1000000) as reader:\n        for n,chunk in enumerate(reader):\n            chunk.reset_index(inplace=True)\n            chunk_c=convert_data_frame(chunk)\n            fname='%s%05d.feather'%(stub,n)\n            chunk_c.to_feather(fname)\n            print('writing chunk',n,fname)\n            del chunk\n            del chunk_c\n            gc.collect()\n    maxchunk=n\n    return maxchunk","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:04:30.837867Z","iopub.execute_input":"2022-06-16T19:04:30.838186Z","iopub.status.idle":"2022-06-16T19:04:30.845393Z","shell.execute_reply.started":"2022-06-16T19:04:30.838158Z","shell.execute_reply":"2022-06-16T19:04:30.844274Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"# Load feather chunks\nThis code loads a series of feather files, and concatenates them into a single data frame","metadata":{}},{"cell_type":"code","source":"def load_feather_chunks(maxchunk,stub='tmp'):\n    chunk_c_list=[]\n    gc.collect()\n    for n in range(maxchunk+1):\n        fname='%s%05d.feather'%(stub,n)\n        print('loading chunk',n,fname)\n        chunk_c_list.append(pd.read_feather(fname))\n    odf=pd.concat(chunk_c_list)\n    chunk_c_list=[]\n    gc.collect()\n    odf.reset_index(inplace=True)\n    odf.drop(['level_0','index'],axis=1,inplace=True)\n    return odf\n","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:04:30.955950Z","iopub.execute_input":"2022-06-16T19:04:30.956363Z","iopub.status.idle":"2022-06-16T19:04:30.962723Z","shell.execute_reply.started":"2022-06-16T19:04:30.956330Z","shell.execute_reply":"2022-06-16T19:04:30.961934Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"# Apply code to test_data, train_data\nNow, call these routines on the test_data and train_data csv files","metadata":{}},{"cell_type":"code","source":"tstart=time.time()\nmaxchunk=convert_and_write_feather_chunks('../input/amex-default-prediction/train_data.csv',stub='tmp_train_data')\ngc.collect()\nelapsed_time=time.time()-tstart\nprint(elapsed_time)","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:12:30.662141Z","iopub.execute_input":"2022-06-16T19:12:30.662579Z","iopub.status.idle":"2022-06-16T19:19:30.032370Z","shell.execute_reply.started":"2022-06-16T19:12:30.662543Z","shell.execute_reply":"2022-06-16T19:19:30.031022Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tstart=time.time()\nodf=load_feather_chunks(maxchunk,stub='tmp_train_data')\nodf.to_feather('train_data.feather')\nodf=''\ngc.collect()\nelapsed_time=time.time()-tstart\nprint(elapsed_time)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tstart=time.time()\nmaxchunk=convert_and_write_feather_chunks('../input/amex-default-prediction/test_data.csv',stub='tmp_test_data')\ngc.collect()\nprint(elapsed_time)","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:19:31.283370Z","iopub.execute_input":"2022-06-16T19:19:31.283697Z","iopub.status.idle":"2022-06-16T19:32:31.583492Z","shell.execute_reply.started":"2022-06-16T19:19:31.283669Z","shell.execute_reply":"2022-06-16T19:32:31.581982Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tstart=time.time()\nodf=load_feather_chunks(maxchunk,stub='tmp_test_data')\nodf.to_feather('test_data.feather')\nodf=''\ngc.collect()\nelapsed_time=time.time()-tstart\nprint(elapsed_time)","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:32:31.585114Z","iopub.execute_input":"2022-06-16T19:32:31.585431Z","iopub.status.idle":"2022-06-16T19:33:35.390650Z","shell.execute_reply.started":"2022-06-16T19:32:31.585403Z","shell.execute_reply":"2022-06-16T19:33:35.389420Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Convert train_labels\nThis file was small enough that no chunking is needed, so just load the entire file, convert the data frame, and save as a feather file.","metadata":{}},{"cell_type":"code","source":"df_train_labels=pd.read_csv('../input/amex-default-prediction/train_labels.csv')\ndf_train_labels_c=convert_data_frame(df_train_labels)\ndf_train_labels_c.to_feather('train_labels.feather')\ndel df_train_labels\ndel df_train_labels_c\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:19:30.034150Z","iopub.execute_input":"2022-06-16T19:19:30.034593Z","iopub.status.idle":"2022-06-16T19:19:31.282162Z","shell.execute_reply.started":"2022-06-16T19:19:30.034556Z","shell.execute_reply":"2022-06-16T19:19:31.280951Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Clean up\nDelete temporary chunked feather files","metadata":{}},{"cell_type":"code","source":"!rm tmp_test_data*\n!rm tmp_train_data*","metadata":{"execution":{"iopub.status.busy":"2022-06-16T19:50:22.056116Z","iopub.execute_input":"2022-06-16T19:50:22.057327Z","iopub.status.idle":"2022-06-16T19:50:23.946071Z","shell.execute_reply.started":"2022-06-16T19:50:22.057192Z","shell.execute_reply":"2022-06-16T19:50:23.944520Z"},"trusted":true},"execution_count":null,"outputs":[]}]}