{"cells":[{"metadata":{},"cell_type":"markdown","source":"**When the data is huge; it takes so much time to retrieve all the records for a user. Libraries such as datatable makes it faster or if you know all the index values (which is difficult).**\n\n**But I will show another approach so that we can update a user's record.**"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"%%time\n\ndtypes = {\n    \"row_id\":\"int64\",\n    \"timestamp\":\"int64\",\n    \"user_id\":\"int32\",\n    \"content_id\":\"int16\",\n    \"content_type_id\":\"int16\",\n    \"task_container_id\":\"int16\",\n    \"user_answer\":\"int8\",\n    \"answered_correctly\":\"int8\",\n    \"prior_question_elapsed_time\":\"float32\",\n    \"prior_question_had_explanation\":\"str\"\n}\n\ndata = pd.read_csv(\"/kaggle/input/riiid-test-answer-prediction/train.csv\", dtype=dtypes)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's check in how much time it consumes to retrieve a user's df with .loc"},{"metadata":{"trusted":true},"cell_type":"code","source":"sample_user = int(data.sample(1, random_state=42)[\"user_id\"])\n\n%time data.loc[data.user_id == sample_user]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"But, using .iloc absolutely so much faster. In order to establish my implement I will firstly use .iloc"},{"metadata":{"trusted":true},"cell_type":"code","source":"%time user_indexes = data.groupby(\"user_id\")[\"row_id\"].agg([\"min\", \"max\"]).reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from tqdm import tqdm\n\ntotal_dict = {}\nfor user in tqdm(range(len(user_indexes))):\n    user_df = data.iloc[user_indexes.iloc[user, 1]:user_indexes.iloc[user, 2], :]\n    total_dict[f'data_{user_indexes.iloc[user, 0]}'] = user_df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now, we see that using iloc is really fast. But it might get confusing to update index values. Instead, as we see the code above, I created a dictionary that contains of every user's dataframe with keys of user id. Now, retrieving and updating should be easier."},{"metadata":{"trusted":true},"cell_type":"code","source":"%timeit data.iloc[5834946:5835038,:]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%timeit total_dict[f'data_{sample_user}']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"So then, retrieving with dictionary keys may be even faster than .iloc and it is easy to update (you may want to append new rows etc.)"}],"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":4,"nbformat_minor":4}