如何用Python生成收益率指数前N大回撤的表格？

我有包含股市收益率、日期、初始值为100的收益率指数（index_100）的时间序列数据，想要生成展示该序列中前N大回撤（drawdown）的表格，表格需包含每个回撤的日期范围、持续时长。目前已有的实现脚本代码混乱，且计算准确性不稳定，原代码如下：

df['drawdown'] = df['index_100'] / df['index_100'].expanding().max() - 1

throw_away_df = df[df['drawdown'] == 0].copy()
throw_away_df = throw_away_df[['Date']]
throw_away_df['duration'] = throw_away_df['Date'].diff().dt.days
throw_away_df['start_date'] = throw_away_df['Date'].shift(1)
throw_away_df = throw_away_df[throw_away_df['duration'] > 35]
throw_away_df['rank'] = throw_away_df['duration'].rank(ascending=False)
throw_away_df['drawdown'] = 0
throw_away_df = throw_away_df.reset_index(drop=True)

for i in throw_away_df.index:
    x = max(abs(df['drawdown'][(df['Date'] > throw_away_df['start_date'].loc[i]) & (df['Date'] < throw_away_df['Date'].loc[i])]))
    throw_away_df['drawdown'].loc[i] = -x


throw_away_df = throw_away_df[throw_away_df['rank'] <= N]
throw_away_df = throw_away_df.sort_values(by=['rank'])
throw_away_df = throw_away_df[['start_date', 'Date', 'duration', 'drawdown']]
throw_away_df.columns = ['start_date', 'end_date', 'duration', 'drawdown']

改进后的稳定实现 #

代码实现 #

import pandas as pd

def get_top_n_drawdowns(df, N):
    # 计算回撤及历史峰值
    df['peak'] = df['index_100'].expanding().max()
    df['drawdown'] = (df['index_100'] / df['peak']) - 1

    # 标记回撤状态及周期转折点
    df['in_drawdown'] = df['index_100'] < df['peak']
    df['drawdown_start'] = (df['in_drawdown'] & ~df['in_drawdown'].shift(1)).fillna(False)
    df['drawdown_end'] = (~df['in_drawdown'] & df['in_drawdown'].shift(1)).fillna(False)

    # 提取所有回撤的起止日期（含未结束的当前回撤）
    start_dates = df[df['drawdown_start']]['Date'].tolist()
    end_dates = df[df['drawdown_end']]['Date'].tolist()
    if df['in_drawdown'].iloc[-1]:
        end_dates.append(df['Date'].iloc[-1])

    # 计算每个回撤周期的核心指标
    drawdown_records = []
    for start, end in zip(start_dates, end_dates):
        period_data = df[(df['Date'] >= start) & (df['Date'] <= end)]
        max_drawdown = period_data['drawdown'].min()
        duration_days = (end - start).days
        drawdown_records.append({
            'start_date': start,
            'end_date': end,
            'duration': duration_days,
            'max_drawdown': max_drawdown
        })

    # 生成前N大回撤表格（按回撤幅度排序）
    drawdown_df = pd.DataFrame(drawdown_records)
    drawdown_df = drawdown_df.sort_values(by='max_drawdown', ascending=True).head(N)
    drawdown_df = drawdown_df.reset_index(drop=True)
    
    return drawdown_df

# 使用示例
# top_drawdowns = get_top_n_drawdowns(your_dataframe, 5)  # 替换为你的DataFrame和N值

关键改进点 #

• 完整捕捉回撤周期：通过状态标记识别所有回撤的起始和结束，包括尚未恢复到前期高点的当前回撤，避免遗漏
• 按回撤幅度排序：原代码错误地按持续时长排名，改进后按最大回撤幅度排序，符合“前N大回撤”的核心需求
• 提升计算效率：优化数据查询逻辑，减少循环内的重复数据切片操作，运行更高效
• 鲁棒性处理：自动处理未结束的回撤场景，无需额外手动干预

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