使用Pandas基于前一行单元格值创建新列的问题求助

问题概述 #

我正在用Pandas构建现金流贷款模型，已生成包含Beginning Balance、Interest、Principal等字段的基础表：

需要新增Net Outstanding Balance、Prepaid Principal、Charge-Off Principal、Scheduled Principle Received列，逻辑如下：

• Net Outstanding Balance首行设为初始贷款余额50000
• 后续行的Net Outstanding Balance = 上一行Net Outstanding Balance - 上一行Scheduled Principle Received - 上一行Prepaid Principal - 上一行Charge-Off Principal
• Prepaid Principal = 当前行Net Outstanding Balance * SMM
• Charge-Off Principal = 当前行Net Outstanding Balance * Default
• Scheduled Principle Received = Principal * Total_SMM_Loss

编写的代码运行后出现大量NaN值，结果如下：

问题根源 #

1. 索引错误：代码中使用cf_table.at[1,'Net Outstanding Balance']赋值，但Pandas默认索引从0开始，导致首行（索引0）的Net Outstanding Balance未被初始化，后续行依赖的该列值缺失。
2. 向量计算不支持递推：直接通过cf_table.at[2:,'Net Outstanding Balance'] = ...进行向量运算，无法实现“用上一行结果计算当前行”的递推逻辑——因为除了索引1的位置，其他行的Net Outstanding Balance初始为NaN，运算后仍为NaN。

解决方案 #

方案1：循环实现递推（直观易理解） #

先重置索引确保从0开始，然后逐行计算：

import pandas as pd

# 重置索引，避免索引混乱
cf_table = cf_table.reset_index(drop=True)

# 参数定义
SMM = 0.0184
Default = 0.0059
Total_SMM_Loss = 0.975

# 初始化新增列
cf_table['Scheduled Principle Received'] = cf_table['Principal'] * Total_SMM_Loss
cf_table['Net Outstanding Balance'] = pd.Series(dtype='float64')
cf_table['Prepaid Principal'] = pd.Series(dtype='float64')
cf_table['Charge-Off Principal'] = pd.Series(dtype='float64')

# 设置首行值
cf_table.at[0, 'Net Outstanding Balance'] = 50000.00
cf_table.at[0, 'Prepaid Principal'] = cf_table.at[0, 'Net Outstanding Balance'] * SMM
cf_table.at[0, 'Charge-Off Principal'] = cf_table.at[0, 'Net Outstanding Balance'] * Default

# 循环计算后续行
for i in range(1, len(cf_table)):
    # 获取上一行的相关值
    prev_outstanding = cf_table.at[i-1, 'Net Outstanding Balance']
    prev_scheduled = cf_table.at[i-1, 'Scheduled Principle Received']
    prev_prepaid = cf_table.at[i-1, 'Prepaid Principal']
    prev_chargeoff = cf_table.at[i-1, 'Charge-Off Principal']
    
    # 计算当前行的Net Outstanding Balance
    cf_table.at[i, 'Net Outstanding Balance'] = prev_outstanding - prev_scheduled - prev_prepaid - prev_chargeoff
    # 计算当前行的Prepaid和Charge-Off
    cf_table.at[i, 'Prepaid Principal'] = cf_table.at[i, 'Net Outstanding Balance'] * SMM
    cf_table.at[i, 'Charge-Off Principal'] = cf_table.at[i, 'Net Outstanding Balance'] * Default

方案2：向量化递推（大数据量更高效） #

如果处理的数据集较大，循环效率较低，可以用自定义函数结合序列运算实现：

cf_table = cf_table.reset_index(drop=True)

SMM = 0.0184
Default = 0.0059
Total_SMM_Loss = 0.975

# 计算固定的Scheduled Principle Received
cf_table['Scheduled Principle Received'] = cf_table['Principal'] * Total_SMM_Loss

# 自定义函数计算Net Outstanding Balance的递推序列
def compute_outstanding(scheduled_series, smm_rate, default_rate, initial_balance):
    outstanding = [initial_balance]
    for idx in range(1, len(scheduled_series)):
        prev_balance = outstanding[-1]
        new_balance = prev_balance - scheduled_series.iloc[idx-1] - prev_balance*smm_rate - prev_balance*default_rate
        outstanding.append(new_balance)
    return pd.Series(outstanding, index=scheduled_series.index)

# 生成Net Outstanding Balance列
cf_table['Net Outstanding Balance'] = compute_outstanding(
    cf_table['Scheduled Principle Received'],
    SMM,
    Default,
    50000.00
)

# 计算剩余列
cf_table['Prepaid Principal'] = cf_table['Net Outstanding Balance'] * SMM
cf_table['Charge-Off Principal'] = cf_table['Net Outstanding Balance'] * Default

验证结果 #

运行上述代码后，将得到符合预期的结果，无NaN值：

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