From ade4387f72c876f779b0956295922003464434f0 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期六, 19 九月 2026 23:26:59 +0800
Subject: [PATCH] fix(汇总表): 中口径排名周转量口径统一为「总量-城市内」差额法
---
docs/工具/持续在规企业545家累计运距生成.py | 174 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 174 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\232545\345\256\266\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\232545\345\256\266\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..25b792d
--- /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\232545\345\256\266\347\264\257\350\256\241\350\277\220\350\267\235\347\224\237\346\210\220.py"
@@ -0,0 +1,174 @@
+# -*- coding: utf-8 -*-
+"""鎸佺画鍦ㄨ浼佷笟锛�545 瀹讹級鍒嗗競宸� 1-8 鏈堢疮璁″钩鍧囪繍璺濆強鍚屾瘮 鍙拌处
+
+鍙ｅ緞锛�
+ 路 鎸佺画鍦ㄨ浼佷笟 = 2025 骞� 1-8 鏈堜笌 2026 骞� 1-8 鏈堝潎杩炵画 8 涓湀涓婃姤 H203-2 鏈堟姤鐨勪紒涓氾紙545 瀹讹級锛�
+ 路 绱骞冲潎杩愯窛锛堝叕閲岋級= 1-8 鏈堢疮璁¤揣鐗╁懆杞噺 梅 1-8 鏈堢疮璁¤揣杩愰噺锛堝姞鏉冨钩鍧囷級锛�
+ 路 鍚屾瘮 = 2026 骞� 1-8 鏈堢疮璁″钩鍧囪繍璺� 梅 2025 骞� 1-8 鏈堢疮璁″钩鍧囪繍璺� 鈭� 1銆�
+鏁版嵁婧愶細traffic_audit.h2032_enterprise_monthly 瀵煎嚭浠� _tmp_persist_scope.tsv
+"""
+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'
+OUT = r'D:\鏂囨。\ChatGPT\traffic-audit\outputs\鎸佺画鍦ㄨ浼佷笟545瀹禵鍒嗗競宸�1-8鏈堢疮璁¤繍璺濆強鍚屾瘮_2026-09-19.xlsx'
+
+ORDER = ['姝︽眽甯�', '榛勭煶甯�', '鍗佸牥甯�', '瀹滄槍甯�', '瑗勯槼甯�', '閯傚窞甯�', '鑽嗛棬甯�', '瀛濇劅甯�', '鑽嗗窞甯�',
+ '榛勫唸甯�', '鍜稿畞甯�', '闅忓窞甯�', '鎭╂柦宸�', '浠欐甯�', '娼滄睙甯�', '澶╅棬甯�', '绁炲啘鏋舵灄鍖�']
+SHORT = {'姝︽眽甯�': '姝︽眽', '榛勭煶甯�': '榛勭煶', '鍗佸牥甯�': '鍗佸牥', '瀹滄槍甯�': '瀹滄槍', '瑗勯槼甯�': '瑗勯槼', '閯傚窞甯�': '閯傚窞',
+ '鑽嗛棬甯�': '鑽嗛棬', '瀛濇劅甯�': '瀛濇劅', '鑽嗗窞甯�': '鑽嗗窞', '榛勫唸甯�': '榛勫唸', '鍜稿畞甯�': '鍜稿畞', '闅忓窞甯�': '闅忓窞',
+ '鎭╂柦宸�': '鎭╂柦宸�', '浠欐甯�': '浠欐', '娼滄睙甯�': '娼滄睙', '澶╅棬甯�': '澶╅棬', '绁炲啘鏋舵灄鍖�': '鏋楀尯'}
+CITY = dict(zip(['4201', '4202', '4203', '4205', '4206', '4207', '4208', '4209', '4210', '4211', '4212', '4213', '4228'],
+ ORDER[:13]))
+CITY.update({'429004': '浠欐甯�', '429005': '娼滄睙甯�', '429006': '澶╅棬甯�', '429021': '绁炲啘鏋舵灄鍖�'})
+
+
+def city_of(rc):
+ rc = (rc or '').strip()
+ return CITY.get(rc, CITY.get(rc[:4], '?'))
+
+
+def dv(f, t):
+ return (t / f) if f else None
+
+
+def gth(a, b):
+ return (a / b - 1) if (a is not None and b) else None
+
+
+def num(x):
+ try:
+ return float(x)
+ except Exception:
+ return 0.0
+
+
+rows = []
+with io.open(SRC, 'r', encoding='utf-8') as fh:
+ hdr = fh.readline().rstrip('\r\n').split('\t')
+ for line in fh:
+ p = line.rstrip('\r\n').split('\t')
+ if len(p) < len(hdr):
+ continue
+ d = dict(zip(hdr, p))
+ if int(num(d['m26'])) != 8 or int(num(d['m25'])) != 8:
+ continue
+ rows.append(dict(city=city_of(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'])))
+
+by_city = {}
+for r in rows:
+ c = by_city.setdefault(r['city'], dict(n=0, f26=0.0, t26=0.0, f25=0.0, t25=0.0))
+ c['n'] += 1
+ c['f26'] += r['f26']; c['t26'] += r['t26']; c['f25'] += r['f25']; c['t25'] += r['t25']
+tot = dict(n=0, f26=0.0, t26=0.0, f25=0.0, t25=0.0)
+for c in by_city.values():
+ for k in tot:
+ tot[k] += c[k]
+
+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, NFP = '#,##0.00', '0.00%'
+
+wb = Workbook()
+ws = wb.active
+ws.title = '甯傚窞姹囨��'
+heads = ['甯傚窞', '鎸佺画鍦ㄨ浼佷笟鏁�(瀹�)', '2026骞�1-8鏈堢疮璁¤揣杩愰噺(涓囧惃)', '2026骞�1-8鏈堢疮璁¤揣鐗╁懆杞噺(涓囧惃鍏噷)',
+ '2026骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', '2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', '绱杩愯窛鍚屾瘮(%)', '澶囨敞']
+widths = [12, 16, 22, 26, 22, 22, 14, 46]
+ws.cell(1, 1, '鎸佺画鍦ㄨ浼佷笟锛�545 瀹讹級鍒嗗競宸� 1-8 鏈堢疮璁″钩鍧囪繍璺濆強鍚屾瘮').font = Font(bold=True, size=14, color='FFFFFF')
+ws.cell(1, 1).fill = TITLE_FILL
+ws.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
+ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(heads))
+ws.row_dimensions[1].height = 28
+notes = [
+ '鍙ｅ緞璇存槑锛�',
+ '鈶� 鎸佺画鍦ㄨ浼佷笟 = 2025 骞� 1-8 鏈堜笌 2026 骞� 1-8 鏈堝潎杩炵画 8 涓湀涓婃姤銆婇亾璺揣鐗╄繍杈撴湀搴︾敓浜ф儏鍐点�嬶紙H203-2锛夋湀鎶ョ殑浼佷笟锛屽叡 %d 瀹讹紱涓ゅ勾闂存柊杩涖�侀��鍑虹殑浼佷笟涓嶈鍏ユ湰琛ㄥ悓姣斻��' % tot['n'],
+ '鈶� 绱骞冲潎杩愯窛锛堝叕閲岋級= 1-8 鏈堢疮璁¤揣鐗╁懆杞噺 梅 1-8 鏈堢疮璁¤揣杩愰噺锛堝姞鏉冨钩鍧囷級锛涘悓姣� = 2026 骞� 1-8 鏈堢疮璁″钩鍧囪繍璺� 梅 2025 骞� 1-8 鏈堢疮璁″钩鍧囪繍璺� 鈭� 1銆�',
+ '鈶� 鍏ㄧ渷琛� = 17 涓競宸炰箣鍜岋紙鎸夌疮璁″懆杞噺銆佺疮璁¤揣杩愰噺鍒嗗埆姹傚拰鍚庣浉闄わ紝涓嶆槸甯傚窞鍚屾瘮鐨勭畻鏈钩鍧囷級銆�',
+ '鈶� 浠欐甯� 2026 骞存寔缁湪瑙勭殑 2 瀹朵紒涓� 2025 骞� 1-8 鏈堟棤杩炵画涓婃姤璁板綍锛屾湰琛ㄦ棤鍙瘮鍩烘暟锛屽悓姣斾笉閫傜敤銆�',
+ '鏁版嵁鏉ユ簮锛氫氦閫氱粺璁℃姤琛ㄥ鏍哥郴缁熸暟鎹簱锛圚203-2 浼佷笟鏈堟姤锛�2025 骞� 1 鏈堚��2026 骞� 8 鏈堬級銆傜敓鎴愭棩鏈燂細2026-09-19銆�',
+]
+r = 2
+for i, t in enumerate(notes):
+ c = ws.cell(r, 1, t)
+ c.font = Font(size=9, bold=(i == 0), color='404040')
+ c.alignment = LT
+ ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=len(heads))
+ r += 1
+hdr_row = r
+for j, h in enumerate(heads, 1):
+ c = ws.cell(hdr_row, j, h)
+ c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
+ ws.column_dimensions[get_column_letter(j)].width = widths[j - 1]
+ws.row_dimensions[hdr_row].height = 42
+
+
+def put(row, vals, fill=None, bold=False):
+ for j, v in enumerate(vals, 1):
+ c = ws.cell(row, j, v)
+ c.border = BORDER
+ c.alignment = LT if j == 8 else (CT if j == 1 else RT)
+ if j in (3, 4, 5, 6):
+ c.number_format = NF2
+ elif j == 7:
+ c.number_format = NFP
+ if fill:
+ c.fill = fill
+ if bold:
+ c.font = Font(bold=True)
+
+
+rr = hdr_row
+d26 = dv(tot['f26'], tot['t26']); d25 = dv(tot['f25'], tot['t25'])
+rr += 1
+put(rr, ['鍏ㄧ渷', tot['n'], tot['f26'] / 10000.0, tot['t26'] / 10000.0, d26, d25, gth(d26, d25), ''], TOT_FILL, True)
+for city in ORDER:
+ c = by_city.get(city)
+ rr += 1
+ if not c or c['n'] == 0:
+ put(rr, [SHORT[city], 0, None, None, None, None, None,
+ '璇ュ競宸� 2026 骞存寔缁湪瑙勪紒涓氬湪 2025 骞� 1-8 鏈堟棤杩炵画涓婃姤璁板綍锛屽悓姣斾笉閫傜敤'])
+ continue
+ a, b = dv(c['f26'], c['t26']), dv(c['f25'], c['t25'])
+ put(rr, [SHORT[city], c['n'], c['f26'] / 10000.0, c['t26'] / 10000.0, a, b, gth(a, b), ''])
+ws.freeze_panes = ws.cell(hdr_row + 1, 2).coordinate
+
+# 浼佷笟鏄庣粏
+ws2 = wb.create_sheet('浼佷笟鏄庣粏(545瀹�)')
+h2 = ['搴忓彿', '甯傚窞', '浼佷笟鍚嶇О', '缁熶竴绀句細淇＄敤浠ｇ爜', '2026骞�1-8鏈堣揣杩愰噺(涓囧惃)', '2026骞�1-8鏈堝懆杞噺(涓囧惃鍏噷)',
+ '2026骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', '2025骞�1-8鏈堢疮璁″钩鍧囪繍璺�(鍏噷)', '绱杩愯窛鍚屾瘮(%)']
+w2 = [6, 12, 42, 24, 20, 24, 22, 22, 14]
+for j, h in enumerate(h2, 1):
+ c = ws2.cell(1, 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 = w2[j - 1]
+ws2.row_dimensions[1].height = 40
+order = {c: i for i, c in enumerate(ORDER)}
+idx = 0
+for i, x in enumerate(sorted(rows, key=lambda z: (order.get(z['city'], 99), -z['t26'])), 1):
+ a, b = dv(x['f26'], x['t26']), dv(x['f25'], x['t25'])
+ vals = [i, SHORT[x['city']], x['en'], x['ucc'], x['f26'] / 10000.0, x['t26'] / 10000.0, a, b, gth(a, b)]
+ for j, v in enumerate(vals, 1):
+ c = ws2.cell(i + 1, j, v)
+ c.border = BORDER
+ c.alignment = LT if j in (3, 4) else (CT if j in (1, 2) else RT)
+ if j in (5, 6, 7, 8):
+ c.number_format = NF2
+ elif j == 9:
+ c.number_format = NFP
+ws2.freeze_panes = 'C2'
+ws2.auto_filter.ref = 'A1:I%d' % (len(rows) + 1)
+
+os.makedirs(os.path.dirname(OUT), exist_ok=True)
+wb.save(OUT)
+print('OK ->', OUT)
+print('浼佷笟鏁� %d锛涘叏鐪� 2026 %.4f / 2025 %.4f -> %.4f%%' % (tot['n'], d26, d25, gth(d26, d25) * 100))
--
Gitblit v1.9.1