CODEWITHSUNDEEP
pythonpandasdataframexlsxwriter
Jesse_V
Jesse_V

Reputation: 72

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

I have this dataset:

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:

I have tried variations of:

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

enter image description here

Upvotes: 1

Views: 3216

Answers (2)

Umar.H
Umar.H

Reputation: 23099

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

Upvotes: 3

Gius
Gius

Reputation: 514

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

Upvotes: 0

Related Questions