How To Append Dataframe To Existing Excel File In Python

Appending data to an existing Excel file is a common task in data analysis. In Python, it can be easily done with the help of some libraries.

Here’s a tutorial on how to append a dataframe to an existing Excel file in Python:

Example Files:

Excel

CSV

Column 1,Column 2,Column 3
Value 10,Value 11,Value 12
Value 13,Value 14,Value 15
Value 16,Value 17,Value 18

Step 1: Import the Required Libraries

To start, we need to import the libraries we need. We will be using the pandas and openpyxl libraries for this task:

1
2
import pandas as pd
from openpyxl import load_workbook

Step 2: Load the Existing Excel File

Next, we need to load the existing Excel file to which we want to append data. We can do this using the Load Workbook function of the openpyxl library as shown below:

1
2
3
4
existing_excel_file = 'existing_file.xlsx'
book = load_workbook(existing_excel_file)
writer = pd.ExcelWriter(existing_excel_file, engine='openpyxl')
writer.book = book

This code will load the existing Excel file and prepare it for writing.

Step 3: Load the DataFrame to be Appended

We also need to load the DataFrame that we want to append to the existing Excel file. We can do this using the pandas library:

1
df = pd.read_csv('data.csv')

This code will load the DataFrame that we want to append.

Step 4: Append the DataFrame to the Existing Excel File

Finally, we can append the DataFrame to the existing Excel file using the pandas to_excel function:

1
2
df.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

This code will add the DataFrame to the ‘Sheet1’ sheet of the existing Excel file and save the changes.

Conclusion

Appending a DataFrame to an existing Excel file in Python is a simple task that can be done using the libraries pandas and openpyxl. By following the steps outlined above, you should be able to easily append data to an existing Excel file.

Here’s the full code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import pandas as pd
from openpyxl import load_workbook
 
existing_excel_file = 'existing_file.xlsx'
book = load_workbook(existing_excel_file)
 
# Read the existing data from the Excel file
existing_df = pd.read_excel(existing_excel_file, sheet_name='Sheet1')
 
# Read the new data from the CSV file
new_df = pd.read_csv('data.csv')
 
# Concatenate the new DataFrame with the existing DataFrame
concatenated_df = pd.concat([existing_df, new_df], ignore_index=True)
 
# Remove the existing sheet
if 'Sheet1' in book.sheetnames:
    book.remove(book['Sheet1'])
 
# Create a new ExcelWriter with the modified book and write the concatenated DataFrame
writer = pd.ExcelWriter(existing_excel_file, engine='openpyxl', mode='w')
writer.book = book
concatenated_df.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

Output: