CODEWITHSUNDEEP
pythonpandasdataframe
ketan
ketan

Reputation: 2904

How to calculate bank statement debit/credit columns using balance column of pandas dataframe?

I have one dataframe which looks like below:

                   Date_1                Date_2         DR      CR      Bal
0                5 Dec 2017               5 Dec 2017    500     NaN     1000
1               14 Dec 2017               14 Dec 2017   NaN     NaN     1500
2               15 Dec 2017               15 Dec 2017   NaN     NaN     1200
3               18 Dec 2017               18 Dec 2017   NaN     NaN     1700
4               21 Dec 2017               21 Dec 2017   NaN     NaN     2000
5               22 Dec 2017                22 Dec 2017  NaN     NaN     1000

In the above dataframe "Bal" column contains balance values and want to fill up the DR/CR values based on the next "Bal" amount.

I did it using simple python but seems like pandas can perform this action in very intelligent manner.

Expected Output:

                   Date_1                Date_2         DR      CR      Bal
0                5 Dec 2017               5 Dec 2017    500     NaN     1000
1               14 Dec 2017               14 Dec 2017   NaN     500     1500
2               15 Dec 2017               15 Dec 2017   300     NaN     1200
3               18 Dec 2017               18 Dec 2017   NaN     500     1700
4               21 Dec 2017               21 Dec 2017   NaN     300     2000
5               22 Dec 2017                22 Dec 2017  1000    NaN     1000

Upvotes: 4

Views: 1089

Answers (1)

Jondiedoop
Jondiedoop

Reputation: 3353

You could use a pd.mask. First calculate the difference of the balance by using diff. By using mask, fill one column by its absolute value if it's negative, and mask the np.nan values in the other column where it's positive.

diff = df['Bal'].diff()
df['DR'] = df['DR'].mask(diff < 0, diff.abs())
df['CR'] = df['CR'].mask(diff > 0, diff)

#Output
#                 Date_1  Date_2      DR     CR   Bal
#0 5  Dec 2017 5     Dec    2017   500.0    NaN  1000
#1 14 Dec 2017 14    Dec    2017     NaN  500.0  1500
#2 15 Dec 2017 15    Dec    2017   300.0    NaN  1200
#3 18 Dec 2017 18    Dec    2017     NaN  500.0  1700
#4 21 Dec 2017 21    Dec    2017     NaN  300.0  2000
#5 22 Dec 2017 22    Dec    2017  1000.0    NaN  1000

Upvotes: 5

Related Questions