# -*- coding: utf-8 -*- """货运月报「持续在规企业」分市州 1-8 月累计运距及同比 台账生成 数据源：traffic_audit.h2032_enterprise_monthly（《道路货物运输月度生产情况》H203-2 企业月报） 导出件 _tmp_persist_scope.tsv（企业级累计）、_tmp_persist_scope_monthly.tsv（企业级分月） 口径： · 持续在规企业 = 2026 年 1-8 月连续 8 个月均上报月报的企业； · 累计平均运距（公里）= 累计货物周转量 ÷ 累计货运量（加权平均）； · 同比 = 2026 年 1-8 月累计平均运距 ÷ 2025 年 1-8 月累计平均运距 − 1， 为保证可比，只在两年均持续在规的企业上计算。 """ 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_VOL_PUB = 31399.82 # 万吨，已发布《2026年8月规上货物周转量与平均运距分析报告》1-8月累计 PROV_TURN_PUB = 4927161.77 # 万吨公里，同上 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, n_cmp=0, f26=0.0, t26=0.0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0)) c['n'] += 1 c['f26'] += r['f26']; c['t26'] += r['t26'] if r['cmp']: c['n_cmp'] += 1 c['f26c'] += r['f26']; c['t26c'] += r['t26'] c['f25c'] += r['f25']; c['t25c'] += r['t25'] tot = dict(n=0, n_cmp=0, f26=0.0, t26=0.0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0) for c in by_city.values(): for k in tot: tot[k] += c[k] unknown = sorted(k for k in by_city if k.startswith('未识别')) persist_names = set(r['en'] for r in rows) cmp_names = set(r['en'] for r in rows if r['cmp']) # ---------- 读企业级分月数据 ---------- 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: continue per, rc, en, fr, tu = p[0], p[1], p[2], p[3], p[4] if per == 'report_period': continue cust.setdefault((per, city_of(rc)), {})[en] = (num(fr), num(tu)) def city_month(period, city, names): d = cust.get((period, city)) if not d: return None, 0.0 fr = tu = 0.0 for en, v in d.items(): if en in names: fr += v[0]; tu += v[1] return dist(fr, tu), fr # ---------- 样式 ---------- 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 = '#,##0.00', '#,##0.0' wb = Workbook() # ---------------- Sheet1 说明与口径 ---------------- ws = wb.active ws.title = '说明与口径' ws.sheet_view.showGridLines = False ws.column_dimensions['A'].width = 16 ws.column_dimensions['B'].width = 104 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 家，全期一致）。' % tot['n']), ('累计运距口径', '累计平均运距（公里）= 1—8 月累计货物周转量 ÷ 1—8 月累计货运量（加权平均）。源表单位为吨、吨公里，' '本表折算为万吨、万吨公里，比值不变。'), ('同比口径', '同比 = 2026 年 1—8 月累计平均运距 ÷ 2025 年 1—8 月累计平均运距 − 1。' '为保证可比，同比只在「两年均持续在规」的 %d 家企业上计算（另有 %d 家 2026 年持续在规企业，' '2025 年 1—8 月无连续上报记录，不参与同比）。' % (tot['n_cmp'], tot['n'] - tot['n_cmp'])), ('全省合计', '一律为 17 个市州之和（不含跨市州重叠口径）。'), ('报表期', '本期累计 2026-01-01—2026-08-31；上年同期 2025-01-01—2025-08-31。'), ('页签说明', '「分市州汇总」按市州汇总 1-8 月累计运距与同比；「分市州分月」为各市州 2026 年逐月平均运距与同期对比；' '「企业明细」为 636 家持续在规企业逐户数据；「口径校验」为明细与汇总对账、以及与已发布全省数据的比对。'), ('注意事项', '① 有 5 家企业 1—8 月月报货运量、周转量均为 0，计入企业数但不影响运距；' '② 武汉石化交通运输有限公司、湖北捷龙交通运业有限公司 2026 年 1 月的地区码与其后月份不同（420100 → 420107 / 420103），' '但同属武汉市，不影响市州归集；③ 累计货运量为 0 的企业累计运距留空。'), ('生成时间', '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月累计货运量(万吨)', '2026年1-8月累计货物周转量(万吨公里)', '2026年1-8月累计平均运距(公里)', '2025年1-8月累计平均运距(公里)[可比口径]', '累计运距同比(%)', '累计货运量同比(%)', '累计货物周转量同比(%)', '备注'] widths = [6, 14, 16, 18, 20, 24, 20, 22, 14, 14, 16, 40] ws2.cell(1, 1, '货运月报持续在规企业分市州 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 年持续在规企业全口径（%d 家）；第 8—11 列为两年均持续在规企业可比口径（%d 家）的同比计算基数。' % (tot['n'], tot['n_cmp'])) 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 d26, d26c, d25c = dist(c['f26'], c['t26']), dist(c['f26c'], c['t26c']), dist(c['f25c'], c['t25c']) if c['n_cmp'] == 0: note = '同比不适用：该市州 2026 年持续在规企业在 2025 年 1-8 月无连续上报记录' elif c['n_cmp'] == c['n']: note = '' else: note = '同比按 %d 家两年均持续在规企业计算' % c['n_cmp'] vals = [idx, city, c['n'], c['n_cmp'], c['f26'] / 10000.0, c['t26'] / 10000.0, d26, d25c, growth(d26c, d25c), growth(c['f26c'], c['f25c']), growth(c['t26c'], c['t25c']), note] for j, v in enumerate(vals, 1): cell = ws2.cell(rr, j, v) cell.border = BORDER cell.alignment = LT if j == 12 else (CT if j <= 4 else RT) if j in (5, 6, 7, 8): cell.number_format = NF2 elif j in (9, 10, 11): cell.number_format = NF1 rr += 1 vals = ['', '全省合计', tot['n'], tot['n_cmp'], tot['f26'] / 10000.0, tot['t26'] / 10000.0, dist(tot['f26'], tot['t26']), dist(tot['f25c'], tot['t25c']), growth(dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c'])), growth(tot['f26c'], tot['f25c']), growth(tot['t26c'], tot['t25c']), ''] 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): cell.number_format = NF2 elif j in (9, 10, 11): cell.number_format = NF1 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, 16] + [14] * 8 + [24, 24, 14] 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 vals = [idx, city, c['n']] for per in MONTHS26: vals.append(city_month(per, city, persist_names)[0]) vals += [dist(c['f26'], c['t26']), dist(c['f25c'], c['t25c']), growth(dist(c['f26c'], c['t26c']), dist(c['f25c'], c['t25c']))] 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 = NF2 if j <= 13 else NF1 rr += 1 # 全省分月：按 17 市州逐月合计重算 vals = ['', '全省合计', tot['n']] for per in MONTHS26: fr = tu = 0.0 for city in CITY_ORDER: _, f0 = city_month(per, city, persist_names) fr += f0 d = cust.get((per, city), {}) for en, v in d.items(): if en in persist_names: tu += v[1] vals.append(dist(fr, tu)) vals += [dist(tot['f26'], tot['t26']), dist(tot['f25c'], tot['t25c']), growth(dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c']))] 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 = NF2 if j <= 13 else NF1 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 月，同比基期 2025 年 1-8 月）').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 = NF1 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 口径校验 ---------------- ws5 = wb.create_sheet('口径校验') ws5.sheet_view.showGridLines = False ws5.column_dimensions['A'].width = 42 for col, w in (('B', 20), ('C', 20), ('D', 14), ('E', 40)): 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 市州汇总合计"), ("与已发布全省 1-8 月累计货运量比对(万吨)", round(tot["f26"] / 10000.0, 2), PROV_VOL_PUB, round(tot["f26"] / 10000.0 - PROV_VOL_PUB, 2), "对照《2026年8月规上货物周转量与平均运距分析报告》"), ("与已发布全省 1-8 月累计周转量比对(万吨公里)", round(tot["t26"] / 10000.0, 2), PROV_TURN_PUB, round(tot["t26"] / 10000.0 - PROV_TURN_PUB, 2), "同上"), ("2026年8月全省货运量比对(万吨)", round(aug_f, 2), 3665.36, round(aug_f - 3665.36, 2), "对照已发布 8 月全省规上货运量，验证市州归集无误"), ("2026年8月全省周转量比对(万吨公里)", round(aug_t, 2), 570004.44, round(aug_t - 570004.44, 2), "对照已发布 8 月全省规上周转量"), ("全省 1-8 月累计平均运距(公里)", round(dist(tot["f26"], tot["t26"]), 2), None, None, "由本表累计周转量 ÷ 累计货运量计算"), ("覆盖市州数", 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 = NF2 os.makedirs(OUT_DIR, exist_ok=True) wb.save(OUT) print('OK ->', OUT) print('持续在规企业数: %d 两年可比: %d' % (tot['n'], tot['n_cmp'])) print('全省累计货运量(万吨): %.2f 累计周转量(万吨公里): %.2f 累计平均运距: %.2f' % (tot['f26'] / 10000.0, tot['t26'] / 10000.0, dist(tot['f26'], tot['t26']))) print('可比口径 2026 累计运距: %.2f 2025: %.2f 同比: %.2f%%' % (dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c']), growth(dist(tot['f26c'], tot['t26c']), dist(tot['f25c'], tot['t25c'])) * 100)) for city in CITY_ORDER: c = by_city.get(city) if c and c['n']: g = growth(dist(c['f26c'], c['t26c']), dist(c['f25c'], c['t25c'])) print('%-8s n=%-4d 2026累计=%8s 2025累计=%8s 同比=%s' % ( city, c['n'], '%.2f' % dist(c['f26'], c['t26']), '%.2f' % dist(c['f25c'], c['t25c']) if dist(c['f25c'], c['t25c']) else '-', '%.1f%%' % (g * 100) if g is not None else '-'))