如何在Pandas中实现类Excel计算列的自定义分组聚合？

Absolutely! The key here is that Excel's pivot calculated fields perform aggregation first (sum A, sum B) then compute the ratio—whereas your current code tries to calculate the ratio row-wise first, which isn't what you want. Let's fix this with a few straightforward approaches that work for any grouping:

1. Aggregate first, then compute the calculated column #

This method mirrors exactly how Excel's pivot calculated fields work, and it's the most efficient:

import pandas as pd

df1 = pd.DataFrame({'lab':['lab1','lab2']*5,'A':[1,2]*5,'B':[4,5]*5})

# Step 1: Aggregate A and B by your grouping column
aggregated = df1.groupby('lab').agg({'A': 'sum', 'B': 'sum'}).reset_index()

# Step 2: Calculate your new column using the aggregated values
aggregated['C'] = aggregated['A'] / aggregated['B']

print(aggregated)

This gives the expected result:

2. Use a custom aggregation function (one-step approach) #

If you prefer to handle everything in a single groupby call, write a custom function that computes all three values at once:

def group_calculation(group):
    sum_a = group['A'].sum()
    sum_b = group['B'].sum()
    return pd.Series([sum_a, sum_b, sum_a/sum_b], index=['A', 'B', 'C'])

result = df1.groupby('lab').apply(group_calculation).reset_index()
print(result)

This will produce the same output as the first method.

3. Fix your pivot_table attempt #

Your original pivot table code was using the wrong lambda (it operated on the row-wise C column instead of the aggregated sums). Instead, compute the pivot for A and B first, then add your calculated column:

pivot = pd.pivot_table(df1, index='lab', values=['A', 'B'], aggfunc='sum').reset_index()
pivot['C'] = pivot['A'] / pivot['B']
print(pivot)

All these methods ensure your calculation (sum(A)/sum(B)) is applied after aggregation, and this logic will work for any grouping you choose—just adjust the groupby or pivot_table index to match your desired grouping column(s).

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