How to Export a Pandas Dataframe to an Excel File
-
Export a Pandas
DataFrameInto Excel File by Using theto_excel()Function -
Export a Pandas
DataFrameby Using theExcelWriter()Method -
Export Multiple Pandas
dataframesInto 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 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 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.
