如何基于第一个DataFrame日期计算另一DataFrame的分组前置平均值

需求说明 #

现有两个DataFrame：
第一个DataFrame（df1）仅包含日期和分组信息：

Date         Group  
06/11/2023     A   
05/11/2023     B   
04/11/2023     A   
03/11/2023     A   
02/11/2023     B        

第二个DataFrame（df2）包含日期、分组和对应数值：

Date         Group    Value
06/11/2023     A        5
05/11/2023     B        8
04/11/2023     A       12
03/11/2023     A        4
02/11/2023     B        9
02/11/2023     B        0
01/11/2023     A        6
01/11/2023     B       10

需要在df1中新增AvgGroupValue_PriorDate列，计算对应分组下当前日期之前所有Value的平均值，最终结果如下：

Date         Group   AvgGroupValue_PriorDate
06/11/2023     A              7.33
05/11/2023     B              6.33
04/11/2023     A               5
03/11/2023     A               6
02/11/2023     B               10  

实现代码 #

import pandas as pd

# 初始化两个DataFrame
df1 = pd.DataFrame({
    'Date': ['06/11/2023', '05/11/2023', '04/11/2023', '03/11/2023', '02/11/2023'],
    'Group': ['A', 'B', 'A', 'A', 'B']
})

df2 = pd.DataFrame({
    'Date': ['06/11/2023', '05/11/2023', '04/11/2023', '03/11/2023', '02/11/2023', '02/11/2023', '01/11/2023', '01/11/2023'],
    'Group': ['A', 'B', 'A', 'A', 'B', 'B', 'A', 'B'],
    'Value': [5, 8, 12, 4, 9, 0, 6, 10]
})

# 转换日期格式为datetime，确保日期比较逻辑正确
df1['Date'] = pd.to_datetime(df1['Date'], format='%d/%m/%Y')
df2['Date'] = pd.to_datetime(df2['Date'], format='%d/%m/%Y')

# 定义计算历史平均值的函数
def get_prior_avg(row):
    # 筛选同分组且日期早于当前行日期的所有记录
    valid_records = df2[(df2['Group'] == row['Group']) & (df2['Date'] < row['Date'])]
    # 计算均值并保留两位小数，无符合条件记录时返回None（或按需调整）
    return round(valid_records['Value'].mean(), 2) if not valid_records.empty else None

# 应用函数生成新列
df1['AvgGroupValue_PriorDate'] = df1.apply(get_prior_avg, axis=1)

# 转换日期回原字符串格式，匹配示例输出
df1['Date'] = df1['Date'].dt.strftime('%d/%m/%Y')

# 打印结果
print(df1)

关键逻辑说明 #

• 日期格式转换：必须将字符串类型的日期转为datetime类型，否则字符串比较会出现逻辑错误（比如跨月份的日期排序错误）。
• 逐行筛选计算：通过apply逐行处理df1的每条记录，针对每个(Group, Date)组合，在df2中筛选出同分组且日期更早的所有Value，计算平均值并保留两位小数。
• 结果格式匹配：最后将日期转回原字符串格式，确保输出和示例一致。

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