基于滑动窗口的DataFrame滚动差值区间计数优化及扩展问询

基础需求实现 #

前置准备 #

先模拟示例数据：

import pandas as pd
import numpy as np

# 生成含500条数据的dfm1
np.random.seed(42)
dfm1 = pd.DataFrame({
    'Start': np.random.randint(0, 100, 500),
    'End': np.random.randint(50, 150, 500)
})

# 预设区间列的空dfm2（示例区间：0-10、10-20…90-100）
bin_edges = [0,10,20,30,40,50,60,70,80,90,100]
dfm2 = pd.DataFrame(columns=[str(edge) for edge in bin_edges[1:]])

用rolling类方法替代循环 #

核心是通过shift获取窗口后的值计算差值，再用pd.cut分配区间完成计数，完全避免循环：

window_size = 2  # 可调整的滑动窗口大小
target_col = 'Start'  # 指定计算的目标列

# 计算滚动差值：index+window位置的值 - 当前index的值
diff_series = dfm1[target_col].shift(-window_size) - dfm1[target_col]
# 移除最后window_size行（无对应后续值）
diff_series = diff_series.dropna()

# 将差值映射到预设区间
bins = pd.cut(diff_series, bins=bin_edges, labels=dfm2.columns)
# 统计各区间计数，对齐dfm2列并补0
counts = bins.value_counts().reindex(dfm2.columns, fill_value=0)

# 赋值到dfm2
dfm2.loc[0] = counts.values

扩展场景处理 #

场景1：先计算End-Start再做滚动计数 #

先生成每行的End-Start差值列，再重复上述流程：

# 新增同索引差值列
dfm1['Duration'] = dfm1['End'] - dfm1['Start']

# 滚动差值计算+区间计数
window_size = 2
diff_series = dfm1['Duration'].shift(-window_size) - dfm1['Duration']
diff_series = diff_series.dropna()

bins = pd.cut(diff_series, bins=bin_edges, labels=dfm2.columns)
counts = bins.value_counts().reindex(dfm2.columns, fill_value=0)
dfm2.loc[1] = counts.values

场景2：动态生成10步长区间列 #

根据差值的实际范围自动生成区间，无需预设dfm2列：

window_size = 2
target_col = 'Start'

# 计算滚动差值
diff_series = dfm1[target_col].shift(-window_size) - dfm1[target_col]
diff_series = diff_series.dropna()

# 动态生成10步长的区间边界
min_diff = np.floor(diff_series.min() / 10) * 10
max_diff = np.ceil(diff_series.max() / 10) * 10
dynamic_bins = np.arange(min_diff, max_diff + 10, 10)

# 创建带动态区间列的dfm2
dfm2_dynamic = pd.DataFrame(columns=[f"{int(b)}-{int(b+10)}" for b in dynamic_bins[:-1]])

# 分配区间并统计计数
bins = pd.cut(diff_series, bins=dynamic_bins, labels=dfm2_dynamic.columns)
counts = bins.value_counts().reindex(dfm2_dynamic.columns, fill_value=0)
dfm2_dynamic.loc[0] = counts.values

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