Pandas DataFrame DataFrame.to_excel() Function

  1. Syntax of pandas.DataFrame.to_excel()
  2. Example Codes: Pandas DataFrame.to_excel()
  3. Example Codes: Pandas DataFrame.to_excel() With ExcelWriter
  4. Example Codes: Pandas DataFrame.to_excel to Append to an Existing Excel File
  5. Example Codes: Pandas DataFrame.to_excel to Write Multiple Sheets
  6. Example Codes: Pandas DataFrame.to_excel With header Parameter
  7. Example Codes: Pandas DataFrame.to_excel With index=False
  8. Example Codes: Pandas DataFrame.to_excel With index_label Parameter
  9. Example Codes: Pandas DataFrame.to_excel With float_format Parameter
  10. Example Codes: Pandas DataFrame.to_excel With freeze_panes Parameter

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.isin(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.

Pandas DataFrame to_excel.png

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

Pandas DataFrame to_excel - append sheet

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.

Pandas DataFrame to_excel - change header name

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.

Pandas DataFrame to_excel - set index label

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.

Pandas DataFrame to_excel - freeze_panes

Related Article - Pandas DataFrame

  • Pandas DataFrame DataFrame.query() Function
  • Pandas DataFrame DataFrame.aggregate() Function
  • comments powered by Disqus