Data Preparation and Exploratory Data Analysis

Introduction

Statistical analysis is only as reliable as the data on which it is based. In business settings, data commonly arrive from transaction systems, spreadsheets, surveys, websites, sensors and third-party providers. These sources may use different formats, definitions, units and identifiers. Records may be duplicated, values may be missing, and variables may contain errors or unusual observations. Data preparation converts such raw inputs into an analyzable dataset. Exploratory data analysis (EDA) then examines the prepared data to understand its structure, quality, distributions, anomalies and relationships before formal modelling or decision-making.

Data preparation is not merely a technical cleaning exercise. Choices about deleting records, imputing missing values, recoding categories and treating outliers can affect statistical conclusions. Analysts must therefore document their decisions, preserve an original copy of the data and avoid changing observations simply because they appear inconvenient.

EDA is primarily investigative. It helps analysts generate questions and identify possible patterns; it does not, by itself, prove causation or establish that a pattern will continue in the future.

Learning Objectives

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

• distinguish observations, variables, records and data types;
• assess data quality and identify common preparation problems;
• clean, code, transform and integrate datasets responsibly;
• construct frequency tables and cross-tabulations;
• select and interpret suitable statistical visualizations;
• describe distributions using appropriate numerical summaries;
• identify anomalies and potential relationships without overstating conclusions; and
• communicate an auditable and ethically defensible EDA process.

1. The Analytical Dataset

An analytical dataset is usually organized as a rectangular table. Each row represents an observation, such as a customer, order, employee or day. Each column represents a variable, such as age, product category, revenue or satisfaction score. A single cell contains one value for one variable and one observation.

The unit of observation must be established before analysis. For example, a sales table may record one row per transaction, while a customer table records one row per customer. Combining these tables without considering their units can produce incorrect results. If a customer appears in ten transaction rows, joining customer attributes to transactions repeats the customer information ten times. This may be appropriate for transaction-level revenue analysis but inappropriate for estimating the number of customers.

Important data types include:

A variable coded as 1, 2 and 3 is not automatically numerical in a meaningful arithmetic sense. If the values represent bronze, silver and gold membership, they are ordinal categories. Calculating an average membership code may be misleading unless the coding has a justified quantitative interpretation.

2. A Structured Data-Preparation Workflow

A professional workflow normally includes the following stages:

1. define the analytical question and unit of observation;
2. acquire and inventory the source data;
3. preserve raw data and record provenance;
4. profile structure, types, ranges, missingness and duplicates;
5. standardize formats and labels;
6. correct, remove or document invalid records;
7. transform and derive variables;
8. integrate datasets using validated keys;
9. perform EDA and quality checks; and
10. save a reproducible, documented analytical dataset.

This workflow is iterative. EDA may reveal that a variable was incorrectly coded or that a join created duplicate records. The analyst then returns to the relevant preparation stage and reassesses the result.

Data profiling

Profiling describes the data before changes are made. It should include the number of rows and columns, variable names, data types, distinct values, minimum and maximum values, missing-value counts, duplicate counts and representative records. For a numerical variable x with n observed values, the arithmetic mean is:

Here, xᵢ is the value for observation i, x̄ is the sample mean and n is the number of non-missing observations. The mean is meaningful when the values are quantitative and comparable; it is not appropriate for nominal labels.

Missing data

Missingness may arise because a question was not answered, a sensor failed, a field was not required or a value was lost during integration. Missing values should not automatically be replaced with zero. A missing sales amount is different from a recorded sale with zero revenue.

Three useful descriptions are:

• MCAR (missing completely at random): missingness is unrelated to observed and unobserved values;
• MAR (missing at random): missingness is related to observed variables but not, after accounting for them, to the missing value itself; and
• MNAR (missing not at random): missingness depends on the unobserved value or another unmeasured mechanism.

At diploma level, practical approaches include retaining missing values, deleting records when justified, imputing a suitable statistic, or adding a missing-category label for categorical data. Mean imputation can reduce variation and distort relationships, particularly when many values are missing. Median imputation is often more resistant to skewed numerical data, but it also understates uncertainty. The method, proportion missing and likely mechanism should be reported.

Duplicates and invalid values

Duplicate detection should distinguish an exact duplicate row from a legitimate repeated event. For example, two identical online orders may be a duplicated import, or they may be two genuine orders placed seconds apart. A unique transaction identifier, timestamp and source-system record can help determine the correct interpretation.

Validity rules should be defined from domain knowledge. Examples include:

• quantity must be a non-negative integer;
• a percentage should normally lie between 0 and 100;
• a delivery date should not precede the order date; and
• a customer age should fall within a plausible, documented range.

A value that violates a rule should be investigated. It should not be silently changed to a convenient value.

Standardization and coding

Text categories often differ because of spelling, capitalization, punctuation or abbreviations. “United Kingdom”, “UK” and “U.K.” may need to be mapped to one controlled category. A coding dictionary should specify the original value, standardized value and rationale.

Binary variables should have explicit meanings. For example, 1 = subscribed and 0 = not subscribed is preferable to unexplained values such as Y, N, yes, no and blank. Category labels should remain available in documentation so that results can be interpreted correctly.

Transformations

Transformations change the representation or scale of a variable. Common examples include extracting year or month from a date, converting dollars to thousands of dollars, calculating profit from revenue and cost, and creating a logarithmic variable for a strongly right-skewed measure.

A percentage change from an old value V₀ to a new value V₁ is:

This formula assumes V₀ is non-zero and that the two values have the same unit and comparable definitions. If V₀ is negative or close to zero, interpretation requires special care.

For a numerical variable x, a z-score is:

where s is the sample standard deviation. The z-score is dimensionless and describes how far an observation is from the sample mean in standard-deviation units. It is useful for comparison across variables, but it should not be treated as proof that an observation is erroneous. In approximately bell-shaped data, large absolute z-scores may warrant investigation; in skewed data, they may be natural.

Integration and joins

Integration combines datasets using a key. A one-to-one join matches one record in one table to one record in another. A one-to-many join matches one entity, such as a customer, to many transactions. A many-to-many join can multiply rows and is a frequent source of inflated totals.

Before joining, analysts should check:

• whether keys are unique where expected;
• whether key formats match;
• how unmatched records will be handled;
• whether the join changes the unit of observation; and
• whether row counts and totals remain plausible afterward.

A reconciliation check might compare total transaction revenue before and after a customer-information join. Unexpected changes indicate that the join requires investigation.

3. Frequency Tables and Cross-Tabulations

A frequency table summarizes how often values or categories occur. If category j occurs fⱼ times in a dataset of n observations, its relative frequency is:

The percentage frequency is pⱼ × 100%. Frequencies should sum to n, while relative frequencies should sum to 1, apart from rounding.

For numerical variables with many distinct values, values are often grouped into intervals. Intervals must be mutually exclusive and collectively exhaustive. For example, age groups of 18–24, 25–34 and 35–44 should not overlap and should specify how boundary values are treated.

A cross-tabulation, or contingency table, summarizes two categorical variables. For row i and column j, the cell count nᵢⱼ is the number of observations having both categories. Row percentages answer questions such as “Within each region, what percentage used online payment?” Column percentages answer a different question: “Among online-payment users, what percentage came from each region?” Analysts must state which denominator is being used.

For two categorical variables, an association can be explored using the chi-square statistic:

Here, Oᵢⱼ is the observed cell count and Eᵢⱼ is the expected count under independence. For a table with row total Rᵢ, column total Cⱼ and grand total N:

The chi-square procedure assumes independent observations and sufficiently large expected counts for the chosen approximation. It indicates evidence of an association, not causation. Practical significance and the size of differences should also be considered.

4. Numerical Summaries and Distributions

A distribution describes how values are spread. Important features include centre, variation, shape and unusual observations.

The median is the middle ordered value, or the average of the two middle values when n is even. The range is maximum minus minimum. The sample variance is:

The sample standard deviation is s = √s². The denominator n − 1 is used when estimating population variability from a sample under standard statistical practice. Standard deviation is expressed in the same units as x, whereas variance is expressed in squared units.

Quartiles divide ordered data into four parts. 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.5IQR or above Q₃ + 1.5IQR as potential outliers. This is a screening rule, not a universal error rule, and quartile calculation conventions can differ slightly between software systems.

The mean and standard deviation are sensitive to extreme values. The median and IQR are more resistant. In a right-skewed income distribution, for example, a small number of very high incomes can make the mean substantially larger than the median.

5. Visual Exploratory Analysis

Visualization makes structure visible and supports communication. The appropriate chart depends on variable type and analytical purpose.

Bar-chart bars represent separate categories and normally have gaps. Histograms group continuous numerical values into adjacent intervals. Changing bin widths can alter the apparent shape, so conclusions should not depend on one arbitrary choice.

A box plot displays the median, first and third quartiles and possible outliers according to the selected rule. It is efficient for comparing groups but does not display every observation. A scatter plot can reveal positive, negative, nonlinear or weak association. Overplotting may conceal points when many observations share similar coordinates; transparency, sampling or aggregation may help.

A correlation coefficient summarizes linear association between two numerical variables. Pearson’s sample correlation is:

Provided both variables have non-zero variation, r lies between −1 and 1. Values near 1 indicate strong positive linear association, values near −1 strong negative linear association and values near 0 little linear association. Correlation can be distorted by outliers, hidden subgroups and nonlinear relationships. It does not establish causation.

Good visualizations include informative titles, clearly labelled axes, units, sensible scales and source notes. Three-dimensional effects, truncated axes and excessive decoration can mislead viewers.

6. Identifying Patterns and Anomalies Without Overstatement

An anomaly is an observation or pattern that differs from expectations. It may indicate data entry error, fraud, a system problem, a rare but genuine event or a change in the underlying process. Analysts should investigate anomalies using source records and domain knowledge rather than automatically deleting them.

A useful investigation asks:

1. Is the value technically valid?
2. Is it plausible in the business context?
3. Does it occur repeatedly or only once?
4. Is it associated with a particular source, time, location or process?
5. Would removing it change the conclusion substantially?

Patterns may be descriptive rather than predictive. A rise in sales during a promotion is a temporal association, but it may also reflect seasonality, competitor activity or changes in product availability. Segment comparisons can be affected by confounding variables. For instance, an observed relationship between training and performance may reflect employee experience, because experienced employees are more likely to receive advanced training.

The analyst should use cautious language: “is associated with,” “coincides with,” “suggests” and “warrants further investigation.” Terms such as “causes,” “proves” and “will result in” require stronger designs and evidence.

7. Worked Example: Retail Order Data

Suppose a retailer has 1,000 order records. The preparation review finds 18 duplicate imports, 12 missing delivery times, inconsistent region labels and five negative quantities. Investigation shows that the negative quantities represent legitimate product returns, not errors.

The analyst first preserves the raw file and documents the unit of observation as one order line. The 18 exact duplicate imports are removed after confirmation against source-system identifiers. The five return lines are retained but coded with a return indicator, because removing them would overstate net sales. Region labels are mapped to a controlled list. Missing delivery times are retained as missing and reported separately; they are not replaced by zero.

After cleaning, the dataset contains 982 records. A frequency table shows 590 standard orders and 392 return lines. The return proportion is:

This high proportion would require business investigation, but it may reflect that the dataset is an order-line extract rather than a customer-order extract. A cross-tabulation by region and return indicator can show whether returns are concentrated in particular regions. A box plot of delivery time by shipping method can reveal differences in spread, while a scatter plot of order value and delivery time can identify unusual combinations.

The analyst should not conclude that a region causes returns merely because its percentage is higher. Differences may reflect product mix, customer type, distance, promotion or reporting completeness. The next step might be a validated investigation using product category, shipping distance and customer segment.

8. Illustrative Case: Improving Subscription Retention Analysis

Background

A fictional streaming company wants to understand why monthly subscriber retention appears to have declined. Its data team receives a customer table, a payment table and a support-ticket table.

Problem

The first dashboard reports more cancellations than the finance system. Investigation identifies three issues: customer IDs have different formats across systems, cancelled accounts can appear more than once in the payment table, and missing cancellation dates are coded as “0”.

Analysis and approach

The team establishes the customer as the unit of observation for retention reporting. It standardizes IDs, converts “0” cancellation dates to missing, and creates a separate transaction-level table for payment analysis. A validated one-to-many join links each customer to transactions without duplicating customer-level attributes. Duplicate cancellation events are examined using event timestamps and retained only when they represent distinct status changes.

EDA includes a frequency table of cancellation reason, a line chart of monthly cancellations, box plots of customer tenure by cancellation status and a cross-tabulation of plan type by cancellation status. The team reports both counts and within-plan percentages, because plan sizes differ. It also checks whether missing cancellation dates are concentrated in a particular acquisition channel.

Outcome

After preparation, the apparent decline is partly explained by a change in the cancellation-recording process. The remaining increase is concentrated among newly acquired customers on a particular plan. The analysis suggests a targeted review of onboarding and plan communication, but it does not prove that the plan causes cancellation.

Learning points

The case demonstrates that data definitions, units of observation and join logic can materially change results. It also shows why missingness, duplicate events and denominators must be made visible to decision-makers.

9. Governance, Ethics and Reproducibility

Data preparation can affect individuals and organizations. Analysts should apply data minimization, restrict access to sensitive fields and follow relevant privacy and data-protection requirements. Personally identifiable information should not be included in exploratory outputs unless necessary and authorized. Small subgroup counts may create re-identification risk.

Fairness must also be considered. Removing records disproportionately from one group, imputing missing values differently across groups or using historical labels can create unequal effects. Analysts should examine data coverage and missingness by relevant demographic or operational groups where lawful and appropriate.

Reproducibility requires a data dictionary, source description, transformation log, validation checks, code or documented procedures, and versioned outputs. The raw data should be read-only. Any manual correction should include the original value, replacement value, reason, date and author. Automated tools and AI-assisted coding can accelerate profiling and transformation, but generated code must be reviewed, tested and checked against privacy policies. An AI system should not be trusted to infer business definitions without human validation.

Best-Practice Checklist

Before releasing an EDA report, confirm that:

• the unit of observation is explicit;
• source data are preserved;
• variable types and units are correct;
• missing values, duplicates and invalid records are documented;
• joins have been validated and row counts reconciled;
• transformations are reproducible;
• charts show clear labels, units and denominators;
• anomalies have been investigated rather than automatically deleted;
• conclusions distinguish association from causation; and
• privacy, fairness and access controls have been considered.

Chapter Summary

Data preparation creates a trustworthy foundation for statistical analysis. It begins with understanding the unit of observation, variables, data types and source systems. Profiling reveals missing values, duplicates, invalid entries and inconsistent coding. Cleaning decisions must be justified, documented and sensitive to the meaning of the data. Transformations and joins should preserve interpretability and be checked through reconciliation.

Frequency tables summarize categorical distributions, while cross-tabulations compare categories using clearly stated denominators. Numerical summaries such as the mean, median, standard deviation and IQR describe centre and spread, but their usefulness depends on distributional shape and resistance to extreme values. Histograms, bar charts, box plots, line charts and scatter plots provide complementary views of data.

EDA helps identify patterns, anomalies and possible relationships. However, exploratory evidence is not proof of causation. Sound practice combines statistical reasoning, domain knowledge, ethical governance, reproducible documentation and cautious communication.

Key Terms

Unit of observation: The entity or event represented by one row of a dataset.

Variable: A characteristic recorded for each observation.

Data profiling: Systematic examination of structure, types, ranges, missingness and duplicates.

Missing data: Values that are unavailable, unrecorded or not applicable.

MCAR: Missingness unrelated to observed or unobserved data values.

MAR: Missingness explainable by observed variables after accounting for them.

MNAR: Missingness related to the unobserved value or another unmeasured mechanism.

Duplicate: A repeated record that may represent an import error or a legitimate repeated event.

Data validation: Checking whether values satisfy technical and domain rules.

Transformation: A change to the representation, scale or construction of a variable.

Join: An operation that combines tables using matching keys.

Frequency table: A summary of counts or proportions for values or categories.

Cross-tabulation: A table showing the joint distribution of two categorical variables.

Histogram: A chart showing the distribution of numerical values grouped into intervals.

Box plot: A display of quartiles, median and potential outliers.

Outlier: An observation unusually distant from the rest of the data or expectation.

IQR: The difference between the third and first quartiles.

Correlation: A measure of linear association between two numerical variables.

Confounding: A situation in which another variable helps explain an observed relationship.

Reproducibility: The ability to repeat an analysis using documented data and procedures.

Review Questions

Short Answer Questions

1. What is meant by the unit of observation, and why must it be established before joining tables?

1. Distinguish nominal, ordinal and numerical variables.

1. Why should a missing sales amount not automatically be coded as zero?

1. What information does an IQR provide?

1. Why does correlation not establish causation?

Long Answer Questions

1. Explain a complete data-preparation workflow from raw-data acquisition to a documented analytical dataset.

1. Compare mean, median, standard deviation and IQR as descriptions of a numerical distribution.

1. Explain how frequency tables and cross-tabulations support business analysis, including the importance of denominators.

1. Discuss the risks of incorrect joins and propose checks that can detect row multiplication.

1. Describe ethical and governance considerations when preparing data about customers or employees.

Analytical / Application Questions

1. A dataset contains customer age values of 22, 24, 25, 26, 27 and 120. Propose an investigation and explain why the value 120 should not be deleted automatically.

1. A marketing report states that customers receiving a promotional email spend more than customers who do not receive one. Identify at least three alternative explanations and describe EDA that could investigate them.

1. A retailer has 800 transactions, of which 120 are returns. One region has 50 returns among 200 transactions, while another has 20 returns among 200 transactions. Calculate the return percentages and explain why the comparison is descriptive rather than proof that region causes returns.

Multiple Choice Questions with Answers

Question 1

Which statement best describes the unit of observation?

A. The number of columns in a dataset

B. The entity or event represented by one row

C. The number of missing values in a variable

D. The software used to analyze data

Correct Answer: B. The entity or event represented by one row

Question 2

Which measure is generally most resistant to extreme values?

A. Mean

B. Variance

C. Median

D. Range

Correct Answer: C. Median

Question 3

What is the main purpose of a histogram?

A. To display the distribution of a numerical variable

B. To join two datasets

C. To identify a primary key

D. To display only nominal categories

Correct Answer: A. To display the distribution of a numerical variable

Question 4

If a category occurs 45 times in a dataset of 300 observations, what is its percentage frequency?

A. 6.7%

B. 15%

C. 45%

D. 255%

Correct Answer: B. 15%

Question 5

What is a major risk of an uncontrolled many-to-many join?

A. All missing values become zero

B. Numerical variables become categorical

C. Rows and totals may be multiplied

D. The dataset automatically becomes representative

Correct Answer: C. Rows and totals may be multiplied

Question 6

Which statement about an outlier is correct?

A. It must always be deleted

B. It is always a data-entry error

C. It should be investigated in context

D. It proves that the mean is incorrect

Correct Answer: C. It should be investigated in context

Question 7

In a cross-tabulation, a row percentage uses which denominator?

A. The total for the relevant row

B. The total for the relevant column

C. The grand total squared

D. The number of variables

Correct Answer: A. The total for the relevant row

Question 8

What does a correlation close to −1 generally indicate?

A. Strong negative linear association

B. No possible relationship of any kind

C. Strong positive causal effect

D. Equal numerical means

Correct Answer: A. Strong negative linear association

Question 9

Which chart is most suitable for examining the relationship between two numerical variables?

A. Pie chart

B. Scatter plot

C. Bar chart of one category

D. Frequency table only

Correct Answer: B. Scatter plot

Question 10

Why should analysts use cautious language when reporting EDA findings?

A. EDA always produces no information

B. Exploratory associations do not by themselves prove causation

C. Charts cannot show distributions

D. Numerical data cannot be summarized

Correct Answer: B. Exploratory associations do not by themselves prove causation