Positional string-formatting on pandas DataFrame

Question

I'm using python to automatise some processes at work. My final product has to be in excel format (formulas have to be there, and everything has to be traceable), so I work on a pandas DataFrame and then export the result to a .xlsx.

What I want to do is to create a pandas DataFrame that looks like this:

  ID                          Price                       Quantity  Total
0  A  =VLOOKUP(A2;'Sheet2'!A:J;6;0)  =VLOOKUP(A2;'Sheet2'!A:J;7;0)  =B2*C2
1  B  =VLOOKUP(A3;'Sheet2'!A:J;6;0)  =VLOOKUP(A3;'Sheet2'!A:J;7;0)  =B3*C3
2  C  =VLOOKUP(A4;'Sheet2'!A:J;6;0)  =VLOOKUP(A4;'Sheet2'!A:J;7;0)  =B4*C4
3  D  =VLOOKUP(A5;'Sheet2'!A:J;6;0)  =VLOOKUP(A5;'Sheet2'!A:J;7;0)  =B5*C5
4  E  =VLOOKUP(A6;'Sheet2'!A:J;6;0)  =VLOOKUP(A6;’Sheet2'!A:J;7;0)  =B6*C6

As you can see in the first row, the formulas reference A2, B2 and C2; the second row references A3, B3 and C3; the 'n' row references A(n+2), B(n+2) and C(n+2). The DataFrame has about 3.000 rows.

I want to generate this dataframe with a few lines of code, and i haven't got the expected result. I though using positional formatting would do:

df = pd.DataFrame()
df['temp'] = range(3000)

df['Price'] = """=VLOOKUP(A{0};'Sheet2'!A:J;6;0)""" .format(df.index + 2)
df['Quantity'] = """=VLOOKUP(A{0};'Sheet2'!A:J;7;0)""" .format(df.index + 2)
df['Total'] = """=B{0}*C{0}""" .format(df.index + 2)

df.drop('temp', axis=1, inplace=True)

Unfortunately it doesn't work. It returns something like this:

 "=VLOOKUP(ARangeIndex(start=2, stop=3002, step=1);'Sheet2'!A:J;6;0)"

Does anyone have any suggestion on how to do this?

Thanks!

could you provide a dummy example of your current sheet in order to save you from running excel formulas in pandas to export to excel! pandas can do this in a second without being so error prone and then you can output your calculation to that excel file. Unless your supervisor insists on the formulas present in the cells. Does he insist? — George Pamfilis
– George Pamfilis, Commented May 12, 2018 at 16:19
He does. Sorry, I forgot to point that out. Formulas have to be there, everything has to be traceable. — ebravo
– ebravo, Commented May 12, 2018 at 16:40
@ebravo Psst, see below, and if your question was answered, would you mind marking it accepted please? Thank you. — cs95
– cs95, Commented May 12, 2018 at 18:29

cs95 · Accepted Answer · 2018-05-12 15:39:42Z

1

Try vectorised string concatenation:

df = pd.DataFrame(index=range(2000)) # no need for temp here, btw

idx = (df.index + 2).astype(str)
df['Price'] = "=VLOOKUP(A" + idx + ";'Sheet2'!A:J;6;0)"

A similar process follows for the remainder of your columns:

df['Quantity'] = "=VLOOKUP(A" + idx + ";'Sheet2'!A:J;7;0)"
df['Total'] = 'B' + idx + '*C' + idx

df.head()

                           Price                       Quantity  Total
0  =VLOOKUP(A2;'Sheet2'!A:J;6;0)  =VLOOKUP(A2;'Sheet2'!A:J;7;0)  B2*C2
1  =VLOOKUP(A3;'Sheet2'!A:J;6;0)  =VLOOKUP(A3;'Sheet2'!A:J;7;0)  B3*C3
2  =VLOOKUP(A4;'Sheet2'!A:J;6;0)  =VLOOKUP(A4;'Sheet2'!A:J;7;0)  B4*C4
3  =VLOOKUP(A5;'Sheet2'!A:J;6;0)  =VLOOKUP(A5;'Sheet2'!A:J;7;0)  B5*C5
4  =VLOOKUP(A6;'Sheet2'!A:J;6;0)  =VLOOKUP(A6;'Sheet2'!A:J;7;0)  B6*C6

edited May 12, 2018 at 15:39

answered May 12, 2018 at 15:29

cs95

406k106 gold badges744 silver badges797 bronze badges

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

2 Comments

ebravo Over a year ago

Thanks! It works perfectly. Sorry for not marking it asap, but I was hoping for a solution using string formating. Anyways, Im very grateful.

cs95 Over a year ago

@ebravo I can guarantee this is going to be faster.

Collectives™ on Stack Overflow

Positional string-formatting on pandas DataFrame

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related