如何在Pandas的GroupBy中同时实现多列求和与列计数并添加百分比列

Absolutely! You can absolutely handle both sum operations and the count in a single groupby call using pandas' agg() method—it's designed exactly for this kind of mixed aggregation scenario. Let me walk you through how to adjust your code:

Step-by-Step Solution #

First, let's tweak your initial data filter to be more precise (using == instead of str.contains() ensures you only get rows where state is exactly 'Done', avoiding accidental matches with similar strings). Then we'll use agg() to bundle all your required aggregations into one groupby step.

Here's the revised, complete code:

# 初始数据筛选（严格匹配'Done'状态）
df_RFQ_by_Salesperson = df[df['state'] == 'Done'][['sales_person_name2', 'rfq_qty', 'rfq_qty_CAD_Equiv', 'state']].copy()
display(df_RFQ_by_Salesperson.head(3))

# 单次groupby完成求和与计数操作
df_RFQ_by_Salesperson = df_RFQ_by_Salesperson.groupby('sales_person_name2').agg(
    rfq_qty=('rfq_qty', 'sum'),
    rfq_qty_CAD_Equiv=('rfq_qty_CAD_Equiv', 'sum'),
    Done_Trades=('state', 'count')  # 对state列计数，命名为清晰的列名
)

# 计算百分比列（和你原来的逻辑一致）
Total_Done_Volume = df_RFQ_by_Salesperson['rfq_qty_CAD_Equiv'].sum()
df_RFQ_by_Salesperson['Percentage'] = df_RFQ_by_Salesperson['rfq_qty_CAD_Equiv'] / Total_Done_Volume

# 展示排序后的结果
display(df_RFQ_by_Salesperson.sort_values('Percentage', ascending=False))

What's happening here? #

• The agg() method accepts keyword arguments (pandas 0.25+ syntax) where each key is the new column name you want, and the value is a tuple of (source_column, aggregation_function). This lets you mix different operations for different columns in one go.
• For Done_Trades, we use count() on the state column. Since we already filtered rows to only include 'Done' state, this directly gives the number of completed trades per salesperson.
• If you're using an older pandas version (pre-0.25), you can use the dictionary syntax instead:

  df_RFQ_by_Salesperson = df_RFQ_by_Salesperson.groupby('sales_person_name2').agg({
      'rfq_qty': 'sum',
      'rfq_qty_CAD_Equiv': 'sum',
      'state': 'count'
  }).rename(columns={'state': 'Done_Trades'})
  

This approach keeps your code concise, efficient, and avoids needing multiple groupby calls or merging dataframes later.

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