Write multiple Excel files for each value of certain column Python Pandas

Question

Consider the following dataframe:

data = {'Col_A': [3, 2, 1, 0], 'Col_B': ['a', 'b', 'a', 'b']}
df = pd.DataFrame.from_dict(data)

I can create a dataframa a and write it to Excel as follows:

a = df[df.Col_B == 'a']
a
a.to_excel(excel_writer = 'F:\Desktop\output.xlsx', index = False)

I am looking for a way to write an Excel file, for each value of column B. In reality, there are hundreds of values for Col_B. Is there a way to loop through this?

Any help is greatly appreciated!

Regards,

M.

jezrael · Accepted Answer · 2019-06-14 11:25:01Z

3

If need for each group separate excel file loop by groupby object:

for i, a in df.groupby('Col_B'):
    a.to_excel(f'F:\Desktop\output_{i}.xlsx', index = False)

If need for each group separate sheetname in one exel file use ExcelWriter:

with pd.ExcelWriter('output.xlsx') as writer:
    for i, a in df.groupby('Col_B'):
        a.to_excel(writer, sheet_name=i, index = False)

answered Jun 14, 2019 at 11:25

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SQL_M Over a year ago

One additional question: Is there a way to import a column with percentages as is. By that I mean 68% will be imported as 68% instead of 0.68. Is that possible?

jezrael Over a year ago

@SQL_M - I think not easy, only if use converters in read_excel.

anky · Accepted Answer · 2019-06-14 11:29:48Z

3

You can do:

for i in df.Col_B.unique():
    df[df.Col_B.eq(i)].to_excel('F:\Desktop\output'+i+'.xlsx',index=False)

answered Jun 14, 2019 at 11:29

anky

75.3k11 gold badges46 silver badges76 bronze badges

1 Comment

SQL_M Over a year ago

Thanks for providing an alternative, plus 1.

Collectives™ on Stack Overflow

Write multiple Excel files for each value of certain column Python Pandas

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related