Pandas按ID左连接两表 统计A表日期小于B表的分组记录数

需求说明 #

基于ID字段对table_A和table_B做左关联，为table_B每一条记录统计：相同ID下，table_A中Date字段值小于当前table_B记录Date值的总记录条数。
示例逻辑对应：

• ID=0在B表的记录日期为2022-05-04，A表同ID下有2条日期早于该值的记录，计数为2
• ID=2在B表无对应A表记录，计数为0

实现方案 #

方案1：merge_asof时序关联（大数据量优先，性能最优） #

pandas.merge_asof是官方针对有序时序数据设计的关联接口，天然支持匹配小于当前键值的关联项，不需要生成笛卡尔积，内存占用低、运行速度快。

import pandas as pd
import datetime

# 测试数据构造
table_A = pd.DataFrame({'ID': [0,1,0,1], 
        'Date':[datetime.date(2022,3,1), datetime.date(2022,3,2), datetime.date(2022,5,2), datetime.date(2022,4,2)]})
table_B = pd.DataFrame({'ID': [0,1,1,2], 
        'Date':[datetime.date(2022,5,4), datetime.date(2022,3,20), datetime.date(2022,5,2), datetime.date(2022,2,22)]})

# 预处理：两表按Date升序排序（merge_asof要求关联的时序键必须有序），给A表加计数标记
table_a_sorted = table_A.sort_values("Date").assign(record_flag=1)
table_b_sorted = table_B.sort_values("Date")

# 按ID分组做时序左连接，仅匹配A表中Date严格小于B表当前行Date的记录
connected = pd.merge_asof(
    table_b_sorted,
    table_a_sorted,
    on="Date",
    by="ID",
    direction="backward",
    strict=True
)

# 空值填0，聚合得到最终计数结果
table_C = connected.fillna({"record_flag": 0})\
    .groupby(["ID", "Date"], as_index=False)["record_flag"]\
    .sum()\
    .rename(columns={"record_flag": "number_of_records"})\
    .sort_values(["ID", "Date"])\
    .reset_index(drop=True)

关键参数说明：strict=True表示仅统计A表日期严格小于B表日期的记录，如果业务要求包含日期相等的记录，将该参数设为False即可。

运行后输出结果和预期完全一致。

方案2：全量关联+条件过滤（逻辑直观，小数据量适用） #

如果数据量不大，可以先按ID做全量左连接，再过滤符合日期大小条件的记录后分组计数，逻辑简单易读。

# 按ID做全量左连接，给两个表的Date字段加后缀区分
full_merged = table_B.merge(table_A, on="ID", how="left", suffixes=("_B", "_A"))

# 过滤出A表日期小于B表日期的记录，分组计数
count_result = full_merged[full_merged["Date_A"] < full_merged["Date_B"]]\
    .groupby(["ID", "Date_B"], as_index=False)\
    .size()\
    .rename(columns={"Date_B": "Date", "size": "number_of_records"})

# 回连原B表，无匹配记录的计数填0
table_C = table_B.merge(count_result, on=["ID", "Date"], how="left")\
    .fillna({"number_of_records": 0})

注意事项 #

• 执行前请确认两表的Date字段为日期类型，如果是字符串格式，先用pd.to_datetime(表名['Date'])做类型转换，否则日期大小比较会按字符串字典序执行，结果出错。
• 单表数据量超过10万行时优先选方案1，方案2生成的笛卡尔积会占用大量内存，运行速度慢。

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