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!

Answer 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