CODEWITHSUNDEEP
pythonexcelpandasdataframeindexing
yaboy618
yaboy618

Reputation: 127

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

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?

Upvotes: 1

Views: 833

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

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()

Upvotes: 1

Related Questions