Data Exploration and Preparation

This document provides a comprehensive guide on data exploration and preparation. It covers topics such as initial data exploration, descriptive statistics, exploration of data, coding categorical attributes, outlier detection, and cross-tabulation. The document also discusses different measurement scales and provides examples and interpretations of the data analysis. Suitable for courses in data analysis and statistics.

Data Exploration 1
DATA EXPLORATION AND PREPARATION
by[Name]
Course
Professor’s Name
Institution
Location of Institution
Date

Data Exploration 2
Contents of Contents
Contents of Contents..................................................................................................................2
A1. Initial Data exploration........................................................................................................3
1. Type of Attributes............................................................................................................3
2. Descriptive Statistics.......................................................................................................3
Percentage and Counts for Categorical Data....................................................................3
Summary Statistics for Interval and Ratio Variables.........................................................7
3. Exploration of Data.........................................................................................................8
Coding Categorical Attributes............................................................................................8
Outliers...............................................................................................................................9
Cross - Tabulation............................................................................................................10
Pearson correlation on Ratio Attributes...........................................................................14
Regression Estimation......................................................................................................14
1B. Data Processing.................................................................................................................17
(a) Binning......................................................................................................................17
(b) Normalisation............................................................................................................17
(c) Discretise...................................................................................................................18
(d) Binarise......................................................................................................................18
1C. Summary............................................................................................................................19

Data Exploration 3
Data Exploration and Preparation
A1. Initial Data exploration
1. Type of Attributes
Type of job, Marital status, Default, Housing, Loan, contact, poutcome are nominal
measurement scale, in which numbers serve as “tags” or “labels” only, to identify or classify
the objects. A nominal scale measurement normally deals with measurements in which
numbers have no value. Education, month, and day of week are ordinal measurement scale
since variables reports the ranking and ordering of the data without actually establishing the
degree of variation between them. Duration, campaign, pdays, and previous are interval
measurement scale since the difference between two variables is meaningful. Finally, age,
Empty.var.rate, cons.price.idx, cons.conf.idx, euribor3m, nr.employed, and y are ratio
measurement scale since the zero point make sense.
2. Descriptive Statistics
Descriptive statistics include measures of central tendency, dispersions and frequencies.
For interval and ratio variables measures of central tendency and dispersion are used to
summarize the data. However, for nominal and ordinal variables counts and frequencies are
used. The nominal and ordinal data re coded as follows:
Percentage and Counts for Categorical Data
The table 1 shows count and frequencies for the categorical data.

Data Exploration 4
Table 1 (a): Counts and Frequencies for Categorical Variable
Variable Categories Count
Frequency
(%)
Admin. 490 24.611
Blue-collar 467 23.456
Entrepreneur 85 4.269
Housemaid 43 2.160
Job Management 142 7.132
Retired 83 4.169
Self-employed 67 3.365
Services 183 9.191
Student 46 2.310
Technician 336 16.876
Unemployed 49 2.461
Approximately 64.9% of the participants are employed in administration (24.611%),
or Blu-collar (23.456%) and Technicians (16.876%). The unemployed and retired form
2.461% and 4.169% respectively of the total sample surveyed. The students form 2.310% and
the rest employed as entrepreneurs, housemaids, management, self-employed and services
form approximately 26.16%.
Table 1 (b): Counts and Frequencies for Categorical Variable
Variable Categories Count
Frequency
(%)
Basic 4 years 210 10.943
Basic 6 years 109 5.680
Highest Education Basic 9 years 298 15.529
Attained High school 453 23.606
Professional Course 266 13.861
University Degree 583 30.380

Data Exploration 5
Approximately 66% of the population have attended school past basic 9 years with
23.606%, 13.861% and 30.380% completing high school, professional course and university
degree respectively. The remaining 34% attendant the basic education with 10.943%, 5.680%
and 15.529% completing basic 4, 6 and 9 years. The values show that a high level of literacy
in the population.
Table 1 (c): Counts and Frequencies for Categorical Variable
Variable Categories Count
Frequency
(%)
Mar 24 1.200
Apr 146 7.300
May 662 33.100
Jun 274 13.700
Last contact month Jul 322 16.100
of year Aug 310 15.500
Sep 21 1.050
Oct 36 1.800
Nov 197 9.850
Dec 8 0.400
Majority of the clients (77%) were last contacted between the month of May to
August. This indicate that the company need to consider it follow up activities so that to
make sale out of the uncontacted clients.
Table 1 (d): Counts and Frequencies for Categorical Variable
Variable Categories Count
Frequency
(%)
Fri 364 18.200
Mon 437 21.850
Day of Week Thu 435 21.750
Tue 345 17.250
Wed 419 20.950
On average equal number of potential customers are contact every days of the week.
The percentage of customers contacted each working day of the week range from 17 to 21 %.

Data Exploration 6
Table 1 (e): Counts and Frequencies for Categorical Variable
Variable Categories Count
Frequency
(%)
Divorced 229 11.461
Marital Status Married 1240 62.062
Single 529 26.476
Outcome of the
previous Failure 197 9.850
marketing campaign Non-existent 1741 87.050
Success 62 3.100
Approximately 62.062% of the participants are married implying they have a family
while 11. 461% are divorced they might also have a family they look after. Thus, a good
population for sales and marketing. The single forms 26.476% of the total population,
however, they also have potential of starting a family at one point.
Table 1 (f): Counts and Frequencies for Categorical Variable
Variable Categories Count Frequency (%)
Credit default No
Unknown
1575
425
78.75
21.25
Housing Status No 926 46.30
Yes
Unknown
1015
59
50.75
2.95
Loan Status No 1650 82.50
Yes
Unknown
291
59
14.55
2.95
Contact Mode Cellular 1272 63.600
Telephone [phone]
Subscription Status No 1789 89.450
Yes 211 10.550
The given information indicates that 78.75% of the participants are not defaulter of
any loan. Further, 46.30%, and 82.50% are currently not serving mortgage or loan
respectively. 63.600% use cellular for communication an indication that they can easily be
reached at any time of the working days. However, out of the campaign and other factors
only 10.55% of the population have subscribed to term deposit.

Data Exploration 7
Summary Statistics for Interval and Ratio Variables
Table 2 shows the summary number of observations, minimum, maximum, mean,
standard deviation and variation coefficients for the interval variables.
Table 2: Summary Statistics for Interval variables
Statistic
Last
contact
duration
Contact
During
campaign
Days since
last
contact
Contact
before
Campaign
s
Observations 2000 2000 2000 2000
Minimum 5 1 1 0
Maximum 2926 28 999 5
Mean 249 3 966 0
Standard deviation 243 3 178 0
Variation coefficient 1 1 0 3
The minimum number of seconds that have passed since a client was contacted is 5
seconds while the maximum is 2926 seconds which converts to 8 hours. On average a client
was contacted within the last 249 seconds less than an hour. The minimum number of contact
that the team had made prior to campaign is one per client with maximum at 28 contacts.
However, on average a client is contacted three times before the campaign. The data on the
number of days that have passed since the last time the client was contacted and the duration
after last contact do not match. Therefore, the management should have a look into the
dataset to define it as propriate.
Table 3 shows the descriptive statistics for the ratio variables.
Table 3 Descriptive statistics for the ratio variables
Statistic Age Employmen Consumer Consumer Euribor Number of

Data Exploration 8
t variation
rate price index
confidence
index
3-month
rate employees
Observations 2000 2000 2000 2000 2000 2000
Minimum 18 -3.40 92.20 -50.80 0.64 4964
Maximum 98 1.40 94.77 -26.90 5.05 5228
Mean 40 0.07 93.57 -40.67 3.60 5167
Std. dev. 10 1.57 0.57 4.62 1.73 71
VC 0.26 23.58 0.01 -0.11 0.48 0.01
The minimum age of the participants was 18 years with maximum age of 98 years. On
average the majority of the participants were aged 40 years with a standard deviation of 10
years. The average employment variation rate is 0.07 approximately 7%. The average
consumer price index is 93.57 with standard deviation of 0.57% while the consumer
confidence index is negative. The Euribor three-month rate averages at 3.60 with variation
coefficient of 0.48. The minimum number of employees is 4964 with maximum of 5228 and
an average of 5167 with corresponding standard deviation of 71 employees.
3. Exploration of Data
The analysis software of choice for this section is KNIME analytic platform.
Coding Categorical Attributes
The figure 1 shows the codes generated for the categorical variables in KNIME analytic
platform.
Figure 1: Codes Generated in KNIME

Data Exploration 9
Job: 0 - services, 1 – admin, 2 - technician, 3 - blue-collar, 4 – management, 5 - self-
employed, 6 – retired, 7 – entrepreneur, 8 – housemaid, 9 – unemployed, 10 - unknown and
11 – Student. Marital: 0 – married, 1 – divorced, 2 – single and 3 - Unknown. Education: 5 -
basic.4y, 3 - basic.6y, 0- basic.9y, 4 - high.school, 2- professional.course, 2 -
university.degree and 6 - Unknown. Default: 0 – Unknown, 1 – no. Housing: 0 – no, 1 – yes
and 2 - unknown. Loan: 0 – no, 1 – yes and 2 - unknown. Contact: 0 – telephone, 1 –
cellular. Month: 6 – mar, 7 – apr, 0 – may, 1 – jun, 2 – jul, 3 – aug, 8 – sep, 4 – oct, 5 – nov,
9 – dec. Day of week: 0 – mon, 1 – tue, 2 - wed, 3 – thu, 4 – fri. poutcome: 0 – nonexistent,
1 – failure, 2 – success. Y: 0 – no, 1 – yes.
Outliers
The next step involved identification of outliers on the interval and ratio variables. The
figure 2 shows the snapshot of the results for outlier estimation in KNIME.
Figure 2: Snapshot of Outlier estimation

Data Exploration 10
From the figure 2 all the attributes have complete data 2000 represented in the member
count column. The variables; number of employees (nr.employed), Euribor 3-month rate
(euribor3m), Consumer price index (cons.price.idx), and employment variation rate
(emp.var.rate) do not have outliers (zero value under outlier count column). The number of
contacts performed before the campaign (previous), number of contacts performed during the
campaign (campaign), and last contact duration (duration) have the highest number of
outliers 259, 128, and 146 respectively.
Cross - Tabulation
The next stage of interest is to perform crosstabulation of various categorical data and
interpret the results. Figure 3 shows the cross tabulation of marital status b level of education.
Figure 3: Cross Tabulation for Marital Status and Education Level

Data Exploration 11
The p-value for the chi-square test for association equals to 0.00 which is less than alpha =
0.05, therefore, at 95% significance level there is association between level of education and
marital status. Further, 60.241% of those who did not disclose their marital status completed
basic 9 year of education while 39.759% completed university degree. Also, more than 50%
of those who are divorced have a minimum level of education of high school. However, for
the married each level of education have almost equal percentage. For the single have an
education level higher or equivalent to basic 9 years. Figure 4: Cross Tabulation for Day of
Week Contacted and Outcome of the Campaign.

Data Exploration 12
Figure 4: Cross Tabulation for Day of Week Contacted and Outcome of the Campaign
The p-value for the chi-square test for association equals to 2.79E-91 which is less than
alpha = 0.05. Thus, we conclude that at 95% significance level there is association between
Cross Tabulation for Day of Week Contacted and Outcome of the Campaign. Next, 10.418%
of those contacted on Friday recorded a failure in terms of the campaign while 87.5967%
were non-existent. Only 1.9851% of those contacted on Friday ended in success. Also,
8.9034% of those contacted on Monday recorded a failure in terms of the campaign while
87.7015% were non-existent, but 3.395% ended in success. Further, 8.9532% of those
contacted on Thursday recorded a failure while 85.5469% were non-existent, and 5.5% ended
in success. Next, 7.7651% of those contacted on Tuesday recorded a failure while 88.9751%
were non-existent, and 3.2598% ended in success. Finally, 11.2646% of those contacted on
Wednesday recorded a failure while 85.6732% were non-existent, and 3.0622% ended in
success.

Data Exploration 13
Figure 5 shows cross-tabulation for Day of week contacted and the status of subscription
to term deposits.
Figure 5: Cross tabulation for Day of Week contacted and the status of subscription to term
deposits
The p-value for the chi-square test for association equals to 3.59E-97 which is less than
alpha = 0.05. Thus, we conclude that at 95% significance level there is association between
Day of Week Contacted and the subscription status to term deposit. Next, 91.8201% of those
contacted on Friday have not subscribed to term deposit while 8.1799% have subscribed.
Also, 90.9371% of those contacted on Monday have not subscribed to term deposit while
9.0629% have subscribed. Further, 85.203% of those contacted on Thursday have not
subscribed to term deposit while 14.7962% have subscribed. Next, 88.7972% of those
contacted on Tuesday have not subscribed to term deposit while 11.2028% have subscribed.
Finally, 87.9836% of those contacted on Wednesday have not subscribed to term deposit
while 12.0164% have subscribed.

Data Exploration 14
Pearson correlation on Ratio Attributes
Figure 6 shows correlation matrix for ratio variables.
Figure 6: Correlation Matrix
The interpretation of Pearson’s correlation coefficients is as follows: -1.0 to -0.75
strong negative relationship, -0.50 to -0.74 moderate negative relationship, -0.4 to -0.01 weak
negative relationship (Mukaka 2012, p.70). 0.75 to 1.0 strong positive relationship, 0.50 to
0.74 moderate positive relationship, 0.4 to 0.01 weak positive relationship (Mukaka 2012,
p.70). Then, there exists a strong positive relationship between consumer price index and
employment variation rate since correlation value =0.771 > 0.75. Next, there exists a weak
positive relationship between consumer confidence index and employment variation rate
since correlation value = 0.2654 lies between 0.01 and 0.4. Also, there exists a strong positive
relationship between consumer Euribor 3-month rate and employment variation rate since
correlation value =0.9729 > 0.75. Further, there exists a strong positive relationship between
number of employees and employment variation rate since correlation value = 0.9113 > 0.75.
Regression Estimation
The final analysis on the estimation of the regression equation with response variable
being customer subscribing to a term deposit (y). Explanatory variables include Employment
variation rate, consumer price index, consumer confidence index, Euribor 3-month rate,

Data Exploration 15
number of employees and the mode of communication that the potential customers use. The
regression equation is of the form:
Y = β0 + β1 X1 +β2 X2 + β3 X3 +β4 X4 + β5 X5 +β6 X6 +e
Where:
Y = has the client subscribed a term deposit
βi = parameter estimates
X1 - employment variation rate
X2 - consumer price index
X3 - consumer confidence index
X 4 - Euribor 3-month rate
X5 - number of employees
X6 - contact communication type
e – Residuals
The figure 7 shows regression estimates. The other attributes were not used in the
regression since they produced unknowns.
Figure 7: Regression estimates
The attributes with p-value greater than or equal to 0.05 are dropped and a new model is
estimated. Euribor 3-month rate and number of employees are insignificant at 95%

Data Exploration 16
significance level because their p-value are 0.1226 and 0.09 respectively greater than alpha =
0.05. The figure 8 shows the new regression estimates and p-values.
Figure 8: Regression Estimates
Since all the parameter are significant at alpha = 0.05. the regression equation takes
the form: Term Subscription status = -19.5327 – 0. 1185contact(telephone) – 0.1213 EVR +
0.217CPI + 0.0151 CCI.
From the estimates, without the influence of contact, EVR, CPI and CCI the chances of a
person subscribing to term deposit reduce by an average of 19.53%. An individual whose
main mode of communication is telephone on average the chances of subscribing to term
deposit reduces by 11.85%. Next, a unit increase in employment variation rate results reduces
the chances of subscribing to term deposit by 12.13%. a unit increase in consumer price index
(CPI) results increases the chances of subscribing to term deposit by 21.70%. Finally, a unit
increase in consumer confidence index (CCI) improves the chances of subscribing to term
deposit by 1.51%. The adjusted R2 for the model is 16.66% implying that 16.66% of the
variations in whether one subscribes or does not in term deposit are influenced by CPI,
communication mode, CCI and EVR.

Data Exploration 17
1B. Data Processing
(a) Binning
The campaign data is copied and pasted in first column of the new sheet, 1B (a).
According to The Freedman-Diaconis rule (1981) the bin-width is set to h=2∗IQR ¿ n
−1
3
where n is the number of observations and IQR is the interquartile range (Olson 2018, p.29).
Then, the number of bins is calculated as follows:
Number of bins = (max−min)
3 h , where n is the number of observations, max is the
maximum value and min is the minimum value. From excel IQR = 2, max = 28 and
min = 1 and n = 2000. Then, h=2∗2 ¿ 2000
−1
3 =0.3175 and
Number of bins = (28−1)
3(0.3175) =28.
For equi-width, first step involves calculation of the width in cell D2 using this
formula: =(MAX($A$2:$A$2001) - MIN($A$2:$A$2001) + 0.00000001)/28. The value 28 is
the number of bins. The + 0.00000000001 is used to ensure that values equal to the maximum
are not put in a separate bin. Next, the bins are obtained using the formula:
=ROUNDDOWN(($A2-MIN($A$2:$A$2001))/$D$2, 0). The function help in finding the
number of bin-widths above the minimum a specific value is through dividing (value -
minimum) by the bin width. The main interest is on the number many full bin-widths fit into
the value, therefore, ROUNDDOWN chops off all the fractional bin-widths.
Finally, for equi-depth, use the formula
=ROUNDDOWN(PERCENTRANK($A$2:$A$2001, $A2)*28, 0). The formula first get the
percentile rank of the current cell ($A2) out of all the cells being binned ($A$2:$A$2001).
The value is the multiplied by the number of bins (YouTube 2019).

Data Exploration 18
.
(b) Normalisation
The min- max method involves identification of the maximum and minimum of the
attributes (Wang, Akeju and Cao 2016, p.260). Then, Min.A = 5 and MaxA = 2926. Define
new min and max as follows new min (NMin) = 0 and new max (NMax) = 1. Let V be
the normalized value where:
V = Value−MinA
MaxA−MinA
( NMax−NMin ) +NMin
The z-score normalization involves calculation of the sample mean and sample
variances using the formula:
Mean = Average (A2:A2001) and Standard deviation, S = STDEV.S(A2:A2001)
Then, z = STANDARDIZE (value, Mean, S).
(c) Discretise
Adult age a cording to international standards starts at age 18 including the youth age.
Mid -age starts from 35 years to 59 years (retirement age). Then, old-age begin from 60 years
and above. In order to categories the age data into these three categories use VLOOKUP
function. The table 4 shows the frequencies for the three categories of age.
Table 4: Descriptive statistics for age
Statistic Adult Mid-age Old-age
Frequency per category 713 1229 58
Rel. frequency per category
(%) 36 61 3
(d) Binarise
Marital status is a nominal data therefore, value 0 or 1 are used as tag (Idreos,
Papaemmanouil and Chaudhuri 2015, p. 278). Implying that there is no specific rule to

Data Exploration 19
deciding on which value takes zero or 1. However, once a decision has been made on the
values to assign tag o and tag 1 the process is kept consistent for the entire dataset. In this
case the attribute marital status has three value married, divorced and single, then for
binarization to work single and divorced are collapsed into a single value and given tag 0.
Then married is given tag 1. In the excel file use IF function as follows:
=IF (value = “married”, 1, 0).
1C. Summary
The campaign should focus on those employed in administration, Blu-collar and
Technicians since they form a large percentage of the population. The management of term
deposits should come up with features that will attract people in these areas. Also, from the
analysis of level of education majority of the people are literate with minimum education
level of basic 9 year of schooling. However, from the analysis marketing department are
mostly active in contacting potential clients during the month of May to August the strategy
should change since with this procedure only ten percent of the people actually subscribe to
term deposits. Further, the married form the highest portion of the population therefore, the
campaigns should focus more on the benefits term deposits have on the married and people
with families.
The results of the analysis show that success during the campaign and those who actually
subscribed to term deposits were contacted on Tuesday and Thursday. This is an indication
that majority of the people are open to considering an investment plan on these days. It was
clears that a very small percentage of success and subscription was recorded on those
contacted on Monday and Friday. Finally, the factors that term deposit marketing team need
to consider prior to launching campaign include average change in consumer price and
confidence index, mode of communication, and employment variation rates.

Data Exploration 20
References
Freedman, D. and Diaconis, P., 1981. On the histogram as a density estimator: L 2
theory. Probability theory and related fields, 57(4), pp.453-476.
Idreos, S., Papaemmanouil, O. and Chaudhuri, S., 2015, May. Overview of data exploration
techniques. In Proceedings of the 2015 ACM SIGMOD International Conference on
Management of Data (pp. 277-281). ACM.
Mukaka, M.M., 2012. A guide to appropriate use of correlation coefficient in medical
research. Malawi Medical Journal, 24(3), pp.69-71.
Olson, E., 2018. A Study of the Effects of Histogram Binning on the Accuracy and Precision
of Particle Sizing Measurements. Pharmaceutical Technology, p.29.
Wang, Y., Akeju, O.V. and Cao, Z., 2016. Bayesian Equivalent Sample Toolkit (BEST): an
Excel VBA program for probabilistic characterisation of geotechnical properties from
limited observation data. Georisk: Assessment and Management of Risk for
Engineered Systems and Geohazards, 10(4), pp.251-268.
YouTube. (2019). Age Variable Categories Binning in MS Excel. [online] Available at:
https://www.youtube.com/watch?v=iOFxyBZVamA [Accessed 3 May 2019].