Read in multiple Excel files into single dataframe with filename as a column in new dataframe.

read_multi_excel_source.py

import pandas as pd
import os
import glob

def read_multi_excel(path):
    '''
    Given a file path with wildcard and extension, parse all files with that extension in directory 
    into a single dataframe.
    '''
    
    all_files = glob.glob(path)
    li = []
    
    for filename in all_files:
        df = pd.read_excel(filename, index_col=None, header=1)
        df['Source'] = os.path.basename(filename)
        li.append(df)
        
    df = pd.concat(li, axis=0, ignore_index=True)
    
    return df