How to Export Pandas DataFrame to an Excel File

In order to export Pandas DataFrame to an Excel file you may use to_excel in Python.

Here is a template that you may apply in Python to export your DataFrame:

df.to_excel(r'Path to store the exported excel file\File Name.xlsx', index = False)

And if you want to export your DataFrame to a specific Excel Sheet, then you may use this template:

df.to_excel(r'Path to store the exported excel file\File Name.xlsx', sheet_name='Your sheet name', index = False)

Note: you will have to install openpyxl if you get the following error:

ModuleNotFoundError: No module named ‘openpyxl’

You may then use PIP to install openpyxl as follows:

pip install openpyxl

In the next section, I’ll show you a simple example, where:

Example used to Export Pandas DataFrame to an Excel File

Let’s say that you have the following dataset about cars and their prices:

BrandPrice
Honda Civic32000
Toyota Corolla35000
Ford Focus37000
Audi A445000

The ultimate goal is to export that dataset into Excel.

But before you export that data, you’ll need to create the DataFrame in order to capture the information about the cars in Python.

You may then use the following syntax to create the DataFrame:

import pandas as pd

cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [32000,35000,37000,45000]
        }

df = pd.DataFrame(cars, columns = ['Brand', 'Price'])

print (df)

This is how the DataFrame would look like:

df.to_excel

Next, you’ll need to define the path where you’d like to store the exported Excel file.

In my case, I decided to store the exported Excel file on my desktop. I used the path below (you’ll need to adjust the path to reflect the location where you want to store the Excel file on your computer):

r‘C:\Users\Ron\Desktop\export_dataframe.xlsx’

Notice that I highlighted three components in relation to that path:

  • In yellow, I placed the ‘r’ character before the path to avoid this unicode error: SyntaxError: (unicode error) ‘unicodeescape’ codec can’t decode bytes in position 2-3: truncated \UXXXXXXXX escape
  • In blue, I specified the file name to be created. You may specify a different file name if you’d like
  • In green, I specified the file type. Since we are dealing with an Excel file, the file type is ‘.xlsx’ for more recent versions of Excel

Putting everything together, here is the full Python code to export Pandas DataFrame to an Excel file:

import pandas as pd

cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [32000,35000,37000,45000]
        }

df = pd.DataFrame(cars, columns = ['Brand', 'Price'])

df.to_excel (r'C:\Users\Ron\Desktop\export_dataframe.xlsx', index = False, header=True)

Finally, run the above code in Python, and you’ll notice that a new Excel file (called export_dataframe) would be created at the location that you specified.

Once you open the Excel file, you would see the cars information:

How to Export Pandas DataFrame to an Excel File

Note that if you wish to include the index, then simply remove “, index = False” from your code.

Export Pandas DataFrame to an Excel File using Tkinter

So far you have seen how to export your DataFrame to Excel by specifying the path name within the code.

But did you know that you could also export your file without specifying the path in the code?

You can certainly do that.

In the code below, you’ll be able to export your DataFrame to Excel using the tkinter package, which can be used to create a graphical user interface in Python.

import pandas as pd
import tkinter as tk
from tkinter import filedialog


cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [32000,35000,37000,45000]
        }

df = pd.DataFrame(cars, columns = ['Brand', 'Price'])


root= tk.Tk()

canvas1 = tk.Canvas(root, width = 300, height = 300, bg = 'lightsteelblue2', relief = 'raised')
canvas1.pack()

def exportExcel ():
    global df
    
    export_file_path = filedialog.asksaveasfilename(defaultextension='.xlsx')
    df.to_excel (export_file_path, index = False, header=True)

saveAsButtonExcel = tk.Button(text='Export Excel', command=exportExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))
canvas1.create_window(150, 150, window=saveAsButtonExcel)

root.mainloop()

Make sure to adjust the code to your desired DataFrame. Simply replace the DataFrame (that captures the ‘cars’ data) with your own tailored DataFrame.

But for the time being, if you run the above code, you’ll see the following display:

Export DataFrame to Excel

Click on the ‘Export Excel‘ button, and then save your file at your desired location.

Conclusion

You just saw how to export Pandas DataFrame to an Excel file. At times, you may need to export Pandas DataFrame to a CSV file. The concept would be quite similar in such cases.

You may also want to check the Pandas documentation for additional information about DataFrame.to_excel.