如何在Pandas中按条件从另一DataFrame生成求和列（类Excel SUMIFS）

需求说明 #

现有两个DataFrame：

• sales：销售数据表，包含商品类型（Fruit）、销售日期（Date）和销量（Quantity）
• ref：参考表，包含商品类型（Fruit）和参考日期（Date）

需要给ref新增一列Total，显示对应商品在参考日期前后7天内的总销量。

示例数据 #

import pandas as pd

sales = pd.DataFrame({'Fruit': {0: 'apples',  1: 'oranges',  2: 'pears',  3: 'apples',  4: 'apples',  5: 'bananas',  6: 'oranges', 7: 'pears',  8: 'pears',  9: 'oranges',  10: 'bananas',  11: 'apples',  12: 'pears',  13: 'pears', 14: 'apples',  15: 'pears',  16: 'oranges',  17: 'oranges',  18: 'pears'},
                      'Date': {0: '2023-07-07',  1: '2023-02-05',  2: '2023-08-16',  3: '2023-07-26',  4: '2023-07-14', 5: '2024-02-01',  6: '2023-09-19',  7: '2023-04-08',  8: '2023-06-08',  9: '2023-05-15', 10: '2023-10-20',  11: '2023-07-25',  12: '2023-07-31',  13: '2023-10-08',  14: '2023-06-28', 15: '2023-08-15',  16: '2023-05-14',  17: '2023-07-28',  18: '2023-07-29'},
                      'Quantity': {0: 18,  1: 10,  2: 10,  3: 20,  4: 16,  5: 14,  6: 18,  7: 18,  8: 14,  9: 19,  10: 16, 11: 16,  12: 17,  13: 10,  14: 16,  15: 15,  16: 18,  17: 20,  18: 19}})
sales['Date'] = pd.to_datetime(sales['Date'])

ref = pd.DataFrame({'Fruit': {0: 'apples', 1: 'bananas',  2: 'oranges',  3: 'apples',  4: 'pears', 5: 'oranges',  6: 'bananas',  7: 'oranges',  8: 'oranges'}, 
                    'Date': {0: '2023-07-25',  1: '2023-12-27',  2: '2023-07-13',  3: '2023-06-27',  4: '2023-07-08',  5: '2023-09-17', 6: '2023-10-25',  7: '2023-10-05',  8: '2023-04-14'}})
ref['Date'] = pd.to_datetime(ref['Date'])

比如ref第一行（apples，2023-07-25）的Total应为36，对应sales中2023-07-25的16个苹果和2023-07-26的20个苹果之和。

对应Excel公式：

=SUMIFS(sales.Quantity, sales.Fruit, ref.Fruit, sales.Date, ">="&ref.Date-7, sales.Date, "<="&ref.Date+7)

遇到的问题 #

单行计算时逻辑正常，比如：

# 硬编码参数
sales[(sales['Fruit']=='apples')&
      (sales['Date']>=pd.to_datetime('2023-07-25')-pd.to_timedelta(7, unit='d'))&
      (sales['Date']<=pd.to_datetime('2023-07-25')+pd.to_timedelta(7, unit='d'))]['Quantity'].sum()

# 用iloc取ref第一行
sales[(sales['Fruit']==ref.iloc[0,0])&
      (sales['Date']>=ref.iloc[0,1]-pd.to_timedelta(7, unit='d'))&
      (sales['Date']<=ref.iloc[0,1]+pd.to_timedelta(7, unit='d'))]['Quantity'].sum()

但批量计算时执行以下代码报错ValueError: Can only compare identically-labeled Series objects：

ref['Total'] = sales[(sales['Fruit']==ref.iloc[ref.index,0])&
      (sales['Date']>=ref.iloc[ref.index,1]-pd.to_timedelta(7, unit='d'))&
      (sales['Date']<=ref.iloc[ref.index,1]+pd.to_timedelta(7, unit='d'))]['Quantity'].sum()

错误原因：ref.iloc[ref.index,0]的写法错误，iloc接收的行索引是单个位置或切片，用ref.index会返回整个索引序列，导致生成的Series和sales的Series维度不匹配，无法直接比较。

解决方案 #

方法1：用apply逐行处理（直观易理解） #

把单行计算的逻辑封装成函数，用apply遍历ref的每一行：

def get_total_sales(row):
    # 生成匹配条件的掩码
    match_mask = (sales['Fruit'] == row['Fruit']) & \
                 (sales['Date'] >= row['Date'] - pd.Timedelta(days=7)) & \
                 (sales['Date'] <= row['Date'] + pd.Timedelta(days=7))
    # 返回符合条件的销量总和
    return sales.loc[match_mask, 'Quantity'].sum()

# 新增Total列
ref['Total'] = ref.apply(get_total_sales, axis=1)

方法2：合并后分组求和（适合中小数据量） #

先按商品类型合并两个表，过滤日期范围后再分组求和：

# 按Fruit合并，区分两个表的日期列
merged_df = ref.merge(sales, on='Fruit', suffixes=('_ref', '_sales'))

# 过滤出参考日期前后7天的销售记录
filtered = merged_df[(merged_df['Date_sales'] >= merged_df['Date_ref'] - pd.Timedelta(days=7)) & 
                     (merged_df['Date_sales'] <= merged_df['Date_ref'] + pd.Timedelta(days=7))]

# 按ref的每一行（Fruit+Date_ref）分组求和
total_sales = filtered.groupby(['Fruit', 'Date_ref'])['Quantity'].sum().reset_index(name='Total')

# 合并回ref，没有匹配的销量填0
ref = ref.merge(total_sales, 
                left_on=['Fruit', 'Date'], 
                right_on=['Fruit', 'Date_ref'], 
                how='left').fillna(0)

# 清理多余列
ref = ref.drop('Date_ref', axis=1)

方法3：向量化广播计算（效率最高，适合大数据量） #

利用numpy的广播特性，一次性完成所有条件判断和求和：

# 转换为numpy数组，方便广播操作
sales_fruit = sales['Fruit'].to_numpy()
sales_date = sales['Date'].to_numpy()
sales_qty = sales['Quantity'].to_numpy()

ref_fruit = ref['Fruit'].to_numpy()[:, None]  # 转成列向量，实现广播
ref_date = ref['Date'].to_numpy()[:, None]

# 生成全局掩码：商品匹配 + 日期在前后7天内
mask = (sales_fruit == ref_fruit) & \
       (sales_date >= ref_date - pd.Timedelta(days=7)) & \
       (sales_date <= ref_date + pd.Timedelta(days=7))

# 按ref的行求和
ref['Total'] = (sales_qty * mask).sum(axis=1)

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