如何在层级DataFrame中跨可变间隔行计算百分比变动（无法用移位运算符）

Let's break down how to solve this problem—since you can't rely on shift() due to inconsistent row spacing, we need a way to explicitly pair the base value with its corresponding target row within each (Symbol, Date, Num) group. I'll walk you through two practical approaches, depending on your specific data structure.

Example Data Setup #

First, let's use a sample DataFrame to mimic your scenario (adjust columns/conditions to match your actual data):

import pandas as pd

data = {
    'Symbol': ['AAPL', 'AAPL', 'AAPL', 'MSFT', 'MSFT', 'MSFT'],
    'Date': ['2023-01-01', '2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02', '2023-01-02'],
    'Num': [1, 1, 1, 2, 2, 2],
    'Value': [100, 120, 150, 200, 240, 300],
    'Row_Type': ['Base', 'Target', 'Other', 'Base', 'Other', 'Target']
}
df = pd.DataFrame(data)

In this example, each group has a Base row (your "first value") and a Target row (the row you need to find based on the base value's calculation).

Approach 1: Merge Base and Target Rows (Best for Clear Row Identifiers) #

If your base and target rows have explicit markers (like the Row_Type column above), this method is efficient and easy to read:

1. Extract base and target rows separately
2. Merge them on your group keys
3. Calculate the percentage change

# Step 1: Isolate base and target rows
base_rows = df[df['Row_Type'] == 'Base'][['Symbol', 'Date', 'Num', 'Value']].rename(columns={'Value': 'Base_Value'})
target_rows = df[df['Row_Type'] == 'Target'][['Symbol', 'Date', 'Num', 'Value']].rename(columns={'Value': 'Target_Value'})

# Step 2: Merge to pair base and target within each group
paired_df = pd.merge(base_rows, target_rows, on=['Symbol', 'Date', 'Num'], how='left')

# Step 3: Calculate percentage change
paired_df['Pct_Change'] = ((paired_df['Target_Value'] - paired_df['Base_Value']) / paired_df['Base_Value']) * 100
paired_df['Pct_Change'] = paired_df['Pct_Change'].round(2)

This gives you a clean result with all group details and the calculated percentage change. Use how='left' to retain groups where no target row exists (they'll show NaN for target values).

Approach 2: Custom Groupby Function (Best for Dynamic Lookup Logic) #

If your target row isn't marked with a static identifier—instead, you need to find it using a calculation based on the base value (e.g., "find the first row where Value is 15% higher than the base")—use a custom function with groupby:

def compute_group_pct(group):
    # Get the base value (adjust this to match how you define the "first value" in your group)
    base_val = group[group['Row_Type'] == 'Base']['Value'].iloc[0]
    
    # Example dynamic lookup: find the first row where Value >= base_val * 1.15
    target_row = group[group['Value'] >= base_val * 1.15].head(1)
    
    if not target_row.empty:
        target_val = target_row['Value'].iloc[0]
        pct_change = ((target_val - base_val) / base_val) * 100
        return pd.Series({
            'Symbol': group['Symbol'].iloc[0],
            'Date': group['Date'].iloc[0],
            'Num': group['Num'].iloc[0],
            'Base_Value': base_val,
            'Target_Value': target_val,
            'Pct_Change': round(pct_change, 2)
        })
    else:
        # Handle groups with no matching target row
        return pd.Series({
            'Symbol': group['Symbol'].iloc[0],
            'Date': group['Date'].iloc[0],
            'Num': group['Num'].iloc[0],
            'Base_Value': base_val,
            'Target_Value': None,
            'Pct_Change': None
        })

# Apply the function to each group
result_df = df.groupby(['Symbol', 'Date', 'Num']).apply(compute_group_pct).reset_index(drop=True)

This approach gives you full control over how you locate the target row—just modify the target_row filtering logic to match your specific calculation rule.

Key Notes for Your Use Case #

• Adjust the base value extraction: If your "first value" is the first row in the group (not marked by a column), replace the base_val line with base_val = group['Value'].iloc[0].
• Handle edge cases: Add checks for groups where no target row exists (like the else clause in Approach 2) to avoid errors.
• Performance: For large datasets, Approach 1 (merge) is faster than a custom groupby function, so use that if your row identifiers are static.

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