{"cells":[{"metadata":{"_uuid":"f8108297891f09ea08fdbccc726c61ef921fabc8"},"cell_type":"markdown","source":"### Preprocessing of 29 million transaction records\n\nThe dataset provides access to 2 files. One of the files `new_merchant_transactions.csv` contains over 29million records. Pandas is considered as a great choice for solving data science problems. It provides some unique features like automatically detecting the datatype of attributes but it's not that efficient. Here comes the role of data engineers to correctly cast datatype for each of the attribute. \n\nIn this notebook, I will be sharing my approach of reducing the size of a pandas df for further Machine Learning/Deep Learning implementation on it. \nI achieved reduction in the dataframe's memory usage to around 8% of original size. "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"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 in \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 \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"13de4b83cbb69f092a42d4d9f668e6e531ce0a83"},"cell_type":"markdown","source":"### Let's read the historical_transactions\n- Read the csv as a pandas dataframe using `read_csv()` function of pandas\n- Get info about each of the attribute of that dataframe along with memory usage using `info()` function."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"historical_tx_df = pd.read_csv('../input/historical_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7b75178401dcf1b35eef688aefc5371c9566feb3"},"cell_type":"code","source":"historical_tx_df.info(memory_usage='deep')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"93b224d2f6445cf50cf94183dd3499087262fce4"},"cell_type":"code","source":"for dtype in ['float','int','object']:\n    selected_dtype = historical_tx_df.select_dtypes(include=[dtype])\n    mean_usage_b = selected_dtype.memory_usage(deep=True).mean()\n    mean_usage_mb = mean_usage_b / 1024 ** 2\n    print(\"Average memory usage for {} columns: {:03.2f} MB\".format(dtype,mean_usage_mb))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"984c59d0c228099c35ac559f5fd2c7ec168fa0b5"},"cell_type":"code","source":"def mem_usage(pandas_obj):\n    if isinstance(pandas_obj,pd.DataFrame):\n        usage_b = pandas_obj.memory_usage(deep=True).sum()\n    else: # we assume if not a df it's a series\n        usage_b = pandas_obj.memory_usage(deep=True)\n    usage_mb = usage_b / 1024 ** 2 # convert bytes to megabytes\n    return \"{:03.2f} MB\".format(usage_mb)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cdb76c3db696fe68f98542db890a71174c3f3f81"},"cell_type":"code","source":"'''\nFunction to count unique values in the columsn. \n\nArguments:\ndf_columns: List of columns in `his_int` dataframe\n'''\n\ndef count_unique( df_columns):\n    for i in df_columns:\n        print(\"Number of unique vals in\",i,\"are\",len(his_int[i].unique()))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bce4e161ef48f5ccb1e3c53a0dda9674af71ed21"},"cell_type":"markdown","source":"### Preprocessing of Integer columns \nThe dataframe reads these columns as integer `city_id, installments, merchant_category_id, month_lag, state_id,subsector_id`. As you can see most of the columns are contain specific ids. These can be mapped to categorical data. \n\nIn the next couple of code cells, I will be downcasting the integer columns to category. "},{"metadata":{"trusted":true,"_uuid":"b81b141ee42da301f852f4a3ea8d099473117e1b"},"cell_type":"code","source":"his_int = historical_tx_df.select_dtypes(include=['integer'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2e236f217c793cc69121a6d89fe355f3ddfc6ab5"},"cell_type":"code","source":"count_unique(his_int.columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8196614c3175af3eaacd0b1edcc1fd230a302ab0"},"cell_type":"code","source":"converted_obj = pd.DataFrame()\n\nfor col in his_int.columns:\n    num_unique_values = len(his_int[col].unique())\n    num_total_values = len(his_int[col])\n    if num_unique_values / num_total_values < 0.5:\n        converted_obj.loc[:,col] = his_int[col].astype('category')\n    else:\n        converted_obj.loc[:,col] = his_int[col]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9ae9623501c7ce4e4ca952c032276bf9a0f24f4c"},"cell_type":"markdown","source":"The integer columns memory usage is now reduced from 1332 MB to 222 MB."},{"metadata":{"trusted":true,"_uuid":"b4b4bbf546bb852fb8ad290da1f767afc77c779e"},"cell_type":"code","source":"print(mem_usage(his_int))\nprint(mem_usage(converted_obj))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8a4950095ec3997a2ffbf1514759f66233b29c0"},"cell_type":"code","source":"converted_obj.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1b186cf6296391320536ca86e8d8390eb30e86a0"},"cell_type":"markdown","source":"### Creation of optimized transaction records dataframe\nWe will be creating a new dataframe `optimized_hist_df` and mapping all the processed columns to that dataframe. "},{"metadata":{"trusted":true,"_uuid":"040b77db22919aa2837e63cb00c57d42dcd24a6b"},"cell_type":"code","source":"optimized_hist_df = pd.DataFrame()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1cf5e16a5647ff4849584113ab2656718fd00fcc"},"cell_type":"code","source":"optimized_hist_df[converted_obj.columns] = converted_obj\nmem_usage(optimized_hist_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9a7976cba721ce4aac38e2574c487269ab8e35f1"},"cell_type":"code","source":"his_int = historical_tx_df.select_dtypes(include=['float'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8e86921fd8ba227469d982e3f15f2120ec6b1d24"},"cell_type":"code","source":"his_int_na = his_int.category_2.fillna(6)\nconv_obj = his_int_na.astype('int')\nmem_usage(conv_obj)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"00565ef59be5077ba26ed3bbf29c14cc3609bfc7"},"cell_type":"code","source":"conv_obj2 = conv_obj.astype('category')\nmem_usage(conv_obj2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"af9edaead1d4d11724e7a5b4e675628fb79f7a8b"},"cell_type":"code","source":"his_int[\"category_2\"] = conv_obj2","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5655b4d5c84c49193a654b87596626c615d84acb"},"cell_type":"code","source":"optimized_hist_df[his_int.columns] = his_int\nmem_usage(optimized_hist_df)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cccf8cb68906d413997df99037c0b1427a33645f"},"cell_type":"code","source":"his_int = historical_tx_df.select_dtypes(include=['object'])\nhis_int.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c85a42493c7634a098c647acd3d106f5df56461c"},"cell_type":"code","source":"count_unique(his_int.columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1b69cf2febe58b19c63cfe3846f455c895e5ea06"},"cell_type":"code","source":"converted_obj = pd.DataFrame()\n\nfor col in his_int.columns:\n    num_unique_values = len(his_int[col].unique())\n    num_total_values = len(his_int[col])\n    if num_unique_values/num_total_values < 0.33:\n        converted_obj.loc[:,col] = his_int[col].astype('category')\n    else:\n        converted_obj.loc[:,col] = his_int[col]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2dd23af6642c1ab8a3c0b1958e5dc5490bc3f18a"},"cell_type":"code","source":"print(mem_usage(his_int))\nprint(mem_usage(converted_obj))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d01432c4b7e0e69accb7e5353ba2bd46087b4e92"},"cell_type":"code","source":"optimized_hist_df[converted_obj.columns] = converted_obj\nmem_usage(optimized_hist_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"96fe298e81627700f30f3e989f31ce1913e54758"},"cell_type":"code","source":"optimized_hist_df['purchase_date'] =  pd.to_datetime(historical_tx_df.purchase_date, format='%Y%m%d %H:%M:%S')\nmem_usage(optimized_hist_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e067fed7fd7e523deb80ab2e289aa102738031bf"},"cell_type":"code","source":"optimized_hist_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a0ab214b0a081337cde038ec24bcfc9c1d423401"},"cell_type":"markdown","source":"## Reduction in the size of our dataframe"},{"metadata":{"trusted":true,"_uuid":"4ff75f8f1d0834a9d5c9e861b0d5d74458d7f93e"},"cell_type":"code","source":"per_df_red = (float(mem_usage(optimized_hist_df).split()[0]) / float(mem_usage(historical_tx_df).split()[0]) )*100\nper_df_red","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3531418e2884a260303f8f3452354b6e77191593"},"cell_type":"markdown","source":"**As per the above computation, you can clearly see that we have reduced the 13+GB pandas dataframe to 1064MB dataframe. This leads to a reduction in size to around 8%.**"},{"metadata":{"trusted":true,"_uuid":"a1085f8f0a142b5dc586ceebac0097882adb12eb"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}