{"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":"code","source":"DEBUG = True","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:01.670754Z","iopub.execute_input":"2022-12-17T13:44:01.671251Z","iopub.status.idle":"2022-12-17T13:44:01.699671Z","shell.execute_reply.started":"2022-12-17T13:44:01.671157Z","shell.execute_reply":"2022-12-17T13:44:01.698517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from IPython.core.interactiveshell import InteractiveShell\nInteractiveShell.ast_node_interactivity = \"all\"","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:01.701889Z","iopub.execute_input":"2022-12-17T13:44:01.703120Z","iopub.status.idle":"2022-12-17T13:44:01.709132Z","shell.execute_reply.started":"2022-12-17T13:44:01.703026Z","shell.execute_reply":"2022-12-17T13:44:01.707681Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install polars","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:01.710687Z","iopub.execute_input":"2022-12-17T13:44:01.711080Z","iopub.status.idle":"2022-12-17T13:44:17.469729Z","shell.execute_reply.started":"2022-12-17T13:44:01.711044Z","shell.execute_reply":"2022-12-17T13:44:17.468470Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import polars as pl\nimport pandas as pd\nimport datetime","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:17.471266Z","iopub.execute_input":"2022-12-17T13:44:17.471737Z","iopub.status.idle":"2022-12-17T13:44:17.535563Z","shell.execute_reply.started":"2022-12-17T13:44:17.471693Z","shell.execute_reply":"2022-12-17T13:44:17.534242Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%time\ntrain = pl.scan_parquet('/kaggle/input/otto-full-optimized-memory-footprint/train.parquet')\ntest = pl.scan_parquet('/kaggle/input/otto-full-optimized-memory-footprint/test.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:17.539758Z","iopub.execute_input":"2022-12-17T13:44:17.540316Z","iopub.status.idle":"2022-12-17T13:44:17.559102Z","shell.execute_reply.started":"2022-12-17T13:44:17.540260Z","shell.execute_reply":"2022-12-17T13:44:17.558117Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.groupby('aid').agg([\n    pl.col('aid').filter(pl.col('type') == 0).count().alias('total_occurs_as_clicks_each_aid'),\n    pl.col('aid').filter(pl.col('type') == 1).count().alias('total_occurs_as_carts_each_aid'),    \n    pl.col('aid').filter(pl.col('type') == 2).count().alias('total_occurs_as_orders_each_aid'),\n    pl.col('aid').count().alias('total_occurs_each_aid'),    \n    pl.when(pl.col('aid').count() <= 30).then(pl.col('aid').count()).otherwise(pl.lit(-1)).alias('<30')\n]).sort('<30', reverse=True).fetch()\n","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:17.560527Z","iopub.execute_input":"2022-12-17T13:44:17.560931Z","iopub.status.idle":"2022-12-17T13:44:17.616615Z","shell.execute_reply.started":"2022-12-17T13:44:17.560885Z","shell.execute_reply":"2022-12-17T13:44:17.615319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = train.groupby('aid').agg([\n    pl.col('aid').filter(pl.col('type') == 0).count().alias('total_occurs_as_clicks_each_aid'),\n    pl.col('aid').filter(pl.col('type') == 1).count().alias('total_occurs_as_carts_each_aid'),    \n    pl.col('aid').filter(pl.col('type') == 2).count().alias('total_occurs_as_orders_each_aid'),\n    pl.col('aid').count().alias('total_occurs_each_aid'),    \n]).collect()","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:17.619001Z","iopub.execute_input":"2022-12-17T13:44:17.620086Z","iopub.status.idle":"2022-12-17T13:44:40.489370Z","shell.execute_reply.started":"2022-12-17T13:44:17.620045Z","shell.execute_reply":"2022-12-17T13:44:40.488054Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.head()","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:40.490947Z","iopub.execute_input":"2022-12-17T13:44:40.491357Z","iopub.status.idle":"2022-12-17T13:44:40.504998Z","shell.execute_reply.started":"2022-12-17T13:44:40.491319Z","shell.execute_reply":"2022-12-17T13:44:40.503647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.with_columns([\n    (pl.col('total_occurs_as_carts_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('cart_over_click_ratio_each_aid'),\n    (pl.col('total_occurs_as_orders_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('order_over_click_ratio_each_aid'),\n]).sort('cart_over_click_ratio_each_aid', reverse=True).head()","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:40.506604Z","iopub.execute_input":"2022-12-17T13:44:40.507913Z","iopub.status.idle":"2022-12-17T13:44:40.779756Z","shell.execute_reply.started":"2022-12-17T13:44:40.507849Z","shell.execute_reply":"2022-12-17T13:44:40.778761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.with_columns([\n    (pl.col('total_occurs_as_carts_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('cart_over_click_ratio_each_aid'),\n    (pl.col('total_occurs_as_orders_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('order_over_click_ratio_each_aid'),\n]).select([\n    pl.col('aid').sort_by('total_occurs_as_clicks_each_aid', reverse=True).alias('aid_clicks'),\n    pl.col('total_occurs_as_clicks_each_aid').sort(reverse=True),\n    \n\n]).limit(20)","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:53:14.472866Z","iopub.execute_input":"2022-12-17T13:53:14.473265Z","iopub.status.idle":"2022-12-17T13:53:14.667936Z","shell.execute_reply.started":"2022-12-17T13:53:14.473233Z","shell.execute_reply":"2022-12-17T13:53:14.666974Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.with_columns([\n    (pl.col('total_occurs_as_carts_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('cart_over_click_ratio_each_aid'),\n    (pl.col('total_occurs_as_orders_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('order_over_click_ratio_each_aid'),\n]).select([\n\n    \n    pl.col('aid').sort_by('total_occurs_as_carts_each_aid', reverse=True).alias('aid_carts'),\n    pl.col('cart_over_click_ratio_each_aid').sort_by('total_occurs_as_carts_each_aid', reverse=True),\n    pl.col('total_occurs_as_carts_each_aid').sort(reverse=True),\n    \n\n]).limit(50).sort('cart_over_click_ratio_each_aid', reverse=True)","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:56:03.184824Z","iopub.execute_input":"2022-12-17T13:56:03.185205Z","iopub.status.idle":"2022-12-17T13:56:03.417005Z","shell.execute_reply.started":"2022-12-17T13:56:03.185175Z","shell.execute_reply":"2022-12-17T13:56:03.415549Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.with_columns([\n    (pl.col('total_occurs_as_carts_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('cart_over_click_ratio_each_aid'),\n    (pl.col('total_occurs_as_orders_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('order_over_click_ratio_each_aid'),\n]).select([\n\n    \n    pl.col('aid').sort_by('total_occurs_as_orders_each_aid', reverse=True).alias('aid_orders'),\n    pl.col('order_over_click_ratio_each_aid').sort_by('total_occurs_as_orders_each_aid', reverse=True),    \n    pl.col('total_occurs_as_orders_each_aid').sort(reverse=True),    \n\n]).limit(20).sort('order_over_click_ratio_each_aid', reverse=True)","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:57:37.790639Z","iopub.execute_input":"2022-12-17T13:57:37.791059Z","iopub.status.idle":"2022-12-17T13:57:38.041414Z","shell.execute_reply.started":"2022-12-17T13:57:37.791027Z","shell.execute_reply":"2022-12-17T13:57:38.040520Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.with_columns([\n    (pl.col('total_occurs_as_carts_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('cart_over_click_ratio_each_aid'),\n    (pl.col('total_occurs_as_orders_each_aid')/pl.col('total_occurs_as_clicks_each_aid')).alias('order_over_click_ratio_each_aid'),\n]).select([\n    pl.col('aid').sort_by('total_occurs_as_clicks_each_aid', reverse=True).alias('aid_clicks'),\n    pl.col('total_occurs_as_clicks_each_aid').sort(reverse=True),\n    pl.col('aid').sort_by('total_occurs_as_carts_each_aid', reverse=True).alias('aid_carts'),\n    pl.col('total_occurs_as_carts_each_aid').sort(reverse=True),\n    pl.col('aid').sort_by('total_occurs_as_orders_each_aid', reverse=True).alias('aid_orders'),\n    pl.col('total_occurs_as_orders_each_aid').sort(reverse=True),    \n    pl.col('aid').sort_by('cart_over_click_ratio_each_aid', reverse=True).alias('aid_cart_over_click'),\n    pl.col('cart_over_click_ratio_each_aid').sort(reverse=True),\n    pl.col('aid').sort_by('order_over_click_ratio_each_aid', reverse=True).alias('aid_order_over_click'),\n    pl.col('order_over_click_ratio_each_aid').sort(reverse=True)\n]).limit(1000).with_columns([\n    pl.col('aid_carts').list(),\n    pl.col('aid_orders').list(),\n    pl.col('aid_cart_over_click').is_in('aid_carts'),\n    pl.col('aid_order_over_click').is_in('aid_orders'),        \n]).select([\n    pl.col('aid_cart_over_click').sum(),\n    pl.col('aid_order_over_click').sum(),    \n])\n#     .select([\n#     pl.col('aid_carts').is_in()\n# ])","metadata":{"execution":{"iopub.status.busy":"2022-12-17T13:44:40.801042Z","iopub.status.idle":"2022-12-17T13:44:40.802234Z","shell.execute_reply.started":"2022-12-17T13:44:40.801753Z","shell.execute_reply":"2022-12-17T13:44:40.801910Z"},"trusted":true},"execution_count":null,"outputs":[]}]}