Data Preprocessing

Data Preprocessing
Dirty Data
Importance of Data Preprocessing
Major tasks in data preprocessing
 Mining Data Descriptive Characteristics
• To better understand data: central tendency, variation and spread
Measuring the dispersion of Data
• The kth percentile of a set of data in numerical order is the
value xi having the property that k percent of the data entries
lie at or below xi. The median is the 50th percentile.
• The first quartile, denoted by Q1, is the 25th percentile; the
third quartile, denoted by Q3, is the 75th percentile.
• The quartiles, including the median, give some indication of
the center, spread, and shape of a distribution.
Normal Distribution Curve
Box Plot Analysis
 Histogram
• Histogram is a graphical method for summarizing the
distribution of a given attribute. histogram for an attribute A
partitions the data distribution of A into disjoint subsets, or
buckets.
• Typically, the width of each bucket is uniform. Each bucket is
represented by a rectangle whose height is equal to the count
or relative frequency of the values at the bucket.
• If A is categoric, such as automobile model or item type, then
one rectangle is drawn for each known value of A, and the
resulting graph is more commonly referred to as a bar chart.
• If A is numeric, the term histogram is preferred.
 Quantile Plot
• A quantile plot is a simple and effective way to represent a
univariate data distribution.
• it displays all of the data for the given attribute with quantile
information.
• Let xi, for i = 1 to N, be the data sorted in increasing order so
that x1 is the smallest observation and xN is the largest.
• Each observation, xi, is paired with a percentage, fi, which
indicates that approximately 100 fi% of the data are below or
equal to the value, xi.
• Quartiles are type of quantiles that divides the dataset into
equal sized four groups i.e min-q1, q1-q2, q2-q3, q3-max.
 Quantile-Quantile Plot
• A quantile-quantile plot, or q-q plot, graphs the quantiles of
one univariate distribution against the corresponding
quantiles of another.
• It is a powerful visualization tool in that it allows the user to
view whether there is a shift in going from one distribution to
another.
• Straight line that represents the case of when, for each given
quantile, the unit price at each branch is the same.
 Scatter Plot
• A scatter plot is one of the most effective graphical methods
for determining if there appears to be a relationship, pattern,
or trend between two numerical attributes.
Missing Data
Handling of Missing Values
Noisy Data
Handling of Noisy Data
 Data Cleaning as a Process
• Data discrepancy detection
– Use metadata (e.g., domain, range, dependency, distribution)
– Check uniqueness rule, consecutive rule and null rule
– A unique rule says that each value of the given attribute must be different
from all other values for that attribute. A consecutive rule says that there can
be no missing values between the lowest and highest values for the attribute,
and that all values must also be unique. A null rule specifies the use of
blanks, question marks, special characters, or other strings that may indicate
the null condition.

– 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
23
 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:
– How can equivalent real-world entities from multiple data sources be
matched up?
• 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
24
24
 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 and covariance analysis
• Careful integration of the data from multiple sources may help
reduce/avoid redundancies and inconsistencies and improve
mining speed and quality

25
25
 Correlation Analysis (Nominal Data)

• Χ2 (chi-square) test
(Observed  Expected ) 2
2  
Expected
• The larger the Χ2 value, the more likely the variables are related
• The cells that contribute the most to the Χ2 value are those
whose actual count is very different from the expected count
• Correlation does not imply causality
– # of hospitals and # of car-theft in a city are correlated
– Both are causally linked to the third variable: population

26
• Suppose that a group of 1,500 people was surveyed. The
gender of each person was noted. Each person was polled as
to whether their preferred type of reading material was fiction
or nonfiction. Thus, we have two attributes, gender and
preferred reading. The observed frequency (or count) of each
possible joint event is summarized in the contingency table.
•
• The expected frequency for the cell (male, fiction) is
 Correlation Analysis (Numeric Data)

• Correlation coefficient (also called Pearson’s product moment

coefficient)

i1 (ai  A)(bi  B) 

n n
(ai bi )  n AB
rA, B   i 1
(n  1) A B (n  1) A B

where n is the number of tuples, A and B are the respective means of A

and B, σA and σB are the respective standard deviation of A and B, and
Σ(aibi) is the sum of the AB cross-product.
• If rA,B > 0, A and B are positively correlated (A’s values increase as
B’s). The higher, the stronger correlation.
• rA,B = 0: independent; rAB < 0: negatively correlated
30
• The PPMC is not able to tell the difference between dependent
variables and independent variables. For example, if you are trying
to find the correlation between a high calorie diet and diabetes,
you might find a high correlation of .8. However, you could also
get the same result with the variables switched around. In other
words, you could say that diabetes causes a high calorie diet.
 Covariance (Numeric Data)
• Covariance is similar to correlation

Correlation coefficient:

where n is the number of tuples, A and B are the respective mean or

expected values of A and B, σA and σB are the respective standard deviation
of A and B.
• Positive covariance: If CovA,B > 0, then A and B both tend to be larger than their
expected values.
• Negative covariance: If CovA,B < 0 then if A is larger than its expected value, B is
likely to be smaller than its expected value.
• Independence: CovA,B = 0
– Some pairs of random variables may have a covariance of 0 but are not independent.
Only under some additional assumptions (e.g., the data follow multivariate normal
distributions) does a covariance of 0 imply independence 33
• covariance tells you that two variables change the same way
while correlation reveals how a change in one variable affects
a change in the other.

Average of ABC= 1.30

Average of XYZ = 3.74
Covariance = 0.532

It means the stocks

move in the same
direction. In other
words, when ABC had a
high return, XYZ also had
a high return.

Covariance can tell how the stocks move together, but to determine the
strength of the relationship, we need to look at their correlation.
 Data Transformation
• A function that maps the entire set of values of a given attribute to a new
set of replacement values s.t. each old value can be identified with one of
the new values
• Methods
– Smoothing: Remove noise from data
– Attribute/feature construction
• New attributes constructed from the given ones
– Aggregation: Summarization, data cube construction
– Normalization: Scaled to fall within a smaller, specified range
• min-max normalization
• z-score normalization
• normalization by decimal scaling
– Discretization: Concept hierarchy climbing
35
 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].
73,600  12,000
(1.0  0)  0  0.716
Then $73,000 is mapped to 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 Where j is the smallest integer such that Max(|ν’|) < 1
v' j
10
36
 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
– Numeric—real numbers, e.g., integer or real numbers
• Discretization: Divide the range of a continuous attribute into intervals
– Interval labels can then be used to replace actual data values
– Reduce data size by discretization
– Supervised vs. unsupervised

37
 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

38
 Concept Hierarchy Generation
• Concept hierarchy organizes concepts (i.e., attribute values) hierarchically
and is usually associated with each dimension in a data warehouse
• Concept hierarchies facilitate drilling and rolling in data warehouses to view
data in multiple granularity
• Concept hierarchy formation: Recursively reduce the data by collecting and
replacing low level concepts (such as numeric values for age) by higher level
concepts (such as youth, adult, or senior)
• Concept hierarchies can be explicitly specified by domain experts and/or data
warehouse designers. Specification of a partial/total ordering of attributes
explicitly at the schema level by users or experts
– street < city < state < country
• Concept hierarchy can be automatically formed for both numeric and
nominal data.
39
 Automatic Concept Hierarchy Generation
• Some hierarchies can be automatically generated based on
the analysis of the number of distinct values per attribute in
the data set
– The attribute with the most distinct values is placed at
the lowest level of the hierarchy
– Exceptions, e.g., weekday, month, quarter, year

country 15 distinct values

province_or_ state 365 distinct values

city 3567 distinct values

street 674,339 distinct values

40