Data Preparation and Exploratory Analysis

Introduction

A capstone project is only as credible as the data on which its conclusions rest. Data preparation is therefore not a mechanical prelude to analysis; it is an analytical and governance activity. Decisions about what to retain, remove, recode, combine, or impute can change the meaning of a dataset and influence the conclusions drawn from it. Exploratory analysis provides the complementary discipline of asking what the data actually contain before formal modelling begins.

This chapter presents an end-to-end approach to profiling, cleaning, integrating, transforming, and exploring data. It addresses missingness, anomalies, bias, measurement error, visualization, reproducibility, and fitness for purpose. The emphasis is on decisions that a business analytics student must be able to justify in a dissertation or capstone report.

Learning objectives

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

• define data quality dimensions and assess fitness for purpose;
• profile datasets using structural, descriptive, and provenance checks;
• identify and treat missing values, duplicates, anomalies, and inconsistent records;
• integrate data from multiple sources while managing keys, granularity, and lineage;
• transform variables appropriately for analysis;
• use exploratory statistics and visualizations to reveal patterns and problems;
• distinguish data errors from legitimate unusual observations;
• recognize bias, measurement error, and ethical risks;
• document a reproducible preparation pipeline and defend its analytical choices.

1. Data preparation as an analytical process

Data preparation converts raw observations into an analysis-ready dataset. A useful conceptual pipeline is:

Figure: Data preparation and exploratory analysis are iterative rather than strictly linear.

The unit of observation must be established first. A row may represent a customer, transaction, product-day, claim, or website session. Confusing these levels creates invalid joins and misleading statistics. A dataset containing several rows per customer is not a customer-level dataset merely because it includes a customer identifier.

Data quality and fitness for purpose

Common quality dimensions include:

Quality is contextual. A dataset may be adequate for analysing monthly revenue but unsuitable for real-time fraud detection because of delayed timestamps. Fitness for purpose should be stated in relation to the research question, population, time period, outcome, and required precision.

2. Profiling before cleaning

Profiling is a systematic description of data before substantive modification. It should record source, extraction date, file or table version, row and column counts, data types, candidate keys, missingness, distinct values, ranges, and suspicious patterns. A data dictionary defines each field, unit, coding scheme, allowable values, and business meaning.

For a numeric variable x with n observed values, the sample mean is:

Here xᵢ is the ith observed value and n is the number of non-missing observations. The mean is appropriate for quantitative measurements when extreme values do not make it misleading. A sample standard deviation is:

The n − 1 denominator estimates population variability from a sample and assumes independent observations for the usual inferential interpretation. Descriptive statistics should be accompanied by the number of observations; a mean based on 20% of the intended sample may be materially less informative than one based on 95%.

For skewed variables, report the median and quartiles. The interquartile range is:

where Q₁ and Q₃ are the 25th and 75th percentiles. A conventional exploratory outlier flag is any value below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a screening rule, not proof of an error. It is less appropriate for strongly discrete, bounded, or highly skewed data without contextual interpretation.

Profiling should include cross-field checks. Examples include order quantity greater than zero, delivery date not preceding order date, a percentage between 0 and 100, and a cancelled order not having a completed delivery. Such rules often reveal errors that univariate summaries miss.

3. Missing data

Missingness may be represented by blank cells, special codes such as 999, text such as “unknown,” or structurally absent records. These representations must be harmonized without confusing “not applicable,” “not recorded,” “refused,” and “unknown.” Each may have a different substantive meaning.

Three useful conceptual mechanisms are:

• Missing completely at random (MCAR): missingness is unrelated to observed and unobserved values.
• Missing at random (MAR): missingness depends on observed variables, after accounting for them.
• Missing not at random (MNAR): missingness depends on the unobserved value itself or on unobserved factors.

These are assumptions about a data-generating process, not labels that can be proven solely from a missingness table. For example, income may be missing more often among high-income respondents, suggesting a possible MNAR mechanism.

First quantify missingness by field, subgroup, and time. A missingness percentage for field j is:

where mⱼ is the number of missing values and nⱼ is the number of records eligible to contain that field. The denominator may differ across fields when fields are structurally conditional.

Treatment options include:

1. Deletion: remove records or fields when missingness is limited and plausibly harmless. Complete-case analysis can reduce statistical power and introduce bias if missingness is not MCAR.
2. Single imputation: use a median, group-specific value, or model prediction. It is simple but can understate uncertainty and create artificial concentration.
3. Missingness indicators: add a flag showing that a value was missing. This can preserve information about the recording process but does not by itself solve bias.
4. Multiple imputation: create several plausible completed datasets, analyse each, and combine results. It is more principled when assumptions and models are appropriate, but requires careful implementation.
5. Substantive recoding: treat “not applicable” or “not asked” as a meaningful category when the research design supports it.

Imputation must be performed within the training data in predictive projects. If information from a test set or future period determines an imputation value, data leakage occurs. In a dissertation, report the amount, pattern, rationale, and sensitivity of conclusions to the chosen treatment.

4. Duplicates, invalid records, and anomalies

Duplicates may be exact copies, repeated transactions, or legitimate repeated events. A duplicate customer record may indicate a failed merge; repeated purchases by the same customer are not duplicates merely because the customer identifier repeats. Define duplicate status using the unit of analysis and a candidate key.

Invalid records violate known constraints: impossible dates, negative ages, malformed identifiers, or incompatible categories. Correcting them requires evidence. A value should not be changed simply because it is inconvenient or unusual.

An anomaly is an observation that differs substantially from a pattern. It can represent a data-entry error, fraud, a new market event, a measurement failure, or a valid extreme. Useful detection approaches include:

• domain limits and logical rules;
• boxplots and robust statistics;
• time-series plots and sudden changes;
• clustering or distance methods;
• residual diagnostics after a model is fitted.

Anomaly treatment may involve correction from a verified source, retaining and flagging the observation, winsorizing extreme values, transforming a variable, or excluding the record with a documented reason. Winsorization replaces values beyond selected thresholds with boundary values; it changes the data and should never be presented as neutral cleaning.

A useful robustness practice is to compare results with and without influential observations. If a business recommendation changes, the observation deserves explicit discussion rather than silent removal.

5. Integration and data lineage

Integration combines data from different systems. The principal risks are mismatched keys, different granularity, inconsistent definitions, time-zone differences, and conflicting source values.

A primary key uniquely identifies a record in a table. A foreign key refers to a key in another table. A one-to-many join, such as customers to orders, should increase rows in a predictable way. Joining two transaction-level tables on customer ID alone can create a many-to-many multiplication and inflate revenue. Keys should therefore be tested for uniqueness and join cardinality before integration.

Consider a customer table with one row per customer and an order table with five rows for one customer. A customer-to-order join produces five customer-order rows, which is appropriate for order analysis. If a second five-row table of support contacts is joined on customer ID, the result may contain 25 combinations for that customer. The correct approach may be to aggregate each event table to customer level before joining, or to analyse the event tables separately.

Integration also requires semantic alignment. “Revenue” may mean gross sales in one system and net sales after returns in another. “Date” may mean order placement, shipment, or payment. A data contract or mapping document should specify definitions, units, currencies, time zones, code translations, and precedence rules.

Lineage records where data came from, what transformations were applied, when they occurred, and who approved them. Version-controlled scripts and immutable raw data support reproducibility. Sensitive identifiers should be minimized, protected, and removed from analytical outputs where they are not needed.

6. Transformation and feature construction

Transformation should serve the analytical question rather than be performed automatically. Common operations include:

• parsing dates and extracting year, month, weekday, or elapsed time;
• converting units and currencies using an appropriate reference date;
• standardizing category labels and spelling;
• aggregating transactions into customer, product, or period measures;
• creating ratios, rates, counts, and indicator variables;
• scaling numeric fields for distance-based or gradient-based methods;
• applying logarithmic transformations to highly right-skewed positive variables.

For a positive value x, a log transformation can be written as z = ln(x). It is defined only for x > 0 and changes the scale; differences in z correspond to ratios on the original scale. If zero values are substantively meaningful, adding an arbitrary constant requires justification and may affect interpretation.

Standardization is commonly defined as:

where x̄ and s are calculated from the relevant training or reference data. The resulting variable has mean approximately zero and sample standard deviation approximately one in that reference data. Standardization does not make a variable normally distributed and is unnecessary for many tree-based methods, although it can be important for methods based on distances or regularization.

Categorical encoding must respect meaning and model requirements. One-hot encoding creates binary indicators for categories, usually omitting one reference category when an intercept is included in a linear model to avoid perfect multicollinearity. Ordered categories should not automatically be treated as equally spaced numeric values.

Feature construction creates new variables such as customer lifetime value, average order value, recency, or conversion rate. Each feature requires a clear time window and denominator. For example, a conversion rate is meaningful only when the population eligible for conversion is defined. Features must not use information unavailable at the intended prediction or decision time.

7. Exploratory data analysis

Exploratory data analysis (EDA) combines numerical summaries, visualization, and contextual reasoning. It is not a search for attractive charts; it is an investigation of distributions, relationships, temporal structure, subgroup differences, and data limitations.

Univariate exploration

For categorical variables, examine counts and proportions, including rare categories. For a category c, its sample proportion is:

where n(c) is the number of observations in category c and n is the total number of eligible observations. For numeric variables, use a histogram, density display, boxplot, and robust summaries. Always inspect the scale and units. A distribution that appears symmetric on a linear scale may be strongly skewed on a business-relevant scale.

Bivariate and multivariate exploration

Scatterplots can reveal association, curvature, clusters, and heteroscedasticity. Grouped boxplots or violin plots can compare distributions across categories, but group sizes should be shown or reported. A correlation coefficient summarizes linear association and does not establish causation; it may also be distorted by outliers or nonlinear relationships.

Time plots should include a correct date axis and identify changes in definitions, collection systems, or business policy. Seasonality, trend, missing periods, and sudden level shifts may be operational rather than substantive.

Heat maps, small multiples, and faceting can show subgroup patterns. However, examining many combinations increases the chance of finding an apparently interesting pattern by chance. EDA findings should therefore be treated as hypotheses or diagnostic evidence, not automatically as confirmatory results.

Visualization design is part of analytical integrity. Use truthful axes, explicit units, readable labels, appropriate aggregation, and colour schemes accessible to people with colour-vision deficiencies. Avoid three-dimensional effects, truncated axes that exaggerate differences, and charts that hide denominators.

8. Bias, measurement error, and fitness for purpose

Data can be technically clean yet substantively biased. Selection bias occurs when inclusion differs systematically from the target population. Survivorship bias excludes failed or inactive cases. Nonresponse bias occurs when respondents differ from nonrespondents. Historical data may encode institutional discrimination, while proxy variables may reproduce protected characteristics indirectly.

Measurement error arises when an observed value differs from the intended construct. A recorded “customer satisfaction score” may reflect survey wording, response mode, timing, and who chose to respond, not satisfaction alone. Classification error may be differential across groups, creating unequal error rates.

Distinguish reliability from validity. Reliability concerns consistency of measurement; validity concerns whether the measure represents the intended construct. A systematically miscalibrated sensor can be reliable but invalid.

A capstone should include a fitness-for-purpose statement addressing:

• target population and sampling process;
• unit of analysis and observation window;
• outcome and predictor definitions;
• missingness and exclusion rules;
• measurement limitations;
• known changes in systems or policies;
• implications for generalization and decision risk.

Ethically, analysts should apply data minimization, purpose limitation, access controls, retention rules, and appropriate anonymization. Direct identifiers should be separated from analysis data where possible. Re-identification risk can remain even after names are removed, especially when rare attributes are combined. Legal obligations vary by jurisdiction, so institutional policy and applicable privacy law must be followed. Fairness assessment should consider subgroup performance and the consequences of errors, not only overall accuracy.

9. Reproducible preparation and validation

A defensible workflow separates raw, intermediate, and analysis-ready data. It uses scripted transformations where possible, records package and software versions, fixes random seeds when randomness is involved, and stores a data-quality report. Manual spreadsheet edits are especially risky when they cannot be audited.

Validation should occur at several levels:

1. Schema checks: data types, required fields, permissible categories, and file structure.
2. Row-level checks: ranges, formats, and logical constraints.
3. Relational checks: key uniqueness, referential integrity, and expected join cardinality.
4. Aggregate checks: totals, counts, and reconciliations against trusted reports.
5. Analytical checks: distributions, missingness, and results before and after transformations.
6. Sensitivity checks: whether reasonable alternative cleaning decisions change conclusions.

A preparation log should state the original issue, decision, rule, number of affected records, rationale, and reviewer or source of authority. This turns cleaning from an invisible intervention into an auditable research method.

10. Illustrative case: preparing retail returns data

Background

The illustrative retailer “Northstar Home” wants to understand why product returns increased during 2024. The project combines order, shipment, product, customer-service, and returns data.

Problem

The initial extract contains 248,600 order lines, 12 fields, and several apparent contradictions. Product IDs differ in format between systems; some return dates precede shipment dates; customer IDs are missing for guest purchases; and revenue totals do not match the finance report.

Analysis

The team first defines the unit of analysis as an order line, not an order and not a customer. Profiling finds 1,420 exact duplicate rows, but investigation shows that 1,100 are legitimate repeated export records created by a system retry and 320 are genuine duplicate lines. The latter are removed using the source transaction ID and export version.

A join test reveals that the product table has multiple rows per product because historical prices are stored by effective date. Joining without a date condition duplicates order lines. The team instead matches each order line to the product-price record valid on the order date. This restores expected row counts and brings revenue within 0.3% of the finance report; the remaining difference is documented as tax and adjustment treatment.

Return rate is defined as returned units divided by shipped units for products shipped during the period. This denominator avoids treating unshipped orders as eligible returns. Guest purchases are retained for product-level analysis but excluded from customer-level repeat-purchase analysis because no stable customer identifier exists. Missing delivery dates are reported separately rather than imputed, since the missingness is concentrated in a carrier integration failure.

Exploration shows that returns are concentrated in two product categories and rose sharply after a packaging redesign. A time plot also shows a reporting-system change at the same date. The team therefore avoids claiming that the redesign caused the increase. It recommends a controlled packaging comparison and inspection of carrier damage codes.

Outcome

The final report includes a data dictionary, reconciliation table, missingness analysis, transformation script, anomaly log, and sensitivity analysis. The business receives a credible diagnostic rather than an unsupported causal conclusion.

Learning points

The case demonstrates that unit of analysis, temporal joins, denominator choice, missingness interpretation, and system-change detection are central to business analytics. Cleaning decisions can reveal or conceal business patterns, so they must be documented and challenged.

Challenges and limitations

No preparation process can guarantee accuracy when the underlying phenomenon is poorly measured or important records were never collected. Imputation depends on assumptions; anomaly rules can remove meaningful events; integration can create false precision; and visual patterns can encourage overinterpretation. Large datasets do not automatically solve these problems. Scale may increase the confidence with which a biased or misdefined measure is reported.

Automation and AI-assisted tools can accelerate profiling, schema inference, anomaly detection, and code generation. They must not replace domain review. Automated tools may infer incorrect types, silently alter dates, expose confidential data through external services, or reproduce historical bias. Analysts should inspect generated code, test edge cases, control data access, and retain human accountability for decisions.

Best-practice checklist

Before analysis, confirm that you can answer the following questions:

• What exactly does one row represent?
• What population and time period are covered?
• Which fields are keys, measures, categories, and timestamps?
• How are missing, not applicable, and unknown values distinguished?
• What rules identify invalid records and anomalies?
• What does each source mean by key business terms?
• Could any join multiply records unexpectedly?
• Were transformations calculated without future or test-set information?
• Have distributions and subgroup patterns been visualized?
• Could selection, measurement, or historical bias affect conclusions?
• Can another analyst reproduce every material preparation step?

Chapter Summary

Data preparation and exploratory analysis establish whether data are suitable for a stated business or research purpose. A rigorous workflow begins by defining the unit of analysis and profiling structure, quality, provenance, and meaning. Missingness must be assessed by pattern and mechanism rather than handled with a universal rule. Duplicates and anomalies require contextual investigation, while integration demands careful attention to keys, granularity, temporal validity, and lineage.

Transformations should be interpretable, time-appropriate, and justified. EDA uses numerical summaries and well-designed visualizations to examine distributions, relationships, trends, subgroup differences, and inconsistencies. Clean data may still be biased or invalid as measures of the intended construct. Reproducible scripts, validation checks, sensitivity analysis, privacy safeguards, and explicit documentation make preparation defensible. The central professional principle is simple: every material data decision should be explainable, auditable, and connected to the analytical purpose.

Key Terms

Data profiling: Systematic description of data structure, content, quality, and provenance.

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

Data dictionary: Documentation of fields, definitions, types, units, and coding rules.

Completeness: The extent to which required values are present.

Validity: Conformity to permitted formats, ranges, and rules.

Accuracy: Closeness of a recorded value to the real-world value.

Missing completely at random: Missingness unrelated to observed or unobserved values.

Missing at random: Missingness explainable by observed information under a specified model.

Missing not at random: Missingness related to unobserved values or factors.

Anomaly: An observation that departs substantially from an expected pattern.

Candidate key: A field or combination of fields that could uniquely identify records.

Referential integrity: Consistency between related keys across tables.

Data lineage: A record of data origins, transformations, and movement.

Data leakage: Use of information unavailable at the intended prediction or decision time.

Standardization: Rescaling a variable using a reference mean and standard deviation.

Exploratory data analysis: Investigation of data patterns using summaries, visualization, and context.

Measurement error: Difference between an observed measure and the intended construct or value.

Selection bias: Systematic difference between observed cases and the target population.

Fitness for purpose: Suitability of data for a specified analytical use.

Sensitivity analysis: Examination of how conclusions change under reasonable alternative assumptions.

Review Questions

Short Answer Questions

1. Why must the unit of analysis be defined before joining datasets?
2. Distinguish completeness, validity, and accuracy.
3. What is the difference between MCAR, MAR, and MNAR missingness?
4. Why is an IQR outlier rule a screening device rather than proof of an error?
5. What is data leakage, and why is it problematic in predictive analytics?

Long Answer Questions

1. Explain a defensible workflow for profiling and cleaning a dataset from raw extraction to analysis-ready form.
2. Compare deletion, single imputation, missingness indicators, and multiple imputation. Discuss their assumptions and limitations.
3. Explain how incorrect join granularity can inflate business metrics. Illustrate with a customer, order, and support-contact example.
4. Discuss how visualization can reveal both substantive business patterns and data-quality problems.
5. Explain why technically clean data may still produce biased or invalid conclusions.

Analytical / Application Questions

1. A sales table contains one row per transaction, while a customer table contains three historical records for some customers. Design checks and a joining strategy that avoids duplicated sales.
2. A delivery-time field is missing for 4% of records overall but 28% for one carrier after a system upgrade. Propose an investigation and treatment plan, including how you would report limitations.
3. A returns rate rises immediately after a product redesign, but a reporting-system change occurs on the same date. Explain how EDA, validation, and further study could distinguish competing explanations.

Multiple Choice Questions with Answers

Question 1

Which question should be answered first when preparing a dataset?

A. What chart style should be used?

B. What does one row represent?

C. Which records should be deleted?

D. Which machine-learning algorithm is most accurate?

Correct Answer: B. What does one row represent?

Question 2

Which missingness mechanism describes missingness related to an observed variable?

A. MCAR

B. MAR

C. MNAR

D. Structural validity

Correct Answer: B. MAR

Question 3

For a numeric variable, what does IQR equal?

A. Mean minus median

B. Q₁ plus Q₃

C. Q₃ minus Q₁

D. Maximum minus minimum only

Correct Answer: C. Q₃ minus Q₁

Question 4

What is a major danger of joining two event-level tables using only customer ID?

A. All dates become standardized

B. Many-to-many multiplication can inflate totals

C. Missingness is automatically removed

D. Categorical variables become numeric

Correct Answer: B. Many-to-many multiplication can inflate totals

Question 5

Which practice helps prevent data leakage?

A. Computing imputation values using the test set

B. Using future outcomes as predictors

C. Fitting preparation steps within the training data

D. Deleting all unusual observations

Correct Answer: C. Fitting preparation steps within the training data

Question 6

What does standardization usually produce in the reference dataset?

A. Values restricted between 0 and 1

B. Approximately mean zero and standard deviation one

C. A guaranteed normal distribution

D. Only positive values

Correct Answer: B. Approximately mean zero and standard deviation one

Question 7

Which statement about an outlier is correct?

A. Every outlier is a data-entry error

B. Every outlier must be deleted

C. An outlier may be a valid and important observation

D. Outliers cannot affect business conclusions

Correct Answer: C. An outlier may be a valid and important observation

Question 8

Which quality dimension concerns whether values conform to permitted ranges and formats?

A. Validity

B. Timeliness

C. Uniqueness

D. Confidentiality

Correct Answer: A. Validity

Question 9

Why should a return rate have a clearly defined denominator?

A. To make the chart decorative

B. Because the eligible population determines the meaning of the rate

C. To eliminate all measurement error

D. Because denominators are never affected by time

Correct Answer: B. Because the eligible population determines the meaning of the rate

Question 10

Which document records data origins and transformations?

A. Data lineage record

B. Marketing calendar

C. Colour palette

D. Sampling histogram

Correct Answer: A. Data lineage record