如何按年份分组对Pandas DataFrame指定条件的Fee列求和

问题背景 #

现有如下Pandas DataFrame：

import pandas as pd
 
data = {
    'year': ['2000','2000', '2000', '2000','2000','2000','2000','2000','2000','2000','2000','2000','2000','2000','2000',
            '2001','2001','2001','2001','2001','2001','2001','2001','2001','2001','2001','2001','2001','2001','2001',
            '2002','2002','2002','2002','2002','2002','2002','2002','2002','2002','2002','2002','2002','2002','2002'],     
    'type':[2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4],    
    'other_type':[0,1,2,3,4,0,1,2,3,4,0,1,2,3,4,0,1,2,3,4,0,1,2,3,4,0,1,2,3,4,0,1,2,3,4,0,1,2,3,4,0,1,2,3,4],    
    'Fee':[0,0,0,0,0,33,40,50,2,33,0,0,0,0,0,
                  30,50,10,200,45,0,0,0,0,0,0,0,0,0,0,
                 0,0,0,0,0,0,0,0,0,0,30,50,10,200,45]
}  
dfobj = pd.DataFrame(data)

需求是：筛选出type列值为3，且other_type列值为0或1的行，对这些行的Fee列值按年份分组求和。

现有代码存在问题：

• 直接求和会将所有年份结果合并：

  row_Sum = data.loc[(data['type']==3)&(data['other_type'] <2)].sum(axis=0,numeric_only=True)
  

• 逐年份处理效率极低，不适用于大数据集：

  row_Sum = dfobj.loc[(dfobj['year']==2000)&(dfobj['type']==3)&(dfobj['other_type'] <2)].sum(axis=0,numeric_only=True)
  

高效解决方案 #

利用Pandas的布尔索引筛选 + groupby分组聚合即可高效实现需求，这两种操作都是Pandas底层优化的矢量化操作，避免Python层面循环，适合处理大规模数据。

方法1：分步实现（清晰易懂） #

# 1. 筛选符合条件的行：type=3 且 other_type为0或1
filtered_df = dfobj[(dfobj['type'] == 3) & (dfobj['other_type'].isin([0, 1]))]
# 2. 按year分组，仅对Fee列求和
yearly_sum = filtered_df.groupby('year')['Fee'].sum()

方法2：一行代码简化 #

yearly_sum = dfobj[(dfobj['type'] == 3) & (dfobj['other_type'] < 2)].groupby('year')['Fee'].sum()

结果示例 #

运行上述代码后，输出结果如下：

year
2000    73
2001     0
2002     0
Name: Fee, dtype: int64

关键说明 #

• 布尔索引(dfobj['type'] == 3) & (dfobj['other_type'] < 2)快速定位符合条件的行，比逐行判断效率高得多；
• groupby('year')['Fee'].sum()仅对分组后的Fee列执行求和，避免了对其他列的无效计算，进一步提升效率；
• 该方案支持任意规模的数据集，无需手动遍历年份，代码简洁且性能优异。

内容的提问来源于stack exchange，提问作者Sebastian H.