Python Pandas按月份升序逐行扣减按揭金额更新字段方法

核心规则 #

• 输入数据为已按month字段升序排列的DataFrame，包含name、mortgage_amount、month、to_be_paid_date四个原始字段，传入参数为总还款额、实际还款日期
• 需新增三个计算字段：
  • mortgage_amount_updated：逐行用总还款额抵扣当期应还后的剩余待还金额
  • paid_status：还款状态，当期全额还清标记为full、完全未被还款额抵扣标记为zero、仅偿还部分标记为partial
  • to_be_paid_date_updated：更新后待还日期，全额还清的行统一填实际还款日期，未全额还清的行按已结清的期数，日期依次前移对应月数

完整代码 #

import pandas as pd
import numpy as np

def update_mortgage_df(df, mortgage_amount_paid, mortgage_amount_paid_date):
    # 统一转换日期格式，适配输入的dd-mm-yyyy格式
    paid_date = pd.to_datetime(mortgage_amount_paid_date, dayfirst=True)
    df['to_be_paid_date'] = pd.to_datetime(df['to_be_paid_date'], dayfirst=True)

    # 计算逐行累计应还金额
    cum_due_amount = df['mortgage_amount'].cumsum()

    # 计算还款状态
    df['paid_status'] = np.select(
        [
            cum_due_amount < mortgage_amount_paid,
            cum_due_amount - mortgage_amount_paid < df['mortgage_amount']
        ],
        ['full', 'partial'],
        default='zero'
    )

    # 计算剩余待还金额
    df['mortgage_amount_updated'] = np.select(
        [
            df['paid_status'] == 'full',
            df['paid_status'] == 'partial'
        ],
        [0, cum_due_amount - mortgage_amount_paid],
        default=df['mortgage_amount']
    )

    # 计算更新后的待还日期
    full_paid_periods = (df['paid_status'] == 'full').sum()
    # 全额结清的行，待还日期统一为实际还款日期
    df.loc[df['paid_status'] == 'full', 'to_be_paid_date_updated'] = paid_date
    # 未结清的行，待还日期前移已结清的总月数
    unsolved_mask = df['paid_status'] != 'full'
    df.loc[unsolved_mask, 'to_be_paid_date_updated'] = (
        df.loc[unsolved_mask, 'to_be_paid_date'] - pd.DateOffset(months=full_paid_periods)
    )

    return df

逻辑说明 #

• 累计应还金额逐行累加后和总还款额对比判断还款状态，比嵌套np.where可读性更高
• 剩余待还金额按状态直接赋值：全额还清的剩余为0，部分还款的剩余为累计应还减去总还款额，未抵扣的剩余金额和原应还额一致
• 日期处理先统计全额结清的总期数，这部分已经提前还完，日期统一用实际还款日期；剩下未结清的期数因为提前还了N期，待还日期统一往前偏移N个自然月，符合期数前移的要求

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