Create an Excel file with multiple sheets with Python/Pandas

Question

I have a big dataset (df) (8M rows, 50 columns). I am trying to build a for loop to create an excel file where each sheet holds the value_counts() of each of the column of the dataset.

(i.e. on worksheet('Sheet1') I write df.columns[0].value_counts() and on worksheet('Sheet2') I write df.columns[1].value_counts() etc etc).

Here's what I tried:

for i in range(3,6):   # I am using a small range to test the loop
    z = df1[df1.columns[i]].value_counts()
    z = z.to_frame().reset_index()
    title = str(i)
    with pd.ExcelWriter('Pivot part1.xlsx') as writer:  
        z.to_excel(writer, sheet_name=title)

This keeps overwrite the file so that I ended up with an excel file with only one sheet rather the an Excel file with 4 sheets.

I hope I managed to explain clearly the issue and I apologize if this question is a duplicate, but I couldn't find a suitable answer, or at least one I could understand.

ASGM · Accepted Answer · 2020-04-23 20:10:33Z

2

Re-arrange so that you only open the excel writer object once:

with pd.ExcelWriter('Pivot part1.xlsx') as writer:  
    for i in range(3,6):
        z = df1[df1.columns[i]].value_counts()
        z = z.to_frame().reset_index()
        title = str(i)
        z.to_excel(writer, sheet_name=title)

edited Apr 23, 2020 at 20:10

answered Apr 23, 2020 at 18:25

ASGM

11.5k1 gold badge37 silver badges54 bronze badges

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

Comments

MohitC · Accepted Answer · 2020-04-23 18:40:47Z

2

You create the writer object once and it shall all work fine.

writer = pd.ExcelWriter('Pivot part1.xlsx', engine='xlsxwriter')
for i in range(3,6):   # I am using a small range to test the loop
    z = df1[df1.columns[i]].value_counts()
    z = z.to_frame().reset_index()
    title = str(i)
    z.to_excel(writer, sheet_name=title)
writer.save()

answered Apr 23, 2020 at 18:40

MohitC

4,8112 gold badges38 silver badges59 bronze badges

Collectives™ on Stack Overflow

Create an Excel file with multiple sheets with Python/Pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related