Pandas DataFrame DataFrame.to_excel() Function
-
Syntax of
pandas.DataFrame.to_excel() -
Example Codes: Pandas
DataFrame.to_excel() -
Example Codes: Pandas
DataFrame.to_excel()WithExcelWriter -
Example Codes: Pandas
DataFrame.to_excelto Append to an Existing Excel File -
Example Codes: Pandas
DataFrame.to_excelto Write Multiple Sheets -
Example Codes: Pandas
DataFrame.to_excelWithheaderParameter -
Example Codes: Pandas
DataFrame.to_excelWithindex=False -
Example Codes: Pandas
DataFrame.to_excelWithindex_labelParameter -
Example Codes: Pandas
DataFrame.to_excelWithfloat_formatParameter -
Example Codes: Pandas
DataFrame.to_excelWithfreeze_panesParameter
Python Pandas DataFrame.to_excel(values) function dumps the dataframe data to an Excel file, in a single sheet or multiple sheets.
Syntax of pandas.DataFrame.to_excel()
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,
)
Parameters
excel_writer |
Excel file path or the existing pandas.ExcelWriter |
sheet_name |
Sheet name to which the dataframe dumps |
na_rep |
Representation of null values. |
float_format |
Format of floating numbers |
header |
Specify the header of the generated excel file. |
index |
If True, write dataframe index to the Excel. |
index_label |
Column label for index column. |
startrow |
The upper left cell row to write the data to the Excel. Default is 0 |
startcol |
The upper left cell column to write the data to the Excel. Default is 0 |
engine |
Optional parameter to specify the engine to use. openyxl or xlswriter |
merge_cells |
Merge MultiIndex to merged cells |
encoding |
Encoding of the output Excel file. Only necessary if xlwt writer is used, other writers support Unicode natively. |
inf_rep |
Representation of infinity. Default is inf |
verbose |
If True, error logs consist of more information |
freeze_panes |
Specify the bottommost and rightmost of the frozen pane. It is one-based, but not zero-based. |
Return
None
Example Codes: Pandas DataFrame.to_excel()
import pandas as pd
dataframe= pd.DataFrame({'Attendance': [60, 100, 80, 78, 95],
'Name': ['Olivia', 'John', 'Laura', 'Ben', 'Kevin'],
'Marks': [90, 75, 82, 64, 45]})
dataframe.to_excel('test.xlsx')
The caller DataFrame is
Attendance Name Marks
0 60 Olivia 90
1 100 John 75
2 80 Laura 82
3 78 Ben 64
4 95 Kevin 45
test.xlsx is created.
Example Codes: Pandas DataFrame.to_excel() With ExcelWriter
The above example uses the file path as the excel_writer, and we could also use pandas.Excelwriter to specify the excel file the dataframe dumps.
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer)
Example Codes: Pandas DataFrame.to_excel to Append to an Existing Excel File
import pandas as pd
import openpyxl
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx", mode="a", engine="openpyxl") as writer:
dataframe.to_excel(writer, sheet_name="new")
We should specify the engine as openpyxl but not default xlsxwriter; otherwise, we will get the error that xlswriter doesn’t support append mode.
ValueError: Append mode is not supported with xlsxwriter!
openpyxl shall be installed and imported because it is not part of pandas.
pip install openpyxl
Example Codes: Pandas DataFrame.to_excel to Write Multiple Sheets
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer, sheet_name="Sheet1")
dataframe.to_excel(writer, sheet_name="Sheet2")
It dumps the dataframe object to both Sheet1 and Sheet2.
You could also write different data to multiple sheets if you specify the columns parameter.
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer, columns=["Name", "Attendance"], sheet_name="Sheet1")
dataframe.to_excel(writer, columns=["Name", "Marks"], sheet_name="Sheet2")
Example Codes: Pandas DataFrame.to_excel With header Parameter
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer, header=["Student", "First Name", "Score"])
The default header in the created Excel file is the same as dataframe’s column names. The header parameter specifies the new header to replace the default one.
Example Codes: Pandas DataFrame.to_excel With index=False
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer, index=False)
index = False specifies that DataFrame.to_excel() generates an Excel file without header row.
Example Codes: Pandas DataFrame.to_excel With index_label Parameter
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer, index_label="id")
index_label='id' sets the column name of the index column to be id.
Example Codes: Pandas DataFrame.to_excel With float_format Parameter
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer, float_format="%.1f")
float_format="%.1f" specifies the floating number to have two floating digits.
Example Codes: Pandas DataFrame.to_excel With freeze_panes Parameter
import pandas as pd
dataframe = pd.DataFrame(
{
"Attendance": [60, 100, 80, 78, 95],
"Name": ["Olivia", "John", "Laura", "Ben", "Kevin"],
"Marks": [90, 75, 82, 64, 45],
}
)
with pd.ExcelWriter("test.xlsx") as writer:
dataframe.to_excel(writer, freeze_panes=(1, 1))
freeze_panes=(1,1) specifies that the excel file has the frozen top row and frozen first column.
Founder of DelftStack.com. Jinku has worked in the robotics and automotive industries for over 8 years. He sharpened his coding skills when he needed to do the automatic testing, data collection from remote servers and report creation from the endurance test. He is from an electrical/electronics engineering background but has expanded his interest to embedded electronics, embedded programming and front-/back-end programming.
LinkedIn Facebook