131 lines
5.6 KiB
Python
131 lines
5.6 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""数值列纯度体检:交付表里所有「本该是数字」的列,逐格分类,量化到底有多少格不是数字。
|
||
|
||
动机:核对复试线时发现污染在扩散——考生成绩名单里的个人总分被写进了「线」的列
|
||
(大理大学门类线原文是「2026 鲁海广 总分269」),学费列则是整张表连成正文。
|
||
逐字段人工看不可扩展,这里一次扫完,按类型给出计数,并把每个坏格导出供抽查。
|
||
"""
|
||
import csv
|
||
import io
|
||
import os
|
||
import re
|
||
import sqlite3
|
||
import sys
|
||
from collections import Counter, defaultdict
|
||
|
||
sys.stdout.reconfigure(encoding="utf-8", errors="replace")
|
||
HERE = os.path.dirname(os.path.abspath(__file__))
|
||
ROOT = os.path.dirname(HERE)
|
||
DB = os.path.join(HERE, "zexiao_2027.db")
|
||
OUT = os.path.join(ROOT, "数值列污染清单.csv")
|
||
|
||
KEY = re.compile(r"复试线|分数线|国家线|校线|院线|门类线|单科|学费|学制|人数|分数|录取|招生|报录|复录比|排名")
|
||
# 这些列名会误命中 KEY,但本身就该是文本或链接
|
||
NOTNUM = re.compile(r"链接|原文|来源|url|规则|说明|备注|口径|地址|电话|时间|方式|名称|名单|标题|"
|
||
r"文件|目录|内容|要求|办法|政策|奖助|信息|学院|专业|方向|类别|科|备注|拟|联系")
|
||
CANDNO = re.compile(r"(?<!\d)\d{15}(?!\d)")
|
||
CN_WORD = re.compile(r"[一-龥]")
|
||
|
||
|
||
def kind(v):
|
||
t = (v or "").strip()
|
||
if not t:
|
||
return "空"
|
||
if CANDNO.search(t):
|
||
return "含考生名单痕迹"
|
||
if re.fullmatch(r"\d+(?:\.\d+)?", t):
|
||
return "纯数字"
|
||
if len(t) > 60:
|
||
return "整段粘连"
|
||
if "。" in t or re.search(r"详见|登录|查看|请|咨询|按.{0,6}执行|办法|规定", t):
|
||
return "指引句/正文"
|
||
core = re.sub(r"【[^】]*】", "", t)
|
||
digits = re.findall(r"\d+(?:\.\d+)?", core)
|
||
if not digits:
|
||
return "无数字文本"
|
||
if len(re.sub(r"\d", "", CN_WORD.sub("", core)).strip()) > 6:
|
||
return "夹带正文"
|
||
if re.match(r"^\s*20\d{2}\s*\D", core):
|
||
return "带年份与专业名"
|
||
if re.search(r"[–—~]|-\s*\d", core) and len(digits) >= 2:
|
||
return "区间"
|
||
if len(digits) >= 2:
|
||
return "多值并列"
|
||
return "带标注数字"
|
||
|
||
|
||
BADKINDS = ("含考生名单痕迹", "整段粘连", "夹带正文", "指引句/正文", "无数字文本")
|
||
|
||
|
||
def main():
|
||
con = sqlite3.connect("file:%s?mode=ro" % DB.replace("\\", "/"), uri=True)
|
||
con.row_factory = sqlite3.Row
|
||
total = Counter()
|
||
per_col = Counter()
|
||
bad_col = Counter()
|
||
worst = defaultdict(list)
|
||
dump = []
|
||
|
||
for tb in ("src_target_major_checked", "src_master"):
|
||
cols = [c[1] for c in con.execute("PRAGMA table_info(%s)" % tb)]
|
||
ncol = [c for c in cols if KEY.search(c) and not NOTNUM.search(c)]
|
||
keys = [c for c in ("院校", "专业代码", "学院") if c in cols][:2]
|
||
n = 0
|
||
for col in ncol:
|
||
sel = ", ".join('"%s"' % k for k in keys)
|
||
for row in con.execute('SELECT %s, "%s" v FROM %s' % (sel, col, tb)):
|
||
v = row["v"]
|
||
s = "" if v is None else (v if isinstance(v, str) else str(v))
|
||
k = kind(s)
|
||
total[k] += 1
|
||
n += 1
|
||
if s.strip():
|
||
per_col[(tb, col)] += 1
|
||
if k in BADKINDS:
|
||
bad_col[(tb, col)] += 1
|
||
if len(worst[(tb, col)]) < 3:
|
||
worst[(tb, col)].append((s[:80], k))
|
||
if len(dump) < 4000:
|
||
dump.append([tb, col, k,
|
||
" / ".join(str(row[kk] or "") for kk in keys), s[:120]])
|
||
print("%s:%d 格 / %d 个数值语义列(另有 %d 列同名但本就该是文本,已排除)"
|
||
% (tb, n, len(ncol), len(cols) - len(ncol)))
|
||
|
||
print("\n按类型合计(%d 格):" % sum(total.values()))
|
||
s = sum(total.values())
|
||
for k, v in total.most_common():
|
||
print(" %-14s %7d (%.1f%%)" % (k, v, 100.0 * v / s))
|
||
hard = sum(total[k] for k in BADKINDS)
|
||
print("其中判为「不是数值」的:%d 格(%.2f%%)" % (hard, 100.0 * hard / s))
|
||
|
||
print("\n坏格最多的列:")
|
||
for ratio, b, nn, (tb, col) in sorted(
|
||
[(bad_col[k] * 1.0 / per_col[k], bad_col[k], per_col[k], k)
|
||
for k in bad_col if per_col[k] >= 20], reverse=True)[:12]:
|
||
print(" %-28s %-24s 坏格 %4d/%4d = %3.0f%%" % (tb, col, b, nn, 100 * ratio))
|
||
for v, k in worst[(tb, col)][:1]:
|
||
print(" 例:%s | %s" % (k, v))
|
||
|
||
with io.open(OUT, "w", encoding="utf-8-sig", newline="") as fh:
|
||
w = csv.writer(fh)
|
||
w.writerow(["表", "列", "类型", "定位(院校/代码)", "原值"])
|
||
w.writerows(dump)
|
||
print("\n坏格清单导出 %d 行 → %s" % (len(dump), os.path.basename(OUT)))
|
||
|
||
# 汇总落一份,供 gen_report.py 现取,报告里的数字就不会写死
|
||
import json
|
||
top = [{"table": k[0], "column": k[1], "bad": b, "cells": per_col[k],
|
||
"pct": round(100.0 * b / per_col[k])}
|
||
for b, k in sorted(((bad_col[k], k) for k in bad_col), reverse=True)
|
||
if per_col[k] >= 20][:12]
|
||
with io.open(os.path.join(HERE, "_purity_summary.json"), "w", encoding="utf-8") as fh:
|
||
json.dump({"cells": s, "by_kind": dict(total), "hard_bad": hard,
|
||
"hard_pct": round(100.0 * hard / s, 2), "worst_columns": top,
|
||
"dump_rows": len(dump), "dump": os.path.basename(OUT)},
|
||
fh, ensure_ascii=False, indent=1)
|
||
con.close()
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|