Data Preparation and Exploratory Analytics

Introduction

Predictive analytics is often described as a modelling activity, but the quality of a predictive model is usually determined before modelling begins. Operational data may contain missing values, duplicate records, inconsistent codes, extreme observations, incorrect dates, incompatible units and information that was not available when a prediction was supposed to be made. If these problems are ignored, a technically sophisticated algorithm can produce unreliable or misleading forecasts.

Data preparation is the systematic process of acquiring, checking, cleaning, integrating, transforming and documenting data for analysis. Exploratory analytics is the disciplined investigation of data through summaries, visualisations and comparisons before formal modelling. Together, they help an analyst understand what the data represents, identify risks, discover patterns and construct variables that are useful for prediction.

In time-series forecasting, preparation requires additional care. Observations are ordered in time, nearby observations may be dependent, and the future must not influence the past. A random shuffle, an inappropriate imputation method or a feature calculated using the entire dataset can create data leakage and produce an unrealistically optimistic evaluation.

Learning Objectives

By the end of this chapter, you should be able to:

• identify suitable data sources and assess data quality;
• clean, integrate and document analytical datasets;
• diagnose and treat missing values, duplicates and outliers;
• distinguish variable types and select appropriate transformations;
• conduct exploratory analysis using numerical summaries and visualisations;
• engineer useful temporal, categorical and lagged features;
• explain and prevent data leakage;
• apply a defensible preparation workflow to a business problem.

1. The Analytical Dataset and Its Context

A dataset is not merely a table of values. Each row represents an observational unit, such as a customer, transaction, product-day or store-week. Each column represents a variable, such as sales, price, region or promotion status. The analyst must establish the unit of analysis, the observation period, the population covered and the meaning of every field.

For time-series work, a record commonly contains a time index t, a measured outcome y_t, and possibly explanatory variables x_1,t, x_2,t, ..., x_p,t. The subscript t indicates the time at which the value was observed. A valid time index should have an unambiguous time zone, a recognised frequency and an appropriate ordering.

Important questions include:

1. What decision will the analysis support?
2. What is the prediction target and forecast horizon?
3. When would the prediction have been available in practice?
4. What records are included or excluded?
5. Are measurements comparable across locations and periods?
6. Which variables are known at prediction time, and which become known only later?

A data dictionary should record the variable name, definition, type, unit, permitted values, source, collection method and quality issues. This documentation is part of analytical governance, not an optional administrative task.

2. Data Acquisition and Quality Assessment

Data may be acquired from transactional systems, spreadsheets, sensors, application programming interfaces, surveys, data warehouses or external providers. Acquisition should be reproducible: record the source, extraction date, query or file version, filters applied and any transformations performed.

Data quality is multidimensional. Common dimensions are:

A basic completeness rate for variable X can be expressed as:

Here, n is the total number of records and m is the number of missing values in X. The measure assumes that each row is an intended observation and that missingness has been correctly identified. A high completeness rate does not guarantee accuracy or validity.

Validation rules should be explicit. For example, an order quantity may need to satisfy q ≥ 0, a discount rate may lie between 0 and 1, and a date may need to fall within the study period. Rule violations should be investigated rather than automatically deleted, because an apparent error may represent a genuine exceptional event.

3. Cleaning and Integrating Data

3.1 Structural cleaning

Structural cleaning includes standardising column names, data types, date formats, units and category labels. For example, “UK”, “U.K.” and “United Kingdom” should be mapped to one controlled value. Currency values should not be combined without recording exchange rates and conversion dates.

Whitespace, punctuation and capitalisation can create false categories. However, indiscriminate text standardisation may destroy meaningful distinctions. Cleaning rules should therefore be based on business definitions and retained in a transformation log.

3.2 Duplicate records

Duplicates may be exact copies, repeated transactions or legitimate repeated observations. A duplicate test should use the business key, not simply every column. A sales transaction might be uniquely identified by transaction ID and line number. If the same transaction appears twice because of a data pipeline error, one record should be retained according to a documented rule and the incident should be reported.

3.3 Joining datasets

Integration requires compatible keys, time granularity and definitions. A many-to-one join, such as attaching a store’s region to each transaction, differs from a many-to-many join, which can unintentionally multiply records. After every join, compare row counts, key uniqueness and aggregate totals before and after integration.

Temporal integration is especially important. Suppose daily sales are joined to a monthly economic indicator. The analyst must specify whether the monthly value was known at the date of each sale. If a revised indicator is used, the historical revision may not have been available at the time and can create leakage.

3.4 Reproducibility

A professional workflow preserves raw data as read-only and creates processed versions through scripts or recorded transformation steps. Each output should be traceable to its source. Version control, validation reports and data-quality logs make results easier to reproduce and audit.

4. Missing Values

A missing value can arise because a field was not collected, a respondent declined to answer, a sensor failed, a value was not applicable or a system used a special code such as 999. Special codes must be converted to genuine missing values only after their meaning is confirmed.

Missingness is often classified as:

• MCAR (Missing Completely At Random): missingness is unrelated to observed and unobserved values;
• MAR (Missing At Random): missingness depends on observed variables;
• MNAR (Missing Not At Random): missingness depends on the unobserved value itself or an unmeasured process.

These categories concern the mechanism generating missingness, not merely the percentage missing. They cannot always be established from the dataset alone.

4.1 Treatment options

The appropriate response depends on the variable, mechanism, amount of missingness and intended use.

• Deletion: remove records or variables when missingness is limited and deletion will not introduce important bias.
• Constant or category treatment: use “Unknown” for a meaningful missing category, particularly for some categorical fields.
• Simple imputation: replace values with a mean, median or mode. The median is less sensitive to extreme values than the mean, but simple imputation reduces variability and may distort relationships.
• Time-series interpolation: estimate values between known observations when the process is sufficiently smooth and the missing interval is short.
• Forward filling: carry the last observed value forward only when that value logically persists, such as a posted policy rate, and never across an unjustified long interval.
• Model-based or multiple imputation: use relationships among variables, while ensuring that the imputation process is fitted only on training data when evaluating predictions.

For a training-set median imputation, let observed values be x₁, ..., x_k. The replacement value is the middle order statistic, or the average of the two middle values when k is even. The median must be calculated from the training period only; calculating it from the full dataset allows future information to influence past processing.

Missingness indicators can be useful. A binary variable r_i may be defined as 1 when the original value for record i was missing and 0 otherwise. Such an indicator should have a plausible interpretation and should not be used mechanically.

5. Outliers and Anomalous Observations

An outlier is an observation that is unusually distant from the general pattern. It may be a recording error, a data-entry mistake, a rare but valid event or evidence of a different operating regime. Outlier detection is therefore not equivalent to outlier removal.

For a variable, the interquartile range is:

where Q₁ is the first quartile and Q₃ is the third quartile. A common exploratory rule flags values below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a screening convention, not a universal law. It is most useful for identifying unusually distant values in a distribution and may be inappropriate for seasonal, skewed or highly heterogeneous data.

A z-score is often written as:

where x_i is an observation, x̄ is the sample mean and s is the sample standard deviation. The score is dimensionless and describes distance from the mean in standard-deviation units. It is sensitive to outliers and is most interpretable when the distribution is approximately symmetric.

For time series, outliers should also be assessed relative to time. A sales value may be normal during a holiday but anomalous on an ordinary day. Compare observations with relevant seasonal peers, investigate event calendars and distinguish additive shocks from persistent level changes. Possible treatments include correction at source, retaining a valid extreme, winsorisation, robust transformations, intervention indicators or separate modelling of exceptional events.

6. Exploratory Analytics

Exploratory analysis develops an evidence-based understanding of the dataset before predictive modelling. It should combine numerical summaries, graphical displays and domain knowledge.

6.1 Univariate analysis

For numerical variables, examine count, minimum, maximum, mean, median, standard deviation, quartiles and missingness. The sample mean is:

where n is the number of observed values. The sample variance is:

The denominator n − 1 is used for the usual unbiased estimator of population variance under independent, identically distributed sampling. In descriptive reporting, clearly distinguish a sample standard deviation from a population standard deviation.

Histograms, box plots and density plots reveal skewness, multimodality and extreme values. Frequency tables and bar charts are appropriate for categorical variables, but categories with very small counts should be reviewed for confidentiality, data-entry errors and unstable model estimates.

6.2 Relationships between variables

Scatter plots help reveal non-linearity, clusters and changing variance. Grouped summaries can compare average sales across regions, but averages alone may conceal unequal sample sizes or confounding variables. Correlation measures linear association, not causation. Pearson’s correlation is:

where x_i and y_i are paired observations, and x̄ and ȳ are their sample means. It is defined when both variables have non-zero variation and measures linear association. It can be distorted by outliers, non-linearity and temporal trends.

6.3 Time-series exploration

Plot the target against time and inspect:

• Trend: persistent upward or downward movement;
• Seasonality: a repeating pattern linked to calendar or operating cycles;
• Cycles: longer, non-fixed fluctuations;
• Level shifts: a change in the average after an event;
• Changing variance: increasing or decreasing dispersion;
• Missing or irregular timestamps: gaps that affect lag calculations.

A rolling mean over a window of w observations can be written as:

This is a descriptive smoother, not automatically a forecast. It requires at least w available observations and, when used for prediction, must be calculated using values available at the forecast origin.

For a time series, a naive one-step forecast is ŷ_t+1|t = y_t, meaning the next value is predicted by the current value. This is a baseline rather than a complete model. More advanced models should demonstrate improvement over an appropriate baseline.

7. Transformation and Feature Engineering

Feature engineering converts raw fields into representations that capture useful business and temporal structure. It should be guided by the prediction task, domain knowledge and information availability.

7.1 Numerical transformations

A logarithmic transformation, commonly z = ln(x), is defined only for positive x. It can reduce right skew and convert multiplicative changes into additive differences. If zero values are meaningful, adding a constant changes interpretation and should be justified rather than applied automatically.

Scaling may be needed for distance-based, gradient-based or regularised algorithms. Standardisation is:

where μ and σ are the mean and standard deviation estimated from the training data. The result has no units. Tree-based models are generally less sensitive to scale, but a consistent preprocessing pipeline remains important.

7.2 Categorical variables

Nominal categories have no natural order and are often represented using one-hot indicators. If a variable has k categories, a model with an intercept commonly uses k − 1 indicators to avoid exact linear dependence. The omitted category becomes the reference group. High-cardinality variables require care because one-hot encoding can create sparse, unstable features.

7.3 Temporal features

Useful temporal features include day of week, month, quarter, holiday flags, days since launch, promotion status and opening hours. Cyclical variables such as month can be represented using sine and cosine terms:

Here, c_t is the calendar position, P is the period, and the angle uses radians. The pair preserves the fact that the end and beginning of a cycle are close. The encoding assumes a meaningful fixed cycle and requires a clear definition of c_t.

Lag features use past values, for example y_t−1 or y_t−7. A change feature may be Δy_t = y_t − y_t−1, provided both observations are available and the difference is meaningful at the chosen frequency. Rolling sums, means and standard deviations can capture recent conditions, but their window must end at or before the prediction origin.

For a forecast made at time t, a safe feature has the form:

only when y_t is already known when the forecast is issued. If the target at time t is not yet observed, using it is leakage; the feature must instead use values through t−1.

8. Data Leakage and Valid Evaluation

Data leakage occurs when information unavailable at prediction time enters model training, feature construction or evaluation. Leakage can be subtle and is one of the most serious threats to predictive validity.

Common forms include:

• using future target values in lag or rolling features;
• imputing or scaling using the full dataset before splitting;
• randomly splitting a time series so that training includes observations after validation;
• using a post-outcome field, such as a final payment status, to predict the outcome that caused it;
• selecting variables after inspecting the complete test set;
• joining revised or delayed external data as though it had been available historically.

The correct principle is: fit every learned preprocessing step on training data and apply it unchanged to validation or test data. Learned steps include imputation values, scaling parameters, category dictionaries, feature-selection decisions and target encoders.

For time series, use chronological evaluation. A simple holdout uses earlier observations for training and later observations for testing. Rolling-origin evaluation repeatedly trains on an earlier period and evaluates on a subsequent period. The forecast horizon must match the business decision. A model for a one-day-ahead forecast should not be evaluated only on a twelve-month-ahead horizon.

A leakage audit should ask: What was known at the forecast timestamp? When was each source published? Does each feature use only permitted records? Was any transformation fitted using future observations? Are validation results being used repeatedly to tune the model without a final untouched test period?

9. An End-to-End Preparation Workflow

A defensible workflow is iterative rather than strictly linear:

1. Define the decision, target, unit of analysis and forecast horizon.
2. Acquire raw data and document sources and extraction times.
3. Profile structure, types, ranges, missingness, duplicates and timestamps.
4. Validate business rules and investigate anomalies.
5. Integrate sources using documented keys and temporal logic.
6. Establish chronological training, validation and test periods.
7. Fit cleaning, imputation, encoding and scaling procedures on training data.
8. Engineer features using only information available at prediction time.
9. Explore training data and compare patterns across relevant groups and periods.
10. Recheck distributions, row counts, units and leakage risks.
11. Save a reproducible data pipeline and a feature dictionary.

The order may change when a business requirement demands it. For example, the initial split should occur before any transformation that estimates parameters from data. Exploratory analysis may be conducted on training data for model development, while a strictly untouched test set is reserved for final assessment.

10. Illustrative Case: Retail Demand Forecasting

Background

A fictional retailer, Northstar Home, operates 40 stores and wants to forecast daily unit demand for each product-store combination seven days ahead. The purpose is to improve replenishment and reduce stockouts.

Problem

The retailer combines point-of-sale transactions, product information, promotions, store calendars and weather data. Initial inspection finds duplicate transaction lines, missing product categories, negative quantities, inconsistent store identifiers and a weather table containing revised historical values. Some features are created from daily sales totals.

Analysis

The analytical unit is product-store-day. The target is units sold on day t + 7. A transaction is not automatically evidence of demand: zero sales may mean no demand, or it may mean the product was out of stock. The team therefore adds an availability indicator where inventory records permit.

Duplicates are resolved using transaction ID and line number. Negative quantities are retained only when linked to verified returns; otherwise they are investigated. Store identifiers are mapped through a controlled reference table. Missing product categories are assigned “Unknown” temporarily and reported for master-data correction.

The team creates weekday, holiday and promotion features. A seven-day rolling mean for a forecast made on day t uses sales through day t, not future values. Because the target is seven days ahead, the feature-generation process is shifted so that the model never uses sales from the forecasted period. Weather variables are joined using the version available at the historical forecast time, rather than a later revised archive.

Approach and outcome

The data is divided chronologically: earlier months for training, a later month for validation and the final period for testing. Median imputation, category encoding and scaling are fitted only on training data. A seasonal-naive baseline is compared with models using promotions, lags and calendar features. Evaluation is reported separately for high-volume and low-volume products because aggregate performance can conceal poor service for important small segments.

The principal outcome is not merely a lower error score. The retailer obtains a traceable feature dictionary, a leakage checklist, an anomaly review process and a forecast pipeline that can be rerun when new data arrives.

Learning points

1. The target and forecast timestamp must be defined before features are created.
2. A zero sale is not always zero demand.
3. Historical data must reflect information that would genuinely have been available at the time.
4. Data-quality corrections require domain investigation.
5. Baselines and segment-level evaluation make model claims more credible.

11. Limitations, Ethics and Governance

Preparation decisions can change who is represented and how predictions affect people. Deleting records disproportionately missing for a demographic group can create unfairness. Imputation can conceal poor service or systematic exclusion. Outlier removal may erase legitimate events affecting smaller regions or vulnerable customers.

Analysts should minimise unnecessary personal data, control access, document lawful purpose and comply with applicable privacy and data-protection requirements. Sensitive attributes should not be used casually, but removing them does not guarantee fairness because proxy variables may remain. Monitor performance across relevant groups and document the reasoning behind exclusions and transformations.

Data lineage, access logs, retention rules, model cards and reproducible pipelines support accountability. Automated anomaly detection and AI-assisted coding can accelerate preparation, but generated transformations must be reviewed, tested and validated against business meaning. Automation does not remove responsibility for leakage, bias or incorrect assumptions.

12. Best-Practice Checklist

Before modelling, confirm that:

• the unit of analysis and target are unambiguous;
• dates, time zones, frequency and forecast horizon are correct;
• raw data is preserved and transformations are recorded;
• duplicates and invalid values have been investigated;
• missingness has been profiled and treated appropriately;
• outliers have been classified rather than automatically deleted;
• joins preserve intended row counts and business meaning;
• features are available at the prediction timestamp;
• learned preprocessing is fitted only on training data;
• chronological validation and a suitable baseline are used;
• results are examined across important segments;
• privacy, fairness and governance requirements are addressed.

Chapter Summary

Data preparation and exploratory analytics are foundational stages of predictive analytics. A reliable workflow begins by defining the decision, observation unit, target and forecast horizon. Data must then be profiled for completeness, validity, consistency, accuracy, uniqueness and timeliness. Cleaning involves careful treatment of types, categories, duplicates, joins and business-rule violations.

Missing values require an understanding of why they occur; deletion, imputation and indicators each have conditions and risks. Outliers should be investigated as possible errors, rare valid events or regime changes. Exploratory summaries and visualisations reveal distributions, relationships, trend, seasonality and structural breaks. Feature engineering can encode categories, scale variables, represent cycles and construct lagged or rolling measures.

The central time-series principle is that every feature and preprocessing decision must respect the information available at the forecast time. Chronological splitting, training-only fitting and leakage audits are essential. Finally, preparation must be reproducible, ethically defensible and connected to the business decision rather than treated as a purely technical cleaning exercise.

Key Terms

Data dictionary: Documentation describing variables, meanings, types, units and permitted values.

Unit of analysis: The entity represented by one observation or row.

Data quality: The degree to which data is complete, valid, consistent, accurate, unique and timely.

Completeness: The proportion of expected values that are present.

Duplicate: A repeated or multiply recorded observation according to a defined key.

Data integration: Combining data from multiple sources.

Missing value: An unavailable, unrecorded or inapplicable value.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Imputation: Replacing missing values using a defined method.

Outlier: An observation unusually distant from a general pattern.

Interquartile range: The difference between the third and first quartiles.

Exploratory analytics: Investigation of data using summaries, visualisations and domain reasoning.

Feature engineering: Creating analytical variables from raw data.

Lag feature: A variable containing a previous time-period value.

Rolling statistic: A statistic calculated over a moving historical window.

Data leakage: Use of information unavailable at prediction time.

Forecast horizon: The distance between the forecast origin and the period being predicted.

Chronological validation: Evaluation that preserves temporal order.

Review Questions

Short Answer Questions

1. Why must the unit of analysis be defined before integrating datasets?
2. Distinguish MCAR, MAR and MNAR missingness.
3. Why is the IQR rule for outliers a screening convention rather than a deletion rule?
4. What is the purpose of fitting preprocessing transformations only on training data?
5. Give two examples of features useful for time-series forecasting.

Long Answer Questions

1. Explain an end-to-end workflow for preparing data for a predictive model, including documentation and quality validation.
2. Compare deletion, simple imputation, interpolation and model-based imputation. Discuss when each may be appropriate.
3. Explain how exploratory analytics differs for cross-sectional data and time-series data.
4. Describe the causes and consequences of data leakage, using at least three examples.
5. Discuss ethical and governance risks associated with missing data, outlier treatment and automated feature engineering.

Analytical / Application Questions

1. A retailer calculates a seven-day rolling average using the entire dataset before splitting it into training and test periods. Identify the problem, explain why it affects evaluation and propose a corrected procedure.
2. A dataset contains sales of 0, 2, 3, 4 and 100 units. Calculate the mean, median and IQR, and explain which summary is more resistant to the extreme value.
3. Design a preparation plan for a daily demand dataset containing promotions, stockouts, missing weather observations and duplicated transactions. Specify the target, temporal split, feature rules and leakage controls.

Multiple Choice Questions with Answers

Question 1

Which property requires that a value conforms to an allowed rule, such as a discount lying between 0 and 1?

A. Completeness

B. Validity

C. Uniqueness

D. Timeliness

Correct Answer: B. Validity

Question 2

Which missingness mechanism means that missingness depends on observed variables?

A. MCAR

B. MAR

C. MNAR

D. Random deletion

Correct Answer: B. MAR

Question 3

For values 0, 2, 3, 4 and 100, what is the median?

A. 2

B. 3

C. 4

D. 21.8

Correct Answer: B. 3

Question 4

What is the main reason for fitting a scaler only on the training data?

A. To increase the number of observations

B. To prevent future information entering the model-development process

C. To remove all outliers

D. To guarantee normally distributed variables

Correct Answer: B. To prevent future information entering the model-development process

Question 5

Which feature is most likely to cause leakage when forecasting tomorrow's sales today?

A. Day of week

B. Holiday indicator known in advance

C. Tomorrow's final recorded sales value

D. Sales from yesterday

Correct Answer: C. Tomorrow's final recorded sales value

Question 6

The formula IQR = Q₃ − Q₁ measures what?

A. The range between the minimum and maximum

B. The middle fifty-percent spread

C. The sample mean

D. The correlation between two variables

Correct Answer: B. The middle fifty-percent spread

Question 7

Why can a many-to-many join be dangerous during data integration?

A. It always removes missing values

B. It can unintentionally multiply records

C. It converts all dates to text

D. It prevents categorical encoding

Correct Answer: B. It can unintentionally multiply records

Question 8

Which method is generally appropriate for representing a repeating calendar cycle such as month?

A. Randomly permuting months

B. Replacing every month with the annual mean

C. Sine and cosine encoding

D. Deleting the month variable

Correct Answer: C. Sine and cosine encoding

Question 9

What is the defining characteristic of chronological validation?

A. Observations are randomly shuffled

B. Training observations occur after test observations

C. Temporal order is preserved

D. Only categorical variables are evaluated

Correct Answer: C. Temporal order is preserved

Question 10

Why should a zero sales value be investigated in a demand dataset?

A. It is always an outlier

B. It may represent a stockout rather than zero demand

C. It cannot be used in a time series

D. It proves that the product was discontinued

Correct Answer: B. It may represent a stockout rather than zero demand