{"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":"# Otto: Breaking out the Dates 📅 \n\nHere is a quick notebook to help!\n\nIn eCommerce, days matter, months matter, even hours matter! Just looking at the timestamp isn't helpful if you want to truly evaluate the data. ","metadata":{}},{"cell_type":"code","source":"# bring in the packages we will need\nimport numpy as np\nimport pandas as pd","metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","execution":{"iopub.status.busy":"2022-11-19T13:37:14.423618Z","iopub.execute_input":"2022-11-19T13:37:14.424104Z","iopub.status.idle":"2022-11-19T13:37:14.448271Z","shell.execute_reply.started":"2022-11-19T13:37:14.423995Z","shell.execute_reply":"2022-11-19T13:37:14.447006Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# grab the data path\nclass CFG:\n    data_path = '/kaggle/input/otto-recommender-system/'","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:37:14.450065Z","iopub.execute_input":"2022-11-19T13:37:14.450362Z","iopub.status.idle":"2022-11-19T13:37:14.454266Z","shell.execute_reply.started":"2022-11-19T13:37:14.450334Z","shell.execute_reply":"2022-11-19T13:37:14.453448Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Convert json to csv","metadata":{}},{"cell_type":"code","source":"# if you want to convert your json to a dataframe\n# grab this great snippet of code from \n# https://www.kaggle.com/code/columbia2131/otto-read-a-chunk-of-jsonl-to-manageable-df/\n\ntrain = pd.DataFrame()\nchunks = pd.read_json(CFG.data_path + 'train.jsonl', lines=True, chunksize=100_000)\n\n\nfor e, chunk in enumerate(chunks):\n    event_dict = {\n        'session': [],\n        'aid': [],\n        'ts': [],\n        'type': [],\n    }\n    if e < 2:\n        # train_sessions = pd.concat([train_sessions, chunk])\n        for session, events in zip(chunk['session'].tolist(), chunk['events'].tolist()):\n            for event in events:\n                event_dict['session'].append(session)\n                event_dict['aid'].append(event['aid'])\n                event_dict['ts'].append(event['ts'])\n                event_dict['type'].append(event['type'])\n        chunk_session = pd.DataFrame(event_dict)\n        train = pd.concat([train, chunk_session])\n    else:\n        break\n        \ntrain = train.reset_index(drop=True)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:37:14.455547Z","iopub.execute_input":"2022-11-19T13:37:14.456105Z","iopub.status.idle":"2022-11-19T13:38:06.959396Z","shell.execute_reply.started":"2022-11-19T13:37:14.456073Z","shell.execute_reply":"2022-11-19T13:38:06.958286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# View the data as a dataframe","metadata":{}},{"cell_type":"code","source":"# view the dataframe\ndisplay(train)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:38:06.960779Z","iopub.execute_input":"2022-11-19T13:38:06.961106Z","iopub.status.idle":"2022-11-19T13:38:06.980908Z","shell.execute_reply.started":"2022-11-19T13:38:06.961075Z","shell.execute_reply":"2022-11-19T13:38:06.980169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# The columns and definitions\n\n**session** - the unique session id [a better name is **customer_id**]\n\n**aid** - the article id [better name is **product_code**] of the associated event\n\n**ts** - the Unix [**timestamp**] of the event\n\n**type** - the [**event type**], i.e., whether a product was clicked, added to the user's cart, or ordered during the session","metadata":{}},{"cell_type":"code","source":"# change the column names the more obvious ones highlighted above\ntrain.rename(index=str, columns={'session': 'customer_id',\n                              'aid' : 'product_code',\n                              'ts' : 'time_stamp',\n                              'type' : 'event_type'}, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:38:06.983101Z","iopub.execute_input":"2022-11-19T13:38:06.984232Z","iopub.status.idle":"2022-11-19T13:38:09.929682Z","shell.execute_reply.started":"2022-11-19T13:38:06.984184Z","shell.execute_reply":"2022-11-19T13:38:09.928112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# View the new column titles","metadata":{}},{"cell_type":"code","source":"# view the first 10 rows\ntrain.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:38:09.931571Z","iopub.execute_input":"2022-11-19T13:38:09.932641Z","iopub.status.idle":"2022-11-19T13:38:09.944840Z","shell.execute_reply.started":"2022-11-19T13:38:09.932589Z","shell.execute_reply":"2022-11-19T13:38:09.943890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Breaking out the Dates!","metadata":{}},{"cell_type":"code","source":"# convert the unix timestamp to datetime -- you will see a lot of \n# stackoverflow showing unit='s' -- that is for seconds, but this\n# timestamp is in milliseconds!\ntrain['date'] = pd.to_datetime(train['time_stamp'], unit='ms')\ntrain.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:38:09.946621Z","iopub.execute_input":"2022-11-19T13:38:09.947307Z","iopub.status.idle":"2022-11-19T13:38:10.172645Z","shell.execute_reply.started":"2022-11-19T13:38:09.947273Z","shell.execute_reply":"2022-11-19T13:38:10.171487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# add year and month as a column\ntrain.insert(loc=2, column='year_month', value=train['date'].map(lambda x: 100*x.year + x.month))\ntrain.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:38:10.174052Z","iopub.execute_input":"2022-11-19T13:38:10.174408Z","iopub.status.idle":"2022-11-19T13:38:57.974042Z","shell.execute_reply.started":"2022-11-19T13:38:10.174376Z","shell.execute_reply":"2022-11-19T13:38:57.972914Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# break out the year \ntrain.insert(loc=3, column='year', value=train.date.dt.year)\ntrain.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:38:57.975636Z","iopub.execute_input":"2022-11-19T13:38:57.976005Z","iopub.status.idle":"2022-11-19T13:38:58.971630Z","shell.execute_reply.started":"2022-11-19T13:38:57.975971Z","shell.execute_reply":"2022-11-19T13:38:58.970474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# break out the month\ntrain.insert(loc=4, column='month', value=train.date.dt.month)\ntrain.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:38:58.973162Z","iopub.execute_input":"2022-11-19T13:38:58.973502Z","iopub.status.idle":"2022-11-19T13:39:00.031463Z","shell.execute_reply.started":"2022-11-19T13:38:58.973471Z","shell.execute_reply":"2022-11-19T13:39:00.030293Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# break out the day\n# +1 to make Monday=1.....until Sunday=7\ntrain.insert(loc=5, column='day', value=(train.date.dt.dayofweek)+1)\ntrain.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:39:00.032803Z","iopub.execute_input":"2022-11-19T13:39:00.033289Z","iopub.status.idle":"2022-11-19T13:39:01.168088Z","shell.execute_reply.started":"2022-11-19T13:39:00.033250Z","shell.execute_reply":"2022-11-19T13:39:01.166998Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# break out the hour \ntrain.insert(loc=6, column='hour', value=train.date.dt.hour)\ntrain.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:39:01.169296Z","iopub.execute_input":"2022-11-19T13:39:01.169642Z","iopub.status.idle":"2022-11-19T13:39:02.186662Z","shell.execute_reply.started":"2022-11-19T13:39:01.169612Z","shell.execute_reply":"2022-11-19T13:39:02.185288Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.to_csv(\"otto-breaking-out-dates.csv\", index=False)","metadata":{"execution":{"iopub.status.busy":"2022-11-19T13:39:02.188727Z","iopub.execute_input":"2022-11-19T13:39:02.189475Z","iopub.status.idle":"2022-11-19T13:40:06.315728Z","shell.execute_reply.started":"2022-11-19T13:39:02.189429Z","shell.execute_reply":"2022-11-19T13:40:06.314882Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Upvote if this helped 🤓","metadata":{}}]}