Pandas to_excel – Pandas Save Dataframe to Excel File

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 name	sourVSsweet
0	Lemon	Sour
1	Grapefruit	Sour
2	Orange	Sour
3	Raspberry	Sour
4	Cherry	Sweet
5	Banana	Sweet
6	Grapes	Sweet
7	Watermelon	Sweet
8	Avacado	None
9	Strawberry	Sour

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:

Fruit	Sweetness	Soreness
Lemon	1	9
Grapefruit	2	8
Orange	3	7
Raspberry	2	8
Cherry	6	4
Banana	9	1
Grapes	8	2
Watermelon	9	1
Avacado	1	1
Strawberry	5	5

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 🙂