title: Rename columns of Pandas DataFrame using values located at fixed locations of each column
body: <p>I am new to Python and am trying to import an Excel spreadsheet with multiple sheets and ~20 cols, 40 rows per sheet.</p>
  <pre><code>            Unnamed: 1 Unnamed: 2 Unnamed: 3  
   0          NaN        NaN        NaN  
   1          NaN        NaN        NaN  
   2          NaN        NaN        NaN  
   3          NaN        NaN        NaN  
   4          NaN        NaN        NaN  
   5   Population        Sex        NaN  
   6          NaN       Male     Female  
   7      Percent        NaN        NaN  
   8          NaN        NaN        NaN  
   9          NaN       48.5       51.5  
   10         NaN        NaN        NaN  
   11        12.2       18.4        6.5  
   12        36.1       41.5       31.1  
   13        51.6       40.1       62.5  
  </code></pre>
  <p>I am trying to rename the columns with values that currently sit within each column. Throughout the dataframe the  current column name and <em>location</em> of the new column names would always be as follows:</p>
  <pre><code>'Unnamed: 1' -&gt; value in 5th row
  'Unnamed: 2' -&gt; value in 6th row
  'Unnamed: 3' -&gt; value in 6th row
  </code></pre>
  <p>However, while the positions are always the same the <em>values</em> are different, so I cannot just rename the columns</p>
  <pre><code>df = df['Sheet'].rename(columns={'Unnamed: 1':'Total population', 
                                   'Unnamed: 2':'Male', 
                                   'Unnamed: 3':'Female',
                                   })
  </code></pre>
  <p>I was hoping to use rename columns and iloc to say</p>
  <pre><code>'where col.name == 'Unnamed: 1', rename(column=df.iloc[5])'
  'where col.name == 'Unnamed: 2', rename(column=df.iloc[6])'
  'where col.name == 'Unnamed: 3', rename(column=df.iloc[6])'
  </code></pre>
  <p>Is it possible to do this?</p>