OpenEDG PCAD: Data Cleaning and Validation

What this domain tests

Cleaning and validation turns raw, inconsistent data into a dataset that is complete enough, correctly typed, and rule-compliant for analysis. The exam tests missing-value handling, duplicates, type conversion, outlier treatment, and validation checks.

Core rules

• Inspect first: df.info(), df.isna().sum(), df.describe(), and value counts for categorical columns.
• Missing data: decide whether to drop, impute with mean, median, mode, or forward-fill, or flag. Document the choice, because imputation changes distributions.
• Duplicates: use df.duplicated(subset=[...]) then drop_duplicates(..., keep='last') when the latest record is authoritative.
• Types: pd.to_datetime(..., errors='coerce') and pd.to_numeric(..., errors='coerce') make bad values visible as NaN instead of crashing.
• Validation: enforce ranges, allowed categories, uniqueness, and referential integrity; fail loudly or quarantine rows.

Worked example

An orders CSV has order_id, order_date, and quantity. You parse dates with pd.to_datetime(df['order_date'], errors='coerce'), count the NaT rows, drop duplicates on order_id keeping the last row, then filter quantity > 0. Finally you assert order_id.is_unique and quantity.notna().all(). Reasoning: coercion exposes malformed dates rather than raising; deduplication on the business key prevents double-counted revenue; the assertion turns a silent data problem into a visible failure.

Traps that cost marks

• Calling df.dropna(inplace=True) and assuming it returns a DataFrame; it returns None.
• Using chained assignment like df[df.a > 0]['b'] = 0, which may not modify the original.
• Imputing the mean for skewed data or for a column where zero is meaningful.
• Dropping every row with any missing value and losing most of the sample.
• Treating '0', 0, and 'unknown' as the same category without a mapping rule.