Convert dataframe in to the specific excel sheet format-CodePudding

I have the below dataframe with the column headers: S_NAME, S_ID, TYPE_NAME, TYPE_ID

AD EXTRACT,181,ABSORB,174
AD EXTRACT,181,VOLUME,107
ACD EXTRACT,129,CLAIM,136
ACD EXTRACT,129,COURSE,203
AV EXTRACT,105,BOOK,708
AV EXTRACT,105,PROTEIN,125

I need the output in this format .xlsx

Every unique S_NAME should have separate sheet in the same excel file, and each sheet should have the respective S_ID, TYPE_NAME, TYPE_ID as in the output image.

Can someone pls help on how to achieve this in Pandas. As a beginner, I tried dict but didn't help

import pandas as pd
df = pd.read_csv('input.csv', sep=',', names=['S_NAME', 'S_ID', 'TYPE_NAME', 'TYPE_ID'])
df_dict = dict(zip([i for i in df.columns] , [pd.DataFrame(df[i].unique(), columns=[i]) for i in df.columns]))

CodePudding user response：

Install openpyxl if needed and try:

with pd.ExcelWriter("output.xlsx") as writer:
    for name, frame in df.groupby("S_NAME"):
        frame.drop("S_NAME", axis=1).to_excel(writer, sheet_name=name, index=False)