merge、concat 与连接检查

计算机基础 · pandas

导出进度导入备份

前置知识：聚合、groupby 与 transform

本章目录

• 本章学习目标
• 先说清每行的含义
• 小数据：为什么两行变四行
• 把假设变成可运行检查
• concat 与连接的区别
• 应用到练习和面试
• 版本与复现约定
• 交互演示：观察机制
• 完整示例与实际输出
• 五道自测：先回答，再核对
• 两项动手任务
• 官方资料与掌握状态

本章学习目标

• 用数据粒度预测连接行数
• 使用 validate 和 indicator 审核连接
• 区分 pandas 空键匹配与 SQL NULL 语义

前置知识：聚合、groupby 与 transform

先说清每行的含义

连接的目的，是把同一个实体的不同信息放到一起。用户表每行是一位用户，订单表每行是一张订单，订单明细表每行是一项商品。这三个粒度不能直接混为一谈。给订单补充用户渠道时，以订单为左表、用户为右表，使用 user_id 连接。因为一位用户可以有多张订单，而用户表应该每个 user_id 只出现一次，所以这是多对一关系。成功连接后订单行数不变，渠道列增加。若直接连接订单明细，订单金额会在每条商品上重复，此后求和便可能把一张订单计算多次。开始写代码前，先写下左右表粒度、连接键和预期关系；这句话就是连接的检查标准。

左连接保留左表全部行，没有匹配的右侧字段为空；内连接只保留能够匹配的组合；全外连接保留两侧的键。选择方法取决于分析对象：计算全体注册用户的购买比例，应从用户表开始左连接订单汇总，否则无订单用户从分母消失。

小数据：为什么两行变四行

假设左表的键为一、一，数值为十、二十；右表的键同样为一、一，数值为五、六。连接枚举每个匹配组合，产生十配五、十配六、二十配五、二十配六共四行。它并没有帮你选一个最合适的记录，也不是按行位置逐项拼接。一般来说，一个键左边有若干行、右边也有若干行，结果就是这两个数量的乘积。

在交互面板调整重复数之前，先用纸笔预测行数，再核对结果。示例额外放入双方都为空的一行，pandas 会让两个空键匹配，因此总行数是五；MySQL 使用等号连接时 NULL 不与 NULL 匹配，对应普通键结果为四。需要模拟 SQL 等值内连接时，先分别移除空键，再连接。左连接则保留左侧空键，把右侧空键排除，这样左侧空行仍以未匹配的形式保留。

把假设变成可运行检查

validate='many_to_one' 要求右表连接键唯一；validate='one_to_one' 要求两侧唯一。发现重复时应报错调查，不要把参数改成 many_to_many 就算修好。indicator=True 添加来源列，区分 both、left_only 与 right_only。用它发现用户键不存在、类型不同或清洗时丢失的记录。示例先检查用户主键，再连接非空用户订单，断言连接前后行数相同且都找到用户。空 user_id 订单留在订单总账里，在用户归因分析中单独报告，不能悄悄改成某一位用户。

连接以后复核行数、主键唯一性和关键金额总和。金额有退款时，净金额按 amount-refund_amount 计算，先过滤 paid/refunded，再按订单粒度核对；连接前后总额应一致。金额相等不代表所有列都正确，还要查看若干未匹配键和边界日期。

concat 与连接的区别

concat(axis=0) 把相同结构的多批记录向下堆叠，ignore_index=True 重新编号，原来的索引不再承担业务键职责。它不会自动去重，也不会按 user_id 判断两行是否属于同一对象。concat(axis=1) 按索引标签横向对齐，如果索引不是相同实体的标识，结果会产生错位或空值。需要按照业务键取字段时优先 merge，并显式声明 on，避免依赖双方同名列的隐式推断。

常见反例是每天读一批数据就在循环中 concat 到越来越大的表，造成反复复制；先把每日表放入列表，再一次拼接。另一个反例是连接后删掉重复 user_id，这只是任意丢弃合法订单，不能恢复被放大的总额。正确做法通常是先把右表聚合到所需粒度，再验证唯一性后连接。

应用到练习和面试

运行脚本会生成每用户订单计数，先按用户聚合订单，再左连接全部用户，最后把没有订单的计数填成零。这个零是定义明确的无订单事实；用户国家缺失则不能用零替代。面试中解释连接时，应说明保留对象、键是否唯一、空键策略、重复键的处理理由和检查方法。

做练习时记录三个数字：输入左表行数、输入右表行数、结果行数。若结果比预期大，按键分别计算出现次数，找到乘积最大的键；若比预期小，查看内连接被排除的键。对于慢变维表，例如渠道历史，需要日期区间条件或近邻时点连接，单纯 user_id 连接不足以保证信息时点正确。课程中的用户表是静态维表，不能把这一简化无条件搬到生产历史分析中。

版本与复现约定

本课程 pandas 示例以 2.3.3 为验证基线，页面运行记录给出实际版本。pandas 3.x 默认使用 Copy-on-Write：从表中取出的对象发生修改时，不据此假定原表同步变化。跨版本都应使用一次完整的 loc 赋值表达式；需要独立工作表时显式 copy。阅读网上的旧示例时，先核对版本、输入类型和索引，再判断结果能否复现。

交互演示：观察机制

完整示例与实际输出

下载 pd-merge.py ↓复制代码

from pathlib import Path
import pandas as pd
import numpy as np
from _shared import load
ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / "outputs"
OUT.mkdir(exist_ok=True)
users = load("users")
orders = load("orders")
assert users["user_id"].is_unique
# 没有订单的用户仍然出现在用户分析中。
valid = orders.loc[orders["user_id"].notna()].copy()
joined = valid.merge(users, on="user_id", how="left", validate="many_to_one", indicator=True)
assert len(joined) == len(valid)
assert joined["_merge"].eq("both").all()
summary = valid.groupby("user_id").size().rename("order_count").reset_index()
per_user = users.merge(summary,on="user_id",how="left",validate="one_to_one")
per_user["order_count"] = per_user["order_count"].fillna(0).astype("int64")
assert len(per_user) == len(users)
left = pd.DataFrame({"k":[1,1,None],"left_value":[10,20,30]})
right = pd.DataFrame({"k":[1,1,None],"right_value":[5,6,7]})
demo = left.merge(right,on="k",how="inner")
assert len(demo) == 5  # 2×2 个普通键匹配，加 1 个空键匹配。
try:
    left.merge(right,on="k",validate="many_to_one")
except pd.errors.MergeError:
    print("重复键已被 validate 拦截")
else:
    raise AssertionError("应该检测出右表重复键")
sql_equal_join = left.dropna(subset=["k"]).merge(right.dropna(subset=["k"]),on="k")
assert len(sql_equal_join) == 4
per_user.to_csv(OUT / "pd_merge_users.csv",index=False)
print("非空订单连接行数:",len(joined))
print("每用户订单数:")
print(per_user[["user_id","order_count"]].to_string(index=False))
print("普通 pandas 连接 / SQL 等值语义:",len(demo),len(sql_equal_join))

实际运行通过CPU Python execution.

重复键已被 validate 拦截
非空订单连接行数: 59
每用户订单数:
 user_id  order_count
       1            3
       2            3
       3            3
       4            3
       5            3
       6            3
       7            3
       8            3
       9            3
      10            3
      11            3
      12            2
      13            2
      14            2
      15            2
      16            2
      17            2
      18            2
      19            2
      20            2
      21            2
      22            2
      23            2
      24            2
      25            0
      26            0
      27            0
      28            0
      29            0
      30            0
普通 pandas 连接 / SQL 等值语义: 5 4

五道自测：先回答，再核对

01 · 订单补充唯一用户属性是什么连接关系？

先写自己的答案

查看提示

比较每个 user_id 在两表的次数。

查看参考答案

多对一；订单可以重复 user_id，右侧用户表必须唯一。

02 · 左右同一键分别出现 2、3 次会得到几行？

先写自己的答案

查看提示

枚举配对。

查看参考答案

六行；每条左记录与三条右记录配对。

03 · pandas 两侧空键会怎样？

先写自己的答案

查看提示

与 SQL 等号连接比较。

查看参考答案

pandas merge 可以匹配空键；SQL NULL= NULL 不为真。模拟 SQL 语义时必须显式处理空键。

04 · 没有订单的注册用户如何保留？

先写自己的答案

查看提示

分析分母是什么？

查看参考答案

先按 user_id 汇总订单，再从全部用户左连接汇总，订单计数缺失填零。

05 · validate 报错后改为 many_to_many 是否完成修复？

先写自己的答案

查看提示

检查原来的业务关系。

查看参考答案

没有。应调查重复键来源，按业务规则聚合或去重，再维持原本的关系断言。

两项动手任务

任务 1 · 运行用户订单连接，保存全体用户的订单数并核对零订单用户。

提示

先汇总订单，再左连接用户表。

完整参考解答与检查方式

from pathlib import Path
import pandas as pd
import numpy as np
from _shared import load
ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / "outputs"
OUT.mkdir(exist_ok=True)
users = load("users")
orders = load("orders")
assert users["user_id"].is_unique
# 没有订单的用户仍然出现在用户分析中。
valid = orders.loc[orders["user_id"].notna()].copy()
joined = valid.merge(users, on="user_id", how="left", validate="many_to_one", indicator=True)
assert len(joined) == len(valid)
assert joined["_merge"].eq("both").all()
summary = valid.groupby("user_id").size().rename("order_count").reset_index()
per_user = users.merge(summary,on="user_id",how="left",validate="one_to_one")
per_user["order_count"] = per_user["order_count"].fillna(0).astype("int64")
assert len(per_user) == len(users)
left = pd.DataFrame({"k":[1,1,None],"left_value":[10,20,30]})
right = pd.DataFrame({"k":[1,1,None],"right_value":[5,6,7]})
demo = left.merge(right,on="k",how="inner")
assert len(demo) == 5  # 2×2 个普通键匹配，加 1 个空键匹配。
try:
    left.merge(right,on="k",validate="many_to_one")
except pd.errors.MergeError:
    print("重复键已被 validate 拦截")
else:
    raise AssertionError("应该检测出右表重复键")
sql_equal_join = left.dropna(subset=["k"]).merge(right.dropna(subset=["k"]),on="k")
assert len(sql_equal_join) == 4
per_user.to_csv(OUT / "pd_merge_users.csv",index=False)
print("非空订单连接行数:",len(joined))
print("每用户订单数:")
print(per_user[["user_id","order_count"]].to_string(index=False))
print("普通 pandas 连接 / SQL 等值语义:",len(demo),len(sql_equal_join))

任务 2 · 给出保留左侧空键、但不匹配右侧空键的 SQL 等值左连接。

提示

只能排除右表空键，不能删除左表空键。

完整参考解答与检查方式

from pathlib import Path
import pandas as pd
import numpy as np
from _shared import load
ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / "outputs"
OUT.mkdir(exist_ok=True)
left = pd.DataFrame({"k":[1,None],"v":[10,20]})
right = pd.DataFrame({"k":[1,None],"name":["known","null-row"]})
result = left.merge(right.loc[right["k"].notna()],on="k",how="left",validate="many_to_one")
assert len(result) == 2
assert result.loc[result["k"].isna(),"name"].isna().all()
print(result.to_string(index=False))

官方资料与掌握状态

• pandas 合并与连接
• merge API

完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。

动手任务记录

记录两项任务的方法、检查结果和卡点

掌握状态与复习

关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。

A · 独立完成B · 提示后会C · 需重学待学

记录尽量保存在当前浏览器，建议定期导出备份。