{"cells":[{"metadata":{"_uuid":"30e304a9d2243b94a01b668d7ad8ebb219e00bf8"},"cell_type":"markdown","source":"# This notebook is made to expolain how to reduce size of big CSV data and make processing faster"},{"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\nimport glob\nlistBigCSV = glob.glob(\"../input/NGS*csv\")\nlistBigCSV\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"22679cb22cbe349c2e2ae8aeb2d2cbb30544a902"},"cell_type":"markdown","source":"## Downcasting for smaller dataset\nLet's load the first dataset.\nWe can us C engine to load faster those big files and take pandas function memory_usage to see what we gain. \nA conversion from bytes to megabytes is needed. So a division by 1024 ** 2 is need."},{"metadata":{"trusted":true,"_uuid":"40ddcc860974234dd3147f7d57b3659716472cd3"},"cell_type":"code","source":"def memory(df):\n    if isinstance(df,pd.DataFrame):\n        value = df.memory_usage(deep=True).sum() / 1024 ** 2\n    else: # we assume if not a df it's a series\n        value = df.memory_usage(deep=True) / 1024 ** 2\n    return value, \"{:03.2f} MB\".format(value)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a73578b0bafe6363c1051372e45f6b630d14ef74"},"cell_type":"code","source":"df = pd.read_csv(listBigCSV[2], engine='c')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"754e1a465650e174cae09d06b73fed6697eecc96"},"cell_type":"code","source":"df.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d3188a201153df5e710bbc7803714c30645c527a"},"cell_type":"markdown","source":"What kind of types do we have ?"},{"metadata":{"trusted":true,"_uuid":"ab1e16f3f10898fb4142704b3df92fe7f4c63c9b"},"cell_type":"code","source":"df.dtypes","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"33c15dba66c0f9bb69ea48ad13ab8457c8e3db4b"},"cell_type":"markdown","source":"We can select int64 and make them matching both uint8 which takes 1 byte or uint16 (2 bytes).\nSelecting them and applying a smaller types gives:"},{"metadata":{"trusted":true,"_uuid":"67ddf4a9b6a4d7f9afbff086d72eeadd124d1bfc"},"cell_type":"code","source":"dfIntSelection = df.select_dtypes(include=['int'])\ndfConverted2int = dfIntSelection.apply(pd.to_numeric,downcast='unsigned')\nmemInt, memIntTxt=  memory(dfIntSelection)\nmemIntDownCast, memIntDownCastTxt = memory(dfConverted2int)\n\nprint(memIntTxt)\nprint(memIntDownCastTxt)\nprint('Gain: ', memInt/memIntDownCast *100.0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"636be56766a125ee4918d89bbbce7cbd5a7e92fc"},"cell_type":"code","source":"dfConverted2int.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fee0b5f78e3ae7451e988bbb8b90d58cf9268a6e"},"cell_type":"markdown","source":"A gain of 400% is observed. Wich really good !\nInteger might also be seen as category. It may be intersting if a convertion to a category is a better choice."},{"metadata":{"trusted":true,"_uuid":"d0a7231f7b903cc957cf77173b93bf1471fbdec5"},"cell_type":"code","source":"dfIntSelection = df.select_dtypes(include=['int'])\ndfConverted2int = dfIntSelection.astype('category')\nmemInt, memIntTxt=  memory(dfIntSelection)\nmemIntDownCast, memIntDownCastTxt = memory(dfConverted2int)\n\nprint(memIntTxt)\nprint(memIntDownCastTxt)\nprint('Gain: ', memInt/memIntDownCast *100.0)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b2c6833072d86e5f8401f653a885b2c342b4292b"},"cell_type":"markdown","source":"We gain 600%  ! We can apply this methods on float too and downcast them to float."},{"metadata":{"trusted":true,"_uuid":"c721b2c1370b5d8ea10877cea2665790f383ca39"},"cell_type":"code","source":"dfFloatSelection = df.select_dtypes(include=['float'])\ndfConverted2float = dfFloatSelection.apply(pd.to_numeric,downcast='float')\nmemInt, memIntTxt=  memory(dfFloatSelection)\nmemIntDownCast, memIntDownCastTxt = memory(dfConverted2float)\n\nprint(memIntTxt)\nprint(memIntDownCastTxt)\nprint('Gain: ', memInt/memIntDownCast *100.0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bc892a1f6a6860eebf2b33265ad92c7695026cd1"},"cell_type":"markdown","source":"Again, the gain is about 200% !"},{"metadata":{"_uuid":"acc3d00d21d910a14d3e17b95733bf99654e8b27"},"cell_type":"markdown","source":"There is two types left: Time and Event.\nFirst can be converted to date time. The second might be a category. "},{"metadata":{"trusted":true,"_uuid":"88033b84a63bcaa83b5801e25844987e6d8d4447"},"cell_type":"code","source":"dfTime = df.Time \ndate_format = '%Y-%m-%d %H:%M:%S.%f'\ndfTimeConvert = pd.to_datetime(dfTimeConvert,format=date_format)\n\nmem, memTxt = memory(dfTime)\nmemConv, memConvTxt = memory(dfTimeConvert)\n\nprint(memTxt)\nprint(memConvTxt)\nprint('Gain: ', mem/memConv *100.0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"212156e1a2d931381d953ff09803792465228c09"},"cell_type":"markdown","source":"We gain more than 1000% converting it."},{"metadata":{"trusted":true,"_uuid":"dc0de7188b2d27b3f28bb26390f7fcb43bc70581"},"cell_type":"markdown","source":"# Do we need to do a special function for this ?\nPandas provides a way to give data types upfront while reading csv file. So we can use it. "},{"metadata":{"trusted":true,"_uuid":"969114c8e6a7ef71ebc8be3e846394f3ce8b2050"},"cell_type":"code","source":"dtypes = df.drop('Time',axis=1).dtypes\n\ndtypes_col = dtypes.index\ndtypes_type = [i.name for i in dtypes.values]\n\ncolumn_types = dict(zip(dtypes_col, dtypes_type))\npreview = first2pairs = {key:value for key,value in list(column_types.items())[:10]}\nimport pprint\npp = pp = pprint.PrettyPrinter(indent=4)\npp.pprint(preview)\ndfDownCast =pd.read_csv(listBigCSV[2],dtype=column_types,parse_dates=['Time'], infer_datetime_format=True)\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":false,"_kg_hide-output":false,"trusted":true,"_uuid":"ac31555e23cde0f28d2496070157100a77fe5fc6"},"cell_type":"code","source":"dfDownCast","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f99f16ad3b5744d35c750ca13aa869aa58f855f5"},"cell_type":"code","source":"memInt, memIntTxt=  memory(df)\nmemIntDownCast, memIntDownCastTxt = memory(dfDownCast)\n\nprint(memIntTxt)\nprint(memIntDownCastTxt)\nprint('Gain: ', memInt/memIntDownCast *100.0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"378b0cfbfdad95b3afd75511e6070d4448857bbd"},"cell_type":"code","source":"dfDownCast.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5dd09edaf807cddc43688bc7bdff4a3ff09fa89a"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"79967c93bf1abf53069cf2233e7e79decebf63af"},"cell_type":"code","source":"def downCast(df):\n    date_format = '%Y-%m-%d %H:%M:%S.%f'\n    converted_obj = df.select_dtypes(include=['int']).astype('category')\n    df[converted_obj.columns] = converted_obj\n    converted_obj = df.select_dtypes(include=['float']).apply(pd.to_numeric,downcast='float')\n    df[converted_obj.columns] = converted_obj\n    if 'Time' in df:\n        df.Time = pd.to_datetime(df.Time,format=date_format)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"27be18ab8e475edb1406435249eda8a7b46fd115"},"cell_type":"code","source":"dfDown = df.copy()\ndfDown = downCast(dfDown)\n\nmemInt, memIntTxt=  memory(df)\nmemIntDownCast, memIntDownCastTxt = memory(dfDown)\n\nprint(memIntTxt)\nprint(memIntDownCastTxt)\nprint('Gain: ', memInt/memIntDownCast *100.0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b6c484ba6231431af3b78b68cd2c2dd939b1ad6"},"cell_type":"markdown","source":"270% of space gain is really important. Now we can save it as pkl for the next time. And do that for every big dataset."},{"metadata":{"trusted":true,"_uuid":"6bb9f513e1c9bb314415eb400dcedfa533ab8d99"},"cell_type":"code","source":"for csvFile in listBigCSV:\n    dataframe = pd.read_csv(csvFile, engine='c')\n    dataframe = downCast(dataframe)\n    dataframe.to_pickle(os.path.basename(csvFile[:-4]+'.pkl'))\n    del dataframe","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"os.path.basename('../input/NGS-2016-reg-wk7-12.pkl')","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}