pd.merge：内连接与外连接
使用内连接和外连接，通过共享的键列合并两个 DataFrames，并理解哪些行会被保留或删除。
pd.merge：内连接与外连接 是 CoddyKit 上的免费 Pandas & NumPy Academy 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Pandas & NumPy Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Pandas & NumPy Academy 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
What Is a Join?
A join combines two DataFrames by matching rows based on a shared key column — the same concept as SQL's JOIN clause. Pandas implements joins through pd.merge(). Unlike pd.concat() which simply stacks data, pd.merge() intelligently aligns rows from two different tables based on matching values in one or more columns.
The Shared Key Column
For a merge to work, both DataFrames need at least one column with shared values — the key column. For example, an orders table has a customer_id column, and a customers table also has a customer_id column. Merging on customer_id attaches customer details to each order row. Specify the key with the on parameter when both DataFrames share the same column name.
import pandas as pd
orders = pd.DataFrame({
'order_id': [1, 2, 3, 4],
'customer_id': [101, 102, 101, 103],
'amount': [250, 80, 320, 150]
})
customers = pd.DataFrame({
'customer_id': [101, 102, 104],
'name': ['Alice', 'Bob', 'Diana']
})Inner Join: Intersection of Both Tables
An inner join (the default) keeps only rows where the key value exists in both DataFrames. Rows in either table with no matching key in the other table are silently dropped. In the orders example, customer 103 and 104 have no match in the other table, so their rows are excluded from the inner join result.
# Inner join (default): only matching rows from both
result = pd.merge(orders, customers, on='customer_id', how='inner')
print(result)
# order_id customer_id amount name
# 0 1 101 250 Alice
# 1 3 101 320 Alice
# 2 2 102 80 Bob
# Order 4 (customer 103) dropped - no match in customers
# Diana (customer 104) dropped - no match in ordersOuter Join: Union of Both Tables
An outer join (how='outer') keeps all rows from both DataFrames. Where a row has no matching key in the other table, the missing columns are filled with NaN. This is useful when you want a complete picture of both datasets, preserving records that did not find a match.
result = pd.merge(orders, customers, on='customer_id', how='outer')
print(result)
# order_id customer_id amount name
# 0 1.0 101 250.0 Alice
# 1 3.0 101 320.0 Alice
# 2 2.0 102 80.0 Bob
# 3 4.0 103 150.0 NaN <- order with unknown customer
# 4 NaN 104 NaN Diana <- customer with no ordersMerging on Columns with Different Names
When the key column has a different name in each DataFrame, use left_on and right_on instead of on. Pandas matches rows where left_on in the left DataFrame equals right_on in the right DataFrame. Both key columns appear in the result; you can drop the redundant one afterward.
products = pd.DataFrame({
'prod_id': [10, 20, 30],
'name': ['Widget', 'Gadget', 'Doohickey']
})
order_lines = pd.DataFrame({
'item_id': [10, 10, 20],
'qty': [3, 1, 5]
})
result = pd.merge(order_lines, products,
left_on='item_id', right_on='prod_id')
print(result.drop(columns='prod_id'))
# item_id qty name
# 0 10 3 Widget
# 1 10 1 Widget
# 2 20 5 GadgetMerging on Multiple Columns
To match rows on a combination of columns (a compound key), pass a list to on. This is common when a single column is not sufficient for a unique match, such as merging on both year and region. All listed columns must match simultaneously for a row to be included in the result.
targets = pd.DataFrame({
'year': [2023, 2023, 2024],
'region': ['East', 'West', 'East'],
'target': [100, 150, 120]
})
actuals = pd.DataFrame({
'year': [2023, 2024, 2024],
'region': ['East', 'East', 'West'],
'actual': [95, 115, 80]
})
result = pd.merge(targets, actuals, on=['year', 'region'], how='inner')
print(result)
# year region target actual
# 0 2023 East 100 95
# 1 2024 East 120 115Handling Overlapping Column Names
When both DataFrames have a column with the same name (other than the key), Pandas appends suffixes to distinguish them. The defaults are _x (left) and _y (right). You can customise these with the suffixes parameter to produce more meaningful names and avoid confusion in the result.
df_a = pd.DataFrame({'id': [1, 2], 'value': [10, 20]})
df_b = pd.DataFrame({'id': [1, 2], 'value': [100, 200]})
# Default suffixes
print(pd.merge(df_a, df_b, on='id'))
# id value_x value_y
# Custom suffixes
print(pd.merge(df_a, df_b, on='id', suffixes=('_budget', '_actual')))
# id value_budget value_actualChecking for Unexpected Duplicates
A common merge pitfall is an unexpected row multiplication. If the key column has duplicate values in both DataFrames, the merge produces a cartesian product of all matching rows. Always check the row count after a merge: if it's larger than you expected, investigate duplicates in the key column with df.duplicated(subset=['key']).sum().
# Both tables have duplicate keys -> cartesian product
left = pd.DataFrame({'id': [1, 1], 'val_l': ['a', 'b']})
right = pd.DataFrame({'id': [1, 1], 'val_r': ['x', 'y']})
result = pd.merge(left, right, on='id')
print(len(result)) # 4 rows! (2x2 cartesian product)
print(result)
# id val_l val_r
# 0 1 a x
# 1 1 a y
# 2 1 b x
# 3 1 b yvalidate Parameter for Safety
Pass the validate parameter to enforce relationship constraints on the merge and raise an error if they are violated. 'one_to_one' requires unique keys in both tables, 'one_to_many' requires unique keys only in the left table, and 'many_to_one' only in the right. This is an excellent defensive coding practice that catches data quality issues early.
# Safe merge: validate that customer_id is unique in customers
try:
result = pd.merge(orders, customers,
on='customer_id',
how='inner',
validate='many_to_one')
print('Merge OK, shape:', result.shape)
except Exception as e:
print('Merge error:', e)Checking Coverage with indicator
Pass indicator=True to add a _merge column to the result that shows where each row came from: 'left_only', 'right_only', or 'both'. This is useful after an outer join to identify which records found a match and which did not, helping you audit data quality before dropping the indicator column.
result = pd.merge(orders, customers, on='customer_id',
how='outer', indicator=True)
print(result['_merge'].value_counts())
# both 3
# left_only 1 <- orders with no customer record
# right_only 1 <- customers with no orders
# Find unmatched orders
print(result[result['_merge'] == 'left_only'])Inner vs Outer Summary
To summarise the two join types: inner join gives the intersection — only rows with matching keys in both DataFrames. Outer join gives the union — all rows from both DataFrames with NaN where there is no match. For completeness: left join preserves all rows from the left DataFrame, and right join preserves all rows from the right DataFrame. These are covered in the next lesson.
# Quick reference
# how='inner' -> intersection: only matched rows
# how='outer' -> union: all rows from both, NaN for no match
# how='left' -> all left rows + matched right rows
# how='right' -> all right rows + matched left rows
# Most common: inner (default) for enrichment, left for preserving recordsQuick Check
Test your understanding of pd.merge inner and outer joins from this lesson.
Lesson Recap
In this lesson you learned: pd.merge() with how='inner' keeps only matching rows from both DataFrames, while how='outer' keeps all rows with NaN for non-matches; on specifies the shared key, while left_on/right_on handle different column names; and the indicator parameter helps audit which rows matched. Next up we explore left and right joins for preserving all rows from one side.
常见问题解答
「pd.merge：内连接与外连接」课时是免费的吗？
是的 — 「pd.merge：内连接与外连接」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Pandas & NumPy Academy 课程的其余内容，请升级到 CoddyKit PRO。 Pandas & NumPy Academy 课程共包含 4 节课。
「pd.merge：内连接与外连接」这节课中我会学到什么？
使用内连接和外连接，通过共享的键列合并两个 DataFrames，并理解哪些行会被保留或删除。 你通过在浏览器中直接运行的动手代码来练习 Pandas & NumPy Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Pandas & NumPy Academy 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Pandas & NumPy Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「pd.merge：内连接与外连接」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Pandas & NumPy Academy 课中编写并运行代码吗？
能。每节 Pandas & NumPy Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。
此课程中的所有课时
- 使用 pd.concat 堆叠 DataFrames
- pd.merge：内连接与外连接
- 左连接与右连接
- 按索引连接