title: Positional string-formatting on pandas DataFrame
body: <p>I'm using <code>python</code> 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 <code>.xlsx</code>.</p>
  
  <p>What I want to do is to create a pandas DataFrame that looks like this:</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Unfortunately it doesn't work. It returns something like this:</p>
  
  <pre><code> "=VLOOKUP(ARangeIndex(start=2, stop=3002, step=1);'Sheet2'!A:J;6;0)"
  </code></pre>
  
  <p>Does anyone have any suggestion on how to do this?</p>
  
  <p>Thanks!</p>