基于另一表格日期对汇总Pandas表格列值的高效实现方法

数据示例 #

df1 #

date        poly_name
0   2018-01-29  zamg_4905_poly4
1   2018-03-25  zamg_4905_poly4
2   2018-03-30  zamg_4905_poly4
3   2018-04-04  zamg_4905_poly4
4   2018-04-11  zamg_4905_poly4
5   2018-04-19  zamg_4905_poly4
6   2018-04-21  zamg_4905_poly4
7   2018-04-29  zamg_4905_poly4
8   2018-05-06  zamg_4905_poly4
9   2018-05-19  zamg_4905_poly4

df2 #

date        rain (mm)
0   2018-03-22  0.3
1   2018-03-23  0.3
2   2018-03-24  0.0
3   2018-03-25  0.0
4   2018-03-26  0.3
5   2018-03-27  2.0
6   2018-03-28  17.6
7   2018-03-29  1.5
8   2018-03-30  0.0
9   2018-03-31  4.0
10  2018-04-01  4.8
11  2018-04-02  0.0
12  2018-04-03  0.0
13  2018-04-04  0.0
14  2018-04-05  2.1
15  2018-04-06  0.0
16  2018-04-07  0.0
17  2018-04-08  0.0
18  2018-04-09  0.0
19  2018-04-10  0.0

需求说明 #

针对df1里每一对连续日期（比如2018-01-29和2018-03-25、2018-03-25和2018-03-30），计算df2中该时间段内的rain (mm)总和，把结果加到df1中对应较晚日期的行里，作为新列。

举两个例子：

• 第一组区间2018-01-29到2018-03-25的雨量总和是0.6，对应放在df1的第1行
• 第二组区间2018-03-25到2018-03-30的雨量总和是21.4，对应放在df1的第2行

优雅高效的实现方法 #

不用循环遍历日期对，直接用Pandas的内置方法处理，既简洁又高效。

方法一：基于累计和与区间匹配 #

1. 预处理数据

先统一日期格式，给df2设置日期索引，再计算雨量的累计和——这样任意区间的总和就能用“终点累计值减起点累计值”快速算出：

import pandas as pd

# 转换为Pandas日期类型
df1['date'] = pd.to_datetime(df1['date'])
df2['date'] = pd.to_datetime(df2['date'])

# 给df2按日期排序并设为索引，生成累计雨量列
df2 = df2.set_index('date').sort_index()
df2['cum_rain'] = df2['rain (mm)'].cumsum()

2. 生成连续日期区间

给df1添加一列存储上一行的日期，作为区间的起始点；第一行没有前序日期，就设一个比df2最早日期还早的值，确保这部分区间没有雨量数据：

# 新增prev_date列，存上一行的日期
df1['prev_date'] = df1['date'].shift(1)
# 第一行的prev_date设为df2最早日期的前一天
df1.loc[0, 'prev_date'] = df2.index.min() - pd.Timedelta(days=1)

3. 计算区间雨量总和

用map方法分别获取每个区间起点和终点的累计雨量，差值就是该区间的总和，最后清理临时列：

# 获取每个区间结束日期的累计雨量，没有匹配到就取df2最后一个累计值
df1['end_cum'] = df1['date'].map(lambda x: df2['cum_rain'].get(x, df2['cum_rain'].iloc[-1]))
# 获取每个区间起始日期的累计雨量，没有匹配到就取0
df1['start_cum'] = df1['prev_date'].map(lambda x: df2['cum_rain'].get(x, 0))
# 计算区间总和
df1['interval_rain_sum'] = df1['end_cum'] - df1['start_cum']

# 删除临时列
df1 = df1.drop(['prev_date', 'end_cum', 'start_cum'], axis=1)

方法二：基于searchsorted的高效写法 #

如果数据量很大，用searchsorted快速定位日期在df2中的位置，效率会更高：

import pandas as pd

df1['date'] = pd.to_datetime(df1['date'])
df2['date'] = pd.to_datetime(df2['date'])

# 给df2按日期排序，计算累计雨量
df2_sorted = df2.sort_values('date').reset_index(drop=True)
df2_sorted['cum_rain'] = df2_sorted['rain (mm)'].cumsum()

# 用searchsorted找到每个区间起点和终点在df2中的位置
start_pos = df2_sorted['date'].searchsorted(df1['date'].shift(1).fillna(pd.Timestamp.min))
end_pos = df2_sorted['date'].searchsorted(df1['date'], side='right')

# 计算每个区间的雨量总和，处理边界情况（比如区间在df2数据范围外时总和为0）
df1['interval_rain_sum'] = [
    df2_sorted['cum_rain'].iloc[e-1] - (df2_sorted['cum_rain'].iloc[s-1] if s > 0 else 0)
    if e > s else 0
    for s, e in zip(start_pos, end_pos)
]

结果验证 #

最终df1会新增interval_rain_sum列，里面的值完全符合需求：

• 第1行（2018-03-25）的总和为0.6
• 第2行（2018-03-30）的总和为21.4

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