Pandas按HUB、FIBER_NODES分组后提取首尾5周数据的方法咨询

Hey Marco, let's fix this step by step. The issue with your original approach is that you're grouping by WEEK alongside HUB and FIBER_NODES—that means each group is a single week's data for a specific hub/node pair, so nlargest() doesn't make sense here (each group only has one row!). Let's adjust the strategy to get exactly what you need: comparing the average traffic of the first 5 weeks vs. the last 5 weeks (34-38) for each hub/node pair, without keeping the middle weeks.

Method 1: Filter First, Then Aggregate (Most Efficient) #

This approach is faster and more readable, especially with large datasets. We'll first narrow down to only the weeks you care about, then calculate averages and their difference.

import pandas as pd
import numpy as np

# Replace 'traffic_volume' with your actual traffic metric column name
# Step 1: Keep only the weeks we need (1-5 and 34-38)
filtered_traffic = traffic[
    traffic['WEEK'].between(1, 5) | traffic['WEEK'].between(34, 38)
]

# Step 2: Add a label to distinguish early vs. late weeks
filtered_traffic['period'] = np.where(
    filtered_traffic['WEEK'].between(1, 5), 
    'early_weeks_1-5', 
    'late_weeks_34-38'
)

# Step 3: Calculate average traffic per hub/node/period, then reshape the results
avg_traffic = filtered_traffic.groupby(
    ["HUB", "FIBER_NODES", "period"]
)['traffic_volume'].mean().unstack()

# Step 4: Compute the difference between late and early averages
avg_traffic['mean_difference'] = avg_traffic['late_weeks_34-38'] - avg_traffic['early_weeks_1-5']

# Step 5: Reset index to get a clean DataFrame
final_result = avg_traffic.reset_index()

Method 2: Use apply() on Hub/Node Groups #

If you prefer working with group-level logic directly, you can group by HUB and FIBER_NODES (excluding WEEK), then compute the averages within each group:

# Replace 'traffic_volume' with your actual column name
final_result = traffic.groupby(["HUB", "FIBER_NODES"]).apply(
    lambda group: pd.Series({
        'avg_early': group[group['WEEK'].between(1, 5)]['traffic_volume'].mean(),
        'avg_late': group[group['WEEK'].between(34, 38)]['traffic_volume'].mean(),
        'mean_difference': group[group['WEEK'].between(34, 38)]['traffic_volume'].mean() - group[group['WEEK'].between(1, 5)]['traffic_volume'].mean()
    })
).reset_index()

Key Notes: #

• Both methods will only retain the aggregate values (early average, late average, difference) and discard all middle week data.
• Make sure your WEEK column is a numeric type (int/float) so the between() checks work correctly. If it's a string, convert it first with traffic['WEEK'] = pd.to_numeric(traffic['WEEK']).

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