Pandas to_excel: 5 Examples of Data Frame to Excel

How to write data in Excel by Pandas to_excel method

  • Pandas is a Python library for data analysis, cleaning of data, and exploring and manipulating data.
  • It has methods to read Excel files with a number of useful parameters.
  • Similarly, it has to_excel method that can be used to write data in an Excel file.
  • Pandas has data frames which is a two-dimensional data structure. Data Frames (DF) can be considered as a table, a 2-D array, or a spreadsheet.
  • Once you have the data frame, it can be stored as Excel file (.xlsx).
  • In this tutorial, we will show you examples of creating Data frames based on Lists in Python.
  • By using data frames, we will use it to create Excel files with various options by using to_excel method parameters.

An example of List to Excel by Pandas to_excel method

For this example, we created a list of employees.

Our Excel file should contain three headers in the first row

  • Employee ID
  • Employee Name
  • Employee Salary

We created a data frame by using a list and there we specified these headers – that act as the first row in the Excel sheet.

Finally, we used to_excel method to write data into an Excel Workbook.

Python program:

import pandas as pd

#Creating a list containing emoloyees data

employee_list = [  [1, "Mike", 5000],

              [2, "Michelle", 4500],

              [3, "Ben", 6000],

              [4, "Shabee", 3500],

              [5, "Mina", 3000]

            ]

df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary'])

#Save list data to an Excel file

df_emp.to_excel("employees.xlsx")

Result:

pandas-to_write Sample data image

In the above result, notice these things:

  • If employee.xlsx does not exist, to_excel will create a new file with that name.
  • It also added the first column with numbers starting at 0 index.
  • By default, data is stored in “Sheet1”. You may change this name – we will show it in an example.

How to exclude index column in the Excel file?

If you do not want to have an index column in the created Excel workbook, use the index parameter in the to_excel method of the data frame.

By default:

index=True

By assigning it a False value, it will omit the index column. See the program and output below:

import pandas as pd

#Creating a list containing emoloyees data

employee_list = [  [1, "Mike", 5000],

              [2, "Michelle", 4500],

              [3, "Ben", 6000],

              [4, "Shabee", 3500],

              [5, "Mina", 3000]

            ]

df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary'])

#To Excel file without index column

df_emp.to_excel("employees.xlsx", index=False)

Result:

pandas to_write index= False demo image in Excel

You can see that the above Excel sheet has no index column.

How to specify the sheet name in the Excel file?

Rather than using the default “Sheet1”, you may specify your own sheet name.

For that, use the sheet_name parameter in the to_excel method. The default is:

sheet_name = “Sheet1”

Let us give it the name “Employee Data”.

The code:

import pandas as pd

#Creating a list containing emoloyees data

employee_list = [  [1, "Mike", 5000],

              [2, "Michelle", 4500],

              [3, "Ben", 6000],

              [4, "Shabee", 3500],

              [5, "Mina", 3000]

            ]

df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary'])

#To Excel file with custom sheet name and without index column

df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data")

Output:

to_write-sheet-name

Create Excel file without headers

If you want to omit the header row as creating a new Excel file then use the header=False parameter as saving the file by to_excel method.

See an example below:

import pandas as pd

#Creating a list containing emoloyees data

employee_list = [  [1, "Mike", 5000],

              [2, "Michelle", 4500],

              [3, "Ben", 6000],

              [4, "Shabee", 3500],

              [5, "Mina", 3000]

            ]

df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary'])

#To Excel file with custom sheet name and without index column

df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data", header=False)

Output:

to_write-header-false

Setting the starting row in Excel sheet

You may also specify which row to start writing data – at 0 based index.

For that, use the startrow parameter. Its default value is:

startrow=0

For this example, we will set its value to 7 and see the result:

import pandas as pd

#Creating a list containing emoloyees data

employee_list = [  [1, "Mike", 5000],

              [2, "Michelle", 4500],

              [3, "Ben", 6000],

              [4, "Shabee", 3500],

              [5, "Mina", 3000]

            ]
df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary'])

#Start writing data from 8th row

df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data", startrow=7)

Result:

to_write-row-number

Specifying column number example

Similarly, starting column can also be set by using startcol parameter. Its default is:

startcol=0

We will set startcol=4 for our example below:

import pandas as pd

#Creating a list containing emoloyees data

employee_list = [  [1, "Mike", 5000],

              [2, "Michelle", 4500],

              [3, "Ben", 6000],

              [4, "Shabee", 3500],

              [5, "Mina", 3000]

            ]

df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary'])

#Start writing data 5th Column adn 4th row in Excel file

df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data", startrow=3, startcol=4)

Output:

to_write-col-number

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!