title: Python Loop through Excel sheets, place into one df
body: <p>I have an excel file <code>foo.xlsx</code> with about 40 sheets <code>sh1</code>, <code>sh2</code>, etc. Each sheet has the format:</p>
  
  <pre><code>area      cnt   name\nparty1   name\nparty2
  blah      9         5               5
  word      3         7               5
  </code></pre>
  
  <p>In each sheet I want to rename the vars with the format <code>name\nparty</code> to only have the <code>party</code> as a label. Example output:</p>
  
  <pre><code>area      cnt    party1    party2     sheet
  bacon     9         5         5        sh1
  spam      3         7         5        sh1
  eggs      2         18        4        sh2
  </code></pre>
  
  <p>I am reading in the file with:</p>
  
  <pre><code>book = pd.ExcelFile(path) 
  </code></pre>
  
  <p>And then wondering if I need to do:</p>
  
  <pre><code>for f in filelist:
      df = pd.ExcelFile.parse(book,sheetname=??)
      'more operations here'
      # only change column names 2 and 3
       i, col in enumerate(df):
       if i&gt;=2 and i&lt;=3:
          new_col_name = col.split("\n")[-1]
          df[new_col_name] =
  </code></pre>
  
  <p>Or something like that?</p>