Pandas pd.merge() Function

Pandas 通用函数 Pandas Common Functions

pd.merge()is a function in the Pandas library used tojoin two DataFrames by column. It is similar to the JOIN operation in SQL, and can merge two data tables into one based on one or more common columns.

This is one of the most commonly used merge methods in data analysis and processing. It is especially suitable for handling relational data, integrating related information scattered across different tables together.

Word Explanation： mergemeans "merge, combine", here it refers to merging two data tables together based on a common key.

Basic Syntax and Parameters

pd.merge()is a top-level function in the Pandas library, used to implement operations similar to JOIN in SQL.

Syntax Format

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)

Parameter Description

Parameter： left
- Type: DataFrame.
- Description: The left DataFrame, the first table participating in the merge.
Parameter： right
- Type: DataFrame.
- Description: The right DataFrame, the second table participating in the merge.
Parameter： on
- Type: String or list of strings.
- Description: The column name (key) used for joining. Both left and right DataFrames must have columns with the same name. If not specified, it will automatically find columns with the same name as the key.
Parameter： how
- Type: String ('left', 'right', 'outer', 'inner').
- Description: The merge method, similar to SQL JOIN types.'inner'indicates inner join (intersection),'outer'indicates outer join (union),'left'indicates left join (retains all records from the left table),'right'indicates right join (retains all records from the right table). The default is'inner'。
Parameter： left_on
- Type: String or list of strings.
- Description: The column name in the left DataFrame used as the join key. Used when the join key column names of the left and right tables are different.
Parameter： right_on
- Type: String or list of strings.
- Description: The column name in the right DataFrame used as the join key. Used when the join key column names of the left and right tables are different.
Parameter： suffixes
- Type: Tuple.
- Description: When the left and right tables have columns with the same name (not join keys), the suffixes used to distinguish them. Default is('_x', '_y')。

Function Description

Return Value: Returns the merged DataFrame.
Effect: Horizontally merges two DataFrames into one DataFrame according to the specified key and method.

Examples

Let us thoroughly master through a series of examples from simple to complexpd.merge()the usage of.

Example 1: Basic Usage - Inner Join Based on a Single Column

Example

import pandas as pd

# 1. Create two DataFrames
employees = pd.DataFrame({
'emp_id': [101, 102, 103, 104],
'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
'dept_id': [1, 2, 1, 2]
})

departments = pd.DataFrame({
'dept_id': [1, 2, 3],
'dept_name': ['Engineering', 'Sales', 'Marketing']
})

print("=== Employees table (employees) ===")
print(employees)

print("\n=== Departments table (departments) ===")
print(departments)

# 2. Use pd.merge() to merge based on the dept_id column (inner join, default)
result = pd.merge(employees, departments, on='dept_id')
print("\n=== pd.merge(employees, departments, on='dept_id') inner join ===")
print(result)

Expected running result:

=== 员工表 (employees) ===
   emp_id     name  dept_id
0     101    Alice         1
1     102      Bob         2
2     103  Charlie         1
3     104   Diana         2

=== 部门表 (departments) ===
   dept_id  dept_name
0        1  Engineering
1        2       Sales
2        3    Marketing

=== pd.merge(employees, departments, on='dept_id') 内连接 ===
   emp_id     name  dept_id    dept_name
0     101    Alice         1  Engineering
1     103  Charlie         1  Engineering
2     102      Bob         2       Sales
3     104   Diana         2       Sales

Code analysis:

The two tables are connected through thedept_idcolumn, which is their common column.
Inner join is used by default (how='inner'), only retaining key values present in both sides (dept_id 1 and 2), so the Marketing department with dept_id 3 is excluded.
After merging, each employee is associated with the corresponding department name.

Example 2: Left Join and Right Join

Using thehowparameter can control the merge method, retaining all records from one side.

Example

import pandas as pd

# Continue using the data above
employees = pd.DataFrame({
'emp_id': [101, 102, 103, 104],
'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
'dept_id': [1, 2, 1, 2]
})

departments = pd.DataFrame({
'dept_id': [1, 2, 3],
'dept_name': ['Engineering', 'Sales', 'Marketing']
})

# 1. Left join - retain all records from the left table
print("=== Left join (how='left') ===")
left_result = pd.merge(employees, departments, on='dept_id', how='left')
print(left_result)

# 2. Right join - retain all records from the right table
print("\n=== Right join (how='right') ===")
right_result = pd.merge(employees, departments, on='dept_id', how='right')
print(right_result)

# 3. Outer join - retain all records from both tables
print("\n=== Outer join (how='outer') ===")
outer_result = pd.merge(employees, departments, on='dept_id', how='outer')
print(outer_result)

Expected running result:

=== 左连接 (how='left') ===
   emp_id     name  dept_id    dept_name
0     101    Alice         1  Engineering
1     103  Charlie         1  Engineering
2     102      Bob         2       Sales
3     104   Diana         2       Sales

=== 右连接 (how='right') ===
   emp_id     name  dept_id    dept_name
0     101    Alice         1  Engineering
1     103  Charlie         1  Engineering
2     102      Bob         2       Sales
3     104   Diana         2       Sales
4     NaN     NaN         3    Marketing

=== 外连接 (how='outer') ===
   emp_id     name  dept_id    dept_name
0     101    Alice         1  Engineering
1     103  Charlie         1  Engineering
2     102      Bob         2       Sales
3     104   Diana         2       Sales
4     NaN     NaN         3    Marketing

Code analysis:

The left join retains all employee records from the left table (employees), and departments without a match in the right table are shown asNaN。
The right join retains all departments from the right table (departments), even if no employee belongs to that department.
The outer join retains all records from both tables, and unmatched fields are filled withNaN.

Example 3: Handling Two Tables with Different Column Names

When the join key column names of the two tables are different, you can use theleft_onandright_onparameters to specify them separately.

Example

import pandas as pd

# 1. Create two DataFrames with different column names
students = pd.DataFrame({
'student_id': [1, 2, 3, 4],
'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
'class_id': [101, 102, 101, 103]
})

classes = pd.DataFrame({
'class_id': [101, 102, 103],
'class_name': ['Class A', 'Class B', 'Class C'],
'teacher': ['Mr. Smith', 'Ms. Johnson', 'Mrs. Lee']
})

print("=== Student table ===")
print(students)

print("\n=== Class table ===")
print(classes)

# 2. Use left_on and right_on to handle different column names
result = pd.merge(students, classes, left_on='class_id', right_on='class_id')
print("\n=== Merge with different column names ===")
print(result)

Expected running result:

=== 学生表 ===
   student_id     name  class_id
0            1    Alice       101
1            2      Bob       102
2            3  Charlie       101
3            4    Diana       103

=== 班级表 ===
   class_id class_name   teacher
0       101   Class A  Mr. Smith
1       102   Class B  Ms. Johnson
2       103   Class C   Mrs. Lee

=== 不同列名时的合并 ===
   student_id     name  class_id class_name   teacher
0            1    Alice       101   Class A  Mr. Smith
1            3  Charlie       101   Class A  Mr. Smith
2            2      Bob       102   Class B  Indicator False
3            indicator column

print("\n=== indicator=True 显示合并详情 ===")
print(result_indicator)
[/mycode4]</div>
</div>

<p><strong>运行结果预期:</strong></p>
<pre>
=== 索引作为连接键 ===
   name     city  score  subject  grade
0  Alice  Beijing    85     Math      A
1    Bob   Shanghai  90     Math      B
2  Charlie   Guangzhou  95    English   A
3  Diana    Shenzhen    88    English   B

=== 多个键的合并 ===
   student_id  name  course_id  course_name  score
0           1  Alice       101     Math       90
1           1  Alice       102     English    85
2           2    Bob       101     Math       88
3           2    Bob       103     Science    92
4           3  Charlie       102     English    95

=== indicator=True 显示合并详情 ===
   name  city     merge_info
0  Alice  Beijing        both
1    Bob  Shanghai      both
2  Charlie  Guangzhou    left_only
3  Diana   Shenzhen    left_only

Code analysis:

Useleft_index=Trueandright_index=TrueYou can use the DataFrame's index as the join key.
Pass a list to theonparameter to achieve multi-key merge; all keys must match for a record to be retained.
Setindicator=Trueto add a special column showing which table each record comes from (left_only, right_only, both).

Tip: pd.merge()is suitable for column-based merge operations. If you only need to simply append data vertically (adding rows), use thepd.concat()function.

Pandas 常用函数 Pandas Common Functions

Other Extensions

Data Reading and Writing

Data Processing Core

Reference Manual

Statistics and Cases

Pandas pd.merge() Function

Basic Syntax and Parameters

Syntax Format

Parameter Description

Function Description

Examples

Example 1: Basic Usage - Inner Join Based on a Single Column

Example

Example 2: Left Join and Right Join

Example

Example 3: Handling Two Tables with Different Column Names

Example