"""按海关字段需求生成测试数据集(可复现,不联网)。 生成内容严格遵循 ``进出口数据字段需求20260819.xlsx`` 的字段与申报要素顺序: * 字段:I_E_FLAG / HS_CODE / ORIGIN_COUNTRY / TRADE_AREA_CODE / TRADE_COUNTRY / DISTINATE_PORT / YEAR / WEEK / MONORSUM / COUNT / G_MODEL 等; * 申报要素:电容 9 段、存储器 13 段,直接按 ``字段规则_申报要素`` 的顺序拼接; * 品牌/型号/国别/港口全部取自真实规则库,保证映射链可跑通; * 同时写出一份 ``expected.json``(总行数、纳入行数、金额合计等),供测试当预言机。 用法:: python scripts/gen_sample_data.py # 生成 small/medium 两套 python scripts/gen_sample_data.py --rows 60000 --name large """ from __future__ import annotations import argparse import csv import json import random import re from collections import defaultdict from datetime import date, timedelta from pathlib import Path from openpyxl import load_workbook ROOT = Path(__file__).resolve().parents[1] RULES = ROOT / "tools-service" / "toolsvc" / "customs" / "rules" SAMPLES = ROOT / "samples" HEADERS = [ "I_E_FLAG", "HS_CODE", "ORIGIN_COUNTRY", "TRADE_AREA_CODE", "DESTINATION_COUNTRY", "TRADE_COUNTRY", "DISTINATE_PORT", "DISTRICT_CODE", "TRADE_MODE", "DECLARING_CUSTOMS", "NAME_OF_CONSIGNOR/CONSIGNEE_IN_CHINA", "CONSIGNEE/SHIPPER_CUSTOMS_REGISTER_10_DIGIT_NUMBER", "CONSIGNEE/SHIPPER_UNIFIED_SOCIAL_CREDIT_NUMBER", "CONSIGNEE_NAME", "CONSIGNEE/SHIPPER_AEO_NUMBER", "IMPORTER_COMPANY_NAME", "IMPORTER_CUSTOMS_REGISTER_10_DIGIT_NUMBER", "IMPORTER_UNIFIED_SOCIAL_CREDIT_NUMBER", "TRAF_MODE", "PACKING_TYPE", "YEAR", "WEEK", "MONORSUM", "COUNT", "G_MODEL", ] #: 应用场景 → 第一轮/第二轮命中的领域(尽量覆盖 8 个标签) SCENES = [ "计算机系统", "手机终端", "汽车电子", "基站", "光伏逆变器", "工业控制", "卫星通信", "服务器", "智能驾驶", "路由器", "储能电池", "医疗设备", ] COUNTRIES = ["KOR", "JPN", "TWN", "HKG", "SGP", "USA", "CHN", "MYS", "PHL", "THA", "DEU", "NLD"] CAPACITOR_BRANDS = [ ("MURATA", "村田"), ("TDK", "TDK"), ("TAIYO YUDEN", "太阳诱电"), ("SAMSUNG", "三星"), ("YAGEO", "国巨"), ("WALSIN", "华新科"), ("Panasonic", "松下"), ("KYOCERA(AVX)", "京瓷"), ] CAPACITOR_SERIES = { "多层瓷介电容器(MLCC)": ("8532241000", ["GRM", "CGA", "GRT"], "陶瓷介质,多层结构"), "单层瓷介电容器": ("8532230000", ["CC", "SL"], "陶瓷介质,单层结构"), "铝电解电容器": ("8532221000", ["EEE", "UVR"], "铝电解"), "钽电解电容器": ("8532211000", ["TPS", "TAJ"], "钽电解"), "其他电容器": ("8532290000", ["MKP", "CBB"], "塑料介质"), } def _load_rule_workbook(name: str): return load_workbook(RULES / name, read_only=True, data_only=True) def load_brand_country() -> dict[str, str]: workbook = _load_rule_workbook("数据映射规则.xlsx") mapping = {} for brand, country in workbook["品牌国别映射规则"].iter_rows(min_row=2, values_only=True): if brand and country: mapping[str(brand)] = str(country) workbook.close() return mapping def load_capacitor_hs() -> list[tuple[str, str]]: workbook = _load_rule_workbook("数据映射规则.xlsx") items = [] for row in workbook["电容器映射规则"].iter_rows(min_row=2, values_only=True): if row[2] and row[1]: code = str(row[2]).split(".")[0].strip() if len(code) == 10: items.append((code, str(row[1]))) workbook.close() return items def load_memory_models() -> dict[str, dict[str, list[str]]]: """返回 {分组: {品牌: [型号, ...]}},分组建模 DDR3/DDR4/DDR5/SDRAM/NAND/NOR/EEPROM。""" workbook = _load_rule_workbook("数据映射规则.xlsx") groups: dict[str, dict[str, set[str]]] = defaultdict(lambda: defaultdict(set)) known_brands = {"SK HYNIX", "SAMSUNG", "MICRON", "NANYA", "CXMT", "HUAWEI", "BIWIN", "LONGSYS", "TOSHIBA", "KIOXIA"} for row in workbook["存储器型号分类映射规则"].iter_rows(min_row=2, values_only=True): en, cn, model, prefix, desc, c1, c2, c3, c4 = (list(row) + [None] * 9)[:9] if not model or str(c1 or "") != "存储器": continue content = " ".join(str(value or "") for value in (desc, c2, c3, c4)).upper() group = None generation = re.search(r"(?:LP)?DDR([345])", content) if generation: group = f"DDR{generation.group(1)}" elif "SDRAM" in content and "DDR" not in content: group = "SDRAM" elif "NAND" in content: group = "NAND" elif "NOR" in content: group = "NOR" elif "EEPROM" in content: group = "EEPROM" if not group: continue brand = str(en or "").upper() if brand not in known_brands: continue groups[group][brand].add(str(model)) workbook.close() trimmed: dict[str, dict[str, list[str]]] = {} for group, brands in groups.items(): trimmed[group] = {} for brand, models in brands.items(): ordered = sorted(models) step = max(1, len(ordered) // 8) trimmed[group][brand] = ordered[::step][:8] return trimmed def load_ports() -> list[tuple[str, str]]: workbook = _load_rule_workbook("进出口数据字段需求20260819.xlsx") ports = [] wanted = {"韩国", "日本", "中国台湾", "中国香港", "新加坡", "美国", "中国", "马来西亚"} for code, cn, en, country in workbook["映射规则_港口代码"].iter_rows(min_row=2, values_only=True): if code and country and str(country) in wanted and len(str(code)) == 6: ports.append((str(code), str(country))) workbook.close() # 每个国家留两个,避免港口维度过于稀疏 by_country: dict[str, list[tuple[str, str]]] = defaultdict(list) for code, country in ports: if len(by_country[country]) < 2: by_country[country].append((code, country)) return [item for items in by_country.values() for item in items][:14] def month_weeks(start: str, end: str) -> list[tuple[int, int, str]]: """返回 [(ISO年, ISO周, 归属月)],按周四归月。""" start_year = int(start[:4]) end_year = int(end[:4]) result = [] for year in range(start_year, end_year + 1): for week in range(1, 54): try: thursday = date.fromisocalendar(year, week, 4) except ValueError: continue key = f"{thursday:%Y-%m}" if start <= key <= end: result.append((year, week, key)) return result def build_memory_series() -> list[dict]: models = load_memory_models() hs_by_group = { "DDR3": "8542321010", "DDR4": "8542321010", "DDR5": "8542321010", "SDRAM": "8542321010", "NAND": "8542329090", "NOR": "8542329090", "EEPROM": "8542329090", } capacity = {"DDR3": "4G", "DDR4": "8G", "DDR5": "16G", "SDRAM": "512M", "NAND": "128G", "NOR": "128M", "EEPROM": "4M"} series = [] for group, brands in models.items(): for brand, brand_models in brands.items(): for model in brand_models: series.append( { "product": "存储器", "group": group, "hs": hs_by_group.get(group, "8542321090"), "brand": brand, "model": model, "capacity": capacity.get(group, "8G"), "base_price": 1.5 if group.startswith("DDR") else (0.8 if group in ("NAND", "NOR") else 0.4), } ) return series def build_capacitor_series() -> list[dict]: series = [] for brand, brand_cn in CAPACITOR_BRANDS: for klass, (hs, prefixes, medium) in CAPACITOR_SERIES.items(): for index in range(6): model = f"{prefixes[index % len(prefixes)]}{100 + index * 37}{'K' if index % 2 else 'M'}" series.append( { "product": "电容器", "group": klass, "hs": hs, "brand": brand, "brand_cn": brand_cn, "model": model, "medium": medium, "base_price": 0.02 if "MLCC" in klass else 0.05, } ) return series def _capacitor_model(series: dict, scene: str | None) -> str: brand = series["brand"] model = series["model"] base = f"4|0|{'多层' if 'MLCC' in series['group'] else '单层'}|{series['medium']}|{brand}|{model}" if scene and random.random() < 0.35: return f"{base}|||应用场景:{scene}" if scene: return f"{base}|||{scene}用" return f"{base}|||" def _memory_model(series: dict, scene: str) -> str: return ( f"4|0|{scene}|数据存储|已封装|已切割|{series['brand']}|{series['model']}|量产|{series['capacity']}|||" ) def generate(rows_target: int, start: str, end: str, seed: int) -> tuple[list[list[str]], dict]: """生成稠密的逐月数据。 真实海关数据里,同一个型号几乎每个月都有申报记录;价格指数样本要求 “当期与基期都有数”,所以测试数据必须逐月覆盖,否则价格指数会按业务规则留空。 因此这里先算出覆盖全部月份所需的型号数,再按月循环生成。 """ random.seed(seed) ports = load_ports() if not ports: ports = [("KOR000", "韩国")] all_series = build_capacitor_series() + build_memory_series() weeks = month_weeks(start, end) months = sorted({item[2] for item in weeks}) weeks_by_month: dict[str, list[str]] = defaultdict(list) for year, week, month in weeks: weeks_by_month[month].append(f"{year}|{week}") capacity = len(months) * 2 # 每个型号每月约 2 条记录 keep = min(len(all_series), max(24, rows_target // max(capacity, 1))) # 按“产品+类目”分组轮转选取,保证每个类目都有样本(否则某些模板表会是空的) grouped: dict[tuple[str, str], list[dict]] = defaultdict(list) for series in all_series: grouped[(series["product"], series["group"])].append(series) for items in grouped.values(): random.shuffle(items) series_list: list[dict] = [] depth = 0 while len(series_list) < keep: added = False for key in sorted(grouped): if depth < len(grouped[key]) and len(series_list) < keep: series_list.append(grouped[key][depth]) added = True if not added: break depth += 1 trend = {index: random.uniform(-0.6, 1.2) for index in range(len(series_list))} capacitor_hs = {code for code, _name in load_capacitor_hs()} memory_hs = {"8542321010", "8542321090", "8542329010", "8542329090"} rows: list[list[str]] = [] expected = { "rows": 0, "included": 0, "excluded": 0, "amount": 0.0, "quantity": 0.0, "zero_or_negative": 0, "months": [months[0], months[-1]], "series": len(series_list), "amount_by_c1": {"电容器": 0.0, "存储器": 0.0}, "quantity_by_c1": {"电容器": 0.0, "存储器": 0.0}, "by_month": {}, } company_index = 1 good_rows: list[list[str]] = [] for month_index, month in enumerate(months): progress = month_index / max(len(months) - 1, 1) month_amount = 0.0 month_quantity = 0.0 for index, series in enumerate(series_list): repeats = 2 if random.random() < 0.75 else 1 for _ in range(repeats): scene = random.choice(SCENES) if random.random() < 0.85 else None if series["product"] == "电容器": model_text = _capacitor_model(series, scene) else: model_text = _memory_model(series, scene or "") base_amount = random.uniform(800, 90000) if series["product"] == "存储器": base_amount *= random.uniform(3, 12) amount = base_amount * (1 + trend[index] * progress) * random.uniform(0.9, 1.1) unit_price = series["base_price"] * (1 + trend[index] * progress * 0.5) * random.uniform(0.92, 1.08) quantity = max(1, int(amount / max(unit_price, 0.001))) year, week = random.choice(weeks_by_month[month]).split("|") row = [ "I", series["hs"], random.choice(COUNTRIES), random.choice(COUNTRIES), "CHN", random.choice(COUNTRIES), random.choice(ports)[0], "32026", "110", "1500", f"福州智捷进出口贸易有限公司{company_index}", f"3122560{company_index:03d}", f"91350100MACNPJ{company_index:04d}", "Apple Inc.", f"SG12345{company_index:06d}", "沪东精密仪器制造有限公司", f"31225600{company_index:02d}", f"121000000YP71XRH{company_index:03d}", random.choice(["5", "4", "Y"]), "包/袋", year, week, f"{amount:.2f}", str(quantity), model_text, ] rows.append(row) good_rows.append(row) company_index += 1 expected["rows"] += 1 month_amount += amount month_quantity += quantity # 每月固定注入少量零值/负值金额,验证“保留原值 + 告警”不被静默丢弃 for _ in range(max(1, len(series_list) // 60)): template = list(random.choice(good_rows)) template[22] = random.choice(["0", "-1200.50"]) row = list(template) rows.append(row) expected["rows"] += 1 expected["zero_or_negative"] += 1 month_amount += float(row[22]) month_quantity += float(row[23]) expected["by_month"][month] = {"amount": round(month_amount, 2), "quantity": month_quantity} for row in rows: hs = row[1] if not (len(hs) == 10 and hs.isdigit()): continue if hs not in capacitor_hs and hs not in memory_hs: continue try: int(row[20]), int(row[21]) except (TypeError, ValueError): continue if int(row[20]) > int(end[:4]): continue key = "电容器" if hs in capacitor_hs else "存储器" expected["amount_by_c1"][key] += float(row[22]) expected["quantity_by_c1"][key] += float(row[23]) expected["amount_by_c1"] = {key: round(value, 2) for key, value in expected["amount_by_c1"].items()} expected["quantity_by_c1"] = {key: round(value, 0) for key, value in expected["quantity_by_c1"].items()} # 明确的“应被排除”记录:8 位 HS、非法周号、超出统计年份 for hs in ["85423210", "85322410", "85177000"]: row = list(good_rows[len(rows) % len(good_rows)]) row[1] = hs rows.append(row) expected["rows"] += 1 expected["excluded"] += 1 for week in ["60", "0", "abc"]: row = list(good_rows[len(rows) % len(good_rows)]) row[21] = week rows.append(row) expected["rows"] += 1 expected["excluded"] += 1 future = list(good_rows[3]) future[20] = "2031" future[21] = "2" rows.append(future) expected["rows"] += 1 expected["excluded"] += 1 expected["included"] = expected["rows"] - expected["excluded"] expected["amount"] = round(sum(item["amount"] for item in expected["by_month"].values()), 2) expected["quantity"] = round(sum(item["quantity"] for item in expected["by_month"].values()), 0) random.shuffle(rows) return rows, expected def write_dataset(name: str, rows: list[list[str]], expected: dict, encoding: str = "utf-8-sig") -> Path: SAMPLES.mkdir(parents=True, exist_ok=True) path = SAMPLES / f"{name}.csv" with path.open("w", encoding=encoding, newline="") as handle: writer = csv.writer(handle) writer.writerow(HEADERS) writer.writerows(rows) (SAMPLES / f"{name}.expected.json").write_text( json.dumps(expected, ensure_ascii=False, indent=2), encoding="utf-8" ) return path def main() -> None: parser = argparse.ArgumentParser(description="生成海关测试数据集") parser.add_argument("--rows", type=int, default=0, help="目标行数;0 表示生成 small+medium 两套") parser.add_argument("--name", default="custom", help="数据集名称(输出 samples/.csv)") parser.add_argument("--start", default="2025-01", help="起始月份 YYYY-MM") parser.add_argument("--end", default="2026-08", help="结束月份 YYYY-MM") parser.add_argument("--seed", type=int, default=20261111) args = parser.parse_args() if args.rows: rows, expected = generate(args.rows, args.start, args.end, args.seed) path = write_dataset(args.name, rows, expected) print(f"已生成 {path}({len(rows)} 行,纳入 {expected['included']} 行)") return for name, target in [("small", 1200), ("medium", 12000)]: rows, expected = generate(target, args.start, args.end, args.seed) path = write_dataset(name, rows, expected) print(f"已生成 {path}({len(rows)} 行,纳入 {expected['included']} 行)") if __name__ == "__main__": main()