如何使求和结果逼近列值并合并Pandas数据框的余额列

我有如下Pandas数据框：

data = {'id':['xxx', 'xxy', 'xxz', 'xyx', 'xzy'],
        'Total_I':[4796.6, 170637.53, 782.64, 449.12, 25654.02],
        'SumImp_RP':[3658.75, 0, 0, 0, 21530.45],
        'Total_E':[1137.84, 170637.56, 1565.26, 449.11, 8501.18],
        'Egre_E':[980.9, 130901.29, 674.69, 387.17, 1532.76],
        'ImpEgre_E':[156.94, 20944.27, 107.94000000000001, 61.94, 245.24],
        'Desc_E':[1137.84, 151845.56, 782.63, 449.11, 1778], 
}
df = pd.DataFrame(data)

通过以下代码计算余额SalIns和SalIns2：

dfLim[["Desc_E", "Total_E", "Total_I", "SumImp_RP"]] = dfLim[["Desc_E", "Total_E", "Total_I", "SumImp_RP"]].astype(float)

dfLim['SalIns2'] = dfLim.loc[(dfLim.Desc_E < dfLim.Total_E) & (dfLim.TotalEgresos_E <= dfLim.Total_I) & ((dfLim.SumImp_RP + dfLim.TotalEgresos_E) <= dfLim.Total_I)].eval('(Total_E + SumImp_RP) - Total_I')

dfLim['SalIns'] = (dfLim.Total_I - (dfLim.SumImp_RP + dfLim.Desc_E))

得到结果：

但结果不符合预期，我需要实现两点：

1. 让Total_E尽可能接近Total_I（允许Total_E大于Total_I）
2. 将SalIns和SalIns2合并为单个SalIns列

尝试了以下代码但未成功：

dfLim['diff'] = abs((dfLim['SumImp_RP'] + dfLim['Total_E ']) - dfLim['Total_I'])

mask = (dfLim['Desc_E'] < dfLim['Total_E ']) & \
       (dfLim['Total_E '] <= dfLim['Total_I']) & \
       (dfLim['diff'] <= 0.1)

dfLim['SalIns2'] = dfLim.loc[mask].eval('(Total_E + SumImp_RP) - Total_I')

dfLim = dfLim.drop(columns=['diff'])

期望输出：

观察期望输出的规律：

• 当Total_E与Total_I的差值绝对值≤0.1时，SalIns取(Total_E + SumImp_RP) - Total_I
• 其他情况保留原SalIns的计算逻辑：Total_I - (SumImp_RP + Desc_E)

另外注意你之前代码里的TotalEgresos_E列在原始数据中不存在，这是导致SalIns2全为空的核心原因。

修正后的代码如下：

import pandas as pd

data = {'id':['xxx', 'xxy', 'xxz', 'xyx', 'xzy'],
        'Total_I':[4796.6, 170637.53, 782.64, 449.12, 25654.02],
        'SumImp_RP':[3658.75, 0, 0, 0, 21530.45],
        'Total_E':[1137.84, 170637.56, 1565.26, 449.11, 8501.18],
        'Egre_E':[980.9, 130901.29, 674.69, 387.17, 1532.76],
        'ImpEgre_E':[156.94, 20944.27, 107.94000000000001, 61.94, 245.24],
        'Desc_E':[1137.84, 151845.56, 782.63, 449.11, 1778], 
}
dfLim = pd.DataFrame(data)

# 先计算基础版SalIns
dfLim['SalIns'] = dfLim['Total_I'] - (dfLim['SumImp_RP'] + dfLim['Desc_E'])

# 定义条件：Total_E和Total_I的差值绝对值≤0.1
close_mask = abs(dfLim['Total_E'] - dfLim['Total_I']) <= 0.1

# 对符合条件的行替换SalIns的值
dfLim.loc[close_mask, 'SalIns'] = (dfLim['Total_E'] + dfLim['SumImp_RP']) - dfLim['Total_I']

# 输出目标结果
print(dfLim[['id', 'Total_I', 'SumImp_RP', 'Total_E', 'Egre_E', 'ImpEgre_E', 'Desc_E', 'SalIns']])

运行后即可得到你期望的输出，核心逻辑是先计算基础余额，再对Total_E与Total_I接近的行进行值替换，最终合并为单一SalIns列。

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