Split Pandas Dataframe into Multiple Excel Sheets Based on Index Value in Dataframe

Question

I have this dataset:

That has the column 'Product' with values 'Gas', 'Oil', and 'Water'. I want to write this dataframe to a single excel workbook with three worksheets with the 'Gas', 'Oil', and 'Water' data in those three worksheets.

I have tried:

and keep getting a type error. Any assistance is appreciated.

naturally you'll get an error, you're appending the entire dataframe to each sheet and Product has NaN values so you're getting the floating point error. — Umar.H
– Umar.H, Commented Feb 19, 2020 at 4:08
can you post next time the data frame directly and not as a picture? That would be helpful. Working on this. You did not defined sheet you want to save the data in in your code — Gius
– Gius, Commented Feb 19, 2020 at 4:09

Umar.H · Accepted Answer · 2021-01-14 11:20:30Z

3

IIUC,

you can group by product and assign this as the sheet name, whilst assigning the data into the sheet based on the aggregation.

writer = pd.ExcelWriter('Report.xlsx')

for group, data in result2.groupby('Product'):
    data.to_excel(writer,group)
writer.save()

edited Jan 14, 2021 at 11:20

answered Feb 19, 2020 at 4:10

Umar.H

23.1k8 gold badges50 silver badges94 bronze badges

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

Comments

Gius · Accepted Answer · 2020-02-19 04:23:59Z

0

This should work. Try to apply this example to your needs:

import pandas as pd

data = pd.read_excel('test.xlsx', index_col = 0)    

Gas = data.loc['Gas']  # look for index Gas
Oil = data.loc['Oil']  # look for index Oil

writer = pd.ExcelWriter("Test.xlsx", engine = 'xlsxwriter')
Gas.to_excel(writer, sheet_name = 'Gas')
Oil.to_excel(writer, sheet_name = 'Oil')
writer.save()

answered Feb 19, 2020 at 4:23

Gius

5146 silver badges15 bronze badges

Collectives™ on Stack Overflow

Split Pandas Dataframe into Multiple Excel Sheets Based on Index Value in Dataframe

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