How to Convert Pandas DataFrame to Excel file

By /
Convert Python DataFrame To Excel File

Exporting the data to an Excel file is usually the most preferred and handy way to read and interpret a given set of data by any user. It is possible to export your web scraping or other collected data using python code to export to an Excel file, and that too in very simple steps, by making use of the Pandas library.

Steps to Convert Pandas DataFrame to Excel

Follow the below step-by-step tutorial to learn to write a Pandas DataFrame to an Excel File. 

Step 1: Install pandas and openpyxl

As you require to export pandas data frame, it is evident that you must be having the pandas package already installed. If not, run the following pip command to install the Pandas python package on your computer.

pip install openpyxl

Now, to work with Excel file functions in Python, you need to install the openpyxl module using the below pip command.

pip install openpyxl

You can write the DataFrame to Excel File without mentioning any sheet name. The step by step process is given below:

Step 2: Make a DataFrame

  • Import Pandas package in your python code/script file.
  • Create a dataframe of the data you wish to export and initialize the DataFrame with values for rows and columns.

Python Code: 

#import pandas package
import pandas as pd

# creating pandas dataframe
df_cars = pd.DataFrame({'Company': ['BMW', 'Mercedes', 'Range Rover', 'Audi'],
     'Model': ['X7', 'GLS', 'Velar', 'Q7'],
     'Power(BHP)': [394.26, 549.81, 201.15, 241.4],
     'Engine': ['3.0 L 6-cylinder', '4.0 L V8', '2.0 L 4-cylinder', '4.0 L V-8']})

Step 3: Create a Writer Object and Export to Excel File

  • Create an Excel Writer Object using the: ExcelWriter() method of pandas package
  • Input the name of the output excel file, to which you would like to write our DataFrame with extension. (In our example, we have named our output excel file as ‘converted-to-excel.xlsx’)
# creating excel writer object

writer = pd.ExcelWriter('converted-to-excel.xlsx')
  • Call to_excel() function on the DataFrame with the Excel Writer passed as an argument to export your data to the Excel file with the already given name and extension.
  • Save the writer object to save the Excel file
# write dataframe to excel

df_cars.to_excel(writer)

# save the excel
writer.save()
print("DataFrame is exported successfully to 'converted-to-excel.xlsx' Excel File.")

Alternate – Direct Approach

A direct approach to this is by exporting data frame directly to the Excel file, without making use of the ExcelWriter object as shown in the below code sample:

import pandas as pd
 
# creating pandas dataframe from dictionary of data
df_cars = pd.DataFrame({'Company': ['BMW', 'Mercedes', 'Range Rover', 'Audi'],
     'Model': ['X7', 'GLS', 'Velar', 'Q7'],
     'Power(BHP)': [394.26, 549.81, 201.15, 241.4],
     'Engine': ['3.0 L 6-cylinder', '4.0 L V8', '2.0 L 4-cylinder', '4.0 L V-8']})
 
#Exporting dataframe to Excel file
df_cars.to_excel("converted-to-excel.xlsx")

Output Excel File

Open the excel file, and you shall see the index, column labels, and row data written to the file.

Bonus Tips

You are not only restricted to only control the excel file name rather with python dataframe exported to an Excel file, but you also have many functionalities available for customization in the pandas package.

You can change the name of the Sheet of the excel file

df.to_excel("output.xlsx", sheet_name='Sheet_name_1')

Use Excel writer to append to an existing excel file

pd.ExcelWriter('output.xlsx', mode='a')

Other options include render Engine, start row, header, index, merge cells, encoding, and many others.

Learn more about all the options available at Pandas official documentation.

Conclusion

I hope you now understand how to export a Pandas dataframe to Excel using the different libraries at hand. Do follow AskPython for more such interesting tutorials.