{ "cells": [ { "cell_type": "markdown", "id": "029e6bb7", "metadata": {}, "source": [ "# 第 12 讲｜pandas 数据整合：连接、合并与重塑\n", "\n", "**参考教材：** `da_8f_operate.pdf` \n", "**适用基础：** 已学过 DataFrame、索引、布尔筛选、缺失值和数据清洗。 \n", "**本讲目标：** 把分散在多张表中的数据正确连接，并能在长格式与宽格式之间转换。\n", "\n", "数据连接最重要的不是记住函数名，而是先回答三个问题：使用哪一列作为键、要保留哪张表的键、连接键是否重复。数据重塑则需要先明确哪些列代表标识，哪些列代表变量和值。" ] }, { "cell_type": "markdown", "id": "195bda84", "metadata": {}, "source": [ "## 训练路线\n", "\n", "1. 在普通列与行索引之间转换；\n", "2. 使用单键、多键和不同列名完成 `merge`；\n", "3. 理解内连接、左连接、外连接和重复键扩张；\n", "4. 使用 `indicator` 检查连接来源；\n", "5. 使用 `concat` 按行或按列拼接；\n", "6. 使用 `combine_first` 按标签填补缺失；\n", "7. 使用 `stack`、`unstack`、`pivot`、`melt` 完成长宽表重塑。\n", "\n", "每个知识点均按“示例代码 → 对应解读 → 动手练习”展开。练习单元格保留空白，补全后再运行。" ] }, { "cell_type": "markdown", "id": "f387b213", "metadata": {}, "source": [ "## 教材内容取舍\n", "\n", "本课件使用 Conda `skills` 环境中的 pandas 3.0 运行验证。主线优先讲比赛和数据分析中常见的列连接、行拼接及长宽表转换，并专门解释连接后行数增加、缺失值出现和列名冲突等易错点。\n", "\n", "MultiIndex 层级交换和排序、按索引 `join` 的多种变体、复杂层次列拼接、PeriodIndex 时间索引等内容放在末尾“阅读拓展”。示例均使用小表，让连接键和每一行的来源可以直接核对。" ] }, { "cell_type": "markdown", "id": "66f06ced", "metadata": {}, "source": [ "## 1. 用 set_index 把列设置为行索引\n", "\n", "`.set_index(\"列名\")` 返回把指定列设置为行索引的新 DataFrame。默认情况下，该列会从普通数据列中移除。\n", "\n", "索引适合保存能够标识一行的标签，例如学号、订单号或城市名称。" ] }, { "cell_type": "markdown", "id": "4b27fdb4", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 1, "id": "f93157ea", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:18.750641Z", "iopub.status.busy": "2026-07-30T13:56:18.750462Z", "iopub.status.idle": "2026-07-30T13:56:21.093104Z", "shell.execute_reply": "2026-07-30T13:56:21.092464Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "设置索引后：\n", " 姓名 成绩\n", "学号 \n", "S01 小林 82\n", "S02 小周 76\n", "S03 小郑 91\n", "索引标签： Index(['S01', 'S02', 'S03'], dtype='str', name='学号')\n", "S02 的成绩： 76\n" ] } ], "source": [ "import pandas as pd\n", "\n", "students = pd.DataFrame({\n", " \"学号\": [\"S01\", \"S02\", \"S03\"],\n", " \"姓名\": [\"小林\", \"小周\", \"小郑\"],\n", " \"成绩\": [82, 76, 91]\n", "})\n", "\n", "indexed = students.set_index(\"学号\")\n", "\n", "print(\"设置索引后：\")\n", "print(indexed)\n", "print(\"索引标签：\", indexed.index)\n", "print(\"S02 的成绩：\", indexed.loc[\"S02\", \"成绩\"])" ] }, { "cell_type": "markdown", "id": "a19ce824", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- “学号”从普通列移动到表格左侧，成为行索引。\n", "- `indexed.index` 显示 S01、S02、S03 三个标签。\n", "- 设置索引后，可以用 `loc` 按学号直接定位成绩。\n", "- 原来的 `students` 没有被修改，因为 `set_index` 返回新表。" ] }, { "cell_type": "markdown", "id": "66fe6153", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 2, "id": "bde36a3d", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.094978Z", "iopub.status.busy": "2026-07-30T13:56:21.094841Z", "iopub.status.idle": "2026-07-30T13:56:21.097046Z", "shell.execute_reply": "2026-07-30T13:56:21.096578Z" } }, "outputs": [], "source": [ "# 把“城市”设置为行索引，再用 loc 取得宁波销量\n", "# sales = pd.DataFrame({\n", "# \"城市\": [\"杭州\", \"宁波\"], \"销量\": [120, 98]\n", "# })\n", "# indexed = __________\n", "# print(__________)" ] }, { "cell_type": "markdown", "id": "72eb4d07", "metadata": {}, "source": [ "## 2. 用 reset_index 把索引恢复为普通列\n", "\n", "`.reset_index()` 返回把行索引移回普通列的新 DataFrame，并创建新的默认整数索引。\n", "\n", "它与 `set_index` 方向相反，也常在合并或透视后恢复普通表格结构。" ] }, { "cell_type": "markdown", "id": "73432256", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 3, "id": "f16b9540", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.098632Z", "iopub.status.busy": "2026-07-30T13:56:21.098522Z", "iopub.status.idle": "2026-07-30T13:56:21.103831Z", "shell.execute_reply": "2026-07-30T13:56:21.103212Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "原来以学号为索引：\n", " 姓名 成绩\n", "学号 \n", "S01 小林 82\n", "S02 小周 76\n", "S03 小郑 91\n", "恢复为普通列：\n", " 学号 姓名 成绩\n", "0 S01 小林 82\n", "1 S02 小周 76\n", "2 S03 小郑 91\n" ] } ], "source": [ "indexed = pd.DataFrame(\n", " {\n", " \"姓名\": [\"小林\", \"小周\", \"小郑\"],\n", " \"成绩\": [82, 76, 91]\n", " },\n", " index=[\"S01\", \"S02\", \"S03\"]\n", ")\n", "indexed.index.name = \"学号\"\n", "\n", "restored = indexed.reset_index()\n", "\n", "print(\"原来以学号为索引：\")\n", "print(indexed)\n", "print(\"恢复为普通列：\")\n", "print(restored)" ] }, { "cell_type": "markdown", "id": "7bbe4241", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- `indexed.index.name = \"学号\"` 为索引设置名称。\n", "- `reset_index()` 使用这个名称创建“学号”普通列。\n", "- 结果重新使用 0、1、2 作为默认行索引。" ] }, { "cell_type": "markdown", "id": "a9c6663d", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 4, "id": "3ada6acb", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.105341Z", "iopub.status.busy": "2026-07-30T13:56:21.105226Z", "iopub.status.idle": "2026-07-30T13:56:21.107437Z", "shell.execute_reply": "2026-07-30T13:56:21.106870Z" } }, "outputs": [], "source": [ "# 把以“城市”为名的索引恢复成普通列\n", "# sales = pd.DataFrame({\"销量\": [120, 98]}, index=[\"杭州\", \"宁波\"])\n", "# sales.index.name = \"城市\"\n", "# restored = __________\n", "# print(restored)" ] }, { "cell_type": "markdown", "id": "6c5e6bb8", "metadata": {}, "source": [ "## 3. 多列索引：set_index 接收列名列表\n", "\n", "`.set_index([\"列1\", \"列2\"])` 把多列共同设置为行索引，得到 MultiIndex（多层索引）。\n", "\n", "当单独一列不能唯一标识记录时，可以用多个字段组合定位，例如“城市 + 年份”。选择一个组合标签时使用元组。" ] }, { "cell_type": "markdown", "id": "f8bc4daf", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 5, "id": "4aea107e", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.108885Z", "iopub.status.busy": "2026-07-30T13:56:21.108767Z", "iopub.status.idle": "2026-07-30T13:56:21.121550Z", "shell.execute_reply": "2026-07-30T13:56:21.120981Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 销量\n", "城市 年份 \n", "杭州 2024 120\n", " 2025 135\n", "宁波 2024 98\n", " 2025 110\n", "索引层数： 2\n", "杭州 2025 年销量： 135\n" ] } ], "source": [ "sales = pd.DataFrame({\n", " \"城市\": [\"杭州\", \"杭州\", \"宁波\", \"宁波\"],\n", " \"年份\": [2024, 2025, 2024, 2025],\n", " \"销量\": [120, 135, 98, 110]\n", "})\n", "\n", "multi_indexed = sales.set_index([\"城市\", \"年份\"])\n", "\n", "print(multi_indexed)\n", "print(\"索引层数：\", multi_indexed.index.nlevels)\n", "print(\"杭州 2025 年销量：\", multi_indexed.loc[(\"杭州\", 2025), \"销量\"])" ] }, { "cell_type": "markdown", "id": "19975433", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 行索引由“城市”和“年份”两层组成，所以 `nlevels` 是 2。\n", "- `(\"杭州\", 2025)` 是一个组合标签，按索引层级顺序写成元组。\n", "- 两列组合后，每一行都有明确的“城市 + 年份”位置。" ] }, { "cell_type": "markdown", "id": "bb15d5ef", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 6, "id": "f1a7c7ce", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.123069Z", "iopub.status.busy": "2026-07-30T13:56:21.122969Z", "iopub.status.idle": "2026-07-30T13:56:21.125088Z", "shell.execute_reply": "2026-07-30T13:56:21.124497Z" } }, "outputs": [], "source": [ "# 用“商品”和“月份”共同设置索引，再取得 A 商品二月的数量\n", "# data = pd.DataFrame({\n", "# \"商品\": [\"A\", \"A\", \"B\"],\n", "# \"月份\": [\"一月\", \"二月\", \"一月\"],\n", "# \"数量\": [3, 5, 2]\n", "# })\n", "# indexed = __________\n", "# print(__________)" ] }, { "cell_type": "markdown", "id": "4adf1881", "metadata": {}, "source": [ "## 4. 内连接：merge 与 on\n", "\n", "`pd.merge(左表, 右表, on=\"连接键\", how=\"inner\")` 按共同键连接两张表。\n", "\n", "内连接只保留左右两表都出现的键。推荐始终显式写出 `on`，不要依赖 pandas 猜测共同列名。" ] }, { "cell_type": "markdown", "id": "925ced40", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 7, "id": "e53e2026", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.126559Z", "iopub.status.busy": "2026-07-30T13:56:21.126470Z", "iopub.status.idle": "2026-07-30T13:56:21.133190Z", "shell.execute_reply": "2026-07-30T13:56:21.132608Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 学号 姓名 成绩\n", "0 S02 小周 76\n", "1 S03 小郑 91\n" ] } ], "source": [ "students = pd.DataFrame({\n", " \"学号\": [\"S01\", \"S02\", \"S03\"],\n", " \"姓名\": [\"小林\", \"小周\", \"小郑\"]\n", "})\n", "scores = pd.DataFrame({\n", " \"学号\": [\"S02\", \"S03\", \"S04\"],\n", " \"成绩\": [76, 91, 85]\n", "})\n", "\n", "matched = pd.merge(\n", " students,\n", " scores,\n", " on=\"学号\",\n", " how=\"inner\"\n", ")\n", "\n", "print(matched)" ] }, { "cell_type": "markdown", "id": "3d92c005", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 两张表共同拥有的学号只有 S02 和 S03，因此结果是两行。\n", "- S01 只在左表，S04 只在右表，内连接不会保留它们。\n", "- “姓名”来自左表，“成绩”来自右表，连接键“学号”只显示一列。" ] }, { "cell_type": "markdown", "id": "6fbee580", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 8, "id": "2c0dc00d", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.134677Z", "iopub.status.busy": "2026-07-30T13:56:21.134556Z", "iopub.status.idle": "2026-07-30T13:56:21.136690Z", "shell.execute_reply": "2026-07-30T13:56:21.136282Z" } }, "outputs": [], "source": [ "# 按“商品编号”做内连接\n", "# goods = pd.DataFrame({\"商品编号\": [1, 2, 3], \"商品\": [\"A\", \"B\", \"C\"]})\n", "# prices = pd.DataFrame({\"商品编号\": [2, 3, 4], \"单价\": [12, 15, 10]})\n", "# result = pd.merge(\n", "# __________,\n", "# __________,\n", "# on=__________,\n", "# how=__________\n", "# )\n", "# print(result)" ] }, { "cell_type": "markdown", "id": "0b6e20d7", "metadata": {}, "source": [ "## 5. 左连接与外连接：how\n", "\n", "`how` 决定保留哪些连接键：\n", "\n", "- `how=\"left\"`：保留左表所有键；\n", "- `how=\"outer\"`：保留左右两表所有键。\n", "\n", "某个键只在一边出现时，另一边提供的列会显示缺失值。" ] }, { "cell_type": "markdown", "id": "5d01ba80", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 9, "id": "d9f566f3", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.138418Z", "iopub.status.busy": "2026-07-30T13:56:21.138291Z", "iopub.status.idle": "2026-07-30T13:56:21.148145Z", "shell.execute_reply": "2026-07-30T13:56:21.147600Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "左连接：\n", " 学号 姓名 成绩\n", "0 S01 小林 NaN\n", "1 S02 小周 76.0\n", "2 S03 小郑 91.0\n", "外连接：\n", " 学号 姓名 成绩\n", "0 S01 小林 NaN\n", "1 S02 小周 76.0\n", "2 S03 小郑 91.0\n", "3 S04 NaN 85.0\n" ] } ], "source": [ "students = pd.DataFrame({\n", " \"学号\": [\"S01\", \"S02\", \"S03\"],\n", " \"姓名\": [\"小林\", \"小周\", \"小郑\"]\n", "})\n", "scores = pd.DataFrame({\n", " \"学号\": [\"S02\", \"S03\", \"S04\"],\n", " \"成绩\": [76, 91, 85]\n", "})\n", "\n", "left_result = pd.merge(\n", " students, scores, on=\"学号\", how=\"left\"\n", ")\n", "outer_result = pd.merge(\n", " students, scores, on=\"学号\", how=\"outer\"\n", ")\n", "\n", "print(\"左连接：\")\n", "print(left_result)\n", "print(\"外连接：\")\n", "print(outer_result)" ] }, { "cell_type": "markdown", "id": "2686d33c", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 左连接保留左表三个学号，因此 S01 仍在结果中，但成绩缺失。\n", "- 外连接保留 S01、S02、S03、S04 四个学号。\n", "- S04 没有左表姓名，所以外连接结果中的姓名缺失。\n", "- 选择连接方式前，应先说明哪张表代表必须保留的主体记录。" ] }, { "cell_type": "markdown", "id": "6a67d237", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 10, "id": "efc2548b", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.149730Z", "iopub.status.busy": "2026-07-30T13:56:21.149632Z", "iopub.status.idle": "2026-07-30T13:56:21.151614Z", "shell.execute_reply": "2026-07-30T13:56:21.151108Z" } }, "outputs": [], "source": [ "# 分别进行左连接和外连接，观察编号 1、4 是否保留\n", "# left = pd.DataFrame({\"编号\": [1, 2, 3], \"名称\": [\"A\", \"B\", \"C\"]})\n", "# right = pd.DataFrame({\"编号\": [2, 3, 4], \"金额\": [20, 30, 40]})\n", "# left_join = __________\n", "# outer_join = __________\n", "# print(left_join)\n", "# print(outer_join)" ] }, { "cell_type": "markdown", "id": "39c33517", "metadata": {}, "source": [ "## 6. 连接键列名不同：left_on 与 right_on\n", "\n", "左右两表的连接键含义相同但列名不同时，使用：\n", "\n", "`pd.merge(..., left_on=\"左表键\", right_on=\"右表键\")`\n", "\n", "结果会保留两个键列，便于核对原始字段。" ] }, { "cell_type": "markdown", "id": "f09173b3", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 11, "id": "91bfa713", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.153198Z", "iopub.status.busy": "2026-07-30T13:56:21.153077Z", "iopub.status.idle": "2026-07-30T13:56:21.158741Z", "shell.execute_reply": "2026-07-30T13:56:21.158345Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 客户编号 金额 编号 客户名\n", "0 101 80 101.0 甲\n", "1 102 120 102.0 乙\n", "2 103 60 NaN NaN\n" ] } ], "source": [ "orders = pd.DataFrame({\n", " \"客户编号\": [101, 102, 103],\n", " \"金额\": [80, 120, 60]\n", "})\n", "customers = pd.DataFrame({\n", " \"编号\": [101, 102, 104],\n", " \"客户名\": [\"甲\", \"乙\", \"丁\"]\n", "})\n", "\n", "result = pd.merge(\n", " orders,\n", " customers,\n", " left_on=\"客户编号\",\n", " right_on=\"编号\",\n", " how=\"left\"\n", ")\n", "\n", "print(result)" ] }, { "cell_type": "markdown", "id": "806592bf", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 左表“客户编号”与右表“编号”含义相同，因此被指定为左右连接键。\n", "- 左连接保留三笔订单；客户 103 在右表没有资料，所以“编号”和“客户名”缺失。\n", "- 结果同时保留“客户编号”和“编号”，可以检查连接是否正确。" ] }, { "cell_type": "markdown", "id": "c6e6405f", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 12, "id": "433169b7", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.160438Z", "iopub.status.busy": "2026-07-30T13:56:21.160313Z", "iopub.status.idle": "2026-07-30T13:56:21.162629Z", "shell.execute_reply": "2026-07-30T13:56:21.162048Z" } }, "outputs": [], "source": [ "# 用左表“商品ID”连接右表“编号”\n", "# orders = pd.DataFrame({\"商品ID\": [1, 2], \"数量\": [3, 5]})\n", "# goods = pd.DataFrame({\"编号\": [1, 2], \"商品\": [\"A\", \"B\"]})\n", "# result = pd.merge(\n", "# orders,\n", "# goods,\n", "# left_on=__________,\n", "# right_on=__________,\n", "# how=\"left\"\n", "# )\n", "# print(result)" ] }, { "cell_type": "markdown", "id": "87e496f8", "metadata": {}, "source": [ "## 7. 使用多个连接键：on 接收列名列表\n", "\n", "单个键不能唯一对应记录时，可以使用多个连接键：\n", "\n", "`pd.merge(..., on=[\"键1\", \"键2\"])`\n", "\n", "pandas 会把多个键的组合当作完整匹配条件，只有所有键都相同才连接。" ] }, { "cell_type": "markdown", "id": "d2336cf2", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 13, "id": "8522855a", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.164204Z", "iopub.status.busy": "2026-07-30T13:56:21.164077Z", "iopub.status.idle": "2026-07-30T13:56:21.171584Z", "shell.execute_reply": "2026-07-30T13:56:21.171023Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 城市 年份 销量 目标\n", "0 杭州 2024 120 110\n", "1 杭州 2025 135 140\n" ] } ], "source": [ "sales = pd.DataFrame({\n", " \"城市\": [\"杭州\", \"杭州\", \"宁波\"],\n", " \"年份\": [2024, 2025, 2024],\n", " \"销量\": [120, 135, 98]\n", "})\n", "targets = pd.DataFrame({\n", " \"城市\": [\"杭州\", \"杭州\", \"宁波\"],\n", " \"年份\": [2024, 2025, 2025],\n", " \"目标\": [110, 140, 105]\n", "})\n", "\n", "result = pd.merge(\n", " sales,\n", " targets,\n", " on=[\"城市\", \"年份\"],\n", " how=\"inner\"\n", ")\n", "\n", "print(result)" ] }, { "cell_type": "markdown", "id": "7accaa4a", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 杭州 2024 和杭州 2025 在两表中都有完整匹配，因此进入结果。\n", "- 宁波虽然两表都有，但年份分别为 2024 和 2025，组合键不相同，所以不会连接。\n", "- 多键连接能避免只按城市连接而产生错误配对。" ] }, { "cell_type": "markdown", "id": "79a59f84", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 14, "id": "d859fee7", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.173250Z", "iopub.status.busy": "2026-07-30T13:56:21.173128Z", "iopub.status.idle": "2026-07-30T13:56:21.175371Z", "shell.execute_reply": "2026-07-30T13:56:21.174854Z" } }, "outputs": [], "source": [ "# 按“商品”和“月份”两个键连接实际数量与目标数量\n", "# actual = pd.DataFrame({\n", "# \"商品\": [\"A\", \"A\"], \"月份\": [1, 2], \"实际\": [3, 5]\n", "# })\n", "# target = pd.DataFrame({\n", "# \"商品\": [\"A\", \"A\"], \"月份\": [1, 2], \"目标\": [4, 5]\n", "# })\n", "# result = pd.merge(actual, target, on=__________, how=\"inner\")\n", "# print(result)" ] }, { "cell_type": "markdown", "id": "6a9e11b6", "metadata": {}, "source": [ "## 8. 处理重名列：suffixes\n", "\n", "两表除连接键外还有相同列名时，`suffixes=(\"左后缀\", \"右后缀\")` 为重名列添加后缀。\n", "\n", "后缀应表达列的来源或含义，避免使用难以理解的默认 `_x`、`_y`。" ] }, { "cell_type": "markdown", "id": "5d81d18d", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 15, "id": "47f58397", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.177018Z", "iopub.status.busy": "2026-07-30T13:56:21.176904Z", "iopub.status.idle": "2026-07-30T13:56:21.182954Z", "shell.execute_reply": "2026-07-30T13:56:21.182391Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 编号 名称_商品 名称_类别\n", "0 1 商品A 甲类\n", "1 2 商品B 乙类\n" ] } ], "source": [ "goods = pd.DataFrame({\n", " \"编号\": [1, 2],\n", " \"名称\": [\"商品A\", \"商品B\"]\n", "})\n", "categories = pd.DataFrame({\n", " \"编号\": [1, 2],\n", " \"名称\": [\"甲类\", \"乙类\"]\n", "})\n", "\n", "result = pd.merge(\n", " goods,\n", " categories,\n", " on=\"编号\",\n", " suffixes=(\"_商品\", \"_类别\")\n", ")\n", "\n", "print(result)" ] }, { "cell_type": "markdown", "id": "d4c017b6", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 两表都包含“名称”，但含义分别是商品名和类别名。\n", "- 连接键“编号”不会添加后缀。\n", "- 其余重名列变成“名称_商品”和“名称_类别”，来源一目了然。" ] }, { "cell_type": "markdown", "id": "fb516c26", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 16, "id": "a4a65899", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.184441Z", "iopub.status.busy": "2026-07-30T13:56:21.184330Z", "iopub.status.idle": "2026-07-30T13:56:21.186629Z", "shell.execute_reply": "2026-07-30T13:56:21.186074Z" } }, "outputs": [], "source": [ "# 两张表都有“数值”列，分别添加“_实际”“_目标”后缀\n", "# actual = pd.DataFrame({\"编号\": [1, 2], \"数值\": [80, 90]})\n", "# target = pd.DataFrame({\"编号\": [1, 2], \"数值\": [85, 88]})\n", "# result = pd.merge(\n", "# actual,\n", "# target,\n", "# on=\"编号\",\n", "# suffixes=__________\n", "# )\n", "# print(result)" ] }, { "cell_type": "markdown", "id": "aa836892", "metadata": {}, "source": [ "## 9. 重复连接键会扩张结果行数\n", "\n", "如果一个键在左表出现 m 次、在右表出现 n 次，连接后该键会产生 `m × n` 行组合。这叫多对多连接。\n", "\n", "连接前应检查键是否重复；`.duplicated(subset=[键], keep=False)` 会把同组中的所有重复行都标记出来。" ] }, { "cell_type": "markdown", "id": "5156c641", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 17, "id": "0537c3a1", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.188235Z", "iopub.status.busy": "2026-07-30T13:56:21.188127Z", "iopub.status.idle": "2026-07-30T13:56:21.195294Z", "shell.execute_reply": "2026-07-30T13:56:21.194768Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "左表重复键标记：\n", "0 True\n", "1 True\n", "dtype: bool\n", "连接结果：\n", " 键 左值 右值\n", "0 A 1 10\n", "1 A 1 20\n", "2 A 2 10\n", "3 A 2 20\n", "结果行数： 4\n" ] } ], "source": [ "left = pd.DataFrame({\n", " \"键\": [\"A\", \"A\"],\n", " \"左值\": [1, 2]\n", "})\n", "right = pd.DataFrame({\n", " \"键\": [\"A\", \"A\"],\n", " \"右值\": [10, 20]\n", "})\n", "\n", "left_repeated = left.duplicated(\n", " subset=[\"键\"], keep=False\n", ")\n", "result = pd.merge(left, right, on=\"键\")\n", "\n", "print(\"左表重复键标记：\")\n", "print(left_repeated)\n", "print(\"连接结果：\")\n", "print(result)\n", "print(\"结果行数：\", len(result))" ] }, { "cell_type": "markdown", "id": "c822b930", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 左表有 2 个 A，右表也有 2 个 A，因此结果出现 `2 × 2 = 4` 行。\n", "- 每个左值都与两个右值分别组合。\n", "- 行数增加不一定是错误，但必须符合业务关系；如果本应一对一，就要先清理重复键。" ] }, { "cell_type": "markdown", "id": "d1fedc82", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 18, "id": "08e31761", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.196774Z", "iopub.status.busy": "2026-07-30T13:56:21.196651Z", "iopub.status.idle": "2026-07-30T13:56:21.198836Z", "shell.execute_reply": "2026-07-30T13:56:21.198258Z" } }, "outputs": [], "source": [ "# 先检查两张表的编号是否重复，再观察连接后有多少行\n", "# left = pd.DataFrame({\"编号\": [1, 1], \"A\": [10, 20]})\n", "# right = pd.DataFrame({\"编号\": [1, 1, 1], \"B\": [3, 4, 5]})\n", "# print(__________)\n", "# print(__________)\n", "# result = __________\n", "# print(len(result))" ] }, { "cell_type": "markdown", "id": "ea6ecf68", "metadata": {}, "source": [ "## 10. 用 indicator 检查每行的连接来源\n", "\n", "`indicator=True` 会在合并结果中增加 `_merge` 列，说明每行来自哪里：\n", "\n", "- `both`：左右两表都有；\n", "- `left_only`：只在左表；\n", "- `right_only`：只在右表。\n", "\n", "它适合检查未匹配键，不应在最终结果中不加说明地保留。" ] }, { "cell_type": "markdown", "id": "831a7651", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 19, "id": "8e9c90ee", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.200325Z", "iopub.status.busy": "2026-07-30T13:56:21.200218Z", "iopub.status.idle": "2026-07-30T13:56:21.211313Z", "shell.execute_reply": "2026-07-30T13:56:21.210719Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 编号 名称 金额 _merge\n", "0 1 A NaN left_only\n", "1 2 B 20.0 both\n", "2 3 C 30.0 both\n", "3 4 NaN 40.0 right_only\n", "各来源数量：\n", "_merge\n", "both 2\n", "left_only 1\n", "right_only 1\n", "Name: count, dtype: int64\n" ] } ], "source": [ "left = pd.DataFrame({\n", " \"编号\": [1, 2, 3],\n", " \"名称\": [\"A\", \"B\", \"C\"]\n", "})\n", "right = pd.DataFrame({\n", " \"编号\": [2, 3, 4],\n", " \"金额\": [20, 30, 40]\n", "})\n", "\n", "checked = pd.merge(\n", " left,\n", " right,\n", " on=\"编号\",\n", " how=\"outer\",\n", " indicator=True\n", ")\n", "\n", "print(checked)\n", "print(\"各来源数量：\")\n", "print(checked[\"_merge\"].value_counts())" ] }, { "cell_type": "markdown", "id": "15cbc366", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 编号 2、3 两边都有，因此标记为 `both`。\n", "- 编号 1 只在左表，标记为 `left_only`。\n", "- 编号 4 只在右表，标记为 `right_only`。\n", "- `value_counts()` 可快速统计未匹配记录数量。" ] }, { "cell_type": "markdown", "id": "89b5cd97", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 20, "id": "e60f97c9", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.212922Z", "iopub.status.busy": "2026-07-30T13:56:21.212814Z", "iopub.status.idle": "2026-07-30T13:56:21.214764Z", "shell.execute_reply": "2026-07-30T13:56:21.214301Z" } }, "outputs": [], "source": [ "# 外连接时加入来源标记，并统计各来源数量\n", "# first = pd.DataFrame({\"键\": [\"A\", \"B\"], \"值1\": [1, 2]})\n", "# second = pd.DataFrame({\"键\": [\"B\", \"C\"], \"值2\": [3, 4]})\n", "# checked = pd.merge(\n", "# first,\n", "# second,\n", "# on=\"键\",\n", "# how=__________,\n", "# indicator=__________\n", "# )\n", "# print(checked)\n", "# print(__________)" ] }, { "cell_type": "markdown", "id": "04c68262", "metadata": {}, "source": [ "## 11. 按行拼接：concat 与 ignore_index\n", "\n", "`pd.concat([表1, 表2, ...], axis=0)` 把多张表按行堆叠。`axis=0` 是默认值，可以省略。\n", "\n", "`ignore_index=True` 丢弃各表原索引，并为结果创建连续的 0、1、2……索引。" ] }, { "cell_type": "markdown", "id": "f4b91def", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 21, "id": "0d05a46b", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.216478Z", "iopub.status.busy": "2026-07-30T13:56:21.216350Z", "iopub.status.idle": "2026-07-30T13:56:21.220534Z", "shell.execute_reply": "2026-07-30T13:56:21.220024Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 城市 销量\n", "0 杭州 120\n", "1 宁波 98\n", "2 杭州 130\n", "3 温州 110\n" ] } ], "source": [ "january = pd.DataFrame({\n", " \"城市\": [\"杭州\", \"宁波\"],\n", " \"销量\": [120, 98]\n", "})\n", "february = pd.DataFrame({\n", " \"城市\": [\"杭州\", \"温州\"],\n", " \"销量\": [130, 110]\n", "})\n", "\n", "combined = pd.concat(\n", " [january, february],\n", " axis=0,\n", " ignore_index=True\n", ")\n", "\n", "print(combined)" ] }, { "cell_type": "markdown", "id": "1cfc4e51", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 一月的两行在上，二月的两行接在下方，总共 4 行。\n", "- 两张表列名相同，因此数据垂直对齐到“城市”“销量”两列。\n", "- `ignore_index=True` 让结果索引重新变为连续的 0 到 3。" ] }, { "cell_type": "markdown", "id": "3b9947c8", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 22, "id": "cc4a330f", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.222134Z", "iopub.status.busy": "2026-07-30T13:56:21.222037Z", "iopub.status.idle": "2026-07-30T13:56:21.224199Z", "shell.execute_reply": "2026-07-30T13:56:21.223622Z" } }, "outputs": [], "source": [ "# 把上半年和下半年记录按行拼接，并重新生成连续索引\n", "# first = pd.DataFrame({\"月份\": [1, 2], \"销量\": [80, 90]})\n", "# second = pd.DataFrame({\"月份\": [7, 8], \"销量\": [100, 110]})\n", "# result = pd.concat(\n", "# __________,\n", "# axis=__________,\n", "# ignore_index=__________\n", "# )\n", "# print(result)" ] }, { "cell_type": "markdown", "id": "173c3537", "metadata": {}, "source": [ "## 12. 按列拼接：concat 的 axis=1 与索引对齐\n", "\n", "`pd.concat([对象1, 对象2], axis=1)` 把对象按列并排放置。\n", "\n", "pandas 会按行索引标签对齐，而不是只按当前位置拼接。只在一边出现的索引会在另一边产生缺失值。" ] }, { "cell_type": "markdown", "id": "0c39a76e", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 23, "id": "760eee06", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.225636Z", "iopub.status.busy": "2026-07-30T13:56:21.225543Z", "iopub.status.idle": "2026-07-30T13:56:21.230257Z", "shell.execute_reply": "2026-07-30T13:56:21.229656Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 实际销量 目标销量\n", "杭州 120.0 125.0\n", "宁波 98.0 NaN\n", "温州 NaN 110.0\n" ] } ], "source": [ "actual = pd.Series(\n", " [120, 98],\n", " index=[\"杭州\", \"宁波\"],\n", " name=\"实际销量\"\n", ")\n", "target = pd.Series(\n", " [125, 110],\n", " index=[\"杭州\", \"温州\"],\n", " name=\"目标销量\"\n", ")\n", "\n", "combined = pd.concat(\n", " [actual, target],\n", " axis=1\n", ")\n", "\n", "print(combined)" ] }, { "cell_type": "markdown", "id": "e3e2aaa5", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 杭州在两个 Series 中都有，因此一行中同时有实际值和目标值。\n", "- 宁波只在实际数据中，温州只在目标数据中，所以另一列显示缺失。\n", "- 两个 Series 的 `name` 成为结果 DataFrame 的列名。" ] }, { "cell_type": "markdown", "id": "183b64b9", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 24, "id": "16e466f8", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.231791Z", "iopub.status.busy": "2026-07-30T13:56:21.231670Z", "iopub.status.idle": "2026-07-30T13:56:21.233790Z", "shell.execute_reply": "2026-07-30T13:56:21.233294Z" } }, "outputs": [], "source": [ "# 把两个带城市索引的 Series 按列拼接\n", "# price = pd.Series([8, 12], index=[\"A\", \"B\"], name=\"单价\")\n", "# quantity = pd.Series([3, 5], index=[\"A\", \"C\"], name=\"数量\")\n", "# result = pd.concat(__________, axis=__________)\n", "# print(result)" ] }, { "cell_type": "markdown", "id": "30a07fad", "metadata": {}, "source": [ "## 13. 用 combine_first 按标签填补缺失\n", "\n", "`主要对象.combine_first(备用对象)` 使用备用对象中同标签位置的值，填补主要对象的缺失位置。\n", "\n", "主要对象中已有的非缺失值优先保留；结果会对齐两个对象的索引和列标签。" ] }, { "cell_type": "markdown", "id": "a283686b", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 25, "id": "87d9e20d", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.235425Z", "iopub.status.busy": "2026-07-30T13:56:21.235305Z", "iopub.status.idle": "2026-07-30T13:56:21.241206Z", "shell.execute_reply": "2026-07-30T13:56:21.240680Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "主要数据：\n", "杭州 120.0\n", "宁波 NaN\n", "温州 135.0\n", "dtype: float64\n", "备用数据：\n", "杭州 118\n", "宁波 98\n", "绍兴 110\n", "dtype: int64\n", "填补后：\n", "宁波 98.0\n", "杭州 120.0\n", "温州 135.0\n", "绍兴 110.0\n", "dtype: float64\n" ] } ], "source": [ "primary = pd.Series(\n", " [120, None, 135],\n", " index=[\"杭州\", \"宁波\", \"温州\"]\n", ")\n", "backup = pd.Series(\n", " [118, 98, 110],\n", " index=[\"杭州\", \"宁波\", \"绍兴\"]\n", ")\n", "\n", "completed = primary.combine_first(backup)\n", "\n", "print(\"主要数据：\")\n", "print(primary)\n", "print(\"备用数据：\")\n", "print(backup)\n", "print(\"填补后：\")\n", "print(completed)" ] }, { "cell_type": "markdown", "id": "67c9f7f8", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- 杭州在主要数据中已有 120，因此不使用备用值 118。\n", "- 宁波在主要数据中缺失，所以使用备用值 98。\n", "- 温州只在主要数据中，绍兴只在备用数据中，两者都会进入结果。\n", "- `combine_first` 是按标签打补丁，不是按位置替换。" ] }, { "cell_type": "markdown", "id": "8116bd4f", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 26, "id": "6529c6cc", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.242660Z", "iopub.status.busy": "2026-07-30T13:56:21.242540Z", "iopub.status.idle": "2026-07-30T13:56:21.244494Z", "shell.execute_reply": "2026-07-30T13:56:21.243971Z" } }, "outputs": [], "source": [ "# 用备用价格填补主要价格中的缺失值\n", "# primary = pd.Series([8, None], index=[\"A\", \"B\"])\n", "# backup = pd.Series([9, 12], index=[\"A\", \"B\"])\n", "# result = __________\n", "# print(result)" ] }, { "cell_type": "markdown", "id": "762bd42b", "metadata": {}, "source": [ "## 14. stack 与 unstack：列和索引层之间转换\n", "\n", "`.stack()` 把 DataFrame 的列压入行索引，通常得到带多层索引的 Series。\n", "\n", "`.unstack()` 执行相反方向的操作，把最内层行索引展开回列。" ] }, { "cell_type": "markdown", "id": "a80e5c1a", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 27, "id": "421bef66", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.246042Z", "iopub.status.busy": "2026-07-30T13:56:21.245938Z", "iopub.status.idle": "2026-07-30T13:56:21.252573Z", "shell.execute_reply": "2026-07-30T13:56:21.252041Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "宽表：\n", " 一月 二月\n", "杭州 120 130\n", "宁波 98 105\n", "stack 后：\n", "杭州 一月 120\n", " 二月 130\n", "宁波 一月 98\n", " 二月 105\n", "dtype: int64\n", "再 unstack：\n", " 一月 二月\n", "杭州 120 130\n", "宁波 98 105\n" ] } ], "source": [ "wide = pd.DataFrame(\n", " {\n", " \"一月\": [120, 98],\n", " \"二月\": [130, 105]\n", " },\n", " index=[\"杭州\", \"宁波\"]\n", ")\n", "\n", "stacked = wide.stack()\n", "restored = stacked.unstack()\n", "\n", "print(\"宽表：\")\n", "print(wide)\n", "print(\"stack 后：\")\n", "print(stacked)\n", "print(\"再 unstack：\")\n", "print(restored)" ] }, { "cell_type": "markdown", "id": "9198abda", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- `stack()` 把“一月”“二月”列名移动到第二层行索引。\n", "- 每个“城市 + 月份”组合对应一个销量值。\n", "- `unstack()` 把第二层索引重新展开为两列，恢复原来的宽表形状。" ] }, { "cell_type": "markdown", "id": "b16fced7", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 28, "id": "d17c6c87", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.254101Z", "iopub.status.busy": "2026-07-30T13:56:21.253999Z", "iopub.status.idle": "2026-07-30T13:56:21.256278Z", "shell.execute_reply": "2026-07-30T13:56:21.255670Z" } }, "outputs": [], "source": [ "# 把季度列 stack 成多层索引 Series，再 unstack 恢复\n", "# wide = pd.DataFrame(\n", "# {\"一季度\": [80, 90], \"二季度\": [100, 110]},\n", "# index=[\"A\", \"B\"]\n", "# )\n", "# stacked = __________\n", "# restored = __________\n", "# print(stacked)\n", "# print(restored)" ] }, { "cell_type": "markdown", "id": "8571e912", "metadata": {}, "source": [ "## 15. 用 pivot 把长格式变为宽格式\n", "\n", "`df.pivot(index=\"行标识列\", columns=\"展开列\", values=\"数值列\")` 把长格式转换为宽格式。\n", "\n", "每个“行标识 + 展开列”组合必须唯一，否则 pandas 不知道该把多个值放进同一单元格。" ] }, { "cell_type": "markdown", "id": "566a823f", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 29, "id": "3b263035", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.257762Z", "iopub.status.busy": "2026-07-30T13:56:21.257656Z", "iopub.status.idle": "2026-07-30T13:56:21.262819Z", "shell.execute_reply": "2026-07-30T13:56:21.262261Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "是否存在重复的日期城市组合： False\n", "城市 宁波 杭州\n", "日期 \n", "7月1日 98 120\n", "7月2日 105 125\n" ] } ], "source": [ "long_data = pd.DataFrame({\n", " \"日期\": [\"7月1日\", \"7月1日\", \"7月2日\", \"7月2日\"],\n", " \"城市\": [\"杭州\", \"宁波\", \"杭州\", \"宁波\"],\n", " \"销量\": [120, 98, 125, 105]\n", "})\n", "\n", "duplicate_pairs = long_data.duplicated(\n", " subset=[\"日期\", \"城市\"]\n", ").any()\n", "wide_data = long_data.pivot(\n", " index=\"日期\",\n", " columns=\"城市\",\n", " values=\"销量\"\n", ")\n", "\n", "print(\"是否存在重复的日期城市组合：\", duplicate_pairs)\n", "print(wide_data)" ] }, { "cell_type": "markdown", "id": "77d32f6e", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- “日期”成为宽表行索引，“城市”的不同值变成列名。\n", "- “销量”填入每个日期和城市交叉的单元格。\n", "- 透视前先检查“日期 + 城市”组合是否重复；本例结果为 `False`，可以安全使用 `pivot`。" ] }, { "cell_type": "markdown", "id": "901fb413", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 30, "id": "41a74c2b", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.264399Z", "iopub.status.busy": "2026-07-30T13:56:21.264275Z", "iopub.status.idle": "2026-07-30T13:56:21.266570Z", "shell.execute_reply": "2026-07-30T13:56:21.266050Z" } }, "outputs": [], "source": [ "# 把“商品、月份、数量”长表转换为月份在列上的宽表\n", "# long_data = pd.DataFrame({\n", "# \"商品\": [\"A\", \"A\", \"B\", \"B\"],\n", "# \"月份\": [\"一月\", \"二月\", \"一月\", \"二月\"],\n", "# \"数量\": [3, 5, 2, 4]\n", "# })\n", "# wide = long_data.pivot(\n", "# index=__________,\n", "# columns=__________,\n", "# values=__________\n", "# )\n", "# print(wide)" ] }, { "cell_type": "markdown", "id": "34f1d74c", "metadata": {}, "source": [ "## 16. 用 melt 把宽格式变为长格式\n", "\n", "`pd.melt` 把多列合并成“变量列 + 数值列”：\n", "\n", "- `id_vars`：保持不变的标识列；\n", "- `value_vars`：需要合并的数值列；\n", "- `var_name`：新变量列的名称；\n", "- `value_name`：新数值列的名称。" ] }, { "cell_type": "markdown", "id": "53bc5f87", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 31, "id": "d0205f84", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.267991Z", "iopub.status.busy": "2026-07-30T13:56:21.267883Z", "iopub.status.idle": "2026-07-30T13:56:21.273265Z", "shell.execute_reply": "2026-07-30T13:56:21.272873Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 城市 月份 销量\n", "0 杭州 一月 120\n", "1 宁波 一月 98\n", "2 杭州 二月 130\n", "3 宁波 二月 105\n" ] } ], "source": [ "wide_data = pd.DataFrame({\n", " \"城市\": [\"杭州\", \"宁波\"],\n", " \"一月\": [120, 98],\n", " \"二月\": [130, 105]\n", "})\n", "\n", "long_data = pd.melt(\n", " wide_data,\n", " id_vars=[\"城市\"],\n", " value_vars=[\"一月\", \"二月\"],\n", " var_name=\"月份\",\n", " value_name=\"销量\"\n", ")\n", "\n", "print(long_data)" ] }, { "cell_type": "markdown", "id": "c8291bfc", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- “城市”是标识列，重复出现在每个月份对应的记录中。\n", "- 原来的“一月”“二月”两个列名进入新列“月份”。\n", "- 原来单元格中的数值进入新列“销量”。\n", "- 2 行 × 2 个被合并列，得到 4 行长格式数据。" ] }, { "cell_type": "markdown", "id": "d017cb1f", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 32, "id": "1c72fdab", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.275103Z", "iopub.status.busy": "2026-07-30T13:56:21.274980Z", "iopub.status.idle": "2026-07-30T13:56:21.277041Z", "shell.execute_reply": "2026-07-30T13:56:21.276672Z" } }, "outputs": [], "source": [ "# 把一季度、二季度两列转换为“季度”“销量”两列\n", "# wide = pd.DataFrame({\n", "# \"城市\": [\"A\", \"B\"], \"一季度\": [80, 90], \"二季度\": [100, 110]\n", "# })\n", "# long = pd.melt(\n", "# wide,\n", "# id_vars=__________,\n", "# value_vars=__________,\n", "# var_name=__________,\n", "# value_name=__________\n", "# )\n", "# print(long)" ] }, { "cell_type": "markdown", "id": "31edcd22", "metadata": {}, "source": [ "## 17. melt 与 pivot 的往返重塑\n", "\n", "在“标识列组合唯一”的前提下，`melt` 和 `pivot` 可以完成相反方向的重塑。\n", "\n", "`pivot` 后标识列会成为索引，因此通常再使用 `.reset_index()` 恢复普通列。" ] }, { "cell_type": "markdown", "id": "127906a9", "metadata": {}, "source": [ "### 示例代码" ] }, { "cell_type": "code", "execution_count": 33, "id": "2289d2c3", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.278855Z", "iopub.status.busy": "2026-07-30T13:56:21.278747Z", "iopub.status.idle": "2026-07-30T13:56:21.286495Z", "shell.execute_reply": "2026-07-30T13:56:21.285948Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "原宽表：\n", " 城市 一月 二月\n", "0 杭州 120 130\n", "1 宁波 98 105\n", "转换成长表：\n", " 城市 月份 销量\n", "0 杭州 一月 120\n", "1 宁波 一月 98\n", "2 杭州 二月 130\n", "3 宁波 二月 105\n", "恢复为宽表：\n", " 城市 一月 二月\n", "0 宁波 98 105\n", "1 杭州 120 130\n" ] } ], "source": [ "original = pd.DataFrame({\n", " \"城市\": [\"杭州\", \"宁波\"],\n", " \"一月\": [120, 98],\n", " \"二月\": [130, 105]\n", "})\n", "\n", "long_data = pd.melt(\n", " original,\n", " id_vars=[\"城市\"],\n", " var_name=\"月份\",\n", " value_name=\"销量\"\n", ")\n", "restored = long_data.pivot(\n", " index=\"城市\",\n", " columns=\"月份\",\n", " values=\"销量\"\n", ").reset_index()\n", "\n", "restored.columns.name = None\n", "\n", "print(\"原宽表：\")\n", "print(original)\n", "print(\"转换成长表：\")\n", "print(long_data)\n", "print(\"恢复为宽表：\")\n", "print(restored)" ] }, { "cell_type": "markdown", "id": "ca35ba63", "metadata": {}, "source": [ "### 代码解读（对应上方示例）\n", "\n", "- `melt` 把一月、二月列合并为“月份”“销量”两列。\n", "- `pivot` 再让月份值成为列名，让城市成为行索引。\n", "- `reset_index()` 把城市恢复为普通列。\n", "- `restored.columns.name = None` 只移除透视产生的列轴名称，不影响数据。" ] }, { "cell_type": "markdown", "id": "a32f60ae", "metadata": {}, "source": [ "### 动手练习" ] }, { "cell_type": "code", "execution_count": 34, "id": "2f67021c", "metadata": { "execution": { "iopub.execute_input": "2026-07-30T13:56:21.288088Z", "iopub.status.busy": "2026-07-30T13:56:21.287864Z", "iopub.status.idle": "2026-07-30T13:56:21.290192Z", "shell.execute_reply": "2026-07-30T13:56:21.289657Z" } }, "outputs": [], "source": [ "# 把宽表转成长表，再恢复为宽表\n", "# original = pd.DataFrame({\n", "# \"商品\": [\"A\", \"B\"], \"一月\": [3, 2], \"二月\": [5, 4]\n", "# })\n", "# long = pd.melt(\n", "# original,\n", "# id_vars=__________,\n", "# var_name=__________,\n", "# value_name=__________\n", "# )\n", "# restored = long.pivot(\n", "# index=__________,\n", "# columns=__________,\n", "# values=__________\n", "# ).reset_index()\n", "# print(long)\n", "# print(restored)" ] }, { "cell_type": "markdown", "id": "b2ff8031", "metadata": {}, "source": [ "## 教材中的阅读拓展\n", "\n", "以下内容在 `da_8f_operate.pdf` 中有介绍，但不列入本次必须掌握范围：\n", "\n", "- **MultiIndex 层级操作**：`swaplevel`、按层级排序和按层级汇总；\n", "- **按索引连接**：`left_index`、`right_index` 以及 DataFrame `.join()`；\n", "- **concat 的层次化来源标签**：`keys`、`levels`、`names`；\n", "- **concat 的交集规则**：`join=\"inner\"`；\n", "- **复杂 stack/unstack**：指定层级、多层列和缺失组合；\n", "- **PeriodIndex 时间索引**：按季度或月份表示时间区间；\n", "- **多个数值列同时 pivot**：会产生多层列索引。\n", "\n", "连接完成后必须再次检查：结果行数、连接键重复数、未匹配键数量、关键字段缺失数和重名列含义。重塑完成后还要检查标识列组合是否唯一。" ] }, { "cell_type": "markdown", "id": "de05bbca", "metadata": {}, "source": [ "## 离场检验\n", "\n", "1. `set_index` 与 `reset_index` 分别把数据从哪里移动到哪里？\n", "2. 内连接、左连接和外连接分别保留哪些键？\n", "3. 左右连接键列名不同时使用哪两个参数？\n", "4. 为什么两个重复连接键可能让结果行数成倍增加？\n", "5. `_merge` 列中的 `both` 表示什么？\n", "6. `concat(axis=0)` 与 `concat(axis=1)` 分别沿什么方向拼接？\n", "7. `combine_first` 中哪一个对象的非缺失值优先？\n", "8. `pivot` 和 `melt` 分别把数据转向宽格式还是长格式？\n", "\n", "
\n", "点击查看参考答案\n", "\n", "1. `set_index` 把普通列移到行索引；`reset_index` 把索引恢复为普通列。\n", "2. 内连接保留交集，左连接保留左表全部键，外连接保留两表键的并集。\n", "3. `left_on` 和 `right_on`。\n", "4. 同一个键的左表每行会与右表每个匹配行组合，形成笛卡儿积。\n", "5. 该连接键在左右两表中都存在。\n", "6. `axis=0` 按行堆叠；`axis=1` 按列并排并按行索引对齐。\n", "7. 调用 `combine_first` 的主要对象。\n", "8. `pivot` 把长格式转为宽格式；`melt` 把宽格式转为长格式。\n", "\n", "
" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (skills)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.14.3" } }, "nbformat": 4, "nbformat_minor": 5 }