Excel 处理一万行,SQL 处理千万行,但它们都要求你每次把步骤重写一遍。
pandas 把“读数据 → 筛选 → 聚合 → 关联 → 对账”固化成一段可重复执行的脚本,
今天跑和明天跑,结果必须一样。
本章只讲三件心智模型:Series / DataFrame / Index、向量化、视图与副本;
然后用 8 步把第 16 章的三道 SQL 重写一遍。所有代码和输出都在 pandas 3.0.5 下实跑,
你可以逐行对照。
pandas 的 API 很多,但真正决定你会不会用错的,只有三个模型:数据长什么样、 计算怎么跑、修改落在谁身上。
Series 是带标签的一维数组:一列数,每个值有一个行标签。DataFrame 是共享同一组行标签的 Series 集合:你可以把它想成“字典的字典”,也可以想成“带行标签的 Excel 表”。 Index 就是那组行标签,它不是行号,也不保证从 0 开始、更不保证唯一。
| Index | order_id | channel | pay_amount |
|---|---|---|---|
| 0 | O23101400001 | 信息流 | 8,503.32 |
| 2 | O23070100003 | 自然搜索 | 4,483.33 |
| 5 | O23031100006 | 自然搜索 | 17,257.24 |
最容易踩的坑是:筛选之后 Index 保留原值。已支付订单的前三行标签是 0、2、5,
不是 0、1、2。此时 paid.iloc[1] 取的是“第二行”,订单号是 O23070100003;
而 paid.loc[1] 会直接报 KeyError,因为标签 1 不在结果里。
第二个坑是按标签对齐。两个 Series 相加,pandas 先对齐 Index,再相加; 标签对不上就产生缺失值,而不是按位置硬加。
还有一个形状差异:df["pay_amount"] 返回 Series,df[["pay_amount"]]
返回只有一列的 DataFrame。前者 .shape 是 (n,),后者是 (n, 1);
传给只接受二维数据的函数时,少一层中括号就会报错。
a = pd.Series([1, 2], index=["a", "b"])
b = pd.Series([10, 20], index=["b", "c"])
print(a + b)
a NaN b 12.0 c NaN dtype: float64
第三个变化来自 pandas 3.0:文本列默认是 str 类型,不再是 object。
这带来更好的缺失值处理和内存占用,但也意味着你不能再用“object 就是文本”来判断列类型。
读进来先看 dtypes,比背 API 有用。
拿到 DataFrame,先确认三件事:多少行多少列、每列什么类型、Index 是什么。 这三件事决定了后面能不能筛、能不能算、结果对不对齐。
如果不这样会怎样:把 order_date 当日期直接做减法,
pandas 会报 TypeError;因为它读进来是 str,
必须先 pd.to_datetime。
pandas 的列底层是连续数组,sum、mean、布尔筛选、字符串截取
都由 C 或 NumPy 在整列上完成。你写一行 paid["pay_amount"].sum(),
实际发生的是“一次遍历整列”,而不是 15,615 次 Python 函数调用。
用 for 循环也能得到同样的结果,但每一行都要经过 .iloc 定位、类型检查、
Python 对象装箱。20,000 行还能忍,200 万行就会从“几秒”变成“几十分钟”。
字符串和日期也有整列方法:orders["order_date"].str[:7] 一次截出年月,
日期用 .dt.year、.dt.month,都比 apply(lambda ...) 快,
也更不容易写错边界。
total = 0
for i in range(len(paid)):
total += paid.iloc[i]["pay_amount"]
total = paid["pay_amount"].sum()
数字用 sum/mean/round,字符串用 .str,日期用 .dt,
分组用 groupby,关联用 merge。这些都比 apply 快,
也比手写循环更难写错。
如果不这样会怎样:用 apply 逐行判断状态,20,000 行要多跑几百毫秒;
换成 orders["status"] == "已支付",一次布尔运算就完成,代码还更短。
pandas 里最经典的一条警告叫 SettingWithCopyWarning。它要提醒的只有一件事:
你正在改的,可能是一个临时副本,不是原始 DataFrame。
老版本 pandas 里,df[df.a > 1]["b"] = 0 这种链式赋值有时会改到原表,
有时不会,所以它给一条警告。pandas 3.0 启用了 Copy-on-Write(CoW)并默认不可关闭:
链式赋值永远不写回原表,并且会抛 ChainedAssignmentError 警告。
行为确定了,但“改错对象”的坑还在。
sub = orders[orders["status"] == "已支付"]
sub["pay_amount"][0] = 0 # 警告,且不会改 orders
sub.loc[sub.index[0], "pay_amount"] = 0 # 只改 sub
orders.loc[0, "pay_amount"] = 0 # 才改原表
ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series through chained assignment.
.copy() 也不是万能药。只在“我要修改这个子集”时复制;如果只是只读分析,
不复制更省内存,CoW 会把真正的复制推迟到写入那一刻。判断标准是:还要不要原始表?
要,就复制一份再改;不要,才可以直接改子集。
sub["pay_amount"][0] = 0
看起来改了,实际只改了一个临时对象;导出时数字没变。
sub.loc[sub.index[0], "pay_amount"] = 0
行和列一次定位,明确改的是 sub,不会碰 orders。
ChainedAssignmentError。结论没变——用 .loc。
.loc[行条件, 列名] = 值 是唯一推荐的赋值方式。它一次定位行和列,
不会产生中间对象,也不依赖视图还是副本。
如果不这样会怎样:用 paid["pay_amount"][0] = 0 改一笔异常订单,
程序不报错、导出也不报错,但那一行还是原值;等你发现时,报表已经发出去三天了。
你不需要从零学一套新逻辑。SQL 的每个子句,在 pandas 里几乎都有对应写法; 差别主要在“写给优化器”还是“写给内存”。
| SQL | pandas | 注意 |
|---|---|---|
SELECT a, b | df[["a", "b"]] | 双中括号返回 DataFrame,单中括号返回 Series |
WHERE status = '已支付' | df[df["status"] == "已支付"] | 多个条件用 &,每个条件加括号 |
GROUP BY channel | df.groupby("channel") | 默认把分组键变成 Index |
SUM / COUNT / AVG | .agg(gmv="sum", n="count") | 命名聚合直接给结果列起名 |
HAVING SUM(...) > 100 | 先 groupby 再按结果列筛 | 筛的是聚合后的行,不是明细行 |
ORDER BY gmv DESC | df.sort_values("gmv", ascending=False) | 默认把 NaN 放最后 |
LIMIT 10 | df.head(10) | 排序后取前 10 才有意义 |
JOIN ... ON user_id | df.merge(other, on="user_id", how="left") | 先预测行数,再看结果 |
UNION ALL | pd.concat([df1, df2]) | 按列名对齐,不是按位置 |
ROW_NUMBER() OVER (...) | df.groupby(...).cumcount() + 1 | 或用 rank / sort_values |
COUNT(DISTINCT user_id) | df["user_id"].nunique() | 缺失值默认不计数 |
CASE WHEN a > 0 THEN ... | np.where(cond, x, y) 或 .loc | 多分支用 np.select |
COALESCE(a, 0) | df["a"].fillna(0) | 只填缺失,不改类型 |
两者最大的差别不是语法,而是执行模型。SQL 把“要什么”交给优化器,数据库决定走哪个索引、 先 JOIN 还是先过滤;pandas 把“怎么算”交给你,数据全在内存里,顺序、类型、副本都得自己管。 所以 SQL 里不用担心的行数翻倍,在 pandas 里必须手动验证。
缺失值也要显式处理。SQL 的 NULL 在 pandas 里是 NaN 或 NaT;
groupby 默认丢弃空键,merge 左关联也会留下空值。写脚本时要先决定:
丢弃、填零,还是单独归为“未知”,不能等结果出来再猜。
WHERE 对应布尔筛选,GROUP BY 对应
groupby,JOIN 对应 merge。先把这三件事映射对,
剩下的只是函数名。
我们用第 16 章的三道典型查询做对照:分渠道 GMV、月度 GMV、按城市 GMV。 每一步都给出 pandas 代码和真实输出;你可以把输出和 SQL 结果逐项核对。
| 项目 | 要求 |
|---|---|
| Python | 3.11 及以上 |
| pandas | 3.0.5(本章输出均在此版本下生成) |
| 编辑器 | VS Code、Jupyter Notebook 或任何 Python 环境 |
| 数据 | ../data/csv/orders.csv、users.csv、order_items.csv(UTF-8 BOM) |
book/chapters/ 下”写相对路径。
如果你的脚本在别处,把 ../data/csv/ 改成实际路径即可。
orders.csv,确认行数、列数、每列类型,并看前三行。str,日期列读进来仍是字符串,不转换就不能按月聚合。read_csv、shape、dtypes,再打印前三行。order_date 是 str,pay_amount 是 float64。import pandas as pd
orders = pd.read_csv("../data/csv/orders.csv")
print(orders.shape)
print(orders.dtypes)
print(orders.head(3).to_string(index=False))
(20000, 9)
order_id str
user_id str
order_date str
status str
channel str
amount float64
discount float64
pay_amount float64
city str
dtype: object
order_id user_id order_date status channel amount discount pay_amount city
O23101400001 U001299 2023-10-14 18:07:25 已支付 信息流 8553.32 50.00 8503.32 上海
O24032900002 U001020 2024-03-29 11:52:50 已取消 自然搜索 7480.48 270.73 7209.75 西安
O23070100003 U000870 2023-07-01 23:09:13 已支付 自然搜索 4533.33 50.00 4483.33 武汉
第一列变成 Unnamed: 0:文件里带了索引列,或者你误用了 index_col=0。先看原始 CSV 第一行。
中文乱码:文件不是 UTF-8。用 encoding="gbk" 试;本章 CSV 是 UTF-8 BOM,read_csv 默认能处理。
.copy(),否则后续赋值可能落到副本上。口径上,GMV 只算已支付,且用 pay_amount。len 和 sum 做两个硬校验。print(orders["status"].value_counts().to_string())
paid = orders[orders["status"] == "已支付"].copy()
print(len(paid), round(paid["pay_amount"].sum(), 2))
status 已支付 15615 已取消 1924 待支付 1258 已退款 1203 15615 157363380.14
如果结果是 20,000 行,说明条件没生效;检查是不是写成了赋值 =,而不是比较 ==。中文状态值要加引号。
groupby + 命名聚合;占比用整列除以总额;最后 sort_values。# SQL: SELECT channel, SUM(pay_amount) AS gmv, COUNT(*) AS orders
# FROM orders WHERE status = '已支付'
# GROUP BY channel ORDER BY gmv DESC
by_channel = (paid.groupby("channel")["pay_amount"]
.agg(gmv="sum", orders="count")
.sort_values("gmv", ascending=False))
by_channel["share"] = (by_channel["gmv"] / paid["pay_amount"].sum() * 100).round(2)
print(by_channel.to_string())
gmv orders share channel 自然搜索 63215624.36 6281 40.17 信息流 47042514.15 4678 29.89 朋友推荐 32164193.80 3154 20.44 线下 14941047.83 1502 9.49
结果只有一列:agg 里没写列名映射,检查是不是漏了 gmv="sum"。四个占比加起来应是 100.00,差 0.01 是四舍五入。
order_date 转成月份,聚合 2024 年 1–6 月的 GMV 和订单数。order_date 读进来是字符串,必须先 to_datetime;直接截字符串看似能用,但跨年、格式不一致或做日期运算时就会出错。pd.to_datetime 转日期,再用 .dt.strftime("%Y-%m") 生成月份列;分组后取后 6 行。paid["month"] = pd.to_datetime(paid["order_date"]).dt.strftime("%Y-%m")
by_month = paid.groupby("month")["pay_amount"].agg(gmv="sum", orders="count")
print(by_month.tail(6).round(2).to_string())
gmv orders month 2024-01 9085918.82 876 2024-02 8345370.02 810 2024-03 9033174.82 893 2024-04 9331840.17 924 2024-05 8765340.49 864 2024-06 9015366.64 881
如果 to_datetime 报错,先看有没有空值或异常格式;加 errors="coerce" 会把无法解析的值变成 NaT,再统计缺失数量。
user_id 左关联,按用户所在城市汇总 GMV。user_id 唯一,订单表一个用户多单,所以是“多对一”,行数应保持 15,615 不变。两张表都有 city,必须先改名,否则会出现 city_x / city_y。city 改名为 user_city;用 how="left" 保留游客订单;加 validate="many_to_one" 让 pandas 帮你检查右表重复。users = pd.read_csv("../data/csv/users.csv")
u = users.rename(columns={"city": "user_city", "channel": "user_channel"})
wide = paid.merge(u[["user_id", "user_city", "member_level"]],
on="user_id", how="left", validate="many_to_one")
print(len(wide), wide["user_city"].isna().sum())
print(wide.groupby("user_city")["pay_amount"].sum()
.sort_values(ascending=False).round(2).to_string())
print(round(wide["pay_amount"].sum(), 2))
print(round(wide.loc[wide["user_city"].isna(), "pay_amount"].sum(), 2))
15615 439 user_city 上海 21313376.40 北京 20472336.02 西安 19756708.45 成都 19520044.03 广州 18819682.17 武汉 17994411.16 深圳 17854031.46 杭州 17358388.53 157363380.14 4274401.92
行数变多:用户表有重复 user_id。validate="many_to_one" 会直接报错,先对用户表去重。
城市列变成 city_x / city_y:两张表都有同名列,merge 自动加后缀。提前改名最清楚。
pivot_table 默认聚合方式是 mean,不写 aggfunc="sum" 就会把 GMV 算成平均值,数字小一个数量级。aggfunc="sum" 和 fill_value=0。cross = paid.pivot_table(index="channel", columns="month",
values="pay_amount", aggfunc="sum", fill_value=0)
print(cross[["2024-04", "2024-05", "2024-06"]]
.round(0).astype("int64").to_string())
month 2024-04 2024-05 2024-06 channel 信息流 2605987 2862959 2757762 朋友推荐 1662689 1636989 2001153 线下 1005938 736822 730596 自然搜索 4057226 3528571 3525855
如果数字是小数且明显偏小,检查 aggfunc 是不是漏了,默认 mean 会把多笔订单平均掉。
pay_amount 会被复制到它的每个商品行上,再求和就会重复累加。amount_x / amount_y;merge 后对比行数、商品行金额、订单金额。pay_amount 求和膨胀到 365,790,418.68,是真实 GMV 的 2.32 倍。items = pd.read_csv("../data/csv/order_items.csv")
lines = (items[["order_id", "product_id", "qty", "amount"]]
.rename(columns={"amount": "line_amount"}))
joined = paid.merge(lines, on="order_id", how="inner")
print(len(paid), len(joined), joined["order_id"].nunique())
print(round(joined["line_amount"].sum(), 2))
print(round(joined["pay_amount"].sum(), 2))
15615 31071 15615 157875311.64 365790418.68
157,875,311.64 是商品行金额合计,等于订单原价;GMV 用的是优惠后的 pay_amount。两者差 511,931.50 元,正好是全部优惠金额。口径不同,不能直接比。
utf-8-sig,Excel 打开中文不乱码。assert 校验行数和 GMV;先 reset_index() 把渠道从 Index 变回列,再 to_csv。channel_gmv.csv。把脚本存成 channel_report.py,下次换文件名就能重跑。sql_gmv, sql_orders = 157363380.14, 15615
assert len(paid) == sql_orders
assert round(paid["pay_amount"].sum(), 2) == sql_gmv
by_channel.reset_index().to_csv("channel_gmv.csv", index=False, encoding="utf-8-sig")
print(f"对账通过:{sql_orders:,} 单 / {sql_gmv:,.2f} 元")
对账通过:15,615 单 / 157,363,380.14 元
断言失败先别改断言,先查筛选条件、日期范围和状态口径。断言是报警器,不是障碍。
先填三个数,再做五道选择题。三个数都对,说明筛选、聚合、merge 三条链路都通了。
不看前面的正文,凭第一反应选。选完立刻出解析。
read_csv 读进来的中文文本列,默认 dtype 是什么?str 类型,不再是 object。这不影响取值,但会影响你判断列类型和缺失值的方式。month,最稳妥的写法是?.copy() 能明确“这是独立副本”,后续修改不会让你猜它到底改的是谁。B 是链式赋值,永远不要用。pay_amount 求和会按商品行重复累加。df.groupby("channel")["pay_amount"].sum() 的结果是什么类型?groupby 默认 as_index=True,分组键会变成结果的 Index,所以返回 Series。要变成普通列,用 reset_index(),或 as_index=False。sub["pay_amount"][0] = 0,会发生什么?ChainedAssignmentError。正确写法是 sub.loc[sub.index[0], "pay_amount"] = 0。pandas 的错误大多不报错,只是结果不对。按“现象 → 原因 → 解决”查表。
| 现象 | 原因 | 解决 |
|---|---|---|
| 中文全是乱码 | 文件是 GBK,脚本按 UTF-8 读 | encoding="gbk";导出用 utf-8-sig |
第一列叫 Unnamed: 0 | CSV 带了索引列,或误用 index_col=0 | 检查文件;必要时删掉该列 |
金额列 dtype 是 str | 有千分位逗号、货币符号或空字符串 | str.replace(",", "").astype(float) |
to_datetime 报错 | 日期格式混杂,或有空值 | errors="coerce",再统计 NaT |
| 改了子集,原表没变 | 链式赋值 / 改的是副本 | .loc 单步赋值;要改子集先 .copy() |
出现 SettingWithCopyWarning | 旧版 pandas 的链式赋值,或对切片赋值 | 改成单步 .loc;pandas 3.0 会抛 ChainedAssignmentError |
| merge 后行数翻倍 | 右表一对多,或键不唯一 | 先聚合右表;用 validate 检查 |
| groupby 结果少了城市 | 分组键有 NaN,默认被丢弃 | dropna=False,或先填“未知” |
| 内存不够 | 全量读入 + 默认类型 | usecols 选列、dtype 降精度、chunksize 分块 |
.copy() 把警告压掉”。
iloc 按位置、loc 按标签。apply 是 Python 级,for 最慢。ChainedAssignmentError 把这件事确定下来。validate 是最便宜的保险。orders[orders.status == "已支付"]["flag"] = 1。1. lines = items[["order_id","product_id","amount"]].rename(columns={"amount":"line_amount"}),再 g = paid.merge(lines, on="order_id").merge(products[["product_id","category"]], on="product_id"),按 category 对 line_amount 求和。品类 GMV 从高到低是手机、电脑、家电、配件、服饰;前三个累计约 67.9%,覆盖 80% 还要加上配件。
2. 四个渠道客单价约 10,064.58、10,056.12、10,197.91、9,947.44,简单平均 10,066.51;总 GMV ÷ 总订单数 = 10,077.71。差异来自订单量权重不同,比率不能直接平均。
3. 439 笔游客订单没有 user_id,左关联后 user_city 为 NaN,groupby 默认丢弃,合计 153,088,978.22。处理方式:把 NaN 填成“未知/游客”单独一行;或在报表上标注“城市合计不含游客订单”。
4. 链式赋值作用在临时对象上,原表不会变。改成 orders.loc[orders.status == "已支付", "flag"] = 1;如果只想改子集,先 sub = orders[...].copy(),再 sub.loc[:, "flag"] = 1。