如何在Pandas中按时间匹配合并两个DataFrame并添加对应数据列

先明确核心思路：把宽表结构的df_aux转成长表，对齐df_main的事件前一小时时段，再通过地点+时段匹配合并数据。以下是具体步骤及示例代码：

1. 准备示例数据（模拟你的场景） #

先确保时间列都是datetime类型，避免匹配出错：

import pandas as pd

# 模拟df_main：记录事件时间与地点
df_main = pd.DataFrame({
    'Event_Date': pd.to_datetime([
        '2024-01-01 08:30:00',
        '2024-01-01 09:15:00',
        '2024-01-01 10:45:00',
        '2024-01-01 11:00:00'
    ]),
    'Description': ['A', 'B', 'E', 'C']  # E是df_aux中不存在的地点
})

# 模拟df_aux：1小时粒度的各地点行人数量
df_aux = pd.DataFrame({
    'Initial_Date': pd.to_datetime([
        '2024-01-01 07:00:00',
        '2024-01-01 08:00:00',
        '2024-01-01 09:00:00',
        '2024-01-01 10:00:00'
    ]),
    'Final_Date': pd.to_datetime([
        '2024-01-01 08:00:00',
        '2024-01-01 09:00:00',
        '2024-01-01 10:00:00',
        '2024-01-01 11:00:00'
    ]),
    'A': [120, 150, 180, 200],
    'B': [80, 90, 100, 110],
    'C': [50, 60, 70, 80],
    'D': [30, 40, 50, 60]
})

2. 转换df_aux为长表结构 #

把df_aux中以列名存在的地点（A/B/C/D）转为行数据，方便后续匹配：

df_aux_long = df_aux.melt(
    id_vars=['Initial_Date'],  # 保留时段起始时间即可（因为是1小时粒度，Final_Date可推导）
    var_name='Location',       # 原列名转为地点字段
    value_name='People_Count'  # 原列值转为行人数字段
)

3. 计算df_main的事件前一小时时段 #

事件发生前一小时的时段，对应df_aux的整点起始时间，所以先给df_main生成匹配用的时段标记：

# 计算事件前一小时的起始时间，并向下取整到整点（和df_aux的Initial_Date对齐）
df_main['Prev_Hour_Initial'] = (df_main['Event_Date'] - pd.Timedelta(hours=1)).dt.floor('H')

4. 匹配合并数据 #

通过地点+时段起始时间两个条件合并，自动填充无匹配的NaN：

# 左连接保留df_main所有行，匹配不到的行人数字段自动为NaN
df_main = df_main.merge(
    df_aux_long,
    left_on=['Prev_Hour_Initial', 'Description'],
    right_on=['Initial_Date', 'Location'],
    how='left'
)

# 清理冗余列，重命名目标字段
df_main = df_main.rename(columns={'People_Count': 'People_prev_hour'})
df_main.drop(columns=['Prev_Hour_Initial', 'Initial_Date', 'Location'], inplace=True)

最终结果 #

处理后的df_main如下：

特殊场景处理 #

如果df_aux的时段不是严格整点到整点（比如有时间偏移），可以用区间匹配替代整点对齐：

# 给df_aux创建时段区间
df_aux['Hour_Interval'] = pd.IntervalIndex.from_arrays(df_aux['Initial_Date'], df_aux['Final_Date'], closed='left')

# 转长表
df_aux_long = df_aux.melt(id_vars=['Hour_Interval'], var_name='Location', value_name='People_Count')

# 匹配事件前一小时的时间点所在区间+地点
df_main['Prev_Hour_Timestamp'] = df_main['Event_Date'] - pd.Timedelta(hours=1)
df_main['People_prev_hour'] = df_main.apply(
    lambda row: df_aux_long[
        (df_aux_long['Location'] == row['Description']) & 
        (row['Prev_Hour_Timestamp'] in df_aux_long['Hour_Interval'])
    ]['People_Count'].values[0] if len(df_aux_long[
        (df_aux_long['Location'] == row['Description']) & 
        (row['Prev_Hour_Timestamp'] in df_aux_long['Hour_Interval'])
    ]) > 0 else pd.NA,
    axis=1
)

注：这种方法适合小数据量，大数据量推荐优先对齐整点再合并，效率更高。

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