Principles of Data Wrangling

Chapter 7. Using Transformation to Clean Data

The third type of data transformation cleans a dataset to fix quality and consistency issues. Cleaning predominately involves manipulating individual field values within records. The most common variants of cleaning involve addressing missing (or NULL) values and addressing invalid values.

Addressing Missing/NULL Values

There are two basic approaches to addressing missing/null values. On the one hand, you can filter out records with missing or NULL fields. On the other hand, you can replace missing or NULL values. Often referred to as data imputation, filling in missing or NULL values might utilize many different strategies. In some cases, the best approach involves inserting the average or median value. In other cases, it is better to generate values from similar records; for example, similar customers or similar transactions. Alternatively, if your data has strong ordering (because it is a time-series dataset, for example), you might be able to fill in missing values by using the last valid value.

Addressing Invalid Values

Extending beyond missing values, another key set of cleaning transformations deals with invalid values—invalid because they are inconsistent with other fields (e.g., a customer age compared with their data of birth), ambiguous (e.g., two-digit years or abbreviations like “CT”—is that Connecticut or Court?), or improperly encoded. In some cases, the correct or consistent value for the field can be calculated ...