How to Merge Two Excel Files Using Python Pandas

This tutorial will guide you in merging two Excel files using Python’s Pandas library. Pandas is an open-source Python Data Analysis Library that provides flexible data manipulation functions.

When equipped with Excel capabilities, you can easily combine files, generate summary tables, and manipulate large datasets. If you have some basic Python knowledge, you can get started right away!

Step 1: Install Required Libraries

Before the tutorial begins, let’s ensure that we have the necessary Python libraries installed. We will need both pandas and openpyxl for this task. The Pandas library is essential for data manipulation, while openpyxl specifically helps with working on Excel files.

1
pip install pandas openpyxl

Step 2: Importing the Libraries

Once they are installed, the next step is to import these libraries into our Python script.

1
import pandas as pd

Step 3: Loading the Excel Files

The next step is to load both Excel files using the pandas read_excel() function.

1
2
data1 = pd.read_excel('file1.xlsx')
data2 = pd.read_excel('file2.xlsx')

Step 4: Merging the Excel Files

Now, we can merge our two datasets into one. We’ll use the concat() function from pandas which can combine DataFrames along a particular axis.

1
merged_data = pd.concat([data1, data2])

Step 5: Writing the Merged Data to an Excel File

Lastly, we can write our merged data back into a new Excel file using the pandas to_excel() function.

1
merged_data.to_excel('merged_file.xlsx', index=False)

Here is the full Python code:

Python Code:

1
2
3
4
5
6
7
8
9
10
11
import pandas as pd
 
# Load the excel files
data1 = pd.read_excel('file1.xlsx')
data2 = pd.read_excel('file2.xlsx')
 
# Merge the files
merged_data = pd.concat([data1, data2])
 
# Write the merged data to a new Excel file
merged_data.to_excel('merged_file.xlsx', index=False)

Conclusion

Merging Excel files with Python and Pandas simplifies the typically tedious task of manual data manipulation within Excel.

This skill can be a powerful tool for any data scientist or anyone who works with data on a regular basis. Practice with your own datasets, and explore the other data management capabilities offered by the Pandas library.