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
# 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:
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
# 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
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
# 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
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.
Contact Us