1

Assume that I have a dataframe of 8000 rows x 7 columns.

Overview of the data

data = pd.read_excel('tmp.xlsx')
data.head(10)

ID          Type    CatID   Val1    val2    Comment      Disposition
20192658     N       52     256     358     Processing    In Progress
20192658     N       53     258     365     repairing     In Progress
20192658     N       53     258     365     Processed     Resloved
20192753     K       98     324     347     Processing    In Progress
20192753     K       87     365     378     Repairing     In Progress
20192753     K       84     357     364     Processed     Resolved
20192854     J       98     324     347     Processing    In Progress
20192854     J       87     365     378     Repairing     In Progress
20192854     J       84     357     364     Processed     Resolved
20192789     M       96     369     345     Processing    In Progress

There are around 197 unique ID's in the dataframe.

I'm trying to export the data to Excel with each ID to a separate sheet.

As of now I'm manually filtering the data and appending to each sheet. Is there any simple and better approach in doing soo in python?

Required Output enter image description here enter image description here

Improve this question
0

2 Answers 2

Reset to default
2

Given your dataframe data:

  • Iterates through each unique ID and slices the dataframe for those values, and then saves to the file.
import pandas as pd

with pd.ExcelWriter('data.xlsx') as writer:
    for i, value in enumerate(data.ID.unique(), start=1):
        data[data.ID == value].to_excel(writer, index=False, sheet_name=f'Sheet{i}')
  • I think you would be better off to name each sheet the ID value instead of Sheet1, etc. e.g. sheet_name=f'{value}' 
import pandas as pd

with pd.ExcelWriter('data.xlsx') as writer:
    for value in data.ID.unique():
        data[data.ID == value].to_excel(writer, index=False, sheet_name=f'{value}')
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You would need to separate the sections of the file you want in each sheet and run the ExcelWriter method using a with statement

output_name = ' '
with pd.ExcelWriter(output_name) as writer:
    data.iloc[0:4].to_excel(writer, sheet_name='my_sheet1')
    data.iloc[4:8].to_excel(writer, sheet_name='my_sheet2')

The syntax for slicing the output of the read_excel method may not be correct but the to_excel is a method of DataFrame as long as you get the desired sections of the object data, this should work.

Improve this answer

Comments