Files
yanxuan/脚本/fix_normalize.py

172 lines
6.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.
"""全量字段标准化与修复(不覆盖原列,只加派生列,可回滚)。
处理四类问题:
1. 学费单位混杂 → 新增 tuition_year(年费/元)、tuition_total(全程/元)、tuition_kind
2. 学制混杂(含区间 2-3年、2年-3年、2~3)→ 新增 duration_min / duration_max
3. dir_hist 专业名称=专业代码(614 行)→ 用 dim_major / chsi 回填
4. getfs 逻辑矛盾 → 新增 data_flag 标记(不删行)
运行:python -B 脚本/fix_normalize.py
"""
import re
import sqlite3
DB = r'D:\择校\数据库\zexiao_2027.db'
con = sqlite3.connect(DB, timeout=600)
con.execute('PRAGMA synchronous=OFF')
# ---------- 学费解析 ----------
def parse_fee(s):
"""返回 (年费元, 全程元, 类型)。
支持:0.8万元/年、8000元/年、8000元/学年、2.4万元/全部、24000元/全部、
1.2万/生、纯数字、待定、复合(非定向:8000元/学年,定向:10000元/学年)
"""
t = (s or '').strip()
if not t or t in ('待定', '-', '—'):
return None, None, 'unknown'
# 复合值:取第一段(非定向)
m_comp = re.match(r'^非定向[::]\s*(.+?)[,,]\s*定向', t)
if m_comp:
t = m_comp.group(1).strip()
# 数值 + 单位
m = re.match(r'^([\d.]+)\s*(万元|万|元)\s*[/每]?\s*(年|学年|全部|生)?', t)
if not m:
return None, None, 'unparsed'
val = float(m.group(1))
unit = m.group(2)
per = m.group(3) or ''
yuan = val * 10000 if unit in ('万元', '万') else val
if per in ('年', '学年'):
return round(yuan), None, 'per_year'
if per == '全部':
return None, round(yuan), 'total'
if per == '生':
return None, round(yuan), 'per_student'
return None, round(yuan), 'unit_unknown'
# ---------- 学制解析 ----------
def parse_duration(s):
"""返回 (最短, 最长, 类型)。支持 3 / 2.5 / 2年-3年 / 3-5年 / 2~3 / 2("硕师计划"4年)"""
t = (s or '').strip()
if not t:
return None, None, 'empty'
nums = re.findall(r'\d+(?:\.\d+)?', t)
if not nums:
return None, None, 'unparsed'
vals = [float(x) for x in nums[:2]]
lo, hi = min(vals), max(vals)
kind = 'single' if len(vals) == 1 or lo == hi else 'range'
if '硕师' in t:
kind = 'special'
return lo, hi, kind
print('=== 1. 学费解析 ===')
cols = [r[1] for r in con.execute('PRAGMA table_info(pd_dir_detail)')]
for c, ddl in [('tuition_year', 'INTEGER'), ('tuition_total', 'INTEGER'),
('tuition_kind', 'TEXT'), ('duration_min', 'REAL'),
('duration_max', 'REAL'), ('duration_kind', 'TEXT')]:
if c not in cols:
con.execute('ALTER TABLE pd_dir_detail ADD COLUMN %s %s' % (c, ddl))
con.commit()
rows = con.execute('SELECT id, tuition, duration FROM pd_dir_detail').fetchall()
upd = []
kinds = {}
for i, fee, dur in rows:
fy, ft, fk = parse_fee(fee)
dlo, dhi, dk = parse_duration(dur)
upd.append((fy, ft, fk, dlo, dhi, dk, i))
kinds[fk] = kinds.get(fk, 0) + 1
con.executemany('''UPDATE pd_dir_detail SET tuition_year=?, tuition_total=?, tuition_kind=?,
duration_min=?, duration_max=?, duration_kind=? WHERE id=?''', upd)
con.commit()
print(' 学费类型分布:')
for k, v in sorted(kinds.items(), key=lambda x: -x[1]):
print(' %-14s %d' % (k, v))
# 抽查:万元/年 与 元/年 是否折算一致
print()
print(' 折算抽查(同校同专业同年的万元/年 vs 元/年):')
chk = con.execute('''SELECT school, code, year, tuition, tuition_year FROM pd_dir_detail
WHERE tuition_year IS NOT NULL AND tuition_kind='per_year'
ORDER BY tuition_year DESC LIMIT 5''').fetchall()
for r in chk:
print(' %s/%s/%s %r → %d 元/年' % r)
print()
print('=== 2. 学制解析 ===')
dk = {}
for r in con.execute('SELECT duration_kind, COUNT(*) FROM pd_dir_detail GROUP BY 1 ORDER BY 2 DESC'):
dk[r[0]] = r[1]
print(' %-10s %d' % (r[0], r[1]))
print()
print(' 区间样例:')
for r in con.execute('''SELECT duration, duration_min, duration_max FROM pd_dir_detail
WHERE duration_kind='range' GROUP BY duration LIMIT 8'''):
print(' %-14r → %s ~ %s' % (r[0], r[1], r[2]))
# ---------- 3. dir_hist 专业名称回填 ----------
print()
print('=== 3. dir_hist 专业名称回填 ===')
NAME = {}
for r in con.execute("SELECT c_code, major_name FROM ggyx_dim_major WHERE IFNULL(c_code,'')<>''"):
NAME.setdefault(r[0], (r[1] or '').split('/')[-1] if r[1] else '')
for r in con.execute('SELECT zydm, zymc FROM chsi_2027_dir GROUP BY zydm'):
NAME.setdefault(r[0], r[1] or '')
bad = con.execute('SELECT rowid, 专业代码 FROM dir_hist WHERE 专业名称=专业代码').fetchall()
fixed = miss = 0
for rid, code in bad:
nm = NAME.get(code)
if nm:
con.execute('UPDATE dir_hist SET 专业名称=? WHERE rowid=?', (nm, rid))
fixed += 1
else:
miss += 1
con.commit()
print(' 待修 %d:回填成功 %d,无名称可查 %d' % (len(bad), fixed, miss))
# 同时修含科目符号的 23 行
bad2 = con.execute("SELECT rowid, 专业代码 FROM dir_hist WHERE 专业名称 LIKE '%①%' OR 专业名称 LIKE '%政治理论%'").fetchall()
f2 = 0
for rid, code in bad2:
nm = NAME.get(code)
if nm:
con.execute('UPDATE dir_hist SET 专业名称=? WHERE rowid=?', (nm, rid))
f2 += 1
con.commit()
print(' 科目错位 %d 行:回填 %d' % (len(bad2), f2))
# ---------- 4. getfs 逻辑矛盾标记 ----------
print()
print('=== 4. getfs 逻辑矛盾标记 ===')
gcols = [r[1] for r in con.execute('PRAGMA table_info(ggyx_getfs_hist_all)')]
if 'data_flag' not in gcols:
con.execute('ALTER TABLE ggyx_getfs_hist_all ADD COLUMN data_flag TEXT')
con.execute("UPDATE ggyx_getfs_hist_all SET data_flag=NULL")
n1 = con.execute('''UPDATE ggyx_getfs_hist_all SET data_flag='nlq_gt_fs'
WHERE nlq_total>fs_total''').rowcount
n2 = con.execute('''UPDATE ggyx_getfs_hist_all SET data_flag='taotai_mismatch'
WHERE data_flag IS NULL AND taotai_total<>fs_total-nlq_total''').rowcount
con.commit()
print(' nlq_gt_fs %d 行,taotai_mismatch %d 行(不删,仅标记)' % (n1, n2))
print()
print('=== 完成,验证 ===')
for sql, label in [
('SELECT COUNT(*) FROM pd_dir_detail WHERE tuition_year IS NOT NULL', '学费已折年费'),
('SELECT COUNT(*) FROM pd_dir_detail WHERE tuition_total IS NOT NULL', '学费已折全程'),
('SELECT COUNT(*) FROM pd_dir_detail WHERE tuition_kind="unparsed"', '学费无法解析'),
('SELECT COUNT(*) FROM pd_dir_detail WHERE duration_min IS NOT NULL', '学制已解析'),
('SELECT COUNT(*) FROM dir_hist WHERE 专业名称=专业代码', '仍名称=代码(应为0)'),
('SELECT COUNT(*) FROM ggyx_getfs_hist_all WHERE data_flag IS NOT NULL', '已标记矛盾行'),
]:
print(' %-24s %s' % (label, con.execute(sql).fetchone()[0]))
con.close()