How to do Sequential Subtraction of a series, reducing from initial Total to zero, subtracting from previous cell with current row value

Question

I have this input table and the total amount 153876.01

Index	AMOUNT	Balance
0	18661.02	135214.99
2	1365.44
3	2821.91
4	2821.91
...	...
75	227.57
76	188.13
77	141.10
78	84.96
79	47.08

I need to compute the Balance, as below:

	Imports	Balance
0	18661.02	135214.99
1	1365.44	133849.55
2	2821.91	131027.64
3	2821.91	128205.73
4	2548.82	125656.91
..	...	...
73	227.57	461.27
74	188.13	273.14
75	141.10	132.04
76	84.96	47.08
77	47.08	0.00

[78 rows x 2 columns]

A better explanation would be:

This is what I am doing right now and able to compute the Balance column:

def somefunc(row):
    print(row.name)
    if row.name != 0:
        print(row.name, df_in['Balance'][row.name-1], row['AMOUNT'])
        df_in.at[row.name, 'Balance'] = df_in['Balance'][row.name-1] - row['AMOUNT']
df_in.apply(somefunc, axis=1)

I am looking for a solution that is right and far better than this one and which is also correct while using pandas.

Answer 1

Compute a cumsum and rsub your initial total:

total = 153876.01
# or if the final Balance should be 0
total = df['AMOUNT'].sum()

df['Balance'] = df['AMOUNT'].cumsum().rsub(total)

If the final Balance is 0, you can also compute a reverse cumsum and shift, which might be even more efficient to compute:

df['Balance'] = df.loc[::-1, 'AMOUNT'].cumsum().shift(fill_value=0)

Output:

      AMOUNT    Balance
 0  18661.02  135214.99
 1   1365.44  133849.55
 2   2821.91  131027.64
...
77     47.08       0.00