Export a Pandas Dataframe to an Excel File

Export a Pandas Dataframe to an Excel File

Samreena Aslam Nov-26, 2021 Oct-24, 2021 Pandas Pandas DataFrame
  1. Export a Pandas DataFrame Into Excel File by Using the to_excel() Function
  2. Export a Pandas DataFrame by Using the ExcelWriter() Method
  3. Export Multiple Pandas dataframes Into Multiple Excel Sheets

We will demonstrate in this tutorial how to export a pandas DataFrame to an excel file using two different ways. The first method is to export a pandas DataFrame to an excel file by calling the to_excel() function with the file name. The other method discussed in this article is the ExcelWriter() method. This method writes objects into the excel sheet and then exports them into the excel file using the to_excel function.

In this guide, we will also discuss how to add multiple Pandas dataframes into the multiple excel sheets using the ExcelWriter() method. Moreover, we have executed multiple examples on our system to explain each method in detail.

Export a Pandas DataFrame Into Excel File by Using the to_excel() Function

When we export a pandas DataFrame to an excel sheet using the dataframe.to_excel() function, it writes an object into the excel sheet directly. To implement this method, create a DataFrame and then specify the name of the excel file. Now, by using the dataframe.to_excel() function, export a pandas DataFrame into an excel file.

In the following example, we created a DataFrame named as sales_record containing Products_ID, Product_Names, Product_Prices, Product_Sales columns. After that, we specified the name for the excel file ProductSales_sheet.xlsx. We used the sales_record.to_excel() method to save all data into the excel sheet.

See the below example code:

import pandas as pd

# DataFrame Creation
sales_record = pd.DataFrame({'Products_ID': {0: 101, 1: 102, 2: 103,
                                 3: 104, 4: 105, 5: 106,
                                 6: 107, 7: 108, 8: 109},
                          'Product_Names': {0: 'Mosuse', 1: 'Keyboard',
                                   2: 'Headphones', 3: 'CPU',
                                   4: 'Flash Drives', 5: 'Tablets',
                                   6: 'Android Box', 7: 'LCD',
                                   8: 'OTG Cables' },
                          'Product_Prices': {0: 700, 1: 800, 2: 200, 3: 2000,
                                    4: 100, 5: 1500, 6: 1800, 7: 1300,
                                    8: 90},
                          'Product_Sales': {0: 5, 1: 13, 2: 50, 3: 4,
                                    4: 100, 5: 50, 6: 6, 7: 1,
                                    8: 50}})
  
# Specify the name of the excel file
file_name = 'ProductSales_sheet.xlsx'
  
# saving the excelsheet
sales_record.to_excel(file_name)
print('Sales record successfully exported into Excel File')

Output:

Sales record successfully exported into Excel File

After executing the above source, the excel file ProductSales_sheet.xlsx will be stored in the current running project’s folder.

export pandas dataframe into an excel file using the to_excel function

Export a Pandas DataFrame by Using the ExcelWriter() Method

The Excelwrite() method is also useful to export a pandas DataFrame into the excel file. First, we use the Excewriter() method to write the object into the excel sheet, and then, by using the dataframe.to_excel() function, we can export the DataFrame into the excel file.

See the example code below.

import pandas as pd

students_data = pd.DataFrame({'Student': ['Samreena', 'Ali', 'Sara', 
                                   'Amna', 'Eva'],
                          'marks': [800, 830, 740, 910, 1090],
                          'Grades': ['B+', 'B+', 'B', 
                                    'A', 'A+']})
  
# writing to Excel
student_result = pd.ExcelWriter('StudentResult.xlsx')
  
# write students data to excel
students_data.to_excel(student_result)
  
# save the students result excel
student_result.save()
print('Students data is successfully written into Excel File')

Output:

Students data is successfully written into Excel File

export pandas dataframe into an excel file using the ExcelWriter function

Export Multiple Pandas dataframes Into Multiple Excel Sheets

In the above methods, we exported a single pandas DataFrame into the excel sheet. But, using this method, we can export multiple pandas dataframes into multiple excel sheets.

See the following example in which we exported multiple dataframes separately into the multiple excel sheets:

import pandas as pd
import numpy as np
import xlsxwriter

# Creating records or dataset using dictionary
Science_subject = {
    'Name': ['Ali', 'Umar', 'Mirha', 'Asif', 'Samreena'],
    'Roll no': ['101', '102', '103', '104', '105'],
    'science': ['88', '60', '66', '94', '40']}

Computer_subject = {
    'Name': ['Ali', 'Umar', 'Mirha', 'Asif', 'Samreena'],
    'Roll no': ['101', '102', '103', '104', '105'],
    'computer_science': ['73', '63', '50', '95', '73']}

Art_subject = {
    'Name': ['Ali', 'Umar', 'Mirha', 'Asif', 'Samreena'],
    'Roll no': ['101', '102', '103', '104', '105'],
    'fine_arts': ['95', '63', '50', '60', '93']}

# Dictionary to Dataframe conversion
dataframe1 = pd.DataFrame(Science_subject)
dataframe2 = pd.DataFrame(Computer_subject)
dataframe3 = pd.DataFrame(Art_subject)

with pd.ExcelWriter('studentsresult.xlsx', engine='xlsxwriter') as writer:
    dataframe1.to_excel(writer, sheet_name='Science')
    dataframe2.to_excel(writer, sheet_name='Computer')
    dataframe3.to_excel(writer, sheet_name='Arts')

print('Please check out subject-wise studentsresult.xlsx file.')

Output:

Please check out subject-wise studentsresult.xlsx file.

export mutliple pandas dataframes into multiple sheets

Related Article - Pandas DataFrame

  • Get Pandas DataFrame Column Headers as a List
  • Delete Pandas DataFrame Column
  • Convert Pandas Column to Datetime
  • Convert a Float to an Integer in Pandas DataFrame
  • Sort Pandas DataFrame by One Column's Values
  • Get the Aggregate of Pandas Group-By and Sum