CODEWITHSUNDEEP
pythonexcelpandas
Make_A_Living
Make_A_Living

Reputation: 61

Python to slice a dataframe to many excel files

I want to split a dataframe into many excel files, using its row value's name for files' name

Sample dataframe

    account_key    State
0    111            NY
1    111            CA
2    222            NJ
3    888            WE
4    888            TL

Expected result:

The first excel file's name is account_111.xlsx, and the data in it is

account_key    State
 111            NY
 111            CA

the Second excel file's name is account_222.xlsx, the data in it is 

account_key    State
222             NJ

my code doesn't give me the expected result

names = df['account_key'].unique().tolist()
writer = pd.ExcelWriter(path + 'my.xlsx', engine='xlsxwriter')
for myname in names:
    mydf = df.loc[df['account_key']==myname]
    mydf.to_excel(writer)
    writer.save()

by the way how to modify the code to rename the worksheet from "sheet1" to "myNote" while looping ?

Upvotes: 2

Views: 250

Answers (1)

rafaelc
rafaelc

Reputation: 59274

IIUC, just use to_excel in groupby

for acc, g in df.groupby('account_key'):
    g.to_excel(f'account_{acc}.xlsx')

If your have an older version of python which does not support f-strings, use

for acc, g in df.groupby('account_key'):
    g.to_excel('account_{}.xlsx'.format(acc))

To name the worksheet, just pass the name as second argument

g.to_excel(f'account_{acc}.xlsx', 'myNote')

Upvotes: 4

Related Questions