Preprocessing plan for a cross-project dataset with mixed text and numeric features, inconsistent encodings, and heterogeneous missing-value rules
Scope and objectives
- Harmonize schemas, encodings, and missing-value semantics across projects.
- Produce clean, typed, and comparable features for modeling while preventing leakage and preserving cross-project generalization.
Pipeline steps
1) Data inventory and schema harmonization
- Compile a data dictionary per project: column names, types, units, allowed ranges, label definitions, missing-value codes, and known sentinels (e.g., -999, 9999, “N/A”, “Unknown”).
- Create a canonical schema with:
- Standardized column names and data types.
- Crosswalk tables mapping project-specific categories/labels to canonical codes.
- Explicit unit definitions for numeric fields.
- Add source metadata columns (e.g., project_id, source_version, ingestion_timestamp).
2) File and character encoding standardization
- Detect file encodings via heuristic tools (e.g., uchardet/chardet) and fallback logic.
- Convert all text to UTF-8 with strict error handling:
- Replace invalid byte sequences with U+FFFD and log occurrences.
- Normalize Unicode to NFC or NFKC consistently.
- Normalize line endings and ensure consistent delimiter, quote, and escape rules for structured files.
3) Locale and format normalization
- Dates/times: parse with project-specific formats; standardize to ISO-8601; convert time zones to UTC; record original timezone if needed.
- Numbers: normalize decimal and thousands separators; enforce dot-decimal; strip locale artifacts (e.g., non-breaking spaces).
- Units: convert to canonical units (e.g., kg, m/s, °C); document conversion rules and edge cases.
- Booleans: unify to {0,1} or {False,True}; standardize truthy/falsey strings.
4) Column alignment and feature presence
- Align to the canonical schema:
- For columns missing in some projects, create empty columns with NaN (or explicit missing-category token for categoricals).
- Resolve name collisions or synonyms via a mapping table.
- Drop columns deemed non-informative or leakage-prone (e.g., direct label proxies, unique IDs used as features).
- Handle duplicate columns (same semantics, different names) by merging with precedence rules.
5) Missingness harmonization (core for inconsistent rules)
- Build a missingness lexicon per column and project:
- String tokens: “NA”, “N/A”, “—”, “?”, “unknown”, “none”, “null”.
- Numeric sentinels: -1, -999, 999, 9999, 1e9, 0 for non-zero-only measures, out-of-range flags.
- Domain-based invalids: negative durations, impossible dates, zero divisions.
- Convert all such values to a single NaN representation; preserve a “missing_reason” code if useful.
- Create missingness indicators:
- One binary flag per feature (is_<col>_missing).
- Optional reason-specific flags if they carry signal (e.g., system_not_collected vs user_refused).
- Quantify per-record missingness to support downstream filtering or modeling strategies.
6) Type inference and casting
- Infer and enforce types: numeric (int/float), categorical, text, datetime, boolean, ordinal.
- Detect numeric-coded categoricals (e.g., 1=Red, 2=Blue) and cast to categorical with proper labels.
- Ensure consistent types across projects for the same field.
7) De-duplication and record linkage
- Remove exact duplicates (all fields identical) per project, then cross-project (if meaningful).
- Identify probable duplicates via:
- Key-based (stable IDs) when available.
- Fuzzy matching for text fields (e.g., MinHash/LSH or cosine for embeddings).
- Keep a linkage table for traceability; choose deduplication priority rules.
8) Outlier and invalid value handling
- Define column-wise validation rules from domain knowledge (ranges, regexes, business constraints).
- Flag violations as invalid; convert to NaN if irrecoverable.
- For continuous features, detect outliers using robust methods (IQR/winsorization or robust z-scores using MAD) computed on the training split only.
- Decide on clipping, winsorization, or leaving as-is depending on model sensitivity.
9) Text preprocessing (multi-project, potentially multi-lingual)
- Language detection per text field; if multi-lingual, consider:
- Language-specific pipelines, or
- A language-agnostic representation (e.g., multilingual embeddings).
- Normalize text:
- Unicode normalization already enforced.
- Lowercasing (unless casing is informative).
- Remove/control punctuation, excessive whitespace, control characters.
- Normalize URLs, emails, mentions, hashtags to placeholders if not informative.
- Handle emojis/symbols based on task needs.
- Tokenization:
- Wordpiece/BPE for transformer models, or
- Standard tokenizers for classical models.
- Optional normalization:
- Lemmatization/stemming (if using sparse models).
- Stopword handling by language.
- Vectorization choices (decide once based on downstream model):
- Sparse: TF-IDF with n-grams; limit vocabulary; handle OOV.
- Dense: pretrained embeddings (e.g., multilingual models) with pooling; store embedding version.
- Truncation strategy for long texts; log truncation rates.
10) Numeric and continuous feature processing
- Unit-harmonized values already ensured.
- Transformations as needed:
- Log1p for heavy-tailed distributions.
- Box-Cox/Yeo-Johnson if appropriate.
- Scaling:
- Use robust scaler (median/IQR) or standard scaler.
- Compute statistics on training data only.
- Consider project-aware scaling if strong covariate shift exists (fit per project, then standardize to global space or use project residualization).
- Handle zero-inflation explicitly if present (e.g., hurdle features).
11) Categorical feature processing
- Harmonize category labels across projects via crosswalk tables.
- Treat rare categories: collapse into “Other” using a frequency threshold per training data.
- Encoding:
- One-hot for low-cardinality features.
- Target encoding for high-cardinality with K-fold, noise, and project-aware folds to prevent leakage.
- Hashing trick as an alternative when cardinality is extreme.
- Reserve an “Unknown” category for unseen levels at inference.
12) Temporal features
- From standardized datetimes, derive consistent time-based features (day-of-week, month, hour, season).
- For time series, handle resampling/alignment; ensure no forward-looking leakage.
13) Cross-project distribution shift mitigation (optional but recommended)
- Add project_id as a feature only if the downstream objective benefits and it does not encode leakage.
- Reweight samples to align marginal distributions if class or feature imbalance is severe.
- Consider per-project normalization or domain adaptation preprocessing (e.g., CORAL) where justified.
14) Train/validation/test splitting and leakage control
- Split by project or by time to reflect deployment scenario:
- Cross-project generalization: leave-one-project-out or group-wise splits by project_id.
- Temporal generalization: chronological splits.
- Fit all imputers, encoders, scalers, and vectorizers on training data only; apply to val/test.
- Ensure any target-based encodings use only in-fold target information.
15) Imputation strategy (after harmonized missingness)
- Numeric:
- Simple: median or constant with missingness indicator.
- Advanced: KNN imputer or model-based imputation; ensure scalability and leakage control.
- Categorical:
- Mode or “Unknown”.
- Text:
- Empty string or a special token [MISSING_TEXT].
- Evaluate imputation impact per feature; prefer simple, robust methods unless gains justify complexity.
16) Feature selection and redundancy checks
- Remove features with:
- Near-zero variance globally.
- Excessive missingness beyond a threshold.
- High multicollinearity (e.g., VIF or correlation pruning).
- Preserve interpretability by documenting dropped features.
17) Quality assurance and validation
- Automated checks:
- Type and range assertions post-transform.
- Missingness rates before/after.
- Category coverage and unseen-level handling.
- Text length and tokenization diagnostics.
- Drift checks across projects/splits (e.g., PSI/KL) for key features.
18) Reproducibility and artifact management
- Implement the pipeline as a single, versioned transformer (e.g., scikit-learn Pipeline/ColumnTransformer with custom steps).
- Version control for:
- Data dictionaries, crosswalks, missingness lexicons, unit conversions, vectorizer vocabularies/embeddings.
- Log configuration and random seeds; fix library versions; store training statistics for all preprocessors.
Deliverables
- Canonical schema and crosswalk tables.
- Missingness lexicon and imputation plan per feature.
- Encoding and unit normalization rules.
- A versioned preprocessing pipeline artifact with train-only fitted statistics.
- QA report with pre/post distributions, missingness, and drift summaries.
