How to create a dataframe in which one column's input is dynamic and derived via an IF statement dependent on another entry in that column

Question

I am looking to convert a two column Excel table which uses IF statements to calculate values in the second column, in to a Pandas dataframe.

Column A figures are static.

Column B figures are dynamic (with the exception of B5 which always remains 100%) and are calculated in Excel with the following IF statement (row 5 being the starting/reference point):

B4 =IF((B5*(1+Variable_1))<2, B5*(1+Variable_1), 2)

B3 =IF((B4*(1+Variable_1))<2, B4*(1+Variable_1), 2)

B2 =IF((B3*(1+Variable_1))<2, B3*(1+Variable_1), 2)

...and so on.

Variable_1 = a percentage (in this example 5%) which used when column A is negative.

When column A switches to positive i.e. >0% (A5), the IF statement changes to:

B6 =IF((B5*(1-Variable_2))>0, B5*(1-Variable_2), 0)

B7 =IF((B6*(1-Variable_2))>0, B6*(1-Variable_2), 0)

… and so on.

Variable_2 is also a percentage and is used when column B is positive (in this example it is also 5%).

I'm a complete amateur when it comes to Python but im hoping someone out there can assist me with something which is rather easy to do in Excel! Many thanks in advance.

why need it dynamic? just create a df with the values from above? — Patrick Artner
– Patrick Artner, Commented Jun 14, 2020 at 12:08
Hi Patrick, it has to be dynamic otherwise I would have just done what you said. — Paul Allen
– Paul Allen, Commented Jun 14, 2020 at 12:31
to what purpose? If you change something in A, B wont be recalculated. Pandas stores values - not calculations. It is not like in excel where you change something in A and the df autocalculates B — Patrick Artner
– Patrick Artner, Commented Jun 14, 2020 at 13:27
Thanks Patrick. What you described i.e. changes to A and the df autocalculates B, is exactly what I am looking to do. — Paul Allen
– Paul Allen, Commented Jun 14, 2020 at 13:32
Variable_1 and variable_2 will change and I then want the table in the OP to update to reflect this. — Paul Allen
– Paul Allen, Commented Jun 14, 2020 at 13:33

Patrick Artner · Accepted Answer · 2020-06-14 14:51:12Z

1

Well, then you can reconstruct your dataframe every time you want A to change:

A = [i/100.0 for i in range(-4,5)]

v1 = 5/100.0
v2 = 5/100.0

b5 = 1.0
b4 = min(2,b5*(1+v1))
b3 = min(2,b4*(1+v1))
b2 = min(2,b3*(1+v1))
b1 = min(2,b2*(1+v1))

b6 = max(0,b5*(1-v2))
b7 = max(0,b6*(1-v2))
b8 = max(0,b7*(1-v2))
b9 = max(0,b8*(1-v2))


import pandas as pd

df = pd.DataFrame( {"A": A, "B": [b1, b2, b3, b4, b5, b6, b7, b8, b9]})

print(df)

Output:

      A         B
0 -0.04  1.215506
1 -0.03  1.157625
2 -0.02  1.102500
3 -0.01  1.050000
4  0.00  1.000000
5  0.01  0.950000
6  0.02  0.902500
7  0.03  0.857375
8  0.04  0.814506

answered Jun 14, 2020 at 14:51

Patrick Artner

51.9k10 gold badges50 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Allen Over a year ago

Patrick, you're a gent. You have nailed it. This is exactly what I was looking to achieve. In my early foray in to the world I really appreciate the different ways of approaching problems. Really appreciate your help on this. Thank you.

Collectives™ on Stack Overflow

How to create a dataframe in which one column's input is dynamic and derived via an IF statement dependent on another entry in that column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related