如何基于日期条件快速将pandas DataFrame的值映射到另一个DataFrame

针对你的百万级df2和万级df1的匹配需求，绝对要避免循环、apply这类逐行操作，以下是几种性能优异的矢量化/批量处理方案：

预处理：统一日期格式 #

首先必须将所有日期列转换为datetime类型，否则无法正确进行区间比较：

import pandas as pd

# 转换df1的日期列
df1['BeginDate'] = pd.to_datetime(df1['BeginDate'], format='%m/%d/%y')
df1['EndDate'] = pd.to_datetime(df1['EndDate'], format='%m/%d/%y')
# 转换df2的日期列
df2['Date'] = pd.to_datetime(df2['Date'], format='%m/%d/%y')

方案1：使用merge_asof（最推荐，矢量化高效） #

merge_asof是pandas专门为按键匹配+有序区间匹配设计的API，底层是矢量化实现，性能远超循环，适合你的场景。

前提条件： #

• df1中每个ID的日期区间连续且无重叠（如示例中ID1的两个区间无缝衔接）
• 需要对两个DataFrame按ID和日期列排序

代码实现： #

# 对df1按ID和BeginDate排序
df1_sorted = df1.sort_values(by=['ID', 'BeginDate'])
# 对df2按ID和Date排序
df2_sorted = df2.sort_values(by=['ID', 'Date'])

# 执行asof合并：匹配同ID下，Date >= BeginDate且最接近的行，再过滤Date <= EndDate的情况
merged = pd.merge_asof(
    df2_sorted,
    df1_sorted[['ID', 'BeginDate', 'EndDate', 'Value']],
    on='Date',
    by='ID',
    direction='backward'  # 找Date之前最近的BeginDate
)

# 过滤掉Date超出EndDate的无效匹配
merged = merged[merged['Date'] <= merged['EndDate']]

# 如果需要恢复原df2的顺序，可以保留原索引后重置
df2 = df2.merge(merged[['ID', 'Date', 'Value']], on=['ID', 'Date'], how='left')

性能优势： #

时间复杂度接近O(n log n)（主要来自排序），处理百万级数据仅需几秒，远快于循环方案。

方案2：使用IntervalIndex+分组查找 #

如果df1的区间存在重叠，或者你需要更灵活的区间匹配，可以用IntervalIndex结合分组操作：

# 按ID分组，为每个ID创建区间索引与Value的映射
id_intervals = {}
for id_val, group in df1.groupby('ID'):
    # 创建左闭右闭的区间
    intervals = pd.IntervalIndex.from_arrays(group['BeginDate'], group['EndDate'], closed='both')
    id_intervals[id_val] = (intervals, group['Value'].values)

# 按ID分组批量处理，避免逐行apply的低效
df2['Value'] = df2.groupby('ID', group_keys=False).apply(
    lambda g: g['Date'].apply(
        lambda d: id_intervals[g.name][1][id_intervals[g.name][0].get_indexer([d])[0]] 
        if id_intervals[g.name][0].get_indexer([d])[0] != -1 else None
    )
)

注意事项： #

如果区间有重叠，get_indexer会返回第一个匹配的区间索引，若需要多个匹配需调整逻辑。

方案3：用SQL内存数据库处理 #

利用SQL的查询优化器，对大表的区间匹配也有很好的性能，适合复杂匹配场景：

import sqlite3

# 创建内存数据库连接
conn = sqlite3.connect(':memory:')

# 将DataFrame导入数据库
df1.to_sql('df1', conn, index=False)
df2.to_sql('df2', conn, index=False)

# 执行SQL查询：匹配同ID且Date在BeginDate和EndDate之间的记录
query = """
SELECT df2.ID, df2.Date, df1.Value
FROM df2
LEFT JOIN df1 ON df2.ID = df1.ID 
AND df2.Date BETWEEN df1.BeginDate AND df1.EndDate
"""

# 读取结果回DataFrame
result = pd.read_sql(query, conn)
df2 = df2.merge(result, on=['ID', 'Date'], how='left')

# 关闭连接
conn.close()

优势： #

无需手动处理排序，数据库会自动优化查询（比如给ID、日期列建索引），适合逻辑复杂的匹配场景。

方案4：Dask（超大数据内存不足时） #

如果你的数据大到内存无法容纳，可以用Dask分块处理：

import dask.dataframe as dd

# 转为Dask DataFrame
ddf1 = dd.from_pandas(df1, npartitions=4)
ddf2 = dd.from_pandas(df2, npartitions=10)

# 预处理日期列
ddf1['BeginDate'] = dd.to_datetime(ddf1['BeginDate'], format='%m/%d/%y')
ddf1['EndDate'] = dd.to_datetime(ddf1['EndDate'], format='%m/%d/%y')
ddf2['Date'] = dd.to_datetime(ddf2['Date'], format='%m/%d/%y')

# 执行merge_asof（Dask支持该API）
merged = dd.merge_asof(
    ddf2.sort_values(['ID', 'Date']),
    ddf1.sort_values(['ID', 'BeginDate']),
    on='Date',
    by='ID',
    direction='backward'
)

# 过滤无效匹配并计算结果
result = merged[merged['Date'] <= merged['EndDate']].compute()
df2 = df2.merge(result[['ID', 'Date', 'Value']], on=['ID', 'Date'], how='left')

性能对比总结 #

绝对不要使用：iterrows、df.apply逐行处理，这类方法处理百万级数据会耗时几十分钟甚至更久。

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