gen_sample_data.py 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475
  1. """按海关字段需求生成测试数据集(可复现,不联网)。
  2. 生成内容严格遵循 ``进出口数据字段需求20260819.xlsx`` 的字段与申报要素顺序:
  3. * 字段:I_E_FLAG / HS_CODE / ORIGIN_COUNTRY / TRADE_AREA_CODE / TRADE_COUNTRY /
  4. DISTINATE_PORT / YEAR / WEEK / MONORSUM / COUNT / G_MODEL 等;
  5. * 申报要素:电容 9 段、存储器 13 段,直接按 ``字段规则_申报要素`` 的顺序拼接;
  6. * 品牌/型号/国别/港口全部取自真实规则库,保证映射链可跑通;
  7. * 同时写出一份 ``expected.json``(总行数、纳入行数、金额合计等),供测试当预言机。
  8. 用法::
  9. python scripts/gen_sample_data.py # 生成 small/medium 两套
  10. python scripts/gen_sample_data.py --rows 60000 --name large
  11. """
  12. from __future__ import annotations
  13. import argparse
  14. import csv
  15. import json
  16. import random
  17. import re
  18. from collections import defaultdict
  19. from datetime import date, timedelta
  20. from pathlib import Path
  21. from openpyxl import load_workbook
  22. ROOT = Path(__file__).resolve().parents[1]
  23. RULES = ROOT / "tools-service" / "toolsvc" / "customs" / "rules"
  24. SAMPLES = ROOT / "samples"
  25. HEADERS = [
  26. "I_E_FLAG",
  27. "HS_CODE",
  28. "ORIGIN_COUNTRY",
  29. "TRADE_AREA_CODE",
  30. "DESTINATION_COUNTRY",
  31. "TRADE_COUNTRY",
  32. "DISTINATE_PORT",
  33. "DISTRICT_CODE",
  34. "TRADE_MODE",
  35. "DECLARING_CUSTOMS",
  36. "NAME_OF_CONSIGNOR/CONSIGNEE_IN_CHINA",
  37. "CONSIGNEE/SHIPPER_CUSTOMS_REGISTER_10_DIGIT_NUMBER",
  38. "CONSIGNEE/SHIPPER_UNIFIED_SOCIAL_CREDIT_NUMBER",
  39. "CONSIGNEE_NAME",
  40. "CONSIGNEE/SHIPPER_AEO_NUMBER",
  41. "IMPORTER_COMPANY_NAME",
  42. "IMPORTER_CUSTOMS_REGISTER_10_DIGIT_NUMBER",
  43. "IMPORTER_UNIFIED_SOCIAL_CREDIT_NUMBER",
  44. "TRAF_MODE",
  45. "PACKING_TYPE",
  46. "YEAR",
  47. "WEEK",
  48. "MONORSUM",
  49. "COUNT",
  50. "G_MODEL",
  51. ]
  52. #: 应用场景 → 第一轮/第二轮命中的领域(尽量覆盖 8 个标签)
  53. SCENES = [
  54. "计算机系统",
  55. "手机终端",
  56. "汽车电子",
  57. "基站",
  58. "光伏逆变器",
  59. "工业控制",
  60. "卫星通信",
  61. "服务器",
  62. "智能驾驶",
  63. "路由器",
  64. "储能电池",
  65. "医疗设备",
  66. ]
  67. COUNTRIES = ["KOR", "JPN", "TWN", "HKG", "SGP", "USA", "CHN", "MYS", "PHL", "THA", "DEU", "NLD"]
  68. CAPACITOR_BRANDS = [
  69. ("MURATA", "村田"),
  70. ("TDK", "TDK"),
  71. ("TAIYO YUDEN", "太阳诱电"),
  72. ("SAMSUNG", "三星"),
  73. ("YAGEO", "国巨"),
  74. ("WALSIN", "华新科"),
  75. ("Panasonic", "松下"),
  76. ("KYOCERA(AVX)", "京瓷"),
  77. ]
  78. CAPACITOR_SERIES = {
  79. "多层瓷介电容器(MLCC)": ("8532241000", ["GRM", "CGA", "GRT"], "陶瓷介质,多层结构"),
  80. "单层瓷介电容器": ("8532230000", ["CC", "SL"], "陶瓷介质,单层结构"),
  81. "铝电解电容器": ("8532221000", ["EEE", "UVR"], "铝电解"),
  82. "钽电解电容器": ("8532211000", ["TPS", "TAJ"], "钽电解"),
  83. "其他电容器": ("8532290000", ["MKP", "CBB"], "塑料介质"),
  84. }
  85. def _load_rule_workbook(name: str):
  86. return load_workbook(RULES / name, read_only=True, data_only=True)
  87. def load_brand_country() -> dict[str, str]:
  88. workbook = _load_rule_workbook("数据映射规则.xlsx")
  89. mapping = {}
  90. for brand, country in workbook["品牌国别映射规则"].iter_rows(min_row=2, values_only=True):
  91. if brand and country:
  92. mapping[str(brand)] = str(country)
  93. workbook.close()
  94. return mapping
  95. def load_capacitor_hs() -> list[tuple[str, str]]:
  96. workbook = _load_rule_workbook("数据映射规则.xlsx")
  97. items = []
  98. for row in workbook["电容器映射规则"].iter_rows(min_row=2, values_only=True):
  99. if row[2] and row[1]:
  100. code = str(row[2]).split(".")[0].strip()
  101. if len(code) == 10:
  102. items.append((code, str(row[1])))
  103. workbook.close()
  104. return items
  105. def load_memory_models() -> dict[str, dict[str, list[str]]]:
  106. """返回 {分组: {品牌: [型号, ...]}},分组建模 DDR3/DDR4/DDR5/SDRAM/NAND/NOR/EEPROM。"""
  107. workbook = _load_rule_workbook("数据映射规则.xlsx")
  108. groups: dict[str, dict[str, set[str]]] = defaultdict(lambda: defaultdict(set))
  109. known_brands = {"SK HYNIX", "SAMSUNG", "MICRON", "NANYA", "CXMT", "HUAWEI", "BIWIN", "LONGSYS", "TOSHIBA", "KIOXIA"}
  110. for row in workbook["存储器型号分类映射规则"].iter_rows(min_row=2, values_only=True):
  111. en, cn, model, prefix, desc, c1, c2, c3, c4 = (list(row) + [None] * 9)[:9]
  112. if not model or str(c1 or "") != "存储器":
  113. continue
  114. content = " ".join(str(value or "") for value in (desc, c2, c3, c4)).upper()
  115. group = None
  116. generation = re.search(r"(?:LP)?DDR([345])", content)
  117. if generation:
  118. group = f"DDR{generation.group(1)}"
  119. elif "SDRAM" in content and "DDR" not in content:
  120. group = "SDRAM"
  121. elif "NAND" in content:
  122. group = "NAND"
  123. elif "NOR" in content:
  124. group = "NOR"
  125. elif "EEPROM" in content:
  126. group = "EEPROM"
  127. if not group:
  128. continue
  129. brand = str(en or "").upper()
  130. if brand not in known_brands:
  131. continue
  132. groups[group][brand].add(str(model))
  133. workbook.close()
  134. trimmed: dict[str, dict[str, list[str]]] = {}
  135. for group, brands in groups.items():
  136. trimmed[group] = {}
  137. for brand, models in brands.items():
  138. ordered = sorted(models)
  139. step = max(1, len(ordered) // 8)
  140. trimmed[group][brand] = ordered[::step][:8]
  141. return trimmed
  142. def load_ports() -> list[tuple[str, str]]:
  143. workbook = _load_rule_workbook("进出口数据字段需求20260819.xlsx")
  144. ports = []
  145. wanted = {"韩国", "日本", "中国台湾", "中国香港", "新加坡", "美国", "中国", "马来西亚"}
  146. for code, cn, en, country in workbook["映射规则_港口代码"].iter_rows(min_row=2, values_only=True):
  147. if code and country and str(country) in wanted and len(str(code)) == 6:
  148. ports.append((str(code), str(country)))
  149. workbook.close()
  150. # 每个国家留两个,避免港口维度过于稀疏
  151. by_country: dict[str, list[tuple[str, str]]] = defaultdict(list)
  152. for code, country in ports:
  153. if len(by_country[country]) < 2:
  154. by_country[country].append((code, country))
  155. return [item for items in by_country.values() for item in items][:14]
  156. def month_weeks(start: str, end: str) -> list[tuple[int, int, str]]:
  157. """返回 [(ISO年, ISO周, 归属月)],按周四归月。"""
  158. start_year = int(start[:4])
  159. end_year = int(end[:4])
  160. result = []
  161. for year in range(start_year, end_year + 1):
  162. for week in range(1, 54):
  163. try:
  164. thursday = date.fromisocalendar(year, week, 4)
  165. except ValueError:
  166. continue
  167. key = f"{thursday:%Y-%m}"
  168. if start <= key <= end:
  169. result.append((year, week, key))
  170. return result
  171. def build_memory_series() -> list[dict]:
  172. models = load_memory_models()
  173. hs_by_group = {
  174. "DDR3": "8542321010",
  175. "DDR4": "8542321010",
  176. "DDR5": "8542321010",
  177. "SDRAM": "8542321010",
  178. "NAND": "8542329090",
  179. "NOR": "8542329090",
  180. "EEPROM": "8542329090",
  181. }
  182. capacity = {"DDR3": "4G", "DDR4": "8G", "DDR5": "16G", "SDRAM": "512M", "NAND": "128G", "NOR": "128M", "EEPROM": "4M"}
  183. series = []
  184. for group, brands in models.items():
  185. for brand, brand_models in brands.items():
  186. for model in brand_models:
  187. series.append(
  188. {
  189. "product": "存储器",
  190. "group": group,
  191. "hs": hs_by_group.get(group, "8542321090"),
  192. "brand": brand,
  193. "model": model,
  194. "capacity": capacity.get(group, "8G"),
  195. "base_price": 1.5 if group.startswith("DDR") else (0.8 if group in ("NAND", "NOR") else 0.4),
  196. }
  197. )
  198. return series
  199. def build_capacitor_series() -> list[dict]:
  200. series = []
  201. for brand, brand_cn in CAPACITOR_BRANDS:
  202. for klass, (hs, prefixes, medium) in CAPACITOR_SERIES.items():
  203. for index in range(6):
  204. model = f"{prefixes[index % len(prefixes)]}{100 + index * 37}{'K' if index % 2 else 'M'}"
  205. series.append(
  206. {
  207. "product": "电容器",
  208. "group": klass,
  209. "hs": hs,
  210. "brand": brand,
  211. "brand_cn": brand_cn,
  212. "model": model,
  213. "medium": medium,
  214. "base_price": 0.02 if "MLCC" in klass else 0.05,
  215. }
  216. )
  217. return series
  218. def _capacitor_model(series: dict, scene: str | None) -> str:
  219. brand = series["brand"]
  220. model = series["model"]
  221. base = f"4|0|{'多层' if 'MLCC' in series['group'] else '单层'}|{series['medium']}|{brand}|{model}"
  222. if scene and random.random() < 0.35:
  223. return f"{base}|||应用场景:{scene}"
  224. if scene:
  225. return f"{base}|||{scene}用"
  226. return f"{base}|||"
  227. def _memory_model(series: dict, scene: str) -> str:
  228. return (
  229. f"4|0|{scene}|数据存储|已封装|已切割|{series['brand']}|{series['model']}|量产|{series['capacity']}|||"
  230. )
  231. def generate(rows_target: int, start: str, end: str, seed: int) -> tuple[list[list[str]], dict]:
  232. """生成稠密的逐月数据。
  233. 真实海关数据里,同一个型号几乎每个月都有申报记录;价格指数样本要求
  234. “当期与基期都有数”,所以测试数据必须逐月覆盖,否则价格指数会按业务规则留空。
  235. 因此这里先算出覆盖全部月份所需的型号数,再按月循环生成。
  236. """
  237. random.seed(seed)
  238. ports = load_ports()
  239. if not ports:
  240. ports = [("KOR000", "韩国")]
  241. all_series = build_capacitor_series() + build_memory_series()
  242. weeks = month_weeks(start, end)
  243. months = sorted({item[2] for item in weeks})
  244. weeks_by_month: dict[str, list[str]] = defaultdict(list)
  245. for year, week, month in weeks:
  246. weeks_by_month[month].append(f"{year}|{week}")
  247. capacity = len(months) * 2 # 每个型号每月约 2 条记录
  248. keep = min(len(all_series), max(24, rows_target // max(capacity, 1)))
  249. # 按“产品+类目”分组轮转选取,保证每个类目都有样本(否则某些模板表会是空的)
  250. grouped: dict[tuple[str, str], list[dict]] = defaultdict(list)
  251. for series in all_series:
  252. grouped[(series["product"], series["group"])].append(series)
  253. for items in grouped.values():
  254. random.shuffle(items)
  255. series_list: list[dict] = []
  256. depth = 0
  257. while len(series_list) < keep:
  258. added = False
  259. for key in sorted(grouped):
  260. if depth < len(grouped[key]) and len(series_list) < keep:
  261. series_list.append(grouped[key][depth])
  262. added = True
  263. if not added:
  264. break
  265. depth += 1
  266. trend = {index: random.uniform(-0.6, 1.2) for index in range(len(series_list))}
  267. capacitor_hs = {code for code, _name in load_capacitor_hs()}
  268. memory_hs = {"8542321010", "8542321090", "8542329010", "8542329090"}
  269. rows: list[list[str]] = []
  270. expected = {
  271. "rows": 0,
  272. "included": 0,
  273. "excluded": 0,
  274. "amount": 0.0,
  275. "quantity": 0.0,
  276. "zero_or_negative": 0,
  277. "months": [months[0], months[-1]],
  278. "series": len(series_list),
  279. "amount_by_c1": {"电容器": 0.0, "存储器": 0.0},
  280. "quantity_by_c1": {"电容器": 0.0, "存储器": 0.0},
  281. "by_month": {},
  282. }
  283. company_index = 1
  284. good_rows: list[list[str]] = []
  285. for month_index, month in enumerate(months):
  286. progress = month_index / max(len(months) - 1, 1)
  287. month_amount = 0.0
  288. month_quantity = 0.0
  289. for index, series in enumerate(series_list):
  290. repeats = 2 if random.random() < 0.75 else 1
  291. for _ in range(repeats):
  292. scene = random.choice(SCENES) if random.random() < 0.85 else None
  293. if series["product"] == "电容器":
  294. model_text = _capacitor_model(series, scene)
  295. else:
  296. model_text = _memory_model(series, scene or "")
  297. base_amount = random.uniform(800, 90000)
  298. if series["product"] == "存储器":
  299. base_amount *= random.uniform(3, 12)
  300. amount = base_amount * (1 + trend[index] * progress) * random.uniform(0.9, 1.1)
  301. unit_price = series["base_price"] * (1 + trend[index] * progress * 0.5) * random.uniform(0.92, 1.08)
  302. quantity = max(1, int(amount / max(unit_price, 0.001)))
  303. year, week = random.choice(weeks_by_month[month]).split("|")
  304. row = [
  305. "I",
  306. series["hs"],
  307. random.choice(COUNTRIES),
  308. random.choice(COUNTRIES),
  309. "CHN",
  310. random.choice(COUNTRIES),
  311. random.choice(ports)[0],
  312. "32026",
  313. "110",
  314. "1500",
  315. f"福州智捷进出口贸易有限公司{company_index}",
  316. f"3122560{company_index:03d}",
  317. f"91350100MACNPJ{company_index:04d}",
  318. "Apple Inc.",
  319. f"SG12345{company_index:06d}",
  320. "沪东精密仪器制造有限公司",
  321. f"31225600{company_index:02d}",
  322. f"121000000YP71XRH{company_index:03d}",
  323. random.choice(["5", "4", "Y"]),
  324. "包/袋",
  325. year,
  326. week,
  327. f"{amount:.2f}",
  328. str(quantity),
  329. model_text,
  330. ]
  331. rows.append(row)
  332. good_rows.append(row)
  333. company_index += 1
  334. expected["rows"] += 1
  335. month_amount += amount
  336. month_quantity += quantity
  337. # 每月固定注入少量零值/负值金额,验证“保留原值 + 告警”不被静默丢弃
  338. for _ in range(max(1, len(series_list) // 60)):
  339. template = list(random.choice(good_rows))
  340. template[22] = random.choice(["0", "-1200.50"])
  341. row = list(template)
  342. rows.append(row)
  343. expected["rows"] += 1
  344. expected["zero_or_negative"] += 1
  345. month_amount += float(row[22])
  346. month_quantity += float(row[23])
  347. expected["by_month"][month] = {"amount": round(month_amount, 2), "quantity": month_quantity}
  348. for row in rows:
  349. hs = row[1]
  350. if not (len(hs) == 10 and hs.isdigit()):
  351. continue
  352. if hs not in capacitor_hs and hs not in memory_hs:
  353. continue
  354. try:
  355. int(row[20]), int(row[21])
  356. except (TypeError, ValueError):
  357. continue
  358. if int(row[20]) > int(end[:4]):
  359. continue
  360. key = "电容器" if hs in capacitor_hs else "存储器"
  361. expected["amount_by_c1"][key] += float(row[22])
  362. expected["quantity_by_c1"][key] += float(row[23])
  363. expected["amount_by_c1"] = {key: round(value, 2) for key, value in expected["amount_by_c1"].items()}
  364. expected["quantity_by_c1"] = {key: round(value, 0) for key, value in expected["quantity_by_c1"].items()}
  365. # 明确的“应被排除”记录:8 位 HS、非法周号、超出统计年份
  366. for hs in ["85423210", "85322410", "85177000"]:
  367. row = list(good_rows[len(rows) % len(good_rows)])
  368. row[1] = hs
  369. rows.append(row)
  370. expected["rows"] += 1
  371. expected["excluded"] += 1
  372. for week in ["60", "0", "abc"]:
  373. row = list(good_rows[len(rows) % len(good_rows)])
  374. row[21] = week
  375. rows.append(row)
  376. expected["rows"] += 1
  377. expected["excluded"] += 1
  378. future = list(good_rows[3])
  379. future[20] = "2031"
  380. future[21] = "2"
  381. rows.append(future)
  382. expected["rows"] += 1
  383. expected["excluded"] += 1
  384. expected["included"] = expected["rows"] - expected["excluded"]
  385. expected["amount"] = round(sum(item["amount"] for item in expected["by_month"].values()), 2)
  386. expected["quantity"] = round(sum(item["quantity"] for item in expected["by_month"].values()), 0)
  387. random.shuffle(rows)
  388. return rows, expected
  389. def write_dataset(name: str, rows: list[list[str]], expected: dict, encoding: str = "utf-8-sig") -> Path:
  390. SAMPLES.mkdir(parents=True, exist_ok=True)
  391. path = SAMPLES / f"{name}.csv"
  392. with path.open("w", encoding=encoding, newline="") as handle:
  393. writer = csv.writer(handle)
  394. writer.writerow(HEADERS)
  395. writer.writerows(rows)
  396. (SAMPLES / f"{name}.expected.json").write_text(
  397. json.dumps(expected, ensure_ascii=False, indent=2), encoding="utf-8"
  398. )
  399. return path
  400. def main() -> None:
  401. parser = argparse.ArgumentParser(description="生成海关测试数据集")
  402. parser.add_argument("--rows", type=int, default=0, help="目标行数;0 表示生成 small+medium 两套")
  403. parser.add_argument("--name", default="custom", help="数据集名称(输出 samples/<name>.csv)")
  404. parser.add_argument("--start", default="2025-01", help="起始月份 YYYY-MM")
  405. parser.add_argument("--end", default="2026-08", help="结束月份 YYYY-MM")
  406. parser.add_argument("--seed", type=int, default=20261111)
  407. args = parser.parse_args()
  408. if args.rows:
  409. rows, expected = generate(args.rows, args.start, args.end, args.seed)
  410. path = write_dataset(args.name, rows, expected)
  411. print(f"已生成 {path}({len(rows)} 行,纳入 {expected['included']} 行)")
  412. return
  413. for name, target in [("small", 1200), ("medium", 12000)]:
  414. rows, expected = generate(target, args.start, args.end, args.seed)
  415. path = write_dataset(name, rows, expected)
  416. print(f"已生成 {path}({len(rows)} 行,纳入 {expected['included']} 行)")
  417. if __name__ == "__main__":
  418. main()