CODEWITHSUNDEEP
pythonexcelpandas
DoctorEXE
DoctorEXE

Reputation: 123

Split excel tabs by categories and save them as separate files

Let's say that I have .xlsx file with several sheets named like ISO3 codes: AFG, ALB, AGO, ... , ZWE. Something around 75 tabs.

What I would like to do is to split this tabs by categories and save them as separate files. Example:

Category 1: AFG, ALB, AGO - output Categoty_1.xlsx only with these 3 tabs.

Category 2: AZE, BGD, BEN, BNT, BOL - output Categoty_2.xlsx only with these 5 tabs etc.

Trying to do that with pd.ExcelWriter but no success yet.

Upvotes: 0

Views: 54

Answers (1)

jezrael
jezrael

Reputation: 863226

Idea is create dictionary for new filenames with expected sheets, then use sheet_name=None in read_excel for dictionary of DataFrames and then write sheetnames in loop by dict:

d = {'Categoty_1': ['AFG', 'ALB', 'AGO'], 'Categoty_2':['AZE','BGD','BEN','BNT','BOL']}

dfs = pd.read_excel('inputfile.xlsx', sheet_name=None)

for k, v in d.items():
    writer = pd.ExcelWriter(f'{k}.xlsx')
    for sheet in v:
        #tested if match sheetname
        if sheet in dfs.keys():
            dfs[sheet].to_excel(writer, sheet_name=sheet)
    writer.save()

Upvotes: 1

Related Questions