CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
user10083444
user10083444

Reputation: 105

How to update 'Balance' column based on other column values in a pandas Data frame

I have the following dataframe.

         Date    Status   Amount  Balance
0  06-10-2000   Deposit    40.00     40.0
1  09-12-2002  Withdraw  1000.00      NaN
2  27-06-2001   Deposit    47.00      NaN
3  07-12-2021  Withdraw   100.00      NaN
4  06-10-2022   Deposit   120.00      NaN
5  06-10-2000   Deposit    40.00      NaN
6  09-12-2024  Withdraw    50.00      NaN

The goal is to update the Balance based on whether it is deposit or withdrawal, Initial balance is = the starting amount. Hence hard coded it as 40.0.

Below is my code, somehow I am not getting the expected result.

Expected result:

         Date    Status   Amount  Balance
0  06-10-2000   Deposit    40.00     40.0
1  09-12-2002  Withdraw  1000.00    -960.0
2  27-06-2001   Deposit    47.00    -913.0
3  07-12-2021  Withdraw   100.00    -1013.0
4  06-10-2022   Deposit   120.00    -893.0
5  06-10-2000   Deposit    40.00    -853.0
6  09-12-2024  Withdraw    50.00    -903.0

What am i doing wrong in the code, the code is below

import pandas as pd
with open(r"transactions.txt", "r") as Account:
    details = Account.read().split(",")
print("details of txt",details)

df=pd.DataFrame(details)

fg=df[0].str.extract('(?P<Date>.*) (?P<Status>.*) (?P<Amount>.*)')
print(fg)

fg['Amount'] = fg.Amount.str.replace('$','') #removing $ sign
#setting first row value of balance as 40, as equal to amount in 1st row
fg.loc[fg.index[0], 'Balance'] = 40.00 
print(fg)

for index, row in fg.iterrows():
    if index==0:
        continue
    if fg.loc[index,'Status']=='Deposit':
        print("reached here")
        fg.at[float(index),'Balance']=sum(fg.loc[float(index),'Amount'],fg.loc[float(index-1),'Balance'])
    elif fg.loc[index,'Status']=='withdraw':  
        fg.at[float(index),'Balance']=fg.loc[float(index),'Amount']-fg.loc[float(index-1),'Balance']

    print(fg)

Upvotes: 2

Views: 485

Answers (1)

Umar.H
Umar.H

Reputation: 23099

IIUC, np.where and cumsum

df['Balance'] = np.where(df['Status'].eq('Deposit'),df['Amount'], df['Amount'] * -1)

df['Balance'] = df['Balance'].cumsum()

         Date    Status  Amount  Balance
0  06-10-2000   Deposit    40.0     40.0
1  09-12-2002  Withdraw  1000.0   -960.0
2  27-06-2001   Deposit    47.0   -913.0
3  07-12-2021  Withdraw   100.0  -1013.0
4  06-10-2022   Deposit   120.0   -893.0
5  06-10-2000   Deposit    40.0   -853.0
6  09-12-2024  Withdraw    50.0   -903.0

Upvotes: 1

Related Questions