{"cells":[{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"All files apart from the NGS files are quite small. The NGS files however, each can contain up to 9 million rows. In that case we need to reduce the amount of memory they take up. In this kernel I will show you how to do this in an easy way.\n\nThe NGS datasets contains player position, speed and direction data for each player during the entire course of the play. The NGS dataset is the only dataset that contains Timeas a variable. "},{"metadata":{"_uuid":"574267e4c469a0f583a9f5d4acf74a6b765509e3"},"cell_type":"markdown","source":"## Import Libraries"},{"metadata":{"trusted":true,"_uuid":"e61cb867d5dac6590c91bc7d8ec000327957a88f"},"cell_type":"code","source":"import numpy as np \nimport pandas as pd\nimport os\nimport tqdm\nimport gc\nimport feather","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"efb741523fc3bdf20046bc3e376d68ad156d1b30"},"cell_type":"code","source":"PATH = '../input/'","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1bc34eb2e340cd4cef375a88eeb89168c8b041e9"},"cell_type":"markdown","source":"## What data are available"},{"metadata":{"trusted":true,"_uuid":"1682dd0bf4bfdb8d86a12cb68bdc331e146a5b9e"},"cell_type":"code","source":"print(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4e056b1b92aa8524055afe995cd318d2e94b4cdb"},"cell_type":"markdown","source":"Let's focus only on the NGS files. Below are the files."},{"metadata":{"_uuid":"10924775c11285330bae19ff8e788e3822917543"},"cell_type":"markdown","source":"* NGS-2016-pre.csv\n* NGS-2016-reg-wk1-6.csv\n* NGS-2016-reg-wk7-12.csv\n* NGS-2016-reg-wk13-17.csv\n* NGS-2016-post.csv\n\n\n* NGS-2017-pre.csv\n* NGS-2017-reg-wk1-6.csv\n* NGS-2017-reg-wk7-12.csv\n* NGS-2017-reg-wk13-17.csv\n* NGS-2017-post.csv"},{"metadata":{"_uuid":"739d4c0a5583396cd217d40ffdc7dc915958220d"},"cell_type":"markdown","source":"## How many rows are there per file"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"27b6218a23c53ae7270642001dc31dba12e3d9c7"},"cell_type":"code","source":"%%time\n\nwith open(f'{PATH}NGS-2016-pre.csv') as file:\n    n_rows = len(file.readlines())\nprint('2016 pre rows:', n_rows)\n\nwith open(f'{PATH}NGS-2016-reg-wk1-6.csv') as file:\n    n_rows = len(file.readlines())\nprint('2016 wk1-6 rows:', n_rows)\n\nwith open(f'{PATH}NGS-2016-reg-wk7-12.csv') as file:\n    n_rows = len(file.readlines())\nprint('2016 wk7-12 rows:', n_rows)\n\nwith open(f'{PATH}NGS-2016-reg-wk13-17.csv') as file:\n    n_rows = len(file.readlines())\nprint('2016 wk13-17 rows:', n_rows)\n\nwith open(f'{PATH}NGS-2016-post.csv') as file:\n    n_rows = len(file.readlines())\nprint('2016 post rows:', n_rows)\n\nwith open(f'{PATH}NGS-2017-pre.csv') as file:\n    n_rows = len(file.readlines())\nprint('2017 pre rows:', n_rows)\n\nwith open(f'{PATH}NGS-2017-reg-wk1-6.csv') as file:\n    n_rows = len(file.readlines())\nprint('2017 wk1-6 rows:', n_rows)\n\nwith open(f'{PATH}NGS-2017-reg-wk7-12.csv') as file:\n    n_rows = len(file.readlines())\nprint('2017 wk7-12 rows:', n_rows)\n\nwith open(f'{PATH}NGS-2017-reg-wk13-17.csv') as file:\n    n_rows = len(file.readlines())\nprint('2017 wk13-17 rows:', n_rows)\n\nwith open(f'{PATH}NGS-2017-post.csv') as file:\n    n_rows = len(file.readlines())\nprint('2017 post rows:', n_rows)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f389ccb5752e7082e56317b95a1964c3dce41915"},"cell_type":"markdown","source":"## Load the Data and Define Data Types"},{"metadata":{"_uuid":"ceaad8a913dc9cc1c70af3a122c4ce1ecb751516"},"cell_type":"markdown","source":"All NGS file have the same structure, which means we can load them individually and concatenate them afterwards. Before doing that we can have a sneak peak at what the data look like and then define the data types before loading the csv files. We coud obviously do this once we have loaded the files but this way saves time and memory right away. "},{"metadata":{"trusted":true,"_uuid":"2f424141dbf70c92fce690e70457458f08fe985c"},"cell_type":"code","source":"# Only load the first 5 rows to get an idea of what the data look like\ndf_temp = pd.read_csv(f'{PATH}NGS-2016-pre.csv', nrows=5)\ndf_temp.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8228bfc9383343cc3b98ac1c97c32ab494cb4458"},"cell_type":"code","source":"# Get information on the datatypes\ndf_temp.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"64963b984d24d86940250c8dcfc3984e6f146d6f"},"cell_type":"code","source":"# Find out the smallest data type possible for each numeric feature\nfloat_cols = df_temp.select_dtypes(include=['float'])\nint_cols = df_temp.select_dtypes(include=['int'])\n\nfor cols in float_cols.columns:\n    df_temp[cols] = pd.to_numeric(df_temp[cols], downcast='float')\n    \nfor cols in int_cols.columns:\n    df_temp[cols] = pd.to_numeric(df_temp[cols], downcast='integer')\n\nprint(df_temp.info())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f49ac671bb8e4f4eab03b6da4c7658ddf4c6a2d4"},"cell_type":"markdown","source":"By 'downcasting' the numeric features we have almost halved the memory consumption. However, not all data types are correct yet. \n1. From the data disctionary we know that **Event** is text and should be data type 'object'. \n2. And **Time** is obviously datetime. It's faster though importing it as 'string' and then converting it.\n3. We can also go a step further with **SeasonYear**. The feature only contains two values: 2016 and 2017. If we changed that to 0 and 1, then we can reduce the data type further from int16 to int8, saving more memory. \n4. **GSISID** is only an integer and not a float. However, there are NANs in the data, which prevent us from converting this straight away.\n\nNow let's define the data types and then upload the NGS files."},{"metadata":{"trusted":true,"_uuid":"a4dc3d996e8729560c3a4ed342009b11346512a7"},"cell_type":"code","source":"dtypes = {'Season_Year': 'int16',\n         'GameKey': 'int16',\n         'PlayID': 'int16',\n         'GSISID': 'float32',\n         'Time': 'str',\n         'x': 'float32',\n         'y': 'float32',\n         'dis': 'float32',\n         'o': 'float32',\n         'dir': 'float32',\n         'Event': 'str'}\n\ncol_names = list(dtypes.keys())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b51dcf5fe2c1d281107e0d8aeb543c1241d460d5"},"cell_type":"code","source":"ngs_files = ['NGS-2016-pre.csv',\n             'NGS-2016-reg-wk1-6.csv',\n             'NGS-2016-reg-wk7-12.csv',\n             'NGS-2016-reg-wk13-17.csv',\n             'NGS-2016-post.csv',\n             'NGS-2017-pre.csv',\n             'NGS-2017-reg-wk1-6.csv',\n             'NGS-2017-reg-wk7-12.csv',\n             'NGS-2017-reg-wk13-17.csv',\n             'NGS-2017-post.csv']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"41fd581bb330c4013338e9d146f1ed12d7f6054e"},"cell_type":"code","source":"# Load each ngs file and append it to a list. \n# We will turn this into a DataFrame in the next step\n\ndf_list = []\n\nfor i in tqdm.tqdm(ngs_files):\n    df = pd.read_csv(f'{PATH}'+i, usecols=col_names,dtype=dtypes)\n    \n    df_list.append(df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9c26a0f218d8144ffebefe6288ea47ea47288523"},"cell_type":"code","source":"# Merge all dataframes into one dataframe\nngs = pd.concat(df_list)\n\n# Delete the dataframe list to release memory\ndel df_list\ngc.collect()\n\n# Convert Time to datetime\nngs['Time'] = pd.to_datetime(ngs['Time'], format='%Y-%m-%d %H:%M:%S')\n\n# See what we have loaded\nngs.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4fef4a55ae63a50df36a38ba04534bfc3d1b33c4"},"cell_type":"markdown","source":"## Final Steps\n\n1. We can turn Season_Year into a category with the values 0 and 1 to save more memory. The feature only contains the values 2016 and 2017.\n2. We can delete the NANs in GSISD and then turn it into an integer. \n3. We can save this as a feather file, which is much faster to load. But we might have to define the data types again after loading."},{"metadata":{"trusted":true,"_uuid":"9b2ec3d49fd14c7e9a03ebe513b64c0ad98aba30"},"cell_type":"code","source":"# Turn Saeson_Year into a category and ultimately into an integer (this doesn't seem necessary)\nif False:\n    ngs['Season_Year'] = ngs['Season_Year'].astype('category').cat.codes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"fea58cb241ca417a6086c02d59946b923c1e6872"},"cell_type":"code","source":"# There are 2536 out of 66,492,490 cases where GSISID is NAN. Let's drop those to convert the data type\nngs = ngs[~ngs['GSISID'].isna()]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4592fa92bcc43efeab07b7058d23f9e4097f157d"},"cell_type":"code","source":"# Convert GSISID to integer\nngs['GSISID'] = ngs['GSISID'].astype('int32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"db29aff3e3e1987d311411a3ecc98ab13a442a40"},"cell_type":"code","source":"# Save to feather so we can use it in other kernels\nngs.reset_index(drop=True).to_feather(f'ngs.feather')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5bfc809144194f7b9dcae313319163c773a9785a"},"cell_type":"markdown","source":"This concludes this tutorial. I hope you found it useful and I'm keen to hear any feedback and how to improve things."},{"metadata":{"trusted":true,"_uuid":"d81c7ff41d7b3b52ea332a52a3d01a882dcac2dc"},"cell_type":"code","source":"!ls -lh","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"54dd6d990f3125648da9e7a31560060bfed675f0"},"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}