Data Transformation

Data Transformation

Data transformation is the process of converting data from its original form into a more useful, consistent,
standardized, and analysis-ready format.

Raw data is rarely ready for immediate analysis. It may contain inconsistent formats, unnecessary columns,
different units, text values that should be numeric, dates stored as text, categorical values that need to be standardized,
or numerical variables that need to be transformed for analysis.

Data transformation is therefore an important stage of the data-cleaning and preparation process.

Learning Objectives

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

• Explain what data transformation means.
• Explain why data transformation is important.
• Distinguish data cleaning from data transformation.
• Convert data types correctly.
• Standardize text values.
• Transform dates and times.
• Create calculated and derived columns.
• Convert units and currencies when appropriate.
• Apply mathematical transformations.
• Use normalization and standardization appropriately.
• Transform categorical variables for analysis.
• Perform common transformations in Excel.
• Perform common transformations with Python and pandas.
• Understand how transformation decisions can affect analysis.
• Document transformation rules.

1. What Is Data Transformation?

Data transformation changes the structure, representation, scale, or format of data while preserving the information
needed for analysis.

For example:

Raw:
"john doe"

Transformed:
"John Doe"

Another example:

Raw:
"15/08/2026"

Transformed:
2026-08-15

The purpose is not to change data randomly. The purpose is to make the data suitable for the intended analytical task.

2. Data Cleaning vs Data Transformation

Data cleaning focuses heavily on finding and correcting problems in data.
Data transformation focuses on changing data into a more useful representation.

The two processes overlap and are often performed together.

3. Why Is Data Transformation Important?

Data transformation is important because analytical tools work best when data is represented consistently.

Transformation can help to:

• Improve consistency.
• Make datasets easier to analyze.
• Make variables comparable.
• Prepare data for visualization.
• Prepare data for statistical models.
• Prepare data for machine learning.
• Reduce unnecessary complexity.
• Create meaningful business metrics.
• Improve reporting accuracy.
• Make datasets easier to combine.

4. Common Types of Data Transformation

Common transformation tasks include:

• Changing data types.
• Standardizing text.
• Formatting dates.
• Creating new columns.
• Combining columns.
• Splitting columns.
• Converting units.
• Converting currencies.
• Normalizing values.
• Standardizing numerical values.
• Log transformation.
• Binning numerical values.
• Encoding categorical variables.
• Aggregating records.
• Reshaping datasets.

5. Data Type Transformation

One of the most common transformation tasks is changing a value from one data type to another.

Examples include:

"150"  →  150
"25.75" → 25.75
"2026-08-15" → Date
"TRUE" → Boolean

A number stored as text may look correct in a spreadsheet but fail when used in mathematical calculations.

6. Text to Number Transformation

Suppose a sales column contains:

"5000"
"12500"
"7500"

These values may need to be converted into numerical values before calculations can be performed.

In Excel, tools such as VALUE(), Text to Columns, or appropriate formatting can help.

=VALUE(A2)

In Python:

df["amount"] = pd.to_numeric(
    df["amount"],
    errors="coerce"
)

The errors="coerce" option converts values that cannot be interpreted as numbers into missing values,
which can then be investigated separately.

7. Standardizing Text

Text values often require standardization before analysis.

For example:

lagos
LAGOS
Lagos
lagos 

These may represent the same location but appear as different values to a computer.

Common text transformations include:

• Removing leading and trailing spaces.
• Converting text to lowercase.
• Converting text to uppercase.
• Applying title case.
• Replacing abbreviations.
• Standardizing spelling.

8. Text Transformation in Excel

Useful Excel functions include:

=TRIM(A2)

=LOWER(A2)

=UPPER(A2)

=PROPER(A2)

=CLEAN(A2)

These functions can help prepare text for matching, grouping, filtering, and reporting.

9. Text Transformation with pandas

df["city"] = (
    df["city"]
    .astype("string")
    .str.strip()
    .str.lower()
)

If the desired presentation is title case:

df["city"] = df["city"].str.title()

10. Standardizing Categories

Categorical variables often contain multiple representations of the same category.

For example:

Male
male
M
MALE
m

These values may all represent the same category.

A mapping table can be used to standardize them.

Male → Male
male → Male
M    → Male
MALE → Male
m    → Male

This is especially useful when preparing datasets for grouping and statistical analysis.

11. Using Mapping Tables

Mapping tables are safer than manually replacing values throughout a large dataset.

For example:

Mapping tables make transformation rules easier to audit and reuse.

12. Date Transformation

Dates are frequently stored in inconsistent formats.

Examples:

15/08/2026
2026-08-15
Aug 15, 2026
15-Aug-2026

These may represent the same date.
A consistent date representation makes filtering, sorting, grouping, and time-series analysis easier.

13. Extracting Date Components

An analyst may need to extract:

• Year
• Month
• Month name
• Quarter
• Day
• Day of week

For example, from:

2026-08-15

we could create:

Year: 2026
Month: 8
Quarter: Q3
Day: 15

14. Date Transformation in Excel

If A2 contains a valid Excel date:

=YEAR(A2)

=MONTH(A2)

=DAY(A2)

=TEXT(A2,"mmmm")

="Q"&ROUNDUP(MONTH(A2)/3,0)

15. Date Transformation with pandas

df["date"] = pd.to_datetime(
    df["date"],
    errors="coerce"
)

df["year"] = df["date"].dt.year
df["month"] = df["date"].dt.month
df["month_name"] = df["date"].dt.month_name()
df["quarter"] = df["date"].dt.quarter

16. Creating Calculated Columns

A calculated column is a new variable created from existing data.

For example, if a sales dataset contains:

quantity
unit_price

we can create:

sales_value = quantity × unit_price

In pandas:

df["sales_value"] = (
    df["quantity"] * df["unit_price"]
)

17. Creating Business Metrics

Data transformation is often used to create meaningful business indicators.

Examples include:

• Profit.
• Profit margin.
• Customer lifetime value.
• Average order value.
• Conversion rate.
• Attendance percentage.
• Student completion rate.
• Monthly revenue.

18. Example: Profit Calculation

Revenue = ₦500,000
Cost = ₦350,000

Profit = Revenue - Cost

Profit = ₦150,000

This transformation creates a new analytical variable from existing variables.

19. Combining Columns

Sometimes information is split across multiple columns.
For example:

First Name
Last Name

These can be combined to create a full-name column.

In Excel:

In pandas:

df["full_name"] = (
    df["first_name"].str.strip()
    + " "
    + df["last_name"].str.strip()
)

20. Splitting Columns

The reverse operation is also common.
A full name may need to be separated into first and last names.

In pandas:

df[["first_name", "last_name"]] = (
    df["full_name"]
    .str.split(" ", n=1, expand=True)
)

Always consider names with multiple parts before applying a simple split rule.

21. Unit Conversion

Data collected from different sources may use different measurement units.

Examples:

• Metres and kilometres.
• Grams and kilograms.
• Celsius and Fahrenheit.
• Minutes and hours.
• Bytes and megabytes.

For example:

1 kilometre = 1000 metres

If a distance is stored in metres but the report requires kilometres:

kilometres = metres / 1000

22. Currency Transformation

Financial datasets may contain different currencies.
Before combining them, the analyst may need to convert them to a common currency.

For example:

Amount × Exchange Rate = Converted Amount

Exchange rates must be sourced appropriately and the date or period of the rate should be documented.

23. Do Not Lose the Original Value

When transforming important financial or operational data, it is often safer to preserve the original column and create a transformed column.

For example:

amount_original
amount_ngn

This makes auditing and verification easier.

24. Aggregation as Transformation

Aggregation combines detailed records into meaningful summaries.

For example:

Daily Sales
↓
Monthly Sales

Or:

Individual Student Scores
↓
Average Score by Class

Aggregation is especially useful for dashboards and management reports.

25. Grouping Data

A dataset may be grouped by:

• Month.
• Year.
• Branch.
• Product.
• Customer segment.
• Course.
• Department.

In pandas:

monthly_sales = (
    df.groupby("month")["sales_value"]
    .sum()
    .reset_index()
)

26. Binning Numerical Data

Binning converts continuous numerical values into categories or ranges.

For example, customer age can be transformed into groups:

18–24
25–34
35–44
45–54
55+

This can make certain reports easier to interpret.

27. Binning in Excel

Excel can use nested IF statements, lookup tables, or other classification methods to assign numerical values to groups.

For example:

=IF(A2<25,"18-24",
IF(A2<35,"25-34",
IF(A2<45,"35-44",
IF(A2<55,"45-54","55+"))))

28. Binning with pandas

bins = [17, 24, 34, 44, 54, 120]

labels = [
    "18-24",
    "25-34",
    "35-44",
    "45-54",
    "55+"
]

df["age_group"] = pd.cut(
    df["age"],
    bins=bins,
    labels=labels
)

29. Normalization

Normalization transforms numerical values to a common scale.
One common method is Min-Max normalization.

The formula is:

x' = (x - min(x)) / (max(x) - min(x))

This typically produces values between 0 and 1.

30. Example of Min-Max Normalization

Suppose the minimum score is 20, the maximum score is 100, and a student’s score is 60.

x' = (60 - 20) / (100 - 20)

x' = 40 / 80

x' = 0.5

The transformed value is 0.5.

31. Standardization

Standardization transforms values so that they are represented in terms of their distance from the mean.
A common formula is the z-score:

z = (x - mean) / standard deviation

After standardization, values are commonly centered around zero with a standard deviation close to one.

32. Normalization vs Standardization

33. Log Transformation

A logarithmic transformation can reduce the influence of very large values and help with strongly right-skewed variables.

For example, transaction values or income may span a very large range.

In Python:

import numpy as np

df["log_amount"] = np.log1p(df["amount"])

log1p() calculates the logarithm of 1 plus the value and can be useful when zero values are present.

Log transformations must be applied carefully because interpretation changes after transformation.

34. Categorical Encoding

Categorical variables often need to be transformed into numerical representations before certain statistical or machine-learning models can use them.

For example:

Gender
Male
Female

One-hot encoding can create separate indicator columns.

Gender_Male
Gender_Female

In pandas:

encoded = pd.get_dummies(
    df["gender"],
    prefix="gender"
)

35. Binary Transformation

A categorical variable can sometimes be transformed into a binary indicator.

For example:

Completed = Yes → 1
Completed = No  → 0

This is useful when an analytical model requires numerical input.

36. Reshaping Data

Data may need to be changed between different structures.
Common operations include:

• Wide to long.
• Long to wide.
• Pivoting.
• Unpivoting.

Reshaping is particularly useful when preparing data for visualization or analysis.

37. Example of Wide Data

Student | January | February | March
John    | 70      | 75       | 80
Mary    | 65      | 72       | 78

This can be transformed into long format:

Student | Month    | Score
John    | January  | 70
John    | February | 75
John    | March    | 80
Mary    | January  | 65
Mary    | February | 72
Mary    | March    | 78

38. Transformation with pandas

The melt() function can convert wide data into long format.

long_df = df.melt(
    id_vars=["Student"],
    var_name="Month",
    value_name="Score"
)

39. Handling Transformation Errors

Transformations can create new problems if they are performed without validation.

Common problems include:

• Incorrect conversion of dates.
• Numbers converted incorrectly.
• Currency conversion using the wrong rate.
• Incorrect category mapping.
• Loss of decimal precision.
• Unexpected missing values.
• Incorrect formulas.
• Wrong units.

Every transformation should therefore be tested.

40. Transformation Validation

After transformation, compare the original and transformed data.

Ask:

• Did the number of records remain correct?
• Did the number of columns change as expected?
• Did any unexpected missing values appear?
• Are numerical ranges reasonable?
• Are categories correct?
• Are dates valid?
• Are calculated values correct?
• Can the transformed value be traced back to the original?

41. Before and After Comparison

A good transformation workflow keeps a clear distinction between source and transformed values where necessary.

Original Amount | Converted Amount
100 USD         | 150,000 NGN

This makes auditing easier.

42. Transformation Documentation

Every important transformation should be documented.

A transformation record may include:

• Original column.
• New column.
• Transformation rule.
• Reason for transformation.
• Date performed.
• Data source.
• Person or process responsible.

43. Example Transformation Documentation

44. Data Transformation Example for Dwallnet

Imagine Dwallnet has a student dataset containing:

Student Name
Course
Registration Date
Amount Paid
Completion Status

The raw data could be transformed into:

Student Name
Course Standardized
Registration Year
Registration Month
Amount Paid Numeric
Completion Flag
Course Fee Balance

These transformed fields can make reporting and dashboard creation much easier.

45. Data Transformation Example for Sales

A sales dataset may contain:

Quantity
Unit Price
Order Date
Customer
Region

Useful transformed fields could include:

Total Sales
Order Year
Order Month
Quarter
Customer Segment
Region Standardized

46. Transformation in Excel Workflow

A practical Excel workflow is:

1. Make a backup of the original dataset.
2. Inspect the columns.
3. Identify required transformations.
4. Create helper columns where appropriate.
5. Apply formulas.
6. Review the results.
7. Check for errors.
8. Convert formulas to values only when appropriate.
9. Document the transformation rules.

47. Transformation in Python Workflow

A typical pandas workflow is:

import pandas as pd

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

# Clean text
df["city"] = (
    df["city"]
    .astype("string")
    .str.strip()
    .str.title()
)

# Convert numeric data
df["amount"] = pd.to_numeric(
    df["amount"],
    errors="coerce"
)

# Convert dates
df["date"] = pd.to_datetime(
    df["date"],
    errors="coerce"
)

# Create derived fields
df["year"] = df["date"].dt.year

df["month"] = df["date"].dt.month

# Create calculated field
df["sales_value"] = (
    df["quantity"] * df["unit_price"]
)

print(df.head())

48. Transformation and Data Integrity

Data transformation should preserve the meaning of the data.
A transformation that changes the meaning of a variable without documentation can produce misleading analysis.

For example, converting ₦500,000 to 500 without documenting that the value is now represented in thousands can create serious reporting errors.

49. Transformation and Precision

Be careful when rounding numbers.

For example:

Original:
₦12,456.78

Rounded:
₦12,457

Rounding may be appropriate for presentation, but the original precision should usually be retained in the analytical dataset when required.

50. Transformation and Privacy

Data transformation can also be used to protect sensitive information.
Examples include:

• Masking phone numbers.
• Hashing identifiers.
• Removing unnecessary personal information.
• Aggregating records.
• Replacing direct identifiers with anonymous IDs.

Privacy transformations should be designed carefully so that the resulting dataset remains useful without exposing unnecessary personal information.

51. Transformation Best Practices

• Understand the business purpose before transforming data.
• Keep a backup of the original dataset.
• Do not overwrite important source data unnecessarily.
• Use reproducible transformation rules.
• Use mapping tables for category standardization.
• Validate the transformed data.
• Preserve important original values.
• Document every significant transformation.
• Check numerical ranges after transformation.
• Check for newly created missing values.
• Confirm units and currencies.
• Test formulas and calculations.

52. Practical Exercise

Create a small dataset containing at least 20 student records with:

• Student name.
• Course.
• Registration date.
• Amount paid.
• Quantity of courses.
• Completion status.

Perform the following transformations:

1. Standardize student names.
2. Standardize course names.
3. Convert registration dates into proper date values.
4. Extract registration year.
5. Extract registration month.
6. Convert amount paid to numeric format.
7. Create a completion flag.
8. Create a calculated fee balance.
9. Create an age or payment category where appropriate.
10. Check the transformed dataset for errors.
11. Document each transformation.

53. Practical Python Exercise

Using pandas, create a transformed dataset from a raw CSV file.

import pandas as pd

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

df["student_name"] = (
    df["student_name"]
    .astype("string")
    .str.strip()
    .str.title()
)

df["amount_paid"] = pd.to_numeric(
    df["amount_paid"],
    errors="coerce"
)

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

df["registration_year"] = (
    df["registration_date"].dt.year
)

df["registration_month"] = (
    df["registration_date"].dt.month
)

df["completed_flag"] = (
    df["completion_status"]
    .astype("string")
    .str.strip()
    .str.lower()
    .eq("completed")
    .astype(int)
)

print(df.head())

df.to_csv(
    "transformed_students.csv",
    index=False
)

54. Knowledge Check

1. What is data transformation?
2. Why is data transformation important?
3. What is the difference between data cleaning and data transformation?
4. Why might a text number need to be converted into a numeric value?
5. What does the Excel TRIM function do?
6. Why should categorical values be standardized?
7. Why are dates often transformed into year, month, and quarter fields?
8. What is a calculated column?
9. What is normalization?
10. What is standardization?
11. What is the difference between normalization and standardization?
12. What is binning?
13. Why might a logarithmic transformation be useful?
14. What is categorical encoding?
15. Why should transformation rules be documented?
16. Why should original source values sometimes be preserved?

55. Key Terms

• Data Transformation: converting data into a more useful representation.
• Normalization: scaling numerical values to a common range.
• Standardization: transforming values according to their mean and standard deviation.
• Binning: grouping numerical values into categories or ranges.
• Encoding: converting categories into representations suitable for analysis.
• Aggregation: combining detailed observations into summaries.
• Derived Column: a new variable calculated from existing data.
• Mapping: converting one set of categorical values into standardized values.
• Reshaping: changing the structural arrangement of a dataset.
• Validation: checking that transformed data remains accurate and meaningful.

56. Key Takeaways

• Data transformation prepares data for analysis and reporting.
• Transformation can change data types, formats, scales, categories, and structures.
• Text should be standardized before grouping or matching.
• Dates can be transformed into year, month, quarter, and other useful components.
• Calculated columns can create useful business metrics.
• Normalization and standardization change numerical scales.
• Binning converts continuous values into meaningful groups.
• Categorical encoding prepares categories for analytical models.
• Aggregation creates useful summaries.
• Transformation should always be validated.
• Important original values should be preserved where necessary.
• Transformation rules should be documented and reproducible.

57. Lesson Summary

Data transformation is a major part of preparing a dataset for analysis.
It converts raw information into consistent, meaningful, and useful representations.

Common transformations include changing data types, standardizing text, formatting dates,
creating calculated fields, converting units, grouping values, normalizing numerical variables,
encoding categories, aggregating records, and reshaping datasets.

Excel provides practical tools and formulas for many transformation tasks, while Python and pandas provide
a powerful and repeatable way to transform larger datasets.

A professional analyst should always validate transformations, preserve important source information,
and document the rules used.

Next Lesson

The next lesson is Data Validation, where we will learn how to systematically check transformed
and cleaned datasets to ensure that they are accurate, complete, consistent, and ready for analysis.