多表数据的合并、清洗、统计与核对
表中一行是一条记录，一列是一个字段，第一行表头规定有哪些字段。多份表要合并，表头必须一字不差。
表中的一行，对应一个具体对象。
表中的一列，对应对象的一项属性。
行列对齐的数据。程序能自动处理数据，前提是数据具备这个形状。
逗号分隔值。纯文本表格，一行一条记录，字段之间用逗号隔开。
Excel 表格文件，压缩过的二进制格式，要由专门的库解析。
通用数据格式。{ } 是对象，[ ] 是列表，两者可以互相嵌套。
一份表写成"班级 "，末尾多一个空格
合并后多出一列，两列都不全
收表前发统一模板；合并前去掉表头首尾空格
字段对齐，合成一张
表格发给别的程序处理，存成 .xlsx
对方必须用专门的库解析
只交换数据用 .csv，要保留公式、格式才用 .xlsx
任何程序都能读
多一个空格、括号用了全角、英文大小写不同，程序都当成两个不同的字段。
十份表变成一张汇总，要经过读取、合并、清洗、统计、导出五步。读取时给每条记录标注来源文件，事后抽查全靠这一列。
…\第11章> python 第11章演示_合并汇总.py 找到 10 份表格 （逐份读取，此处省略十行） 合并后共 99 行 去掉全空行后剩 97 行 平时分为空的记录共 6 条，已按 0 计入 班级 人数 总评平均分 数据2301 28 73.34 数据2302 36 75.45 数据2303 33 80.10 已导出 汇总.xlsx ，原始表格未作任何改动
十份表直接摞在一起
发现一条数字不对，找不回是哪份表填错的
读入时加一列"来源文件"
抽查时顺着这一列回到原表
| 生成的原始表 | 事先安排的脏数据 |
|---|---|
| 成绩表_07 | 中间两个全空行 |
| 成绩表_09 | 班级列含全角空格 |
| 成绩表_03、05、08 | 平时分有空值 |
脚本同时生成 原始表格（xlsx）与 原始表格_CSV 两个文件夹，各十份；重复运行会清空重建。
不符合预期形状的数据叫脏数据。清洗要先定规则、写下来，再让程序按规则统一处理，不逐条去改。
不符合预期形状的数据：全空行、首尾空格（含全角空格）、写法不一致、重复记录、缺失值。
按事先定好、写下来的规则，逐类处理脏数据。
把指向同一个对象的多条记录合成一条。
| 类别 | 处理规则 | 常见错误 |
|---|---|---|
| 全空行 | 五个字段全为空才删 | 任一字段为空就删，只漏填一项的记录也被删掉 |
| 首尾空格 | 去掉文本字段首尾的空格，半角、全角一起去 | 没想到全角空格，分组时多出一个"同名"班级 |
| 写法不一致 | 先把这一列出现过的取值全部列出来看一遍，再定一张映射表，把同义写法归到一处 | 凭印象替换，漏掉自己没想到的写法 |
| 重复记录 | 先说清哪个字段唯一（学号、身份证号、订单号），按它去重，保留最先出现的一条，打印去掉了几条 | 按"整行完全一样"去重，改过一个字的重复行留了下来 |
| 缺失值 | 记为 0、按其余字段折算、整条剔除，三选一，全表一致，报告条数（见 11.4） | 这一列填 0、那一列剔除，同一张表两套口径 |
规则不写下来，同一批数据两次处理会得到不同的结果，而结果本身看不出差别。
同一个人交了两次，第二次改了一个字
两条都留下，人数偏多
同一个学号只留最先出现的一条，打印去掉几条
人数对得上
姓名相同就当作同一个人
重名的人被并成一个
手机号能唯一确定一个人
重名的人各留一条
缺失值可以记为 0、按其余字段折算、整条剔除。选定一种写进说明，并报告受影响的条数。
| 处理方式 | 规则 | 参与人数 | 全体平均 | 数据2301 | 数据2302 | 数据2303 |
|---|---|---|---|---|---|---|
| 记为 0 | 空的平时分按 0 分参与计算 | 97 | 76.42 | 73.34 | 75.45 | 80.10 |
| 按期末分折算 | 缺平时分的记录，总评直接取期末分 | 97 | 78.22 | 77.15 | 77.33 | 80.10 |
| 整条剔除 | 缺平时分的记录不参与统计 | 91 | 78.58 | 78.88 | 76.91 | 80.10 |
某条记录的某个字段没有数据。JSON 里写作 null。
只填补，不报告条数
平均分整体偏低两分左右，从结果上看不出来
先统计缺失几条，再填 0，并打印出来
"平时分为空的记录共 6 条，已按 0 计入"
这一列填 0、那一列剔除
看表的人无从判断平均分意味着什么
三选一，全表一致，规则写在结果旁边
受影响条数与所用规则一起交出
分组统计先按某个字段把记录归堆，再对每一堆分别计算。归堆依据的字段必须清洗干净。
先按某个字段把记录归堆，再对每一堆分别计算。
数的是该字段非空的条数。按学号计数，就是学号非空的记录数。
"数据2301"与带空格的"数据2301"直接分组
同一个班拆成两行，两行人数都不对
先去掉班级列首尾空格，再分组统计
组数与实际班级数一致
算法是完成一件事的固定步骤。写规则的人不必会实现算法，但要知道自己的要求属于哪一类，数据变多时代价涨多快。
完成一件事的固定步骤。筛选、排序、去重、分组各有各的算法。
程序只读原始文件，结果写入新文件。交出前核对行数、分组之和、抽查三条，三项都对才认这张表。
程序不报错，每一行格式都对、小数都保留两位，只是数字是错的。
"就地修改"，或用同名文件覆盖
清洗规则一改，原始数据已经没了
结果一律写入新文件
规则改几次，都能从原始数据重跑
格式都对、排得整齐，直接交出
错误数字没人发现
行数、分组之和、抽查三条
错误在交出前被发现
提示词里贴真实的表头和前三行，不用文字描述数据。已知的脏数据逐条列出，列几条 AI 处理几条。
不列出已知的脏数据
AI 处理了空行，没处理首尾空格，更想不到全角空格
空行、空格（含全角）、空值，有几类写几类
列几条，处理几条
运行之后我发现还有两种情况没处理： 第一，有两份表的表头写成了"班级 "，末尾多一个空格，合并后多出一列。 第二，有三条记录的学号重复了，是同一个人交了两次。 请在原脚本的基础上补上这两条处理： ① 合并之前先把每份表的表头首尾空格去掉； ② 学号重复时保留最先出现的一条，并打印一共去掉了几条重复记录。 【不要改动】设置区，以及已经写好的清洗与统计部分。 只给出需要新增或修改的那几行，不要重写整个脚本。
在现有脚本的末尾加一段抽查代码： 从合并后的明细里随机取三条记录，每条打印出：来源文件名、学号、姓名、平时分、期末分、 按公式算出的总评。格式排整齐，方便我照着回原表逐项核对。 如果某条记录的平时分是被填补过的 0，请在该行末尾标注"（平时分原为空）"。 只给出新增的那几行。
| 提示词中的要求 | 缺少时 AI 常见的写法 | 代码中的对应 |
|---|---|---|
| 贴上真实的表头和前三行 | 按想象的列名写 df['成绩'] 或 df['score']，运行到那一行报 KeyError | 列名与表头一致 |
| 给每条记录标注来自哪份表 | 发现一条数字不对，找不回是哪份表填错的 | one['来源文件'] = one_file.name |
| 去掉五个字段全为空的行 | 写成 how='any'，只漏填一项的记录也被删掉，人数少一截 | dropna(…, how='all') |
| 已知脏数据逐条列出 | 只处理空行，漏掉首尾空格和全角空格 | str.strip() |
| 统计缺失记录共几条，并打印 | 空值按 0 计入却不说明，平均分偏低而无从察觉 | 先 isna().sum()，再 fillna(0) |
| 原始文件一个都不要改动 | 结果写回其中一份原表，或用同名文件覆盖 | 写入新文件 汇总.xlsx |
仅标准库版读取 原始表格_CSV，不需要安装第三方库；处理逻辑与 pandas 版相同，算出的人数与平均分逐项一致。
from pathlib import Path import pandas as pd # ===== 设置区：只需要改下面四项 ===== FOLDER = '原始表格' # 存放原始表的文件夹 OUTPUT = '汇总.xlsx' # 导出的文件名 GROUP_BY = '班级' # 按哪一个字段分组统计 WEIGHT = (0.4, 0.6) # 总评权重：平时分 40%，期末分 60% # ② 逐份读取并合并。额外记下每条记录来自哪份表，供抽查时溯源 for one_file in files: one = pd.read_excel(one_file) one['来源文件'] = one_file.name tables.append(one) data = pd.concat(tables, ignore_index=True) # ③ 清洗一：去掉五个字段全为空的行 data = data.dropna(subset=['学号', '姓名', '班级', '平时分', '期末分'], how='all') # ④ 清洗二：去掉文本字段首尾的空格，全角空格同样会被去掉 for col in ['姓名', '班级']: data[col] = data[col].str.strip() # ⑤ 缺失值：先数清楚有几条，再统一填 0 missing = int(data['平时分'].isna().sum()) data['平时分'] = data['平时分'].fillna(0) print('平时分为空的记录共', missing, '条，已按 0 计入') # ⑥ 计算总评 ⑦ 分组统计：按班级计算人数与总评平均分 data['总评'] = data['平时分'] * WEIGHT[0] + data['期末分'] * WEIGHT[1] summary = data.groupby(GROUP_BY).agg( 人数=('学号', 'count'), 总评平均分=('总评', 'mean'), ).round(2).reset_index() # ⑨ 导出：写入新文件，原始表格一个都不改动 with pd.ExcelWriter(out) as writer: data.to_excel(writer, sheet_name='合并明细', index=False) summary.to_excel(writer, sheet_name='按班级汇总', index=False)
本次上机的成果就是作业 3。先只读一份确认读对，再合并统计，最后抽三条回原表核对。
| 命令 | 作用 |
|---|---|
| pip install pandas openpyxl | 一次装两个库。pandas 负责表格计算，openpyxl 负责读写 .xlsx；装一次，之后写多少脚本都能用 |
| pip install pandas openpyxl -i 镜像地址 | 默认源超时时改用国内镜像。镜像地址查学校或公开镜像站的说明 |
| pip list | 列出已装的库，确认这两个确实装上了 |
| python -c "import pandas" | 没有任何输出表示能正常导入；有报错说明装的位置不对 |
至少六份格式相同的数据，其中两份带脏数据。可以改造生成脚本；脏数据自己安排并记下来，验收时要说明处理了哪几类
产出：原始数据文件夹抄下表头与前三行，连同已知的脏数据情况写进主提示词，有几类写几类。不用文字描述代替真实数据
产出：一份完整的提示词先只读一份，打印前五行，确认列名与类型正确。这一步不通过不往下走，后面的数字都建立在读得对的基础上
产出：单份读取截图合并全部数据，完成清洗与分组统计。屏幕上要能看到行数的变化过程与缺失记录的条数，这些数字是验收依据
产出：运行截图与汇总结果抽三条记录回原表逐项核对，写明来自哪份文件、原始各项数值、手算结果与程序输出是否一致。没有这一步的作业不予验收
产出：三条核对记录把脚本与数据发给同组同学，在他的机器上跑一遍。重点确认路径没有写死，缺失值规则在说明里写清楚了
产出：一行互测记录| 抽查 | 来源文件 | 学号 | 原始各项数值 | 按公式手算 | 程序输出 | 一致 |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | ||||||
| 3 |
三条核对记录照这张表填写，随作业提交。
有报错时按报错信息在下表里查；不报错但数字不对，先查行数和组数。
| 现象 | 原因 | 处理 |
|---|---|---|
| ModuleNotFoundError: No module named 'pandas' | 第三方库还没装，或者装到了另一个 Python 环境 | 运行 pip install pandas openpyxl；仍不行时改用仅标准库版本 |
| pip 长时间无响应，最后超时 | 默认从境外服务器取包 | 命令后面加 -i 指定国内镜像源，重新安装 |
| KeyError: '成绩' | 代码里的列名与表头对不上，多半是没贴样本 | 先打印一次 data.columns 看真实列名，再把表头补进提示词重新生成 |
| 合并后多出一列，两列都不全 | 某几份表的表头首尾带空格 | 合并之前先统一去掉表头的首尾空格 |
| 分组结果里同一个班出现两行 | 分组字段没有清洗，带空格与不带空格被当成两个值 | 对分组字段先去空格，再分组统计 |
| 人数比实际少了一大截 | 去空行时用了"任一字段为空即删" | 改成"五个字段全为空才删"，即 how='all' |
| 人数比实际报名多了几个 | 有人交了两次，没有去重，或按整行去重 | 按学号去重，保留最先出现的一条，打印去掉几条 |
| 数字列被当成文本，无法求平均 | 原表里这一列混入了空格或者全角字符 | 清洗后把该列转成数值类型，转不了的记录单独打印出来 |
| PermissionError: 汇总.xlsx | 导出的文件正在 Excel 里打开着 | 关掉该文件后重新运行 |
作业 3 提交四项内容，按六条标准验收；选做一项。
| 作业 3 评分构成 | 占比 |
|---|---|
| 数据结构理解与字段设计 | 30% |
| 处理链路完整可复现 | 40% |
| 结果正确性与核对过程 | 30% |
按教师提供的作业模板填写，文档开头注明所选题目。提交渠道见课堂通知，当堂提交。
零代码应用开发 · 第 11 章 处理数据