f you’re reading in a workbook with multiple sheets, you can pull them all into one dataframe using:
df = pd.concat(pd.read_excel('Ticket_Sales_Total.xlsx', sheet_name=None), ignore_index=True)
This trick works when your data uses all the same headers and there’s no additional info to be gained from the sheet name.
Alternatively, if you want to read in the sheets and retain some info from the sheet name, you can use this function below.
def read_excel_sheets(xls_path):
"""Read all sheets of an Excel workbook and return a single DataFrame"""
print(f'Loading {xls_path} into pandas')
xl = pd.ExcelFile(xls_path)
df = pd.DataFrame()
columns = None
for idx, name in enumerate(xl.sheet_names):
print(f'Reading sheet #{idx}: {name}')
sheet = xl.parse(name)
if idx == 0:
# Save column names from the first sheet to match for append
columns = sheet.columns
sheet.columns = columns
# Add sheet name as column
sheet['sheet'] = name.split(" ")[-1]
# Assume index of existing data frame when appended
df = df.append(sheet, ignore_index=True)
return df
pandas creates a new column (‘sheet’) containing the last word of the sheet name as its value. If the sheets in Ticket_Sales_Total.xlsx are named Ticket Sales 2017, Ticket Sales 2018, and Ticket Sales 2019, then the read_excel_sheets() function will append each row with the relevant year from the sheet name.