Python实现SQL按时间区间+变量分组聚合 双DataFrame关联均值计算

现有两个DataFrame A和B，字段信息如下：

• DataFrame A包含4个字段：name、start_date、end_date、ID，其中A.ID为name和start_date的拼接值
• DataFrame B包含4个字段：name、date、parameter、value

需求：对value按规则做均值聚合，关联条件为A.name与B.name相等，且B.date落在A.start_date和A.end_date的区间内。A中name可重复，B的date为步长1小时的连续时间。

参考SQL逻辑（修正了原SQL的GROUP BY字段遗漏问题）：

SELECT A.ID, A.name,  B.parameter, AVG(value)
FROM A
JOIN B ON A.name = B.name
WHERE start_date < date < end_date
GROUP BY A.ID, A.name, B.parameter

完整实现代码 #

1. 导入依赖并构造测试数据 #

import pandas as pd

# 构造测试数据A
A = pd.DataFrame({
    'name': ['a', 'b', 'a', 'c'], 
    'start_date': ['01-01-2020', '01-01-2020', '10-01-2020', '15-01-2020'], 
    "end_date": ['05-01-2020', '06-01-2020', '15-01-2020', '20-01-2020'],
    'ID': ['a_01-01-2020', 'b_01-01-2020', 'a_10-01-2020', 'c_15-01-2020']
})

# 构造测试数据B
B = pd.DataFrame({
    'name': ['a','a','b','b','a','a','c','c'], 
    'date': ['01-01-2020:00','01-01-2020:01', '01-01-2020:05','01-01-2020:06', '10-01-2020:12', '10-01-2020:13', '15-01-2020:00', '15-01-2020:01' ],
    'parameter': ['dog','dog','dog','dog','dog','dog','dog','cat'],
    'value': [10,20,20,30,1000,2000,50,100]
})

2. 统一时间格式 #

# A的起止日期转datetime，结束日期加1天覆盖当日所有小时数据
A['start_date'] = pd.to_datetime(A['start_date'], format='%d-%m-%Y')
A['end_date'] = pd.to_datetime(A['end_date'], format='%d-%m-%Y') + pd.Timedelta(days=1)

# B的日期转datetime
B['date'] = pd.to_datetime(B['date'], format='%d-%m-%Y:%H')

3. 关联过滤+分组聚合 #

# 按name关联两表
merged = A.merge(B, on='name', how='inner')
# 过滤符合时间区间的行
filtered = merged[(merged['date'] >= merged['start_date']) & (merged['date'] < merged['end_date'])]
# 按ID、name、parameter分组求value均值
C = filtered.groupby(['ID', 'name', 'parameter'], as_index=False)['value'].mean()

输出结果 #

最终得到的C和预期完全一致：

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