1CS490D:Introduction to Data MiningChris CliftonJanuary 23, 2004Data PreparationCS490D 2Data Preprocessing• Why preprocess the data?• Data cleaning • Data integration and transformation• Data reduction• Discretization and concept hierarchy generation• Summary2CS490D 3Why Data Preprocessing?• Data in the real world is dirty– incomplete: lacking attribute values, lacking certain attributes of interest, or containing only aggregate data• e.g., occupation=“”– noisy: containing errors or outliers• e.g., Salary=“-10”– inconsistent: containing discrepancies in codes or names• e.g., Age=“42” Birthday=“03/07/1997”• e.g., Was rating “1,2,3”, now rating “A, B, C”• e.g., discrepancy between duplicate recordsCS490D 4Why Is Data Dirty?• Incomplete data comes from– n/a data value when collected– different consideration between the time when the data was collected and when it is analyzed.– human/hardware/software problems• Noisy data comes from the process of data– collection– entry– transmission• Inconsistent data comes from– Different data sources– Functional dependency violation3CS490D 5Why Is Data Preprocessing Important?• No quality data, no quality mining results!– Quality decisions must be based on quality data• e.g., duplicate or missing data may cause incorrect or even misleading statistics.– Data warehouse needs consistent integration of quality data• Data extraction, cleaning, and transformation comprises the majority of the work of building a data warehouse. —Bill InmonCS490D 6Multi-Dimensional Measure of Data Quality• A well-accepted multidimensional view:– Accuracy– Completeness– Consistency– Timeliness– Believability– Value added– Interpretability– Accessibility• Broad categories:– intrinsic, contextual, representational, and accessibility.4CS490D 7Major Tasks in Data Preprocessing• Data cleaning– Fill in missing values, smooth noisy data, identify or remove outliers, and resolve inconsistencies• Data integration– Integration of multiple databases, data cubes, or files• Data transformation– Normalization and aggregation• Data reduction– Obtains reduced representation in volume but produces the same or similar analytical results• Data discretization– Part of data reduction but with particular importance, especially for numerical dataCS490D 9Data Preprocessing• Why preprocess the data?• Data cleaning• Data integration and transformation• Data reduction• Discretization and concept hierarchy generation• Summary5CS490D 10Data Cleaning• Importance– “Data cleaning is one of the three biggest problems in data warehousing”—Ralph Kimball– “Data cleaning is the number one problem in data warehousing”—DCI survey• Data cleaning tasks– Fill in missing values– Identify outliers and smooth out noisy data – Correct inconsistent data– Resolve redundancy caused by data integrationCS490D 11Missing Data• Data is not always available– E.g., many tuples have no recorded value for several attributes, such as customer income in sales data• Missing data may be due to – equipment malfunction– inconsistent with other recorded data and thus deleted– data not entered due to misunderstanding– certain data may not be considered important at the time of entry– not register history or changes of the data• Missing data may need to be inferred.6CS490D 12How to Handle Missing Data?• Ignore the tuple: usually done when class label is missing (assuming the tasks in classification—not effective when the percentage of missing values per attribute varies considerably.• Fill in the missing value manually: tedious + infeasible?• Fill in it automatically with– a global constant : e.g., “unknown”, a new class?! – the attribute mean– the attribute mean for all samples belonging to the same class: smarter– the most probable value: inference-based such as Bayesian formula or decision treeCS490D 13Noisy Data• Noise: random error or variance in a measured variable• Incorrect attribute values may due to– faulty data collection instruments– data entry problems– data transmission problems– technology limitation– inconsistency in naming convention • Other data problems which requires data cleaning– duplicate records– incomplete data– inconsistent data7CS490D 14How to Handle Noisy Data?• Binning method:– first sort data and partition into (equi-depth) bins– then one can smooth by bin means, smooth by bin median, smooth by bin boundaries, etc.• Clustering– detect and remove outliers• Combined computer and human inspection– detect suspicious values and check by human (e.g., deal with possible outliers)• Regression– smooth by fitting the data into regression functionsCS490D 15Simple DiscretizationMethods: Binning• Equal-width (distance) partitioning:– Divides the range into N intervals of equal size: uniform grid– if A and B are the lowest and highest values of the attribute, the width of intervals will be: W = (B –A)/N.– The most straightforward, but outliers may dominate presentation– Skewed data is not handled well.• Equal-depth (frequency) partitioning:– Divides the range into N intervals, each containing approximately same number of samples– Good data scaling– Managing categorical attributes can be tricky.8CS490D 16Binning Methods for Data Smoothing• Sorted data (e.g., by price)– 4, 8, 9, 15, 21, 21, 24, 25, 26, 28, 29, 34• Partition into (equi-depth) bins:– Bin 1: 4, 8, 9, 15– Bin 2: 21, 21, 24, 25– Bin 3: 26, 28, 29, 34• Smoothing by bin means:– Bin 1: 9, 9, 9, 9– Bin 2: 23, 23, 23, 23– Bin 3: 29, 29, 29, 29• Smoothing by bin boundaries:– Bin 1: 4, 4, 4, 15– Bin 2: 21, 21, 25, 25– Bin 3: 26, 26, 26, 34CS490D 17Cluster Analysis9CS490D 18Regressionxyy = x + 1X1Y1Y1’CS490D 19Data Preprocessing• Why preprocess the data?• Data cleaning • Data integration and transformation• Data reduction• Discretization and concept hierarchy generation• Summary10CS490D 20Data Integration• Data integration: – combines data from multiple sources into a coherent store• Schema integration– integrate metadata from different sources– Entity identification problem: identify real world entities frommultiple data sources, e.g., A.cust-id ≡ B.cust-#• Detecting and resolving data value conflicts– for the same real world entity, attribute