python- pandas减去列中的列中值的值

Question

Have a dataframe mortgage_data with columns name mortgage_amount and month (in asceding order)

mortgage_amount_paid = 1000

mortgage_data:

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

How to deduct and update mortgage_amount in ascending order or month using mortgage_amount_paid在dataframe中逐行行 并添加一列付费

> MORTGAGE_DATA：

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

EX：

如果Mortgage_amount_Paid = 600

Mortgage_data：

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

尝试以下：

mortgage_amount_paid = 600

# amount saved - debt
m1 = df['mortgage_amount'].cumsum().sub(mortgage_amount_paid)
# is it positive?
m2 = m1>0
# is the previous month also positive?
m3 = m2.shift(fill_value=False)

df['mortgage_amount_updated'] = (m1.clip(0, mortgage_amount_paid)
                                   .mask(m3, df['mortgage_amount'])
                                 )
df['paid_status'] = np.select([m3, m2], ['zero', 'partial'], 'full')

错误：我给出了Mortgage_amount_paid = 400。付费状态应支付，零，零。我得到的报酬，部分，零

和

mortgage_amount_paid = 600

m = df['mortgage_amount'].cumsum()

df['paid_status'] = np.select(
    [m <= mortgage_amount_paid,
     (m > mortgage_amount_paid) & (m.shift() < mortgage_amount_paid)
     ],
    ['full', 'partial'],
    default='zero'
)
df['mortgage_amount_updated'] = np.select(
    [df['paid_status'].eq('full'),
     df['paid_status'].eq('partial')],
    [0, m-mortgage_amount_paid],
    default=df['mortgage_amount']
)

错误：如果Mortgage_amount_paid = 1付费_STATUS应该是部分，零，零。我的部分零，零，零

Answer 1

您可以写一个函数：

def new(mortgage_amount_paid, df):
    m = df.mortgage_amount.cumsum()
    n = mortgage_amount_paid
    df['paid_status'] = np.where(m < n, 'full', 
             np.where(m - n < df.mortgage_amount, 'partial', 'zero'))
    return df # You do not have to since it does inplace replacement


new(600, df)
   name  mortgage_amount  month paid_status
0  mark              400      1        full
1  mark              500      2     partial
2  mark              200      3        zero

new(1000, df)
   name  mortgage_amount  month paid_status
0  mark              400      1        full
1  mark              500      2        full
2  mark              200      3     partial

new(100, df)
   name  mortgage_amount  month paid_status
0  mark              400      1     partial
1  mark              500      2        zero
2  mark              200      3        zero

new(2000, df)
   name  mortgage_amount  month paid_status
0  mark              400      1        full
1  mark              500      2        full
2  mark              200      3        full

Answer 2

我添加了一些额外的列，并使用剩余数量来确定状态，而不是相反。它解决了问题并通过了您的测试案例：

df['cumsum'] = df['debt'].cumsum()
df['previous']= df['cumsum'] - df['debt']
df['paid']=mortgage_amount_paid-df['previous']

df['remaining'] = np.select(
    [(df['paid'] >= df['debt']),
     ((df['paid']<df['debt']) & (df['paid']>0)),
     df['paid']<=0],
    [0, df['debt']-df['paid'],df['debt']],
    default=df['debt']
)

df['status'] = np.select(
    [(df['remaining'] ==0) ,
     (df['remaining'] <df['debt']),
     (df['remaining']==df['debt'])
     ],
    ['full', 'partial','zero'],
    default='zero'
)

Answer 3

我会做一个班级

import pandas as pd
df = pd.DataFrame({"name":['mark','mark','mark'],   "mortgage_amount":[400,500,200],  "month":[1,2,3] })
        
class MortageUpdate(object):
    def __init__(self,df: pd.DataFrame, mortgage_amount_paid: float):
        self.mortgage_amount_updated = mortgage_amount_paid
        df[['mortgage_amount_updated','paid_status']] = df.apply(lambda x: self.update_mortage(x['mortgage_amount']), axis=1, result_type='expand')
        self.df = df
        
    def update_mortage(self, mortgage_amount):
        if mortgage_amount <= self.mortgage_amount_updated and self.mortgage_amount_updated > 0:
            self.mortgage_amount_updated = self.mortgage_amount_updated - mortgage_amount
            return 0, 'full'
        elif mortgage_amount > self.mortgage_amount_updated and self.mortgage_amount_updated > 0:
            self.mortgage_amount_updated = self.mortgage_amount_updated - mortgage_amount
            return abs(self.mortgage_amount_updated), 'partial'
        else:
            return abs(mortgage_amount), 'zero'

---

MortageUpdate(df, 600).df
    
    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

---

MortageUpdate(df, 1000).df

    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

---

MortageUpdate(df, 100).df
    
    name   mortgage_amount   month   mortgage_amount_updated   paid_status
0   mark               400       1                       300   partial
1   mark               500       2                       500   zero
2   mark               200       3                       200   zero