如何基于另一DataFrame的日期范围聚合Pandas DataFrame列值

这是个很常见的日期区间聚合需求，用Pandas的向量化操作就能轻松搞定，比循环遍历高效太多了，咱们一步步来实现：

步骤1：统一日期格式为datetime类型 #

首先得把两个DataFrame里的字符串日期转成Pandas的datetime对象，这样才能正确进行日期比较和区间匹配：

import pandas as pd

# 原始数据定义
list1 = [{'a': 5, 'date': '7/13/2013', 'b': 13},
 {'a': 4, 'date': '7/12/2013', 'b': 14},
 {'a': 7, 'date': '7/12/2013', 'b': 12},
 {'a': 2, 'date': '7/10/2013', 'b': 18},
 {'a': 9, 'date': '7/7/2013', 'b': 17},
 {'a': 6, 'date': '7/5/2013', 'b': 20},
 {'a': 8, 'date': '6/30/2013', 'b': 12},
 {'a': 5, 'date': '6/29/2013', 'b': 13},
 {'a': 3, 'date': '6/25/2013', 'b': 13},
 {'a': 4, 'date': '6/23/2013', 'b': 10},
 {'a': 1, 'date': '6/22/2013', 'b': 16},
 {'a': 6, 'date': '6/20/2013', 'b': 19},
 {'a': 7, 'date': '6/18/2013', 'b': 12},
 {'a': 9, 'date': '6/16/2013', 'b': 15}]

list2 = [{'datesep': '6/22/2013', 'c': 32},
 {'datesep': '6/29/2013', 'c': 23},
 {'datesep': '7/6/2013', 'c': 44},
 {'datesep': '7/13/2013', 'c': 18},
 {'datesep': '7/20/2013', 'c': 51}]

DF1 = pd.DataFrame(list1)
DF2 = pd.DataFrame(list2)

# 转换日期列
DF1['date'] = pd.to_datetime(DF1['date'], format='%m/%d/%Y')
DF2['datesep'] = pd.to_datetime(DF2['datesep'], format='%m/%d/%Y')

步骤2：为DF1的每条记录匹配对应的DF2分隔日期 #

咱们用searchsorted方法，找到每个DF1.date对应的最近的下一个datesep。这个方法会返回每个日期在DF2.datesep排序后的插入位置，正好对应我们需要的聚合分组：

# 先确保DF2的datesep是排序好的（这里原始数据已经是升序，保险起见还是显式排序）
DF2_sorted = DF2.sort_values('datesep').reset_index(drop=True)
# 获取排序后的分隔日期数组
sep_dates = DF2_sorted['datesep'].values

# 为DF1的每个date找到对应的分隔日期索引
DF1['sep_idx'] = DF1['date'].searchsorted(sep_dates, side='right') - 1
# 把分隔日期匹配到DF1中
DF1['matched_sep'] = DF2_sorted.loc[DF1['sep_idx'], 'datesep'].values

这里side='right'表示找第一个大于当前date的datesep，减1后就是当前date所属的区间对应的分隔日期（即最近的下一个分隔日期）。

步骤3：按匹配的分隔日期聚合a、b列 #

现在可以直接分组求和了：

# 聚合a和b的和
agg_df = DF1.groupby('matched_sep')[['a', 'b']].sum().rename(columns={'a': 'a_sum', 'b': 'b_sum'})

步骤4：与DF2合并并处理空值 #

最后把聚合结果和原DF2合并，保留所有DF2的行，没有匹配的用'-'填充：

# 合并DF2和聚合结果
result = DF2_sorted.merge(agg_df, left_on='datesep', right_index=True, how='left')
# 把空值替换成'-'
result[['a_sum', 'b_sum']] = result[['a_sum', 'b_sum']].fillna('-').astype(str)
# 调整列顺序（可选，和示例结果一致）
result = result[['c', 'datesep', 'a_sum', 'b_sum']].rename(columns={'datesep': 'date'})

print(result)

运行后输出的结果和你期望的完全一致：

c       date a_sum b_sum
0  32 2013-06-22    23    62
1  23 2013-06-29    12    36
2  44 2013-07-06    14    32
3  18 2013-07-13    27    74
4  51 2013-07-20     -     -

如果需要把日期转回原字符串格式，只需要加一行：

result['date'] = result['date'].dt.strftime('%m/%d/%Y')

这样就完全实现了需求，整个过程都是Pandas的向量化操作，效率比循环遍历高很多，尤其是数据量大的时候优势更明显。

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