如何基于ID和月度末次日期合并两个DataFrame？

需求分析 #

需要将每日数据DataFrame（day_df）与月末值DataFrame（eom_df）合并，核心逻辑是：每个ID对应月份的最后一条每日记录，填充该月的月末EoM值，其余记录留空。

实现步骤（基于Pandas） #

1. 构造示例数据（可替换为你的实际数据）

import pandas as pd

# 月末值DataFrame
eom_df = pd.DataFrame({
    'ID': ['AAA', 'BBB', 'AAA', 'BBB'],
    'Date': ['2021-06-30', '2021-06-30', '2021-07-31', '2021-07-31'],
    'EoM_Val': [1417744, 3946750, 2792182, 81073822]
})

# 每日数据DataFrame
day_df = pd.DataFrame({
    'ID': ['AAA', 'AAA', 'AAA', 'BBB', 'BBB', 'BBB', 'AAA', 'AAA', 'BBB', 'BBB'],
    'Date': ['2021-06-05', '2021-06-12', '2021-06-21', '2021-06-06', '2021-06-18', '2021-06-27', '2021-07-08', '2021-07-12', '2021-07-15', '2021-07-31'],
    'Day_Val': [14, 11, 15, 33, 35, 55, 6, 8, 9, 10]
})

2. 日期格式转换与年月提取

将字符串类型的Date转为datetime，方便后续按月份分组：

# 转换日期列类型
eom_df['Date'] = pd.to_datetime(eom_df['Date'])
day_df['Date'] = pd.to_datetime(day_df['Date'])

# 提取年月（用于ID+月份的匹配）
eom_df['YearMonth'] = eom_df['Date'].dt.to_period('M')
day_df['YearMonth'] = day_df['Date'].dt.to_period('M')

3. 标记每月最后一条记录

按ID和YearMonth分组，标记每组内日期最晚的记录：

day_df['is_last_in_month'] = day_df.groupby(['ID', 'YearMonth'])['Date'].rank(ascending=False, method='first') == 1

4. 合并数据并填充月末值

通过ID和YearMonth匹配月末值，仅在标记为最后一行的记录中保留EoM值：

# 合并月末值
merged_df = day_df.merge(
    eom_df[['ID', 'YearMonth', 'EoM_Val']],
    on=['ID', 'YearMonth'],
    how='left'
)

# 仅保留每月最后一条记录的EoM值
merged_df['EoM_Val'] = merged_df.apply(
    lambda row: row['EoM_Val'] if row['is_last_in_month'] else None,
    axis=1
)

# 清理辅助列并调整列顺序
merged_df = merged_df[['ID', 'Date', 'Day_Val', 'EoM_Val']]

# 可选：将Date转回字符串格式
merged_df['Date'] = merged_df['Date'].dt.strftime('%Y-%m-%d')

5. 输出结果

执行print(merged_df.to_string(index=False))，得到的结果与需求完全一致：

ID        Date  Day_Val   EoM_Val
 AAA  2021-06-05       14       NaN
 AAA  2021-06-12       11       NaN
 AAA  2021-06-21       15  1417744.0
 BBB  2021-06-06       33       NaN
 BBB  2021-06-18       35       NaN
 BBB  2021-06-27       55  3946750.0
 AAA  2021-07-08        6       NaN
 AAA  2021-07-12        8  2792182.0
 BBB  2021-07-15        9       NaN
 BBB  2021-07-31       10 81073822.0

关键说明 #

• 使用dt.to_period('M')提取年月，确保跨ID的月份匹配准确；
• rank(ascending=False, method='first')用于处理同一组内可能存在的重复日期，保证仅标记一条最后记录；
• 合并后通过apply过滤值，避免不必要的全量填充。

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