优化Pandas中基于条件的历史索引查找：使用bisect模块实现高效性能

            lower  upper
DATE                    
2020-01-01      7      2
2020-01-02      1      3
2020-01-03      6      4
2020-01-04      1      5
2020-01-05      1      6
2020-01-06      1      7
2020-01-07      1      8
2020-01-08     11      9
2020-01-09      1     10
2020-01-10      1     11

import pandas as pd
import numpy as np

# 示例DataFrame生成函数
def get_sample_df(rows=10):
    data = {'lower': np.random.default_rng(seed=1).uniform(1,100,rows),
            'upper': np.random.default_rng(seed=2).uniform(1,100,rows)}
    df = pd.DataFrame(data=data).astype(int)
    df['DATE'] = pd.date_range('2020-01-01', periods=rows, freq="min")
    df.set_index('DATE', inplace=True)
    return df

def get_baseline():
    df = get_sample_df()

    def get_most_recent_index(row):
        # 筛选当前行之前的所有行
        previous_indices = df.loc[:row.name - pd.Timedelta(minutes=1)]  
        # 在之前行中找到满足条件的行，并返回最近的索引
        recent_index = previous_indices[previous_indices['lower'] >= row['upper']].index.max()
        return recent_index

    df['prev'] = df.apply(get_most_recent_index, axis=1) 
    return df

# 运行示例
df_baseline = get_baseline()
print(df_baseline)

from bisect import bisect_left

def get_bisect():
    df = get_sample_df() # 使用相同的示例数据生成函数

    def get_prev_bs(lower_series, upper_series, date_index):
        # 存储所有出现过的唯一lower值，并保持排序
        uniq_lower = sorted(list(set(lower_series)))
        # 存储每个lower值最近一次出现的日期
        last_seen = {}

        results = []
        for l, u, d in zip(lower_series, upper_series, date_index):
            # 使用二分查找找到在uniq_lower中，第一个大于或等于u的元素的索引
            # 这意味着uniq_lower[idx:]包含了所有 >= u 的lower值
            idx = bisect_left(uniq_lower, u)

            max_date = None
            # 遍历所有符合条件的lower值
            for lv in uniq_lower[idx:]:
                if lv in last_seen:
                    # 如果该lower值之前出现过
                    if max_date is None:
                        max_date = last_seen[lv]
                    elif last_seen[lv] > max_date:
                        # 更新为更近的日期
                        max_date = last_seen[lv]
            results.append(max_date)
            # 更新当前lower值最近一次出现的日期
            last_seen[l] = d
        return results

    df["prev"] = list(get_prev_bs(df["lower"], df["upper"], df.index))
    return df

# 运行示例
df_bisect = get_bisect()
print(df_bisect)

import pandas as pd
from bisect import bisect_left

data = {'lower': [7, 1, 6, 1, 1, 1, 1, 11, 1, 1],
        'upper': [2, 3, 4, 5, 6, 7, 8, 9, 10, 11]}
df = pd.DataFrame(data=data)
df['DATE'] = pd.date_range('2020-01-01', periods=len(data['lower']))
df.set_index('DATE', inplace=True)

def get_prev_bs_verify(lower_series, upper_series, date_index):
    uniq_lower = sorted(list(set(lower_series)))
    last_seen = {}
    results = []
    for l, u, d in zip(lower_series, upper_series, date_index):
        idx = bisect_left(uniq_lower, u)
        max_date = None
        for lv in uniq_lower[idx:]:
            if lv in last_seen:
                if max_date is None:
                    max_date = last_seen[lv]
                elif last_seen[lv] > max_date:
                    max_date = last_seen[lv]
        results.append(max_date)
        last_seen[l] = d
    return results

df["prev_new"] = list(get_prev_bs_verify(df["lower"], df["upper"], df.index))
print(df)

            lower  upper   prev_new
DATE                             
2020-01-01      7      2        NaT
2020-01-02      1      3 2020-01-01
2020-01-03      6      4 2020-01-01
2020-01-04      1      5 2020-01-03
2020-01-05      1      6 2020-01-03
2020-01-06      1      7 2020-01-01
2020-01-07      1      8        NaT
2020-01-08     11      9        NaT
2020-01-09      1     10 2020-01-08
2020-01-10      1     11 2020-01-08