Data Transformation

Raw data rarely arrives in the format your analysis needs. Dates come as strings. Categories are inconsistently capitalised. Numbers are stored as text. Addresses need splitting. This file covers the transformation operations that bridge messy raw data and analysis-ready data.

Learning Objectives

• Convert data types correctly
• Standardise string values
• Parse and extract from dates
• Encode categorical variables
• Normalise and scale numeric data

---

Type Conversion

import pandas as pd
import numpy as np

df = pd.read_csv("orders_raw.csv")
print(df.dtypes)

String to number

# Remove £ sign and commas before converting
df["revenue"] = df["revenue"].str.replace("£", "").str.replace(",", "").astype(float)

# Handle "N/A" strings
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")  # invalid → NaN

# Percentage strings
df["discount_pct"] = df["discount_pct"].str.replace("%", "").astype(float)

String to datetime

# ISO format — usually auto-detected
df["order_date"] = pd.to_datetime(df["order_date"])

# Non-standard format
df["order_date"] = pd.to_datetime(df["order_date"], format="%d/%m/%Y")  # UK format: 25/01/2024
df["order_date"] = pd.to_datetime(df["order_date"], format="%m-%d-%Y")  # US: 01-25-2024

# Mixed formats in the same column
df["order_date"] = pd.to_datetime(df["order_date"], infer_datetime_format=True, errors="coerce")

Reduce memory with better types

# Category dtype — huge memory savings for low-cardinality strings
before = df.memory_usage(deep=True).sum() / 1024**2  # MB

df["status"] = df["status"].astype("category")
df["country"] = df["country"].astype("category")
df["category"] = df["category"].astype("category")

after = df.memory_usage(deep=True).sum() / 1024**2
print(f"Memory: {before:.1f} MB → {after:.1f} MB ({(before-after)/before:.0%} reduction)")

---

String Cleaning

# Standardise case
df["status"] = df["status"].str.lower().str.strip()
df["country"] = df["country"].str.title().str.strip()

# Remove extra whitespace
df["product_name"] = df["product_name"].str.strip().str.replace(r"\s+", " ", regex=True)

# Remove special characters
df["phone"] = df["phone"].str.replace(r"[^\d+]", "", regex=True)  # keep only digits and +

# Standardise common variations
status_map = {
    "complete": "completed",
    "done": "completed",
    "shipped": "completed",
    "wait": "pending",
    "waiting": "pending",
}
df["status"] = df["status"].replace(status_map)

# Handle encoding artifacts
df["name"] = df["name"].str.encode("ascii", errors="ignore").str.decode("ascii")

---

Date Feature Engineering

df["order_date"] = pd.to_datetime(df["order_date"])

# Extract useful features
df["year"] = df["order_date"].dt.year
df["month"] = df["order_date"].dt.month
df["month_name"] = df["order_date"].dt.strftime("%b")     # "Jan", "Feb", ...
df["quarter"] = df["order_date"].dt.quarter
df["day_of_week"] = df["order_date"].dt.dayofweek        # 0=Monday, 6=Sunday
df["day_name"] = df["order_date"].dt.day_name()          # "Monday", "Tuesday", ...
df["week_number"] = df["order_date"].dt.isocalendar().week
df["is_weekend"] = df["order_date"].dt.dayofweek >= 5

# Period (useful for groupby)
df["month_period"] = df["order_date"].dt.to_period("M")  # "2024-01"
df["quarter_period"] = df["order_date"].dt.to_period("Q")  # "2024Q1"

# Date arithmetic
df["days_since_order"] = (pd.Timestamp("2024-03-01") - df["order_date"]).dt.days
df["order_age_weeks"] = df["days_since_order"] // 7

---

Categorical Encoding

When feeding data into statistical models or dashboards that need numeric input:

One-hot encoding

# Create binary columns for each category value
status_dummies = pd.get_dummies(df["status"], prefix="status")
df = pd.concat([df, status_dummies], axis=1)

Result: new columns status_completed, status_pending, status_cancelled with 0/1 values.

Label encoding (ordinal categories)

# When categories have a meaningful order
segment_order = {"New": 0, "Regular": 1, "VIP": 2}
df["segment_code"] = df["segment"].map(segment_order)

Frequency encoding

# Replace category with how often it appears (useful for high-cardinality)
country_freq = df["country"].value_counts(normalize=True)
df["country_freq"] = df["country"].map(country_freq)

---

Numeric Scaling

For analytics (not required), but essential for ML:

from sklearn.preprocessing import MinMaxScaler, StandardScaler

# Min-Max scaling: scales to [0, 1]
scaler = MinMaxScaler()
df["total_scaled"] = scaler.fit_transform(df[["total"]])

# Standardisation (z-score): mean=0, std=1
scaler = StandardScaler()
df["total_std"] = scaler.fit_transform(df[["total"]])

# Log transformation (for right-skewed distributions)
df["log_total"] = np.log1p(df["total"])

For analysts (not ML engineers)

You rarely need to scale data for dashboards or SQL queries. Scaling is mainly needed for distance-based ML algorithms (k-means, PCA, SVM). For business analytics, the raw or log-transformed values are usually fine.

---

Splitting and Combining Columns

# Split "first_name last_name" into two columns
df[["first_name", "last_name"]] = df["full_name"].str.split(" ", n=1, expand=True)

# Extract email domain
df["email_domain"] = df["email"].str.split("@").str[1]

# Extract from address: "City, Country"
df[["city", "country"]] = df["location"].str.split(", ", expand=True)

# Combine columns
df["full_address"] = df["city"] + ", " + df["country"]

---

Practice Exercises

Warm-up 1. Convert a column of revenue strings like "£1,249.50" to float. 2. Standardise a status column so all values are lowercase and stripped of whitespace. 3. Extract year, month, and day_of_week from an order_date column.

Main 4. A phone column contains values like "+44 (0) 20-7946 0958", "020 7946 0958", "+44-20-7946-0958". Write code to standardise all to international format "+44XXXXXXXXXX". 5. Encode the segment column as ordinal numbers (New=1, Regular=2, VIP=3). Use .map(). 6. One-hot encode the category column and count how many categories there are.

Stretch 7. Write a function parse_price(value) that handles: "£1,249.50", "1249.50", "$89.99", "N/A", None — returning a float or np.nan. 8. Write a cleaning pipeline that takes a raw orders DataFrame and returns a clean one: corrected types, standardised strings, extracted date features, and validated ranges.

---

Interview Questions

[Beginner] How do you convert a string column to datetime in Pandas?

[Mid-level] What is the errors="coerce" parameter in pd.to_numeric()? When would you use it?

[Mid-level] What is the difference between one-hot encoding and label encoding? When would you use each?

[Senior] You have a unit_price column where 95% of values are between £5 and £500, but 5% are over £10,000 (data entry errors with extra zeroes). Write a function that detects and corrects likely entry errors.

---

← Outliers · Next: Cleaning Workflows →