02 – Data Exploration

IS5740 : Management Support and Business Intelligence Systems

Juhee Kwon
[email protected]
College of Business, City U of Hong Kong
BA/BI Analytics Process Model
“Data are to be seen as an input or basic resource needing further processing before actually being of use.”

• The analytics process model defines the subsequent steps from data to knowledge as below:

Data Knowledge

2
 Key Steps of the Analytics Process Model

1. Pre-processing: (about 70% of the total 2. Analytics

effort!) ̶ Choosing descriptive or predictive analytics models
to find patterns of interest
̶ Learning business domains to identify business
• Summarization, classification, regression,
problems
association, clustering, etc.
̶ Identifying a target data set  data selection
3. Post-processing
̶ Cleaning the data
̶ Result evaluation and knowledge presentation
̶ Transforming the data • Visualization, transformation, removing
• Find useful features and variable selection. redundant patterns…
̶ Use of discovered knowledge

3
The analytics processes convert Data to Information,
Knowledge, and Wisdom.
From Data to Information, Knowledge, and Wisdom

• By combining an analyst’s business and

Wisdom offers deep and
analytics knowledge and experience
Wisdom Applied
•e.g., how are the results interpreted and
accurate business strategies
and insights
applied for areal-world business environment?

•By descriptive & predictive analytics Knowledge answers the

Knowledge Context •e.g., which factors have the largest impact questions of why and how.
on housing price?

• By data exploration Information answers the

Information Meaning •e.g., the relationship between price and questions a who, when,
condition, mean, max, min, etc. what, or where.

•By searching data sources - Raw and

Data are unorganized facts
Data unorganized data
Raw required to be processed to
•e.g., number of bathrooms, number of make them meaningful.
bedrooms, etc.

5
 Wisdom

Data as Raw Materials Knowledge

Information

Data
 Structured vs. Unstructured Data
• There are primarily two different categories of data, which exist in any organization.
̶ Structured vs. Unstructured data

• Structured data consists • Unstructured data is simply

of clearly defined data everything except the

that are easy to search. structured one.

‒ Data constructed by ‒ No specific form (e.g.,

rows and columns text/audio/video files,

(e.g., Spreadsheet) social media posts, and

e-mails)

7
Structured vs. Unstructured Data in Business Data

• 80% of business data is

unstructured

8
Structured Data

• Structured data have the form of letters and numbers that fit nicely into the columns
(attributes or field) and rows (observations).
Example – Housing Column (Variable) description
Column Name Type Housing Data
bathrooms Interval Number of bathrooms
bedrooms Interval Number of bedrooms
condition Ordinal Overall condition of the house
floors Interval Number of floors
grade Interval Overall grade of the house
id Nominal House ID
month Interval Month of Sale
price Interval Sale price
price_gte_500K Binary Whether sale price is greater than or equal to $500,000
sqft_above Interval Square footage of the house apart from basement
sqft_basement Interval Square footage of the basement
sqft_living Interval Square footage of the house
sqft_living15 Interval Square footage of the house (renovation)
sqft_lot Interval Square footage of the total area of land or property in square feet
sqft_lot15 Interval Square footage of the lot (renovation)
view Interval Number of times the house is viewed
waterfront Binary Whether the house is located at waterfront
year Interval Year of Sale
yr_built Interval Year built
yr_renovated Interval Year renovated
zipcode Norminal Zip Code
10
Types of Variables (Columns)

Variable Type Data Type Description

Binary Contains two discrete values (for example, PURCHASE: Yes, No).

Categorical Contains a discrete set of values (text data) that do not have a
(Class) Nominal Text, Character logical ordering (for example, Marital status: Single, Married,
Variables Divorced, and Widow).
Contains a discrete set of values that do have a logical ordering (for
Ordinal example, letter grade: A, B, C, D, and F, House condition: 1, 2, 3, 4,
and 5).
Continuous Contains values that vary across a continuous range (for example,
Interval Numeric
Variables grade 50,55, 60, 63=2,…98, 100).

11
Cross-sectional vs longitudinal data by Time factor (1)
• Cross-sectional data include many subjects at a single • Longitudinal data include the same subjects over time – each
point in time. subject at multiple points in time.

12
 Cross-sectional vs Longitudinal Data (2) : Income data
• Cross-sectional data have a type of data from many different individuals at a single time point.
• Longitudinal data have multiple observations from one project over time.

s s
Group ID Year Income Age Sex
1 2019 1500 27
2 2019 2100 41
3 2019 3000 43
1 2020 1700 28
2 2020 2100 42
3 2020 2900 44
1 2021 1700 29
2 2021 2200 43
Group ID Year Income Age Sex 3 2021 3100 45
1 2019 1500 27
2 2019 2100 41
3 2019 3000 43
13
Example

A. Individuals’ salaries in 2017 B. Individuals’ salary changes over years

ID company Year job degree salary_more_then_100k ID company Year job degree salary_more_then_100k

100001 google 2017 sales executive bachelors 0 100001 google 2017 sales executive bachelors 0

100002 google 2017 sales executive masters 0 100001 google 2018 sales executive bachelors 0

100003 google 2017 business manager bachelors 1 100001 facebook 2019 sales executive bachelors 1

100004 google 2017 business manager masters 1 100001 facebook 2020 sales executive bachelors 1

100005 google 2017 computer programmer bachelors 0 100007 abc pharma 2017 sales executive bachelors 0

100006 google 2017 computer programmer masters 1 100007 abc pharma 2018 sales executive masters 1

100007 abc pharma 2017 sales executive masters 0 100007 abc pharma 2019 sales executive masters 1

100008 abc pharma 2017 computer programmer bachelors 0 100007 abc pharma 2020 sales executive masters 1

100009 abc pharma 2017 business manager bachelors 0 100003 google 2017 sales executive masters 1

100010 abc pharma 2017 business manager masters 1 100003 google 2018 sales executive masters 1

100011 facebook 2017 sales executive bachelors 1 100003 google 2019 sales executive masters 1

100012 facebook 2017 sales executive masters 1 100003 google 2020 sales executive masters 1

14
 Wisdom

Extracting Information from data by

Data Exploration Knowledge

Information

Data
Data Exploration

• Data exploration aims to discover outliers, basic patterns, and relationships between
variables.

• Data Exploration has two methods.

̶ Statistic exploration
• Quantitatively understand specific features of a dataset, such as basic
statistics, distribution, dispersion, and correlations.

̶ Graphic exploration
• Graphically understand trends, outliers, and patterns in data via visual
elements like charts and graphs.

16
Statistic exploration

• Statistic exploration quantitatively describes each variable’s mean median, min, max, skewness,
and kurtosis.

17
 Measures of Central Tendency: Mean and Median

• What is the “Center” of a variable’s values?

̶ Mean N (Ex) 1, 3, 5, 7, 9, 10, 11

x i
 i 1
Mean=
1 + 3 + 5 + 7 + 9 + 10 + 11
= 6.57
N 7
Median = 7
• Affected by unusually large or small observations (outliers) Standard deviation = 3.74

̶ Median (Ex) 1, 3, 5, 7, 9, 10, 11, 100

Mean= 18.25
• It is a middle value when data are ordered from smallest to
Median = 7 or 9
largest.
Standard deviation = 33.21

• Not affected by outliers

18
Measures of Dispersion and Distribution Shape
• Dispersion - Standard Deviation σ(𝑥𝑖 − 𝑥)ҧ 2
𝜎=
𝑁
̶ Square root of variance Variance

̶ A low standard deviation indicates that the values tend to be close to the mean of the dataset, while a
high standard deviation indicates that the values are spread out over a wider range.

• Distribution Shape – Skewness and Kurtosis

̶ Skewness measures the symmetry in a distribution of a variable’s values. An asymmetric distribution is
said to be skewed.

̶ Kurtosis identifies how many extreme values the tails of a given distribution contain.

• Kurtosis measures whether the distribution’s tail is thin or flatter.

19
 Measures of Distribution Shape (1) - Skewness

• Skewness
̶ If a variable’s skewness is
zero, its distribution is
perfectly symmetric.
̶ If a variable’s skewness is
less than -1 or greater
than 1, its distribution is
highly positively or
negatively skewed.
-1.0< skewness <-0.5 -0.5< skewness <0.5 0.5 < skewness <1.0

20
Measures of Distribution Shape (2) - Kurtosis
• Kurtosis
̶ if a variable’s kurtosis is greater than + 3.0 (positive), it has a thin tail.
̶ If a variable’s kurtosis is less than 3.0 (negative), it has a flat tail.

(Kurtosis >3)

(Kurtosis <3)

(Kurtosis =3)

21
Covariance vs. Correlation
• What is covariance?
̶ Covariance indicates the extent to which two variables increase or decrease with each other.
• What is correlation?
̶ Correlation tells us the strength and the direction of the relationship between two variables.
̶ (e.g.) the relationship between height and weight is positive, or the relationship between outside
temperature and heating bills is negative.

COMPARISON COVARIANCE CORRELATION

Covariance is a measure indicating the Correlation is a statistical measure that indicates

Meaning
extent to which two variables change. how strongly two variables are related.

Values Lie between -∞ and +∞ Lie between -1 and +1

ത 𝑖 − 𝑌)
ത ത 𝑖 − 𝑌)
σ(𝑋𝑖 − 𝑋)(𝑌 ത
σ(𝑋𝑖 − 𝑋)(𝑌
Formula ത 2 σ(𝑌𝑖 − 𝑌)
ത 2
𝑁−1 σ(𝑋𝑖 − 𝑋)

22
Correlation
• Correlation analysis is an important tool for variable
reduction.
̶ A high correlation between two input variables means
that one of these variables can be eliminated from our
analysis.
• If sqft_above and sqft_living are highly correlated, we
can eliminate one variable.

23
Graphic Exploration
• Plots for the distribution of a variable’s values
‒ Histogram
‒ Box plot
• Plots for the configuration of a categorical/numeric variable’s values
̶ Bar Chart (categorical & numeric)
̶ Pie Chart (categorical)

• Plots for the trend of a variable’s values

‒ Line Graph

• Plots for the correlation between two variables

‒ Scatterplot
‒ Matrix

24
 Histogram

• Frequency distribution of a numeric (interval) variable’s values

• To make:

̶ Divide a numeric variable’s values into different bins which

means the range of the values.

̶ Count the number of observations in each bin.

̶ Draw a bar with each bin on the X axis and number of

observations on the Y axis.
 Boxplot – Distribution plot (1)

• Box plot illustrates the shape of a variable’s

distribution, its center value, and its range.
̶ Display the most extreme values (maximum and
minimum values) and the median in the data set .

̶ Indicate whether a distribution is skewed and whether “max”

outliers
there are any outliers in the data set.
• The distribution of bathrooms: positive skewed and flat
(Skewness = 0.511 and Kurtosis = 0.279)
mean

“min”

26
Boxplot – Distribution plot (2)

• Compare two or more data sets using categorical breakdown.

27
 Bar Chart (1)

• A bar chart may be either horizontal or vertical.

• The important point is their bar length or height

̶ The greater their length or height, the greater their value.

̶ The bar length/height represents the size of values while

the bars represent the groups of values.

• Comparing an individual value.

̶ Readers may readily recognize patterns or trends

28
Bar Chart (2)

• Vertical bar charts are useful to compare

outcome values across a categorical or
discrete variable (nominal or ordinal),
such as room types.

29
Pie Chart (1)

• A pie chart is a way of summarizing a set of

categorical (ordinal or nominal) data.
̶ It displays the percentage of values in a given variable.

̶ Each segment represents a particular category.

30
Pie Chart (2)

• Each segment shows its percentage of the total.

̶ An apartment type accounts for 75.85% among posted
properties.

• A pie chart is quite popular, as the circle provides

a visual configuration of the whole (100%).
̶ Pie charts show how much each category makes up for
the total.

31
Line Graph (1)

• A line graph is a visual relationship and

comparison of how two variables are
related or vary with each other.
̶ Line graphs compare two variables: one is
plotted along the x-axis (horizontal) and the
other along the y-axis (vertical).

̶ Ex) how housing price has been changing with

the size of a house.

32
Line Graph (2)
• Line graph is frequently used with a longitudinal data.
̶ The y-axis indicates quantity (e.g. temperature), and the x-axis has units of time. As a result, the
line chart is viewed as a time series graph.

− This line graph reveals the trend of

temperature over time.

33
Scatterplot (1)

• The scatterplot can compare the relationships

between two variables.

̶ The resulting pattern indicates the type and

strength of the relationship between two or
more variables.

34
Scatterplot (2)

• Display the relationship between two numerical variables.

Red, if waterfront = 1
Blue, if waterfront =0

35
Scatterplot Matrix (1)

• A Scatterplot Matrix combines several

scatterplots into one panel enabling the user to
see pairwise relationships between multiple
variables.
̶ Given a set of variables, the matrix plot contains all the
pairwise scatter plots of the selected variables on a
single page in a matrix format.

̶ The names of the variables are on the diagonals.

36
Scatterplot Matrix (2)
• Shows scatterplots for variable pairs (review score, response rates, and price).

37