Pandas分组滚动均值最大值计算性能优化咨询

Hey there! Let's break down why your code is running so slow and how we can speed it up significantly. The core issue is redundant, expensive computations that we can eliminate with smarter grouping and batch processing.

Core Bottlenecks in Your Current Code #

1. Repeated groupby + rolling Operations: You're re-running the full groupby and rolling mean calculation for every single interval length. This means you're doing the same grouping work dozens (or hundreds) of times—total waste of resources.
2. Unintended Modifications to Original Data: When you do temp_df = intervals, you're creating a reference to the original DataFrame, not a copy. Adding columns like best_power and interval in each loop bloats the original data and causes unnecessary memory overhead.
3. Overusing groupby for Single-Group Data: Your latest_df only contains one workout, but you still run groupby(['workoutId']) on it—this adds unnecessary overhead when a direct rolling calculation would work.

Optimized Approach #

We'll fix these issues by:

• Grouping Once, Processing All Intervals: Split the data by workoutId once, then compute all required rolling mean maxima for each group in one pass.
• Avoiding Side Effects: We'll only extract the rows we need (the max rolling mean for each interval) instead of modifying the original dataset.
• Simplifying Single-Group Logic: Skip groupby entirely for the latest workout since it's just one group.
• Cleaner Interval Generation: Use numpy.arange to build your interval list more efficiently.

Optimized Code #

import pandas as pd
import numpy as np
import math

# Generate interval lengths with numpy (cleaner & slightly faster)
interval_lengths = np.concatenate([
    np.arange(1, 61),                # 1s intervals 0-60s
    np.arange(75, 301, 15),          # 15s intervals 1:15-5:00
    np.arange(330, 601, 30),         # 30s intervals 5:00-10:00
    np.arange(660, 
              df_samples['seconds_since_pedaling_start'].apply(lambda x: int(math.ceil(x / 10.0)) * 10).max() + 1, 
              60)                     # 1min intervals after 10min
]).tolist()

# Preprocess base data
intervals = df_samples.sort_index(ascending=True)
intervals['power'] = intervals['power'].interpolate()  # Fill missing power values

# Isolate the latest workout
latest_workout_id = intervals['workoutId'].iloc[-1]
latest_df = intervals[intervals['workoutId'] == latest_workout_id]
latest_df_length = latest_df['seconds_since_pedaling_start'].max()

# --------------------------
# Process all historical workouts
# --------------------------
def process_single_workout(group):
    """Calculate max rolling mean for all intervals for one workout"""
    group_results = []
    for interval in interval_lengths:
        # Compute rolling mean for this interval
        rolling_avg = group['power'].rolling(window=interval, min_periods=interval-1).mean()
        # Find the row with the highest rolling average
        max_avg_idx = rolling_avg.idxmax()
        
        if pd.notna(max_avg_idx):  # Skip if no valid window exists
            result_row = group.loc[max_avg_idx].copy()
            result_row['interval'] = interval
            result_row['best_power'] = rolling_avg.loc[max_avg_idx]
            group_results.append(result_row)
    
    return pd.DataFrame(group_results)

# Apply processing to every workout group, then combine results
all_workout_intervals = intervals.groupby('workoutId').apply(process_single_workout).reset_index(drop=True)

# For each interval, keep only the global best (highest best_power)
best_interval_df = all_workout_intervals.loc[
    all_workout_intervals.groupby('interval')['best_power'].idxmax()
].set_index('interval')
best_interval_df['datetime'] = best_interval_df.index  # Match original logic

# --------------------------
# Process latest workout only
# --------------------------
latest_interval_results = []
for interval in interval_lengths:
    if interval > latest_df_length:
        continue  # Skip intervals longer than the latest workout
    
    rolling_avg = latest_df['power'].rolling(window=interval, min_periods=interval-1).mean()
    max_avg_idx = rolling_avg.idxmax()
    
    if pd.notna(max_avg_idx):
        result_row = latest_df.loc[max_avg_idx].copy()
        result_row['interval'] = interval
        result_row['best_power'] = rolling_avg.loc[max_avg_idx]
        latest_interval_results.append(result_row)

latest_interval_df = pd.DataFrame(latest_interval_results).set_index('interval')
latest_interval_df['datetime'] = latest_interval_df.index  # Match original logic

Extra Tips for Even More Speed #

• Simplify min_periods: Since you're interpolating missing power values, you can set min_periods=interval (or omit it entirely, since default is window size) to ensure you only compute averages for full windows—this makes the logic cleaner and avoids invalid partial windows.
• Use numba for Ultra-Large Datasets: If your data is massive, you can compile the rolling mean calculation with numba to get a further speed boost.
• Trim Unnecessary Columns: When copying rows (like result_row = group.loc[max_avg_idx].copy()), only copy the columns you actually need (e.g., power, workoutId, seconds_since_pedaling_start) instead of the entire row to save memory.

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