Files
yanxuan/数据库/check_numeric_purity.py

131 lines
5.6 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# -*- 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()