# -*- 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 '-'))
|
