Data Preparation for

Visualization
Dr. Gibrail Islam
Preparing and understanding data
Cleaning and Preparing (Tabular)
Data
Chapter 3: Data Preprocessing
• Data Preprocessing: An Overview

• Data Quality

• Major Tasks in Data Preprocessing

• Data Cleaning

• Data Integration

• Data Reduction

• Data Transformation and Data Discretization

• Summary

4
4
Data Quality: Why Preprocess the Data?

• Measures for data quality: A multidimensional view

• Accuracy: correct or wrong, accurate or not
• Completeness: not recorded, unavailable, …
• Consistency: some modified but some not, dangling, …
• Timeliness: timely update?
• Believability: how trustable the data are correct?
• Interpretability: how easily the data can be understood?

5
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 reduction
• Dimensionality reduction
• Numerosity reduction
• Data compression
• Data transformation and data discretization
• Normalization: the process of organizing and restructuring data within a
database to reduce redundancy and improve data integrity
• Concept hierarchy generation
6
Chapter 3: Data Preprocessing
• Data Preprocessing: An Overview

• Data Quality

• Major Tasks in Data Preprocessing

• Data Cleaning

• Data Integration

• Data Reduction

• Data Transformation and Data Discretization

• Summary

7
7
Tabular data Columns or fields or variables

Variable 1 Variable 2 Variable 3 Variable 4 Variable 5

A 32 0.8318 North True
B 35 0.6632 East True
A 29 0.0182 East True
Rows or A 48 0.7465 South False
records or B 33 0.9624 West True
observations B 19 0.9773 South False
A 27 0.6182 East False
B 36 0.1638 West False
A 55 0.3462 North False
Data Cleaning
• Data in the Real World Is Dirty: Lots of potentially incorrect data, e.g., instrument faulty, human or computer
error, transmission error
• incomplete: lacking attribute values, lacking certain attributes of interest, or containing only aggregate data
• e.g., Occupation=“ ” (missing data)
• noisy: containing noise, errors, or outliers
• e.g., Salary=“−10” (an error)
• inconsistent: containing discrepancies in codes or names, e.g.,
• Age=“42”, Birthday=“03/07/2010”
• Was rating “1, 2, 3”, now rating “A, B, C”
• discrepancy between duplicate records
• Intentional (e.g., disguised missing data)
• Jan. 1 as everyone’s birthday?

9
 Incomplete (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
10
 Data are in a full table

Optimal data format (no systematic gaps like

missing rows or columns)

Just data in table

(no subtotals in the middle or
stats at bottom)

Headers in first row

(one header per column, no
merged fields)

Columns are specific

(no complex values within a
single column)

Values are consistent

(all values in column have
common data type, format)
 How to Handle Missing
Data?
• Ignore the tuple: usually done when class label is missing (when
doing classification)—not effective when the % 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/label?!
• 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 tree 12
 Noisy Data
• Noise: random error or variance in a measured variable
• Incorrect attribute values may be due to
• faulty data collection instruments
• data entry problems
• data transmission problems
• technology limitation
• inconsistency in naming convention
• Other data problems which require data cleaning
• duplicate records
• incomplete data
• inconsistent data

13
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)

14
Remove empty and non-data rows
Fill incomplete data values

Original
data

Blanks to
be filled

Extend Office, “How to fill blank cells with value above / bel
ow / left / right in Excel?”
Remove subtotal rows
 Data Cleaning VS Data Wrangling

• Data cleaning focuses on fixing errors

and inconsistencies in raw data, while
data wrangling is the full process of
transforming raw data into structured
format.

CS 366 – Data Visualization 18

Data cleaning
● Process of finding and correcting inaccurate or unstandardized data
points
● Goal is to make data “machine readable” - ready for a computer to
analyze or visualize
Cleaning
● Remove stray punctuation, spaces, test records, spelling errors, other
issues that impact the data
● Examples:
○ Zygon. → change to Zygon

○ (if using title case) Zoological journal of the Linnean Society → change to
Zoological Journal of the Linnean Society

○ (for some programs) 25% → change to .25

Standardizing
● Setting standards for how data appears
● Examples:
○ Names: “Firstname Lastname” vs “Lastname, Firstname” vs separate columns
○ Dates: “May 1, 2020” vs “1 May 2020” vs “2020-05-01”
○ Missing values: blanks vs “N/A” vs -99
Make sure data
types are
consistent
 Is this data clean enough?
Customer
Name Age Email Purchase_Amount Join_Date Country
_ID

001 John Doe 28 [Link]@[Link] 150.75 2022-03-15 USA

002 Alice alice123@domain 200 2022-03-20 U.S.A

003 Bob Smith 45 [Link]@[Link] -50 2022/03/18 Canada

004 maria_jones 32 [Link]@[Link] 300.40 15-03-2022 UnitedKingdom

005 NULL 29 NULL 175.00 2022-03-19 UK

006 Kate 22 kate@[Link] Not Available 2022-03-21 USA

007 Mike O’Neil 120 mike.o’neil@[Link] 220.50 2022-03-22 USA

Data 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)
24
Chapter 3: Data Preprocessing
• Data Preprocessing: An Overview

• Data Quality

• Major Tasks in Data Preprocessing

• Data Cleaning

• Data Integration

• Data Reduction

• Data Transformation and Data Discretization

• Summary

25
25
 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: occurs when different records in one or more datasets refer to the
same real-world entity, but they are represented in different ways
• Identify real world entities from multiple data sources, e.g., Bill Clinton = William Clinton
• Techniques to Solve
1. Exact Matching (e.g., same ID numbers)
2. Fuzzy String Matching (edit distance, soundex for names)
3. Attribute Similarity (compare multiple fields: name + address + date of birth)
4. Probabilistic Models (calculate probability that two records are same entity)
5. Machine Learning Approaches (classify pairs as “same entity” or not)

• 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
26
26
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 27
27
Correlation Analysis (Nominal Data)

• Χ2 (chi-square) test
2
(Observed  Expected )
 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

28
An example:
So every expected cell is 25. The expected table is: Variable A Variable B
Count
(Gender) (Gender Code)
M F
Male 25 25
Male M 50
Female 25 25 Female F 50

Χ2 (chi-square) test = ?

Χ2 (chi-square) test = 100 Contingency table

M F Row total
Note: A contingency table is a statistical tool Male 50 0 50
presented as a matrix that displays the relationship Female 0 50 50
between two or more categorical variables by Col total 50 50 100
showing the frequency distribution of their
combinations. Also known as a crosstab or two-
way frequency table
 Chi-Square Calculation: An Example

Play Not play Sum

chess chess (row)
Like science fiction 250(90) 200(360) 450

Not like science 50(210) 1000(840) 1050

fiction
Sum(col.) 300 1200 1500

• Χ2 (chi-square) calculation (numbers in parenthesis are expected

counts calculated based on the data distribution in the two
categories)
(250  90) 2 (50  210) 2 (200  360) 2 (1000  840) 2
 
2
   507.93
90 210 360 840

• It shows that like_science_fiction and play_chess are correlated in

the group 30
Correlation Analysis (Numeric Data)

• Correlation coefficient (also called Pearson’s product moment

coefficient)

• If r > 0, x and y are positively correlated (x’s values increase as

y’s). The higher, the stronger correlation.
• r = 0: independent; r < 0: negatively correlated
31
Pearson’s product moment coefficient
Hours
Pearson’s r measures the strength Exam
Studied Score (Y)
and direction of the linear (X)
relationship between two 2 50
quantitative variables. 4 60
• It answers: 6 65
👉 Do two variables move together?
👉 How strong is the relationship? 8 80
10 90

Interpretation: Hours studied

and exam score have a very
strong positive linear
relationship.
Another example
Variable A Variable B
Calculate Pearson’s Product Moment (Height in cm) (Height in
Correlation Coefficient (r) inches)
160 63
170 67
Remember Correlation is an 180 71
indication of redundancy. 190 75
Correlation does not always mean
redundancy
• r=1
What Does r = –1 Mean? • redundant → choose one.
Visually Evaluating Correlation

Scatter plots
showing the
similarity from
–1 to 1.

34
Data Structures
Wide data structures State Sector 1990 1991 1992 1993
Alabama Commercial 2.43 2.00 2.10 2.05

Wide data or cross-tab Alabama

Alabama
Electric Power
Industrial
50.28
25.15
54.18
25.36
56.93
28.36
62.82
26.18
(e.g., one column per year) Alabama Residential 3.09 3.02 3.20 3.42
Alabama Transportation 28.13 28.70 29.39 29.51
Good for comparing two or more Alaska Commercial 2.20 2.23 2.51 2.56

measurements of the same entity Alaska

Alaska
Electric Power
Industrial
2.61
15.83
2.46
17.44
2.29
18.87
2.32
18.51
Alaska Residential 1.58 1.60 1.74 1.73
Alaska Transportation 12.09 11.17 10.86 11.03
Arizona Commercial 1.90 1.83 1.78 1.74
Arizona Electric Power 32.52 32.76 35.37 36.58
Arizona Industrial 3.86 3.92 3.93 3.94
Arizona Residential 1.83 1.89 1.75 1.71
Arizona Transportation 22.83 22.36 23.74 25.05
Long data structures State Sector Year Emissions
Alabama Commercial 1990 2.43

Long data, a.k.a. “tidy” or Alabama

Alabama
Commercial
Commercial
1991
1992
2.00
2.10
normalized data (e.g., many rows Alabama Commercial 1993 2.05
per entity) Alabama Electric Power 1990 50.28
Alabama Electric Power 1991 54.18
Good for filtering and subsetting on Alabama Electric Power 1992 56.93

the fly, aggregating across Alabama Electric Power 1993 62.82

Alabama Industrial 1990 25.15
measurements Alabama Industrial 1991 25.36
Alabama Industrial 1992 28.36
R for Data Science: Tidy Data Alabama Industrial 1993 26.18
Alabama Residential 1990 3.09
Alabama Residential 1991 3.02
Alabama Residential 1992 3.20
Alabama Residential 1993 3.42
Is this data tidy? Building
Alspaugh Residence Hall
Building_Type
dorm
Angier Buchanan Duke Gymnasium gym
Baldwin Auditorium auditorium
Bassett Residence Hall dorm
Bell Tower Residence Hall dorm
Bishop's House house
Bivins Building dorm
Blackwell Residence Hall dorm
Branson Hall dorm; academics
Brodie Recreation Center gym
Brown Residence Hall dorm
Carr Building academics
Crowell Building academics
East Duke Building offices
East Residence Hall dorm
Epworth Residence Hall dorm
Responsible Use of Data
 Beware of visualizing non-
comparable absolutes
Important to compare fairly and consider
relative values
(e.g., normalize by population, cost of
living)

Nathan Yau, “How to Spot Visualization Lies” XKCD Heatmap Cartoon

 Don’t distort the message by
limiting the data
Chart shown to John Carter:
a.k.a. cherry picking
data

Nathan Yau, “How to Spot Visualization Lies” When Graphs Are a Matter of Life and De
ath
Divisions can be important
(Simpson’s Paradox) Simpson's paradox is a statistical
phenomenon where a trend observed in multiple groups of data reverses or disappears
when those groups are combined. This can lead to misleading conclusions

Data-to-viz, “The simpson’s paradox”

 Beware of oversimplifying variation
(e.g., creating data bins that
don’t match the variation in the
data)

Excel Charts, “The same data, the same map, different stories”

Nathan Yau, “How to Spot Visualization Lies”

Numbers are placeholders
“Data represents real life. It is a snapshot of the world, in the same way that a
picture catches a small moment in time. Numbers are always placeholders for
something else, a way to capture a point of view — but sometimes this can get lost.
Failing to represent these limitations and nuances and blindly putting numbers in a
chart is like reviewing a movie by analyzing the chemical properties of the cellulose
on which the images were recorded.”

Giorgia Lupi, “Data Humanism, the Revolution will be Visualized”

Data isn’t truth

“A chart doesn’t make something true.

Data doesn’t make something true.
It bends. It shows many things.
So keep your eyes open.”
Nathan Yau, “How to Spot Visualization Lies”
Data preparation tools
● Spreadsheets (e.g., Excel, Google Sheets, OpenOffice Calc)
● OpenRefine
● Tableau Prep Builder
● Scripting languages (e.g., Python, R)