15 Data Cleaning and Preparation
Raw data collected from surveys, transactions, sensors, or secondary sources is almost never ready for direct analysis. Data cleaning and preparation is the process of detecting and correcting missing values, errors, duplicates, and inconsistencies so that the dataset accurately and consistently represents what it is supposed to measure. Analysts often spend more time cleaning data than on any other step in the analytics workflow.
15.1 Why Data Cleaning Matters
- “Garbage in, garbage out”: even the most advanced statistical model or dashboard will produce misleading results if built on dirty data.
- Missing values, duplicates, and inconsistent formats can silently bias summary statistics and hypothesis tests.
- Clean, well-structured data is a prerequisite for reliable descriptive, diagnostic, predictive, and prescriptive analytics.
15.2 Common Data Quality Problems
Missing Values
Cells or fields with no recorded value, arising from non-response, system errors, or skipped questions.
Duplicate Records
The same entity (customer, transaction, respondent) appearing more than once in the dataset.
Inconsistent Formatting
The same underlying value recorded differently across rows, such as “NY”, “New York”, and “new york” for the same city, or mixed date formats.
Outliers and Invalid Values
Values that are implausible or fall far outside the expected range, such as a negative age or a purchase amount of zero when a transaction clearly occurred.
Structural Errors
Typos, inconsistent capitalisation, mislabeled categories, and incorrect data types (numbers stored as text, for example).
15.3 Handling Missing Values
Deletion
Removing rows or columns with missing values, appropriate when missingness is small and random, but risky when it removes a large or non-random portion of the data.
Imputation
Filling in missing values using the mean, median, or mode of the column, a predicted value from a model, or a domain-appropriate constant. Example: Replacing a missing income value with the median income of similar respondents.
Flagging
Creating an indicator variable that marks which values were missing, preserving the information that a value was originally absent even after imputation.
15.4 Handling Duplicates and Inconsistencies
Deduplication
Identifying and removing records that refer to the same entity, using exact matches on key fields (customer ID, email) or fuzzy matching when identifiers are not perfectly consistent.
Standardisation
Converting values to a single consistent format: unifying capitalisation and spelling, converting all dates to one format, and mapping category labels (e.g. “M”/“Male”/“male” all become “Male”).
Outlier Treatment
Investigating extreme values to determine whether they are genuine (a legitimately very large purchase) or errors (a data-entry mistake), and then correcting, capping, or removing them as appropriate.
Type Conversion and Encoding
Ensuring each column has the correct data type (numeric, date, categorical) and encoding categorical variables (such as one-hot or label encoding) so that they can be used in statistical models.
15.5 Data Cleaning in R and Python
15.6 Common Challenges and Best Practices
Challenges
- Deciding whether missingness is random or systematic before choosing how to handle it.
- Over-aggressive outlier removal can discard genuine, informative extreme values.
- Cleaning steps performed inconsistently across a team can make results hard to reproduce.
Best Practices
- Document every cleaning decision (what was removed, imputed, or recoded, and why).
- Keep a copy of the raw, uncleaned data before making any changes.
- Visualise the data before and after cleaning to confirm that the changes had the intended effect.
- Automate cleaning steps in a script rather than editing spreadsheets by hand, so the process is repeatable.
Transition to Descriptive Analytics
With data collected, sampled appropriately, and cleaned, the dataset is finally ready for analysis. The next part of the book turns to descriptive analytics — summarising and describing the cleaned data using measures of central tendency, dispersion, and visualisation.
Summary
| Concept | Description |
|---|---|
| Foundations | |
| Data Cleaning | The process of detecting and correcting missing values, errors, duplicates, and inconsistencies in raw data |
| Missing Values | Cells or fields with no recorded value, from non-response, system errors, or skipped questions |
| Duplicate Records | The same entity appearing more than once in a dataset |
| Inconsistent Formatting | The same value recorded differently across rows, such as varied capitalisation or date formats |
| Outliers/Invalid Values | Values that are implausible or fall far outside the expected range |
| Structural Errors | Typos, inconsistent capitalisation, mislabeled categories, or incorrect data types |
| Handling Techniques | |
| Deletion (Missing Data) | Removing rows or columns with missing values, suitable when missingness is small and random |
| Imputation | Filling missing values using the mean, median, mode, a model prediction, or a domain constant |
| Flagging Missingness | Creating an indicator variable that records which values were originally missing |
| Deduplication | Identifying and removing records referring to the same entity, using exact or fuzzy matching |
| Standardisation | Converting values to a single consistent format across capitalisation, dates, and category labels |
| Outlier Treatment | Investigating and correcting, capping, or removing extreme values found to be genuine or erroneous |
| Type Conversion and Encoding | Ensuring correct data types and encoding categorical variables for use in statistical models |
| In R and Python | |
| R duplicated()/is.na() | Base R functions used to detect duplicate rows and missing values |
| Python drop_duplicates()/fillna() | Pandas methods used to remove duplicate rows and fill in missing values |