如何通过Pandas GroupBy实现分组聚合并添加加权平均计算列

Let's walk through what went wrong with your code and fix it step by step:

Why You're Getting KeyErrors & Logic Issues #

1. Non-existent Columns in Final Selection: At the end, you tried to select columns like actual and net which were never defined — you meant perf and net perf.
2. Incorrect Aggregation Dictionary Structure: Your initial approach of creating empty perf/net perf columns and trying to aggregate them doesn't work, since those columns have no data. Plus, your lambda functions were operating on individual rows instead of calculating a weighted average across the entire group.
3. Flawed Weighted Average Logic: Your lambda multiplied trade_shares twice, and didn't compute the sum of weighted values divided by total weight — which is how weighted averages work.

Corrected Solution #

First, let's define a clear aggregation function that handles each group, calculates the weighted averages properly, and returns the desired metrics:

import pandas as pd

# Sample data (matches your example)
data = [
    ["EMEA", "flag1", "target1", "broker1", 3900, 39532, 0.00406, -0.067, -0.067, 10.2037, -261.91, 1],
    ["APAC", "flag2", "target2", "broker2", 1700, 17232, 0.00406, -0.067, -0.067, 10.2037, -114.17, 1],
    ["AMER", "flag1", "target1", "broker3", 1400, 14191, 0.00406, -0.067, -0.067, 10.2037, -94.02, 1],
    ["EMEA", "flag2", "target2", "broker2", 2000, 20273, 0.00406, -0.067, -0.067, 10.2037, -134.31, 1]
]
cols = ["region_2", "trade_flag", "trade_target", "broker", "trade_shares", "total_value", "commission_in_gbp", "IS/Order Start PTA - Realized Cost/Sh", "IS/Order Start PTA - Realized Net Cost/Sh", "IS/Order Start PTA - Base Bench Price", "IS/Order Start PTA - P/L", "count"]
df = pd.DataFrame(data, columns=cols)

# If you didn't already have the count column, uncomment this:
# df['count'] = 1

def aggregate_trade_group(group):
    # Calculate sum metrics
    total_count = group['count'].sum()
    total_value = group['total_value'].sum()
    
    # Calculate weighted average for perf
    perf_weighted_sum = (
        group['IS/Order Start PTA - Realized Cost/Sh'] 
        * 10000 
        / group['IS/Order Start PTA - Base Bench Price'] 
        * group['trade_shares']
    ).sum()
    total_shares = group['trade_shares'].sum()
    perf = perf_weighted_sum / total_shares if total_shares != 0 else 0
    
    # Calculate weighted average for net perf
    net_perf_weighted_sum = (
        group['IS/Order Start PTA - Realized Net Cost/Sh'] 
        * 10000 
        / group['IS/Order Start PTA - Base Bench Price'] 
        * group['trade_shares']
    ).sum()
    net_perf = net_perf_weighted_sum / total_shares if total_shares != 0 else 0
    
    # Return a Series with the aggregated metrics
    return pd.Series({
        'count': total_count,
        'total_value': total_value,
        'perf': perf,
        'net perf': net_perf
    })

# Perform groupby and aggregation
aggregated_df = df.groupby(['region_2', 'trade_flag', 'broker']).apply(aggregate_trade_group).reset_index()

# Reorder columns to match your desired output
aggregated_df = aggregated_df[['region_2', 'trade_flag', 'broker', 'count', 'total_value', 'perf', 'net perf']]

print(aggregated_df)

What This Does #

• Group Handling: The aggregate_trade_group function receives each grouped subset of your DataFrame, so we can compute metrics across the entire group.
• Proper Weighted Averages: We calculate the sum of (per-row perf value * trade_shares) then divide by total trade_shares to get the weighted average — this is the standard way to compute weighted averages in group aggregations.
• Clean Column Output: After aggregation, we reset the index to bring the group keys back as columns, then reorder to match your desired output structure.

Sample Output #

For your example data, the output will look like this (rounded for readability):

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