Knowledge Discovery and Data Preparation

Introduction

Data mining rarely fails because an algorithm cannot be executed. It more often fails because the data do not represent the problem accurately, contain systematic errors, or have been prepared in a way that introduces bias. Knowledge discovery in databases (KDD) provides a broader view of analytical work: useful knowledge is discovered through a sequence of problem definition, data selection, preparation, analysis, evaluation, and communication. Data preparation is therefore not a preliminary clerical activity; it is a central part of analytical reasoning.

This chapter presents KDD and CRISP-DM, explains how data are sampled and integrated, and develops practical methods for handling missing values, inconsistent representations, noise, outliers, normalization, discretization, feature construction, reduction, and exploratory data analysis (EDA). The emphasis is on decisions, assumptions, trade-offs, and responsible use in AI and machine-learning projects.

Learning Objectives

After studying this chapter, a student should be able to:

1. Distinguish KDD from data mining and describe the CRISP-DM lifecycle.

2. Select an appropriate sampling strategy and identify sampling bias.

3. Integrate data from multiple sources while managing schema, entity, and semantic conflicts.

4. Diagnose and treat missing values, noise, outliers, and inconsistent data.

5. Apply normalization, discretization, feature construction, and dimensionality-reduction methods appropriately.

6. Conduct EDA using numerical summaries and visualizations.

7. Evaluate preparation choices with respect to validity, leakage, fairness, privacy, and reproducibility.

1. KDD as an Analytical Process

Knowledge discovery in databases is the process of identifying valid, novel, useful, and understandable patterns in data. It is commonly represented as an iterative pipeline:

1. Domain and goal understanding: define the decision, population, unit of analysis, target, and success criteria.

2. Data selection: identify relevant tables, records, attributes, and time periods.

3. Data cleaning and preprocessing: correct errors, address missingness, reduce noise, and document assumptions.

4. Data transformation: construct features, normalize values, discretize variables, and create an analysis-ready representation.

5. Data mining: apply algorithms to discover patterns or build predictive models.

6. Pattern evaluation: assess statistical, operational, ethical, and domain validity.

7. Knowledge presentation: communicate findings through reports, dashboards, rules, models, or deployed services.

These stages are not strictly linear. Evaluation may reveal that the target was poorly defined; exploration may uncover a data-quality problem; and deployment may require new features. KDD is consequently an iterative process of inquiry rather than a mechanical sequence.

Data mining is the algorithmic component of KDD concerned with discovering patterns, associations, clusters, classifications, or predictions. KDD includes the surrounding human and organizational work. For example, a decision tree is a data-mining technique, whereas choosing the population, preparing the variables, interpreting the tree, and deciding whether it is safe to use are KDD activities.

A useful pattern should be more than mathematically detectable. It should be sufficiently reliable for the intended population, provide value beyond existing knowledge, and be understandable enough to support action. Correlation alone does not establish causation, and a highly accurate model may still be inappropriate if it relies on leaked information or produces discriminatory outcomes.

2. CRISP-DM and Project Governance

The Cross-Industry Standard Process for Data Mining (CRISP-DM) organizes projects into six phases:

• Business understanding: translate an organizational problem into an analytical objective. A business objective such as “reduce loan losses” must become a measurable task, such as estimating probability of default within a specified horizon.
• Data understanding: collect initial data, describe variables, explore distributions, and identify quality problems.
• Data preparation: select cases, clean and integrate sources, construct features, and format data for modeling.
• Modeling: choose algorithms, train models, and tune their parameters.
• Evaluation: determine whether the model meets technical and business criteria and whether risks are acceptable.
• Deployment: present, operationalize, monitor, and maintain the result.

CRISP-DM is valuable because it makes the business purpose explicit and recognizes deployment as part of the project. It is not a rigid waterfall method. Preparation and modeling commonly alternate, and evaluation can return the team to business understanding.

A well-governed project maintains a data dictionary containing definitions, units, valid ranges, source systems, and ownership. It also records lineage, transformation code, versioned datasets, sampling decisions, and reasons for excluding records. A reproducible pipeline should produce the same output when supplied with the same versioned inputs and configuration.

3. Sampling and Data Selection

A population is the complete set of units about which conclusions are intended; a sample is the subset actually observed. The unit may be a person, transaction, device, visit, image, or time interval. Defining the unit prevents errors such as treating ten transactions from one customer as ten independent customers.

In a simple random sample, every unit has an equal known chance of selection. Systematic sampling selects every kth unit after a random start and can be efficient, but periodic ordering in the source may create bias. Stratified sampling divides the population into relevant groups and samples within each group; it is useful when small but important groups must be represented. Cluster sampling selects groups such as schools or branches and observes units within them, reducing collection cost but often increasing similarity among observations.

A convenience sample is selected because it is easy to access. It may be useful for prototyping but provides weak grounds for population-level claims. Oversampling a rare class can improve learning representation. If estimates of population prevalence are required, class weights or sampling weights may be needed. A sampling weight is commonly related to the inverse of a unit’s probability of selection, although the exact estimation method depends on the survey design.

Sampling must respect time. In forecasting or prediction of future events, training observations should precede validation and test observations chronologically. Randomly mixing future records into training can create temporal leakage. Similarly, all records from one patient, household, or customer should generally remain in one partition when the goal is to generalize to unseen entities.

4. Data Integration and Quality Assessment

Data integration combines files, databases, APIs, sensors, or external sources. The main difficulties are:

• Schema conflict: one source calls a field `customer_id`, another calls it `client_no`.
• Structural conflict: one table stores address as one field, another separates street, city, and postal code.
• Semantic conflict: “revenue” may mean gross sales in one source and net sales in another.
• Unit conflict: temperature may be recorded in Celsius and Fahrenheit.
• Entity-resolution conflict: two records may represent the same person with different spellings or identifiers.
• Granularity conflict: one source is transaction-level and another is monthly customer-level.

Integration begins with a conceptual data model and explicit keys. A primary key identifies a record within a table; a foreign key links it to another table. Before joining, analysts should check key uniqueness, referential integrity, duplicate rates, and expected join cardinality. An unintended many-to-many join can multiply records and distort totals.

Entity resolution may use deterministic rules, such as an exact verified identifier, or probabilistic matching based on name, address, telephone number, and date of birth. Matching rules must be validated because false matches merge different people while missed matches split one person into several entities.

Data quality is multidimensional. Accuracy concerns correctness; completeness concerns the presence of required values; consistency concerns agreement across records and sources; timeliness concerns currency; validity concerns conformity to rules; and uniqueness concerns duplication. Quality checks should be expressed as testable rules, such as “date of discharge cannot precede date of admission” or “a percentage must lie between 0 and 100.”

5. Missing Values, Noise, and Outliers

Missingness may be represented by null, blank, a special code such as 999, or an impossible value. Special codes must be converted to a consistent missing-value representation before analysis. Missingness is often classified as:

• MCAR (missing completely at random): missingness is unrelated to observed and unobserved values.
• MAR (missing at random): after conditioning on observed variables, missingness does not depend on the missing value itself.
• MNAR (missing not at random): missingness depends on the unobserved value or an unmeasured process.

These assumptions cannot usually be proven from the observed data alone. A missingness indicator, domain knowledge, and comparison of observed groups can provide evidence, but not certainty.

Simple treatments include deleting records or imputing a constant, mean, median, or mode. Mean imputation is sensitive to outliers and reduces apparent variation; median imputation is more robust for skewed numerical variables. More advanced approaches include regression imputation, k-nearest-neighbor imputation, and multiple imputation. Multiple imputation creates several plausible completed datasets, analyzes each, and combines results; it is preferable when inference about uncertainty is important.

For predictive modeling, preprocessing parameters must be learned from the training data only. The training median, scaling parameters, imputation model, or feature-selection rule must not use validation or test observations. Otherwise, information from the evaluation set leaks into the model-development process.

Noise is random variation or measurement error. It can be reduced through validation rules, repeated measurements, smoothing, aggregation, or robust modeling. An outlier is an observation unusually distant from the rest under a specified representation. It may be an error, a rare but valid case, or evidence of a new subgroup. Deleting an outlier solely because it is inconvenient is poor practice. Investigate its provenance, compare it with domain limits, and determine whether the analytical objective concerns ordinary behavior or rare events.

For a numerical variable, a common exploratory rule defines the interquartile range as

IQR = Q3 − Q1,

where Q1 and Q3 are the first and third quartiles. Values below Q1 − 1.5IQR or above Q3 + 1.5IQR are often flagged as potential outliers. This is a screening convention, not a universal error criterion; its usefulness depends on the distribution and context.

6. Transformation and Feature Construction

6.1 Normalization and Scaling

Scaling is important for distance-based methods, k-nearest neighbors, k-means, support-vector machines, and gradient-based optimization. It is less essential for many decision-tree methods, although consistent preprocessing remains useful.

Min–max scaling maps a value x to

x' = (x − xmin) / (xmax − xmin),

where xmin and xmax are the minimum and maximum values estimated from the training data. Under the same bounds, values lie in [0, 1]. The method is sensitive to extreme values and new observations may fall outside this interval.

Z-score standardization is

z = (x − μ) / σ,

where μ is the training mean and σ is the training standard deviation, with σ > 0. The transformed training variable has mean approximately 0 and standard deviation 1. Standardization does not make a variable normally distributed and can remain sensitive to outliers.

Robust scaling uses the median and IQR, for example (x − median)/IQR, and is often more appropriate for skewed data. Logarithmic or other monotonic transformations may reduce right skew, but the domain must support the transformation; a logarithm cannot be applied directly to a nonpositive value without a justified adjustment.

Scaling must be fitted within each training fold during cross-validation. Applying scaling to the full dataset before cross-validation causes leakage.

6.2 Discretization

Discretization converts a continuous variable into intervals. Equal-width binning divides the numerical range into intervals of the same width; equal-frequency binning aims for approximately equal numbers of observations in each bin. Domain-based bins, such as clinically meaningful age bands, may be more interpretable.

Discretization can simplify rules, reduce sensitivity to small measurement differences, and support algorithms requiring categorical inputs. It also loses information, introduces boundary effects, and may reduce predictive performance. Cut points should be learned from training data or justified independently, then applied unchanged to future data.

6.3 Feature Construction

Feature construction converts raw observations into variables that better express the mechanism of interest. Examples include age from date of birth, total spending from transactions, recency-frequency-monetary measures, ratios, interaction terms, rolling averages, and counts over a defined time window.

Every constructed feature needs a time reference. A “number of complaints in the next 30 days” feature is leakage if used to predict today’s churn. A ratio can be unstable when its denominator is near zero, and a feature based on a post-outcome event may encode the answer directly. Feature definitions should include formula, unit, time window, source fields, and missing-value behavior.

Categorical variables may be represented by one-hot encoding, ordinal encoding, target encoding, or embeddings. Ordinal encoding is appropriate only when categories possess a meaningful order. Target encoding must be calculated without using the target value of the current validation or test record.

7. Data Reduction

Data reduction seeks a smaller representation while preserving information relevant to the objective.

Feature selection retains a subset of variables. Filter methods use statistical properties independently of a model, such as variance screening or mutual information. Wrapper methods evaluate subsets using a chosen model and can be computationally expensive. Embedded methods perform selection during learning, for example through regularization or tree-based importance. A selected feature is not necessarily causal, fair, or stable across samples.

Dimensionality reduction creates new variables. Principal component analysis (PCA) finds orthogonal directions of maximum variance after suitable centering and, when appropriate, scaling. The first principal component captures the largest possible variance among linear combinations, and subsequent components capture the largest remaining variance subject to orthogonality. PCA is unsupervised: high variance does not necessarily mean high predictive value, and components may be difficult to interpret.

Sampling, aggregation, compression, and removal of duplicate records are also forms of reduction. Reduction should be evaluated against storage, computation, interpretability, and information-loss requirements.

8. Exploratory Data Analysis

EDA is an investigative stage used to understand structure, quality, relationships, and unusual observations before formal modeling. It should be guided by questions rather than by indiscriminate production of charts.

For a numerical variable, useful summaries include count, missing count, minimum, maximum, mean, median, quartiles, standard deviation, and selected percentiles. For categorical variables, examine frequency and proportion tables, rare levels, and inconsistent labels. A histogram or density plot reveals shape; a box plot highlights spread and potential outliers; a scatter plot examines relationships; and a correlation matrix summarizes linear association among numerical variables.

The sample mean is

x̄ = (1/n) Σ(xi),

where xi is observation i and n is the number of nonmissing observations included. The sample variance is commonly estimated as

s² = [Σ(xi − x̄)²]/(n − 1),

when observations are treated as a sample and n > 1. The denominator n − 1 is associated with the usual unbiased estimator of population variance under independent, identically distributed sampling; it does not correct all forms of sampling bias.

Correlation should not be interpreted as causation. Pearson correlation measures linear association and can be distorted by outliers; rank-based measures may better represent monotonic but nonlinear relationships. Group comparisons should examine sample size, uncertainty, confounding, and practical significance rather than relying only on a p-value.

EDA should also examine the target distribution, class imbalance, duplicate entities, time trends, subgroup performance, and the relationship between missingness and the target. Visualizations must be accessible, labelled with units, and designed to avoid misleading scales.

9. Worked Example: Preparing Customer-Churn Data

Suppose a telecommunications company wants to predict whether a customer will cancel service within the next 30 days. The raw sources contain customer profiles, monthly invoices, service tickets, and cancellation records.

Problem definition: The unit of analysis is one customer-month. The target is cancellation during the 30 days after the prediction date. Data after that date cannot be used as predictors.

Integration: Customer identifiers are standardized, duplicate profiles are resolved, and invoices are aggregated to the customer-month level. A join audit confirms that each customer-month has at most one profile record and that invoice totals are not multiplied by ticket joins.

Quality assessment: Negative invoice amounts are investigated as refunds rather than automatically removed. Ticket categories such as “Billing,” “billing,” and “BILL” are mapped to one controlled vocabulary. A customer with no ticket is distinguished from a customer whose ticket history is unavailable.

Missingness: Monthly income is missing for many prepaid customers. The team compares missingness by plan type and creates an income-missing indicator. The median income within a suitable training subgroup is used for a baseline model, with imputation fitted only on training folds. The limitation is documented because missingness may represent customer type, not random absence.

Feature construction: Features include tenure in months, number of tickets in the previous 90 days, average invoice in the previous three months, payment delay rate in the previous six months, and change in usage between the previous two months. All windows end on the prediction date.

Scaling and partitioning: Numerical features are standardized within the training pipeline. The final test period is later than the training period. Customers appearing in multiple months are assigned according to a policy that prevents future records from informing earlier predictions.

EDA and evaluation: The team examines churn by plan, tenure, and region, but does not infer that plan causes churn. It compares precision, recall, calibration, and subgroup error rates. A model with slightly lower overall accuracy may be preferred if it identifies vulnerable customers without imposing disproportionate costs on a protected group.

The result is not merely a cleaner table. It is a documented analytical representation whose variables, time boundaries, assumptions, and limitations can be reviewed and reproduced.

10. Ethics, Privacy, and Contemporary Practice

Data preparation can create or amplify unfairness. Removing records with missing values may disproportionately remove people with limited access to services. Oversampling a minority group can improve representation, but synthetic records must not be treated as real observations for every purpose. Proxy variables may reproduce protected characteristics even when those attributes are excluded.

Privacy principles include purpose limitation, data minimization, access control, retention limits, secure storage, and appropriate consent or legal basis. De-identification reduces risk but does not guarantee anonymity, especially when datasets can be linked. Regulations vary by jurisdiction; projects should involve institutional governance and legal review where personal or sensitive data are processed.

Contemporary pipelines increasingly use data contracts, automated quality tests, feature stores, lineage systems, privacy-preserving techniques, and monitoring for data drift. Automated tools do not replace domain review. Generative AI may assist with documentation or exploratory code, but generated transformations must be tested, reproducible, secure, and checked for leakage or fabricated assumptions.

11. Best Practices and Limitations

Effective preparation follows several principles:

• Begin with the decision and population, not with available columns.
• Define the unit of analysis and prediction time explicitly.
• Profile data before transforming them.
• Preserve raw data and create versioned derived datasets.
• Fit all learned transformations on training data only.
• Treat missingness and outliers as questions about the data-generating process.
• Validate joins and reconcile totals before modeling.
• Prefer interpretable, domain-justified features when performance is comparable.
• Test results across time and relevant subgroups.
• Record assumptions, rejected alternatives, and known limitations.

No preparation method is universally best. Mean imputation, deletion, scaling, binning, and PCA may be appropriate in one context and harmful in another. Preparation can reduce noise but also remove meaningful rare cases; integration can increase coverage but introduce incompatible definitions; and dimensionality reduction can improve efficiency while reducing interpretability. The correct choice depends on the analytical goal, data-generating process, costs of error, and governance requirements.

Chapter Summary

KDD is an iterative process that extends beyond data-mining algorithms to include problem definition, data selection, preparation, evaluation, and communication. CRISP-DM provides a practical lifecycle linking business understanding, data understanding, preparation, modeling, evaluation, and deployment. Sound projects define their population and unit of analysis, use sampling methods appropriate to the intended claims, and protect temporal and entity boundaries.

Data integration requires attention to schema, semantics, units, keys, granularity, and entity resolution. Data-quality assessment addresses accuracy, completeness, consistency, timeliness, validity, and uniqueness. Missing values should be understood through MCAR, MAR, and MNAR concepts, while noise and outliers require investigation rather than automatic deletion.

Normalization, discretization, and feature construction can improve model compatibility and express useful domain structure, but they may also lose information or create leakage. Feature selection, aggregation, sampling, and PCA reduce complexity with different interpretability and information-retention consequences. EDA combines numerical summaries, visualizations, subgroup analysis, and domain reasoning. Finally, reproducibility, privacy, fairness, security, and regulatory governance are essential parts of responsible data preparation.

Key Terms

KDD: The process of discovering valid, useful, novel, and understandable knowledge from data.

Data mining: The algorithmic discovery of patterns or predictive relationships in data.

CRISP-DM: A lifecycle consisting of business understanding, data understanding, preparation, modeling, evaluation, and deployment.

Population: The complete set of units about which conclusions are intended.

Sampling bias: Systematic difference between the sampled data and the target population.

Stratified sampling: Sampling separately within defined population groups.

Data integration: Combining data from multiple sources into a coherent representation.

Entity resolution: Determining which records refer to the same real-world entity.

Data lineage: A record of data origins and transformations.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed variables.

MNAR: Missing not at random.

Outlier: An unusually distant observation under a specified representation.

Normalization: Rescaling variables to comparable numerical ranges or distributions.

Discretization: Conversion of continuous values into intervals or categories.

Feature construction: Creation of analytical variables from raw data.

Data leakage: Use of information during training that would not be available at prediction time.

Feature selection: Choosing a subset of available variables.

PCA: An unsupervised linear dimensionality-reduction method based on directions of variance.

Exploratory data analysis: Investigative examination of data structure, quality, distributions, and relationships.

Review Questions

Short Answer Questions

1. How does KDD differ from data mining?

2. What is the purpose of defining the unit of analysis?

3. Distinguish MCAR, MAR, and MNAR missingness.

4. State the z-score standardization formula and define its parameters.

5. Why must preprocessing parameters be fitted only on training data?

Long Answer Questions

1. Explain the phases of CRISP-DM and describe why the process is iterative.

2. Compare simple random, stratified, cluster, and convenience sampling, including an appropriate use or limitation for each.

3. Discuss the major challenges of integrating data from multiple sources.

4. Explain alternative approaches to missing values, noise, and outliers, and identify the assumptions behind them.

5. Compare feature selection, PCA, aggregation, and sampling as data-reduction strategies.

Analytical / Application Questions

1. A hospital combines patient, laboratory, and admission tables, and the final patient count doubles after a join. Diagnose possible causes and propose validation tests.

2. Design a leakage-resistant preparation pipeline for predicting next-month customer churn from transaction and support data. Specify time windows, partitions, and transformations.

3. A dataset has highly skewed income, 18% missing income values, and a rare protected subgroup. Recommend an EDA and preparation plan that addresses skew, missingness, representation, fairness, and privacy.

Multiple Choice Questions with Answers

Question 1

Which activity belongs to KDD but is broader than data mining itself?

A. Selecting an appropriate business objective

B. Calculating a distance between two records

C. Fitting a decision tree

D. Computing cluster centroids

Correct Answer: A. Selecting an appropriate business objective

Question 2

Which CRISP-DM phase primarily determines whether the analytical result meets business and technical requirements?

A. Data understanding

B. Modeling

C. Evaluation

D. Deployment

Correct Answer: C. Evaluation

Question 3

Which sampling method deliberately samples within defined population groups?

A. Convenience sampling

B. Stratified sampling

C. Systematic sampling

D. Cluster sampling

Correct Answer: B. Stratified sampling

Question 4

For a variable with Q1 = 10 and Q3 = 18, what is the upper 1.5-IQR screening boundary?

A. 20

B. 22

C. 30

D. 34

Correct Answer: C. 30

Question 5

In z-score standardization, which quantities should normally be estimated from the training data?

A. The training mean and standard deviation

B. The test mean and standard deviation

C. The global minimum and maximum only

D. The target prevalence and class labels

Correct Answer: A. The training mean and standard deviation

Question 6

What is the main risk of randomly splitting longitudinal records when predicting future events?

A. Underfitting caused by too few variables

B. Temporal leakage from future observations

C. Loss of all categorical variables

D. Automatic removal of outliers

Correct Answer: B. Temporal leakage from future observations

Question 7

Which statement about PCA is correct?

A. It is supervised and always maximizes predictive accuracy

B. It creates orthogonal directions ordered by explained variance

C. It converts every variable into a binary category

D. It guarantees that components are easy to interpret

Correct Answer: B. It creates orthogonal directions ordered by explained variance

Question 8

Which situation is an example of data leakage?

A. Using past invoices to predict current churn

B. Scaling predictors using training-fold parameters

C. Using a post-outcome event as a predictor of that outcome

D. Removing duplicate records before partitioning

Correct Answer: C. Using a post-outcome event as a predictor of that outcome

Question 9

Which transformation is generally most robust to extreme values?

A. Mean imputation

B. Min–max scaling

C. Median-and-IQR scaling

D. Equal-width binning

Correct Answer: C. Median-and-IQR scaling

Question 10

Why can an unintended many-to-many join be dangerous?

A. It always removes missing values

B. It can multiply records and distort aggregates

C. It prevents all feature construction

D. It guarantees referential integrity

Correct Answer: B. It can multiply records and distort aggregates