为何Pandas滚动函数在云平台运行时出现非线性性能下降？

问题描述 #

将股票市场分析库迁移至CodeAnywhere、Google Colab等云平台后，核心pandas滚动百分位计算出现非线性性能骤降：本地戴尔XPS笔记本上耗时随数据量线性增长，但云平台在数据量超过810后，每3倍数据量耗时激增10倍，复杂度仿佛变为O(N²)。

测试逻辑：基于日内数据，计算每个时间点往前20个工作日内所有high价格的第20百分位，使用VariableOffsetWindowIndexer实现可变窗口。

测试代码 #

from data_access import backfill_historical_intraday_data, form_full_data
from time import time
import pandas as pd
from pandas.api.indexers import VariableOffsetWindowIndexer

my_ticker = 'DBL'

my_intraday_data = form_full_data(handle=my_ticker)

my_data = my_intraday_data[my_intraday_data['date'] > '2020-01-01']
my_data.set_index('date', inplace=True)

for k in range(8):
    my_length = 10 * (3 ** k)
    start = time()
    my_trial_data = my_data.copy().iloc[-1 * my_length:]
    offset = pd.offsets.BDay(20)
    indexer = VariableOffsetWindowIndexer(index=my_trial_data.index, offset=offset)
    my_low_highs = my_trial_data['high'].rolling(indexer, closed='left').quantile(0.2)
    end = time()
    print(f'Process with df length of {my_length} took {end - start} seconds.')

数据集大小：整个DataFrame仅占用4.4M内存，无内存压力

测试结果对比 #

戴尔XPS（Windows系统） #

Process with df length of 10 took 0.004008054733276367 seconds.
Process with df length of 30 took 0.003987312316894531 seconds.
Process with df length of 90 took 0.006006479263305664 seconds.
Process with df length of 270 took 0.00899958610534668 seconds.
Process with df length of 810 took 0.02326822280883789 seconds.
Process with df length of 2430 took 0.07396459579467773 seconds.
Process with df length of 7290 took 0.23105525970458984 seconds.
Process with df length of 21870 took 0.7741758823394775 seconds.

耗时随数据量线性增长，符合预期

Google Colab（Linux系统，已购买100计算额度） #

Process with df length of 10 took 0.003057718276977539 seconds.
Process with df length of 30 took 0.0040819644927978516 seconds.
Process with df length of 90 took 0.004738807678222656 seconds.
Process with df length of 270 took 0.01237034797668457 seconds.
Process with df length of 810 took 0.02781844139099121 seconds.
Process with df length of 2430 took 0.22655129432678223 seconds.
Process with df length of 7290 took 6.735988140106201 seconds.
Process with df length of 21870 took 51.67249917984009 seconds.

数据量≥2430后耗时激增，呈现非线性增长

排查进展 #

编写了模拟数据集的可复现代码（如下），但在Colab运行时未复现性能问题，推测问题出在实际生产DataFrame与测试DataFrame的差异上。

import pandas as pd
from numpy.random import default_rng
from pandas.api.indexers import VariableOffsetWindowIndexer
from time import time
my_rng = default_rng()

print(pd.__version__)

num_minutes = my_rng.random(100000) * 5 * 365.24 * 24 * 60
tick_value = my_rng.random(100000)

raw_df = pd.DataFrame(
    {
    'num_minutes': num_minutes,
    'value': tick_value
    }
)

raw_df['num_minutes'] = raw_df['num_minutes'].astype(int)
raw_df['date_time'] = pd.to_datetime('2020-01-01') + pd.to_timedelta(raw_df['num_minutes'], unit='m')
raw_df['date'] = pd.to_datetime(raw_df['date_time'].dt.date)
raw_df.sort_values('date_time', inplace=True)
raw_df.set_index('date', inplace=True)
for k in range(9):
    my_length = 10 * (3 ** k)
    start = time()
    my_trial_data = raw_df.copy().iloc[-1 * my_length:]
    offset = pd.offsets.BDay(20)
    indexer = VariableOffsetWindowIndexer(index=my_trial_data.index, offset=offset)
    my_rolling_value = my_trial_data['value'].rolling(indexer, closed='left').quantile(0.2)
    end = time()
    print(f'Process with df length of {my_length} took {end - start} seconds.')

注：本地与Colab使用相同版本的pandas

问题根源分析与解决方案 #

1. 核心原因：索引重复度与平台优化差异 #

真实股票日内数据中，单个日期对应大量行（每分钟/每笔交易一条数据），高重复度的date索引触发了pandas底层的低效逻辑：

• Windows环境下的pandas对高重复索引的可变窗口计算做了隐性优化；
• Linux环境（云平台）的pandas在处理此类场景时，每一行都需要重新扫描前20个工作日的所有数据，而非复用已有计算结果，导致复杂度从O(N)变为O(N*W)（W为窗口内平均行数），表现为非线性耗时增长。

2. 验证方向 #

• 检查真实DataFrame的索引重复情况：执行my_data.index.value_counts()，对比模拟数据的索引重复度；
• 确认数据排序：检查日内数据的时间顺序是否严格递增，乱序会额外增加窗口计算的排序开销。

3. 优化方案 #

方案1：预聚合日期维度数据

先按日期聚合每日high数据，再计算滚动百分位，最后映射回日内数据，大幅减少重复计算：

# 按日期聚合所有high值
daily_highs = my_data['high'].groupby('date').agg(list)
# 计算20个工作日窗口的第20百分位
daily_low_highs = daily_highs.rolling(window=20, closed='left').apply(
    lambda x: pd.Series([val for sublist in x for val in sublist]).quantile(0.2)
)
# 将结果合并回原数据
my_data = my_data.join(daily_low_highs.rename('low_highs'), on='date')

方案2：使用DatetimeIndex替代日期索引

将索引改为包含时间的DatetimeIndex，利用其有序性触发pandas高效优化：

# 假设原数据有date_time列
my_data.set_index('date_time', inplace=True)
# 重新计算滚动百分位
offset = pd.offsets.BDay(20)
indexer = VariableOffsetWindowIndexer(index=my_data.index, offset=offset)
my_low_highs = my_data['high'].rolling(indexer, closed='left').quantile(0.2)

方案3：用Numba加速自定义滚动函数

绕过pandas低效逻辑，用Numba实现自定义滚动计算：

from numba import jit
import numpy as np

@jit(nopython=True)
def rolling_quantile(arr, window_indices, q=0.2):
    result = np.zeros(len(arr))
    for i in range(len(arr)):
        start_idx = window_indices[i]
        window = arr[start_idx:i]
        result[i] = np.percentile(window, q*100) if len(window) > 0 else np.nan
    return result

# 获取每个窗口的起始索引
indexer = VariableOffsetWindowIndexer(index=my_data.index, offset=pd.offsets.BDay(20))
window_starts = indexer.get_window_bounds(num_values=len(my_data), min_periods=1)[0]
# 执行加速计算
my_low_highs = pd.Series(rolling_quantile(my_data['high'].values, window_starts), index=my_data.index)

内容的提问来源于stack exchange，提问作者David R