CODEWITHSUNDEEP
pythonexceldataframexlsxwriter
bravopapa
bravopapa

Reputation: 389

Save each slice of a dataframe into a specific excel sheet

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

Upvotes: 2

Views: 306

Answers (3)

Jakob Guldberg Aaes
Jakob Guldberg Aaes

Reputation: 844

This is a more pandasesque way of doing the same thing as @kaispace30098, but should be faster as we do not iterate over each group.

First the code, then an explanation

with pd.ExcelWriter('one_df.xlsx') as writer:
    df.groupby('city').apply(
        lambda group: group.to_excel(writer, sheet_name=group.name)
    )
  1. We create a writer using the with... as... syntax as that will automatically close the file when we are done. Otherwise you will have to manually do it later.
  2. Since you want to group each sheet by city, we do that with .groupby('city')
  3. we then apply a function to each group with .apply() where we cast each group to the Excel write with the name of the group i.e. the name of the city.

Upvotes: 2

kaispace30098
kaispace30098

Reputation: 130

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)

Upvotes: 1

bravopapa
bravopapa

Reputation: 389

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

Upvotes: 0

Related Questions