Speed Up Excel Data Merging with Python: A Step‑by‑Step Pandas Guide

Introduction

Hello, I am Cui Yanfei. This article explains how to use Python to associate two Excel or CSV tables by a key field, which is faster and more reliable than Excel's VLOOKUP when handling large data volumes.

Project Goal

Implement data association between two Excel/CSV files using Python.

Preparation

Software: PyCharm

Required library: pandas

Analysis

1) How to read the CSV file?

Use pandas' read_csv function.

2) How to read the Excel file?

Use pandas' read_excel function.

3) How to merge the two tables by a key field?

Use pandas' merge() function with the key column.

4) How to save the result?

Use pandas' to_csv method.

Implementation

Step 1: Import required libraries

import pandas as pd

Step 2: Read the CSV file

# Read table 1
df1 = pd.read_csv('D:/a/1.csv', encoding='gbk')

Step 3: Read the Excel file

# Read table 2
df2 = pd.read_excel('D:/a/2.xlsx', encoding='utf-8')

Step 4: Merge and save the data

# Merge data
data = df1.merge(df2, on='姓名', left_index=False, right_index=False, sort=False)
# Save data
data.to_csv('D:/a/result.csv', encoding='gbk', index=False)

Result Showcase

Before merging – Table 1:

Before merging – Table 2:

After merging:

Conclusion

The article shows how Python can replace Excel's VLOOKUP for data merging, saving system resources and delivering higher efficiency, especially with larger datasets. Python offers many similar functions for data processing; interested readers are encouraged to explore further.