In this tutorial, we will see how we can save pandas dataframe to Excel files.

Pandas to_excel – Pandas Dataframe to Excel

The to_excel() method of pandas will save the data frame object as a comma-separated values file having a .csv extension.

Pandas to_excel Syntax:

The official documentation provides the syntax below, We will learn the most commonly used among these in the following sections with an example.

DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None,
 header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None,
merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None))

Let us look at some of the arguments to save the data-frame as an Excel file

1. Save dataframe to Excel (.xlsx) file

  • excel_writer – The path of the location where the file needs to be saved which end with the name of the file having a .xlsx extension. If only the name of the file is provided it will be saved in the same location as the script.
  • sheet_name – This will be the name of the sheet. The default name is ‘Sheet1’.
  • float_format – String representing the format for floating-point numbers.
  • columns – Names to the columns from the data to write in the file.
  • header – The name to be given to the columns when writing the file.
  • index – A boolean value which determines whether the column index to be included or not in the output file.

See the code below to save the data frame that we have created above including some of its column indexes and changing the names of columns.

data2.to_excel('fruit_modified.xlsx',sheet_name='fruit types',columns=['Fruit','Fruit Type'],
header=['fruit name','sourVSsweet'])
Output:
fruit namesourVSsweet
0LemonSour
1GrapefruitSour
2OrangeSour
3RaspberrySour
4CherrySweet
5BananaSweet
6GrapesSweet
7WatermelonSweet
8AvacadoNone
9StrawberrySour

2. Save data-frame to Excel file by changing separator

In this example, we will save the data frame as an excel file by changing the separator to comma (“,”) and saving index as a particular column from the data.

import pandas as pd
fpath = "F:/onlinetutorialspoint/Fruit.xlsx"
data2 = pd.read_excel(fpath,usecols=['Fruit','Sweetness','Soreness'],index_col='Fruit')
data2.to_csv('modified_emp_data2.csv',sep=',',index=True )
Output:
FruitSweetnessSoreness
Lemon19
Grapefruit28
Orange37
Raspberry28
Cherry64
Banana91
Grapes82
Watermelon91
Avacado11
Strawberry55

So we learned about how we can read an excel file and save a data frame as an excel file along with customizations.

References:

Happy Learning 🙂

About the Author:

Aspiring Data Scientist who loves Python Programming, Software Development and wants to Solve Real-world Problems.

Leave A Comment