Data Preprocessing — Cleaning, Integration & Transformation

1Why preprocessing is needed

Real-world databases and data warehouses are commonly incomplete, noisy and inconsistent. Mining dirty data gives poor results, and users don't trust results from data they believe is dirty.

Mining routines do have some ways of handling incomplete or noisy data, but they are not always robust. They focus mostly on avoiding overfitting. So it is useful to run the data through data cleaning routines first.

The four preprocessing techniques

2Data cleaning — handling missing values

Six methods, from simplest to smartest:

3Noisy data and smoothing

Noise is a random error or variance in a measured variable. Three smoothing techniques:

1. Binning

Binning smooths a sorted value by consulting its neighbourhood (the values around it). This is local smoothing.

• Smoothing by bin medians replaces each value with the bin's median.
• The larger the bin width, the stronger the smoothing effect.
• Bins can also be equal-width (constant interval range). There's an interactive binning lab in Week 4.

2. Regression

Fit the data to a function. Linear regression finds the best line to fit two attributes so one can predict the other. Multiple linear regression uses more than two attributes and fits a multidimensional surface.

3. Clustering

Group similar values into clusters. Values that fall outside all clusters can be considered outliers.

4Data cleaning as a process

So far we've looked at techniques. In practice cleaning is a two-step iterative process:

Step 1 — Discrepancy detection

Causes of discrepancies: - Poorly designed data-entry forms with many optional fields - Human error in data entry - Deliberate errors (respondents hiding personal information) - Data decay (outdated addresses) - Inconsistent representations and inconsistent use of codes - Instrument and system errors - Data used for purposes other than originally intended - Integration inconsistencies (the same attribute has different names in different DBs)

How to detect them: start with metadata ("data about data"). Ask: what is the domain and data type of each attribute? What are the acceptable values? Do all values fall in the expected range? Are there known dependencies?

Look out for: - Inconsistent representations: "2004/12/25" vs "25/12/2004" - Field overloading: developers squeeze new attribute definitions into unused bits of existing attributes (e.g. using the unused bit of a 32-bit field that only needs 31)

Check the data against three kinds of rules:

Reasons for missing values (the null rule must handle these): 1. The person refuses to give the value, or it is not applicable (e.g. licence number left blank by non-drivers) 2. The data-entry person doesn't know the correct value 3. The value will be provided by a later step

Step 2 — Data transformation (to correct discrepancies)

Newer, interactive approaches

• Potter's Wheel is a publicly available cleaning tool that integrates discrepancy detection and transformation:
• You build transformations one step at a time on a spreadsheet-like interface, either graphically or by giving examples.
• Results show immediately, you can undo, and discrepancy checking runs in the background.
• Declarative languages: powerful extensions to SQL for specifying data-cleaning operations.
• Keep updating the metadata as you learn about the data. This speeds up cleaning of future versions.

5Data integration

Data integration combines data from multiple sources (databases, data cubes, flat files) into a coherent data store, as in data warehousing.

Issue 1 — Entity identification problem

How do we match equivalent real-world entities from different sources? Is customer_id in one DB the same attribute as cust_number in another? This is schema integration and object matching.

Solution: metadata for each attribute (name, meaning, data type, permitted range, null rules). Metadata also helps transform codes. For example, pay type may be "H"/"S" in one DB and 1/2 in another, so integration overlaps with cleaning.

Issue 2 — Redundancy

An attribute is redundant if it can be derived from other attributes (e.g. annual revenue). Inconsistent attribute naming can also create redundancy. Many redundancies can be found with correlation analysis.

Correlation coefficient (Pearson's product-moment)

For numerical attributes A and B over N tuples:

where Ā, B̄ are the means, σ_A, σ_B are the standard deviations, Σ(a_ib_i) is the sum of the cross-products, and −1 ≤ r ≤ +1.

6Data transformation

Data are transformed or consolidated into forms suitable for mining. Four kinds:

Min-max normalization

A linear transformation. It maps value v of A from [min_A, max_A] into [new_min_A, new_max_A]:

Z-score (zero-mean) normalization

Uses the mean Ā and standard deviation σ_A:

Use it when the actual min and max are unknown, or when outliers dominate min-max normalization.

Decimal scaling (extra, from the textbook)

Move the decimal point: v′ = v / 10^j, where j is the smallest integer such that max(|v′|) < 1. For example, if values range from −986 to 917, j = 3, so −986 → −0.986.

7Quick revision sheet