如何在Pandas query中扩展时间范围并筛选数据？

问题背景 #

现有如下结构的DataFrame：

start_time_1    end_time_1    start_time_2    end_time_2
1 
2  

原本使用df.query()筛选两个时间区间重叠的行，代码可正常运行：

df.query(
    "(start_time_1 <= start_time_2 <= end_time_1) or "
    "(start_time_2 <= start_time_1 <= end_time_2)"
)

但尝试将两个时间范围各扩展10秒后，以下代码无法运行：

df.query(
    "(start_time_1-timedelta(seconds=10) <= start_time_2 <= end_time_1+timedelta(seconds=10)) or "
    "(start_time_2-timedelta(seconds=10) <= start_time_1 <= end_time_2+timedelta(seconds=10))"
)

解决方案 #

df.query()的字符串表达式无法直接调用timedelta，可以用两种方法解决：

方法1：提前计算扩展后的时间列 #

先在DataFrame中新增扩展后的时间列，再用query筛选：

import pandas as pd
from datetime import timedelta

# 新增扩展后的时间列
df['start_time_1_ext'] = df['start_time_1'] - timedelta(seconds=10)
df['end_time_1_ext'] = df['end_time_1'] + timedelta(seconds=10)
df['start_time_2_ext'] = df['start_time_2'] - timedelta(seconds=10)
df['end_time_2_ext'] = df['end_time_2'] + timedelta(seconds=10)

# 用新列执行筛选
result = df.query(
    "(start_time_1_ext <= start_time_2 <= end_time_1_ext) or "
    "(start_time_2_ext <= start_time_1 <= end_time_2_ext)"
)

# 可选：删除临时新增的列
result = result.drop(['start_time_1_ext', 'end_time_1_ext', 'start_time_2_ext', 'end_time_2_ext'], axis=1)

方法2：直接使用布尔索引 #

跳过query的字符串解析，直接在布尔条件中计算时间偏移：

import pandas as pd
from datetime import timedelta

# 构造布尔筛选条件
mask = (
    (df['start_time_1'] - timedelta(seconds=10) <= df['start_time_2']) & (df['start_time_2'] <= df['end_time_1'] + timedelta(seconds=10))
) | (
    (df['start_time_2'] - timedelta(seconds=10) <= df['start_time_1']) & (df['start_time_1'] <= df['end_time_2'] + timedelta(seconds=10))
)

result = df[mask]

补充说明 #

df.query()的表达式上下文默认不包含datetime.timedelta这类外部对象，直接在字符串中调用会触发解析错误。提前计算列或直接使用布尔索引都是更稳妥的方案，后者还省去了临时列的创建步骤。

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