Save each slice of a dataframe into a specific excel sheet-CodePudding

I have a dataframe which looks like this:

print(df)
    city    Year    ville   Asset_Type  Titre   psqm
0   Agadir  2010.0  Agadir  Appart  3225    6276.923077
1   Agadir  2010.0  Agadir  Maison_Dar  37  8571.428571
2   Agadir  2010.0  Agadir  Villa   107 6279.469027
3   Agadir  2011.0  Agadir  Appart  2931    6516.853933
4   Agadir  2011.0  Agadir  Maison_Dar  33  9000.000000
... ... ... ... ... ... ...
669 Tanger  2020.0  Tanger  Maison_Dar  134 13382.653061
670 Tanger  2021.0  BeniMakada  Appart  67  5555.555556
671 Tanger  2021.0  BeniMakada  Maison_Dar  4   14533.492823
672 Tanger  2021.0  Tanger  Appart  160 6148.338940
673 Tanger  2021.0  Tanger  Maison_Dar  12  13461.538462

Saving the dataframe into an excel sheet is straightforward

df.to_excel(path_to_output 'df.xlsx')

I would like to save the output of each city (column city) in a different sheet in the same excel file. How can I do that please? I am not sure if I need to create the sheets manually in advance and then loop over each one or create them on the fly (via python)?

Thanks for your help

CodePudding user response：

This code worked for me. It outputs each city in a separate excel sheet.

writer = pd.ExcelWriter(path_to_output 'Index_Output.xlsx', engine = 'xlsxwriter')
name=list(df.city.unique())
for i in range(len(df.city.unique())):    
    df[df.city==df.city.unique()[i]].to_excel(writer, sheet_name= name[i])

writer.save()
writer.close()

CodePudding user response：

loop through each unique city by using a query within dataframe, in each loop use .to_excel function

for i in range(len(df.city.unique())):
 df[df.city==df.city.unique()[i]].to_excel(path_to_output f'df{i}.xlsx')

To store into one excel with different sheets, try:

writer = pd.ExcelWriter('one_excel.xlsx',engine='xlsxwriter')

for i in range(len(df.city.unique())):
 df[df.city==df.city.unique()[i]].to_excel(writer,sheet_name=f'df{i}',startrow=0 , startcol=0)