基于两个DataFrames计算季度末前12个月的交易聚合数

核心思路 #

• 先统一日期格式并排序，为高效匹配做准备
• 利用pandas的merge_asof实现精准的日期范围匹配，结合分组滚动窗口完成计数，避免低效的逐行循环

具体实现步骤 #

1. 数据类型预处理 #

确保两个DataFrame的日期列都是datetime类型，避免后续匹配出错：

import pandas as pd

# 转换日期列至datetime类型
df1['Q End date'] = pd.to_datetime(df1['Q End date'])
df2['Sell Date'] = pd.to_datetime(df2['Sell Date'])

2. 高效统计交易数量 #

针对大数据量场景，采用以下高效方案：

# 按销售代表+日期排序，为merge_asof和窗口计算提速
df1_sorted = df1.sort_values(['Rep', 'Q End date'])
df2_sorted = df2.sort_values(['Rep', 'Sell Date'])

# 按销售代表匹配，仅保留交易日期早于等于季度末日期的记录
merged = pd.merge_asof(
    df1_sorted,
    df2_sorted,
    by='Rep',
    left_on='Q End date',
    right_on='Sell Date',
    direction='backward'
)

# 按销售代表分组，滚动统计过去12个月内的交易数
merged['12m_transactions'] = merged.groupby('Rep').apply(
    lambda group: group['Sell Date'].rolling(
        window=pd.DateOffset(months=12),
        on='Q End date'
    ).count()
).reset_index(level=0, drop=True)

# 将统计结果合并回原DataFrame，保留原始顺序
df1 = df1.merge(
    merged[['Rep', 'Q End date', '12m_transactions']],
    on=['Rep', 'Q End date'],
    how='left'
)

# 无交易的记录填充0，并转为整数类型
df1['12m_transactions'] = df1['12m_transactions'].fillna(0).astype(int)

3. 性能优化技巧 #

• 若数据量超出单进程处理能力，可使用Dask进行并行分块处理
• 为交易表设置复合索引（Rep + Sell Date），进一步提升分组和匹配效率：

df2_sorted = df2_sorted.set_index(['Rep', 'Sell Date']).sort_index()

结果说明 #

处理完成后，原DataFrame（df1）会新增一列12m_transactions，该列值对应每个销售代表在对应季度末日期前12个月内的交易总数，无交易记录时显示0。

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