<jupyter_start><jupyter_text>3. 複数の値を条件にしてレコードを抽出したいゲーム内アイテム購入データから、特定の`user_id`を持つレコードを抽出したいとします。<jupyter_code>from itertools import product
from matplotlib import pyplot as plt
import numpy as np
import pandas as pd
from utilities.process_time import PandasProcessTimeMeasure

plt.rcParams['font.size'] = 12
plt.rcParams['figure.figsize'] = (10, 6)
item_data = pd.read_csv('./data/game_user_item.csv')
item_data.head(5)<jupyter_output><empty_output><jupyter_text>このような場合、```pythonis_user1 = (item_data.user_id == 1)is_user2 = (item_data.user_id == 2)is_user3 = (item_data.user_id == 3)item_data.loc[is_user1 & is_user2 & is_user3, :]```とすると処理効率が1番良さそうです。  しかし、データ分析では特定の条件を満たすユーザーを抽出したい場合がほとんどなので、　このように少数が対象になることはなく、また、対象者数は変動するものなので、このコードは採用しづらいです。  そこでDataFrameに実装されている`isin`メソッドを使うと```pythontarget_user_id = [1, 2, 3]item_data.loc[item_data.user_id.isin(target_user_id), :]```となり、target_user_idを外部変数として与えることが簡単になるので、運用しやすいコードが書けます。   しかし、この`isin`メソッドはなぜか動作が遅く、1000万件のデータに対して使えないことが問題になります。  今回はこの場合について検証します。  方法1　isinメソッドを使った場合まずはベンチマークとして`isin`メソッドを使った方法です。  コードの記述が完結になるのが１番の魅力です。<jupyter_code>TEST_TARGET_USER_ID = [2855, 6635]
def method1(data, user_id):
    _data = data.copy()
    is_target_user = _data.user_id.isin(user_id)
    result = _data.loc[is_target_user, :]
    return result

method1(item_data.iloc[:100, :], TEST_TARGET_USER_ID).head()<jupyter_output><empty_output><jupyter_text>方法2　mapで対象アイテムにフラグを立てて抽出する次は`map`メソッドを使って抽出対象にフラグを立てて抽出する方法です。  `isin`メソッドの場合と同じような気がしますが、一応、検証してみます。<jupyter_code>def method2(data, user_id):
    _data = data.copy()
    is_target_user = _data.user_id.map(lambda i: i in user_id)
    result = _data.loc[is_target_user, :]
    return result

method2(item_data.iloc[:100, :], TEST_TARGET_USER_ID).head()<jupyter_output><empty_output><jupyter_text>方法3　mergeを使って抽出対象にフラグを立てて抽出する<jupyter_code>def method3(data, user_id):
    _data = data.copy()
    flag_table = pd.DataFrame(
        data={
            'user_id': user_id,
            'is_target': [1] * len(user_id)
        }
    )
    tmp = _data.merge(
        right=flag_table,
        on=['user_id'],
        how='left'
    )
    result = tmp.loc[tmp.is_target == 1, :].copy()
    return result

method3(item_data.iloc[:100,:], TEST_TARGET_USER_ID).head()<jupyter_output><empty_output><jupyter_text>検証結果 対象者数を10に限定して、方法1〜3を20000件までのデータで比較<jupyter_code>def generate_target_user_id_list(n):
    user_id_list = item_data.user_id.unique()
    return np.random.choice(user_id_list, size=n).tolist()
TARGET_USER_ID = generate_target_user_id_list(10)
process_time_measure = PandasProcessTimeMeasure(
    data=item_data,
    sample_sizes=[100, 500, 1000, 5000, 10000, 20000]
)
process_time_measure.set_method(name='method01', method=lambda x: method1(x, TARGET_USER_ID))
process_time_measure.set_method(name='method02', method=lambda x: method2(x, TARGET_USER_ID))
process_time_measure.set_method(name='method03', method=lambda x: method3(x, TARGET_USER_ID))
process_time_measure.measure_process_time_for_each_sample_sizes()
process_time_measure.plot_process_time()
process_time_measure.process_time<jupyter_output><empty_output><jupyter_text>1000万件までのデータで方法1〜3を比較する<jupyter_code>TARGET_USER_ID = generate_target_user_id_list(10)
process_time_measure = PandasProcessTimeMeasure(
    data=item_data,
    sample_sizes=[10000, 50000, 100000, 1000000, 10000000]
)
process_time_measure.set_method(name='method01', method=lambda x: method1(x, TARGET_USER_ID))
process_time_measure.set_method(name='method02', method=lambda x: method2(x, TARGET_USER_ID))
process_time_measure.set_method(name='method03', method=lambda x: method3(x, TARGET_USER_ID))
process_time_measure.measure_process_time_for_each_sample_sizes()
process_time_measure.plot_process_time()
process_time_measure.process_time<jupyter_output><empty_output><jupyter_text>抽出対象ユーザー数を変化させて方法1〜3を検証<jupyter_code>TARGET_USER_ID = generate_target_user_id_list(100)
process_time_measure = PandasProcessTimeMeasure(
    data=item_data,
    sample_sizes=[10000, 50000, 100000, 1000000, 10000000]
)
process_time_measure.set_method(name='method01', method=lambda x: method1(x, TARGET_USER_ID))
process_time_measure.set_method(name='method02', method=lambda x: method2(x, TARGET_USER_ID))
process_time_measure.set_method(name='method03', method=lambda x: method3(x, TARGET_USER_ID))
process_time_measure.measure_process_time_for_each_sample_sizes()
process_time_measure.plot_process_time()
process_time_100 = process_time_measure.process_time.copy()
user_num_index = pd.Series([100] * process_time_100.shape[0])
user_num_index.name = 'user_num'
process_time_100.set_index(user_num_index, append=True, inplace=True)
process_time_100<jupyter_output><empty_output><jupyter_text>`method2`は遅いので除外し、`method1`と`method3`のみを比較する。<jupyter_code>TARGET_USER_ID = generate_target_user_id_list(500)
process_time_measure = PandasProcessTimeMeasure(
    data=item_data,
    sample_sizes=[10000, 50000, 100000, 1000000, 10000000]
)
process_time_measure.set_method(name='method01', method=lambda x: method1(x, TARGET_USER_ID))
process_time_measure.set_method(name='method03', method=lambda x: method3(x, TARGET_USER_ID))
process_time_measure.measure_process_time_for_each_sample_sizes()
process_time_measure.plot_process_time()
TARGET_USER_ID = generate_target_user_id_list(1000)
process_time_measure = PandasProcessTimeMeasure(
    data=item_data,
    sample_sizes=[10000, 50000, 100000, 1000000, 10000000]
)
process_time_measure.set_method(name='method01', method=lambda x: method1(x, TARGET_USER_ID))
process_time_measure.set_method(name='method03', method=lambda x: method3(x, TARGET_USER_ID))
process_time_measure.measure_process_time_for_each_sample_sizes()
process_time_measure.plot_process_time()
TARGET_USER_ID = generate_target_user_id_list(5000)
process_time_measure = PandasProcessTimeMeasure(
    data=item_data,
    sample_sizes=[10000, 50000, 100000, 1000000, 10000000]
)
process_time_measure.set_method(name='method01', method=lambda x: method1(x, TARGET_USER_ID))
process_time_measure.set_method(name='method03', method=lambda x: method3(x, TARGET_USER_ID))
process_time_measure.measure_process_time_for_each_sample_sizes()
process_time_measure.plot_process_time()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10927471116816431, 'fraction_numerical': 0.053112965340179716, 'mean_word_length': 9.353820598006644, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 5, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '80943', 'license': 'MIT', 'n_tokens_mistral': 2909, 'n_tokens_neox': 2625, 'n_words': 276, 'path': 'pandas_tips_03.ipynb', 'repo_name': 'Katsuya-Ishiyama/pandas_tips', 'size': '6232'}