172 lines
6.8 KiB
Python
172 lines
6.8 KiB
Python
"""全量字段标准化与修复(不覆盖原列,只加派生列,可回滚)。
|
||
|
||
处理四类问题:
|
||
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()
|