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.
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