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 that we have already created “Employee.xlsx” file.
It has five rows of employees’ data – as shown below:
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:
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:
| Value | Description |
| 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?
The above example works if you have a single sheet and data is supposed to be written there.
In 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:
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:
