Append Data in Excel by Pandas ExcelWriter / to_excel with 2 Examples

How to append new data into existing Excel file. The answer is using Pandas ExcelWriter object.

Appending data to an existing file by Pandas to_excel

As we have seen in the Pandas to_excel tutorial, every time we execute the to_excel method for saving data into the Excel file – if the file does not exist, it creates a new file and saves the data.

However, if a file exists, it overwrites the contents.

For example, consider this program:

import pandas as pd

#Creating a list containing employee’s 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")

If employees.xlsx already exists, it will overwrite and save this data frame.

How to append new data into an existing Excel file

The answer is using Pandas ExcelWriter object.

Consider, we have already created “Employee.xlsx” file.

It has five rows of employees' data – as shown below:

pandas-append-excel

Now we want to add two more employees’ information without losing the existing data.

The example below shows how with output:

import pandas as pd

#Creating new rows to be appended in list

employee_list = [  [6, "John", 5000],

                  [7, "Hashir", 6000]

            ]
df_emp = pd.DataFrame (employee_list)

#Using ExcelWriter to append data

with pd.ExcelWriter('employees.xlsx', mode="a", if_sheet_exists="overlay") as writer:

    df_emp.to_excel(writer, startrow=6, header=False, index=False)

Result:

pandas-append-ExcelWriter

How did it work?

  • In the ExcelWriter, we used append mode i.e. mode="a"
  • if_sheet_exists parameter is important. We used if_sheet_exists="overlay" which means writing content to an existing sheet without removing old contents.
  • In the to_excel method, we specified startrow=6, as we already had a record till row number 5 (See first graphic above).
  • The header is kept False because we already have a header in place.

A little about if_sheet_exists

The if_sheet_exists has the following possible values:

  • error – Raises a value error.
  • New – create a new sheet and save the data frame there.
  • Replace – Delete the contents before writing to it.
  • Overlay – already mentioned.

What if Workbook has multiple sheets?

Above example works if you have a single sheet and data is supposed to write there.

In the case, you have multiple sheets (as shown below), how to append data to the specified sheet?

The answer is using “sheet_name” parameter of to_excel.

Suppose, we have the following Workbook with three sheets:

pandas-append-multiple

You can see, our Employees sheet is the second number and is not active. Instead, the “Products” sheet is active.

The program below appends to the “Employees” sheet:

import pandas as pd

#Creating new rows to be appended in Excel sheet

employee_list = [  [6, "Nasir", 4500],

                  [7, "Anil", 5500]

            ]

df_emp = pd.DataFrame (employee_list)

#Appending data to the specified sheet

with pd.ExcelWriter('employees.xlsx', mode="a", if_sheet_exists="overlay") as writer:

    df_emp.to_excel(writer, sheet_name = "Employees", startrow=6, header=False, index=False)

Result:

pandas-append-spe-sheet