Chapter 2: Data PreprocessingData CleaningMissing DataHow to Handle Missing Data?Noisy DataHow to Handle Noisy Data?Simple Discretization Methods: BinningBinning Methods for Data SmoothingRegressionCluster AnalysisData Cleaning as a ProcessSlide 12Data IntegrationHandling Redundancy in Data IntegrationCorrelation Analysis (Numerical Data)Correlation Analysis (Categorical Data)Chi-Square Calculation: An ExampleData TransformationData NormalizationData Transformation: NormalizationZ-Score (Example)01/14/19Data Mining: Concepts and Techniques 1Chapter 2: Data PreprocessingWhy preprocess the data?Descriptive data summarizationData cleaning Data integration and transformationData reductionDiscretization and concept hierarchy generationSummary01/14/19Data Mining: Concepts and Techniques 2Data 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 integration01/14/19Data Mining: Concepts and Techniques 3Missing 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.01/14/19Data Mining: Concepts and Techniques 4How 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 tree01/14/19Data Mining: Concepts and Techniques 5Noisy 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 data01/14/19Data Mining: Concepts and Techniques 6How to Handle Noisy Data?Binningfirst sort data and partition into (equal-frequency) binsthen one can smooth by bin means, smooth by bin median, smooth by bin boundaries, etc.Regressionsmooth by fitting the data into regression functionsClusteringdetect and remove outliersCombined computer and human inspectiondetect suspicious values and check by human (e.g., deal with possible outliers)01/14/19Data Mining: Concepts and Techniques 7Simple Discretization Methods: 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 tricky01/14/19Data Mining: Concepts and Techniques 8Binning Methods for Data SmoothingSorted data for price (in dollars): 4, 8, 9, 15, 21, 21, 24, 25, 26, 28, 29, 34* Partition into equal-frequency (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, 3401/14/19Data Mining: Concepts and Techniques 9Regressionxyy = x + 1X1Y1Y1’01/14/19Data Mining: Concepts and Techniques 10Cluster Analysis01/14/19Data Mining: Concepts and Techniques 11Data Cleaning as a ProcessData discrepancy detectionUse metadata (e.g., domain, range, dependency, distribution)Check field overloading Check uniqueness rule, consecutive rule and null ruleUse commercial toolsData scrubbing: use simple domain knowledge (e.g., postal code, spell-check) to detect errors and make correctionsData auditing: by analyzing data to discover rules and relationship to detect violators (e.g., correlation and clustering to find outliers)Data migration and integrationData migration tools: allow transformations to be specifiedETL (Extraction/Transformation/Loading) tools: allow users to specify transformations through a graphical user interfaceIntegration of the two processesIterative and interactive (e.g., Potter’s Wheels)01/14/19Data Mining: Concepts and Techniques 12Chapter 2: Data PreprocessingWhy preprocess the data?Data cleaning Data integration and transformationData reductionDiscretization and concept hierarchy generationSummary01/14/19Data Mining: Concepts and Techniques 13Data IntegrationData integration: Combines data from multiple sources into a coherent storeSchema integration: e.g., A.cust-id  B.cust-#Integrate metadata from different sourcesEntity identification problem: Identify real world entities from multiple data sources, e.g., Bill Clinton = William ClintonDetecting and resolving data value conflictsFor the same real world entity, attribute values from different sources are differentPossible reasons: different representations, different scales, e.g., metric vs. British units01/14/19Data Mining: Concepts and Techniques 14Handling Redundancy in Data IntegrationRedundant data occur often when integration of multiple databasesObject identification: The same attribute or object may have different names in different databasesDerivable data: One attribute may be a “derived” attribute in another table, e.g., annual revenueRedundant attributes may be able to be detected by correlation