Files
yanxuan/脚本/audit_tuimian_groups.py
Your Name 3cdf484621 普查推免重复组并按主键粒度自纠判据
按 (校,年,码,名) 分组的初版判据报出 12 组虚增 67 人,全在东南大学;
回查来源是普通推免与强基计划两份不同公示,plan_type 本就在主键里,
相加正确。改用与 PRIMARY KEY 同粒度的键重算后重复组为 0,
确认这批 451 行写入没有造成同键双来源,表不需并档。
2026-11-02 14:51:34 +08:00

157 lines
7.8 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 -*-
"""y2027_tuimian_2027 同校同码同名重复组普查(只读)。
目的:确认这批「官网 PDF 名单重做」写进去的行,与库里原有的同校同码同名行
是同一份名单的第二种载体(须并档),还是同一个人被两种口径各算一次(须去重),
还是确有两份不同的公示文件(须保留)。
"""
import collections
import os
import re
import sqlite3
ZX = r'D:/择校/数据库/zexiao_2027.db'
TAB = 'y2027_tuimian_2027'
PDF = r'D:/择校/运行暂存/agent_ingest_20261101/tm0856/raw'
OUT = r'D:/择校/审计报告/推免重复组普查_20261102.md'
pdfs = set(os.listdir(PDF)) if os.path.isdir(PDF) else set()
z = sqlite3.connect('file:' + ZX + '?mode=ro', uri=True)
COLS = ['rowid', 'school', 'year', 'college', 'college_code', 'major_code', 'major_name',
'kind', 'value', 'basis', 'plan_type', 'source_url', 'local_file', 'note', 'data_flag']
rows = [dict(zip(COLS, r)) for r in z.execute(
'select %s from %s' % (','.join(COLS), TAB))]
z.close()
PK = ('school', 'year', 'college', 'college_code', 'major_code', 'major_name', 'kind',
'basis', 'plan_type')
import json
_cand = json.load(open(r'D:/择校/运行暂存/agent_ingest_20261101/cand_tm0856_pdf.json', encoding='utf-8'))
newpk = {tuple(c.get(k) or '' for k in PK) for c in _cand}
NEW = [r for r in rows if tuple(r.get(k) or '' for k in PK) in newpk]
OLD = [r for r in rows if tuple(r.get(k) or '' for k in PK) not in newpk]
rep = []
rep.append('# 推免表重复组普查(2026-11-02)')
rep.append('')
rep.append('- 表 %s 共 %d 行 / %d 校;本批 PDF 名单重做写入 %d 行 / %d 校,其余 %d 行为既有行'
% (TAB, len(rows), len({r['school'] for r in rows}), len(NEW),
len({r['school'] for r in NEW}), len(OLD)))
rep.append('- 判据:分组键取与表 PRIMARY KEY 同粒度的 (校, 年, 院系, 院系码, 专业码, 专业名, '
'层次, basis, plan_type),组内出现 ≥2 个不同 local_file 才算重复;'
'跨 plan_type 同键另列(强基等是另一批人,不算重复)')
rep.append('')
groups = collections.defaultdict(list)
for r in rows:
groups[(r['school'], r['year'], r['major_code'], r['major_name'], r['plan_type'])].append(r)
dup = {k: v for k, v in groups.items() if len({x['local_file'] for x in v}) > 1}
# 跨计划同键:不是重复计数(普通推免与强基是两批人),单独列出备查
xplan = collections.defaultdict(list)
for r in rows:
xplan[(r['school'], r['year'], r['major_code'], r['major_name'])].append(r)
xdup = {k: v for k, v in xplan.items() if len({x['plan_type'] for x in v}) > 1}
rep.append('## 一、总览')
rep.append('')
rep.append('| 指标 | 值 |')
rep.append('|---|---|')
rep.append('| 分组键总数(含 plan_type,与表主键同粒度) | %d |' % len(groups))
rep.append('| 重复组数(同键 ≥2 个来源文件) | %d |' % len(dup))
rep.append('| 重复组涉及行数 | %d |' % sum(len(v) for v in dup.values()))
rep.append('| 重复组虚增人数(组内合计 − 组内最大值) | %d |'
% sum(sum(int(x['value']) for x in v) - max(int(x['value']) for x in v) for v in dup.values()))
rep.append('| 同时有实测与名单推导两种 basis 的组 | %d |'
% sum(1 for v in dup.values() if len({x['basis'] for x in v}) > 1))
rep.append('| 跨 plan_type 同键组(不算重复,另列) | %d |' % len(xdup))
rep.append('')
# 虚增人数最大的组
worst = sorted(dup.items(),
key=lambda kv: -(sum(int(x['value']) for x in kv[1]) - max(int(x['value']) for x in kv[1])))
rep.append('## 二、虚增最多的 15 组')
rep.append('')
rep.append('| 校 | 码 | 名 | 组内行 | 合计 | 取max | 虚增 | basis | 来源文件 |')
rep.append('|---|---|---|---|---|---|---|---|---|')
for k, v in worst[:15]:
tot, mx = sum(int(x['value']) for x in v), max(int(x['value']) for x in v)
rep.append('| %s | %s | %s | %d | %d | %d | **%d** | %s | %s |' % (
k[0], k[2], k[3], len(v), tot, mx, tot - mx,
'/'.join(sorted({x['basis'] for x in v})),
'<br>'.join((x['local_file'] or '-')[:40] for x in sorted(v, key=lambda y: -int(y['value'])))))
rep.append('')
rep.append('## 三、重复组按 basis 组合分类')
rep.append('')
combo = collections.Counter(
tuple(sorted({x['basis'] for x in v})) for v in dup.values())
for cs, n in combo.most_common():
rep.append('- %s:%d 组' % (' + '.join(cs), n))
rep.append('')
# 关键问题:名单推导(roster) 与 官方计数(count_direct) 同键并存 → 两种口径各算一次
both = {k: v for k, v in dup.items() if len({x['basis'] for x in v}) > 1}
rep.append('## 四、实测与推导同键并存(%d 组)——这两种口径绝不能相加' % len(both))
rep.append('')
for k, v in list(both.items())[:20]:
per = collections.defaultdict(int)
for x in v:
per[(x['basis'], x['plan_type'])] += int(x['value'])
rep.append('- %s %s %s → %s' % (k[0], k[2], k[3], dict(per)))
rep.append('')
# 五、同 basis 内多来源文件(真正的重复计数风险)
same_b = {k: v for k, v in dup.items() if len({x['basis'] for x in v}) == 1}
rep.append('## 五、同 basis 内多来源文件(%d 组)' % len(same_b))
rep.append('')
sb = collections.Counter(tuple(sorted(x['basis'] for x in v)[:1]) for v in same_b.values())
for cs, n in sb.most_common():
rep.append('- %s:%d 组' % (cs[0], n))
rep.append('')
rep.append('前 20 组明细:')
rep.append('')
rep.append('| 校 | 码 | 名 | 院系 | basis | 各行值/来源 |')
rep.append('|---|---|---|---|---|---|')
for k, v in list(same_b.items())[:20]:
rep.append('| %s | %s | %s | %s | %s | %s |' % (
k[0], k[2], k[3], (v[0]['college'] or '-')[:12], v[0]['basis'],
'<br>'.join('%s→%s' % (x['value'], (x['local_file'] or '-')[:34])
for x in sorted(v, key=lambda y: -int(y['value'])))))
rep.append('')
# 六、抽样复核:这些组里新写的一批是否就是同一份名单
newkeys = collections.Counter((r['school'], r['year'], r['major_code'], r['major_name'],
r['plan_type']) for r in NEW)
in_dup = sum(1 for k in newkeys if k in dup)
rep.append('## 六、新写入行与重复组的关系')
rep.append('')
rep.append('- 新写 %d 校批次,键 %d 个,其中落在重复组里的 %d 个' % (
len({r['school'] for r in NEW}), len(newkeys), in_dup))
rep.append('')
cnt = collections.Counter()
for k, v in dup.items():
if k in newkeys and len({x['basis'] for x in v}) > 1:
cnt['新行所在组且basis混用'] += 1
elif k in newkeys:
cnt['新行所在组,basis单一'] += 1
for a, b in cnt.most_common():
rep.append('- %s:%d 组' % (a, b))
rep.append('')
# 判定
tot_new = sum(int(r['value']) for r in NEW)
tot_all = sum(int(r['value']) for r in rows)
mx_fix = sum(sum(int(x['value']) for x in v) - max(int(x['value']) for x in v) for v in dup.values())
rep.append('## 七、判定')
rep.append('')
rep.append('- 表内推免总人数(逐行相加)= %d;若每组只保留最大值则 = %d,差 %d。'
% (tot_all, tot_all - mx_fix, mx_fix))
rep.append('- 重复组数 %d / 分组总数 %d,虚增占总量 %.1f%%。'
% (len(dup), len(groups), 100.0 * mx_fix / max(tot_all, 1)))
rep.append('- 跨 plan_type 同键 %d 组(东南大学等把强基计划另出一份计数表)属**不同的人**,'
'相加正确,不计入重复。' % len(xdup))
rep.append('- 结论按主键粒度给出:与表 PRIMARY KEY 同粒度的重复组 = %d,即库里不存在同键双来源;'
'上一版判据漏了 plan_type,把东南大学「普通推免 + 强基计划」两份公示误判成重复。' % len(dup))
open(OUT, 'w', encoding='utf-8').write('\n'.join(rep) + '\n')
print('\n'.join(rep[:14]))
print('\n'.join(rep[-8:]))
print('\n报告写入 %s;明细共 %d 行' % (OUT, len(rep)))