{"cells":[{"metadata":{"_uuid":"73be65e63702c4b15027d45c7dc92b26384a38f1"},"cell_type":"markdown","source":"# Understanding and testing perfomance of pandas.read_csv"},{"metadata":{"_uuid":"53cabf3bad5464d002a700fa098d88ec6557e476"},"cell_type":"markdown","source":"Comma Separated Values (CSVs) are widely used to read, write and transfer data for machine learning applications hence pandas.read_csv is the starting point of many data analysis and machine learning projects thus it deems it necessary to have a good insight on it's working and perfomance, let's look under it's hood and explore it's working and performance. You may refer the [documentation](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) and source code to have a more indepth knowledge. "},{"metadata":{"trusted":true,"_uuid":"f9d7c49ab91f064ab5a3ee12816e3bb1a3f90168"},"cell_type":"code","source":"import pandas as pd\nimport numpy as np","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3e2e61d7bf616899dafe4b571d0f0115c2305a89"},"cell_type":"markdown","source":"Let's first explore it's performance\nadd *%time* before a statement in jupyter notebook to time it, it's a handy tool to evaluate performance. \n### Starting out with all the default parameters"},{"metadata":{"trusted":true,"_uuid":"3a5f6bea1b06cc189acdfaaf9948f1c7bd80a166","scrolled":true},"cell_type":"code","source":"%time df=pd.read_csv('../input/csv-large/csv_large.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"866fcc228dcc9c703d7ee982ba12e647fee0c183"},"cell_type":"markdown","source":"Here's what our data looks like"},{"metadata":{"trusted":true,"_uuid":"69cda399b17a107d78c2b273f6d89b5fd72ac443"},"cell_type":"code","source":"df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9e85399f5d058f966fce67f832c03bb699d8888c"},"cell_type":"markdown","source":"python is a memory hog and anything written in pure python is expected to be upto [400 times slower](https://stackoverflow.com/questions/801657/is-python-faster-and-lighter-than-c) than native C code. Which is why most of pandas and numpy or any other scientifc calculation module functions are written in either cython or C. Let's assert this fact again.\n### Using the python engine / parser to read the file"},{"metadata":{"trusted":true,"_uuid":"5b4b33a7133678626eea1b9d2e75302b918f9a8a"},"cell_type":"code","source":"%time df=pd.read_csv('../input/csv-large/csv_large.csv',engine='python')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7010919235801a8e899a770ab85f30f427c43dc2"},"cell_type":"markdown","source":"### Using the C engine / parser (default)\nTo show the remarkable time difference, lets run using the default engine (C) by explicitly passing it in the arguments"},{"metadata":{"trusted":true,"_uuid":"a61944ddc4436a4124af964fe33f78f901880185"},"cell_type":"code","source":"%time df=pd.read_csv('../input/csv-large/csv_large.csv',engine='c')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"63f8f39d07f1aa842f53819d38b50598190e999f"},"cell_type":"markdown","source":"42.5s vs 3.57s, C parser is more than 10 times better\n## Why even use python parser?\nOn reading the pandas.read_csv [documentation](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) i realized, python parser even though is much slower, provides a few functionalities that C parser does not. Quoting the docs \n>The C engine is faster while the python engine is currently more feature-complete. \n\nWhat are these features?\n- read_csv has another parameter called \"sep\" which by default is ',' which very well makes sense because after all, we are reading \"comma seperated files (CSV)\" but if in some rare cases the seperator is not ',' rather something else, lets say period(.) then the C parser can not automatically detect the seperator while the python parser engine can with it's build in sniffer tool called csv.Sniffer. Quoting the docs\n>if sep is None, the C engine cannot automatically detect the separator, but the Python parsing engine can, meaning the latter will be used and automatically detect the separator by Python’s builtin sniffer tool, csv.Sniffer.\n\n- Python parser has another great built-in feature that allows us to have a regex as a seperator which can be very useful in case of badly fomatted csv files. Qouting the docs \n>separators longer than 1 character and different from '\\s+' will be interpreted as regular expressions and will also force the use of the Python parsing engine. \n\n"},{"metadata":{"_uuid":"cdf6a35da2559209105512f30bceb6ceb8e1fd12"},"cell_type":"markdown","source":"Let's test it out"},{"metadata":{"trusted":true,"_uuid":"f8c67ed32b6079298ad713cbda702be92ac9793f"},"cell_type":"code","source":"df=pd.read_csv('../input/csv-large/csv_large.csv',engine='c',sep=None)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4447480a0a9444cc36ffd75d99ec2aaeb3844e78"},"cell_type":"markdown","source":"As expected, we get an error when trying to run C parser with sep=None, now lets try running it with python parser"},{"metadata":{"trusted":true,"_uuid":"d8a48cbd47cf39c36f7f39e129c785eb035a19ed"},"cell_type":"code","source":"df=pd.read_csv('../input/csv-large/csv_large.csv',engine='python',sep=None)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"872545b8d74e6bf5f5ddfc0b714f18c160c7c881"},"cell_type":"code","source":"df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0c3ed508f24864d43ef81e695df29e075774f361"},"cell_type":"markdown","source":"Not only we get no errors, python parser has very well read the data into a pd.DataFrame. Hence python parser has a greater support for such functionalities even though it's much slower compared to the C parser. "},{"metadata":{"_uuid":"85f8b540895e112381acb64a066362586c494e08"},"cell_type":"markdown","source":"### Setting verbose = True to get insights on time distribution"},{"metadata":{"trusted":true,"_uuid":"50cc8defbc25737c63356ce3de94540d4a67123e"},"cell_type":"code","source":"%time df=pd.read_csv('../input/csv-large/csv_large.csv',verbose=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"41be39983b2dc051723199156a5aad7e5ca27981"},"cell_type":"markdown","source":"**Tokenizer** and **type conversions** take most of the time, let's try to make it better\n1. **low_memory** Setting low_memory=False may reduce number of tokenizations which could have a better impact, because by default low_memory = True which allows the parser to internally break down the csv data into multiple chunks and then read it. \n2. **Explicitly specifying the dtypes of each column** shall reduce the overall type conversion time"},{"metadata":{"_uuid":"927a359a2abc43951ef2d75005720bad8ef2f67c"},"cell_type":"markdown","source":"# low_memory\nFirst, lets fiddle around with **low_memory** parameter.\nQouting the docs\n>**low_memory** : boolean, default True\nInternally process the file in chunks, resulting in lower memory use while parsing, but possibly mixed type inference. To ensure no mixed types either set False, or specify the type with the dtype parameter. "},{"metadata":{"trusted":true,"_uuid":"34c2b2e3aaf18bf73fed37631c44666ea724689f"},"cell_type":"code","source":"%time df=pd.read_csv('../input/csv-large/csv_large.csv',low_memory=False,verbose=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aeaecb7c12d945fdd03a972793a8d80b28c0fb19"},"cell_type":"markdown","source":"setting **low_memory = False** reduced the number of tokenizations but didn't make much progress in improving the parse time rather made it worse"},{"metadata":{"_uuid":"67d0701e7a31a5211914294ef465c35d530d4b10"},"cell_type":"markdown","source":"### Total tokenization time in case low_memory=True\nLets sum over the chunk tokenization time created due to **low_memory=True** (default) "},{"metadata":{"trusted":true,"_uuid":"0407178e3352fc3f91c2a208276efa3b3d9fd8d1"},"cell_type":"code","source":"def get_linedata(lines):\n    return [\n                    [line for line in lines if line.split()[0]=='Tokenization' ],\n                    [line for line in lines if line.split()[0]=='Type' ],\n                    [line for line in lines if line.split()[0]=='Parser' ],\n    ]\n\ndef sum_times():\n    total_tokenization=0\n    total_type=0\n    total_parser=0\n    with open('../input/token-time/tokenization_time.txt') as handle:\n        lines=handle.readlines()\n        tokenizations,types,parsers=get_linedata(lines)\n        for tz,ty,pr in zip(tokenizations,types,parsers):\n            total_tokenization+=float(tz[19:24])\n            total_type+=float(ty[22:26])\n            total_parser+=float(pr[28:32])\n    return {'total tokenization time':'{:.2f} ms'.format(total_tokenization),\n                    'total type conversion time':'{:.2f} ms'.format(total_type) ,\n                    'total parser time':'{:.2f} ms'.format(total_parser) \n           }     \n        \nsum_times()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"133a084e0e4000ed3429594857bb7cc285ccd455"},"cell_type":"markdown","source":"The total parser time does not seem to be a correct measure because within the small chunks made due to **low_memory=True** the parsing time was so less that it got lost by the 2 digit precision of time printed by the **verbose=True**, the third digit decimal would've had a great impact on the total sum of parser time. \nApart from that, total tokanization and total type conversion time are pretty insightful, turns out processing the whole file at once is **not a good way** of reading large csv files as far as the performance is concerned. Hence it's good to use the defaults and keep **low_memory = False**\n\n## When to use low_memory=True?\nLet's see an example using a much larger dataset which is from the infamous kaggle [problem](https://www.kaggle.com/c/bluebook-for-bulldozers/data)"},{"metadata":{"trusted":true,"_uuid":"c9851d090f62ba64ae02530a2b8e2b1dbd02828c"},"cell_type":"code","source":"%time df=pd.read_csv('../input/bluebook-for-bulldozers/train/Train.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a3d8df8f05a8c634aa14c580989c8476b929a474"},"cell_type":"markdown","source":"We get a DtypeWarning.\n\nColumns (13,39,40,41) have mixed types, let's see these columns and total number of columns"},{"metadata":{"trusted":true,"_uuid":"e43428def16c88602f48019ded3e7b39e8c74f58"},"cell_type":"code","source":"print(df.columns[13],df.columns[39],df.columns[40],df.columns[41])\nprint(f\"Total columns: {df.columns.size}\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b8df3637c20e50eea5e3ece2dd1bceca8897796e"},"cell_type":"code","source":"!wc -l ../input/bluebook-for-bulldozers/train/Train.csv","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4ec9c3bfa460b3558a0bb095af441b5e5d0c7daf"},"cell_type":"markdown","source":"There are 53 columns and 401126 rows in our dataset, giving dtypes to 53 columns while looking at 401126 rows for consistency is humanly impossible. In such cases reading files in chunks can cause DtypeWarning due to mix datatypes of columns. \nHence, we use **low_memory=False** when:-\n1. Inconsistent data type of a column because reading the csv all at once analyzes each column and accordingly assign a data type to that column\n2. Too many columns to hardcode their dtypes prior to reading the csv\n\nNow lets run the above with **low_memory=False**"},{"metadata":{"trusted":true,"_uuid":"c70141141a96f5c540ec99f8fba1d3e55a742396"},"cell_type":"code","source":"%time df=pd.read_csv('../input/bluebook-for-bulldozers/train/Train.csv',low_memory=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ba46209cb265f871848689e0eb648f7a960a344e"},"cell_type":"markdown","source":"reading_csv with **low_memory=False** is still slower but atleast we evaded the warning and our parser has a better understanding of the data hence it minimizes the chances of errors\n"},{"metadata":{"_uuid":"9be402686bc9f24e46c4384ff11d58804d53de8f"},"cell_type":"markdown","source":"# Explicitly specifying datatypes of columns"},{"metadata":{"_uuid":"e4a924b7d975875a4912b14b9bfd7b8468d6f70c"},"cell_type":"markdown","source":"Let's read a synthetically generated very large csv named **vv_large.csv**"},{"metadata":{"trusted":true,"_uuid":"bd7e2749969ae5f371c19d91caad31e585f34a3b"},"cell_type":"code","source":"!wc -l ../input/vv-large/vv_large.csv","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8423efd1398eebdf455779852466f073499b1921"},"cell_type":"markdown","source":"It has 17.2M lines! It's massive"},{"metadata":{"_uuid":"b653d8305218343e9db3093772730182c126abea"},"cell_type":"markdown","source":"Let's see what data type has our parser given to the columns of our synthetic csv by itself. We are naming of columns as names_. Passing **names=columns names**  names the columns according to passed list **column names**"},{"metadata":{"trusted":true,"_uuid":"152436c5a001bb76988d437a260d9e33b99f5e79"},"cell_type":"code","source":"names_=['id','name','sex']\n%time df=pd.read_csv('../input/vv-large/vv_large.csv', names=names_)\nprint(\n    type(df.id[2]),\n    type(df.sex[2]),\n    type(df.name[2]),\n     )","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"729dd8e4589614da490e4d97e9fcc175cf1c908f"},"cell_type":"markdown","source":"This is what the csv looks like, I synthetically generated it by appending the same file in a very large python loop"},{"metadata":{"trusted":true,"_uuid":"68ba54f1b847ef1d1ad7e117897452de99365c5e"},"cell_type":"code","source":"df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"589aee9153dcea2a177a92c1df45c63044cf4b96"},"cell_type":"markdown","source":"## Explicit type passing\nThere's no need to have a 64bit integer to store the id (since it's just a single digit integer), it would be rather wiser to use an 8 bit integer for 'id' and str type for both name and sex"},{"metadata":{"trusted":true,"_uuid":"b5c8c57b0753360ac31eef97a19d68d0dcc0e076"},"cell_type":"code","source":"names_=['id','name','sex']\ndtypes={'id':np.int8,'name':'str','sex':'str'}\n%time df=pd.read_csv('../input/vv-large/vv_large.csv',names=names_,dtype=dtypes)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fa34d4a512137ba09ab16e18b67e78da93bff4d4"},"cell_type":"markdown","source":"- As expected an improvement in the performance is observed, compared to earlier when datatypes were not not passed explicitly in the parameters\nLet's now try turning off **low_memory** while specifying the data types"},{"metadata":{"trusted":true,"_uuid":"024f90e4693162aaf7ab78f5fc93de1b0040521c"},"cell_type":"code","source":"names_=['id','name','sex']\ndtypes={'id':np.int32,'name':'str','sex':'str'}\n%time df=pd.read_csv('../input/vv-large/vv_large.csv',names=names_,dtype=dtypes,low_memory=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"026e427de18d9d9f46c11359fd6b68c1e665ef0c"},"cell_type":"markdown","source":"**low_memory=False** is slow as expected and always\n### Set datatypes as objects\n- Let's try specifying the datatypes of each column as 'object'. \n- It should be computationally harder because this is pretty much asking for more data type conversions"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"cda6f793d8a32aeef43e4940f8d3507a317c84b1"},"cell_type":"code","source":"names_=['id','name','sex']\ndtypes={'id':'object','name':'object','sex':'object'}\n%time df=pd.read_csv('../input/vv-large/vv_large.csv',names=names_,dtype=dtypes)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d2af11933b70ba9eecc0c65ac25d9a1be08ef0d"},"cell_type":"code","source":"names_=['id','name','sex']\ndtypes={'id':'object','name':'object','sex':'object'}\n%time df=pd.read_csv('../input/vv-large/vv_large.csv',names=names_,dtype=dtypes,verbose=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6de667001bbb1634c31abab7315d96016e1ae250"},"cell_type":"markdown","source":"As expected, the runtime is much larger, mostly due to time wasted in type conversion from \"object\" to integer and string"},{"metadata":{"_uuid":"b08f8723b4ca3cd34ef6e7fc4434549661ca8589"},"cell_type":"markdown","source":"# Reading really really large and massive CSV files\ntrain.csv is a very large CSV i got i from this kaggle [problem](https://www.kaggle.com/c/favorita-grocery-sales-forecasting/data)"},{"metadata":{"trusted":true,"_uuid":"7bae2c9d5ccaf8ced300f0d480400e935a77e18c"},"cell_type":"code","source":"!ls -sh ../input/favorita-grocery-sales-forecasting/train.csv","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"76065dc10b2b1ffb5b992f951dec82a2e43cdc56"},"cell_type":"code","source":"!wc -l ../input/favorita-grocery-sales-forecasting/train.csv","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"849a30e866b7797860c84b69c0f8b0e00ab1be7c"},"cell_type":"markdown","source":"125.4M lines is a lot, the file size is 4.7Gb which is huge! \n\nReading such a file at once causes my browser kernel to stop responding so i will be pasting my linux terminal sceenshot here (I really don't really like using jupyter notebooks, here's  [theme](https://github.com/TimeTraveller-San/OCD_fix/tree/master/jupyter_moe) i made for it to make it bearable)"},{"metadata":{"_uuid":"dc1a505436438ab64895d951b6ff8824734ebcd8"},"cell_type":"markdown","source":"![alt text](https://i.imgur.com/54Df6r9.png \"Title\")"},{"metadata":{"_uuid":"268a5f80ec82811a35d6c75709159f4e3d30653f"},"cell_type":"markdown","source":"I won't even bother using low_memory=False because loading such a big file in memory at once is unreasonably foolish\n\nOne very obvious thing to do here is reading only the first few rows as follows :-\n- passing **nrows = n** parameter only reads first **n** number of rows"},{"metadata":{"trusted":true,"_uuid":"e56b7cef41ed82b79095ebd05e015ba54e99911e"},"cell_type":"code","source":"df=pd.read_csv('../input/favorita-grocery-sales-forecasting/train.csv',nrows=10**3)\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1da90928d00e6a432931b1f698265c0827c576c2"},"cell_type":"markdown","source":"But many a times we want to read the whole csv into a single dataframe, what to do in such cases?\n# Introducing chunksize | Iterating through files chunk by chunk¶\nA very interesting paramenter is **chunksize** which returns a **TextFileReader** object that can be iterated upon. \n\n>pd.read_csv('some_csv',chunksize=chunk_size)\n\nEach element the iterator returns consist of **chunk_size** number of rows"},{"metadata":{"_uuid":"03eb76f2ee5019180d2b541457d2034dc51da8a5"},"cell_type":"markdown","source":"Let's examine this object"},{"metadata":{"trusted":true,"_uuid":"2f2a69468e9cf050c37664004662a48bc4eff5e3"},"cell_type":"code","source":"??pd.io.parsers.TextFileReader","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"725c3c9f9e8b904732047ff7be0b52a1b197d719"},"cell_type":"markdown","source":"The following is the code that concerns us for our problem:-\n\n```python\ndef __next__(self):\n        try:\n            return self.get_chunk()\n        except StopIteration:\n            self.close()\n            raise\n            ```\n This code calls ```self.get_chunk()``` function which further calls ```self.read()``` function which reads the file by the **chunk_size** specified by us in the arguments.\n \n ```__next__```  means it's an iterator, lets iterate over it and try reading the whole file \n            "},{"metadata":{"trusted":true,"_uuid":"221583da8bb4d899eae8f0f5e4117431accc3edf"},"cell_type":"code","source":"%time TextFileReaderObject=pd.read_csv('../input/favorita-grocery-sales-forecasting/train.csv',chunksize=10**5) \n#Reading 100k rows in each chunk","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"932094fd07946efa146ea30e82ec86d424be3415"},"cell_type":"markdown","source":"next(iterator) returns the next object in the iterator "},{"metadata":{"trusted":true,"_uuid":"27123645ec0ca7c03d9c5d95e152972b5d461c39"},"cell_type":"code","source":"print(next(TextFileReaderObject).shape)\nnext(TextFileReaderObject).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c09de8ee914bba5504f2a88556337603f6479c5"},"cell_type":"code","source":"TextFileReaderObject=pd.read_csv('../input/favorita-grocery-sales-forecasting/train.csv',chunksize=10**5)\n%time df = pd.concat(chunk for chunk in TextFileReaderObject)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"31dd66e7aea778c9984bd01917d8a2d34bf201fc"},"cell_type":"code","source":"print(df.shape)\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d78bf519c27021d2b2fa168712bf3f1925f1ceb0"},"cell_type":"markdown","source":"There we have it! 125.4 million lines and 4.5G of data read into a single dataframe within 2 minutes\n\nThank you o' great pandas [developers](https://github.com/pandas-dev/pandas/graphs/contributors)"},{"metadata":{"_uuid":"a184f6485ca1a3c0e652ec32e80fa3a2a6e57968"},"cell_type":"markdown","source":"## Conclusion\n- Default values are made default by great programmers, almost always use them with **read_csv** function\n- Name your columns of dataframe by passing **\"names=column name list\"** parameter\n- Always try to specify dtypes of the columns unless there are too many / ambiguous columns \n- Set **low_memory=False** only in case when the data has too many columns with ambiguous datatypes and you can not hardcode their datatypes to avoid warnings (Note: It will always be very memory demanding since the whole file if being loaded into memory at once)\n- Set verbose= True if you want to get insights on what exactly is taking so much time in reading the data\n- Humongous CSV? break it into chunks and read it! "}],"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}