数据规整(2) -- 数据连接merge

Pandas 数据规整精讲(2)：关系型数据库连接 pd.merge 核心语法与四种关联模式全解

小象实战讲义 · Python数据分析实战

在关系型数据库（RDBMS）中，通过主外键对多张表进行 JOIN 关联查询是最核心的操作。而在 Pandas 中，pd.merge() 提供了完全等价于 SQL JOIN 的关系型数据连接引擎。无论是一对一连接（One-to-One）、一对多连接（One-to-Many）还是多对多连接（Many-to-Many），pd.merge() 都能以极其出色的性能快速完成表格关联。本节我们将全面拆解 Pandas 的四种核心连接模式（Inner、Left、Right、Outer）、同名列冲突后缀处理以及主外键映射规则。

💡 核心导读

• 四种核心连接模式（how 参数）：
  • how='inner'（默认内连接）：仅保留两表连接键重合匹配的交集记录；
  • how='left'（左连接）：保留左表全量记录，右表未匹配项填充 NaN；
  • how='right'（右连接）：保留右表全量记录，左表未匹配项填充 NaN；
  • how='outer'（全外连接）：保留两表所有记录，求连接键的并集。
• 连接键指定机制：同名列 on='key'、不同名列 left_on='k1', right_on='k2'、索引参与关联 left_index=True。
• 同名列冲突消解：suffixes=('_left', '_right') 自定义重命名。

1. 四种关系型连接模式几何图解与语法

四种连接模式的韦恩图模型与数据对齐逻辑如下：

┌─────────────────────────────────────────────────────────────┐
│                 pd.merge() 四种 JOIN 关联模式速查           │
├──────────────┬──────────────────────────────────────────────┤
│ 连接模式     │ 集合定义与业务说明                           │
├──────────────┼──────────────────────────────────────────────┤
│ Inner Join   │ 左表 ∩ 右表 (仅保留双方都匹配的有效记录)      │
│ Left Join    │ 左表全量 + 右表匹配项 (最常用，以左表为主干) │
│ Right Join   │ 右表全量 + 左表匹配项                        │
│ Outer Join   │ 左表 ∪ 右表 (双方全量并集，不丢失任何信息)   │
└──────────────┴──────────────────────────────────────────────┘

import pandas as pd

# 创建用户基础表与订单明细表
df_users = pd.DataFrame({
    "user_id": ["U101", "U102", "U103", "U104"],
    "username": ["张三", "李四", "王五", "赵六"],
    "city": ["北京", "上海", "广州", "深圳"]
})

df_orders = pd.DataFrame({
    "order_id": ["ORD_01", "ORD_02", "ORD_03", "ORD_04"],
    "user_id": ["U101", "U102", "U101", "U105"], # U105 为新注册未登记用户
    "amount": [299.0, 1580.0, 89.0, 450.0]
})

print("=== 1. 用户表与订单表原始数据 ===")
print("用户表:\n", df_users)
print("订单表:\n", df_orders)

# 1. 默认内连接 (Inner Join: 仅保留双方 user_id 重合的记录)
df_inner = pd.merge(df_users, df_orders, on="user_id", how="inner")
print("\n=== 2. 内连接 (Inner Join) ===")
print(df_inner)

# 2. 左连接 (Left Join: 保留所有用户，无订单者金额为 NaN)
df_left = pd.merge(df_users, df_orders, on="user_id", how="left")
print("\n=== 3. 左连接 (Left Join: 保留未下单用户 U103, U104) ===")
print(df_left)

2. 主外键名称不一致与同名列后缀处理

当两表的关联键名称不同时，使用 left_on 和 right_on；当两表有非关联的同名列时，使用 suffixes 区分。

# 创建员工表 (键名为 emp_id) 与 部门表 (键名为 dept_code)
df_employees = pd.DataFrame({
    "emp_id": [1, 2, 3],
    "name": ["小明", "小红", "小刚"],
    "dept_code": ["D01", "D02", "D01"],
    "update_time": ["2024-01", "2024-02", "2024-01"]
})

df_depts = pd.DataFrame({
    "department_id": ["D01", "D02", "D03"],
    "dept_name": ["技术部", "市场部", "运营部"],
    "update_time": ["2023-12", "2023-11", "2023-10"]
})

# 关联键不同名 + 同名列 update_time 添加后缀
df_merged_custom = pd.merge(
    df_employees,
    df_depts,
    left_on="dept_code",
    right_on="department_id",
    how="left",
    suffixes=("_emp", "_dept")
)

print("=== 4. 键名不同与同名列后缀自动消歧 ===")
print(df_merged_custom)

📝 动手练一练

1. 场景分析题：在电商数据分析中，若想找出“在系统注册了账号但从未下过任何订单”的沉睡用户名单，应该如何使用 pd.merge() 配合布尔筛选来实现？

   👉 点击查看参考答案

   参考答案： 使用 左连接（how='left'） 将用户表作为左表与订单表关联，然后筛选出订单号为空（order_id.isna()）的行：

   merged = pd.merge(df_users, df_orders, on='user_id', how='left')
   inactive_users = merged[merged['order_id'].isna()]

2. 编程练习：给定两张表 df1 与 df2，请以 df1 的行索引和 df2 的 'user_id' 列进行左连接合并。

   👉 点击查看参考答案

   参考答案：

   import pandas as pd
   df1 = pd.DataFrame({'val': [1, 2]}, index=['U1', 'U2'])
   df2 = pd.DataFrame({'user_id': ['U1', 'U2'], 'score': [90, 80]})
   merged = pd.merge(df1, df2, left_index=True, right_on='user_id', how='left')
   print(merged)

本章小结

• 深入掌握了 pd.merge() 在 Inner、Left、Right、Outer 四种模式下的数据关联逻辑；
• 熟练运用 on、left_on、right_on 与 left_index 灵活指定主外键；
• 掌握了使用 suffixes 优雅解决非主键同名列命名冲突的最佳实践。

📋 行动清单

• 在本地执行示例代码，观察控制台输出并记录关键指标。
• 做好准备，进入下一小节学习《数据规整(3)：数据形状重构与旋转 stack 与 unstack》！

—— 小象教研组