如何跨DataFrame按日期范围统计日期数量与平均时间？

问题背景 #

现有两个DataFrame：
df_1：

Id         dates              time(sec)_1           time(sec)_2
1        02/02/2022             15                     20
1        04/02/2022             20                     30
1        03/02/2022             30                     40
1        06/02/2022             50                     40
2        10/02/2022             10                     10
2        11/02/2022             15                     20

df_2：

Id          min_date          action_date         
1          02/02/2022          04/02/2022 
2          06/02/2022          10/02/2022     

需求：为df_2新增三列：

• count_of_dates：对应Id下df_1中日期落在min_date与action_date之间的记录数
• avg_time_1：符合条件的time(sec)_1平均值（保留两位小数）
• avg_time_2：符合条件的time(sec)_2平均值

预期结果：

Id          min_date          action_date        count_of_dates     avg_time_1        avg_time_2
1          02/02/2022          04/02/2022              3               21.67              30
2          06/02/2022          10/02/2022              1               10                 10

核心解决思路 #

1. 优先转换日期格式 #

原始日期为字符串类型，直接比较会出现逻辑错误（比如字符串按字符顺序对比，而非实际日期先后），必须先转为datetime类型：

import pandas as pd

# 转换df_1日期列，格式为日/月/年
df_1['dates'] = pd.to_datetime(df_1['dates'], format='%d/%m/%Y')
# 转换df_2的日期列
df_2['min_date'] = pd.to_datetime(df_2['min_date'], format='%d/%m/%Y')
df_2['action_date'] = pd.to_datetime(df_2['action_date'], format='%d/%m/%Y')

2. 方案一：合并后分组聚合（高效向量化） #

通过merge关联两个DataFrame，筛选日期区间内的记录，再分组计算统计值，适合大数据量场景：

# 按Id合并两个DataFrame
merged_df = pd.merge(df_1, df_2, on='Id')

# 筛选日期在min_date和action_date之间（包含两端）的记录
filtered_df = merged_df[(merged_df['dates'] >= merged_df['min_date']) & (merged_df['dates'] <= merged_df['action_date'])]

# 分组聚合计算所需指标
result_df = filtered_df.groupby(['Id', 'min_date', 'action_date']).agg(
    count_of_dates=('dates', 'count'),
    avg_time_1=('time(sec)_1', lambda x: round(x.mean(), 2)),
    avg_time_2=('time(sec)_2', 'mean')
).reset_index()

# 查看结果
print(result_df)

3. 方案二：逐行应用函数（直观易理解） #

对df_2的每一行，筛选对应Id下符合日期条件的df_1记录，计算统计值，适合小数据量或逻辑复杂场景：

def compute_stats(row):
    # 筛选当前Id的日期区间内的记录
    mask = (df_1['Id'] == row['Id']) & (df_1['dates'] >= row['min_date']) & (df_1['dates'] <= row['action_date'])
    target_data = df_1[mask]
    # 返回统计结果
    return pd.Series({
        'count_of_dates': len(target_data),
        'avg_time_1': round(target_data['time(sec)_1'].mean(), 2),
        'avg_time_2': target_data['time(sec)_2'].mean()
    })

# 应用函数到df_2每一行，合并结果
result_df = df_2.join(df_2.apply(compute_stats, axis=1))

# 查看结果
print(result_df)

关键说明 #

• 日期格式转换时必须指定format='%d/%m/%Y'，避免pandas自动识别错误（比如把日/月/年当成月/日/年）
• 日期区间判断使用>=和<=，包含min_date和action_date本身，若需要排除端点可改为>和<
• 平均值保留两位小数用round()函数，可根据需求调整小数位数

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