| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475 |
- """按海关字段需求生成测试数据集(可复现,不联网)。
- 生成内容严格遵循 ``进出口数据字段需求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/<name>.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()
|