0

I'm manipulating some excel files, merging them en calculating balances. Because after the script is done some changes could be required I prefer a formula for balance over a calculated balance amount (this is working already). So I need python to create formulas which I will put into a pandas column and replace the old column with the new one. Now I don't understand how to generate the following:

=IF(Q3="some text",W2,W2+U3))

=IF(Q4="some text",W3,W3+U4))

etc

This formula works within Excel.

Latika Agarwal
  • 973
  • 1
  • 6
  • 11
Franky
  • 1

1 Answers1

0

You could define a function that takes an index and returns a string representing a formula. One way to do this is with str.format like so:

import pandas as pd

df = pd.DataFrame(np.random.rand(10, 4))
df.columns = [c for c in "ABCD"]


def i2str(i):
    return '=IF(A{1}="some text",B{0},C{0}+D{1}))'.format(i, i + 1)


df["E"] = (df.index + 1).map(i2str)

          # A         B         C         D                                  E
# 0  0.076411  0.200761  0.309738  0.088941      =IF(A2="some text",B1,C1+D2))
# 1  0.336796  0.721301  0.037390  0.816526      =IF(A3="some text",B2,C2+D3))
# 2  0.595759  0.909850  0.242617  0.590498      =IF(A4="some text",B3,C3+D4))
# 3  0.966616  0.005933  0.866219  0.416781      =IF(A5="some text",B4,C4+D5))
# 4  0.554771  0.804761  0.086853  0.641952      =IF(A6="some text",B5,C5+D6))
# 5  0.908722  0.042198  0.930092  0.327798      =IF(A7="some text",B6,C6+D7))
# 6  0.156592  0.402616  0.748740  0.601990      =IF(A8="some text",B7,C7+D8))
# 7  0.671168  0.239831  0.744886  0.497128      =IF(A9="some text",B8,C8+D9))
# 8  0.017318  0.773414  0.133034  0.167445    =IF(A10="some text",B9,C9+D10))
# 9  0.954062  0.849017  0.732924  0.310983  =IF(A11="some text",B10,C10+D11))
hilberts_drinking_problem
  • 11,322
  • 3
  • 22
  • 51
  • Thank you! This looks very much what I'm aiming for. Give me a moment to understand it and translate that into my script. I will let you know if I can get it to work. – Franky May 31 '18 at 17:57
  • Excel is not accepting the new input and removes it. When I print the new frame to screen I can see the formulas and change them as desired. Thanks for that bit. Not sure why Excel views it as erroneous input. – Franky May 31 '18 at 18:17