Trịnh Tấn Đạt

Khoa CNTT – Đại Học Sài Gòn

Email: trinhtandat@[Link]
Website: [Link]

1
Outline
 Why preprocess the data?

 Descriptive data summarization

 Data cleaning

 Data integration and transformation

 Data reduction

 Discretization and concept hierarchy generation

 Summary

2
Why Data Preprocessing?
 Data in the real world is dirty
 incomplete: lacking attribute values, lacking certain attributes of interest, …
 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 records

3
Why Is Data Dirty?
 Incomplete data may come from
 “Not applicable” data value when collected
 Different considerations between the time when the data was collected and when it is
analyzed.
 Human/hardware/software problems
 Noisy data (incorrect values) may come from
 Faulty data collection instruments
 Human or computer error at data entry
 Errors in data transmission
 Inconsistent data may come from
 Different data sources
 Functional dependency violation (e.g., modify some linked data)
 Duplicate records also need data cleaning
4
Why 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

5
Multi-Dimensional Measure of Data Quality

 A well-accepted multidimensional view:

 Accuracy
 Completeness
 Consistency
 Timeliness
 Believability
 Value added
 Interpretability
 Accessibility

6
Data type
 Numeric: The most used data type, and the stored content is numeric
 Characters and strings: strings are arrays of characters
 Boolean: for binary data with true and false values
 Time series data: including time-or sequential-related properties
 Sequential data: data itself has sequential relationship
 Time series data: each data will be subject to change with time

7
Data type
 Spatial data: for data including special related attributes
 For example, Google Map, Integrated Circuit Design Layout, Wafer Exposure
Layout, Global Positioning System (GPS), etc.
 Text data: for paragraph description, including patent reports, diagnostic
reports, etc.
 Structured data: library bibliographic data, credit card data
 Semi-structured data: email, extensible markup language (XML)
 Unstructured data: social media data of messages in Facebook
 Multimedia data: Including data of pictures, audio, video, etc. in media with
mass data volumes as compared to other types of data that need data
compression for data storage
8
 “A proxy attribute is a variable that is used to represent or stand in for
another variable or attribute that is difficult to measure directly. A
proxy attribute is typically used in situations where it is not possible or
practical to measure the actual attribute of interest. For example, in a

Data scale study of income, the amount of money a person earns per year may be
difficult to determine accurately. In such a case, a proxy attribute, such
as education level or occupation, may be used instead.” ChatGPT

 Each variable of data has its corresponding attribute and scale to quantify and
measure its level
 natural quantitative scale
 qualitative scale
 When one variable is hard to find the corresponding attribute, proxy attribute
can be used instead as a measurement
 Common scales: nominal scale, categorical scale, ordinal scale, interval scale,
ratio scale, and absolute scale

9
Six common scales
 nominal scale: only used as codes, where the values has no meaning for
mathematical operations
 categorical scale: according to its characteristics, and each category is marked
with a numeric code to indicate the category to which it belongs
 ordinal scale: to express the ranking and ordering of the data without
establishing the degree of variation between them
 interval scale: also called distance scale, can describes numerical differences
between different numbers in a meaningful way
 ratio scale: different numbers can be compared to each other by ratio
 absolute scale: the numbers measured have absolute meaning

10
Data inspection
 Goal: Inspects the obtained data in different view points to find the errors in
advance and then correct or remove some of them after discussion with domain
experts
 Data are categorized into quantitative and qualitative aspects
 Quantitative data
 Data inspection: number of samples, number of variables or features, and different data
values
 Sample sizes: too small samples may affect the results, while too much samples may
affect statistical significance
 Variable sizes: too much may cause much time for computation
 Qualitative data
 Inspect centralized trends (mean, median, etc.) and variability
 Inspect data omissions, data noise, etc. in different graphs

11
Data discovery and visualization
 Statistical table: a table is made according to specific rules after organized the data
 Statistical chart: graphical representation of various characteristics of statistical data
in different graphic styles
 Data Type:
 Frequency: histogram, bar plot, pie chart
 Distribution: box plot, Q-Q plot
 Trends: trend chart
 Relationships: scatter plot
 Different data categories have different statistical charts
 Categorical data: Bar chart applicable
 Continuous data: histogram and pie chart applicable

12
Major 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 data

13
Forms of Data Preprocessing

14
Descriptive data summarization

15
Mining Data Descriptive Characteristics

 Motivation
 To better understand the data: central tendency, variation and spread
 Data dispersion characteristics
 median, max, min, quantiles, outliers, variance, etc.

16
Measuring the Central Tendency

=
1 n x
 Mean (algebraic measure) (sample vs. population): x =  xi
n n i =1 N
 Weighted arithmetic mean: w x i i
x= i =1
n

 Median: A holistic measure w

i =1
i

 Middle value if odd number of values, or average of the middle two values
otherwise
 Mode
 Value that occurs most frequently in the data
 Unimodal, bimodal, trimodal
 Empirical formula: mean − mode = 3  (mean − median)

17
Symmetric vs. Skewed Data

 Median, mean and mode of symmetric,

positively and negatively skewed data

Data Mining: Concepts and Techniques 18

Four moments of distribution: Mean, Variance, Skewness, and
Kurtosis

19
20
Measuring the Dispersion of Data

 Quartiles, outliers and boxplots

 Quartiles: Q1 (25th percentile), Q3 (75th percentile)
 Inter-quartile range: IQR = Q3 – Q1
 Five number summary: min, Q1, M, Q3, max
 Boxplot: ends of the box are the quartiles, median is marked, whiskers, and plot outlier
individually
 Outlier: usually, a value higher/lower than 1.5 x IQR
 Variance and standard deviation (sample: s, population: σ)
 Variance: (algebraic, scalable computation)
1 n 1 n 2 1 n 2
 [ xi − ( xi ) ]
n n
1 1
s = ( xi − x ) =  =  −  =  xi −  2
2 2 2 2 2
( x )
n − 1 i =1 n − 1 i =1 n i =1 N i =1
i
N i =1

 Standard deviation s (or σ) is the square root of variance s2 (or σ2)

21
Example

22
Properties of Normal Distribution Curve
 The normal (distribution) curve
 From μ–σ to μ+σ: contains about 68% of the measurements (μ:
mean, σ: standard deviation)
 From μ–2σ to μ+2σ: contains about 95% of it
 From μ–3σ to μ+3σ: contains about 99.7% of it

23
Boxplot Analysis

 Five-number summary of a distribution:

Minimum, Q1, M, Q3, Maximum
 Boxplot
 Data is represented with a box
 The ends of the box are at the first and third quartiles, i.e., the
height of the box is IRQ
 The median is marked by a line within the box
 Whiskers: two lines outside the box extend to Minimum and
Maximum

24
Visualization of Data Dispersion: Boxplot Analysis

25
Line chart
 Line chart: displays changes as a series of data points connected by straight
line segments, where data points are ordered by their x-axis value with y-axis
label to compare data trends among variables

26
Histogram Analysis

 Graph displays of basic statistical class descriptions

 Frequency histograms
 A univariate graphical method

 Consists of a set of rectangles that reflect the counts or frequencies of the classes
present in the given data

27
28
Cameraman image with the histogram

29
 Low contrast cameraman image with
High contrast cameraman image with the histogram
the histogram

Bright cameraman image with the histogram Dark cameraman image with the histogram
30
Quantile-Quantile (Q-Q) Plot
 Graphs the quantiles of one univariate distribution against the
corresponding quantiles of another
 Allows the user to view whether there is a shift in going from one
distribution to another

31
Scatter plot
 Displays relationships between variables and usually uses dots to represent values
for two different numeric variables, each dot on the x-axis and y-axis indicates
values for an individual data point

32
Loess Curve
 Adds a smooth curve to a scatter plot in order to provide better
perception of the pattern of dependence
 Loess curve is fitted by setting two parameters: a smoothing
parameter, and the degree of the polynomials that are fitted by
the regression

33
Positively and Negatively Correlated Data

34
Not Correlated Data

35
Data preprocessing

36
Data 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 integration

37
Missing 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.

38
How 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

 interpolation

 the most probable value: inference-based such as Bayesian formula or decision tree

39
Noisy 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 data

40
How 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)

41
Simple 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 tricky

42
Binning 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, 34

43
Regression

Y1

Y1’ y=x+1

X1 x

44
Cluster Analysis

45
Data Integration
 Data integration:
 Combines data from multiple sources into a coherent store
 Schema integration: e.g., [Link]-id  [Link]-#
 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 units

46
Handling 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 analysis
 Careful integration of the data from multiple sources may help reduce/avoid
redundancies and inconsistencies and improve mining speed and quality

47
Data Transformation
 Goal: Convert the data into a data format suitable for data mining
approach or enrich the content of the data to convert the original data or
re-encode to increase the value of the data
 Smoothing: remove noise from data
 Aggregation: summarization, data cube construction
 Generalization: concept hierarchy climbing
 Normalization: scaled to fall within a small, specified range
 min-max normalization
 z-score normalization
 normalization by decimal scaling
 Attribute/feature construction
 New attributes constructed from the given ones

48
Data Transformation: Normalization
 Min-max normalization: to [new_minA, new_maxA]
v − minA
v' = (new _ maxA − new _ minA) + new _ minA
maxA − minA
 Ex. Let income range $12,000 to $98,000 normalized to [0.0, 1.0]. Then $73,000
73,600 − 12,000
is mapped to (1.0 − 0) + 0 = 0.716
98,000 − 12,000
 Z-score normalization (μ: mean, σ: standard deviation):
v − A
v' =
 A

73,600 − 54,000
 Ex. Let μ = 54,000, σ = 16,000. Then = 1.225
16,000
 Normalization by decimal scaling
v
v' = j Where j is the smallest integer such that Max(|ν’|) < 1
10
49
Data Reduction Strategies

 Why data reduction?

 A database/data warehouse may store terabytes of data
 Complex data analysis/mining may take a very long time to run on the complete data
set
 Data reduction
 Obtain a reduced representation of the data set that is much smaller in volume but yet
produce the same (or almost the same) analytical results
 Data reduction strategies
 Data cube aggregation:
 Dimensionality reduction — e.g., remove unimportant attributes
 Data Compression
 Numerosity reduction — e.g., fit data into models
 Discretization

50
Data Reduction
 Data value itself is different based on data resolution and the value can be
enhanced through the process of data aggregation
 The data collection stage should collect all the recordable variables as much as
possible, and then aggregate the data to obtain a more compact data set with
the same information as the original data
 Benefits include:
 Enhance data quality
 Decrease time for data mining
 Increase data value and readability
 Decrease costs for data storage
51
Data Resolution
 Usually used for dealing with problems of classification and prediction
 Methods:
 Use feature selectionmethods to reduce the dimensionality of data and remove
uncorrelated attributes withthe information of response variables
 Use PCA method to do the linear transformations of variables to reduce the
dimensionality of data under the chosen principle components withoutthe
information of response variable that can find the best linear combination for data
interpretation

52
Attribute Subset Selection
 Feature selection (i.e., attribute subset selection):
 Select a minimum set of features such that the probability distribution of
different classes given the values for those features is as close as possible to
the original distribution given the values of all features
 reduce # of patterns in the patterns, easier to understand
 Heuristic methods (due to exponential # of choices):
 Step-wise forward selection
 Step-wise backward elimination
 Combining forward selection and backward elimination
 Decision-tree induction

53
Example of Decision Tree Induction

Initial attribute set:

{A1, A2, A3, A4, A5, A6}

A4 ?

A1? A6?

Class 1 Class 2 Class 1 Class 2

> Reduced attribute set: {A1, A4, A6}

54
Heuristic Feature Selection Methods
 There are 2d possible sub-features of d features
 Several heuristic feature selection methods:
 Best single features under the feature independence assumption: choose by
significance tests
 Best step-wise feature selection:
 The best single-feature is picked first
 Then next best feature condition to the first, ...
 Step-wise feature elimination:
 Repeatedly eliminate the worst feature

 Best combined feature selection and elimination

 Optimal branch and bound:
 Use feature elimination and backtracking

55
Data resolution
 To obtain higher-level knowledge, such as special patterns or trends, for
discretizing and generalizing data of lower-level knowledge in original data
 Discretization of continuous data for various blocks
 Discretization of data by using hierarchical layers

56
Data Compression
 String compression
 There are extensive theories and well-tuned algorithms
 Typically lossless
 But only limited manipulation is possible without expansion
 Audio/video compression
 Typically lossy compression, with progressive refinement
 Sometimes small fragments of signal can be reconstructed without
reconstructing the whole
 Time sequence is not audio
 Typically short and vary slowly with time

57
Data Compression

Original Data Compressed

Data
lossless

Original Data
Approximated

58
Numerosity Reduction
 Reduce data volume by choosing alternative, smaller forms of data
representation
 Parametric methods
 Assume the data fits some model, estimate model parameters, store only
the parameters, and discard the data (except possible outliers)
 Example: regression model
 Non-parametric methods
 Do not assume models
 Major families: histograms, clustering, sampling

59
Sampling: with or without Replacement

Raw Data
60
Discretization
 Three types of attributes:

 Nominal — values from an unordered set, e.g., color, profession

 Ordinal — values from an ordered set, e.g., military or academic rank

 Continuous — real numbers, e.g., integer or real numbers

 Discretization:

 Divide the range of a continuous attribute into intervals

 Some classification algorithms only accept categorical attributes.

 Reduce data size by discretization

 Prepare for further analysis

61
Discretization process

 According to different aspects

 Supervised and unsupervised
 Dynamic and static
 Global and local
 Splitting and merging
 Direct and incremental 62
Data partition
 Data partition: training data, testing data, validation data
 Different partition methods have different mining and should present original
information as possible
 Method 1: 70% for training, 10% for validation and 20%for testing
 Method 2: k fold cross-validation

63
Data partition
 Training data: use the data to build the model
 Validation data: examples used to tune the hyperparameters of a model (a part of
training data).
 Testing data: evaluate the model for robustness
 K fold cross-validation: a resampling procedure that splits the data into ksubsets, to fit
the same statistical method k times using different subsets of the data

64
Model evaluation for classification
 Two aspects to evaluate the results of classification model
 Using the results from testing data set to evaluate the better model
 Finding the best model from domain experts
 Classification Accuracy
 Calculate accuracy or error rate by classification results

 It assumes equal cost for all classes

 Misleading in unbalanced datasets
 It doesn’t differentiate between different types of errors
 Examples:

65
Model evaluation for classification
 A binary classifier predicts all data instances of a
test dataset as either positive or negative
 This classification (prediction) produces four
outcomes –true positive, true negative, false
positive and false negative
 Four outcomes of classification:
 True positive (TP): correct positive prediction
 False positive (FP): incorrect positive prediction
 True negative (TN): correct negative prediction
 False negative (FN): incorrect negative prediction
 Confusion matrix: an error matrix. It is presented as
a table in which the predicted class is compared
with the actual class.

66
Model evaluation for classification

67
Model evaluation for classification

68
Receiver-Operator Curve (ROC Curve)
 ROC curve: visualize the performance of a
binary classifier between FP rate and TP rate
 TP rate: the larger the better
 FP rate: the smaller the better
 Accuracy =1－FP rate: FP rate increases
while accuracy decreases
 FP rate changes depend on the setting of
thresholds
 The larger the area, the better the model

69
Area Under the Curve (AUC)

AUC size is directly connected to model performance.

Models that perform better will have higher AUC values. A
random model will have an AUC of 0.5, while a perfect
classifier would have an AUC of 1.
70
Summary

 Data preparation or preprocessing is a big issue for both data warehousing

and data mining
 Discriptive data summarization is need for quality data preprocessing
 Data preparation includes
 Data cleaning and data integration
 Data reduction and feature selection
 Discretization
 A lot a methods have been developed but data preprocessing still an active
area of research

71