Subtract columns value in ascending order value of a column-CodePudding

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 row by row in a dataframe and add a column paid_full as yes if mortgage_amount_paid is fully deducted for that amount and no if not like this

mortgage_data:

name   mortgage_amount  month  mortgage_amount_updated  paid_full 
mark     400              1         0                     yes
mark     500              2         0                     yes
mark     200              3       100                     no

CodePudding user response：

IIUC, you get the cumulative mortgage amount with cumsum and subtract the amount paid. Then filter this to get your columns:

total = df["mortgage_amount"].cumsum().sub(1000)
df["mortgage_amount_updated"] = total.where(total.gt(0), 0)
df["paid_full"] = df["mortgage_amount_updated"].gt(0).map({False: "no", True: "yes"})

>>> df
   name  mortgage_amount  month  mortgage_amount_updated paid_full
0  mark              400      1                        0        no
1  mark              500      2                        0        no
2  mark              200      3                      100       yes

CodePudding user response：

Here is one way :

import numpy as np
mortgage_amount_paid = 1000
df['mortgage_amount_updated'] = np.where(mortgage_amount_paid - df['mortgage_amount'].cumsum() >=0 , 0, df['mortgage_amount'].cumsum() - mortgage_amount_paid)
df['paid_full'] = np.where(df['mortgage_amount_updated'],'no','yes')

output:

>>>
   name  mortgage_amount  month  mortgage_amount_updated paid_full
0  mark              400      1                        0       yes
1  mark              500      2                        0       yes
2  mark              200      3                      100        no