#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""样本构成核对：单位去重 / 城市 / 区域 / 岗位 / 使用时长 —— 用于与报告数字对账。"""
import json
import re
from collections import Counter

import pandas as pd

P = ('/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/'
     'doc_3f5eafe34e61_宝锐生物客户满意度调查问卷-2026（按选项文本）-全部.xlsx')
d = pd.read_excel(P, sheet_name='答卷数据')
C = list(d.columns)
print('=== 末段列名（找单位/城市/岗位）===')
for i, c in enumerate(C[108:], start=108):
    print('  [%3d] %s' % (i, str(c)[:80]))

main = d[d['答卷状态'].astype(str).str.contains('质控通过', na=False)]

# 找单位名称列（含「单位」或「公司」或「您所在」）
unit_col = next((i for i, c in enumerate(C) if re.search(r'单位名称|公司名称|您所在的?(单位|公司|机构)', str(c))), None)
city_col = next((i for i, c in enumerate(C) if re.search(r'城市|地区|所在地', str(c))), None)
post_col = next((i for i, c in enumerate(C) if re.search(r'岗位|职位|职务', str(c))), None)
print()
print('识别到的列：单位=%s 城市=%s 岗位=%s' % (unit_col, city_col, post_col))

if unit_col is not None:
    u = main[C[unit_col]].astype(str).str.strip()
    u = u[(u != '') & (u != 'nan')]
    cnt = Counter(u)
    print()
    print('=== 单位去重（主口径 n=%d）===' % len(main))
    print('  有单位名的答卷数:', len(u))
    print('  唯一单位数:', len(cnt))
    multi = {k: v for k, v in cnt.items() if v >= 2}
    print('  提交 ≥2 份的单位数:', len(multi), '｜ 涉及答卷:', sum(multi.values()),
          '（占 %.1f%%）' % (100.0 * sum(multi.values()) / len(u)))
    print('  TOP10 多份单位:', sorted(multi.items(), key=lambda x: -x[1])[:10])

if city_col is not None:
    c2 = main[C[city_col]].astype(str).str.strip()
    c2 = c2[(c2 != '') & (c2 != 'nan')]
    cc = Counter(c2)
    print()
    print('=== 城市分布（唯一 %d 个城市）===' % len(cc))
    for k, v in cc.most_common(14):
        print('   %-10s %3d (%.1f%%)' % (k, v, 100.0 * v / len(c2)))
    prov = {'广东': ['深圳', '广州', '珠海', '东莞', '中山', '佛山', '惠州', '汕头'],
            '长三角': ['上海', '杭州', '南京', '苏州', '宁波', '无锡', '常州', '合肥', '温州', '嘉兴', '湖州', '绍兴', '金华'],
            '京津': ['北京', '天津']}
    print()
    print('=== 区域汇总 ===')
    tot = len(c2)
    other = 0
    for nm, ks in prov.items():
        s = sum(v for k, v in cc.items() if any(x in k for x in ks))
        other += s
        print('   %-6s %3d (%.1f%%)' % (nm, s, 100.0 * s / tot))
    print('   其它   %3d (%.1f%%)' % (tot - other, 100.0 * (tot - other) / tot))

if post_col is not None:
    p = main[C[post_col]].astype(str).str.strip()
    p = p[(p != '') & (p != 'nan')]
    print()
    print('=== 岗位分布（主口径）===')
    for k, v in Counter(p).most_common(10):
        print('   %-16s %3d (%.1f%%)' % (k[:16], v, 100.0 * v / len(p)))