Cleaning, integrating and transforming raw data so it is fit for mining.
8.1 Why pre-process
Real-world data is usually incomplete, noisy and inconsistent. If such data is mined without first being cleaned, the results are poor, a problem summed up by the phrase "garbage in, garbage out".
ExampleThe table below shows three rows from a raw customer extract.
| Cust ID | Age | Income (₹) | City | Gender |
|---|---|---|---|---|
| 101 | 34 | 6,20,000 | Lucknow | M |
| 102 | 250 | 4,80,000 | lucknow | Male |
| 103 | 29 | (missing) | Kanpur | F |
ExplanationRow 103 is incomplete, because its income is missing. Row 102 is noisy, because an age of 250 is impossible, and it is also inconsistent, because it writes the city as "lucknow" instead of "Lucknow" and the gender as "Male" instead of "M". If this data were clustered without cleaning, customer 102 would end up in a group of its own, and Lucknow would be counted as two different cities.
8.2 Forms of data pre-processing
Data pre-processing takes five main forms. Data cleaning fixes missing, noisy and inconsistent values, for example by correcting the age of 250. Data integration merges data from several sources, for example by joining the POS and CRM records on the customer ID. Data transformation changes the form of the data, for example by scaling incomes to the range 0 to 1. Data reduction shrinks the volume of data, for example by taking a 10 per cent sample of the bills. Discretisation turns numbers into ranges, for example by recording an age of 34 as "30–39".
8.3 Data cleaning: missing values
There are several ways to fill a missing value, and they differ in how much information they use.
ExampleCustomer 103, who is a "Gold" member, has no income recorded. The other ten customers in the data set have the incomes below, in lakhs of rupees.
| Customers | Incomes (₹ lakh) | Sum | Count | Mean |
|---|---|---|---|---|
| Gold members | 6.2, 7.5, 8.0, 6.3 | 28.0 | 4 | 28.0 ÷ 4 = 7.0 |
| Other members | 3.0, 3.5, 4.0, 4.5, 3.0, 4.0 | 22.0 | 6 | 22.0 ÷ 6 ≈ 3.67 |
| All customers | All ten values | 50.0 | 10 | 50.0 ÷ 10 = 5.0 |
The attribute mean is therefore ₹5,00,000 and the Gold class mean is ₹7,00,000. A regression fitted on the Gold members gives income (₹ lakh) = 1.0 + 0.2 × age, so for customer 103, who is 29, it predicts 1.0 + 0.2 × 29 = 1.0 + 5.8 = ₹6.8 lakh. The table below shows the value each method would fill in.
| Method | Value filled for customer 103 | When it is suitable |
|---|---|---|
| Ignore the tuple | The row is dropped | When the class label is missing, or when very few rows are affected |
| Fill in manually | The branch is asked and reports ₹6,50,000 | When the data set is small and each value matters |
| Use a global constant | "Unknown" | When speed matters, although mining may treat "Unknown" as a real group |
| Use the attribute mean | ₹5,00,000 | When a simple method is enough, although it ignores the class |
| Use the class mean | ₹7,00,000 | When the class is known; this is better because it uses the Gold class |
| Use the most probable value | ₹6,80,000, predicted by regression on age | When accuracy matters most; this takes the most effort |
ExplanationThe methods lower in the table use more information, so their guesses are usually closer to the true value. In practice, the class mean and the predicted value are the most common choices.
8.4 Data cleaning: noisy data
Noisy data contains random errors or extreme values, and there are four main ways to smooth it.
Binning. In binning, the values are sorted and divided into groups called bins, and each value is then smoothed using the other values in its bin.
In the figure, nine prices (4, 8, 15, 21, 21, 24, 25, 28 and 34) are divided into three equal-depth bins of three values each.
ExplanationWhen smoothing by means, each value is replaced by the mean of its bin. The bin means are (4 + 8 + 15) ÷ 3 = 27 ÷ 3 = 9 for bin 1, (21 + 21 + 24) ÷ 3 = 66 ÷ 3 = 22 for bin 2, and (25 + 28 + 34) ÷ 3 = 87 ÷ 3 = 29 for bin 3. When smoothing by boundaries, each value is replaced by whichever of its bin's minimum and maximum is closer. In bin 1, the value 8 is 8 − 4 = 4 away from 4 but 15 − 8 = 7 away from 15, so it becomes 4. In bin 3, the value 28 is 3 away from 25 but 6 away from 34, so it becomes 25. Every other value is already a boundary and stays the same.
Equal-width binning works differently, because it fixes the width of each bin instead of the number of values. The width is (maximum − minimum) ÷ number of bins = (34 − 4) ÷ 3 = 10, so the bins are 4 to 14, 14 to 24 and 24 to 34. Bin 1 then holds {4, 8}, bin 2 holds {15, 21, 21} and bin 3 holds {24, 25, 28, 34}, so the bins no longer hold equal numbers of values.
Clustering. Similar values are grouped into clusters, and any value that falls outside all the clusters is treated as an outlier.
ExampleEleven customer ages are recorded: 21, 22, 23, 24, 34, 35, 36, 58, 60, 62 and 250. They form three clusters: {21, 22, 23, 24} with centre (21 + 22 + 23 + 24) ÷ 4 = 22.5, {34, 35, 36} with centre 105 ÷ 3 = 35, and {58, 60, 62} with centre 180 ÷ 3 = 60. The value 250 is 250 − 60 = 190 away from the nearest centre, while every other age is within 2 of its centre.
ExplanationThe age of 250 does not belong to any cluster, so it is flagged as noise and is either corrected or removed.
Regression. A function is fitted to the data, and noisy values are replaced by the values the function predicts.
ExampleThe table below shows the area and the yearly electricity bill of five houses. A line fitted through the four normal houses gives bill ≈ 2.1 × area.
| House | Area (sq ft) | Recorded bill (₹) | Fitted bill = 2.1 × area (₹) |
|---|---|---|---|
| H1 | 800 | 1,700 | 1,680 |
| H2 | 1,000 | 21,000 | 2,100 |
| H3 | 1,200 | 2,500 | 2,520 |
| H4 | 1,500 | 3,150 | 3,150 |
| H5 | 2,000 | 4,200 | 4,200 |
ExplanationFor houses H1, H3, H4 and H5, the recorded bill is within ₹20 of the fitted value. House H2's recorded bill of ₹21,000 is ten times its fitted value of 2.1 × 1,000 = ₹2,100, so it is probably a typing error with an extra zero. It is therefore smoothed to ₹2,100.
Combined computer and human inspection. Software flags values that look suspicious, and a person then decides whether each one is really an error.
ExampleA rule flags any single grocery bill of more than ₹5 lakh, and an auditor then checks each flagged bill.
ExplanationSome of the flagged bills are genuine bulk orders, so the human check prevents correct values from being deleted by mistake.
8.5 Data cleaning: inconsistent data
Inconsistent data records the same thing in conflicting ways, and it must be made uniform before mining.
ExampleOne source records a product as "Lux Soap 100g" with the code 4501, while another source records it as "LUX 100 GM" with the code 4501-A. A business rule states that each code must refer to exactly one product name.
ExplanationMetadata and domain rules, such as "one code, one product" or "the date of birth must come before the date of joining", detect these conflicts. A cleaning step then maps both records to one standard record.
8.6 Data integration
Data integration combines data from several sources into one consistent store, and it raises three main problems.
The first problem is entity identification, also called schema integration, which means recognising that differently named fields refer to the same thing.
ExampleThe POS system uses a column called cust_id, the CRM system uses one called customer_no, and both identify the same customer.
ExplanationThe metadata records that both columns mean the same thing, so they are mapped to a single customer key in the warehouse.
The second problem is redundancy, which is detected by correlation analysis.
ExampleAfter merging, the table contains both monthly_income (X) and annual_income (Y) for five customers, in thousands of rupees. The correlation coefficient r is calculated with the formula below.
| Customer | x | y | x − x̄ | y − ȳ | (x − x̄)(y − ȳ) | (x − x̄)² | (y − ȳ)² |
|---|---|---|---|---|---|---|---|
| 1 | 20 | 250 | −20 | −234 | 4,680 | 400 | 54,756 |
| 2 | 30 | 365 | −10 | −119 | 1,190 | 100 | 14,161 |
| 3 | 40 | 480 | 0 | −4 | 0 | 0 | 16 |
| 4 | 50 | 610 | 10 | 126 | 1,260 | 100 | 15,876 |
| 5 | 60 | 715 | 20 | 231 | 4,620 | 400 | 53,361 |
| Total | 200 | 2,420 | 11,750 | 1,000 | 1,38,170 |
The means are x̄ = 200 ÷ 5 = 40 and ȳ = 2,420 ÷ 5 = 484. So r = 11,750 ÷ √(1,000 × 1,38,170) = 11,750 ÷ √13,81,70,000 = 11,750 ÷ 11,754.6 ≈ 0.9996.
ExplanationA correlation this close to 1 means that one attribute can be derived from the other, so one of them is redundant and is dropped.
For categorical attributes, a chi-square (χ²) test is used instead. Suppose 1,000 customers are classified by gender and by how they pay, as in the table of observed counts below.
| Observed | Card | Cash | Total |
|---|---|---|---|
| Male | 250 | 50 | 300 |
| Female | 200 | 500 | 700 |
| Total | 450 | 550 | 1,000 |
Each expected count is (row total × column total) ÷ grand total. The expected counts are therefore 300 × 450 ÷ 1,000 = 135 for male card users, 300 × 550 ÷ 1,000 = 165 for male cash users, 700 × 450 ÷ 1,000 = 315 for female card users and 700 × 550 ÷ 1,000 = 385 for female cash users. The statistic is χ² = Σ (observed − expected)² ÷ expected, which gives (250 − 135)² ÷ 135 + (50 − 165)² ÷ 165 + (200 − 315)² ÷ 315 + (500 − 385)² ÷ 385 = 97.96 + 80.15 + 41.98 + 34.35 = 254.45.
ExplanationThe table has (2 − 1) × (2 − 1) = 1 degree of freedom, and the critical value of χ² at the 0.05 level is 3.84. Because 254.45 is far greater than 3.84, gender and payment mode are strongly related, so one of them partly predicts the other.
The third problem is conflicting data values, where the same quantity is recorded in different units or under different rules.
ExampleOne source stores weights in kilograms and another in pounds. Similarly, one source stores prices including tax and another stores them without tax.
ExplanationAll the values are converted to one unit and one rule before the data is merged, so that totals and comparisons remain correct.
8.7 Data transformation
Data transformation changes data into a form that suits mining. The table below lists the main techniques with an example of each.
| Technique | Example |
|---|---|
| Smoothing | Binning daily sales to remove one-off spikes |
| Aggregation | Adding daily sales into monthly totals |
| Generalisation | Replacing a street with its city, or an age of 34 with "young adult" |
| Attribute construction | Creating area = length × width, or profit = price − cost |
| Normalisation | Scaling an income of 73,600 to 0.716 on a scale from 0 to 1 |
Normalisation is the technique most often tested with numbers. The first formula below is min-max normalisation, and the second is z-score normalisation.
ExampleIncomes range from ₹12,000 to ₹98,000, with a mean of ₹54,000 and a standard deviation of ₹16,000, and the value v = ₹73,600 is to be normalised. With min-max normalisation to the range 0 to 1, new_min = 0 and new_max = 1, so the formula reduces to (v − min) ÷ (max − min), and the result is (73,600 − 12,000) ÷ (98,000 − 12,000) = 61,600 ÷ 86,000 = 0.716. With z-score normalisation, the result is (73,600 − 54,000) ÷ 16,000 = 1.225. For decimal scaling, consider a different attribute whose values range from −986 to 917. The largest absolute value is 986, which has three digits, so the smallest power of 10 that brings it below 1 is 10³ = 1,000. Dividing every value by 1,000 turns −986 into −0.986 and 917 into 0.917.
ExplanationWithout normalisation, income, which is measured in thousands, would dominate age, which is measured in tens, in any distance calculation. Min-max normalisation keeps the shape of the data within a fixed range, while the z-score is better when outliers make the minimum and maximum unreliable.
Exam points
- The forms of pre-processing are cleaning, integration, transformation, reduction and discretisation.
- Filling a missing value with the class mean is usually better than using the overall mean.
- Binning smooths noisy data by bin means, medians or boundaries; equal-depth bins hold the same number of values.
- Correlation analysis detects redundant numeric attributes; the chi-square test does so for categorical ones.
- Min-max normalisation maps values to a new range; z-score uses the mean and standard deviation.
- Decimal scaling divides by a power of 10 so the largest absolute value is below 1.
Independent study notes for learning purposes. Not affiliated with or endorsed by any university, publisher or course provider. Product and company names are trademarks of their respective owners.