Pandas按月份升序逐行扣减抵押金额并生成还款状态列

基础前提 #

• 目标DataFrame为mortgage_data，包含name、mortgage_amount、month三列，数据已按month升序排列，示例初始数据：

name   mortgage_amount  month 
mark     400              1
mark     500              2
mark     200              3

• 已定义已还总金额变量：mortgage_amount_paid
• 字段规则：
  • 新增mortgage_amount_updated列：存储逐行扣减后该行剩余待还抵押金额
  • 新增paid_status列标记还款状态：
    • full：该行抵押金额被全额扣完
    • partial：该行抵押金额仅被部分扣减
    • zero：该行抵押金额完全未被还款覆盖

原有代码问题 #

原有实现仅做了二值判断，没有识别部分扣减、完全未覆盖的边界条件，无法正确区分partial和zero状态。

正确实现代码 #

import numpy as np
import pandas as pd

mortgage_amount_paid = 1000 # 可替换为600测试第二种场景
df = mortgage_data.copy()

# 计算累计抵押金额、上一行累计抵押金额（首行前置值补0）
df['cum_mortgage'] = df['mortgage_amount'].cumsum()
df['prev_cum_mortgage'] = df['cum_mortgage'].shift(fill_value=0)

# 计算剩余待还金额
df['mortgage_amount_updated'] = np.select(
    condlist=[
        # 场景1：累计到当前行的抵押金额 <= 总还款额，全额扣完
        df['cum_mortgage'] <= mortgage_amount_paid,
        # 场景2：上一行累计 < 总还款额，当前行累计 > 总还款额，部分扣减
        (df['prev_cum_mortgage'] < mortgage_amount_paid) & (df['cum_mortgage'] > mortgage_amount_paid),
        # 场景3：上一行累计已经 >= 总还款额，当前行未被覆盖
        df['prev_cum_mortgage'] >= mortgage_amount_paid
    ],
    choicelist=[
        0,
        df['mortgage_amount'] - (mortgage_amount_paid - df['prev_cum_mortgage']),
        df['mortgage_amount']
    ]
)

# 标记还款状态
df['paid_status'] = np.select(
    condlist=[
        df['cum_mortgage'] <= mortgage_amount_paid,
        (df['prev_cum_mortgage'] < mortgage_amount_paid) & (df['cum_mortgage'] > mortgage_amount_paid),
        df['prev_cum_mortgage'] >= mortgage_amount_paid
    ],
    choicelist=[
        'full',
        'partial',
        'zero'
    ]
)

# 移除中间计算列
df = df.drop(columns=['cum_mortgage', 'prev_cum_mortgage'])

效果验证 #

• 当mortgage_amount_paid = 1000时，输出结果：

name  mortgage_amount  month  mortgage_amount_updated paid_status
0  mark              400      1                        0        full
1  mark              500      2                        0        full
2  mark              200      3                      100     partial

• 当mortgage_amount_paid = 600时，输出结果：

name  mortgage_amount  month  mortgage_amount_updated paid_status
0  mark              400      1                        0        full
1  mark              500      2                      300     partial
2  mark              200      3                      200        zero

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