{"cells":[{"metadata":{"_uuid":"26a02f114ffc7766f2adf465cc280af6a3f385db"},"cell_type":"markdown","source":"# Tips for using pandas with large csv datasets"},{"metadata":{"_uuid":"7eba668d686d73faf9c0599014d5a54324ede220"},"cell_type":"markdown","source":"### How to get from 20.3 GB dataframe to 9.7 GB without deleting anything\n\nThe PLAsTiCC comptetion testset is nearly half-a-billion rows of csv data (453.65 million, to be exact). In the discussion I have seen people mention the more than 20 GB (in RAM) testset that we were provided with.\nThe \"tips\" presented here may be obvious to some people, but even most of the top scorers did not use this in their public kernels.\n\nThis is useful even if you have enough RAM available and it also gives a minor speedup of calculations on the data (e.g. about 5% faster for calculating std on a column).\n\n(Note: this kernel will not run as-is, it is just for demonstration of the methods to use on other machines)\n"},{"metadata":{"_uuid":"761cdd63ea1ceed532b9e429a2938e1e49fa9511"},"cell_type":"markdown","source":"### Import libs"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"```\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport json\n\ntestset_file = '../input/test_set.csv'\n```"},{"metadata":{"_uuid":"c828178fd335225ca0a8be93b438ad6c95903cb0"},"cell_type":"markdown","source":"### Standard way of reading in CSV with pandas"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"markdown","source":"```\n%%time\ntestset = pd.read_csv(testset_file)     # the standard way most people read in the data\ntestset.info()\n```"},{"metadata":{"_uuid":"c9eae69dc48558d6d3df6059fdee0cfce22645c1"},"cell_type":"markdown","source":"```\n<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 453653104 entries, 0 to 453653103\nData columns (total 6 columns):\nobject_id    int64\nmjd          float64\npassband     int64\nflux         float64\nflux_err     float64\ndetected     int64\ndtypes: float64(3), int64(3)\nmemory usage: 20.3 GB\n```\n\nAs you can see, the testset occupies 20.3 GB of memory when stored in a dataframe, which is even larger than the unzipped csv file (19.3 GB).\nThis will of course fail ('kernel died') in kaggle kernels, because it is larger then available memory.\n\nNote that pandas - by default - uses 64-bit precision for all numbers (floats and ints). While this usually is okay for smaller datasets (like e.g. the training set), for very large datasets this can and should be optimized as follows."},{"metadata":{"_uuid":"9ceb7c3af62a268a571748fa8abe5d6aa2c02303"},"cell_type":"markdown","source":"### Optimized way of reading CSV with datatypes"},{"metadata":{"_uuid":"6d93485c687b0e71da7ec91ec1d13bf867fda283"},"cell_type":"markdown","source":"On the \"data\"  page of the competition, the actual precision and datatypes are given for each column. So we should make use of that information!"},{"metadata":{"trusted":true,"_uuid":"6b0570124afbef22a072bd57d0b0e07a0f1afca6"},"cell_type":"markdown","source":"```\nthead = pd.read_csv(testset_file, nrows=5) # just read in a few lines to get the column headers\ndtypes = dict(zip(thead.columns.values, ['int32', 'float32', 'float64', 'float32', 'float32', 'bool']))   # datatypes as given by the data page\ndel thead\nprint('Datatype used for each column:\\n', json.dumps(dtypes))\ntestset = pd.read_csv(testset_file, dtype=dtypes)\ntestset.info()\n```"},{"metadata":{"_uuid":"bb8ff6aa88de9e61b59d902196d284db9b4c2f96"},"cell_type":"markdown","source":"```\nDatatype used for each column:\n {\"object_id\": \"int32\", \"mjd\": \"float64\", \"passband\": \"int8\", \"flux\": \"float32\", \"flux_err\": \"float32\", \"detected\": \"bool\"}\n \n<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 453653104 entries, 0 to 453653103\nData columns (total 6 columns):\nobject_id    int32\nmjd          float64\npassband     int8\nflux         float32\nflux_err     float32\ndetected     bool\ndtypes: bool(1), float32(2), float64(1), int32(1), int8(1)\nmemory usage: 9.3 GB\n```\n"},{"metadata":{"_uuid":"ad37a0b927764be88b80c5f8e85570c0d906a2d4"},"cell_type":"markdown","source":"So adding the `dtypes` argument to the `pd.read_csv` call makes a huge difference. The dataset is now only 9.3GB in Memory.\n\nWe can reduce it even further, if we are willing to reduce the accuracy. As we are on a \"galactic timescale\" here, probably timestamps with +/- 36 seconds accuracy are acceptable for the mjd colum (float64->float32), which reduces the memory footprint to 7.6 GB, almost one third of the original size. This could be done without reduction in precision or loss of information, by first subtracting the min value from all mjds to reduce the range (and neccessary number of significant digits).\n\nIf you are thinking about desparsifying the data by binning the bands into days, you could even live with integer mjds, which reduces it to 6.7 GB\n"},{"metadata":{"_uuid":"a2fb74d30b6ad2ae71769832d286c86b92828cd5"},"cell_type":"markdown","source":"### mjd as float32\n```\n<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 453653104 entries, 0 to 453653103\nData columns (total 6 columns):\nobject_id    int32\nmjd          float32\npassband     int8\nflux         float32\nflux_err     float32\ndetected     bool\ndtypes: bool(1), float32(3), int32(1), int8(1)\nmemory usage: 7.6 GB\n```\n\n### mjd as uint16\n```\n<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 453653104 entries, 0 to 453653103\nData columns (total 6 columns):\nobject_id    int32\nmjd          uint16\npassband     int8\nflux         float32\nflux_err     float32\ndetected     bool\ndtypes: bool(1), float32(2), int32(1), int8(1), uint16(1)\nmemory usage: 6.8 GB\n```"},{"metadata":{"trusted":true,"_uuid":"e7d7750e83f2f86d3374a5dac15c367d127d0da3"},"cell_type":"markdown","source":"## Storing the data\nIn order to reduce your waiting time when loading the data, it makes sense to store it in a different format. Pandas offers a lot of different formats. Below is a table of the sizes on disk, in mem, read and write times using the full dataset with correct precision (9.3 GB in mem). This is of course only a quick and dirty benchmark, but should be enough to give you a feel.\n\nThe times may differ significantly on your machines, but the relative differences should stay similar. (this was measured using a GCP highmen-8 instance using the boot disk of 200GB, which is quite slow, so local NVMe storage will be much faster)\n\n|Format|Size on disk|Read Time|Write Time|Size In Mem\n| :--- | ---: | ---: | ---: | ---:\n|csv|19.3 GB|4min 56s|  - |_ 9.3 GB\n|hdf|13.0  GB|  |_ 1min 07s | 12.7 GB\n|pickle|_ 9.3 GB|1min 28s |_ 1min 04s |_ 9.3 GB\n|pickle zip|_ 5.0 GB|2min 11s |11min 36s |_ 9.3 GB\n|feather|_ 9.0 GB|1min 17s |_ 0min 29s |_ 9.3 GB\n|parquet|_ 9.0 GB|1min 17s |_ 1min 29s | 12.7 GB|"},{"metadata":{"trusted":true,"_uuid":"e7d7750e83f2f86d3374a5dac15c367d127d0da3"},"cell_type":"markdown","source":"I have no clue why for hdf and parquet the actual in-memory size grows to 12.7 GB after writing a df of 9.3 GB to disk and then reading it back in, but this was reproducible. Maybe someone here has an explanation?"},{"metadata":{"trusted":true,"_uuid":"b31196cba852c2963f0a53219911217f427e9eeb"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d9e3386561f5e0481b98acc16aa1c14188ba3bfb"},"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}