{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Here are some techniques you might find useful for dealing with larger datasets. The `nyra_2019_complete` isn't overly gigantic but it presents an opportunity to speed up iterations for machine learning. \n\nI meant to send this a while back - hope it still helps now and with future competitions.","metadata":{}},{"cell_type":"code","source":"from pathlib import Path\n\nimport pandas as pd\nimport skmem","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:09:24.655825Z","iopub.execute_input":"2022-11-03T04:09:24.656274Z","iopub.status.idle":"2022-11-03T04:09:24.662293Z","shell.execute_reply.started":"2022-11-03T04:09:24.656236Z","shell.execute_reply":"2022-11-03T04:09:24.660739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Thanks to Jari Peltola and others for the column names\ncols = [\n    \"track_id\",\n    \"race_date\",\n    \"race_number\",\n    \"program_number\",\n    \"trakus_index\",\n    \"latitude\",\n    \"longitude\",\n    \"distance_id\",\n    \"course_type\",\n    \"track_condition\",\n    \"run_up_distance\",\n    \"race_type\",\n    \"post_time\",\n    \"purse\",\n    \"weight_carried\",\n    \"jockey\",\n    \"odds\",\n    \"position_at_finish\",\n]\nfilepath = Path(\"../input/big-data-derby-2022/nyra_2019_complete.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:09:27.457619Z","iopub.execute_input":"2022-11-03T04:09:27.458041Z","iopub.status.idle":"2022-11-03T04:09:27.463860Z","shell.execute_reply.started":"2022-11-03T04:09:27.457956Z","shell.execute_reply":"2022-11-03T04:09:27.462733Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Below are the initial read time and memory footprint. The read time in and of itself is no big deal, but watch what kind of improvements you can get and imagine scaling it. ","metadata":{}},{"cell_type":"code","source":"%%time\ndf_complete = pd.read_csv(filepath, header=None, names=cols, low_memory=False)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:10:06.394328Z","iopub.execute_input":"2022-11-03T04:10:06.394689Z","iopub.status.idle":"2022-11-03T04:10:12.918917Z","shell.execute_reply.started":"2022-11-03T04:10:06.394661Z","shell.execute_reply":"2022-11-03T04:10:12.918013Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Look at shape and memory in RAM (MB)\nprint(df_complete.shape, (df_complete.memory_usage(deep=True).sum() / 1024**2).round(0).astype(int)) # MB\n","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:13:59.460446Z","iopub.execute_input":"2022-11-03T04:13:59.460848Z","iopub.status.idle":"2022-11-03T04:14:02.547654Z","shell.execute_reply.started":"2022-11-03T04:13:59.460820Z","shell.execute_reply":"2022-11-03T04:14:02.546541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Looking at the dtypes below you can see opportunities for reduction:\n- object -> datetime for the race_date\n- object -> category for other low-cardinal features\n- int64 -> lower int types\n\nFor floats, I'll reduce `post_time` to float32 which is safe since it's whole numbers. The lats and longs are best left alone IMO unitl you convert them to m or ft.","metadata":{}},{"cell_type":"code","source":"df_complete.dtypes","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:17:00.652025Z","iopub.execute_input":"2022-11-03T04:17:00.652419Z","iopub.status.idle":"2022-11-03T04:17:00.661868Z","shell.execute_reply.started":"2022-11-03T04:17:00.652388Z","shell.execute_reply":"2022-11-03T04:17:00.660807Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Converting dates is usually good for both efficiency and for enabling pandas datetime functions. On most sytems, converting 5.2M date strings to dates with `pd.to_datetime()` takes a looonnnnnnnggg time. It's much faster to use a dictionary and map values, especially when we're talking about only 365 dates.","metadata":{}},{"cell_type":"code","source":"date_dict = dict(\n    zip(\n        pd.date_range(\"2019-01-01\", \"2019-12-31\").strftime(\"%Y-%m-%d\"),\n        pd.date_range(\"2019-01-01\", \"2019-12-31\"),\n    )\n)\ndf_complete['race_date'] = df_complete.race_date.map(date_dict)\ndf_complete.race_date.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:17:15.939965Z","iopub.execute_input":"2022-11-03T04:17:15.940355Z","iopub.status.idle":"2022-11-03T04:17:16.588038Z","shell.execute_reply.started":"2022-11-03T04:17:15.940324Z","shell.execute_reply":"2022-11-03T04:17:16.586861Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"An easy way to further reduce data size is by using the skmem utility script. Disclosure: I'm the author.","metadata":{}},{"cell_type":"code","source":"mr = skmem.MemReducer(nullables=False)\nmr.fit_transform(df_complete, float_cols=['post_time'])\ndf_complete.dtypes","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:18:07.699831Z","iopub.execute_input":"2022-11-03T04:18:07.700219Z","iopub.status.idle":"2022-11-03T04:18:17.795431Z","shell.execute_reply.started":"2022-11-03T04:18:07.700189Z","shell.execute_reply":"2022-11-03T04:18:17.794375Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can see from the output above we reduced the memory footprint by 90% and a little more, counting the date conversion. The memory and time savings will scale with the complexity of the pipeline and the size of the original data.","metadata":{}},{"cell_type":"markdown","source":"Now you can save the optimized data to parquet and read it in very quickly going forward - a small fraction of the original time!","metadata":{}},{"cell_type":"code","source":"df_complete.to_parquet('nyra_complete.parq')","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:20:28.622613Z","iopub.execute_input":"2022-11-03T04:20:28.622967Z","iopub.status.idle":"2022-11-03T04:20:30.012484Z","shell.execute_reply.started":"2022-11-03T04:20:28.622938Z","shell.execute_reply":"2022-11-03T04:20:30.010710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\npd.read_parquet('nyra_complete.parq')","metadata":{"execution":{"iopub.status.busy":"2022-11-03T04:21:02.237887Z","iopub.execute_input":"2022-11-03T04:21:02.238237Z","iopub.status.idle":"2022-11-03T04:21:02.606721Z","shell.execute_reply.started":"2022-11-03T04:21:02.238209Z","shell.execute_reply":"2022-11-03T04:21:02.605769Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Thanks for reading.","metadata":{}}]}