Pandas统计df2中符合df1日期间隔及特征条件的记录数

实现步骤 #

1. 首先将两个表的日期字段统一转换为datetime类型，避免字符串比较出现逻辑错误
2. 提前过滤df2中characteristic != 'T'的记录，减少后续无效计算
3. 关联两个表并统计符合区间条件的记录数，两种实现方案如下：

方案1：merge关联统计（适合数据量较小的场景）

import pandas as pd

# 构造样例数据
df1 = pd.DataFrame( { 
    "ID" : ["11", "11", "11", "11"] , 
    "updated_date" : ["2019/04/03", "2019/05/02", "2019/05/20", "2019/03/03"],
    "other_date" : ["2019/04/09", "2019/05/14", "2019/06/05", "2019/03/07"] 
} )

df2 = pd.DataFrame( { 
    "ID" : ["11", "11", "11", "11"] , 
    "new_date" : ["2019/04/02", "2019/05/03", "2019/05/13", "2019/03/04"],
    "characteristic" : ["T", "T", "T", "P"] 
} )

# 1. 日期格式转换
df1['updated_date'] = pd.to_datetime(df1['updated_date'])
df1['other_date'] = pd.to_datetime(df1['other_date'])
df2['new_date'] = pd.to_datetime(df2['new_date'])

# 2. 过滤df2只保留characteristic=T的记录
df2_t = df2[df2['characteristic'] == 'T'].copy()

# 3. 按ID关联两个表，保留df1所有原始行
merged = df1.reset_index().merge(df2_t, on='ID', how='left')

# 4. 筛选符合日期区间的记录
valid_mask = (merged['new_date'] > merged['updated_date']) & (merged['new_date'] < merged['other_date'])
valid_records = merged[valid_mask]

# 5. 按df1原始行分组计数，合并回原表补0
counts = valid_records.groupby('index').size().rename('count_ts')
result = df1.join(counts).fillna({'count_ts': 0}).astype({'count_ts': int})

print(result)

方案2：分组映射统计（适合数据量大、ID取值多的场景）

避免大表笛卡尔积join导致的内存占用过高问题：

# 接上面的预处理步骤（日期转换、df2过滤）
# 按ID把符合条件的new_date存成字典
df2_id_map = df2[df2['characteristic'] == 'T'].groupby('ID')['new_date'].apply(list).to_dict()

# 逐行统计匹配的数量
def count_valid(row):
    if row['ID'] not in df2_id_map:
        return 0
    return sum(1 for d in df2_id_map[row['ID']] if row['updated_date'] < d < row['other_date'])

df1['count_ts'] = df1.apply(count_valid, axis=1)
print(df1)

两种方案输出结果均和需求一致：

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