如何在Pandas中基于日期范围实现Left Join关联每月首日？

我有两个Pandas DataFrame，想要基于日期范围条件（df1.start_date ≤ df2.date ≤ df1.end_date）执行左连接，并且只保留df2中每月第一天的记录。

数据构造代码 #

df1构造代码 #

import pandas as pd
d1 = {'object': ['A1', 'B2', 'C1'], 
      'start_date': ['2019-01-19', '2009-07-03', '2021-11-23'],
      'end_date': ['2019-03-15', '2009-10-28', '2024-12-22']
     }
df1 = pd.DataFrame(data=d1)

df2构造代码 #

df2 = pd.DataFrame({"date": pd.date_range('1990-01-01', '2050-12-31')})
df2["first_day_of_month"] = df2["date"].to_numpy().astype('datetime64[M]')

预期逻辑与结果 #

想要实现类似如下SQL的逻辑：

SELECT * FROM df1 LEFT JOIN df2
ON df1.start_date <= df2.date AND df1.end_date >= df2.date
WHERE df2.date = df2.first_day_of_month

即df1中的每一行，会匹配所有处于其start_date和end_date范围内的df2每月首日记录，期望结果示例如下：

步骤1：统一日期类型 #

首先将df1的日期列转换为datetime类型，确保能和df2的日期列正确比较：

df1['start_date'] = pd.to_datetime(df1['start_date'])
df1['end_date'] = pd.to_datetime(df1['end_date'])

步骤2：提取df2的每月首日记录 #

先从df2中筛选出每月第一天的记录，减少后续计算量：

# 筛选每月首日
df2_first_days = df2[df2['date'] == df2['first_day_of_month']].copy()

# 更高效的方式：直接生成每月首日，无需先创建全量日历表
# df2_first_days = pd.DataFrame({"date": pd.date_range('1990-01-01', '2050-12-31', freq='MS')})

步骤3：执行范围左连接 #

提供两种实用方法，可根据数据量选择：

方法一：交叉连接+条件筛选（直观易理解）

先做df1和df2首日记录的交叉连接，再筛选符合日期范围的行：

# 交叉连接（通过临时key实现）
cross_join = df1.assign(key=1).merge(df2_first_days.assign(key=1), on='key').drop('key', axis=1)
# 筛选满足日期范围的记录
result = cross_join[(cross_join['date'] >= cross_join['start_date']) & (cross_join['date'] <= cross_join['end_date'])]
# 调整列顺序和命名，匹配预期结果
result = result[['object', 'start_date', 'end_date', 'date']].rename(columns={'date': 'df2.date'})

方法二：merge_asof连接（高效，适合大数据集）

merge_asof是Pandas专门用于范围匹配的高效方法，需先对df2的首日记录排序：

# 对df2首日记录按日期排序
df2_first_days_sorted = df2_first_days.sort_values('date')
# 执行asof左连接，匹配所有在start_date之后、end_date之前的日期
result = pd.merge_asof(
    df2_first_days_sorted,
    df1,
    left_on='date',
    right_on='start_date',
    direction='backward'
).query('date <= end_date')
# 调整列顺序和命名
result = result[['object', 'start_date', 'end_date', 'date']].rename(columns={'date': 'df2.date'})

结果验证 #

运行上述代码后，result会输出符合预期的结果，其中C1行会匹配2021-12-01到2024-12-01之间的所有每月首日记录。

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