Python Pandas：按ID及日期范围左连接DataFrame并聚合number列

问题背景 #

现有两个DataFrame df1 和 df2，数据结构如下：

df1数据： #

id      start        end
 a  1/12/2022 18/12/2022
 a 19/12/2022 25/12/2022
 a 26/12/2022 31/12/2022
 b 01/12/2022 20/12/2022
 b 21/12/2022 31/12/2022
 c 01/12/2022 31/12/2022
 d 01/12/2022 15/12/2022
 d 16/12/2022 31/12/2022

df2数据： #

id    start_2      end_2  number
 a 15/12/2022 15/12/2022       1
 b 17/12/2022 19/12/2022       3
 b 25/12/2022 27/12/2022       2
 c 12/12/2022 12/12/2022       1
 d 03/12/2022 04/12/2022       2
 d 25/12/2022 25/12/2022       1

需实现以下需求：

• 以df1为主表执行左连接，关联键为id
• 将df2的number匹配到df1对应日期区间的行：当df2的start_2至end_2完全落在df1某一行的start至end区间内时，将该number对应到该行
• 若多个number落在同一区间，按id+日期区间聚合求和
• 未匹配到的行number填0

期望结果：

id      start        end  number
 a  1/12/2022 18/12/2022       1
 a 19/12/2022 25/12/2022       0
 a 26/12/2022 31/12/2022       0
 b 01/12/2022 20/12/2022       3
 b 21/12/2022 31/12/2022       2
 c 01/12/2022 31/12/2022       1
 d 01/12/2022 15/12/2022       2
 d 16/12/2022 31/12/2022       1

解决方案 #

步骤1：转换日期格式为datetime类型 #

先将所有日期列转为datetime类型，确保区间判断的准确性：

import pandas as pd

# 转换df1的日期列
df1['start'] = pd.to_datetime(df1['start'], dayfirst=True)
df1['end'] = pd.to_datetime(df1['end'], dayfirst=True)

# 转换df2的日期列
df2['start_2'] = pd.to_datetime(df2['start_2'], dayfirst=True)
df2['end_2'] = pd.to_datetime(df2['end_2'], dayfirst=True)

步骤2：关联并筛选匹配的日期区间 #

通过id合并两个DataFrame，筛选出日期区间匹配的行后聚合求和，再合并回原表填充缺失值：

# 按id左连接两个DataFrame
merged = pd.merge(df1, df2, on='id', how='left')

# 筛选df2日期区间完全落在df1区间内的行
mask = (merged['start_2'] >= merged['start']) & (merged['end_2'] <= merged['end'])
matched_data = merged[mask].groupby(['id', 'start', 'end'])['number'].sum().reset_index()

# 合并匹配结果到df1，未匹配的行number填0
result = df1.merge(matched_data, on=['id', 'start', 'end'], how='left').fillna(0)
# 将number转为整数类型
result['number'] = result['number'].astype(int)

步骤3：查看最终结果 #

执行代码后，打印result即可得到期望输出：

print(result)

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