I have a sample a of dataframe with banking data. I would like to know if it is possible to fill the empty cells without using a for loop.
In this example, let's say that at the row number 2 (pythonic way), it should take the value of the balance at the previous row 52867,36 and add the amount of the row number 2 : 847.00.
This happens when there are several transactions on the same date.
It is easy with a for loop but I would like to know if there is a way to do it by a vectorisation.
import pandas as pd
l1 = ['26.10.2022', '27.10.2022', '28.10.2022', '28.10.2022', '28.10.2022','28.10.2022', '31.10.2022', '31.10.2022', '01.11.2022', '01.11.2022', '03.11.2022', '04.11.2022', '07.11.2022', '07.11.2022', '07.11.2022', '08.11.2022', '09.11.2022', '09.11.2022']
l2 = [54267.36,52867.36, '','' , '',52744.21,'' ,52646.91,'',34898.36,34871.46,51026.46,'','',50612.36,61468.52,'',69563.27]
l3 = [-390,-1400,847,-900.15,-45,-25,-57.3,-40,-12528.55,-5220,-26.9,16155,-275,-105,-34.1,10856.16,7663.95,430.8]
df = pd.DataFrame(list(zip(l1,l2,l3)), columns = ['Date','Balance','Amount'])
print(df)
Date Balance Amount
0 26.10.2022 54267.36 -390.00
1 27.10.2022 52867.36 -1400.00
2 28.10.2022 847.00
3 28.10.2022 -900.15
4 28.10.2022 -45.00
5 28.10.2022 52744.21 -25.00
6 31.10.2022 -57.30
7 31.10.2022 52646.91 -40.00
8 01.11.2022 -12528.55
9 01.11.2022 34898.36 -5220.00
10 03.11.2022 34871.46 -26.90
11 04.11.2022 51026.46 16155.00
12 07.11.2022 -275.00
13 07.11.2022 -105.00
14 07.11.2022 50612.36 -34.10
15 08.11.2022 61468.52 10856.16
16 09.11.2022 7663.95
17 09.11.2022 69563.27 430.80
You can cumsum on Amount column to get difference to the first value of Balance then fillna value in Balance column
df['Balance'] = (pd.to_numeric(df['Balance'])
.fillna(df['Amount'].shift(-1).cumsum().add(df.iloc[0]['Balance']).shift(1)))
print(df)
Date Balance Amount
0 26.10.2022 54267.36 -390.00
1 27.10.2022 52867.36 -1400.00
2 28.10.2022 53714.36 847.00
3 28.10.2022 52814.21 -900.15
4 28.10.2022 52769.21 -45.00
5 28.10.2022 52744.21 -25.00
6 31.10.2022 52686.91 -57.30
7 31.10.2022 52646.91 -40.00
8 01.11.2022 40118.36 -12528.55
9 01.11.2022 34898.36 -5220.00
10 03.11.2022 34871.46 -26.90
11 04.11.2022 51026.46 16155.00
12 07.11.2022 50751.46 -275.00
13 07.11.2022 50646.46 -105.00
14 07.11.2022 50612.36 -34.10
15 08.11.2022 61468.52 10856.16
16 09.11.2022 69132.47 7663.95
17 09.11.2022 69563.27 430.80