title: Using pandas Combining/merging 2 different Excel files/sheets
body: <p>I am trying to combine 2 different Excel files. (thanks to the post <a href="https://stackoverflow.com/questions/20908018/import-multiple-excel-files-into-python-pandas-and-concatenate-them-into-one-dat">Import multiple excel files into python pandas and concatenate them into one dataframe</a>)</p>
  
  <p>The one I work out so far is:</p>
  
  <pre><code>import os
  import pandas as pd
  
  df = pd.DataFrame()
  
  for f in ['c:\\file1.xls', 'c:\\ file2.xls']:
      data = pd.read_excel(f, 'Sheet1')
      df = df.append(data)
  
  df.to_excel("c:\\all.xls")
  </code></pre>
  
  <p>Here is how they look like.</p>
  
  <p><img src="https://i.stack.imgur.com/Nz0j1.jpg" alt="enter image description here"></p>
  
  <p>However I want to:</p>
  
  <ol>
  <li>Exclude the last rows of each file (i.e. row4 and row5 in File1.xls; row7 and row8 in File2.xls).</li>
  <li>Add a column (or overwrite Column A) to indicate where the data from.</li>
  </ol>
  
  <p>For example:</p>
  
  <p><img src="https://i.stack.imgur.com/jnM01.jpg" alt="enter image description here"></p>
  
  <p>Is it possible? Thanks.</p>