import pandas as pd sheets_dict = pd.read_excel('Book1.xlsx', sheetname=None) full_table = pd.DataFrame() for name, sheet in sheets_dict.items(): sheet['sheet'] = name sheet = sheet.rename(columns=lambda x: x.split('\n')[-1]) full_table = full_table.append(sheet) full_table.reset_index(inplace=True, drop=True) print full_table