From b14fec1599277629e10b2bc686f02fd0c2e0bec1 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期五, 18 九月 2026 16:46:30 +0800
Subject: [PATCH] fix(货运月报): 订正持续在规企业累计运距同比基期口径（v2）
---
docs/工具/持续在规企业累计运距生成.py | 468 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 468 insertions(+), 0 deletions(-)
diff --git "a/docs/\345\267\245\345\205\267/\346\214\201\347\273\255\345\234\250\350\247\204\344\274\201\344\270\232\347\264\257\350\256\241\350\277\220\350\267\235\347\224\237\346\210\220.py" "b/docs/\345\267\245\345\205\267/\346\214\201\347\273\255\345\234\250\350\247\204\344\274\201\344\270\232\347\264\257\350\256\241\350\277\220\350\267\235\347\224\237\346\210\220.py"
new file mode 100644
index 0000000..e7171cf
--- /dev/null
+++ "b/docs/\345\267\245\345\205\267/\346\214\201\347\273\255\345\234\250\350\247\204\344\274\201\344\270\232\347\264\257\350\256\241\350\277\220\350\267\235\347\224\237\346\210\220.py"
@@ -0,0 +1,468 @@
+# -*- coding: utf-8 -*-
+"""璐ц繍鏈堟姤銆屾寔缁湪瑙勪紒涓氥�嶅垎甯傚窞 1-8 鏈堢疮璁¤繍璺濆強鍚屾瘮 鍙拌处鐢熸垚锛坴2锛氬悓姣斿熀鏈熸敼涓哄畼鏂瑰叏鍙ｅ緞锛�
+
+鏁版嵁婧愶細traffic_audit.h2032_enterprise_monthly锛堛�婇亾璺揣鐗╄繍杈撴湀搴︾敓浜ф儏鍐点�婬203-2 浼佷笟鏈堟姤锛�
+ 瀵煎嚭浠� _tmp_persist_scope.tsv锛堜紒涓氱骇绱锛夈�乢tmp_persist_scope_monthly.tsv锛堜紒涓氱骇鍒嗘湀锛�
+鍙ｅ緞锛坴2 淇锛夛細
+ 路 鎸佺画鍦ㄨ浼佷笟 = 2026 骞� 1-8 鏈堣繛缁� 8 涓湀鍧囦笂鎶ユ湀鎶ョ殑浼佷笟锛�636 瀹讹紝鐢ㄦ埛 2026-09-18 纭锛夛紱
+ 路 绱骞冲潎杩愯窛锛堝叕閲岋級= 绱璐х墿鍛ㄨ浆閲� 梅 绱璐ц繍閲忥紙鍔犳潈骞冲潎锛夛紱
+ 路 鍚屾瘮 = 2026 骞� 1-8 鏈堢疮璁″�� 梅 2025 骞� 1-8 鏈堢疮璁″�� 鈭� 1锛�**鍩烘湡涓� 2025 骞� 1-8 鏈堝叏閮ㄨ涓婁紒涓氫笂鎶ユ暟**
+ 锛堝叏鐪� 664 瀹讹級锛屼笌姹囨�昏〃銆岃揣杩愩�嶉〉 T 鍒� `=S/SUM(V7,X7,鈥�,AJ7)-1` 鍚屼竴鍙ｅ緞锛�
+ v1 璇敤銆屼袱骞村潎鎸佺画鍦ㄨ鐨� 545 瀹躲�嶄綔鍩烘湡锛屽鑷村悓姣斿亸灏忥紝宸蹭慨姝ｃ��
+"""
+import io, os
+from openpyxl import Workbook
+from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
+from openpyxl.utils import get_column_letter
+
+SRC = r'D:\鏂囨。\ChatGPT\traffic-audit\_tmp_persist_scope.tsv'
+MONTHLY_SRC = r'D:\鏂囨。\ChatGPT\traffic-audit\_tmp_persist_scope_monthly.tsv'
+OUT_DIR = r'D:\鏂囨。\ChatGPT\traffic-audit\outputs'
+OUT = os.path.join(OUT_DIR, '璐ц繍鏈堟姤_鎸佺画鍦ㄨ浼佷笟_鍒嗗競宸�1-8鏈堢疮璁¤繍璺濆強鍚屾瘮_2026-09-18.xlsx')
+
+PROV = {
+ 'vol26': 31399.82, 'turn26': 4927161.77, # 宸插彂甯冿細2026 骞� 1-8 鏈堢疮璁�
+ 'vol25': 33994.45, 'turn25': 5102518.95, # 姹囨�昏〃銆岃揣杩愩�嶉〉 2025 骞� 1-8 鏈堬紙鍚屾瘮鍩烘湡锛�
+ 'vol26_08': 3665.36, 'turn26_08': 570004.44, # 宸插彂甯冿細2026 骞� 8 鏈堝綋鏈�
+ 'yoy_vol': -0.0763, 'yoy_turn': -0.0344, # 宸插彂甯冿細1-8 鏈堢疮璁″悓姣�
+}
+
+CITY_ORDER = ['姝︽眽甯�', '榛勭煶甯�', '鍗佸牥甯�', '瀹滄槍甯�', '瑗勯槼甯�', '閯傚窞甯�', '鑽嗛棬甯�', '瀛濇劅甯�', '鑽嗗窞甯�',
+ '榛勫唸甯�', '鍜稿畞甯�', '闅忓窞甯�', '鎭╂柦宸�', '浠欐甯�', '娼滄睙甯�', '澶╅棬甯�', '绁炲啘鏋舵灄鍖�']
+CITY = {'4201': '姝︽眽甯�', '4202': '榛勭煶甯�', '4203': '鍗佸牥甯�', '4205': '瀹滄槍甯�', '4206': '瑗勯槼甯�',
+ '4207': '閯傚窞甯�', '4208': '鑽嗛棬甯�', '4209': '瀛濇劅甯�', '4210': '鑽嗗窞甯�', '4211': '榛勫唸甯�',
+ '4212': '鍜稿畞甯�', '4213': '闅忓窞甯�', '4228': '鎭╂柦宸�',
+ '429004': '浠欐甯�', '429005': '娼滄睙甯�', '429006': '澶╅棬甯�', '429021': '绁炲啘鏋舵灄鍖�'}
+MONTHS26 = ['2026-%02d' % m for m in range(1, 9)]
+
+
+def city_of(rc):
+ rc = (rc or '').strip()
+ return CITY[rc] if rc in CITY else CITY.get(rc[:4], '鏈瘑鍒�(%s)' % rc)
+
+
+def num(x):
+ try:
+ return float(x)
+ except Exception:
+ return 0.0
+
+
+def dist(f, t):
+ return (t / f) if f else None
+
+
+def growth(now, base):
+ return (now / base - 1) if (now is not None and base) else None
+
+
+# ---------- 璇讳紒涓氱骇绱鏁版嵁 ----------
+rows = []
+with io.open(SRC, 'r', encoding='utf-8') as f:
+ hdr = f.readline().rstrip('\r\n').split('\t')
+ for line in f:
+ p = line.rstrip('\r\n').split('\t')
+ if len(p) < len(hdr):
+ continue
+ d = dict(zip(hdr, p))
+ if int(num(d['m26'])) != 8:
+ continue
+ rows.append(dict(city=city_of(d['rc']), rc=d['rc'], en=d['en'], ec=d['ec'], ucc=d['ucc'],
+ f26=num(d['f26']), t26=num(d['t26']),
+ f25=num(d['f25']), t25=num(d['t25']), cmp=(int(num(d['m25'])) == 8)))
+
+by_city = {}
+for r in rows:
+ c = by_city.setdefault(r['city'], dict(n=0, f26=0.0, t26=0.0))
+ c['n'] += 1
+ c['f26'] += r['f26']; c['t26'] += r['t26']
+
+tot = dict(n=0, f26=0.0, t26=0.0)
+for c in by_city.values():
+ for k in tot:
+ tot[k] += c[k]
+by_city_cmp = {}
+for r in rows:
+ if not r['cmp']:
+ continue
+ c = by_city_cmp.setdefault(r['city'], dict(n=0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0))
+ c['n'] += 1
+ c['f26c'] += r['f26']; c['t26c'] += r['t26']
+ c['f25c'] += r['f25']; c['t25c'] += r['t25']
+tot_cmp = dict(n=0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0)
+for c in by_city_cmp.values():
+ for k in tot_cmp:
+ tot_cmp[k] += c[k]
+unknown = sorted(k for k in by_city if k.startswith('鏈瘑鍒�'))
+persist_names = set(r['en'] for r in rows)
+
+# ---------- 璇讳紒涓氱骇鍒嗘湀鏁版嵁 ----------
+cust = {}
+with io.open(MONTHLY_SRC, 'r', encoding='utf-8') as f:
+ for line in f:
+ p = line.rstrip('\r\n').split('\t')
+ if len(p) < 5 or p[0] == 'report_period':
+ continue
+ per, rc, en, fr, tu = p[0], p[1], p[2], p[3], p[4]
+ cust.setdefault((per, city_of(rc)), {})[en] = (num(fr), num(tu))
+
+
+def city_month(period, city, names=None):
+ d = cust.get((period, city))
+ if not d:
+ return None, 0.0, 0.0
+ fr = tu = 0.0
+ for en, v in d.items():
+ if names is None or en in names:
+ fr += v[0]; tu += v[1]
+ return dist(fr, tu), fr, tu
+
+
+# ---------- 2025 骞� 1-8 鏈堝叏鍙ｅ緞鍩烘湡 ----------
+B25 = {}
+for (per, city), d in cust.items():
+ if not ('2025-01' <= per <= '2025-08'):
+ continue
+ c = B25.setdefault(city, dict(ents=set(), f=0.0, t=0.0))
+ for en, v in d.items():
+ c['ents'].add(en)
+ c['f'] += v[0]; c['t'] += v[1]
+tot25 = dict(ents=set(), f=0.0, t=0.0)
+for c in B25.values():
+ tot25['ents'] |= c['ents']
+ tot25['f'] += c['f']; tot25['t'] += c['t']
+
+# ---------- 鏍峰紡 ----------
+THIN = Side(style='thin', color='BFBFBF')
+BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
+HDR_FILL = PatternFill('solid', fgColor='DDEBF7')
+TOT_FILL = PatternFill('solid', fgColor='FFF2CC')
+TITLE_FILL = PatternFill('solid', fgColor='1F4E79')
+CT = Alignment(horizontal='center', vertical='center', wrap_text=True)
+LT = Alignment(horizontal='left', vertical='center', wrap_text=True)
+RT = Alignment(horizontal='right', vertical='center')
+NF2, NF1, NFP = '#,##0.00', '#,##0.0', '0.00%'
+
+wb = Workbook()
+
+# ---------------- Sheet1 璇存槑涓庡彛寰� ----------------
+ws = wb.active
+ws.title = '璇存槑涓庡彛寰�'
+ws.sheet_view.showGridLines = False
+ws.column_dimensions['A'].width = 16
+ws.column_dimensions['B'].width = 106
+t = ws.cell(1, 1, '璐ц繍鏈堟姤銆屾寔缁湪瑙勪紒涓氥�嶅垎甯傚窞 1-8 鏈堢疮璁¤繍璺濆強鍚屾瘮鎯呭喌琛�')
+t.font = Font(bold=True, size=15, color='FFFFFF')
+t.fill = TITLE_FILL
+t.alignment = Alignment(horizontal='center', vertical='center')
+ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2)
+ws.row_dimensions[1].height = 30
+
+info = [
+ ('鏁版嵁鏉ユ簮', '绯荤粺鏁版嵁搴� traffic_audit.h2032_enterprise_monthly銆婇亾璺揣鐗╄繍杈撴湀搴︾敓浜ф儏鍐点�嬶紙H203-2锛変紒涓氭湀鎶ワ紝'
+ '鍙� 2025 骞� 1 鏈堚��2026 骞� 8 鏈堝叏閮ㄤ紒涓氱骇涓婃姤璁板綍銆�'),
+ ('鎸佺画鍦ㄨ浼佷笟', '2026 骞� 1鈥�8 鏈堣繛缁� 8 涓湀鍧囦笂鎶ユ湀鎶ョ殑浼佷笟锛屽叡 %d 瀹讹紙2026 骞村悇鏈堜笂鎶ヤ紒涓氭暟鍧囦负 636 瀹讹紝鍏ㄦ湡涓�鑷达紱'
+ '鍙ｅ緞缁忕敤鎴� 2026-09-18 纭锛夈��' % tot['n']),
+ ('绱杩愯窛鍙ｅ緞', '绱骞冲潎杩愯窛锛堝叕閲岋級= 1鈥�8 鏈堢疮璁¤揣鐗╁懆杞噺 梅 1鈥�8 鏈堢疮璁¤揣杩愰噺锛堝姞鏉冨钩鍧囷級銆傛簮琛ㄥ崟浣嶄负鍚ㄣ�佸惃鍏噷锛�'
+ '鏈〃鎶樼畻涓轰竾鍚ㄣ�佷竾鍚ㄥ叕閲岋紝姣斿�间笉鍙樸��'),
+ ('鍚屾瘮鍙ｅ緞', '鍚屾瘮 = 2026 骞� 1鈥�8 鏈堢疮璁″�� 梅 2025 骞� 1鈥�8 鏈堢疮璁″�� 鈭� 1銆�**鍩烘湡涓� 2025 骞� 1鈥�8 鏈堝叏閮ㄨ涓婁紒涓氫笂鎶ユ暟**'
+ '锛堝叏鐪� %d 瀹讹級锛屼笌姹囨�昏〃銆岃揣杩愩�嶉〉 T 鍒楀叕寮� =S/SUM(V7,X7,鈥�,AJ7)-1 鍚屼竴鍙ｅ緞锛屽彲涓庡彂甯冩姤鍛婄洿鎺ュ榻愶細'
+ '鍏ㄧ渷绱璐ц繍閲忓悓姣� 鈭�7.63%%銆佸懆杞噺鍚屾瘮 鈭�3.44%%銆佸钩鍧囪繍璺濆悓姣� +4.54%%銆�' % len(tot25['ents'])),
+ ('鍏ㄧ渷鍚堣', '涓�寰嬩负 17 涓競宸炰箣鍜岋紙涓嶅惈璺ㄥ競宸為噸鍙犲彛寰勶級銆�'),
+ ('鎶ヨ〃鏈�', '鏈湡绱 2026-01-01鈥�2026-08-31锛涘悓姣斿熀鏈� 2025-01-01鈥�2025-08-31銆�'),
+ ('椤电璇存槑', '銆屽垎甯傚窞姹囨�汇�嶆寜甯傚窞姹囨�� 1-8 鏈堢疮璁¤繍璺濅笌鍚屾瘮锛涖�屽垎甯傚窞鍒嗘湀銆嶄负鍚勫競宸� 2026 骞撮�愭湀骞冲潎杩愯窛锛�'
+ '銆屼紒涓氭槑缁嗐�嶄负 636 瀹舵寔缁湪瑙勪紒涓氶�愭埛鏁版嵁锛堜紒涓氱骇鍚屾瘮浠呭涓ゅ勾鍧囪繛缁笂鎶ョ殑 545 瀹剁粰鍑猴級锛�'
+ '銆屽彛寰勫姣斻�嶅苟鍒楀畼鏂瑰彛寰勪笌鍚屼紒涓氬彛寰勭殑鍚屾瘮锛涖�屽彛寰勬牎楠屻�嶄负鏄庣粏涓庢眹鎬诲璐︼紝浠ュ強涓庢眹鎬昏〃銆佸凡鍙戝竷鎶ュ憡鐨勬瘮瀵广��'),
+ ('娉ㄦ剰浜嬮」', '鈶� 鏈� 5 瀹朵紒涓� 1鈥�8 鏈堟湀鎶ヨ揣杩愰噺銆佸懆杞噺鍧囦负 0锛岃鍏ヤ紒涓氭暟浣嗕笉褰卞搷杩愯窛锛�'
+ '鈶� 姝︽眽鐭冲寲浜ら�氳繍杈撴湁闄愬叕鍙搞�佹箹鍖楁嵎榫欎氦閫氳繍涓氭湁闄愬叕鍙� 2026 骞� 1 鏈堢殑鍦板尯鐮佷笌鍏跺悗鏈堜唤涓嶅悓锛�420100 鈫� 420107 / 420103锛夛紝'
+ '浣嗗悓灞炴姹夊競锛屼笉褰卞搷甯傚窞褰掗泦锛涒憿 绱璐ц繍閲忎负 0 鐨勪紒涓氱疮璁¤繍璺濈暀绌恒��'),
+ ('鐗堟湰', 'v2锛�2026-09-18锛夛細淇鍚屾瘮鍩烘湡鍙ｅ緞鈥斺�攙1 璇敤銆屼袱骞村潎鎸佺画鍦ㄨ鐨� 545 瀹躲�嶄綔鍩烘湡锛屽叏鐪佺疮璁¤繍璺濆悓姣旂畻寰� +2.90%%锛�'
+ '瀹樻柟鍙ｅ緞搴斾负 +4.54%%銆�'),
+ ('鐢熸垚鏃堕棿', '2026-09-18'),
+]
+r = 3
+for k, v in info:
+ a = ws.cell(r, 1, k); a.font = Font(bold=True); a.alignment = LT; a.border = BORDER
+ b = ws.cell(r, 2, v); b.alignment = LT; b.border = BORDER
+ r += 1
+
+# ---------------- Sheet2 鍒嗗競宸炴眹鎬� ----------------
+ws2 = wb.create_sheet('鍒嗗競宸炴眹鎬�')
+heads = ['搴忓彿', '甯傚窞', '2026骞�1-8鏈堟寔缁湪瑙勪紒涓氭暟(瀹�)', '2025骞�1-8鏈堣涓婁紒涓氭暟(瀹�)',
+ '2026骞�1-8鏈堢疮璁¤揣杩愰噺(涓囧惃)', '2026骞�1-8鏈堢疮璁¤揣鐗╁懆杞噺(涓囧惃鍏噷)', '2026骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)',
+ '2025骞�1-8鏈堢疮璁¤揣杩愰噺(涓囧惃)', '2025骞�1-8鏈堢疮璁¤揣鐗╁懆杞噺(涓囧惃鍏噷)', '2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)',
+ '绱骞冲潎杩愯窛鍚屾瘮(%)', '绱璐ц繍閲忓悓姣�(%)', '绱璐х墿鍛ㄨ浆閲忓悓姣�(%)']
+widths = [6, 14, 20, 18, 20, 24, 20, 20, 24, 20, 15, 15, 17]
+ws2.cell(1, 1, '璐ц繍鏈堟姤鎸佺画鍦ㄨ浼佷笟鍒嗗競宸� 1-8 鏈堢疮璁″钩鍧囪繍璺濆強鍚屾瘮鎯呭喌锛堝悓姣斿熀鏈燂細2025 骞� 1-8 鏈堝叏閮ㄨ涓婁紒涓氾級').font = Font(bold=True, size=13, color='FFFFFF')
+ws2.cell(1, 1).fill = TITLE_FILL
+ws2.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
+ws2.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(heads))
+ws2.row_dimensions[1].height = 26
+ws2.cell(2, 1, '娉細绗� 5鈥�7 鍒椾负 2026 骞存寔缁湪瑙勪紒涓氾紙636 瀹讹級绱鍊硷紱绗� 8鈥�10 鍒椾负 2025 骞� 1-8 鏈堝叏閮ㄨ涓婁紒涓氾紙鍏ㄧ渷 664 瀹讹級绱鍊硷紝'
+ '鍗虫眹鎬昏〃銆岃揣杩愩�嶉〉 T 鍒楀悓姣斿叕寮忕殑鍩烘湡锛涚 11鈥�13 鍒楀悓姣斿彛寰勪笌鍙戝竷鎶ュ憡涓�鑷淬��')
+ws2.cell(2, 1).font = Font(size=9, italic=True, color='808080')
+ws2.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(heads))
+for j, h in enumerate(heads, 1):
+ c = ws2.cell(3, j, h)
+ c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
+ ws2.column_dimensions[get_column_letter(j)].width = widths[j - 1]
+ws2.row_dimensions[3].height = 46
+
+rr = 3
+idx = 0
+for city in CITY_ORDER:
+ c = by_city.get(city)
+ if not c or c['n'] == 0:
+ continue
+ idx += 1
+ rr += 1
+ b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
+ d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
+ vals = [idx, city, c['n'], len(b['ents']),
+ c['f26'] / 10000.0, c['t26'] / 10000.0, d26,
+ b['f'] / 10000.0, b['t'] / 10000.0, d25,
+ growth(d26, d25), growth(c['f26'], b['f']), growth(c['t26'], b['t'])]
+ for j, v in enumerate(vals, 1):
+ cell = ws2.cell(rr, j, v)
+ cell.border = BORDER
+ cell.alignment = CT if j <= 4 else RT
+ if j in (5, 6, 7, 8, 9, 10):
+ cell.number_format = NF2
+ elif j in (11, 12, 13):
+ cell.number_format = NFP
+rr += 1
+d26p, d25p = dist(tot['f26'], tot['t26']), dist(tot25['f'], tot25['t'])
+vals = ['', '鍏ㄧ渷鍚堣', tot['n'], len(tot25['ents']),
+ tot['f26'] / 10000.0, tot['t26'] / 10000.0, d26p,
+ tot25['f'] / 10000.0, tot25['t'] / 10000.0, d25p,
+ growth(d26p, d25p), growth(tot['f26'], tot25['f']), growth(tot['t26'], tot25['t'])]
+for j, v in enumerate(vals, 1):
+ cell = ws2.cell(rr, j, v)
+ cell.border = BORDER
+ cell.fill = TOT_FILL
+ cell.font = Font(bold=True)
+ cell.alignment = CT if j <= 4 else RT
+ if j in (5, 6, 7, 8, 9, 10):
+ cell.number_format = NF2
+ elif j in (11, 12, 13):
+ cell.number_format = NFP
+ws2.freeze_panes = 'C4'
+
+# ---------------- Sheet3 鍒嗗競宸炲垎鏈� ----------------
+ws3 = wb.create_sheet('鍒嗗競宸炲垎鏈�')
+h3 = ['搴忓彿', '甯傚窞', '鎸佺画鍦ㄨ浼佷笟鏁�(瀹�)'] + ['%s鏈堝钩鍧囪繍璺�(鍏噷)' % m for m in range(1, 9)] + \
+ ['2026骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', '2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', '绱杩愯窛鍚屾瘮(%)']
+w3 = [6, 14, 18] + [15] * 8 + [24, 24, 15]
+ws3.cell(1, 1, '鍒嗗競宸� 2026 骞撮�愭湀骞冲潎杩愯窛锛堟寔缁湪瑙勪紒涓氬彛寰勶紱鍚屾瘮鍩烘湡锛�2025 骞� 1-8 鏈堝叏閮ㄨ涓婁紒涓氾級').font = Font(bold=True, size=13, color='FFFFFF')
+ws3.cell(1, 1).fill = TITLE_FILL
+ws3.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
+ws3.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h3))
+ws3.row_dimensions[1].height = 26
+for j, h in enumerate(h3, 1):
+ c = ws3.cell(2, j, h)
+ c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
+ ws3.column_dimensions[get_column_letter(j)].width = w3[j - 1]
+ws3.row_dimensions[2].height = 40
+
+rr = 2
+idx = 0
+for city in CITY_ORDER:
+ c = by_city.get(city)
+ if not c or c['n'] == 0:
+ continue
+ idx += 1
+ rr += 1
+ b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
+ d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
+ vals = [idx, city, c['n']] + [city_month(p, city, persist_names)[0] for p in MONTHS26] + \
+ [d26, d25, growth(d26, d25)]
+ for j, v in enumerate(vals, 1):
+ cell = ws3.cell(rr, j, v)
+ cell.border = BORDER
+ cell.alignment = CT if j <= 3 else RT
+ if j >= 4:
+ cell.number_format = NFP if j == 14 else NF2
+rr += 1
+vals = ['', '鍏ㄧ渷鍚堣', tot['n']] + [city_month(p, None)[0] for p in MONTHS26] + \
+ [d26p, d25p, growth(d26p, d25p)]
+for j, v in enumerate(vals, 1):
+ cell = ws3.cell(rr, j, v)
+ cell.border = BORDER
+ cell.fill = TOT_FILL
+ cell.font = Font(bold=True)
+ cell.alignment = CT if j <= 3 else RT
+ if j >= 4:
+ cell.number_format = NFP if j == 14 else NF2
+ws3.freeze_panes = 'D3'
+
+# ---------------- Sheet4 浼佷笟鏄庣粏 ----------------
+ws4 = wb.create_sheet('浼佷笟鏄庣粏')
+h4 = ['搴忓彿', '甯傚窞', '浼佷笟鍚嶇О', '浼佷笟浠ｇ爜', '缁熶竴绀句細淇＄敤浠ｇ爜',
+ '2026骞�1-8鏈堣揣杩愰噺(涓囧惃)', '2026骞�1-8鏈堝懆杞噺(涓囧惃鍏噷)', '2026骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)',
+ '2025骞�1-8鏈堣揣杩愰噺(涓囧惃)', '2025骞�1-8鏈堝懆杞噺(涓囧惃鍏噷)', '2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)',
+ '骞冲潎杩愯窛鍚屾瘮(%)', '鏄惁涓ゅ勾鍧囨寔缁湪瑙�']
+w4 = [6, 12, 42, 22, 24, 20, 24, 22, 20, 24, 22, 14, 18]
+ws4.cell(1, 1, '鎸佺画鍦ㄨ浼佷笟閫愭埛绱骞冲潎杩愯窛锛�2026 骞� 1-8 鏈堬紱鍚屾湡鍊间笌鍚屾瘮浠呭涓ゅ勾鍧囨寔缁湪瑙勭殑 545 瀹剁粰鍑猴級').font = Font(bold=True, size=13, color='FFFFFF')
+ws4.cell(1, 1).fill = TITLE_FILL
+ws4.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
+ws4.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h4))
+ws4.row_dimensions[1].height = 26
+for j, h in enumerate(h4, 1):
+ c = ws4.cell(2, j, h)
+ c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
+ ws4.column_dimensions[get_column_letter(j)].width = w4[j - 1]
+ws4.row_dimensions[2].height = 40
+
+order = {c: i for i, c in enumerate(CITY_ORDER)}
+rows_sorted = sorted(rows, key=lambda x: (order.get(x['city'], 99), -(x['t26'])))
+rr = 2
+for i, x in enumerate(rows_sorted, 1):
+ rr += 1
+ d26, d25 = dist(x['f26'], x['t26']), dist(x['f25'], x['t25'])
+ vals = [i, x['city'], x['en'], x['ec'], x['ucc'],
+ x['f26'] / 10000.0, x['t26'] / 10000.0, d26,
+ (x['f25'] / 10000.0) if x['cmp'] else None,
+ (x['t25'] / 10000.0) if x['cmp'] else None,
+ d25 if x['cmp'] else None,
+ growth(d26, d25) if x['cmp'] else None,
+ '鏄�' if x['cmp'] else '鍚�']
+ for j, v in enumerate(vals, 1):
+ cell = ws4.cell(rr, j, v)
+ cell.border = BORDER
+ cell.alignment = LT if j in (3, 4, 5) else (CT if j in (1, 2, 13) else RT)
+ if j in (6, 7, 8, 9, 10, 11):
+ cell.number_format = NF2
+ elif j == 12:
+ cell.number_format = NFP
+ if j == 12 and isinstance(v, float) and v > 0.5:
+ cell.font = Font(color='C00000', bold=True)
+ws4.freeze_panes = 'D3'
+ws4.auto_filter.ref = 'A2:M%d' % rr
+
+# ---------------- Sheet5 鍙ｅ緞瀵规瘮 ----------------
+ws6 = wb.create_sheet('鍙ｅ緞瀵规瘮')
+h6 = ['搴忓彿', '甯傚窞', '2026骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)\n[鎸佺画鍦ㄨ浼佷笟636瀹禲',
+ '2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)\n[鍏ㄩ儴瑙勪笂浼佷笟]', '瀹樻柟鍙ｅ緞鍚屾瘮(%)',
+ '2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)\n[鍚屼紒涓�545瀹禲', '鍚屼紒涓氬彛寰勫悓姣�(%)', '涓よ�呭樊寮�(鐧惧垎鐐�)', '璇存槑']
+w6 = [6, 14, 26, 24, 15, 24, 15, 16, 46]
+ws6.cell(1, 1, '涓ょ鍚屾瘮鍙ｅ緞瀵规瘮锛堝畼鏂瑰彛寰� = 涓庢眹鎬昏〃銆岃揣杩愩�嶉〉 T 鍒椾竴鑷达紱鍚屼紒涓氬彛寰� = 浠呬袱骞村潎鎸佺画鍦ㄨ浼佷笟锛�').font = Font(bold=True, size=13, color='FFFFFF')
+ws6.cell(1, 1).fill = TITLE_FILL
+ws6.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
+ws6.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h6))
+ws6.row_dimensions[1].height = 26
+ws6.cell(2, 1, '娉細浜や粯涓昏〃锛堝垎甯傚窞姹囨�伙級閲囩敤瀹樻柟鍙ｅ緞锛涙湰椤典袱涓彛寰勫苟鍒楋紝宸紓澶х殑甯傚窞澶氫负涓ゅ勾闂磋涓婁紒涓氬悕鍗曞彉鍔紙鍋滀骇銆佹柊澧炪�佹媶鍒嗭級鎵�鑷达紝渚涗笟鍔℃牳瀵瑰弬鑰冦��')
+ws6.cell(2, 1).font = Font(size=9, italic=True, color='808080')
+ws6.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(h6))
+for j, h in enumerate(h6, 1):
+ c = ws6.cell(3, j, h)
+ c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
+ ws6.column_dimensions[get_column_letter(j)].width = w6[j - 1]
+ws6.row_dimensions[3].height = 48
+
+rr = 3
+idx = 0
+for city in CITY_ORDER:
+ c = by_city.get(city)
+ if not c or c['n'] == 0:
+ continue
+ idx += 1
+ rr += 1
+ b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
+ g = by_city_cmp.get(city)
+ d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
+ g_off = growth(d26, d25)
+ if g:
+ dc26, dc25 = dist(g['f26c'], g['t26c']), dist(g['f25c'], g['t25c'])
+ g_same = growth(dc26, dc25)
+ else:
+ dc25, g_same = None, None
+ diff = (g_off - g_same) if (g_off is not None and g_same is not None) else None
+ note = ''
+ if diff is not None and abs(diff) > 0.10:
+ note = '涓ゅ彛寰勫樊寮傝秴 10 涓櫨鍒嗙偣锛屼富瑕佸洜涓ゅ勾瑙勪笂浼佷笟鍚嶅崟鍙樺姩锛屽缓璁牳瀵硅甯傚窞浼佷笟鍙樺姩鎯呭喌'
+ vals = [idx, city, d26, d25, g_off, dc25, g_same, (diff * 100 if diff is not None else None), note]
+ for j, v in enumerate(vals, 1):
+ cell = ws6.cell(rr, j, v)
+ cell.border = BORDER
+ cell.alignment = LT if j in (1, 9) else (CT if j == 2 else RT)
+ if j in (3, 4, 6):
+ cell.number_format = NF2
+ elif j in (5, 7):
+ cell.number_format = NFP
+ elif j == 8:
+ cell.number_format = NF1
+rr += 1
+g_cmp = growth(dist(tot_cmp['f26c'], tot_cmp['t26c']), dist(tot_cmp['f25c'], tot_cmp['t25c']))
+vals = ['', '鍏ㄧ渷鍚堣', d26p, d25p, growth(d26p, d25p), dist(tot_cmp['f25c'], tot_cmp['t25c']), g_cmp,
+ (growth(d26p, d25p) - g_cmp) * 100 if g_cmp is not None else None, '']
+for j, v in enumerate(vals, 1):
+ cell = ws6.cell(rr, j, v)
+ cell.border = BORDER
+ cell.fill = TOT_FILL
+ cell.font = Font(bold=True)
+ cell.alignment = CT if j == 2 else RT
+ if j in (3, 4, 6):
+ cell.number_format = NF2
+ elif j in (5, 7):
+ cell.number_format = NFP
+ elif j == 8:
+ cell.number_format = NF1
+ws6.freeze_panes = 'C4'
+
+# ---------------- Sheet5 鍙ｅ緞鏍￠獙 ----------------
+ws5 = wb.create_sheet('鍙ｅ緞鏍￠獙')
+ws5.sheet_view.showGridLines = False
+ws5.column_dimensions['A'].width = 46
+for col, w in (('B', 20), ('C', 20), ('D', 14), ('E', 46)):
+ ws5.column_dimensions[col].width = w
+ws5.cell(1, 1, '鍙ｅ緞鏍￠獙涓庡璐�').font = Font(bold=True, size=13, color='FFFFFF')
+ws5.cell(1, 1).fill = TITLE_FILL
+ws5.merge_cells(start_row=1, start_column=1, end_row=1, end_column=5)
+sum_f = sum(r['f26'] for r in rows) / 10000.0
+sum_t = sum(r['t26'] for r in rows) / 10000.0
+aug_f = sum(v[0] for c in CITY_ORDER for en, v in cust.get(('2026-08', c), {}).items() if en in persist_names) / 10000.0
+aug_t = sum(v[1] for c in CITY_ORDER for en, v in cust.get(('2026-08', c), {}).items() if en in persist_names) / 10000.0
+chk = [
+ ('鏍￠獙椤�', '鏈〃鏁板��', '瀵圭収鏁板��', '宸紓', '璇存槑'),
+ ('鏄庣粏琛ㄤ紒涓氭暟 = 姹囨�昏〃浼佷笟鏁�', len(rows), tot['n'], len(rows) - tot['n'], '636 瀹舵寔缁湪瑙勪紒涓氶�愭埛涓�琛�'),
+ ('鏄庣粏琛ㄨ揣杩愰噺鍚堣(涓囧惃)', round(sum_f, 2), round(tot['f26'] / 10000.0, 2), round(sum_f - tot['f26'] / 10000.0, 2), '鏄庣粏姹傚拰 = 17 甯傚窞姹囨�诲悎璁�'),
+ ('鏄庣粏琛ㄥ懆杞噺鍚堣(涓囧惃鍏噷)', round(sum_t, 2), round(tot['t26'] / 10000.0, 2), round(sum_t - tot['t26'] / 10000.0, 2), '鏄庣粏姹傚拰 = 17 甯傚窞姹囨�诲悎璁�'),
+ ('2026骞�1-8鏈堢疮璁¤揣杩愰噺(涓囧惃)', round(tot['f26'] / 10000.0, 2), PROV['vol26'], round(tot['f26'] / 10000.0 - PROV['vol26'], 2), '瀵圭収宸插彂甯冩姤鍛� 1-8 鏈堢疮璁�'),
+ ('2026骞�1-8鏈堢疮璁″懆杞噺(涓囧惃鍏噷)', round(tot['t26'] / 10000.0, 2), PROV['turn26'], round(tot['t26'] / 10000.0 - PROV['turn26'], 2), '鍚屼笂'),
+ ('2026骞�8鏈堝叏鐪佽揣杩愰噺(涓囧惃)', round(aug_f, 2), PROV['vol26_08'], round(aug_f - PROV['vol26_08'], 2), '楠岃瘉甯傚窞褰掗泦鏃犺'),
+ ('2026骞�8鏈堝叏鐪佸懆杞噺(涓囧惃鍏噷)', round(aug_t, 2), PROV['turn26_08'], round(aug_t - PROV['turn26_08'], 2), '鍚屼笂'),
+ ('2025骞�1-8鏈堢疮璁¤揣杩愰噺(涓囧惃)銆愬悓姣斿熀鏈熴��', round(tot25['f'] / 10000.0, 2), PROV['vol25'], round(tot25['f'] / 10000.0 - PROV['vol25'], 2), '瀵圭収姹囨�昏〃銆岃揣杩愩�嶉〉 V7..AJ7锛堢紦瀛樺�硷級'),
+ ('2025骞�1-8鏈堢疮璁″懆杞噺(涓囧惃鍏噷)銆愬悓姣斿熀鏈熴��', round(tot25['t'] / 10000.0, 2), PROV['turn25'], round(tot25['t'] / 10000.0 - PROV['turn25'], 2), '瀵圭収姹囨�昏〃銆岃揣杩愩�嶉〉 V8..AJ8锛堢紦瀛樺�硷級'),
+ ('2025骞�1-8鏈堝叏鐪佽涓婁紒涓氭暟(瀹�)', len(tot25['ents']), 664, len(tot25['ents']) - 664, '鍚屾瘮鍩烘湡浼佷笟鑼冨洿'),
+ ('绱璐ц繍閲忓悓姣�', growth(tot['f26'], tot25['f']), PROV['yoy_vol'], round(growth(tot['f26'], tot25['f']) - PROV['yoy_vol'], 4), '瀵圭収宸插彂甯冩姤鍛� 1-8 鏈堢疮璁″悓姣�'),
+ ('绱鍛ㄨ浆閲忓悓姣�', growth(tot['t26'], tot25['t']), PROV['yoy_turn'], round(growth(tot['t26'], tot25['t']) - PROV['yoy_turn'], 4), '鍚屼笂'),
+ ('2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', round(d25p, 2), None, None, '= 2025 绱鍛ㄨ浆閲� 梅 绱璐ц繍閲�'),
+ ('2026骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', round(d26p, 2), None, None, '= 2026 绱鍛ㄨ浆閲� 梅 绱璐ц繍閲�'),
+ ('鍏ㄧ渷绱骞冲潎杩愯窛鍚屾瘮', growth(d26p, d25p), None, None, '= 156.92 梅 150.10 鈭� 1'),
+ ('瑕嗙洊甯傚窞鏁�', len([c for c in CITY_ORDER if by_city.get(c, {}).get('n')]), 17, None, '17 涓競宸炲叏瑕嗙洊'),
+ ('鏈瘑鍒湴鍖虹爜', len(unknown), 0, None, ('銆�'.join(unknown) if unknown else '鏃�')),
+]
+for i, row in enumerate(chk, 1):
+ for j, v in enumerate(row, 1):
+ cell = ws5.cell(i + 2, j, v)
+ cell.border = BORDER
+ cell.alignment = CT if i == 1 else (LT if j in (1, 5) else RT)
+ if i == 1:
+ cell.font = Font(bold=True); cell.fill = HDR_FILL
+ elif j in (2, 3, 4):
+ cell.number_format = NFP if row[0].endswith('鍚屾瘮') else NF2
+
+os.makedirs(OUT_DIR, exist_ok=True)
+wb.save(OUT)
+print('OK ->', OUT)
+print('2026 1-8鏈�: %.4f 涓囧惃 / %.4f 涓囧惃鍏噷 / %.4f 鍏噷' % (tot['f26'] / 10000.0, tot['t26'] / 10000.0, d26p))
+print('2025 1-8鏈�: %.4f 涓囧惃 / %.4f 涓囧惃鍏噷 / %.4f 鍏噷 (%d 瀹�)' % (tot25['f'] / 10000.0, tot25['t'] / 10000.0, d25p, len(tot25['ents'])))
+print('鍏ㄧ渷鍚屾瘮: 璐ц繍閲� %.2f%% 鍛ㄨ浆閲� %.2f%% 骞冲潎杩愯窛 %.2f%%' %
+ (growth(tot['f26'], tot25['f']) * 100, growth(tot['t26'], tot25['t']) * 100, growth(d26p, d25p) * 100))
+for city in CITY_ORDER:
+ c = by_city.get(city)
+ if c and c['n']:
+ b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
+ d1, d0 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
+ print('%-8s n=%-4d 2026=%8.2f 2025=%8s 鍚屾瘮=%s' % (
+ city, c['n'], d1, ('%.2f' % d0) if d0 else '-', ('%.1f%%' % (growth(d1, d0) * 100)) if d0 else '-'))
--
Gitblit v1.9.1