求助：基于Pandas实现拍卖销售数据中卖家销售年份前5年（不含当年）的滚动汇总统计

我明白你现在的核心需求：要为每个卖家计算对应销售年份前5年（不含当年）的业绩汇总，同时替代效率偏低的嵌套for循环，用更原生的Pandas方法实现，方便后续ETL流程复用。下面是具体的实现步骤和封装好的函数：

核心思路 #

1. 生成所有需要的**(卖家, 参考年份)**组合，确保不会漏掉任何需要计算的场景；
2. 通过左连接关联原数据，筛选出参考年份前5年的历史记录；
3. 用Pandas分组聚合一次性计算所有基础指标，再衍生出剩余统计量；
4. 处理空值并格式化结果，匹配你期望的输出格式。

完整代码实现 #

1. 导入依赖并定义测试数据 #

import pandas as pd
import numpy as np

# 测试用DataFrame
data = {
    "sale_year": [2019, 2019, 2018, 2017, 2017, 2017, 2017, 2017, 2017, 2016, 2015, 2015, 2015, 2015, 2009],
    "seller": ["bob", "alice", "bob", "alice", "alice", "alice", "alice", "bob", "alice", "alice", "bob", "alice", "alice", "alice", "alice"],
    "item_id": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15],
    "mean_estimate": [20000, 35000, 15000, 60000, 50000, 60000, 40000, 20000, 200000, 100000, 75000, 100000, 10000, 100000, 150000],
    "sale_price": [11000, 39000, 17000, 120000, 80000, 120000, 120000, 27000, 175000, 150000, 100000, 150000, 15000, 150000, 150000],
    "deviation": [-9000, 4000, 2000, 60000, 30000, 60000, 80000, 7000, -25000, 50000, 25000, 50000, 5000, 50000, 0],
    "status": ["sold", "sold", "not sold", "sold", "sold", "sold", "sold", "sold", "sold", "sold", "sold", "sold", "sold", "sold", "sold"]
}
test = pd.DataFrame(data)

2. 封装成可复用的函数 #

def calculate_historical_metrics(df, ref_years=None):
    """
    计算每个卖家对应参考年份前5年的业绩汇总指标
    
    参数:
        df: 包含销售数据的DataFrame，需包含列：seller, sale_year, mean_estimate, deviation, status
        ref_years: 可选，指定需要计算的参考年份列表，若为None则使用df中的所有sale_year
    
    返回:
        包含汇总指标的DataFrame，列包括：ref_year, seller, avg_est, avg_dev, sd_est, listings, sales, prop_sold, ln_sales
    """
    # 获取唯一卖家列表
    unique_sellers = df['seller'].unique()
    
    # 确定需要计算的参考年份
    if ref_years is None:
        ref_years = df['sale_year'].unique()
    else:
        ref_years = np.array(ref_years)
    
    # 生成所有(卖家, 参考年份)的组合，确保覆盖所有场景
    seller_ref_year = pd.MultiIndex.from_product(
        [unique_sellers, ref_years], 
        names=['seller', 'ref_year']
    ).to_frame(index=False)
    
    # 左连接原数据，筛选出参考年份前5年的历史记录
    merged = seller_ref_year.merge(df, on='seller', how='left')
    merged = merged[
        (merged['sale_year'] >= merged['ref_year'] - 5) & 
        (merged['sale_year'] < merged['ref_year'])
    ]
    
    # 分组聚合计算基础指标
    agg_funcs = {
        'mean_estimate': ['mean', 'std', 'count'],
        'deviation': ['mean', 'std'],
        'status': lambda x: sum(x == 'sold')  # 统计已售出数量
    }
    result = merged.groupby(['ref_year', 'seller']).agg(agg_funcs).reset_index()
    
    # 重命名列，简化后续处理
    result.columns = [
        'ref_year', 'seller', 'avg_est', 'sd_est', 'listings', 
        'avg_dev', 'sd_dev', 'sales'
    ]
    
    # 计算衍生指标
    result['prop_sold'] = result['sales'] / result['listings']
    result['ln_sales'] = np.log(1 + result['sales'])
    
    # 处理空值：无历史数据时，相关指标设为NaN，ln_sales设为ln(1)=0
    mask = result['listings'] == 0
    result.loc[mask, ['avg_est', 'sd_est', 'avg_dev', 'sd_dev', 'prop_sold']] = np.nan
    result['ln_sales'] = result['ln_sales'].fillna(np.log(1))
    
    # 调整列顺序，匹配期望输出
    result = result[
        ['ref_year', 'seller', 'avg_est', 'avg_dev', 'sd_est', 
         'listings', 'sales', 'prop_sold', 'ln_sales']
    ]
    
    # 格式化数值，提升可读性（可选）
    result = result.round({
        'avg_est': 0,
        'avg_dev': 1,
        'sd_est': 1,
        'prop_sold': 2
    })
    
    return result

3. 使用函数生成结果 #

# 计算包含示例中2010年在内的所有参考年份指标
metrics = calculate_historical_metrics(
    test, 
    ref_years=np.concatenate([test['sale_year'].unique(), [2010]])
)

# 按参考年份降序、卖家名称升序排序输出
print(metrics.sort_values(['ref_year', 'seller'], ascending=[False, True]))

优势对比 #

• 效率更高：完全使用Pandas向量化操作替代嵌套for循环，在大数据量（比如单卖家5000+记录）场景下，性能提升显著；
• 可复用性强：封装成函数后，只需传入DataFrame和目标年份即可生成结果，完美适配ETL流程；
• 可读性好：代码逻辑清晰，每一步操作都有明确的目标，便于后续维护和扩展。

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