python - A value that need to be extended in time

Question

people.

I need to sum values of a data frame in different columns.

• OUT with the amount invested
• IN with the amount received
• DRAW with the amount taken.

So, OUT is the total invested. If you have a DRAW, means that value was taken for the investment. As an example, -100 (LINE 1) + 100 (LINE 2[DRAW]) means that you took part of the investment. In this case, the value received in the column IN, means we have 110-100 (both in line 2. One in column IN, the other, in DRAW) of income, giving us a total income of 10 units (10% of the investment = (110-100)/100 = (IN-DRAW)/OUT).

We also could have a DRAW without a return, as in line 12. In this example, from this line on, the income will be calculate in 2x-200 (-400) + 20 = -380.

After line 5, we have an investment of 2 times -200; -400 in the total and no DRAWs and OUTs, until line 12. My doubt lies in what is the best way to calculate the % in each month based on the OUTs, INs and DRAWs in all table.

LINE	DATE	OUT	IN	DRAW
1	2020-01-20	-100	-
2	2020-02-10	-	110	100
3	2020-02-11	-200	-
4	2020-02-21	-	20
5	2020-02-25	-200	-
6	2020-02-26	-200	-
7	2020-02-26	-	20
8	2020-03-09	-	40
9	2020-04-01	-	10
10	2020-04-07	-	20
11	2020-04-10	-	10
12	2020-05-10	-	-	20

Answer 1

I came with a solution, still not knowing if is the best one, but worked fine. I made a new column join OUT and DRAW together (OUTDRAW). This column was created with OUT data, and them filled the NaN spaces with DRAW values (this worked only because the values are not in the same line):

df['OUTDRAW'] = df['OUT']
df['OUTDRAW'].fillna(df['DRAW'], inplace=True)

After, filled NaN with 0 and made a cumsum on it.

df['OUTDRAW'].fillna(0, inplace=True)
df['OUTDRAW'].cumsum()

This gave me the column

LINE	DATE	OUT	DRAW	OUTDRAW
1	2020-01-20	-100	-	-100
2	2020-02-10	-	100	0
3	2020-02-11	-200	-	-200
4	2020-02-21	-	20	-200
5	2020-02-25	-200	-	-400
6	2020-02-26	-200	-	-600
7	2020-02-26	-	20	-600
8	2020-03-09	-	40	-600
9	2020-04-01	-	10	-600
10	2020-04-07	-	20	-600
11	2020-04-10	-	10	-600
12	2020-05-10	-	20	-580