Pandas Data Transformation

Real-World Scenario

A financial analyst receives a quarterly revenue report as a wide table (one column per quarter). The plotting library and ML model both need the data in long format (one row per data point). A data engineer building a feature store needs to reshape user activity data from event-level records into per-user feature vectors. Knowing how to reshape data is what separates analysts who get stuck from those who move fast.

Wide to Long: melt

import pandas as pd

# Wide format — one column per quarter
revenue_wide = pd.DataFrame({
    "company":  ["Acme", "Globex", "Initech"],
    "Q1_2024":  [120000, 85000, 210000],
    "Q2_2024":  [135000, 92000, 195000],
    "Q3_2024":  [128000, 88000, 220000],
    "Q4_2024":  [145000, 105000, 235000],
})
print(revenue_wide)

# melt: unpivot to long format
revenue_long = revenue_wide.melt(
    id_vars=["company"],              # columns to keep as-is
    value_vars=["Q1_2024", "Q2_2024", "Q3_2024", "Q4_2024"],  # columns to unpivot
    var_name="quarter",               # new column for the variable names
    value_name="revenue",             # new column for the values
)
print(revenue_long)
#    company quarter  revenue
# 0     Acme Q1_2024   120000
# 1   Globex Q1_2024    85000
# ...

Long to Wide: pivot

import pandas as pd

# Long format — one row per (company, quarter) combination
revenue_long = pd.DataFrame({
    "company": ["Acme", "Acme", "Globex", "Globex"],
    "quarter": ["Q1",   "Q2",   "Q1",    "Q2"],
    "revenue": [120000, 135000, 85000, 92000],
})

# pivot: long → wide
revenue_wide = revenue_long.pivot(
    index="company",    # becomes the row index
    columns="quarter",  # unique values become column headers
    values="revenue",   # values to fill
)
revenue_wide.columns.name = None    # remove the "quarter" label from column header
revenue_wide = revenue_wide.reset_index()
print(revenue_wide)
#   company      Q1      Q2
# 0    Acme  120000  135000
# 1  Globex   85000   92000

Stack and Unstack

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
df = pd.DataFrame(
    rng.integers(100, 1000, (4, 3)),
    index   = pd.Index(["North", "South", "East", "West"], name="region"),
    columns = pd.Index(["Widget", "Gadget", "Gizmo"], name="product"),
)
print(df)
# product  Widget  Gadget  Gizmo
# region
# North       ...

# stack: columns → additional row index level → Series
stacked = df.stack()     # MultiIndex Series: (region, product) → value
print(type(stacked))     # pandas.core.series.Series
print(stacked.head(6))

# unstack: inner row index level → columns (inverse of stack)
print(stacked.unstack())   # back to original DataFrame
print(stacked.unstack(level=0))  # regions become columns instead

apply — Row and Column Transformations

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name":    ["Alice Chen", "Bob Smith", "Carol Jones"],
    "scores":  [[88, 92, 76], [71, 85, 90], [95, 88, 79]],
    "salary":  [95000, 72000, 88000],
    "country": ["US", "UK", "US"],
})

# Apply to a column (Series) — use for complex single-column logic
# Simple cases: prefer vectorized .str, .dt, arithmetic instead
df["first_name"] = df["name"].apply(lambda s: s.split()[0])

# Apply a function to a list stored in a cell
df["avg_score"] = df["scores"].apply(lambda lst: sum(lst) / len(lst))

# Apply to a row (axis=1) — use sparingly, it's slow at scale
def calculate_tax(row):
    rate = 0.35 if row["country"] == "US" else 0.30
    return row["salary"] * rate

df["tax"] = df.apply(calculate_tax, axis=1)

# map — element-wise substitution (only works on Series)
country_map = {"US": "United States", "UK": "United Kingdom"}
df["country_full"] = df["country"].map(country_map)

print(df[["name", "first_name", "avg_score", "tax", "country_full"]])

String Operations with .str

import pandas as pd

df = pd.DataFrame({
    "email":    ["[email protected]", "  [email protected]  ", "[email protected]"],
    "name":     ["alice chen", "bob smith", "carol jones"],
    "phone":    ["555-123-4567", "555.234.5678", "(555) 345-6789"],
    "address":  ["123 Main St, New York, NY 10001", "456 Oak Ave, Chicago, IL 60601", "789 Pine Rd, Austin, TX 78701"],
})

# Normalize email
df["email"] = df["email"].str.strip().str.lower()

# Title case names
df["name"] = df["name"].str.title()

# Extract from structured strings
df["city"]  = df["address"].str.extract(r",\s*([^,]+),\s*[A-Z]{2}")
df["state"] = df["address"].str.extract(r",\s*([A-Z]{2})\s+\d{5}")
df["zip"]   = df["address"].str.extract(r"(\d{5})$")

# Normalize phone — keep digits only
df["phone_clean"] = df["phone"].str.replace(r"[^\d]", "", regex=True)

# Check membership / contains
df["is_gmail"] = df["email"].str.endswith("@gmail.com")
df["has_ny"]   = df["address"].str.contains("NY", regex=False)

print(df)

Real-World: Feature Engineering for ML

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
n = 5000

events = pd.DataFrame({
    "user_id":    rng.integers(1, 501, n),
    "event_type": rng.choice(["view", "click", "purchase", "return"], n,
                              p=[0.6, 0.25, 0.1, 0.05]),
    "amount":     np.where(rng.random(n) < 0.1, rng.uniform(10, 500, n), 0),
    "timestamp":  pd.date_range("2024-01-01", periods=n, freq="10min"),
    "category":   rng.choice(["Electronics", "Books", "Clothing"], n),
})

# Step 1: Count-based features per user
count_features = events.groupby("user_id")["event_type"].value_counts().unstack(fill_value=0)
count_features.columns = [f"event_{col}" for col in count_features.columns]

# Step 2: Revenue features
revenue_features = events.groupby("user_id")["amount"].agg(
    total_spent  = "sum",
    num_purchases= lambda x: (x > 0).sum(),
    avg_purchase = lambda x: x[x > 0].mean() if (x > 0).any() else 0,
)

# Step 3: Temporal features
events["hour"] = events["timestamp"].dt.hour
temporal = events.groupby("user_id")["hour"].agg(
    most_active_hour = lambda x: x.value_counts().idxmax(),
    sessions_in_evening = lambda x: (x >= 18).sum(),
)

# Step 4: Category preferences
category_counts = events.groupby(["user_id", "category"]).size().unstack(fill_value=0)
category_counts.columns = [f"cat_{col.lower()}" for col in category_counts.columns]

# Combine into a feature matrix
feature_matrix = (
    count_features
    .join(revenue_features)
    .join(temporal)
    .join(category_counts)
    .fillna(0)
    .reset_index()
)

print(feature_matrix.shape)   # (500, ~15 features)
print(feature_matrix.head(3))