基于条件合并两个DataFrame：Python实现按ID财年匹配最新值

我有两个数据表（A和B），需要实现以下逻辑：

• 对于表A中的每一行，先判断该行的data_date是否是对应ID和fiscal_year下的最新日期
• 如果是最新日期，匹配表B中同一ID和fiscal_year下最新data_date对应的Value
• 如果不是最新日期，或者表B中无对应ID-fiscal_year的记录，Value设为NA

以下是示例数据和预期结果：

表A #

表B #

预期结果表 #

使用pandas库完成逻辑，步骤如下：

import pandas as pd

# ---------------------- 1. 构造示例数据（实际使用时可替换为读取文件） ----------------------
df_a = pd.DataFrame({
    'ID': ['A', 'A', 'A', 'B', 'B', 'B', 'C'],
    'data_date': ['2016-03-31', '2016-03-31', '2018-09-30', '2017-06-30', '2017-09-30', '2018-06-30', '2013-03-31'],
    'fiscal_year': [2016, 2016, 2018, 2017, 2017, 2018, 2013]
})

df_b = pd.DataFrame({
    'ID': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C', 'C'],
    'data_date': ['2015-12-31', '2016-12-31', '2018-03-30', '2015-12-31', '2016-12-31', '2017-12-31', '2013-03-30', '2013-12-31', '2014-12-31', '2015-12-31'],
    'Value': [1, 4, 85, 7, 14, 12, 45, 9, 64, 25]
})

# ---------------------- 2. 转换日期格式为datetime类型 ----------------------
df_a['data_date'] = pd.to_datetime(df_a['data_date'])
df_b['data_date'] = pd.to_datetime(df_b['data_date'])

# ---------------------- 3. 处理表B：获取每个ID-财年的最新Value ----------------------
# 为表B添加fiscal_year列（根据data_date的年份，若财年规则不同可调整）
df_b['fiscal_year'] = df_b['data_date'].dt.year

# 按ID和fiscal_year分组，筛选每组最新data_date的记录
df_b_latest = df_b.sort_values('data_date').groupby(['ID', 'fiscal_year']).last().reset_index()
# 只保留需要的列
df_b_latest = df_b_latest[['ID', 'fiscal_year', 'Value']]

# ---------------------- 4. 处理表A：标记每行是否为该ID-财年的最新日期 ----------------------
# 按ID和fiscal_year分组，计算每组的最新data_date
df_a_max_date = df_a.groupby(['ID', 'fiscal_year'])['data_date'].max().reset_index(name='max_date_a')

# 合并回表A，标记当前行是否是最新日期
df_a = df_a.merge(df_a_max_date, on=['ID', 'fiscal_year'], how='left')
df_a['is_latest'] = df_a['data_date'] == df_a['max_date_a']

# ---------------------- 5. 合并表A和表B的最新数据，生成结果 ----------------------
result = df_a.merge(df_b_latest, on=['ID', 'fiscal_year'], how='left')

# 非最新日期的行，Value设为NA
result.loc[~result['is_latest'], 'Value'] = pd.NA

# 整理结果列顺序，删除中间列
result = result[['ID', 'data_date', 'fiscal_year', 'Value']]

# 格式化日期显示（可选）
result['data_date'] = result['data_date'].dt.strftime('%Y-%m-%d')

print(result)

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