CODEWITHSUNDEEP
pythonpandas
farhany
farhany

Reputation: 1541

How do I add/calculate a 'Balance' row?

I have a transaction download from my bank, e.g.

Date, Amount

Unfortunately, the CSV download does not contain a starting balance, so I've added the initial value at the top of the DataFrame. So now the data looks like:

Date, Amount, Balance
2018-01-01, 0, 10
2018-01-01, 10, 20
2018-01-02, 20, 40
2018-01-02, -10, 30
2018-01-03, 20, 50
2018-01-31, 0, 50

Balance is computed, by adding previous balance amount to current amount.

This is what I could muster, and it smells bad:

df = pd.read_csv("~/Downloads/Chequing.CSV", parse_dates=[0], na_values="n/a")

df['Date'] = pd.to_datetime(df['Date'])
df['Balance'] = 0

df1 = pd.DataFrame(data={'Date': ['2018-01-01'], 'Transaction': 
['CREDIT'], 'Name': ['Open'], 'Memo': ['Open'], 'Amount': [0], "Balance": [10.00]})
df1['Date'] = pd.to_datetime(df1['Date'])

df2 = pd.concat([df1, df], sort=False, ignore_index=True)

for i in range(1, len(df2)):
    prev_balance = df2['Balance'].iloc[i-1]
    amount = df2['Amount'].iloc[i]
    new_balance = round(amount + prev_balance, 2)
    df2['Balance'].iloc[i] = new_balance
    # Above generates a warning: 
    # SettingWithCopyWarning: 
    # A value is trying to be set on a copy of a slice from a DataFrame

# While writing this, I was able to get it working by replacing the for loop above with:
df2['Balance'] = round((df2["Amount"] + df2["Balance"]).cumsum(), 2)

pd.set_option('display.max_columns', None)

print(df2.groupby(df['Date'].dt.strftime('%m %B'))['Date', 'Amount', 'Transaction', 'Name', 'Balance'].max())

My question now becomes, is round-ing necessary? Can this be optimized or written in a better way?

Thank you!

Upvotes: 1

Views: 238

Answers (1)

Ananay Mital
Ananay Mital

Reputation: 1475

Thanks to @meW I would not have thought of cumsum()

Here's what I could do

%%time
df.Balance = np.concatenate((df.Balance[:1], (df.Balance.shift().fillna(0)+df.Amount).cumsum()[1:]))

#Wall time: 2 ms

Comparing to for loop method

%%time
for i in range(1,len(df.Balance)):
    df.Balance[i] = df.Balance[i-1]+df.Amount[i]
    
# Wall time: 173 ms

Month-wise maximum balance

df

          Date   Amount    Balance
0    2018-01-01       0         10
1    2018-01-01      10         20
2    2018-01-02      20         40
3    2018-02-02     -10         30
4    2018-03-03      20         50
5    2018-03-31      10         60


df.groupby(df.Date.dt.month).apply(lambda x: x[x.Balance == x.Balance.max()]).reset_index(drop=True)

          Date  Amount   Balance
0   2018-01-02      20        40
1   2018-02-02     -10        30
2   2018-03-31      10        60

I hope this helped. Comments are welcome ;)

Upvotes: 1

Related Questions