如何在Pandas中按Key匹配且日期范围条件合并两个DataFrame？

原始数据 #

表格1（日期范围表） #

表格2（交易数据表） #

需求说明 #

将两个表格按key字段匹配合并，同时检查表格2的date是否落在表格1对应的start和end日期范围内：

• 日期在范围内时，保留表格2的date和amount数据
• 日期不在范围内时，date和amount显示为空
• 最终结果需保留表格1的所有行

解决方案步骤 #

1. 导入库并创建DataFrame #

先将数据转为Pandas的DataFrame，必须把日期列转换为datetime类型，否则无法进行日期范围判断：

import pandas as pd

# 创建表格1的DataFrame
df1 = pd.DataFrame({
    'key': [23, 15, 03, 11],
    'start': ['05/04/2019', '23/10/2017', '11/05/2020', '17/11/2016'],
    'end': ['23/01/2020', '23/06/2018', '23/07/2022', '23/10/2018']
})

# 创建表格2的DataFrame
df2 = pd.DataFrame({
    'key': [11, 23, 03, 15],
    'date': ['01/10/2017', '14/11/2019', '10/01/2010', '30/05/2018'],
    'amount': [230, 100, 400, 830]
})

# 转换日期列为datetime类型（适配dd/mm/yyyy格式）
df1['start'] = pd.to_datetime(df1['start'], format='%d/%m/%Y')
df1['end'] = pd.to_datetime(df1['end'], format='%d/%m/%Y')
df2['date'] = pd.to_datetime(df2['date'], format='%d/%m/%Y')

2. 按Key左连接并筛选日期范围 #

先通过merge做左连接保留表格1的所有行，再用条件判断将不符合日期范围的date和amount设为空值：

# 按key左连接两个表格
merged_df = pd.merge(df1, df2, on='key', how='left')

# 筛选日期在start和end之间的记录，不符合的字段置空
mask = (merged_df['date'] >= merged_df['start']) & (merged_df['date'] <= merged_df['end'])
merged_df.loc[~mask, ['date', 'amount']] = pd.NA

# 转换日期列回原字符串格式（适配期望输出样式）
merged_df['start'] = merged_df['start'].dt.strftime('%d/%m/%Y')
merged_df['end'] = merged_df['end'].dt.strftime('%d/%m/%Y')
merged_df['date'] = merged_df['date'].dt.strftime('%d/%m/%Y').fillna('')
merged_df['amount'] = merged_df['amount'].fillna('')

3. 最终结果 #

执行上述代码后，得到的结果如下：

关键说明 #

• 日期列必须转换为datetime类型，字符串无法直接进行大小比较
• 左连接(how='left')确保表格1的所有行都被保留
• 布尔掩码(mask)用于精准筛选符合日期范围的记录
• 转换日期列回字符串格式是为了匹配期望输出样式，若后续需日期计算可跳过此步骤

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