您的输出不正确（04/11/2023，A，3.33）。该值应为5.00（=[4+6]/2）。

验证码：

df1['Date'] = pd.to_datetime(df1['Date'], format='%d/%m/%Y')
df2['Date'] = pd.to_datetime(df2['Date'], format='%d/%m/%Y')
df2 = df2.sort_values(by=['Date'])

df2['CumulativeSum'] = df2.groupby('Group')['Value'].cumsum()  # cumulative sum of value for each group
df2['CumulativeCount'] = df2.groupby('Group').cumcount()       # cumulative count for each group
result = df1.merge(df2, on=['Date', 'Group'], how='left')
result['AvgGroupValue_PriorDate'] = (result['CumulativeSum'] - result['Value']) / result['CumulativeCount']
result['AvgGroupValue_PriorDate'] = result.groupby('Group')['AvgGroupValue_PriorDate'].fillna(0)
new_df= result[['Date', 'Group', 'AvgGroupValue_PriorDate']]
print(new_df)

字符串

输出：

Date Group  AvgGroupValue_PriorDate
0 2023-11-06     A                 7.333333
1 2023-11-05     B                 6.333333
2 2023-11-04     A                 5.000000
3 2023-11-03     A                 6.000000
4 2023-11-02     B                10.000000
5 2023-11-02     B                 9.500000

型

我认为有两种选择。第一种选择，是循环遍历df1的行并手动计算。如：

import pandas as pd

df1 = pd.DataFrame([
    ["06/11/2023", "A"],   
    ["05/11/2023", "B"],   
    ["04/11/2023", "A"],  
    ["03/11/2023", "A"],
    ["02/11/2023", "B"],
], columns=["Date", "Group"])
df1["Date"] = pd.to_datetime(df1["Date"])

df2 = pd.DataFrame([
    ["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]
], columns=["Date", "Group", "Value"])
df2["Date"] = pd.to_datetime(df2["Date"])

# 1st option
def find_average_prior_date(row):
    avg = df2[(df2["Date"] < row["Date"]) & (df2["Group"] == row["Group"])]["Value"].mean()
    row["AvgGroupValue_PriorDate"] = avg
    return row

df1.apply(find_average_prior_date, axis=1)
>>>         Date Group  AvgGroupValue_PriorDate
>>> 0 2023-06-11     A                 7.333333
>>> 1 2023-05-11     B                 6.333333
>>> 2 2023-04-11     A                 5.000000
>>> 3 2023-03-11     A                 6.000000
>>> 4 2023-02-11     B                10.000000

字符串
这是最基本的（但绝对是最慢的）选项，因为有apply循环。第二个选项是交叉连接两个表（仅在组上连接），过滤行，然后计算平均值。

tmp = df1.merge(df2, on='Group', how='left', suffixes=['', '_df2'])
tmp = tmp[tmp['Date_df2'] < tmp['Date']].groupby(['Date', 'Group'])['Value'].mean().to_frame(name='AvgGroupValue_PriorDate').reset_index()
tmp
>>>         Date Group  AvgGroupValue_PriorDate
>>> 0 2023-06-11     A                 7.333333
>>> 1 2023-05-11     B                 6.333333
>>> 2 2023-04-11     A                 5.000000
>>> 3 2023-03-11     A                 6.000000
>>> 4 2023-02-11     B                10.000000

型
这些小 Dataframe 的执行时间没有显著差异。但是您应该在数据和环境中尝试这两种选项。由于第一种较慢，第二种需要更多内存。这取决于您的需求。

%timeit df1.apply(find_average_prior_date, axis=1)
>>> 4.26 ms ± 335 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
tmp = df1.merge(df2, on='Group', how='left', suffixes=['', '_df2'])
tmp[tmp['Date_df2'] < tmp['Date']].groupby(['Date', 'Group'])['Value'].mean().to_frame(name='AvgGroupValue_PriorDate').reset_index()
>>> 3.41 ms ± 456 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

型

一个有效的合并方法是使用janitor的conditional_join来合并值，然后groupby.mean：

# pip install pyjanitor
import janitor

df1['Date'] = pd.to_datetime(df1['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

out = (df1.reset_index()
       .conditional_join(df2,
                         ('Date', 'Date', '>'),
                         ('Group', 'Group', '=='),
                         how='left', right_columns=['Value'])
       .groupby(['index', 'Date', 'Group'], as_index=False).mean()
       .set_index('index').rename_axis(df1.index.name)
      )

字符串
输出量：

Date Group      Value
0 2023-06-11     A   7.333333
1 2023-05-11     B   6.333333
2 2023-04-11     A   5.000000
3 2023-03-11     A   6.000000
4 2023-02-11     B  10.000000

型