Excel Writer Python Separate Sheet For Each Row/Index In DataFrame

Question

I have a dataframe with 14 columns and about 300 rows. What I want to do is create an xlsx with multiple sheets, each sheet holding a single row of the main dataframe. I'm setting it up like this because I want to append to these individual sheets every day for a new instance of the same row to see how the column values for the unique rows change over time. Here is some code.

tracks_df = pd.read_csv('final_outputUSA.csv')
writer2 = pd.ExcelWriter('please.xlsx', engine='xlsxwriter')

for track in tracks_df:
    tracks_df.to_excel(writer2, sheet_name="Tracks", index=False, header=True)

writer2.save()
writer2.close()

Right now this just outputs the exact same format as the csv that I'm reading in. I know that I'm going to need to dynamically change the sheet_name based on an indexed value, I would like to have each sheet_name=df['Col1'] for each sheet. How do I output a xlsx with a separate sheet for each row in my dataframe?

Scott Boston · Accepted Answer · 2020-02-21 01:18:51Z

1

Try this:

writer2 = pd.ExcelWriter('please.xlsx', engine='xlsxwriter')
df.apply(lambda x: x.to_frame().T.to_excel(writer2, sheet_name=x['Col1'].astype('str'), index=True, header=True), axis=1)
writer2.save()
writer2.close()

answered Feb 21, 2020 at 1:18

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

1 Comment

yaboy618 Over a year ago

just now checking back on this. thank you so much for your response. this worked perfectly, i just had to do a little editing as well making sure sheet names were less then the 31 characters and replacing certain characters that aren't allowed!

Collectives™ on Stack Overflow

Excel Writer Python Separate Sheet For Each Row/Index In DataFrame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related