如何在Pandas中基于多列/分组实现merge_asof式时间序列合并

一、Pandas 向量化方案：GroupBy + Merge_asof #

直接按lookup_id分组后对每组应用merge_asof，这是比手动循环更高效的向量化实现，利用groupby的内部优化减少重复计算：

步骤说明 #

1. 先确保两个DataFrame都按lookup_id和date排序（merge_asof要求键列必须有序）
2. 对table_a按lookup_id分组，每组和table_b中对应lookup_id的子集执行merge_asof
3. 合并所有分组结果得到最终表

基础代码示例 #

import pandas as pd

# 先对两个表按lookup_id和date排序
table_a_sorted = table_a.sort_values(['lookup_id', 'date'])
table_b_sorted = table_b.sort_values(['lookup_id', 'date'])

# 定义分组内的merge_asof逻辑
def merge_asof_group(group):
    lookup_val = group['lookup_id'].iloc[0]
    # 筛选table_b中当前lookup_id的子集
    b_subset = table_b_sorted[table_b_sorted['lookup_id'] == lookup_val]
    # 执行merge_asof，默认匹配<=当前date的最近记录，可通过direction调整
    return pd.merge_asof(group, b_subset, on='date', by='lookup_id')

# 应用分组逻辑并合并结果
table_joined = table_a_sorted.groupby('lookup_id', group_keys=False).apply(merge_asof_group)

性能优化点 #

提前将table_b按lookup_id分组并存储为字典，避免每次分组都重复筛选，能大幅减少耗时：

# 预先生成lookup_id到对应行的字典
b_groups = {k: v for k, v in table_b_sorted.groupby('lookup_id')}

def merge_asof_group_optimized(group):
    lookup_val = group['lookup_id'].iloc[0]
    # 直接从字典取对应子集，无则返回空表
    b_subset = b_groups.get(lookup_val, pd.DataFrame())
    return pd.merge_asof(group, b_subset, on='date', by='lookup_id')

table_joined = table_a_sorted.groupby('lookup_id', group_keys=False).apply(merge_asof_group_optimized)

通过direction参数控制匹配规则：

• direction='backward'：默认，匹配小于等于目标date的最近记录
• direction='forward'：匹配大于等于目标date的最近记录
• direction='nearest'：匹配距离目标date最近的记录（无论大小）

二、PostgreSQL 解决方案 #

如果数据量达到千万级以上，Pandas内存压力会显著增大，此时用PostgreSQL的LATERAL JOIN或窗口函数实现更高效：

方案1：LATERAL JOIN 实现类似XLOOKUP的匹配 #

为table_a的每一行，找到table_b中同lookup_id且date<=当前行date的最新记录：

SELECT a.*, b.*
FROM table_a a
LEFT JOIN LATERAL (
    SELECT *
    FROM table_b b
    WHERE b.lookup_id = a.lookup_id
      AND b.date <= a.date
    ORDER BY b.date DESC
    LIMIT 1
) b ON true;

方案2：窗口函数实现灵活匹配 #

如果需要匹配大于等于目标date的首个记录，可结合窗口函数筛选：

WITH ranked_b AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY lookup_id ORDER BY date) AS rn
    FROM table_b
)
SELECT a.*, b.*
FROM table_a a
LEFT JOIN ranked_b b
    ON b.lookup_id = a.lookup_id
    AND b.date >= a.date
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.date) = 1;

（注：QUALIFY是PostgreSQL 13+支持的语法，用于直接筛选窗口函数结果）

优势 #

• 为table_b创建(lookup_id, date)复合索引后，查询速度会大幅提升
• 无需加载全量数据到内存，适合超大数据量的离线批量处理

三、方案选择建议 #

• 数据量中等（百万级以内）：优先用Pandas的GroupBy+Merge_asof方案，代码简洁且效率足够
• 数据量极大（千万级以上）或需定期批量处理：改用PostgreSQL方案，利用数据库的索引和并行查询能力

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