按Unique ID补全账户对账单缺失月度记录并结转余额的技术问询

核心思路 #

针对每个Unique ID，生成其放款日期当月最后一天至2022年5月31日的所有月末日期序列，再与原始数据合并，用前向填充补全缺失的余额值，确保每个月份的记录都以上月末余额结转。

步骤与代码实现 #

假设你的原始DataFrame名为df，列名对应：Unique ID, Disbursed date, payment date, balance amount。

1. 预处理数据

先把日期列转为datetime类型，同时确保payment date统一为每月最后一天（避免原始数据中存在非月末日期的情况）：

import pandas as pd

# 转换日期列
df['Disbursed date'] = pd.to_datetime(df['Disbursed date'])
df['payment date'] = pd.to_datetime(df['payment date'])

# 将payment date统一为当月最后一天
df['payment date'] = df['payment date'] + pd.offsets.MonthEnd(0)

# 按Unique ID和payment date排序，保证时序正确
df = df.sort_values(['Unique ID', 'payment date']).reset_index(drop=True)

2. 生成每个ID的完整月末日期序列

对每个Unique ID分组，生成其对应时间范围内的所有月末日期：

def generate_full_month_series(group):
    # 获取当前ID的放款日期，取当月最后一天作为起始日期
    start_date = group['Disbursed date'].iloc[0] + pd.offsets.MonthEnd(0)
    # 结束日期固定为2022年5月31日
    end_date = pd.to_datetime('2022-05-31')
    # 生成所有月末日期
    full_dates = pd.date_range(start=start_date, end=end_date, freq='M')
    # 返回包含完整日期的DataFrame
    return pd.DataFrame({'payment date': full_dates, 'Unique ID': group['Unique ID'].iloc[0]})

# 对每个Unique ID应用函数，生成完整日期序列
full_df = df.groupby('Unique ID').apply(generate_full_month_series).reset_index(drop=True)

3. 合并原始数据并填充缺失余额

将原始数据与完整日期序列合并，用前向填充（ffill）补全缺失的余额，同时保留放款日期信息：

# 合并原始数据与完整日期序列
merged_df = pd.merge(full_df, df, on=['Unique ID', 'payment date'], how='left')

# 按Unique ID分组，前向填充缺失的balance amount和Disbursed date
merged_df['balance amount'] = merged_df.groupby('Unique ID')['balance amount'].ffill()
merged_df['Disbursed date'] = merged_df.groupby('Unique ID')['Disbursed date'].ffill()

# 重置索引
merged_df = merged_df.reset_index(drop=True)

效果说明 #

• 对于Unique ID 123这类缺失2022年3月记录的情况，会自动生成2022-03-31的记录，余额沿用2月末的5000；
• 自动补全2022年5月的记录，余额沿用4月末的2000；
• 所有Unique ID都会生成从放款当月到2022年5月的完整月末记录，确保时序连贯。

原代码问题修正 #

你之前的代码遍历单日期并生成单日范围，无法按ID分组生成月度序列，且日期范围设置错误（写的是2020-11-28），上述方案通过分组处理每个ID的时间范围，精准生成月末日期，解决了缺失月份的补全需求。

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