{"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":"# Recreate otto-full-optimized-memory-footprint in polars on Kaggle\n\nIn this [post](https://www.kaggle.com/competitions/otto-recommender-system/discussion/363843), @radek1 has created Recreate otto-full-optimized-memory-footprint dataset which makes my life super easier. In order to understand how he did the processing, I can study his code he prepared and experiment on `test.jsonl`. However, if I want to reproduce both `train.parquet` and `test.parquet`, I will very quickly hit the RAM error, so he converted `train.jsonl` to `train.parquet` on his local machine with larger RAM.\n\nAlso in this competition @radek1 has introduced polars another library equivalent to pandas which is only faster, more efficient on RAM and easier to use (IMO). In order to learn more of polars and it feels great if I could contribute a little back @radek1 after I have learned so much from his selfless sharing. So, I decided to experiment on converting both `train.jsonl` and `test.jsonl` to `train.parquet` and `test.parquet` on Kaggle (within 30RAM) with polars.\n\nTo my surprise, yes, it works!","metadata":{}},{"cell_type":"code","source":"from IPython.core.interactiveshell import InteractiveShell\nInteractiveShell.ast_node_interactivity = \"all\"","metadata":{"execution":{"iopub.status.busy":"2022-12-27T06:32:50.925270Z","iopub.execute_input":"2022-12-27T06:32:50.925754Z","iopub.status.idle":"2022-12-27T06:32:50.956215Z","shell.execute_reply.started":"2022-12-27T06:32:50.925640Z","shell.execute_reply":"2022-12-27T06:32:50.955163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install polars","metadata":{"execution":{"iopub.status.busy":"2022-12-27T06:33:53.676960Z","iopub.execute_input":"2022-12-27T06:33:53.677405Z","iopub.status.idle":"2022-12-27T06:34:09.536359Z","shell.execute_reply.started":"2022-12-27T06:33:53.677371Z","shell.execute_reply":"2022-12-27T06:34:09.534796Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import polars as pl\nimport pandas as pd\nimport gc","metadata":{"execution":{"iopub.status.busy":"2022-12-27T06:34:09.538606Z","iopub.execute_input":"2022-12-27T06:34:09.539048Z","iopub.status.idle":"2022-12-27T06:34:09.604415Z","shell.execute_reply.started":"2022-12-27T06:34:09.539006Z","shell.execute_reply":"2022-12-27T06:34:09.603318Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Verify my train.parquet\nBefore reading onto how polars does the convertion, I really have to make sure my `train.parquet` is the same as radek's `train.parquet`","metadata":{}},{"cell_type":"code","source":"train_radek = pl.scan_parquet('/kaggle/input/otto-full-optimized-memory-footprint/train.parquet')\ntest_radek = pl.scan_parquet('/kaggle/input/otto-full-optimized-memory-footprint/test.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-12-27T06:34:09.605666Z","iopub.execute_input":"2022-12-27T06:34:09.606070Z","iopub.status.idle":"2022-12-27T06:34:09.629903Z","shell.execute_reply.started":"2022-12-27T06:34:09.606037Z","shell.execute_reply":"2022-12-27T06:34:09.628843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_pl = pl.scan_parquet('/kaggle/input/otto-radek-style-polars/train.parquet')\ntest_pl = pl.scan_parquet('/kaggle/input/otto-radek-style-polars/test.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-12-27T06:34:09.633533Z","iopub.execute_input":"2022-12-27T06:34:09.634042Z","iopub.status.idle":"2022-12-27T06:34:09.663685Z","shell.execute_reply.started":"2022-12-27T06:34:09.633995Z","shell.execute_reply":"2022-12-27T06:34:09.662688Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_pl_ms = pl.scan_parquet('/kaggle/input/otto-radek-style-polars/train_ms.parquet')\ntest_pl_ms = pl.scan_parquet('/kaggle/input/otto-radek-style-polars/test_ms.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-12-27T06:34:09.665190Z","iopub.execute_input":"2022-12-27T06:34:09.666221Z","iopub.status.idle":"2022-12-27T06:34:09.693183Z","shell.execute_reply.started":"2022-12-27T06:34:09.666176Z","shell.execute_reply":"2022-12-27T06:34:09.691892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_radek.select([\n    pl.col('session').n_unique().alias('num_sess'),\n    pl.col('aid').n_unique().alias('num_aid'),\n    pl.col('session').count().alias('num_rows')\n]).collect()","metadata":{"execution":{"iopub.status.busy":"2022-12-27T06:35:48.065967Z","iopub.execute_input":"2022-12-27T06:35:48.066381Z","iopub.status.idle":"2022-12-27T06:36:11.310794Z","shell.execute_reply.started":"2022-12-27T06:35:48.066350Z","shell.execute_reply":"2022-12-27T06:36:11.309825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_pl.select([\n    pl.col('session').n_unique().alias('num_sess'),\n    pl.col('aid').n_unique().alias('num_aid'),\n    pl.col('session').count().alias('num_rows')\n]).collect()","metadata":{"execution":{"iopub.status.busy":"2022-12-27T00:54:11.155656Z","iopub.execute_input":"2022-12-27T00:54:11.156001Z","iopub.status.idle":"2022-12-27T00:54:42.795931Z","shell.execute_reply.started":"2022-12-27T00:54:11.155972Z","shell.execute_reply":"2022-12-27T00:54:42.794445Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_pl_ms.select([\n    pl.col('session').n_unique().alias('num_sess'),\n    pl.col('aid').n_unique().alias('num_aid'),\n    pl.col('session').count().alias('num_rows')\n]).collect()","metadata":{"execution":{"iopub.status.busy":"2022-12-27T00:54:42.798241Z","iopub.execute_input":"2022-12-27T00:54:42.798576Z","iopub.status.idle":"2022-12-27T00:55:17.515511Z","shell.execute_reply.started":"2022-12-27T00:54:42.798548Z","shell.execute_reply":"2022-12-27T00:55:17.514642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_radek.select([\n    pl.col('session').n_unique().alias('num_sess'),\n]).collect()\n\ntest_pl.select([\n    pl.col('session').n_unique().alias('num_sess'),\n]).collect()\n\ntest_pl_ms.select([\n    pl.col('session').n_unique().alias('num_sess'),\n]).collect()","metadata":{"execution":{"iopub.status.busy":"2022-12-27T00:56:34.781413Z","iopub.execute_input":"2022-12-27T00:56:34.781810Z","iopub.status.idle":"2022-12-27T00:56:36.327380Z","shell.execute_reply.started":"2022-12-27T00:56:34.781778Z","shell.execute_reply":"2022-12-27T00:56:36.326259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_radek.tail().collect()\ntrain_pl.tail().collect()\ntrain_pl_ms.tail().collect()","metadata":{"execution":{"iopub.status.busy":"2022-12-27T00:58:32.323179Z","iopub.execute_input":"2022-12-27T00:58:32.323520Z","iopub.status.idle":"2022-12-27T00:59:51.824049Z","shell.execute_reply.started":"2022-12-27T00:58:32.323493Z","shell.execute_reply":"2022-12-27T00:59:51.822566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# How big are the numbers for ts, aid, session?\nhttps://github.com/otto-de/recsys-dataset#dataset-statistics\n\n# What is the difference between int64 and Int32?\n- In short you can store more than 32767 value in int16 , \n\n- more than 2_147_483_647 value in int32 \n\n- and more than 9223372036854775807 value in int64.","metadata":{}},{"cell_type":"code","source":"12_899_779/100","metadata":{"execution":{"iopub.status.busy":"2022-12-18T21:55:01.344328Z","iopub.execute_input":"2022-12-18T21:55:01.344957Z","iopub.status.idle":"2022-12-18T21:55:01.351438Z","shell.execute_reply.started":"2022-12-18T21:55:01.344921Z","shell.execute_reply":"2022-12-18T21:55:01.350638Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd","metadata":{"execution":{"iopub.status.busy":"2022-12-18T21:55:01.352908Z","iopub.execute_input":"2022-12-18T21:55:01.353836Z","iopub.status.idle":"2022-12-18T21:55:01.362036Z","shell.execute_reply.started":"2022-12-18T21:55:01.353803Z","shell.execute_reply":"2022-12-18T21:55:01.361157Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks = pd.read_json('/kaggle/input/otto-recommender-system/train.jsonl', lines = True, chunksize=129000)","metadata":{"execution":{"iopub.status.busy":"2022-12-18T21:55:01.363561Z","iopub.execute_input":"2022-12-18T21:55:01.364174Z","iopub.status.idle":"2022-12-18T21:55:01.373121Z","shell.execute_reply.started":"2022-12-18T21:55:01.364140Z","shell.execute_reply":"2022-12-18T21:55:01.371939Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import gc","metadata":{"execution":{"iopub.status.busy":"2022-12-18T21:55:01.374852Z","iopub.execute_input":"2022-12-18T21:55:01.375241Z","iopub.status.idle":"2022-12-18T21:55:01.382790Z","shell.execute_reply.started":"2022-12-18T21:55:01.375196Z","shell.execute_reply":"2022-12-18T21:55:01.381814Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks_joined = pl.DataFrame()\nfor idx, c in enumerate(chunks):\n    print(f'current chunk is {idx}')\n    \n    train_chunk = pl.from_pandas(c)\n    train_chunk = train_chunk.select([\n    pl.col('session'),\n    pl.col('events'),\n]).explode('events').select([\n    pl.col('session'),\n    pl.col('events')\n]).select([\n    pl.col('session'),\n    pl.col('events').struct.field('aid').alias('aid'),\n    pl.col('events').struct.field('ts').alias('ts'),\n    pl.col('events').struct.field('type').alias('type'),\n]).select([\n    pl.col('session').cast(pl.Int32).alias('session'),\n    pl.col('aid').cast(pl.Int32).alias('aid'),\n    (pl.col('ts')/1000).cast(pl.Int32).alias('ts'),\n    (pl.when(pl.col('type') == 'clicks')\n        .then(pl.lit(0))\n        .when(pl.col('type') == 'carts')\n        .then(pl.lit(1))\n        .otherwise(pl.lit(2))).cast(pl.UInt8).alias('type'),\n])\n#     train_chunk.write_parquet(f'129000_sessions_{idx}.parquet')\n    if chunks_joined.is_empty(): chunks_joined = train_chunk\n    else: chunks_joined = pl.concat([chunks_joined, train_chunk])\n\n    \n    del train_chunk\n    gc.collect()\n\n\nchunks_joined.write_parquet('train.parquet')\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del chunks_joined, chunks\ngc.collect()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks = pd.read_json('/kaggle/input/otto-recommender-system/train.jsonl', lines = True, chunksize=129000)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks_joined = pl.DataFrame()\nfor idx, c in enumerate(chunks):\n    print(f'current chunk is {idx}')\n    \n    train_chunk = pl.from_pandas(c)\n    train_chunk = train_chunk.select([\n    pl.col('session'),\n    pl.col('events'),\n]).explode('events').select([\n    pl.col('session'),\n    pl.col('events')\n]).select([\n    pl.col('session'),\n    pl.col('events').struct.field('aid').alias('aid'),\n    pl.col('events').struct.field('ts').alias('ts'),\n    pl.col('events').struct.field('type').alias('type'),\n]).select([\n    pl.col('session').cast(pl.Int32).alias('session'),\n    pl.col('aid').cast(pl.Int32).alias('aid'),\n    pl.col('ts').cast(pl.Int64).alias('ts'),\n    (pl.when(pl.col('type') == 'clicks')\n        .then(pl.lit(0))\n        .when(pl.col('type') == 'carts')\n        .then(pl.lit(1))\n        .otherwise(pl.lit(2))).cast(pl.UInt8).alias('type'),\n])\n#     train_chunk.write_parquet(f'129000_sessions_{idx}.parquet')\n    if chunks_joined.is_empty(): chunks_joined = train_chunk\n    else: chunks_joined = pl.concat([chunks_joined, train_chunk])\n\n    \n    del train_chunk\n    gc.collect()\n\n\nchunks_joined.write_parquet('train_ms.parquet')\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del chunks_joined, chunks\ngc.collect()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks = pd.read_json('/kaggle/input/otto-recommender-system/test.jsonl', lines = True, chunksize=129000)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks_joined = pl.DataFrame()\nfor idx, c in enumerate(chunks):\n    print(f'current chunk is {idx}')\n    \n    test_chunk = pl.from_pandas(c)\n    test_chunk = test_chunk.select([\n    pl.col('session'),\n    pl.col('events'),\n]).explode('events').select([\n    pl.col('session'),\n    pl.col('events')\n]).select([\n    pl.col('session'),\n    pl.col('events').struct.field('aid').alias('aid'),\n    pl.col('events').struct.field('ts').alias('ts'),\n    pl.col('events').struct.field('type').alias('type'),\n]).select([\n    pl.col('session').cast(pl.Int32).alias('session'),\n    pl.col('aid').cast(pl.Int32).alias('aid'),\n    (pl.col('ts')/1000).cast(pl.Int32).alias('ts'),\n    (pl.when(pl.col('type') == 'clicks')\n        .then(pl.lit(0))\n        .when(pl.col('type') == 'carts')\n        .then(pl.lit(1))\n        .otherwise(pl.lit(2))).cast(pl.UInt8).alias('type'),\n])\n#     test_chunk.write_parquet(f'129000_sessions_{idx}.parquet')\n    if chunks_joined.is_empty(): chunks_joined = test_chunk\n    else: chunks_joined = pl.concat([chunks_joined, test_chunk])\n    del test_chunk\n    gc.collect()\n\n\nchunks_joined.write_parquet('test.parquet')\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del chunks_joined, chunks\ngc.collect()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks = pd.read_json('/kaggle/input/otto-recommender-system/test.jsonl', lines = True, chunksize=129000)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"chunks_joined = pl.DataFrame()\nfor idx, c in enumerate(chunks):\n    print(f'current chunk is {idx}')\n    \n    test_chunk = pl.from_pandas(c)\n    test_chunk = test_chunk.select([\n    pl.col('session'),\n    pl.col('events'),\n]).explode('events').select([\n    pl.col('session'),\n    pl.col('events')\n]).select([\n    pl.col('session'),\n    pl.col('events').struct.field('aid').alias('aid'),\n    pl.col('events').struct.field('ts').alias('ts'),\n    pl.col('events').struct.field('type').alias('type'),\n]).select([\n    pl.col('session').cast(pl.Int32).alias('session'),\n    pl.col('aid').cast(pl.Int32).alias('aid'),\n    pl.col('ts').cast(pl.Int64).alias('ts'),\n    (pl.when(pl.col('type') == 'clicks')\n        .then(pl.lit(0))\n        .when(pl.col('type') == 'carts')\n        .then(pl.lit(1))\n        .otherwise(pl.lit(2))).cast(pl.UInt8).alias('type'),\n])\n#     test_chunk.write_parquet(f'129000_sessions_{idx}.parquet')\n    if chunks_joined.is_empty(): chunks_joined = test_chunk\n    else: chunks_joined = pl.concat([chunks_joined, test_chunk])\n    del test_chunk\n    gc.collect()\n\n\nchunks_joined.write_parquet('test_ms.parquet')\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del chunks_joined, chunks\ngc.collect()","metadata":{},"execution_count":null,"outputs":[]}]}