{"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":"# Create the cv's features for ranker model \n\nI publish this NOTEBOOK in which I created features for final submission.\nWith this feature, the LB is about 0.577.\nI used \"TVU VM v3-8\" as the execution environment because it tends to run out of memory.\nIf you have any other good feature amount, please let me know.\n\nHere is a great document that I used as a reference.\n- https://www.kaggle.com/code/seholee/otto-generate-features-for-training\n- https://www.kaggle.com/competitions/otto-recommender-system/discussion/370210","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport glob\nimport gc\nimport numpy as np\n\ntype_labels = {'clicks':0, 'carts':1, 'orders':2}","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def load_train():\n    dfs = []\n    for e, chunk_file in enumerate(glob.glob('../input/otto-validation/train_parquet/*')):\n        chunk = pd.read_parquet(chunk_file)\n        chunk.ts = (chunk.ts/1000).astype('int32')\n        chunk['type'] = chunk['type'].map(type_labels).astype('int8')\n        dfs.append(chunk)\n    return pd.concat(dfs).reset_index(drop=True)\n\ndf = load_train()\nprint('All data has shape', df.shape)\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2023-01-29T00:46:35.704256Z","iopub.execute_input":"2023-01-29T00:46:35.704734Z","iopub.status.idle":"2023-01-29T00:47:19.284171Z","shell.execute_reply.started":"2023-01-29T00:46:35.704690Z","shell.execute_reply":"2023-01-29T00:47:19.283031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['hhmm'] = df.ts % (24 * 60 * 60)\ndf['day'] = (df.ts // (24 * 60 * 60) % 7).astype('int32')\ndf['date'] = (df.ts // (60 * 60 * 24)).astype('int32')\ndf['hour'] = df.ts % (24 * 60 * 60) // (60 * 60 * 3)","metadata":{"execution":{"iopub.status.busy":"2023-01-29T00:47:19.285876Z","iopub.execute_input":"2023-01-29T00:47:19.286216Z","iopub.status.idle":"2023-01-29T00:47:24.265146Z","shell.execute_reply.started":"2023-01-29T00:47:19.286186Z","shell.execute_reply":"2023-01-29T00:47:24.263805Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['is_click_event'] = df['type'] == 0\ndf['is_cart_event'] = df['type'] == 1\ndf['is_order_event'] = df['type'] == 2","metadata":{"execution":{"iopub.status.busy":"2023-01-29T00:47:24.267357Z","iopub.execute_input":"2023-01-29T00:47:24.268392Z","iopub.status.idle":"2023-01-29T00:47:24.604915Z","shell.execute_reply.started":"2023-01-29T00:47:24.268342Z","shell.execute_reply":"2023-01-29T00:47:24.603583Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\nitem_features = df.groupby(['aid']).agg(\n    {\n        'aid':'count',\n        'session':'nunique',\n        'is_click_event': ['sum', 'mean'], \n        'is_cart_event': ['sum', 'mean'], \n        'is_order_event': ['sum', 'mean'],\n        'hhmm': ['mean', 'std'],\n    }\n).droplevel(level=0, axis=1)\n\nitem_features.columns = [\n    'item_item_count',\n    'item_user_count',\n    'item_click_count',\n    'item_cart_count',\n    'item_order_count',\n    'item_click_ratio',\n    'item_cart_ratio',\n    'item_order_ratio',\n    'hhmm_mean',\n    'hhmm_std'\n]\n\nitem_features['item_cart_by_click_ratio'] = item_features['item_cart_count'] / (item_features['item_click_count'] + 1)\nitem_features['item_order_by_click_ratio'] = item_features['item_order_count'] / (item_features['item_click_count'] + 1)\nitem_features['item_order_by_cart_ratio'] = item_features['item_order_count'] / (item_features['item_cart_count'] + 1)\n\nitem_features.to_parquet('item_features.pqt')\n\nprint(item_features.head(10))\n\ndel item_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-28T01:29:16.696188Z","iopub.execute_input":"2023-01-28T01:29:16.696631Z","iopub.status.idle":"2023-01-28T01:29:16.842419Z","shell.execute_reply.started":"2023-01-28T01:29:16.696594Z","shell.execute_reply":"2023-01-28T01:29:16.840984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\nitem_features = df.groupby([\n    'aid',\n    'date'\n]).agg(\n    {\n        'aid':'count',\n        'session':'nunique',\n        'is_click_event': ['sum', 'mean'], \n        'is_cart_event': ['sum', 'mean'], \n        'is_order_event': ['sum', 'mean']\n    }\n).droplevel(level=0, axis=1)\n\nitem_features.columns = [\n    'item_item_count',\n    'item_user_count',\n    'item_click_count',\n    'item_cart_count',\n    'item_order_count',\n    'item_click_ratio',\n    'item_cart_ratio',\n    'item_order_ratio',\n]\nitem_features = item_features.groupby('aid', as_index=False).agg(['median', 'min', 'max', 'mean', 'std'])\nitem_features.columns = item_features.columns.map('-'.join).str.strip('-')\nitem_features.to_parquet('daily_item_features.pqt')\n\ndel item_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-28T01:29:24.683988Z","iopub.execute_input":"2023-01-28T01:29:24.684442Z","iopub.status.idle":"2023-01-28T01:29:24.870991Z","shell.execute_reply.started":"2023-01-28T01:29:24.684403Z","shell.execute_reply":"2023-01-28T01:29:24.869633Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\nitem_features = df.groupby([\n    'aid',\n    'hour'\n]).agg(\n    {\n        'is_click_event': ['sum'], \n        'is_cart_event': ['sum'], \n        'is_order_event': ['sum'],\n    }\n).droplevel(level=0, axis=1).reset_index(level=\"hour\")\n\nitem_features.columns = [\n    'hour',\n    'item_click_count',\n    'item_cart_count',\n    'item_order_count',\n]\n\nfor i in range(12):\n    for col in [\n        'item_click_count',\n        'item_cart_count',\n        'item_order_count',\n    ]:\n        item_features[f'hour_{col}_{i}'] = item_features[item_features['hour'] == i].groupby('aid')[\n            col\n        ].transform('sum')\n        item_features[f'hour_{col}_{i}'] = item_features[f'hour_{col}_{i}'].fillna(0).astype('int32')\n        item_features[f'hour_{col}_{i}'] = (item_features[f'hour_{col}_{i}']/item_features[col]).fillna(0)\n\nitem_features = item_features.groupby('aid').min().drop([\n    'hour', 'item_click_count', 'item_cart_count', 'item_order_count'\n], axis=True)\nitem_features.to_parquet('hourly_item_features.pqt')\n\ndel item_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-29T02:44:31.067212Z","iopub.execute_input":"2023-01-29T02:44:31.067834Z","iopub.status.idle":"2023-01-29T02:44:36.341014Z","shell.execute_reply.started":"2023-01-29T02:44:31.067802Z","shell.execute_reply":"2023-01-29T02:44:36.339671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\nitem_features = df.groupby([\n    'aid',\n    'day'\n]).agg(\n    {\n        'aid':'count',\n        'session':'nunique',\n        'is_click_event': ['sum'], \n        'is_cart_event': ['sum'], \n        'is_order_event': ['sum'],\n    }\n).droplevel(level=0, axis=1).reset_index(level=\"day\")\n\nitem_features.columns = [\n    'day',\n    'item_item_count',\n    'item_user_count',\n    'item_click_count',\n    'item_cart_count',\n    'item_order_count',\n]\n\nfor i in range(7):\n    for col in [\n        'item_item_count',\n        'item_user_count',\n        'item_click_count',\n        'item_cart_count',\n        'item_order_count',\n    ]:\n        item_features[f'day_{col}_{i}'] = item_features[item_features['day'] == i].groupby('aid')[\n            col\n        ].transform('sum')\n        item_features[f'day_{col}_{i}'] = item_features[f'day_{col}_{i}'].fillna(0).astype('int32')\n        item_features[f'day_{col}_{i}_ratio'] = (item_features[f'day_{col}_{i}']/item_features[col]).fillna(0)\n\nitem_features = item_features.groupby('aid').min().drop([\n    'day', 'item_item_count', 'item_user_count', 'item_click_count', 'item_cart_count', 'item_order_count'\n], axis=True)\nitem_features.to_parquet('day_item_features.pqt')\n\ndel item_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-29T02:45:34.994897Z","iopub.execute_input":"2023-01-29T02:45:34.995768Z","iopub.status.idle":"2023-01-29T02:45:35.846302Z","shell.execute_reply.started":"2023-01-29T02:45:34.995729Z","shell.execute_reply":"2023-01-29T02:45:35.844232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def load_test():\n    dfs = []\n    for e, chunk_file in enumerate(glob.glob('../input/otto-validation/test_parquet/*')):\n        chunk = pd.read_parquet(chunk_file)\n        chunk.ts = (chunk.ts/1000).astype('int32')\n        chunk['type'] = chunk['type'].map(type_labels).astype('int8')\n        dfs.append(chunk)\n    return pd.concat(dfs).reset_index(drop=True) #.astype({\"ts\": \"datetime64[ms]\"})\n\ndf = load_test()\nprint('All data has shape', df.shape)\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2023-01-06T15:06:01.219010Z","iopub.execute_input":"2023-01-06T15:06:01.219459Z","iopub.status.idle":"2023-01-06T15:06:04.117746Z","shell.execute_reply.started":"2023-01-06T15:06:01.219420Z","shell.execute_reply":"2023-01-06T15:06:04.116436Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"co_visitation_matrix = \"../input/fixed-create-co-visitation-matrix-for-cv\"\n\nVER = 5\nDISK_PIECES = 15\n\n# LOAD THREE CO-VISITATION MATRICES\ndef pqt_to_dict(df):\n    return df.groupby('aid_x').aid_y.apply(list).to_dict()\n\ntop_buys = pqt_to_dict( pd.read_parquet(f'{co_visitation_matrix}/top_carts_orders_v{VER}_0.pqt') )\nfor k in range(1,DISK_PIECES): \n    top_buys.update( pqt_to_dict( pd.read_parquet(f'{co_visitation_matrix}/top_carts_orders_v{VER}_{k}.pqt') ) )\n    \ntop_buy2buy = pqt_to_dict( pd.read_parquet(f'{co_visitation_matrix}/top_buy2buy_v{VER}_0.pqt') )\nfor k in range(1,DISK_PIECES):\n    top_buy2buy.update( pqt_to_dict( pd.read_parquet(f'{co_visitation_matrix}/top_buy2buy_v{VER}_{k}.pqt') ) )","metadata":{"execution":{"iopub.status.busy":"2023-01-06T16:12:51.630438Z","iopub.execute_input":"2023-01-06T16:12:51.630834Z","iopub.status.idle":"2023-01-06T16:13:59.710038Z","shell.execute_reply.started":"2023-01-06T16:12:51.630803Z","shell.execute_reply":"2023-01-06T16:13:59.708768Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from collections import Counter\nimport itertools\n\ndef func(df):\n    aids=df.aid.tolist()\n    unique_aids = list(dict.fromkeys(aids[::-1] ))\n    aids2 = pd.Series(list(itertools.chain(*[top_buys[aid] for aid in unique_aids if aid in top_buys])), name=('aid'))\n    \n    return aids2.value_counts()\n    \nbuy_co_visitation_features = df.groupby('session').apply(lambda x: func(x)).reset_index()\nbuy_co_visitation_features.columns = ['session', 'aid', 'buy_co_visitation_count']\nbuy_co_visitation_features.to_parquet('buy_co_visitation_features.pqt')\n\ndel buy_co_visitation_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-06T16:08:02.730374Z","iopub.execute_input":"2023-01-06T16:08:02.730790Z","iopub.status.idle":"2023-01-06T16:08:02.751049Z","shell.execute_reply.started":"2023-01-06T16:08:02.730756Z","shell.execute_reply":"2023-01-06T16:08:02.749891Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def func(df):\n    aids=df.aid.tolist()\n    unique_aids = list(dict.fromkeys(aids[::-1] ))\n    aids2 = pd.Series(list(itertools.chain(*[top_buy2buy[aid] for aid in unique_aids if aid in top_buy2buy])), name=('aid'))\n    \n    return aids2.value_counts()\n    \nbuy2buy_co_visitation_features = df.groupby('session').apply(lambda x: func(x)).reset_index()\nbuy2buy_co_visitation_features.columns = ['session', 'aid', 'buy2buy_co_visitation_count']\nbuy2buy_co_visitation_features.to_parquet('buy2buy_co_visitation_features.pqt')\n\ndel buy2buy_co_visitation_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-06T16:14:29.704170Z","iopub.execute_input":"2023-01-06T16:14:29.704715Z","iopub.status.idle":"2023-01-06T16:14:29.724977Z","shell.execute_reply.started":"2023-01-06T16:14:29.704666Z","shell.execute_reply":"2023-01-06T16:14:29.723825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from datetime import datetime\n\nrange_of_day = (datetime.fromtimestamp(df.ts.max()) - datetime.fromtimestamp(df.ts.max())).days + 1","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['hhmm'] = df.ts % (24 * 60 * 60)\ndf['day'] = (df.ts // (24 * 60 * 60) % 7).astype('int32')\ndf['date'] = (df.ts // (60 * 60 * 24)).astype('int32')\ndf['hour'] = df.ts % (24 * 60 * 60) // (60 * 60)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['is_click_event'] = df['type'] == 0\ndf['is_cart_event'] = df['type'] == 1\ndf['is_order_event'] = df['type'] == 2","metadata":{"execution":{"iopub.status.busy":"2023-01-03T05:02:49.588076Z","iopub.status.idle":"2023-01-03T05:02:49.588843Z","shell.execute_reply.started":"2023-01-03T05:02:49.588603Z","shell.execute_reply":"2023-01-03T05:02:49.588626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"item_features = df.groupby(['aid']).agg(\n    {\n        'aid':'count',\n        'session':'nunique',\n        'is_click_event': ['sum', 'mean'], \n        'is_cart_event': ['sum', 'mean'], \n        'is_order_event': ['sum', 'mean'],\n        'hhmm': ['mean', 'std'],\n    }\n).droplevel(level=0, axis=1)\n\nitem_features.columns = [\n    'latest_item_item_count',\n    'latest_item_user_count',\n    'latest_item_click_count',\n    'latest_item_cart_count',\n    'latest_item_order_count',\n    'latest_item_click_ratio',\n    'latest_item_cart_ratio',\n    'latest_item_order_ratio',\n    'latest_hhmm_mean',\n    'latest_hhmm_std'\n]\n    \n\nitem_features.to_parquet('latest_item_features.pqt')\n\ndel item_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-03T05:02:49.590306Z","iopub.status.idle":"2023-01-03T05:02:49.590728Z","shell.execute_reply.started":"2023-01-03T05:02:49.590505Z","shell.execute_reply":"2023-01-03T05:02:49.590525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"user_features = df.groupby('session').agg(\n    {\n        'aid':'count',\n        'session':'nunique',\n        'is_click_event': ['sum', 'mean'], \n        'is_cart_event': ['sum', 'mean'], \n        'is_order_event': ['sum', 'mean'],\n        'hhmm': ['mean', 'std'],\n    }\n).droplevel(level=0, axis=1)\n\nuser_features.columns = [\n    'user_user_count',\n    'user_item_count',\n    'user_click_count',\n    'user_cart_count',\n    'user_order_count',\n    'user_click_ratio',\n    'user_cart_ratio',\n    'user_order_ratio',\n    'user_hhmm_mean',\n    'user_hhmm_std'\n]\n\nuser_features.to_parquet('user_features.pqt')\n\ndel user_features\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-03T05:02:49.592572Z","iopub.status.idle":"2023-01-03T05:02:49.592860Z","shell.execute_reply.started":"2023-01-03T05:02:49.592716Z","shell.execute_reply":"2023-01-03T05:02:49.592730Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.sort_values(by=['session', 'aid', 'ts']).reset_index(drop=True)\n\ndf['progress_of_session'] = df.groupby(['session']).agg(\n    progress_of_session=('aid', lambda x: pd.Series(np.arange(len(x)) / len(x), x.index))\n).explode('progress_of_session').reset_index()['progress_of_session']\n\nuser_item_features = df.groupby(['session', 'aid']).agg(\n    {\n        'type': 'count',\n        'is_click_event': ['mean', 'sum'], \n        'is_cart_event': ['mean', 'sum'], \n        'is_order_event': ['mean', 'sum'],\n        'progress_of_session': ['min', 'max'],\n        'hhmm': ['mean', 'std'],\n    }\n).droplevel(level=0, axis=1)\nuser_item_features.columns = [\n    'user_item_pair_count',\n    'user_item_pair_click_count',\n    'user_item_pair_cart_count',\n    'user_item_pair_order_count',\n    'user_item_pair_click_ratio',\n    'user_item_pair_cart_ratio',\n    'user_item_pair_order_ratio',\n    'min_progress_of_session',\n    'max_progress_of_session',\n    'user_item_pair_hhmm_mean',\n    'user_item_pair_hhmm_std',\n]\n\nuser_item_features.to_parquet('user_item_features.pqt')\n\ndel user_item_features, df\n_ = gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-01-03T05:02:49.593509Z","iopub.status.idle":"2023-01-03T05:02:49.593799Z","shell.execute_reply.started":"2023-01-03T05:02:49.593653Z","shell.execute_reply":"2023-01-03T05:02:49.593666Z"},"trusted":true},"execution_count":null,"outputs":[]}]}