[FIXED] Running subtraction calculation

Issue

I have this dataframe and I want to achieve this type of running subtraction calculation:

For ID 1 at index 1, the payment of 10 must be subtracted from the amount of 1000 and result of 990 placed in balance field. For ID 1 at index 2, the payment of 15 must be subtracted from the 990 and result placed in balance which in this case was 975. For ID 1 at index 3, the payment of 20 must be subtracted from the 975 and result placed in balance which in this case was 955.

For ID 2 at index 1, the payment of 5 must be subtracted from the amount of 200 and result of 195 placed in balance field. For ID 2 at index 2, the payment of 5 must be subtracted from the 195 and result of 190 placed in balance field.

Solution

df['bal']=df['amount']-df.groupby('ID')['payment'].transform('cumsum')
df

    ID  payment     amount  balance     bal
0   1        10       1000      990     990
1   1        15       1000      975     975
2   1        20       1000      955     955
3   2         5        200      195     195
4   2         5        200      190     190

Answered By - Naveed