{"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 is my little notebook, that explores problem discussed [here](https://www.kaggle.com/competitions/predict-student-performance-from-game-play/discussion/398732).","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\n\nimport seaborn as sns\nimport matplotlib.pyplot as plt","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:41:30.365370Z","iopub.execute_input":"2023-04-04T11:41:30.365842Z","iopub.status.idle":"2023-04-04T11:41:31.237731Z","shell.execute_reply.started":"2023-04-04T11:41:30.365802Z","shell.execute_reply":"2023-04-04T11:41:31.236478Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As we can see from the oficial GitHub [page](https://github.com/fielddaylab/jo_wilder/tree/5082da4057f30dd0917c97a65f2aa7be13469f79)\n\n<img src=\"https://i.ibb.co/XbvdBTG/2.png\"  width=50%>\n\nSo all fqid values come from room_fqid by substracting room name","metadata":{}},{"cell_type":"code","source":"dtypes={\n    'session_id':'category',\n    'event_name':'category',\n    'fqid':'category',\n    'room_fqid':'category',\n    'hover_duration':np.float64,\n}\n\nuse_columns=[\n    'session_id',\n    'event_name',\n    'fqid',\n    'room_fqid',\n    'hover_duration',\n]\ndf = pd.read_csv('/kaggle/input/predict-student-performance-from-game-play/train.csv',\n                 dtype=dtypes,\n                 usecols=use_columns)","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:41:31.243557Z","iopub.execute_input":"2023-04-04T11:41:31.246201Z","iopub.status.idle":"2023-04-04T11:43:15.848707Z","shell.execute_reply.started":"2023-04-04T11:41:31.246149Z","shell.execute_reply":"2023-04-04T11:43:15.847133Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's try to repeat the same process as described on GitHub","metadata":{}},{"cell_type":"code","source":"rooms = list(df['room_fqid'].unique())\nvalid_map_fqids = []\nfor room in rooms:\n    valid_map_fqids.append('.'.join(room.split('.')[:-1]))\nvalid_map_fqids = list(set(valid_map_fqids))\nvalid_map_fqids","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:15.850317Z","iopub.execute_input":"2023-04-04T11:43:15.850665Z","iopub.status.idle":"2023-04-04T11:43:15.997061Z","shell.execute_reply.started":"2023-04-04T11:43:15.850631Z","shell.execute_reply":"2023-04-04T11:43:15.995621Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"All these fqids can be seen on the map (capitol counted 3 times for every level_group)\n\n<img src=\"https://i.ibb.co/XyDwRtd/1.png\" width=50%>","metadata":{}},{"cell_type":"markdown","source":"Let's compare these values with fqids present in map_hover events.","metadata":{}},{"cell_type":"code","source":"map_hover = df[df['event_name'] == 'map_hover']\nall_map_fqids = list(map_hover['fqid'].unique())\nall_map_fqids","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:15.999566Z","iopub.execute_input":"2023-04-04T11:43:16.000061Z","iopub.status.idle":"2023-04-04T11:43:16.154103Z","shell.execute_reply.started":"2023-04-04T11:43:16.000018Z","shell.execute_reply":"2023-04-04T11:43:16.152714Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's remove all valid fqids.","metadata":{}},{"cell_type":"code","source":"invalid_fqids = []\nfor fqid in all_map_fqids:\n    if not fqid in valid_map_fqids:\n        invalid_fqids.append(fqid)\ninvalid_fqids","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:16.155552Z","iopub.execute_input":"2023-04-04T11:43:16.156020Z","iopub.status.idle":"2023-04-04T11:43:16.163610Z","shell.execute_reply.started":"2023-04-04T11:43:16.155968Z","shell.execute_reply":"2023-04-04T11:43:16.162503Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Before making the desicion, we need to check one fact","metadata":{}},{"cell_type":"code","source":"hovers = df[(df['event_name'] == 'map_hover') | (df['event_name'] == 'object_hover')]\nhovers['hover_duration'].isna().sum()","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:16.164777Z","iopub.execute_input":"2023-04-04T11:43:16.165794Z","iopub.status.idle":"2023-04-04T11:43:16.358400Z","shell.execute_reply.started":"2023-04-04T11:43:16.165759Z","shell.execute_reply":"2023-04-04T11:43:16.357493Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df[~df['hover_duration'].isna()]['event_name'].unique()","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:16.359480Z","iopub.execute_input":"2023-04-04T11:43:16.363441Z","iopub.status.idle":"2023-04-04T11:43:16.609890Z","shell.execute_reply.started":"2023-04-04T11:43:16.363376Z","shell.execute_reply":"2023-04-04T11:43:16.608904Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The event has type of map_hover or object_hover if and only if its hover_duration is not NaN, so all we are allowed only to interchange these two types and do not use others.\n\nNow let's move to the decision making. I will use ratio of map_hover and object_hover events for every fqid. If there is an inconsistency, we will dive deeper into the dataset and look fo examples.\n\nBut before building the chart, let's look at fqids that have type of hover event rarely. We do this, because normalized values they don't indicate us that hover events for this type of event are rare.","metadata":{}},{"cell_type":"code","source":"invalid_hovers = df[df['fqid'].isin(invalid_fqids) & \\\n                       df['event_name'].isin(['object_hover', 'map_hover'])]\nhovers_count = invalid_hovers.groupby('fqid').size()\nhovers_count = hovers_count[hovers_count > 0]\nhovers_count","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:16.611491Z","iopub.execute_input":"2023-04-04T11:43:16.612127Z","iopub.status.idle":"2023-04-04T11:43:17.054381Z","shell.execute_reply.started":"2023-04-04T11:43:16.612088Z","shell.execute_reply":"2023-04-04T11:43:17.052998Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So coffee, crane ranger, directory can't be dropped before analysing rations. In this step only directory can be argued, but I assume it to be dropped.","metadata":{}},{"cell_type":"code","source":"invalid_fqids = list(hovers_count[hovers_count > 100].index)\ninvalid_fqids","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:17.058188Z","iopub.execute_input":"2023-04-04T11:43:17.058596Z","iopub.status.idle":"2023-04-04T11:43:17.067645Z","shell.execute_reply.started":"2023-04-04T11:43:17.058561Z","shell.execute_reply":"2023-04-04T11:43:17.066338Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"invalid_fqid_data = invalid_hovers.groupby(['fqid'], observed=True) \\\n                    ['event_name'].value_counts(normalize=True)\ninvalid_fqid_data = invalid_fqid_data[invalid_fqid_data > 0]\ninvalid_fqid_data = invalid_fqid_data.reset_index() \\\n                    .rename(columns={'level_1':'event_name', 'event_name':'frequency'})\ninvalid_fqid_data.head()","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:17.069779Z","iopub.execute_input":"2023-04-04T11:43:17.070165Z","iopub.status.idle":"2023-04-04T11:43:17.204795Z","shell.execute_reply.started":"2023-04-04T11:43:17.070131Z","shell.execute_reply":"2023-04-04T11:43:17.203391Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.xticks(rotation=90)\nsns.barplot(x=invalid_fqid_data['fqid'], \n            order=invalid_fqid_data['fqid'].unique(),\n            y=invalid_fqid_data['frequency'],\n            hue=invalid_fqid_data['event_name'],\n            hue_order=invalid_fqid_data['event_name'].unique())","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:17.207037Z","iopub.execute_input":"2023-04-04T11:43:17.207442Z","iopub.status.idle":"2023-04-04T11:43:17.745072Z","shell.execute_reply.started":"2023-04-04T11:43:17.207404Z","shell.execute_reply":"2023-04-04T11:43:17.743498Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"toentry and tomap are clear dedicated to map_hover and I think they appear when you want go to the map using the exit in the room as in the example below","metadata":{}},{"cell_type":"markdown","source":"<img src=\"https://i.ibb.co/CVP2sR5/5.png\" width=25%>","metadata":{}},{"cell_type":"markdown","source":"Directory, logbook, wells, colorbook, businesscards, expert, boss, are clear examples of object hover and small percentage of hover maps is just an error.\n\nSame goes for tohallway, tobasement,  but here we treat them as map_hovers (kind of strange as they are placed in the rooms, not on the map)\n\nOther strange examples must be investigated: tostacks, worker, flag_girl","metadata":{}},{"cell_type":"code","source":"invalid_fqids = ['tostacks', 'worker', 'flag_girl']","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:17.747415Z","iopub.execute_input":"2023-04-04T11:43:17.747834Z","iopub.status.idle":"2023-04-04T11:43:17.753179Z","shell.execute_reply.started":"2023-04-04T11:43:17.747797Z","shell.execute_reply":"2023-04-04T11:43:17.752077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"invalid_fqid_data = df[df['fqid'].isin(invalid_fqids)]\ninvalid_fqid_data = invalid_fqid_data.groupby(['fqid', 'event_name']).size()\ninvalid_fqid_data = invalid_fqid_data[invalid_fqid_data > 0]\ninvalid_fqid_data","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:17.754541Z","iopub.execute_input":"2023-04-04T11:43:17.755761Z","iopub.status.idle":"2023-04-04T11:43:18.110198Z","shell.execute_reply.started":"2023-04-04T11:43:17.755720Z","shell.execute_reply":"2023-04-04T11:43:18.109193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at pieces of data, where these three fqids occure with both map and objects hovers.","metadata":{}},{"cell_type":"code","source":"invalid_fqids = ['tostacks', 'worker', 'flag_girl']","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:18.111333Z","iopub.execute_input":"2023-04-04T11:43:18.112133Z","iopub.status.idle":"2023-04-04T11:43:18.117320Z","shell.execute_reply.started":"2023-04-04T11:43:18.112090Z","shell.execute_reply":"2023-04-04T11:43:18.116226Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['next_event'] = df['event_name'].shift(-1)\ndf['previous_event'] = df['event_name'].shift(1)\ndf['previous_fqid'] = df['fqid'].shift(1)","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:43:18.118754Z","iopub.execute_input":"2023-04-04T11:43:18.119372Z","iopub.status.idle":"2023-04-04T11:43:18.272304Z","shell.execute_reply.started":"2023-04-04T11:43:18.119334Z","shell.execute_reply":"2023-04-04T11:43:18.271124Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"invalid_map_hovers = df[(df['event_name'] == 'map_hover') & (df['fqid'].isin(invalid_fqids))]","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:45:15.930832Z","iopub.execute_input":"2023-04-04T11:45:15.931319Z","iopub.status.idle":"2023-04-04T11:45:16.104324Z","shell.execute_reply.started":"2023-04-04T11:45:15.931280Z","shell.execute_reply":"2023-04-04T11:45:16.102703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_counts(column):\n    counts = column.value_counts()\n    counts = counts[counts>0]\n    return counts","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:45:16.163785Z","iopub.execute_input":"2023-04-04T11:45:16.164215Z","iopub.status.idle":"2023-04-04T11:45:16.170113Z","shell.execute_reply.started":"2023-04-04T11:45:16.164180Z","shell.execute_reply":"2023-04-04T11:45:16.168997Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"get_counts(invalid_map_hovers['previous_fqid'])","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:45:25.925775Z","iopub.execute_input":"2023-04-04T11:45:25.926202Z","iopub.status.idle":"2023-04-04T11:45:25.938687Z","shell.execute_reply.started":"2023-04-04T11:45:25.926169Z","shell.execute_reply":"2023-04-04T11:45:25.937590Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"get_counts(invalid_map_hovers['previous_event'])","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:45:26.110629Z","iopub.execute_input":"2023-04-04T11:45:26.111565Z","iopub.status.idle":"2023-04-04T11:45:26.120202Z","shell.execute_reply.started":"2023-04-04T11:45:26.111525Z","shell.execute_reply":"2023-04-04T11:45:26.119332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"get_counts(invalid_map_hovers['next_event'])","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:45:26.425101Z","iopub.execute_input":"2023-04-04T11:45:26.425818Z","iopub.status.idle":"2023-04-04T11:45:26.435696Z","shell.execute_reply.started":"2023-04-04T11:45:26.425778Z","shell.execute_reply":"2023-04-04T11:45:26.434479Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As we can see all this strange map hovers come in the same situations: they go after tomap, toentry fqid (as I suggested they are tranitions to map actions) with after navigation click, and before normal map event. Example is below.","metadata":{}},{"cell_type":"code","source":"df.iloc[62784-5:62784+6]","metadata":{"execution":{"iopub.status.busy":"2023-04-04T11:47:15.783559Z","iopub.execute_input":"2023-04-04T11:47:15.784028Z","iopub.status.idle":"2023-04-04T11:47:15.811576Z","shell.execute_reply.started":"2023-04-04T11:47:15.783968Z","shell.execute_reply":"2023-04-04T11:47:15.810146Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So we can come to the conclusion that these hovers are mistakes of logging system. My view of situation is that when the players clicks on the exit to map (navigation click with toentry, tomap fqid) all hovers that goes during the Jo walking to the exit are assumed to be map hovers, so object_hover on the flag_girl is counted as an map_hover. After that there goes normal map actions. The picture as an example is below.\n\n<img src=\"https://i.ibb.co/LdjCnn2/355.png\" width=50%>","metadata":{}},{"cell_type":"markdown","source":"So I think these 3 are gonna to be converted to the object_hover.\n\nHere is the code that do all the conversions","metadata":{}},{"cell_type":"code","source":"to_replace = {'toentry':'map_hover',\n              'tomap':'map_hover',\n              'tohallway':'map_hover',\n              'tobasement':'map_hover',\n              'boss':'object_hover',\n              'logbook':'object_hover',\n              'wells':'object_hover',\n              'expert':'object_hover',\n              'colorbook':'object_hover',\n              'businesscards':'object_hover',\n              'flag_girl':'object_hover',\n              'tostacks':'object_hover',\n              'worker':'object_hover'}\nto_drop = ['crane_ranger', 'coffee', 'directory']\n\nhover_filter = df['event_name'].isin(['map_hover', 'object_hover'])\ndf = df[~(df['fqid'].isin(to_drop) & hover_filter)].copy()\n\nfor key in to_replace.keys():\n    df.loc[hover_filter & (df['fqid'] == key), 'event_name'] = to_replace[key]","metadata":{"execution":{"iopub.status.busy":"2023-04-04T12:20:28.489405Z","iopub.execute_input":"2023-04-04T12:20:28.489817Z","iopub.status.idle":"2023-04-04T12:20:32.554593Z","shell.execute_reply.started":"2023-04-04T12:20:28.489782Z","shell.execute_reply":"2023-04-04T12:20:32.553090Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df[df['fqid'] == 'flag_girl'].groupby('event_name', observed=True).size()","metadata":{"execution":{"iopub.status.busy":"2023-04-04T12:22:19.878716Z","iopub.execute_input":"2023-04-04T12:22:19.879156Z","iopub.status.idle":"2023-04-04T12:22:19.969762Z","shell.execute_reply.started":"2023-04-04T12:22:19.879120Z","shell.execute_reply":"2023-04-04T12:22:19.967065Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Hooray! 🎉 That's the end.\n\nIn conclusion, I would like to say, that these convertions are optional and depends on your goals (data cleaning for model, proper data analysis). For example, if I needed to make an exploration of players' travelling across the map, I would definitely left only valid fqids, described on GitHub, and ignored tohallway, tobasement, although here I decided to treat them as hover maps, because they contain only information about travelling inside the same map place (capitol).\n\nAnother way to approach this problem is inspect game logger code, but in this notebook I decided to come from the data side.\n\nThank you for reading this article. If you have any questions, suggestions, or ideas, please leave them in the comments section.","metadata":{}}]}