Exporting a Pandas DataFrame to an Excel file

Sometimes we need an Excel file for reporting, so as a coder we will see how to export Pandas DataFrame to an Excel file. The to_excel() function in the Pandas library is utilized to export a DataFrame to an Excel sheet with the .xlsx extension.

Syntax

# saving the excel
dataframe_name.to_excel(file_name.xlsx)

Below are different examples by which we can export our Pandas DataFrame to an Excel File.

Pandas DataFrame to Excel Using to_excel() Function

In this example, a Pandas DataFrame named marks_data is created to store information about students’ IDs, names, marks, and grades. The data is then saved to an Excel file named ‘MarksData.xlsx’ using the to_excel() function.

# importing the module
import pandas as pd

# creating the DataFrame
marks_data = pd.DataFrame({'ID': {0: 23, 1: 43, 2: 12,
                                  3: 13, 4: 67, 5: 89,
                                  6: 90, 7: 56, 8: 34},
                           'Name': {0: 'Ram', 1: 'Deep',
                                    2: 'Yash', 3: 'Aman',
                                    4: 'Arjun', 5: 'Aditya',
                                    6: 'Divya', 7: 'Chalsea',
                                    8: 'Akash'},
                           'Marks': {0: 89, 1: 97, 2: 45, 3: 78,
                                     4: 56, 5: 76, 6: 100, 7: 87,
                                     8: 81},
                           'Grade': {0: 'B', 1: 'A', 2: 'F', 3: 'C',
                                     4: 'E', 5: 'C', 6: 'A', 7: 'B',
                                     8: 'B'}})

# determining the name of the file
file_name = 'MarksData.xlsx'

# saving the excel
marks_data.to_excel(file_name)
print('DataFrame is written to Excel File successfully.')

Output:

DataFrame is written to Excel File successfully.

The Excel file is:

Export DataFrame to an Excel file Using ExcelWriter() Method

In this example, a Pandas DataFrame named cars_data is created to store information about different car models, their maximum speeds, and colors. The data is then written to an Excel file named ‘CarsData1.xlsx’ using the to_excel() function along with an ExcelWriter object, and the file is saved successfully.

# importing the module
import pandas as pd

# creating the DataFrame
cars_data = pd.DataFrame({'Cars': ['BMW', 'Audi', 'Bugatti',
                                   'Porsche', 'Volkswagen'],
                          'MaxSpeed': [220, 230, 240, 210, 190],
                          'Color': ['Black', 'Red', 'Blue',
                                    'Violet', 'White']})

# writing to Excel
datatoexcel = pd.ExcelWriter('CarsData1.xlsx')

# write DataFrame to excel
cars_data.to_excel(datatoexcel)

# save the excel
datatoexcel.close()
print('DataFrame is written to Excel File successfully.')

Output:

DataFrame is written to Excel File successfully.

Excel File

Pandas Write to Excel using with Statemenet

In this example, a Pandas DataFrame named marks_data is created to represent student information. The data is then written to an Excel file named ‘MarksData.xlsx’ with a sheet named ‘Employee’ using an ExcelWriter object, and a success message is printed.

# importing the module
import pandas as pd

# creating the DataFrame
marks_data = pd.DataFrame({'ID': {0: 23, 1: 43, 2: 12,
                                  3: 13, 4: 67, 5: 89,
                                  6: 90, 7: 56, 8: 34},
                           'Name': {0: 'Ram', 1: 'Deep',
                                    2: 'Yash', 3: 'Aman',
                                    4: 'Arjun', 5: 'Aditya',
                                    6: 'Divya', 7: 'Chalsea',
                                    8: 'Akash'},
                           'Marks': {0: 89, 1: 97, 2: 45, 3: 78,
                                     4: 56, 5: 76, 6: 100, 7: 87,
                                     8: 81},
                           'Grade': {0: 'B', 1: 'A', 2: 'F', 3: 'C',
                                     4: 'E', 5: 'C', 6: 'A', 7: 'B',
                                     8: 'B'}})

# determining the name of the file
file_name = 'MarksData.xlsx'

# creating an ExcelWriter object
with pd.ExcelWriter(file_name) as writer:
    # writing to the 'Employee' sheet
    marks_data.to_excel(writer, sheet_name='Employee', index=False)
print('DataFrames are written to Excel File successfully.')

Output:

DataFrame is written to Excel File successfully.

Employee Excel Sheet Output

Exporting a Pandas DataFrame to an Excel file – FAQs

How do I write Pandas DataFrames to an existing Excel spreadsheet?

To write to an existing Excel spreadsheet without overwriting other data, you can use the ExcelWriter object from Pandas along with the openpyxl library.

Example:

from openpyxl import load_workbook
import pandas as pd

# Create a DataFrame
df = pd.DataFrame({‘Data’: [50, 60, 70, 80]})

# Load existing workbook
book = load_workbook(‘output.xlsx’)
writer = pd.ExcelWriter(‘output.xlsx’, engine=’openpyxl’)
writer.book = book

# Write to a specific sheet
df.to_excel(writer, sheet_name=’Sheet1′, index=False, startrow=book[‘Sheet1’].max_row, header=False)

# Save the changes
writer.save()

Which is better, XlsxWriter or openpyxl?

Both XlsxWriter and openpyxl are excellent libraries for working with Excel files in Python, but they have different strengths:

• XlsxWriter: Offers faster performance and more advanced formatting and charting features. It’s better for writing data to new files.
• openpyxl: Can read and write Excel files, so it is suitable for modifying existing files. Offers more comprehensive support for reading Excel features like formulas, charts, and macros.

The choice between XlsxWriter and openpyxl often depends on the specific needs of your task (e.g., file modification versus file creation, need for advanced formatting).

How to export Pandas to Excel without index?

To export a DataFrame to Excel without including the DataFrame index, set the index parameter to False in the to_excel() method.

Example:

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({‘Name’: [‘Alice’, ‘Bob’], ‘Age’: [24, 28]})

# Export to Excel without index
df.to_excel(‘no_index_output.xlsx’, index=False)

V

vishalarya1701

Follow

Improve

Article Tags :

• Pandas
• Python
• Python pandas-dataFrame
• Python-pandas

Practice Tags :

• python