Using pandas Combining/merging 2 different Excel files/sheets
Question: I am trying to combine 2 different Excel files. (thanks to the post [Import
multiple excel files into python pandas and concatenate them into one
dataframe](http://stackoverflow.com/questions/20908018/import-multiple-excel-
files-into-python-pandas-and-concatenate-them-into-one-dat))

The one I work out so far is:

    
    
    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")
    

Here is how they look like.

![enter image description here](http://i.stack.imgur.com/Nz0j1.jpg)

However I want to:

  1. Exclude the last rows of each file (i.e. row4 and row5 in File1.xls; row7 and row8 in File2.xls).
  2. Add a column (or overwrite Column A) to indicate where the data from.

For example:

![enter image description here](http://i.stack.imgur.com/jnM01.jpg)

Is it possible? Thanks.


Answer: For num. 1, you can specify `skip_footer` as explained
[here](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.read_excel.html#pandas.read_excel); or,
alternatively, do

    
    
    data = data.iloc[:-2]
    

once your read the data.

For num. 2, you may do:

    
    
    from os.path import basename
    data.index = [basename(f)] * len(data)
    

Also, perhaps would be better to put all the data-frames in a list and then
`concat` them at the end; something like:

    
    
    df = []
    for f in ['c:\\file1.xls', 'c:\\ file2.xls']:
        data = pd.read_excel(f, 'Sheet1').iloc[:-2]
        data.index = [os.path.basename(f)] * len(data)
        df.append(data)
    
    df = pd.concat(df)
    

