{"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":"## This notebook generates bunch of features.\n## If anybody shares a faster version of thie notebook, I would be more than grateful. (especially the hourly ratio generation for aids)\n\n## Versions\n- ver5: only test data is used for session. + minor bug fixes","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nfrom datetime import datetime\nimport matplotlib.pyplot as plt","metadata":{"execution":{"iopub.status.busy":"2023-01-13T13:09:34.987007Z","iopub.execute_input":"2023-01-13T13:09:34.987507Z","iopub.status.idle":"2023-01-13T13:09:35.021953Z","shell.execute_reply.started":"2023-01-13T13:09:34.987409Z","shell.execute_reply":"2023-01-13T13:09:35.020989Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# !pip install pyarrow fastparquet","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:13:48.966883Z","iopub.execute_input":"2023-01-12T11:13:48.967315Z","iopub.status.idle":"2023-01-12T11:13:48.973252Z","shell.execute_reply.started":"2023-01-12T11:13:48.967283Z","shell.execute_reply":"2023-01-12T11:13:48.971715Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## General Approach Summary\n- use test.parquet for session data\n- use train.parquet + test.parquet for aid data\n- merge them for final training data\n- you can split test.parquet in two pieces for training and validation so that you don't have to submit every time.","metadata":{}},{"cell_type":"markdown","source":"## Prepare Session data","metadata":{}},{"cell_type":"code","source":"df = pd.read_parquet('/kaggle/input/otto-train-and-test-data-for-local-validation/test.parquet')\ndf2 = df.iloc[0:10000]\ndel df\ndf = df2\ndel df2","metadata":{"execution":{"iopub.status.busy":"2023-01-13T13:10:26.041885Z","iopub.execute_input":"2023-01-13T13:10:26.042371Z","iopub.status.idle":"2023-01-13T13:10:41.179857Z","shell.execute_reply.started":"2023-01-13T13:10:26.042331Z","shell.execute_reply":"2023-01-13T13:10:41.178869Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(datetime.fromtimestamp(np.min(df.ts)))\nprint(datetime.fromtimestamp(np.max(df.ts)))","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:19.733259Z","iopub.execute_input":"2023-01-12T11:38:19.733629Z","iopub.status.idle":"2023-01-12T11:38:19.739966Z","shell.execute_reply.started":"2023-01-12T11:38:19.733596Z","shell.execute_reply":"2023-01-12T11:38:19.739177Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:19.741371Z","iopub.execute_input":"2023-01-12T11:38:19.741965Z","iopub.status.idle":"2023-01-12T11:38:19.757606Z","shell.execute_reply.started":"2023-01-12T11:38:19.741933Z","shell.execute_reply":"2023-01-12T11:38:19.756766Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Session Feature - session count","metadata":{}},{"cell_type":"code","source":"df['sess_cnt'] = df.groupby('session')['session'].transform('count')\ndf['sess_cnt'] = df['sess_cnt'].astype('int16')","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:19.762416Z","iopub.execute_input":"2023-01-12T11:38:19.763088Z","iopub.status.idle":"2023-01-12T11:38:19.772069Z","shell.execute_reply.started":"2023-01-12T11:38:19.763051Z","shell.execute_reply":"2023-01-12T11:38:19.771229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.hist(df.sess_cnt, bins=200)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:19.774263Z","iopub.execute_input":"2023-01-12T11:38:19.775533Z","iopub.status.idle":"2023-01-12T11:38:20.302363Z","shell.execute_reply.started":"2023-01-12T11:38:19.775488Z","shell.execute_reply":"2023-01-12T11:38:20.301047Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Session Feature - session average time of the day + std of that","metadata":{}},{"cell_type":"code","source":"%%time\ndts = pd.to_datetime(df['ts'], unit='s') ## pandas recognizes your format\n\ndf['day'] = (dts.dt.weekday).astype('int8')\ndf['hour'] = (dts.dt.hour).astype('int8')\ndf['hm'] = (dts.dt.hour*100 + dts.dt.minute*100//60).astype('int16')","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:20.304199Z","iopub.execute_input":"2023-01-12T11:38:20.304893Z","iopub.status.idle":"2023-01-12T11:38:20.322718Z","shell.execute_reply.started":"2023-01-12T11:38:20.304858Z","shell.execute_reply":"2023-01-12T11:38:20.321326Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['hm_mean'] = df.groupby('session')['hm'].transform('mean')\ndf['hm_std'] = df.groupby('session')['hm'].transform('std')\ndf['hm_std'] = df['hm_std'].fillna(0)","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:20.324416Z","iopub.execute_input":"2023-01-12T11:38:20.324812Z","iopub.status.idle":"2023-01-12T11:38:20.335007Z","shell.execute_reply.started":"2023-01-12T11:38:20.324778Z","shell.execute_reply":"2023-01-12T11:38:20.333647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['hm_mean'] = df['hm_mean'].astype('int16')\ndf['hm_std'] = df['hm_std'].astype('int16')\ndf","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Session Features - weekday ratio + hour ratio","metadata":{}},{"cell_type":"code","source":"%%time\n# 요일별 cnt\nfor i in range(7):\n    df[f'day{i}cnt'] = df[df['day'] == i].groupby('session')['day'].transform('count')\n    df[f'day{i}cnt'] = df.groupby('session')[f'day{i}cnt'].transform(lambda x: x.fillna(x.min())) #  먼저 못 채운 값 채우기.\n    df[f'day{i}cnt'] = df.groupby('session')[f'day{i}cnt'].transform(lambda x: x.fillna(0)) # 전체 column이 0이면 모두 0으로. day7로 실험해보면 앎.\n    df[f'day{i}cnt'] = df[f'day{i}cnt']/df['sess_cnt']","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:20.336739Z","iopub.execute_input":"2023-01-12T11:38:20.337302Z","iopub.status.idle":"2023-01-12T11:38:20.726510Z","shell.execute_reply.started":"2023-01-12T11:38:20.337258Z","shell.execute_reply":"2023-01-12T11:38:20.725491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"np.max(df.hour)","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:20.727983Z","iopub.execute_input":"2023-01-12T11:38:20.728376Z","iopub.status.idle":"2023-01-12T11:38:20.736633Z","shell.execute_reply.started":"2023-01-12T11:38:20.728343Z","shell.execute_reply":"2023-01-12T11:38:20.735306Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 시간별 cnt\nfor i in range(24):\n    df[f'hour{i}cnt'] = df[df['hour'] == i].groupby('session')['hour'].transform('count')\n    df[f'hour{i}cnt'] = df.groupby('session')[f'hour{i}cnt'].transform(lambda x: x.fillna(x.min())) #  먼저 못 채운 값 채우기.\n    df[f'hour{i}cnt'] = df.groupby('session')[f'hour{i}cnt'].transform(lambda x: x.fillna(0)) # 전체 column이 0이면 모두 0으로. day7로 실험해보면 앎.\n    df[f'hour{i}cnt'] = df[f'hour{i}cnt']/df['sess_cnt']","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:20.742251Z","iopub.execute_input":"2023-01-12T11:38:20.742629Z","iopub.status.idle":"2023-01-12T11:38:22.534994Z","shell.execute_reply.started":"2023-01-12T11:38:20.742597Z","shell.execute_reply":"2023-01-12T11:38:22.533761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# session cnt\ndf","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.536465Z","iopub.execute_input":"2023-01-12T11:38:22.536924Z","iopub.status.idle":"2023-01-12T11:38:22.568060Z","shell.execute_reply.started":"2023-01-12T11:38:22.536889Z","shell.execute_reply":"2023-01-12T11:38:22.566876Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Session Feature - number of group of consecutive events within 1600 seconds (actual session count)","metadata":{}},{"cell_type":"code","source":"df['ts_diff'] = df.groupby('session')['ts'].transform('diff').fillna(0)\ndf['sess_cnt2'] = df[df['ts_diff'] > 1600].groupby('session')['ts_diff'].transform('count')\ndf['sess_cnt2'] = df.groupby('session')['sess_cnt2'].transform(lambda x: x.fillna(x.min()))\ndf['sess_cnt2'] = df.groupby('session')['sess_cnt2'].transform(lambda x: x.fillna(0))\ndf['sess_cnt2'] = df['sess_cnt2'].astype('int16')\ndf.drop('ts_diff', inplace=True, axis=1)","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.569490Z","iopub.execute_input":"2023-01-12T11:38:22.569822Z","iopub.status.idle":"2023-01-12T11:38:22.652124Z","shell.execute_reply.started":"2023-01-12T11:38:22.569794Z","shell.execute_reply":"2023-01-12T11:38:22.651163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.653353Z","iopub.execute_input":"2023-01-12T11:38:22.654340Z","iopub.status.idle":"2023-01-12T11:38:22.683320Z","shell.execute_reply.started":"2023-01-12T11:38:22.654305Z","shell.execute_reply":"2023-01-12T11:38:22.682288Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Session Feature - session buys, carts, clicks cnt + ratio","metadata":{}},{"cell_type":"code","source":"# number of buys, carts, and clicks\ndf['cl_cnt'] = df[df['type'] == 0].groupby('session')['type'].transform('count')\ndf['ca_cnt'] = df[df['type'] == 1].groupby('session')['type'].transform('count')\ndf['or_cnt'] = df[df['type'] == 2].groupby('session')['type'].transform('count')","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.684567Z","iopub.execute_input":"2023-01-12T11:38:22.685459Z","iopub.status.idle":"2023-01-12T11:38:22.704806Z","shell.execute_reply.started":"2023-01-12T11:38:22.685427Z","shell.execute_reply":"2023-01-12T11:38:22.703838Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['cl_cnt'] = df.groupby('session')['cl_cnt'].transform(lambda x: x.fillna(x.min()))\ndf['cl_cnt'] = df.groupby('session')['cl_cnt'].transform(lambda x: x.fillna(0))\ndf['ca_cnt'] = df.groupby('session')['ca_cnt'].transform(lambda x: x.fillna(x.min()))\ndf['ca_cnt'] = df.groupby('session')['ca_cnt'].transform(lambda x: x.fillna(0))\ndf['or_cnt'] = df.groupby('session')['or_cnt'].transform(lambda x: x.fillna(x.min()))\ndf['or_cnt'] = df.groupby('session')['or_cnt'].transform(lambda x: x.fillna(0))\ndf","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.706199Z","iopub.execute_input":"2023-01-12T11:38:22.706741Z","iopub.status.idle":"2023-01-12T11:38:22.900641Z","shell.execute_reply.started":"2023-01-12T11:38:22.706710Z","shell.execute_reply":"2023-01-12T11:38:22.899383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# click to cart, click to order, cart to order ratio\ndf['cl_ca_ratio'] = df['ca_cnt']/df['cl_cnt']\ndf['cl_or_ratio'] = df['or_cnt']/df['cl_cnt']\ndf['ca_or_ratio'] = df['or_cnt']/df['ca_cnt']","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.902275Z","iopub.execute_input":"2023-01-12T11:38:22.902773Z","iopub.status.idle":"2023-01-12T11:38:22.913862Z","shell.execute_reply.started":"2023-01-12T11:38:22.902728Z","shell.execute_reply":"2023-01-12T11:38:22.912345Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.915287Z","iopub.execute_input":"2023-01-12T11:38:22.915756Z","iopub.status.idle":"2023-01-12T11:38:22.959082Z","shell.execute_reply.started":"2023-01-12T11:38:22.915710Z","shell.execute_reply":"2023-01-12T11:38:22.958048Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['cl_ca_ratio'] = df['cl_ca_ratio'].fillna(0)\ndf['cl_or_ratio'] = df['cl_or_ratio'].fillna(0)\ndf['ca_or_ratio'] = df['ca_or_ratio'].fillna(0)","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:42:24.979804Z","iopub.execute_input":"2023-01-12T11:42:24.981079Z","iopub.status.idle":"2023-01-12T11:42:24.989226Z","shell.execute_reply.started":"2023-01-12T11:42:24.981033Z","shell.execute_reply":"2023-01-12T11:42:24.987992Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Session - minimum ts, average ts, maximum ts ( time passed since last visit, most active visit, first visit )","metadata":{}},{"cell_type":"code","source":"df['ss_ts_max'] = df.groupby('session')['ts'].transform('max')\ndf['ss_ts_min'] = df.groupby('session')['ts'].transform('min')\ndf['ss_ts_mean'] = df.groupby('session')['ts'].transform('mean')\ndf['ss_ts_mean'] = df['ss_ts_mean'].astype('int32')\ndf","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.to_parquet('sess_feats.parquet')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Prepare Aid Data","metadata":{}},{"cell_type":"code","source":"df = pd.concat([pd.read_parquet('/kaggle/input/otto-train-and-test-data-for-local-validation/train.parquet'),\n                pd.read_parquet('/kaggle/input/otto-train-and-test-data-for-local-validation/test.parquet')])\ndf2 = df.iloc[0:10000]\ndel df\ndf = df2\ndel df2","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Aid Features - aid count","metadata":{}},{"cell_type":"code","source":"df['aid_cnt'] = df.groupby('aid')['aid'].transform('count')\ndf['aid_cnt'] = df['aid_cnt'].astype('int16')","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.960527Z","iopub.execute_input":"2023-01-12T11:38:22.961246Z","iopub.status.idle":"2023-01-12T11:38:22.970763Z","shell.execute_reply.started":"2023-01-12T11:38:22.961215Z","shell.execute_reply":"2023-01-12T11:38:22.969645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.hist(df.aid_cnt, bins=200)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:22.972068Z","iopub.execute_input":"2023-01-12T11:38:22.972451Z","iopub.status.idle":"2023-01-12T11:38:23.495812Z","shell.execute_reply.started":"2023-01-12T11:38:22.972418Z","shell.execute_reply":"2023-01-12T11:38:23.494705Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Aid Features - aid average time mean and std","metadata":{}},{"cell_type":"code","source":"%%time\ndts = pd.to_datetime(df['ts'], unit='s') ## pandas recognizes your format\n\ndf['day'] = (dts.dt.weekday).astype('int8')\ndf['hour'] = (dts.dt.hour).astype('int8')\ndf['hm'] = (dts.dt.hour*100 + dts.dt.minute*100//60).astype('int16')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# item time mean and std\ndf['aid_hm_mean'] = df.groupby('aid')['hm'].transform('mean')\ndf['aid_hm_std'] = df.groupby('aid')['hm'].transform('std')\ndf['aid_hm_std'] = df['aid_hm_std'].fillna(0)","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:23.497375Z","iopub.execute_input":"2023-01-12T11:38:23.497744Z","iopub.status.idle":"2023-01-12T11:38:23.511578Z","shell.execute_reply.started":"2023-01-12T11:38:23.497709Z","shell.execute_reply":"2023-01-12T11:38:23.510394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:23.512810Z","iopub.execute_input":"2023-01-12T11:38:23.513184Z","iopub.status.idle":"2023-01-12T11:38:23.548414Z","shell.execute_reply.started":"2023-01-12T11:38:23.513151Z","shell.execute_reply":"2023-01-12T11:38:23.547199Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Aid Feature - weekday and hour ratio","metadata":{}},{"cell_type":"code","source":"%%time\n# 요일별 cnt\nfor i in range(7):\n    df[f'aid_day{i}cnt'] = df[df['day'] == i].groupby('aid')['day'].transform('count')\n    df[f'aid_day{i}cnt'] = df.groupby('aid')[f'aid_day{i}cnt'].transform(lambda x: x.fillna(x.min())) #  먼저 못 채운 값 채우기.\n    df[f'aid_day{i}cnt'] = df.groupby('aid')[f'aid_day{i}cnt'].transform(lambda x: x.fillna(0)) # 전체 column이 0이면 모두 0으로. day7로 실험해보면 앎.\n    df[f'aid_day{i}cnt'] = df[f'aid_day{i}cnt']/df['aid_cnt']","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:23.550003Z","iopub.execute_input":"2023-01-12T11:38:23.551209Z","iopub.status.idle":"2023-01-12T11:38:39.844853Z","shell.execute_reply.started":"2023-01-12T11:38:23.551158Z","shell.execute_reply":"2023-01-12T11:38:39.843354Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# 시간별 cnt\nfor i in range(24):\n    df[f'aid_hour{i}cnt'] = df[df['hour'] == i].groupby('aid')['hour'].transform('count')\n    df[f'aid_hour{i}cnt'] = df.groupby('aid')[f'aid_hour{i}cnt'].transform(lambda x: x.fillna(x.min())) #  먼저 못 채운 값 채우기.\n    df[f'aid_hour{i}cnt'] = df.groupby('aid')[f'aid_hour{i}cnt'].transform(lambda x: x.fillna(0)) # 전체 column이 0이면 모두 0으로. day7로 실험해보면 앎.\n    df[f'aid_hour{i}cnt'] = df[f'aid_hour{i}cnt']/df['aid_cnt']","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:38:39.846515Z","iopub.execute_input":"2023-01-12T11:38:39.846884Z","iopub.status.idle":"2023-01-12T11:39:36.931491Z","shell.execute_reply.started":"2023-01-12T11:38:39.846849Z","shell.execute_reply":"2023-01-12T11:39:36.930342Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Aid Feature - aid buys, carts, clicks cnt + ratio","metadata":{}},{"cell_type":"code","source":"# number of buys, carts, and clicks\ndf['aid_cl_cnt'] = df[df['type'] == 0].groupby('aid')['type'].transform('count')\ndf['aid_ca_cnt'] = df[df['type'] == 1].groupby('aid')['type'].transform('count')\ndf['aid_or_cnt'] = df[df['type'] == 2].groupby('aid')['type'].transform('count')\n\ndf['aid_cl_cnt'] = df.groupby('aid')['aid_cl_cnt'].transform(lambda x: x.fillna(x.min()))\ndf['aid_cl_cnt'] = df.groupby('aid')['aid_cl_cnt'].transform(lambda x: x.fillna(0))\ndf['aid_ca_cnt'] = df.groupby('aid')['aid_ca_cnt'].transform(lambda x: x.fillna(x.min()))\ndf['aid_ca_cnt'] = df.groupby('aid')['aid_ca_cnt'].transform(lambda x: x.fillna(0))\ndf['aid_or_cnt'] = df.groupby('aid')['aid_or_cnt'].transform(lambda x: x.fillna(x.min()))\ndf['aid_or_cnt'] = df.groupby('aid')['aid_or_cnt'].transform(lambda x: x.fillna(0))\ndf","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:39:36.932795Z","iopub.execute_input":"2023-01-12T11:39:36.933286Z","iopub.status.idle":"2023-01-12T11:39:43.687784Z","shell.execute_reply.started":"2023-01-12T11:39:36.933227Z","shell.execute_reply":"2023-01-12T11:39:43.686495Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['aid_cl_ca_ratio'] = df['aid_ca_cnt']/df['aid_cl_cnt']\ndf['aid_cl_or_ratio'] = df['aid_or_cnt']/df['aid_cl_cnt']\ndf['aid_ca_or_ratio'] = df['aid_or_cnt']/df['aid_ca_cnt']","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:39:43.689369Z","iopub.execute_input":"2023-01-12T11:39:43.689735Z","iopub.status.idle":"2023-01-12T11:39:43.697136Z","shell.execute_reply.started":"2023-01-12T11:39:43.689705Z","shell.execute_reply":"2023-01-12T11:39:43.696326Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['aid_cl_ca_ratio'] = df['aid_cl_ca_ratio'].fillna(0)\ndf['aid_cl_or_ratio'] = df['aid_cl_or_ratio'].fillna(0)\ndf['aid_ca_or_ratio'] = df['aid_ca_or_ratio'].fillna(0)","metadata":{"execution":{"iopub.status.busy":"2023-01-12T11:41:12.455224Z","iopub.execute_input":"2023-01-12T11:41:12.455656Z","iopub.status.idle":"2023-01-12T11:41:12.464281Z","shell.execute_reply.started":"2023-01-12T11:41:12.455621Z","shell.execute_reply":"2023-01-12T11:41:12.462983Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Aid Feature - same ts stuff","metadata":{}},{"cell_type":"code","source":"df['aid_ts_max'] = df.groupby('aid')['ts'].transform('max')\ndf['aid_ts_min'] = df.groupby('aid')['ts'].transform('min')\ndf['aid_ts_mean'] = df.groupby('aid')['ts'].transform('mean')\ndf['aid_ts_mean'] = df['aid_ts_mean'].astype('int32')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Now Merge","metadata":{}},{"cell_type":"code","source":"df[df['aid'] == 769168] # you can see they all have same values. so we just need to left join.","metadata":{"execution":{"iopub.status.busy":"2023-01-14T14:28:34.899113Z","iopub.execute_input":"2023-01-14T14:28:34.899459Z","iopub.status.idle":"2023-01-14T14:28:34.974166Z","shell.execute_reply.started":"2023-01-14T14:28:34.899391Z","shell.execute_reply":"2023-01-14T14:28:34.973039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_temp = pd.read_parquet('/kaggle/working/sess_feats.parquet')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cols_to_use = df.columns.difference(df_temp.columns)\ncols_to_use","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_temp.join(df[cols_to_use], how='left')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_temp.to_parquet('features.parquet')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Important Notes\n- There should be High Advantage in doing Normalization, because the distribution of original dataset and the dataset for submission has completely different numbers.","metadata":{}},{"cell_type":"markdown","source":"#### Idea Bank\n0. user이 어떤 아이템을 주로 사는지 cluster 번호로 넣어주어야함.\n1. 쇼핑몰 웹사이트 보면서 패턴 확인하기. 첫 페이지 UI들을 보면 insight들을 얻을 수 있다.\n만약 OTTO에서 제공한 데이터가 어떤 종류의 쇼핑몰인지 알면, 더욱 자세히 알 수 있다. 독일 => 쇼핑몰 => 가전 처럼 국가와 도메인을 좁혀서 보면\n어떤 걸 더 클릭할 가능성이 많은지 볼 수 있음.\n2. Youtube Recommendation System처럼 다른 recommendation system 만드는 내용 나오는 논문이나 자료에서 자기들이 썼다고 하는 feature들을 가져와서 쓰기\n3. Chris Deotte가 말한 features\n\n4. discussion에서 말한 feature\n\n5. word2vec - windowsize? => https://www.kaggle.com/competitions/otto-recommender-system/discussion/370751\n6. 3 types of classification loss => https://www.kaggle.com/competitions/otto-recommender-system/discussion/370502\n7. numba fast pipeline study => https://www.kaggle.com/code/carnozhao/otto-fast-cpu-end-to-end-pipeline\n","metadata":{}},{"cell_type":"markdown","source":"#### Deotte's Features\nYou make features for users and features for items. (Note in this competition, \"session\" actually means \"user\"). If we use a binary loss, then we feed our model user-item pairs and our model predicts a single number probability between 0 and 1 (that the user will order this item in future. Another model or output can predict click. And another cart.)\n\nInstead of inputting the user id and item id into our model, we input all of the user features concatenated with all of the item features for that particular user and particular item.\n\nExample user features\n\n- how many items has user already clicked\n- how many items has user already ordered\n- what is average hour that user clicks\n- what is average hour that user orders\n- how many real sessions does user have (real session define by time gap between activity)\n- what is average number of items in each user real session\n- what is last day of week user made activity (i.e. monday, tuesday)\n- what is first day of week user made activity\n- what is average time between clicks\n\nExample item features\n\n- has this item already been clicked by user\n- has this item already been added to cart by user\n- if already clicked, what is its relative order? 1 means last clicked, 2 means second to last clicked etc\n- has user clicked this item multiple times already? how many\n- how many items (that user has already clicked) have recommended this item with their co-visitation matrix\n- !!!when was date that this item was first seen in train\n- how many times what this item clicked in train\n- what is the average hour of day that this item is clicked\n- what is the average hour of day that this item is ordered\n- how popular is this item on monday (i.e. what percentage of monday clicks are this item)\n- how popular is this item on tuesday\n- what is the most common day of week this item is clicked\n\n- count up all unique items that were clicked immediately before and after. How many unique items have been clicked immediately before and after. (For example, maybe item only has 10 unique items that get clicked before and after. Whereas another item has 1000 unique items clicked before and after)\n\n- what percentage of users click this item more than once\n- has this item ever been bought in train data\n\nAbove are just examples, we can brainstorm 1000s of more features.","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}