pandas Combine Excel Spreadsheets

user_5569314 0
Viewed 13025

I have an Excel workbook with many tabs. Each tab has the same set of headers as all others. I want to combine all of the data from each tab into one data frame (without repeating the headers for each tab).

So far, I've tried:

import pandas as pd
xl = pd.ExcelFile('file.xlsx')
df = xl.parse()

Can use something for the parse argument that will mean "all spreadsheets"? Or is this the wrong approach?

Thanks in advance!

Update: I tried:

a=xl.sheet_names
b = pd.DataFrame()
for i in a:
    b.append(xl.parse(i))
b

But it's not "working".

excel python
2 Answers
import pandas as pd  

f = 'file.xlsx'
df = pd.read_excel(f, sheet_name=None, ignore_index=True) 
df2 = pd.concat(df, sort=True)

df2.to_excel('merged.xlsx', 
             engine='xlsxwriter', 
             sheet_name=Merged,
             header = True,
             index=False)
user_134420260
Related