Organizing and Cleaning Data
What this domain tests
This domain tests turning a messy extract into a tidy, typed, documented dataset: reshaping to one row per observation, handling missing values and duplicates, standardizing text and types, and catching outliers and out-of-range values without destroying information.
Core rules and concepts
- Tidy structure: one row per observation, one column per variable, one table per observational unit. Values belong in cells, not in column headers like "sales_Jan", "sales_Feb".
- Missing values: drop only when few and random; otherwise impute (mean for symmetric numeric data, median for skewed, mode for categorical) or add a flag column such as was_imputed. Never silently replace blanks with 0 when 0 is a valid value.
- Duplicates: define the key first (e.g. invoice_id), then drop duplicates on that subset and state which record you keep.
- Text cleaning: strip whitespace, fix case, unify synonyms ("UK", "U.K.", "uk"), remove stray punctuation - before grouping, or "London " and "London" become two categories.
- Types: convert numeric strings to int/float, parse dates with a fixed format, and validate ranges (age 0-120, quantity >= 0). Decide explicitly what an impossible value becomes.
- Outliers: inspect before deleting. A value may be a typo (fix), a unit error (convert), or a genuine extreme (keep and report).
- Reproducibility: keep the raw file untouched, write cleaning steps as a script, and log every rule applied and how many rows each rule changed.
Worked example
A sales CSV has " Acme Ltd", a duplicate invoice INV-1042 appearing twice with different totals, and "N/A" in quantity. Cleaning order: strip and title-case the customer name; parse the date column; convert quantity with a rule that maps "N/A" to missing rather than 0; resolve INV-1042 by keeping the later timestamped record; then compute the median quantity excluding missing rows. Each step is written down so a reviewer can rerun it.
Traps
- Dropping every row with any missing field and losing most of the dataset.
- Mean-imputing a heavily skewed column; the median is safer.
- Editing the original file instead of writing a cleaned copy.
- Grouping before standardizing text, which splits one category into several.