OEM Cross-Reference Enrichment Pipeline

A production-style data engineering workflow to standardize Dutch electrical article descriptions and extract OEM part numbers for cross-reference use.

Repository Description

This repository provides a practical, auditable pipeline for OEM extraction from semi-structured product descriptions. It combines deterministic normalization rules, manufacturer-specific parsing logic, and optional no-account web enrichment for unresolved candidates.

Key Features

• Description standardization (uppercase, whitespace cleanup, token normalization)
• OEM extraction with manufacturer-aware rules (LAPP/UNITRONIC, HELU, MOLEX/TE/JST)
• Explicit exclusion of standards and UL style numbers to reduce false positives
• Confidence scoring (Hoog, Medium, Laag)
• External lookup artifact generation for additional enrichment
• Account-free fallback enrichment from public search pages

Project Structure

• data/input: private source workbooks (excluded from public git)
• data/output: generated analysis workbooks (excluded from public git)
• data/output/reports: text summaries and metrics
• data/output/external-lookups: CSV artifacts for external enrichment
• scripts: reproducible pipeline scripts
• docs: documentation

Public-Safe Setup

This repository is prepared for public sharing.

• Real input/output datasets are ignored by git.
• Use synthetic demo files for showcasing and testing.
• Generate demo files with:
  • python scripts/create_demo_data.py
• The main pipeline automatically falls back to demo files when private input files are not present.

See docs/PUBLIC_SAFE_RELEASE.md for details.

Methodology

1. Normalize text:
   • Convert to uppercase
   • Trim leading/trailing whitespace
   • Collapse duplicate spaces
   • Replace known shorthand (GRS -> GRIJS, ZT -> ZWART)
2. Extract OEM candidates with prioritized rules:
   • LAPP/UNITRONIC patterns (7-8 digits, often 00/01 prefixes)
   • HELU patterns (5-6 digits with HELU context)
   • Connector patterns (for MOLEX/TE/JST contexts)
   • General patterns (parentheses and end-of-line 5-8 digit numbers)
3. Exclude known non-OEM numbers:
   • DIN/ISO/UL-style exclusions: 47100, 43650, 1015, 1007, 1569, 2464, 1061
4. Assign confidence and record extraction source.

Reproducible Run

1. Create environment and install dependencies:
   • python -m pip install -r requirements.txt
2. Optional: generate synthetic demo input files:
   • python scripts/create_demo_data.py
3. Build V2 core analysis:
   • python scripts/build_oem_analysis_v2.py
4. Run no-account web enrichment:
   • python scripts/no_account_enrichment.py
5. Merge enrichment into final workbook:
   • python scripts/merge_no_account_results.py

Main Outputs

• data/output/Cross_Reference_OEM_Analyse_V2.xlsx
• data/output/Cross_Reference_OEM_Analyse_V2_Uitgebreid.xlsx
• data/output/Cross_Reference_OEM_Analyse_V3_NoAccount.xlsx
• data/output/reports/Cross_Reference_OEM_Analyse_V2_Samenvatting.txt

Notes on Data Quality

• Account-free enrichment improves coverage but should be treated as Medium confidence unless confirmed against official manufacturer/distributor sources.
• For release-grade validation, add authenticated API checks where available.

License

MIT License. See LICENSE.