Exporting a Pandas DataFrame to an Excel file
Last Updated :
11 Mar, 2024
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. By default, it saves a single DataFrame to an Excel file. However, the capability to write multiple sheets can be achieved by employing an ExcelWriter object. This object requires a target file name and a sheet name for writing to the specified Excel file.
Pandas DataFrame to an Excel file
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.
Python3
import pandas as pd
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' }})
file_name = 'MarksData.xlsx'
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:
Exporting a Pandas 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.
Python3
import pandas as pd
cars_data = pd.DataFrame({ 'Cars' : [ 'BMW' , 'Audi' , 'Bugatti' ,
'Porsche' , 'Volkswagen' ],
'MaxSpeed' : [ 220 , 230 , 240 , 210 , 190 ],
'Color' : [ 'Black' , 'Red' , 'Blue' ,
'Violet' , 'White' ]})
datatoexcel = pd.ExcelWriter( 'CarsData1.xlsx' )
cars_data.to_excel(datatoexcel)
datatoexcel.close()
print ( 'DataFrame is written to Excel File successfully.' )
|
Output:
DataFrame is written to Excel File successfully.
Excel File
Example 3: Write to Multiple Sheet
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.
Python3
import pandas as pd
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' }})
file_name = 'MarksData.xlsx'
with pd.ExcelWriter(file_name) as writer:
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
Share your thoughts in the comments
Please Login to comment...