Using apply function to Pandas dataframe

Question

I am trying to do an emulation of a loan with monthly payments in pandas.

The credit column contains amount of money which I borrowed from the bank.

The debit column contains amount of money which I payed back to a bank.

The total column should contain the amount which is left to pay to a bank. Basically it contains the subtraction result between the credit and debit column).

I was able to write the following code:

import pandas as pd

# This function returns the subtraction result of credit and debit
def f(x):
    return (x['credit'] - x['debit'])


df = pd.DataFrame({'credit': [1000, 0, 0, 500],
                   'debit': [0, 100, 200, 0]})

for i in df:
    df['total'] = df.apply(f, axis=1)

print(df)

It works (it subtracts the debit from the credit). But it doesn't keep results in the total columns. Please see Actual and Expected results below.

Actual result:

   credit  debit        total
0    1000      0         1000
1       0    100         -100
2       0    200         -200
3     500      0          500

Expected result:

   credit  debit        total
0    1000      0         1000
1       0    100          900
2       0    200          700
3     500      0         1200

Please feel free to correct this question if I am wrong on something — Alex, Feb 21 '19 at 16:17

Dani Mesejo · Accepted Answer · 2019-02-21T16:28:57.133

6

You could use cumsum:

df['total'] = (df.credit - df.debit).cumsum()

print(df)

Output

   credit  debit  total
0    1000      0   1000
1       0    100    900
2       0    200    700
3     500      0   1200

edited Feb 21 '19 at 16:28

answered Feb 21 '19 at 16:19

Dani Mesejo

61,499
6
49
76

Might be helpful to explain cumsum() also. – Edeki Okoh Feb 21 '19 at 16:27
Daniel, thank you. It is short and sweet and works like a charm! – Alex Feb 22 '19 at 09:39

Edeki Okoh · Answer 2 · 2019-02-21T16:26:31.267

You don't need apply here.

import pandas as pd

df = pd.DataFrame({'credit': [1000, 0, 0, 500],
                   'debit': [0, 100, 200, 0]})

df['Total'] = (df['credit'] - df['debit']).cumsum()

print(df)

Output

  credit  debit  Total                                                                                                                                                           
0    1000      0   1000                                                                                                                                                           
1       0    100    900                                                                                                                                                           
2       0    200    700                                                                                                                                                           
3     500      0   1200

The reason why apply wasn't working is because apply executes over each row rather than keeping the running total after each subtraction. Passing cumsum() into the subtraction kill keep the running total to get the desired results.

Hi, Edeki. Thank you for your answer. It works excellent as well. I apologize that I can't check both answers as better ones. Cheers! — Alex, Feb 22 '19 at 09:41

Using apply function to Pandas dataframe

2 Answers2