Pandas跨数据集逐行匹配条件计算指标均值差新增列

计算规则说明 #

统一按如下规则计算，避免歧义：

• 对每条法规记录，仅筛选同州的Metric数据参与计算
• 法规实施当年的Metric值不纳入前后统计范围
• 前均值 = 同州所有早于法规实施年份的Metric平均值
• 后均值 = 同州所有晚于法规实施年份的Metric平均值
• 最终列值 = 后均值 - 前均值

方法1：逐行apply实现（代码简洁，适合中小数据集） #

直接逐行遍历df2的法规记录，匹配对应州和年份计算差值即可，代码逻辑直观好改：

import pandas as pd

# --------------- 以下是示例数据初始化，你使用时替换成本地的df1、df2即可 ---------------
df1 = pd.DataFrame({
    'Metric': [8,6,4,5,7,20,21,20,34,35,36,22,20,22,24],
    'Year': [1996,1997,1998,1999,2000,2001,2002,2003,1996,1997,1998,1999,2000,2001,2002],
    'State': ['AL']*8 + ['CA']*7
})
df2 = pd.DataFrame({
    'ID': ['ABC123','DEF456','GHI789','JKL012','PQR678','STU901','YZA567'],
    'State': ['AL']*4 + ['CA']*3,
    'Year': [1999,2000,2001,2001,1999,2000,2001]
})
# --------------- 以上是示例数据初始化 ---------------

# 核心差值计算函数
def calc_metric_diff(row):
    target_state = row['State']
    law_year = row['Year']
    # 筛选当前州的所有Metric数据
    state_data = df1[df1['State'] == target_state]
    pre_mean = state_data[state_data['Year'] < law_year]['Metric'].mean()
    post_mean = state_data[state_data['Year'] > law_year]['Metric'].mean()
    return post_mean - pre_mean

# 批量生成diff列
df2['diff'] = df2.apply(calc_metric_diff, axis=1)

运行后得到的df2结果符合预期，比如AL州1999年实施的ABC123法规，前均值为1996-1998年Metric均值(8+6+4)/3=6，后均值为2000-2003年Metric均值(7+20+21+20)/4=17，对应diff值为11。
如果遇到某法规实施前/后无对应Metric数据的场景，返回值为NaN，需要默认填0的话可以把返回值做fillna(0)处理即可。

方法2：分组预计算实现（性能更高，适合大数据量场景） #

如果你的数据集是十万/百万行级别，逐行apply速度会偏慢，可以先按州分组预计算好所有州-年份组合对应的差值，再通过关联合并到df2中，性能提升明显：

# 按州分组处理Metric数据
def gen_year_diff_map(state_df):
    year_diff = {}
    all_years = state_df['Year'].unique()
    for y in all_years:
        pre_val = state_df[state_df['Year'] < y]['Metric'].mean()
        post_val = state_df[state_df['Year'] > y]['Metric'].mean()
        year_diff[y] = post_val - pre_val
    return pd.Series(year_diff, name='diff')

# 生成[州, 年份, 差值]的映射表
year_diff_table = df1.groupby('State').apply(gen_year_diff_map).reset_index()
year_diff_table.columns = ['State', 'Year', 'diff']

# 直接关联到df2，一次性完成所有diff列赋值
df2 = df2.merge(year_diff_table, on=['State', 'Year'], how='left')

原写法的问题说明 #

你之前写的np.where逻辑存在几个硬伤：

• 硬编码了State=='AL'和Year==2000的固定条件，无法批量适配所有州、年份的组合
• 筛选Metric数据时没有加State过滤条件，会把其他州的无关数据混入计算
• 存在语法错误：df1'Year'位置缺少左方括号
• 差值计算顺序写反，原逻辑是前均值减后均值，和需求要求的方向相反

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