使用Pandas按组计算指定日期前180天内的最大价格

需求：使用Pandas按num字段分组后，针对每条数据的date字段，计算该日期往前180天内同组的最大price值，生成max列。现有实现运行效率极低，寻求更优方案。

示例DataFrame #

df = pd.DataFrame({'num': ["50110-374","50110-374","50110-374","50110-374","50110-374","50110-374","50110-374","50110-374","50110-374","50110-374","50110-3421","50110-3421","50110-3421","50110-3421","50110-3421","50110-3421","50110-3421"],
                   'date': ["2017-11-24","2018-02-08","2018-03-08","2018-03-17","2018-04-11","2018-04-16","2018-05-05","2018-06-04","2018-06-16","2018-07-13","2019-01-28","2019-02-10","2019-03-16","2019-03-16","2019-06-07","2019-06-30", "2022-06-30"],
                   'type':["39","39","39","39","39","39","39","39","39","39","73","73","73","73","73","73","73"],
                   'price':[17000,12500,14000,14000,18000,13000,14250,15000,12900,15000,35500,34500,35000,37000,33300,34800, 32000]})

数据展示 #

num        date       type  price
0   50110-374   2017-11-24  39  17000
1   50110-374   2018-02-08  39  12500
2   50110-374   2018-03-08  39  14000
3   50110-374   2018-03-17  39  14000
4   50110-374   2018-04-11  39  18000
5   50110-374   2018-04-16  39  13000
6   50110-374   2018-05-05  39  14250
7   50110-374   2018-06-04  39  15000
8   50110-374   2018-06-16  39  12900
9   50110-374   2018-07-13  39  15000
10  50110-3421  2019-01-28  73  35500
11  50110-3421  2019-02-10  73  34500
12  50110-3421  2019-03-16  73  35000
13  50110-3421  2019-03-16  73  37000
14  50110-3421  2019-06-07  73  33300
15  50110-3421  2019-06-30  73  34800
16  50110-3421  2022-06-30  73  32000

期望结果 #

num         date        type  price      max
0   50110-374   2017-11-24  39  17000      NaN
1   50110-374   2018-02-08  39  12500  17000.0
2   50110-374   2018-03-08  39  14000  17000.0
3   50110-374   2018-03-17  39  14000  17000.0
4   50110-374   2018-04-11  39  18000  18000.0
5   50110-374   2018-04-16  39  13000  18000.0
6   50110-374   2018-05-05  39  14250  18000.0
7   50110-374   2018-06-04  39  15000  18000.0
8   50110-374   2018-06-16  39  12900  18000.0
9   50110-374   2018-07-13  39  15000  18000.0
10  50110-3421  2019-01-28  73  35500      NaN
11  50110-3421  2019-02-10  73  34500  35500.0
12  50110-3421  2019-03-16  73  35000  35500.0
13  50110-3421  2019-03-16  73  37000  37000.0
14  50110-3421  2019-06-07  73  33300  37000.0
15  50110-3421  2019-06-30  73  34800  37000.0
16  50110-3421  2022-06-30  73  32000  32000.0

现有低效代码 #

def maxDeal(date):
    testDate = date
    dateIndex = totalMonthList.index(testDate)
    testRange = totalMonthList[dateIndex-720:dateIndex+1]
    
    tmpCdDf = priceApi[priceApi['date'] == testDate][['num','type','date']]
    tmpCdLst = list(tmpCdDf['num'].drop_duplicates())
    maxDf = df[(df['num'].isin(tmpCdLst)) & (df['date'].isin(testRange))].groupby(['date','type'])['price'].max().reset_index()
    tmpCdDf = pd.merge(tmpCdDf,maxDf, how='left', on=['num','type'] )
    maxValue = list(tmpCdDf['price'])
    df.loc[df['date'] == date, 'max'] = maxValue

高效解决方案 #

核心思路 #

利用Pandas的时间窗口滚动计算（rolling）功能，直接按分组和时间维度批量计算，避免循环和多次数据切片，大幅提升效率。

实现代码 #

# 1. 将date列转换为datetime类型，确保时间运算有效
df['date'] = pd.to_datetime(df['date'])

# 2. 按num分组，每组内按date排序，保证时间顺序正确
df = df.sort_values(['num', 'date']).reset_index(drop=True)

# 3. 分组计算180天时间窗口内的price最大值
# window='180D'表示窗口为180天，on='date'指定时间基准列
# closed='both'表示窗口包含起始和结束日期（即当前日期也纳入计算）
df['max'] = df.groupby('num').apply(
    lambda group: group['price'].rolling(window='180D', on='date', closed='both').max()
).reset_index(level=0, drop=True)

# 4. 匹配期望结果：将每组第一个数据的max设为NaN（无历史数据）
df['max'] = df.groupby('num')['max'].transform(
    lambda x: x.where(x.index != x.index[0], pd.NA)
)

# 可选：恢复原始顺序（如果需要）
# df = df.sort_index()

代码说明 #

• 时间窗口滚动计算是Pandas的矢量化操作，比循环遍历快几个数量级，尤其适合大数据量场景。
• closed='both'确保当前日期的price被纳入窗口计算，匹配期望结果中第4、13、16行的取值。
• 最后一步将每组第一个值设为NaN，完全对齐期望结果的格式。

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