How to Write Pandas DataFrame to Excel Sheet?

The Python Pandas library is utilized for data manipulation such as creating, analyzing, and removing the data. Pandas DataFrame is simply a 2-Dimensional table having rows and columns. Pandas DataFrame is commonly written on excel sheets because excel sheet data has easy integration with other tools and systems that use excel.

To write pandas DataFrame to excel sheets, the following methods are explained in this blog post using appropriate examples:

Method: Using “df.to_excel()” Function

The panda’s module provides a “df.to_excel()” function which is used to write an object to an excel sheet. Let’s understand how we can write pandas DataFrame to an excel sheet:

Note: To Generate a spreadsheet file compatible with ms excel few pre-requisite libraries, “xlwt” and “openpyxl” are required in Python. If you don’t have “xlwt” and “openpyxl” libraries installed on your Python, you can use the following command in the cmd terminal to install it before using the df.to_excel() function:

> pip install xlwt
> pip install openpyxl

Example 1: Writing Pandas DataFrame to Single Excel Sheet

In the below code, the pandas DataFrame is written on the single excel sheet:

Code:

import pandas as pd

df = pd.DataFrame([['Alex', 12, 6.7], ['Lily', 28, 6.9],
    ['Joseph', 22, 5.9]], columns=['Name', 'Age', 'Height'])
df.to_excel('ILf.xlsx',sheet_name='Information')

In the above code:

  • The “pd.DataFrame()” function is used to create the pandas DataFrame. The column name of the given DataFrame is also defined inside the DataFrame.
  • The “df.to_excel()” function will return the excel sheet by writing the given DataFrame into excel.

Output:

The above output verified that the DataFrame content had been written on the excel sheet named “ILF.xlsx”.

Example 2: Writing Pandas DataFrame to Multiple Excel Sheets

In the bellow code, the “ExcelWriter” class is used to write multiple DataFrame into multiple excel sheets:

Code:

import pandas as pd

df = pd.DataFrame([['Alex', 12, 6.7], ['Lily', 28, 6.9],
    ['Joseph', 22, 5.9]], columns=['Name', 'Age', 'Height'])

df1 = pd.DataFrame([['Katy', '12k', '6%'], ['John', '28k', '16%'],
    ['Alexa', '22k', '26%']], columns=['Employee', 'Salary', 'Bonus'])

# Write to Multiple Sheets
with pd.ExcelWriter('ILF.xlsx') as writer:
    df.to_excel(writer, sheet_name='Personal Info')
    df1.to_excel(writer, sheet_name='Business Info')

In the above code:

  • The two different pandas DataFrame “df” and “df1” are created using the “pd.DataFrame()” function.
  • The “pd.ExcelWriter()” function is used to write DataFrame to multiple excel sheets.

Output:

The above output shows that the DataFrame “df” has been written to the excel sheet successfully with the sheet name “Personal Info”.

The above output shows that the second DataFrame “df1” has been written to another excel sheet named “Business Info” successfully.

Conclusion

To write pandas DataFrame to excel sheets, the “df.to_excel()” function is used in Python. To write pandas DataFrame to multiple sheets, the “ExcelWriter” class is used in Python. The “df.to_excel()” function writes the given DataFrame into excel sheets and modifies the sheets’ names. This Python post provided the methods to write pandas DataFrame to an excel sheet with appropriate examples.