Preparing Analysis-Ready Datasets

Preparing Analysis-Ready Datasets

An analysis-ready dataset is a dataset that has been carefully structured,
cleaned, transformed, validated, documented, and prepared so that it can be
used confidently for analysis, reporting, visualization, dashboards, and
decision-making.

A dataset is not automatically ready for analysis simply because it can be
opened in Excel, imported into Python, or stored inside a database. Good data
analysis begins with good data preparation.

Learning Objectives

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

• Explain what an analysis-ready dataset is.
• Distinguish between raw, cleaned, transformed, validated, and analysis-ready data.
• Apply tidy-data principles.
• Choose appropriate columns, rows, identifiers, and data types.
• Prepare text, dates, numbers, categories, and Boolean fields.
• Handle missing values, duplicates, outliers, and invalid records.
• Use Excel and Python to prepare datasets.
• Understand how SQL can be used to produce analysis-ready data.
• Document transformations and maintain reproducibility.
• Perform a final data-quality check before analysis.

1. What Does Analysis-Ready Mean?

Analysis-ready means that the structure and quality of the dataset are
appropriate for the analytical task you intend to perform.

An analysis-ready dataset should not contain avoidable structural problems
that could produce misleading calculations, charts, models, or conclusions.

For example, imagine a student dataset containing:

• Student Name
• Course
• Gender
• Date Registered
• Age
• Payment Status
• Score

Before analysis, you should confirm that names are consistently stored,
courses use approved categories, dates are valid dates, ages are numeric,
payment statuses use consistent values, and scores fall within the expected
range.

2. Raw Data vs Clean Data vs Analysis-Ready Data

Raw Data

Raw data is the data collected directly from its original source. It may
contain missing values, duplicate records, inconsistent formats, spelling
differences, unnecessary fields, invalid values, and other quality problems.

Cleaned Data

Cleaned data has had obvious quality problems investigated and corrected,
removed, standardized, or flagged.

Transformed Data

Transformed data has been changed into a structure or representation that is
more useful for analysis.

Examples include converting dates, creating categories, calculating totals,
standardizing units, or creating derived variables.

Validated Data

Validated data has been checked against defined rules to determine whether
values and relationships are acceptable.

Analysis-Ready Data

Analysis-ready data combines appropriate cleaning, transformation,
validation, structure, documentation, and preparation for a specific
analytical purpose.

3. Characteristics of an Analysis-Ready Dataset

A strong analysis-ready dataset normally has:

• A correct and understandable structure.
• Clear column names.
• Correct data types.
• Consistent formats.
• Appropriate treatment of missing values.
• Duplicates investigated and addressed.
• Outliers investigated.
• Validated categories.
• Valid relationships between related fields.
• No unnecessary columns.
• No unnecessary records.
• Reliable unique identifiers.
• Documented assumptions.
• Reproducible transformations.
• A documented cleaning and preparation process.

4. The Tidy Data Principle

One of the most useful ideas in data analysis is the concept of tidy data.
A tidy dataset is structured so that each part of the dataset has a clear
meaning.

The basic principles are:

• Each variable should have its own column.
• Each observation should have its own row.
• Each observational unit should have an appropriate table or dataset.

For example, if a dataset records students, each row might represent one
student registration and each column represents a variable such as name,
course, age, registration date, or payment status.

5. Rows and Columns

Before analysis, determine what one row represents.

A row could represent:

• One student.
• One course registration.
• One sale.
• One customer.
• One transaction.
• One employee.
• One website visit.

If the meaning of a row is unclear, analysis can become unreliable.

Similarly, every column should represent a meaningful variable rather than
a mixture of unrelated information.

6. Unique Identifiers

A unique identifier is a field that allows individual records to be
distinguished from one another.

Examples include:

• Student ID
• Customer ID
• Transaction ID
• Employee ID
• Registration ID

A good identifier should be stable and should not unnecessarily depend on
changing descriptive information such as a person’s name.

7. Primary Keys and Unique IDs

In relational databases, a primary key uniquely identifies a record in a
table.

For example:

student_id
1001
1002
1003
1004

If two records unexpectedly have the same identifier, the analyst should
investigate whether they represent duplicate records, a legitimate
relationship, or a data-entry problem.

8. Clear Column Naming

Column names should be descriptive, consistent, and easy to work with.

Prefer names such as:

student_id
first_name
registration_date
payment_status
course_name
exam_score

Avoid ambiguous names such as:

data1
info
misc
field2
new_column

Consistent naming becomes especially important when working with SQL,
Python, dashboards, and automated data pipelines.

9. Data Naming Conventions

Choose a naming convention and apply it consistently.

Common approaches include snake_case:

registration_date
payment_status
student_id

or other clearly defined conventions.
The most important rule is consistency.

10. Data Dictionary

A data dictionary explains what each field means.

A useful data dictionary can contain:

• Column name.
• Description.
• Data type.
• Allowed values.
• Unit of measurement.
• Whether the field is required.
• Example value.

Example:

11. Metadata

Metadata is information that describes the data itself.

Useful metadata may include the source, collection date, owner, update
frequency, units, definitions, transformation history, and quality notes.

12. Correct Data Types

Every field should have an appropriate data type.

Common types include:

• Text/string.
• Integer.
• Decimal/float.
• Date.
• Date and time.
• Boolean.
• Categorical values.

13. Preparing Date Fields

Dates are particularly important because analysts frequently group data by
day, week, month, quarter, and year.

A dataset should use a consistent date representation and avoid mixing
different interpretations.

For example, these values can be dangerous when mixed:

03/04/2026
04/03/2026
2026-03-04
March 4, 2026

The intended date format and interpretation should be clearly defined.

14. Preparing Numeric Fields

Numeric fields should contain numeric values rather than numbers mixed with
unnecessary text.

For example, avoid storing:

₦150,000
150,000 Naira
N150000

inside a field that is expected to contain numeric values.

The currency should preferably be represented separately through metadata,
documentation, or a dedicated currency field where appropriate.

15. Preparing Categorical Fields

Categorical fields should use controlled and consistent values.

For example, instead of mixing:

Paid
paid
PAID
Payment Made
Yes

define an approved vocabulary such as:

Paid
Unpaid
Pending

This makes grouping, filtering, Pivot Tables, charts, and calculations more
reliable.

16. Preparing Boolean Fields

Boolean fields normally represent two states such as true/false or
yes/no.

Do not casually mix values such as:

Yes
Y
TRUE
1
Available

unless those values have been intentionally standardized and documented.

17. Cleaning Text Fields

Text often requires normalization before analysis.

Common problems include leading spaces, trailing spaces, inconsistent
capitalization, hidden characters, spelling variations, and inconsistent
abbreviations.

Useful operations include:

• Trim unnecessary spaces.
• Remove unwanted characters.
• Standardize capitalization.
• Correct approved spelling variations.
• Map abbreviations to standard categories.

18. Whitespace

The values Agbor and Agbor may appear identical
to a person but can behave differently in some analytical operations.

Whitespace should therefore be investigated and standardized where
appropriate.

19. Casing

Names and categories can appear in different cases:

Excel
EXCEL
excel
ExCeL

A defined standard can make these values easier to group and compare.

20. Standardizing Categories

Standardization may involve mapping multiple source values to a single
approved category.

For example:

Male
M
MALE
m

could be mapped to:

Male

The mapping rule should be documented.

21. Missing-Value Strategy

Missing values should not simply be deleted without understanding why they
are missing.

Possible strategies include:

• Keep the missing value.
• Replace it with a justified value.
• Impute it using an appropriate method.
• Remove the record if justified.
• Use a separate category such as “Unknown” where appropriate.
• Flag the record for further investigation.

22. Duplicates

Duplicate records can distort counts, totals, averages, customer numbers,
student registrations, and other calculations.

Before removing duplicates, determine why they exist and which record should
be retained.

Possible rules include keeping the latest record, the most complete record,
or the record from the most trusted source.

23. Outliers

Outliers should be investigated rather than automatically deleted.

An unusually high or low value may be an error, but it may also represent a
legitimate and important observation.

The final dataset should document how important outliers were treated.

24. Data Validation

Validation confirms that the prepared dataset satisfies defined rules.

Examples include:

• Scores must be between 0 and 100.
• Age must be within a reasonable range.
• Registration dates cannot be impossible dates.
• Required IDs must not be blank.
• Payment status must use approved values.
• Unique IDs should not be duplicated.

25. Data Transformation

Transformation changes data into a more useful representation.

Examples include:

• Converting dates into standard formats.
• Converting text numbers into numeric values.
• Changing units.
• Creating categories.
• Calculating totals.
• Creating age groups.
• Creating month or year fields from dates.

26. Derived and Calculated Columns

A derived column is calculated from one or more existing fields.

For example, if a sales dataset contains quantity and unit price, you can
calculate revenue:

revenue = quantity × unit_price

Derived columns should have clear names and documented formulas.

27. Do Not Destroy the Raw Data

One of the most important professional practices is to preserve the original
data.

Instead of overwriting the raw dataset, maintain separate versions or
layers.

A useful structure is:

Raw Data
   ↓
Staging Data
   ↓
Clean Data
   ↓
Validated Data
   ↓
Analysis-Ready Data

This makes it possible to trace how the final dataset was produced.

28. Filtering Unnecessary Records

Not every record in a source dataset belongs in the analysis.

For example, a sales analysis might exclude test transactions, cancelled
transactions, or records outside the required reporting period if the
analytical definition requires it.

Every filtering decision should have a reason.

29. Selecting Useful Columns

Remove or exclude fields that are unnecessary for the analytical question,
especially sensitive or irrelevant information.

However, do not remove a field merely because it does not appear useful at
first glance. Understand its analytical and relational importance first.

30. Wide and Long Data

Data can be represented in wide or long formats.

Wide data may contain separate columns for different periods or categories,
while long data stores those categories as values in rows.

The appropriate structure depends on the analytical tool and task.

31. Joins and Merges

Combining datasets can create powerful analytical datasets, but incorrect
joins can also multiply records and produce inaccurate totals.

Before merging datasets, understand:

• What each table represents.
• Which field connects the tables.
• Whether the key is unique.
• Whether the relationship is one-to-one, one-to-many, or many-to-many.
• Whether the join can create duplicate records.

32. Aggregation

Sometimes the analysis-ready dataset should contain aggregated information.

For example, transaction-level sales data can be aggregated by:

• Product.
• Month.
• Branch.
• Salesperson.
• Customer segment.

Aggregation should only be performed when it matches the analytical
objective.

33. Normalization and Denormalization

Normalization is a database design principle that reduces unnecessary
duplication and separates related information into appropriate tables.

Denormalization intentionally combines information when convenient for
reporting or analytical workloads.

Data analysts should understand both concepts because the structure used for
transactional systems may differ from the structure used for reporting.

34. Preparing Data in Excel

Excel is widely used for data preparation, particularly for small and
medium-sized datasets.

Useful Excel features include:

• Excel Tables.
• Filters.
• Sort.
• Remove Duplicates.
• Data Validation.
• Find and Replace.
• Text functions.
• Date functions.
• Conditional formatting.
• Power Query.
• Structured references.

35. Excel Tables

Converting a dataset into an Excel Table provides useful filtering,
structured references, automatic expansion, and easier formula management.

A clean table should normally have one header row and consistent columns.

36. Excel Power Query

Power Query is especially useful when data preparation needs to be repeated.
It can connect to data sources, remove unnecessary columns, change data
types, split columns, replace values, remove duplicates, merge tables,
append tables, and perform many other transformations.

A major advantage is that transformation steps can be saved and refreshed
rather than manually repeated each time new data arrives.

37. Useful Excel Cleaning Operations

Examples include:

=TRIM(A2)

=UPPER(A2)

=LOWER(A2)

=PROPER(A2)

=IFERROR(A2,"")

=COUNTIF(A:A,A2)

These functions can help with text standardization, error handling, and
duplicate detection.

38. Preparing Data with Python and pandas

Python is useful when datasets become larger, preparation needs to be
repeatable, or the workflow requires automation.

The pandas library provides powerful tools for manipulating tabular data.

39. Loading Data with pandas

import pandas as pd

df = pd.read_csv("students.csv")

print(df.head())

The read_csv() function loads a CSV file into a pandas
DataFrame.

40. Create a Working Copy

Creating a copy helps protect the original DataFrame during experimentation.

clean_df = df.copy()

41. Rename Columns

clean_df = clean_df.rename(columns={
    "Student Name": "student_name",
    "Registration Date": "registration_date",
    "Payment Status": "payment_status"
})

42. Remove Unnecessary Columns

clean_df = clean_df.drop(
    columns=["temporary_column"],
    errors="ignore"
)

Only remove columns after confirming that they are not required for the
analysis or relationships with other data.

43. Convert Data Types

clean_df["score"] = pd.to_numeric(
    clean_df["score"],
    errors="coerce"
)

clean_df["registration_date"] = pd.to_datetime(
    clean_df["registration_date"],
    errors="coerce"
)

Invalid values can become missing values during conversion and should then
be investigated.

44. Clean Text in pandas

clean_df["course_name"] = (
    clean_df["course_name"]
    .astype("string")
    .str.strip()
    .str.title()
)

This removes unnecessary surrounding spaces and standardizes capitalization.

45. Handle Missing Values in pandas

clean_df["age"] = clean_df["age"].fillna(
    clean_df["age"].median()
)

The appropriate missing-value method depends on the meaning and distribution
of the variable.

46. Remove Duplicate Records

clean_df = clean_df.drop_duplicates()

For duplicates based on a specific field:

clean_df = clean_df.drop_duplicates(
    subset=["student_id"],
    keep="last"
)

47. Merge Datasets in pandas

merged = students.merge(
    payments,
    on="student_id",
    how="left"
)

Always verify the relationship between the datasets before merging.

48. Grouping Data

summary = (
    clean_df
    .groupby("course_name")["score"]
    .mean()
    .reset_index()
)

This creates an aggregated summary by course.

49. Sorting Data

clean_df = clean_df.sort_values(
    by="registration_date"
)

50. Resetting the Index

clean_df = clean_df.reset_index(drop=True)

This can be useful after filtering, sorting, or other transformations.

51. Validation Checks in Python

print(clean_df["student_id"].is_unique)

print(clean_df["score"].between(0, 100).all())

print(clean_df["payment_status"].isin([
    "Paid",
    "Unpaid",
    "Pending"
]).all())

These checks help determine whether the final dataset satisfies important
rules.

52. Saving the Final Dataset

clean_df.to_csv(
    "analysis_ready_students.csv",
    index=False
)

The final file should have a clear name that communicates its purpose.

53. SQL for Analysis-Ready Data

SQL can also prepare data directly from relational databases.

A simple query can select only the required columns:

SELECT
    student_id,
    course_name,
    registration_date,
    score
FROM students;

54. Filtering with WHERE

SELECT
    student_id,
    course_name,
    score
FROM students
WHERE score IS NOT NULL;

55. Aliases

SELECT
    student_id AS id,
    course_name AS course,
    score AS assessment_score
FROM students;

56. SQL Joins

SELECT
    s.student_id,
    s.course_name,
    p.payment_status
FROM students AS s
LEFT JOIN payments AS p
    ON s.student_id = p.student_id;

The join should be checked carefully to ensure that it does not multiply
records unexpectedly.

57. SQL Aggregation

SELECT
    course_name,
    AVG(score) AS average_score,
    COUNT(*) AS student_count
FROM students
GROUP BY course_name;

58. SQL CASE Expressions

SELECT
    student_id,
    score,
    CASE
        WHEN score >= 70 THEN 'Excellent'
        WHEN score >= 50 THEN 'Pass'
        ELSE 'Needs Improvement'
    END AS performance_group
FROM students;

59. Reproducibility

A professional data-preparation process should be reproducible.

Another analyst should be able to understand how the final dataset was
created and, where possible, repeat the same process with updated source
data.

Manual changes that cannot be explained or repeated create unnecessary risk.

60. Cleaning Log

Maintain a cleaning log that records important transformations.

Example:

61. Before-and-After Row Counts

Always compare the number of records before and after important cleaning
operations.

For example:

Raw rows: 5,000
After duplicate removal: 4,850
After invalid-record filtering: 4,790
Final analysis-ready rows: 4,790

Unexpectedly large changes should be investigated.

62. Data Quality Summary

A final data-quality summary can report:

• Total number of rows.
• Total number of columns.
• Missing values by column.
• Duplicate records.
• Invalid values.
• Unique categories.
• Minimum and maximum numeric values.
• Date range.
• Validation failures.

63. Example: Dwallnet Student Dataset

Imagine Dwallnet has a registration dataset containing:

student_id
student_name
course_name
age
registration_date
payment_status
score

The preparation process could include:

1. Verify that student IDs are unique.
2. Trim student names.
3. Standardize course names.
4. Convert age to numeric.
5. Convert registration date to a date type.
6. Standardize payment statuses.
7. Check missing values.
8. Investigate duplicates.
9. Validate scores from 0 to 100.
10. Investigate unusual ages.
11. Document all transformations.
12. Export the final analysis-ready dataset.

64. Example: Sales Dataset

A sales dataset might contain:

transaction_id
transaction_date
product
quantity
unit_price
customer_id
branch

Preparation could include converting dates, validating quantities, checking
prices, confirming unique transaction IDs, standardizing branch names, and
calculating revenue.

revenue = quantity * unit_price

65. Example: Customer Dataset

A customer dataset might require:

• Cleaning customer names.
• Standardizing phone numbers.
• Validating email addresses.
• Checking duplicate customer IDs.
• Standardizing locations.
• Handling missing demographic information.

66. Example: Training Registration Dataset

A training registration dataset may contain multiple course registrations
for the same person.

The analyst must determine whether each row represents a person or a course
registration before removing duplicates.

A student can legitimately appear multiple times if the dataset represents
registrations rather than unique students.

67. The Importance of Understanding the Business Meaning

Data preparation is not only a technical process. Analysts must understand
what the data represents.

A value that appears unusual from a purely statistical perspective may be
completely valid from a business perspective.

Therefore, data analysts should combine technical checks with domain
knowledge.

68. Final Validation Checklist

Before using a dataset for analysis, ask:

• Do I understand what one row represents?
• Are the column names clear?
• Are the data types correct?
• Are identifiers valid and unique where required?
• Have missing values been investigated?
• Have duplicates been investigated?
• Have outliers been investigated?
• Are categories standardized?
• Are dates valid?
• Are numeric values within expected ranges?
• Are relationships between tables correct?
• Have unnecessary records been filtered for a valid reason?
• Have unnecessary columns been removed or excluded?
• Are transformations documented?
• Is the raw dataset preserved?
• Can the preparation process be reproduced?
• Has the final dataset been saved correctly?

69. Practical Capstone Exercise

Create or obtain a small messy dataset containing at least 100 records.
The dataset should intentionally contain realistic data-quality problems.

Your task is to:

1. Inspect the raw dataset.
2. Define what one row represents.
3. Identify the important variables.
4. Create a data dictionary.
5. Standardize column names.
6. Correct data types.
7. Handle missing values.
8. Investigate duplicate records.
9. Investigate outliers.
10. Standardize categories.
11. Validate important fields.
12. Create useful derived columns.
13. Remove irrelevant records where justified.
14. Preserve the raw dataset.
15. Create an analysis-ready version.
16. Document every major transformation.
17. Produce a final validation report.
18. Export the final dataset as CSV.

70. Expected Capstone Deliverables

Your submission should contain:

• Raw dataset.
• Analysis-ready dataset.
• Data dictionary.
• Validation report.
• Cleaning log.
• Short explanation of the transformations performed.

71. Knowledge Check

1. What is an analysis-ready dataset?
2. What is the difference between raw data and cleaned data?
3. What does the tidy-data principle mean?
4. Why is a unique identifier important?
5. What is a data dictionary?
6. Why should raw data normally be preserved?
7. Why should duplicate records be investigated before deletion?
8. Why are data types important?
9. What is a derived column?
10. What is the purpose of a cleaning log?
11. Why can incorrect joins create inaccurate results?
12. How can Power Query help with repeatable data preparation?
13. What does drop_duplicates() do in pandas?
14. What does pd.to_datetime() help accomplish?
15. Why is reproducibility important in data analysis?

72. Key Terms

• Analysis-ready dataset: Data prepared for reliable analysis.
• Raw data: Data in its original collected form.
• Tidy data: Data structured with clear variables and observations.
• Unique identifier: A value used to distinguish records.
• Data dictionary: Documentation describing dataset fields.
• Metadata: Information describing the data.
• Transformation: A change made to data to make it more useful.
• Validation: Checking data against defined rules.
• Derived column: A field calculated from existing data.
• Reproducibility: The ability to repeat a data process and obtain a consistent result.
• Cleaning log: A record of important data-preparation actions.

73. Key Takeaways

• Good analysis begins with well-prepared data.
• Analysis-ready data is more than simply data without blanks.
• Every row and column should have a clear meaning.
• Data types must be appropriate for the values they contain.
• Missing values, duplicates, outliers, and inconsistent categories must be investigated.
• Raw data should normally be preserved.
• Transformations should be documented.
• Data dictionaries improve understanding and collaboration.
• Excel, Power Query, Python, pandas, and SQL can all support data preparation.
• Correct joins and relationships are essential when combining datasets.
• Validation should happen before the dataset is used for important analysis.
• Reproducibility makes data preparation more professional and reliable.

74. Module 3 Summary

In this module, you learned how to identify and handle common data-quality
problems including dirty data, missing values, duplicate records, incorrect
formats, inconsistent values, and outliers. You also learned about data
transformation, validation, and finally how to produce an analysis-ready
dataset.

The goal of data cleaning and preparation is not simply to make a dataset
look neat. The goal is to create data that can support accurate, meaningful,
and reproducible analysis.

You are now ready to apply the complete data-cleaning workflow to real-world
datasets and move from raw information toward reliable analytical insight.

Next Step

Module 3 — Data Cleaning and Preparation is now complete. The next stage is
to apply these principles to practical analytical tasks and continue building
your professional data-analysis workflow.