基于QQQ历史数据构建累计收益高低分档可视化指标图求助

Hey there! Great job getting the cumulative returns calculated already—let's walk through how to categorize those values and build the visualization you're targeting, step by step.

Step 1: Categorize Cumulative Returns into Low/Normal/High Tiers #

First, we'll use pandas to bucket your cumulative_ret values into three tiers. You have two flexible options here:

Option A: Equal-sized tiers (using quantiles) #

This splits your returns into three groups with roughly the same number of observations, perfect for balanced performance tiers:

import numpy as np  # Don't forget to import numpy since your code relies on it

# Bucket returns into 3 equal quantiles, labeled Low/Normal/High
df['ret_category'] = pd.qcut(
    df['cumulative_ret'].dropna(),  # Drop NaNs from the initial rolling window rows
    q=3,
    labels=['Low', 'Normal', 'High']
)

# Reattach categorized values to keep alignment with the original DataFrame
df['ret_category'] = df['ret_category'].astype('category')

Option B: Custom threshold tiers #

If you want to use fixed benchmarks (e.g., industry standards or your own rules), use pd.cut instead:

# Define your custom bins (adjust these values to match your needs)
bins = [-np.inf, -0.03, 0.03, np.inf]  # Example: < -3% = Low, -3% to 3% = Normal, >3% = High
df['ret_category'] = pd.cut(
    df['cumulative_ret'].dropna(),
    bins=bins,
    labels=['Low', 'Normal', 'High']
)
df['ret_category'] = df['ret_category'].astype('category')

Step 2: Build the Visualization with Plotly #

Now we'll create a chart matching your target—with colored background regions for each tier and a clear line showing cumulative returns. First, ensure your date column is properly formatted:

# Convert your date column to datetime (replace 'date' with your actual column name from QQQ2.csv)
df['date'] = pd.to_datetime(df['date'])

Next, construct the figure with colored backgrounds and the return line:

# Define color mapping to match your target chart's style
color_map = {'Low': '#ff4c4c', 'Normal': '#cccccc', 'High': '#4ca64c'}

fig = go.Figure()

# Add the core cumulative return line
fig.add_trace(go.Scatter(
    x=df['date'],
    y=df['cumulative_ret'],
    mode='lines',
    line=dict(color='black', width=1.5),
    name='60-Day Cumulative Return'
))

# Add colored background regions for each consecutive category group
df['category_group'] = (df['ret_category'] != df['ret_category'].shift(1)).cumsum()
grouped_categories = df.groupby('category_group')

for _, group in grouped_categories:
    category = group['ret_category'].iloc[0]
    if pd.isna(category):
        continue  # Skip NaN groups from the early rolling window rows
    # Draw a rectangle covering the entire duration of the category
    fig.add_shape(
        type='rect',
        x0=group['date'].iloc[0],
        x1=group['date'].iloc[-1],
        y0=df['cumulative_ret'].min(),
        y1=df['cumulative_ret'].max(),
        fillcolor=color_map[category],
        opacity=0.2,
        layer='below',
        line_width=0
    )

# Customize layout for clarity and alignment with your target
fig.update_layout(
    title='QQQ 60-Day Cumulative Return (2005-2011) - Tiered by Performance',
    xaxis_title='Date',
    yaxis_title='Cumulative Return',
    hovermode='x unified',
    showlegend=True
)

# Add legend entries for the tier colors (shapes don't auto-appear in legends)
fig.add_trace(go.Scatter(
    x=[None], y=[None],
    marker=dict(color=color_map['Low'], size=10),
    mode='markers',
    name='Low Return'
))
fig.add_trace(go.Scatter(
    x=[None], y=[None],
    marker=dict(color=color_map['Normal'], size=10),
    mode='markers',
    name='Normal Return'
))
fig.add_trace(go.Scatter(
    x=[None], y=[None],
    marker=dict(color=color_map['High'], size=10),
    mode='markers',
    name='High Return'
))

fig.show()

Key Adjustments for Your Data #

• Date Column: Replace 'date' with the actual name of your date column in QQQ2.csv.
• NaN Handling: The first 59 rows of cumulative_ret will be NaN (from the rolling 60-day window)—the code skips these when adding background regions.
• Tiers: Tweak the q parameter in qcut or the bins in cut to adjust how performance tiers are defined.
• Colors: Modify the color_map hex codes to match the exact colors from your target chart.

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