生成含happen与valid日期组合及分组求和的DataFrame

我有一个DataFrame，每行代表一个事件，包含4列：

happen start_date   end_date  number
0 2015-01-01 2015-01-01 2015-01-03   100.0
1 2015-01-01 2015-01-01 2015-01-01    20.0
2 2015-01-01 2015-01-02 2015-01-02    50.0
3 2015-01-02 2015-01-02 2015-01-02    40.0
4 2015-01-02 2015-01-02 2015-01-03    50.0

其中happen为事件发生日期，start_date和end_date为事件有效期，number为可求和变量。

需求是生成一个新DataFrame，每行包含happen日期与valid日期的组合（要求valid >= happen），以及对应的number列求和结果——即所有happen等于当前组合的happen，且valid落在该事件start_date和end_date之间的行的number总和。

尝试过两种方法但都因效率极低无法完成：

1. 双重循环遍历所有日期组合：

startdate = pd.to_datetime('01/06/2014', format='%d/%m/%Y') # 最小可能的happen日期
enddate = pd.to_datetime('31/12/2021', format='%d/%m/%Y') # 最大可能的happen和有效期日期

df_day = pd.DataFrame()
for dt1 in pd.date_range(start=startdate, end=enddate):
    for dt2 in pd.date_range(start=dt1, end=enddate):
        num_sum = df[(df['happen'] == dt1)&(df['start_date'] <= dt2)&
                   (df['end_date'] >= dt2)]['number'].sum()
        row = {'happen':dt1,'valid':dt2,'number':num_sum}
        df_day = df_day.append(row,ignore_index = True)

2. 先生成所有日期组合再用lambda逐行计算：

from itertools import product

dt1 = pd.date_range(start=startdate, end=enddate).tolist()
df_day = pd.DataFrame()

for i in dt1:
    dt_acc1 = [i]
    dt2 = pd.date_range(start=i, end=enddate).tolist()
    df_comb = pd.DataFrame(list(product(dt_acc1, dt2)), columns=['happen', 'valid'])
    df_day = df_day.append(df_comb, ignore_index=True)

df_day['number'] = 0
def append_num(happen,valid):
    return df[(df['happen'] == happen)&(df['start_date'] <= valid)&
              (df['end_date'] >= valid)]['number'].sum()

df_day['number'] = df_day.apply(lambda x: append_num(x['happen'],x['valid']), axis=1)

预期输出示例：

happen      valid  number
0 2015-01-01 2015-01-01   120.0
1 2015-01-01 2015-01-02   150.0
2 2015-01-01 2015-01-03   100.0
3 2015-01-02 2015-01-02    90.0
4 2015-01-02 2015-01-03    50.0
5 2015-01-03 2015-01-03     0.0

核心思路是避免逐行循环，利用pandas的向量化操作和numpy广播来批量计算，大幅提升效率。

方法一：事件扩展+分组求和（易理解，适合事件数量不多的场景） #

1. 预处理原数据，确保日期列是datetime类型

import pandas as pd

# 转换日期列格式
df['happen'] = pd.to_datetime(df['happen'])
df['start_date'] = pd.to_datetime(df['start_date'])
df['end_date'] = pd.to_datetime(df['end_date'])

2. 生成所有需要的happen和valid日期组合（仅保留valid >= happen的情况）

startdate = pd.to_datetime('01/06/2014', format='%d/%m/%Y')
enddate = pd.to_datetime('31/12/2021', format='%d/%m/%Y')

# 生成所有happen日期
happen_dates = pd.date_range(start=startdate, end=enddate, name='happen')
# 生成所有valid日期
valid_dates = pd.date_range(start=startdate, end=enddate, name='valid')

# 生成笛卡尔积并过滤符合条件的组合
df_comb = pd.merge(happen_dates.to_frame(), valid_dates.to_frame(), how='cross')
df_comb = df_comb[df_comb['valid'] >= df_comb['happen']].reset_index(drop=True)

3. 扩展每个事件对应的有效(happen, valid)组合，再分组求和

events_expanded = []
for _, row in df.iterrows():
    # 生成该事件覆盖的valid日期范围
    valid_range = pd.date_range(start=row['start_date'], end=row['end_date'])
    # 生成该事件对应的所有(happen, valid)组合
    event_df = pd.DataFrame({
        'happen': row['happen'],
        'valid': valid_range,
        'number': row['number']
    })
    events_expanded.append(event_df)

# 合并所有事件的扩展数据
events_expanded = pd.concat(events_expanded, ignore_index=True)

# 左连接组合表与扩展表，按(happen, valid)求和，空值填充0
result = df_comb.merge(events_expanded, on=['happen', 'valid'], how='left').fillna(0)
result = result.groupby(['happen', 'valid'], as_index=False)['number'].sum()

方法二：numpy广播（效率最高，适合大数据量场景） #

完全避免循环，用矩阵运算批量计算每个组合的求和结果：

import pandas as pd
import numpy as np

# 预处理日期列
df['happen'] = pd.to_datetime(df['happen'])
df['start_date'] = pd.to_datetime(df['start_date'])
df['end_date'] = pd.to_datetime(df['end_date'])

startdate = pd.to_datetime('01/06/2014', format='%d/%m/%Y')
enddate = pd.to_datetime('31/12/2021', format='%d/%m/%Y')

# 生成符合条件的(happen, valid)组合
happen_dates = pd.date_range(start=startdate, end=enddate, name='happen')
valid_dates = pd.date_range(start=startdate, end=enddate, name='valid')
df_comb = pd.merge(happen_dates.to_frame(), valid_dates.to_frame(), how='cross')
df_comb = df_comb[df_comb['valid'] >= df_comb['happen']].reset_index(drop=True)

# 将日期转换为timestamp数值，方便矩阵运算
happen_comb = df_comb['happen'].values.astype(np.int64)
valid_comb = df_comb['valid'].values.astype(np.int64)

happen_event = df['happen'].values.astype(np.int64)[:, np.newaxis]
start_event = df['start_date'].values.astype(np.int64)[:, np.newaxis]
end_event = df['end_date'].values.astype(np.int64)[:, np.newaxis]
number_event = df['number'].values[:, np.newaxis]

# 构建条件矩阵：匹配happen，且valid在事件有效期内
mask = (happen_event == happen_comb) & (valid_comb >= start_event) & (valid_comb <= end_event)

# 计算每个组合的number总和
df_comb['number'] = (mask * number_event).sum(axis=0)

两种方法都能快速得到符合预期的结果，其中方法二的效率远超原有的循环方案，适合处理大日期范围的场景。

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