{"cells":[{"metadata":{},"cell_type":"markdown","source":"# FAST PANDAS LEFT JOIN (357x faster)\n\nHi, I think many people are irritated overhead to join each test dataframe with user dataframes (or content dataframes). For the left join and the case when the right table index is unique, we can join them much faster than pd.merge.\n\n* UPDATE: added the method @alijs1 mentioned (`right_index=True`), 10x faster.\n* UPDATE: added the method @doctorkael mentioned (`right_index=True`and present users), 45x faster.\n\nDiscussion: https://www.kaggle.com/c/riiid-test-answer-prediction/discussion/197023"},{"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)\nimport riiideducation","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"users = pd.read_csv('../input/riiid-test-answer-prediction/train.csv', usecols=['user_id'])['user_id'].unique()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Maybe, you have a user dataframe like this."},{"metadata":{"trusted":true},"cell_type":"code","source":"n_cols = 100\ndf_user = pd.DataFrame(np.random.random((users.shape[0], n_cols)), index=users, columns=[f'feat{i}' for i in range(n_cols)])\ndf_user.index.name = 'user_id'\nprint(df_user.shape)\ndf_user","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Comparison"},{"metadata":{"trusted":true},"cell_type":"code","source":"# prepare data\nenv = riiideducation.make_env()\niter_test = env.iter_test()\n\nlist_df = []\nfor itr, (df_test, sample_prediction_df) in enumerate(iter_test):\n    df_test.loc[:, 'answered_correctly'] = 0.5\n    list_df.append(df_test)\n    env.predict(df_test.loc[df_test['content_type_id'] == 0, ['row_id', 'answered_correctly']])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# pd.merge()\n\nIt should takes around 1.75 sec."},{"metadata":{"trusted":true},"cell_type":"code","source":"%%timeit\nfor df_test in list_df:\n    df_test.merge(df_user, how='left', on='user_id')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# pd.merge() with right_index=True\n\nMentioned by @alijs1 in https://www.kaggle.com/c/riiid-test-answer-prediction/discussion/197023.\n\nIt should takes around 139 ms. This is 13 times faster!"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%timeit\nfor df_test in list_df:\n    df_test.merge(df_user, how='left', left_on='user_id', right_index=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# pd.merge() with right_index=True and the present users filtering\n\nMentioned by @doctorkael in https://www.kaggle.com/c/riiid-test-answer-prediction/discussion/197023.\n\nIt should takes around 38.3 ms. This is 45 times faster!"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%timeit\nfor df_test in list_df:\n    df_test.merge(df_user.loc[df_user.index.isin(df_test['user_id'])],\n                  how='left', left_on='user_id', right_index=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fast left join\nIt should takes around 4.89 ms sec, <span style=\"color: red; \">**357 TIMES FASTER!!!!!!**</span>"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%timeit\nfor df_test in list_df:\n    pd.concat([df_test.reset_index(drop=True), df_user.reindex(df_test['user_id'].values).reset_index(drop=True)], axis=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Of course, they are equivalent.\n\nBut, `right_index=True` preserves the original left index. `reset_index(drop=True)` is required to be equal."},{"metadata":{"trusted":true},"cell_type":"code","source":"for df_test in list_df:\n    df_merge = df_test.merge(df_user, how='left', on='user_id')\n    df_merge_right_index = df_test.merge(df_user, how='left',\n                                         left_on='user_id', right_index=True).reset_index(drop=True)\n    df_merge_right_index_user = df_test.merge(df_user.loc[df_user.index.isin(df_test['user_id'])],\n                                              how='left', left_on='user_id', right_index=True).reset_index(drop=True)\n    df_fast_merge = pd.concat([df_test.reset_index(drop=True),\n                               df_user.reindex(df_test['user_id'].values).reset_index(drop=True)], axis=1)\n    print(df_merge.equals(df_merge_right_index), \n          df_merge.equals(df_merge_right_index_user), \n          df_merge.equals(df_fast_merge), \n          )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Enjoy your kaggle life!!!"}],"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}