基于多条件为Pandas DataFrame列赋值的最快方法探究

已知存在类似问题，但未发现针对大数据集的timeit测试。我有一个约160万行的DataFrame（DF），希望找到最快的方法，根据索引的星期几和小时数为['stag']列赋值0或1。

我是否遗漏了更快的实现方式？欢迎提出建议。

测试代码 #

import timeit
import statistics

...
...

df_backtest['weekday'] = df_backtest.index.weekday

def approach_1():
    df_backtest["stag"]=1
    df_backtest.loc[(df_backtest.index.hour>=2) & (df_backtest.index.hour<=10),"stag" ]=0
    df_backtest.loc[df_backtest.index.strftime('%w')==6,"stag" ]=0
    df_backtest.loc[df_backtest.index.strftime('%w')==0,"stag" ]=0


def approach_2():
    df_backtest['stag'] = 1
    df_backtest.loc[df_backtest.index.hour.isin(range(2, 11)), 'stag'] = 0
    df_backtest.loc[df_backtest.index.strftime('%w').isin(['6', '0']), 'stag'] = 0
    

def approach_3():
    df_backtest['stag'] = 1
    df_backtest.loc[df_backtest.index.hour.isin(range(2, 11)), 'stag'] = 0
    df_backtest.loc[df_backtest['weekday'].isin(['6', '0']), 'stag'] = 0


def approach_4():
    df_backtest['stag'] = 1
    df_backtest['stag'] = df_backtest['stag'].where((df_backtest.index.hour < 2) | (df_backtest.index.hour > 10))
    df_backtest['stag'] = df_backtest['stag'].where(~df_backtest['weekday'].isin(['6', '0']))

num_repeats = 10
num_loops = 5

print(f'Approach 1: {statistics.mean(timeit.repeat(approach_1, number=num_loops, repeat=num_repeats)) / num_loops} seconds per loop')
print(f'Approach 2: {statistics.mean(timeit.repeat(approach_2, number=num_loops, repeat=num_repeats)) / num_loops} seconds per loop')
print(f'Approach 3: {statistics.mean(timeit.repeat(approach_3, number=num_loops, repeat=num_repeats)) / num_loops} seconds per loop')
print(f'Approach 4: {statistics.mean(timeit.repeat(approach_4, number=num_loops, repeat=num_repeats)) / num_loops} seconds per loop')
print('Shape of DF:',df_backtest.shape)

测试输出 #

Approach 1: 4.617 seconds per loop
Approach 2: 2.256 seconds per loop
Approach 3: 0.087 seconds per loop
Approach 4: 0.106 seconds per loop
Shape of DF: (1605144, 7)

测试结果显示approach_3是最快的，推测原因是它没有使用where的开销，也无需处理字符串。欢迎提供其他实现方法。谢谢。

内容的提问来源于stack exchange，提问作者Lorenzo Bassetti