Pandas: Data Transformation

Last updated: 2026-08-26

The final step of data cleaning is "transformation" — reshaping raw data into the form your analysis requires: converting scores to grades, prices to discounts, and chaining multi-step operations together. Pandas' map / apply / pipe / replace / assign / transform form a multi-level transformation toolkit spanning from single elements to entire tables. This section covers the use cases and performance pitfalls of each method.

⚠️ Note: The code below must be run in a local Python environment.

1. What You Will Learn

❶ map element mapping
❷ apply row/column functions
❸ pipe chaining
❹ replace value replacement
❺ assign new columns
❻ transform group transformation

2. Alice's Grade Conversion

(1) The Problem: Converting 0-100 Scores to ABCD Grades

Alice has 100 students' scores on a 0-100 scale and needs to convert them to letter grades:

PYTHON

import pandas as pd

scores = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'Carol', 'David'],
    'score': [92, 78, 65, 88, 45]
})
# How to convert 0-100 → A/B/C/D/F?

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) Solution: map + Dictionary Mapping

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: map dictionary mapping (Difficulty ⭐)

PYTHON

import pandas as pd

scores = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'Carol', 'David'],
    'score': [92, 78, 65, 88, 45]
})

# Step 1: Define grade mapping function
def score_to_grade(s):
    if s >= 90: return 'A'
    elif s >= 80: return 'B'
    elif s >= 70: return 'C'
    elif s >= 60: return 'D'
    else: return 'F'

# Step 2: Apply with map
scores['grade'] = scores['score'].map(score_to_grade)
print(scores)
#      name  score grade
# 0   Alice     92     A
# 1     Bob     78     C
# 2 Charlie     65     D
# 3   Carol     88     B
# 4   David     45     F

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

3. map Element Mapping

(1) Three Ways to Use map

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Three mapping approaches with map (Difficulty ⭐⭐)

PYTHON

import pandas as pd

s = pd.Series([1, 2, 3, 4, 5])

# 1. Dictionary mapping
category_map = {1: 'Low', 2: 'Low', 3: 'Medium', 4: 'High', 5: 'High'}
print(s.map(category_map))
# 0      Low
# 1      Low
# 2   Medium
# 3     High
# 4     High

# 2. Function mapping
print(s.map(lambda x: x ** 2))
# 0     1
# 1     4
# 2     9
# 3    16
# 4    25

# 3. Series mapping (align by index)
other = pd.Series({1: 'One', 2: 'Two', 3: 'Three'})
print(s.map(other))
# 0    NaN   ← 1 exists in other → 'One'
# 1   Two
# 2 Three
# 3    NaN  ← 4 not in other → NaN
# 4    NaN

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) When to Use map

Feature	map	apply
Operates on	Series individual elements	Series / DataFrame rows/columns
Input	Dictionary/function/Series	Function
Returns	Series (one-to-one mapping)	Any type
Performance	Fast (dictionary lookup O(1))	Slower (per-element/row/column calls)
Best for	One-to-one mapping	Complex logic/multi-column operations

4. apply Row/Column Functions

(1) Series apply

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Series apply (Difficulty ⭐⭐)

PYTHON

import pandas as pd

s = pd.Series(['alice@example.com', 'bob@company.org', 'charlie@uni.edu'])

# Extract domain from email
domains = s.apply(lambda email: email.split('@')[1])
print(domains)
# 0    example.com
# 1    company.org
# 2       uni.edu

# Complex logic: classify email type
def classify_email(email):
    domain = email.split('@')[1]
    if '.edu' in domain: return 'Academic'
    elif '.org' in domain: return 'Non-profit'
    else: return 'Commercial'

email_type = s.apply(classify_email)
print(email_type)
# 0    Commercial
# 1    Non-profit
# 2     Academic

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) DataFrame apply

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: DataFrame apply row/column operations (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'math': [85, 92, 78, 88, 65],
    'english': [90, 85, 82, 75, 70],
    'science': [88, 95, 80, 90, 60]
}, index=['Alice', 'Bob', 'Charlie', 'Carol', 'David'])

# Apply function to each COLUMN (axis=0, default)
print(df.apply(np.mean))
# math       81.6
# english    80.4
# science    82.6

# Apply function to each ROW (axis=1)
df['average'] = df.apply(lambda row: row.mean(), axis=1)
print(df['average'])
# Alice      87.67
# Bob        90.67
# Charlie    80.00
# Carol      84.33
# David      65.00

# Return Series per row (more structured)
def score_report(row):
    return pd.Series({
        'total': row.sum(),
        'avg': row.mean(),
        'max': row.max(),
        'min': row.min()
    })

report = df[['math', 'english', 'science']].apply(score_report, axis=1)
print(report)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

⚠️ Note: apply is not a vectorized operation — calling a function row by row on a large DataFrame will be very slow. If you can use vectorized operations (arithmetic, str methods), don't use apply. apply is "the last resort when nothing else works."

5. pipe Chaining

(1) Chained Data Flow

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: pipe pipeline operations (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# Raw data
df = pd.DataFrame({
    'name': ['  Alice  ', 'BOB', 'Charlie', '  carol  ', 'David'],
    'age': [28, -1, 25, 30, 999],
    'salary': [75000, np.nan, 68000, 88000, np.nan]
})

# Step-by-step without pipe (nested calls)
# result = assign_dept(replace_invalid(clean_names(df)))

# With pipe — readable left-to-right flow
def clean_names(df):
    df = df.copy()
    df['name'] = df['name'].str.strip().str.title()
    return df

def replace_invalid(df):
    df = df.copy()
    df.loc[df['age'] < 0, 'age'] = np.nan
    df.loc[df['age'] > 150, 'age'] = np.nan
    return df

def fill_salary(df):
    df = df.copy()
    df['salary'] = df['salary'].fillna(df['salary'].median())
    return df

result = (df
    .pipe(clean_names)
    .pipe(replace_invalid)
    .pipe(fill_salary)
)
print(result)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) pipe vs apply Comparison

Feature	pipe	apply
Input	Entire DataFrame	Row/column/Series
Returns	DataFrame (transformed)	Any type
Purpose	Multi-step pipeline	Single-step element/row/column operations
Chaining	✅ Natively supported	❌ Requires nesting
Readability	High (read top to bottom)	Medium

6. replace Value Replacement

(1) Exact Replacement

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: replace value replacement (Difficulty ⭐)

PYTHON

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'status': ['active', 'inactive', 'pending', 'active', 'unknown'],
    'priority': [1, 2, 3, 1, 99]
})

# Single value replacement
df['status'] = df['status'].replace('unknown', 'inactive')
print(df['status'])

# Multiple values replacement (dict)
df['status'] = df['status'].replace({
    'active': 'Active',
    'inactive': 'Inactive',
    'pending': 'Pending'
})
print(df['status'])

# Replace invalid sentinel value
df['priority'] = df['priority'].replace(99, np.nan)
print(df['priority'])

# Regex replacement
text = pd.Series(['Phone: 555-0100', 'Email: test@example.com'])
cleaned = text.str.replace(r'[\d-]+', '[REDACTED]', regex=True)
print(cleaned)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) replace vs map Comparison

Feature	replace	map
Unmatched values	Kept unchanged	Become NaN
Multi-value replacement	✅ Dictionary	✅ Dictionary
Regex	✅ regex=True	❌
Best for	Replacing specific values	Full mapping

🔥 Common Mistake: Values not covered by the dictionary in map will become NaN! In replace, uncovered values stay unchanged. If you want to "replace only a few values and keep the rest," use replace. If you want a "full mapping where unmapped values are discarded," use map.

7. assign New Columns

(1) Chaining Multiple New Columns

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: assign new columns (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'price': [100, 200, 150, 80, 300],
    'quantity': [5, 3, 10, 8, 2],
    'discount': [0.1, 0.2, 0.0, 0.15, 0.05]
})

# Add multiple columns in one chain
result = (df
    .assign(
        discounted_price=lambda x: x['price'] * (1 - x['discount']),
        total=lambda x: x['discounted_price'] * x['quantity'],
        is_bulk=lambda x: x['quantity'] >= 5
    )
)
print(result)
#   price  quantity  discount  discounted_price  total  is_bulk
# 0   100         5      0.10              90.0  450.0     True
# 1   200         3      0.20             160.0  480.0    False
# 2   150        10      0.00             150.0 1500.0     True
# 3    80         8      0.15              68.0  544.0     True
# 4   300         2      0.05             285.0  570.0    False

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

💡 Tip: Inside assign, use lambda x: to reference the current DataFrame. You can reference columns created earlier in the same assign call (e.g., total references discounted_price). This makes it more suitable for chaining than the df['new_col'] = ... syntax.

8. transform Group Transformation

(1) transform Preserves Original Shape

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: transform group standardization (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'student': ['Alice', 'Alice', 'Bob', 'Bob', 'Charlie', 'Charlie'],
    'subject': ['Math', 'English', 'Math', 'English', 'Math', 'English'],
    'score': [85, 90, 92, 88, 78, 82]
})

# Z-score normalization within each student
df['z_score'] = df.groupby('student')['score'].transform(
    lambda x: (x - x.mean()) / x.std()
)
print(df)

# Fill with group mean (instead of global mean)
df['score_filled'] = df.groupby('student')['score'].transform('mean')
print(df[['student', 'score', 'score_filled']])

# Rank within group
df['rank_in_class'] = df.groupby('subject')['score'].transform('rank', method='min')
print(df[['student', 'subject', 'score', 'rank_in_class']])

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) transform vs apply Comparison

Feature	transform	apply
Output shape	Same as input	Can vary
After groupby	Returns same-length result per group	Returns any result per group
Aggregation	❌ Cannot reduce	✅ Can
Broadcasting	✅ Auto-broadcasts to original rows	❌
Common uses	Standardization/filling/ranking	Aggregation/complex computation

9. Choosing the Right Transformation Method

(1) Quick Reference for 6 Transformation Methods

graph TB
    Q["Need to transform data?"] --> TYPE{"Transform type?"}
    TYPE -->|"One-to-one mapping"| MAP["map()"]
    TYPE -->|"Row/Col function"| APPLY["apply()"]
    TYPE -->|"Multi-step pipeline"| PIPE["pipe()"]
    TYPE -->|"Replace specific values"| REPLACE["replace()"]
    TYPE -->|"Add new columns"| ASSIGN["assign()"]
    TYPE -->|"Group-level transform"| TRANS["transform()"]
    MAP --> NOTE1["✅ dict/function<br>❌ unmapped → NaN"]
    APPLY --> NOTE2["⚠️ Slow for large DF<br>✅ Flexible"]
    PIPE --> NOTE3["✅ Readable chain<br>✅ Reusable steps"]
    REPLACE --> NOTE4["✅ Unmatched kept<br>✅ Regex support"]
    ASSIGN --> NOTE5["✅ Chain-friendly<br>✅ Multiple columns"]
    TRANS --> NOTE6["✅ Same shape output<br>✅ Group broadcast"]

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) Performance Ranking

Rank	Method	Performance	Reason
1	Vectorized operations	Fastest	Executed at C level, no Python loop
2	map (dictionary)	Fast	O(1) lookup
3	replace	Fast	Optimized under the hood
4	map (function)	Medium	Per-element function calls
5	apply	Slow	Per-row/column Python calls
6	apply + complex logic	Slowest	Function overhead compounds

💡 Tip: If you can write df['col'] * 2, don't write df['col'].apply(lambda x: x * 2). Vectorized operations are 10-100x faster than apply.

10. Complete Example: E-commerce Data Transformation Pipeline

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Full data transformation pipeline (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# ============================================
# Comprehensive example: E-commerce data
# transformation pipeline
# ============================================

# 1. Raw data
np.random.seed(42)
df = pd.DataFrame({
    'product': [f'Item_{i:03d}' for i in range(1, 21)],
    'category': np.random.choice(['Electronics', 'Clothing', 'Home'], 20),
    'price': np.round(np.random.uniform(10, 500, 20), 2),
    'cost': np.round(np.random.uniform(5, 250, 20), 2),
    'quantity_sold': np.random.randint(1, 100, 20),
    'rating': np.round(np.random.uniform(2.0, 5.0, 20), 1)
})

# 2. Step-by-step pipeline with pipe
def add_margin(df):
    df = df.copy()
    df['margin_pct'] = ((df['price'] - df['cost']) / df['price'] * 100).round(1)
    return df

def categorize_price(df):
    df = df.copy()
    df['price_tier'] = df['price'].map(
        lambda p: 'Budget' if p < 50 else ('Mid' if p < 200 else 'Premium')
    )
    return df

def add_revenue(df):
    return df.assign(
        revenue=lambda x: x['price'] * x['quantity_sold'],
        is_top_rated=lambda x: x['rating'] >= 4.0
    )

def normalize_rating(df):
    df = df.copy()
    df['rating_zscore'] = df.groupby('category')['rating'].transform(
        lambda x: (x - x.mean()) / x.std()
    )
    return df

# 3. Execute pipeline
result = (df
    .pipe(add_margin)
    .pipe(categorize_price)
    .pipe(add_revenue)
    .pipe(normalize_rating)
)

# 4. Summary
print("=== Transformation Result ===")
print(result[['product', 'category', 'price_tier', 'margin_pct', 'revenue', 'is_top_rated']].head(10))
print(f"\nTotal revenue: ${result['revenue'].sum():,.2f}")
print(f"Top-rated items: {result['is_top_rated'].sum()}")
print(f"Price tier distribution:\n{result['price_tier'].value_counts()}")

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

❓ FAQ

Q What's the difference between map and apply?

A map only works on a Series, performing a one-to-one mapping on each element (accepting a dictionary or function). Unmapped values become NaN. apply works on both Series and DataFrame, supporting row/column-level operations (axis=0/1) with flexible return types. Use map for simple mappings (faster), and apply for complex logic (more flexible).

Q Why is apply slow?

A apply is essentially a Python loop — it calls a Python function once per row/column, and function call overhead is enormous. Vectorized operations execute at the C level without this overhead. Rule of thumb: if you can write df['a'] + df['b'], don't write df.apply(lambda r: r['a']+r['b'], axis=1). apply is a last resort.

Q What's the difference between pipe and apply?

A pipe operates on the entire DataFrame, accepting and returning a DataFrame — it's designed for multi-step pipelines. apply operates on rows/columns/Series for per-element or per-row computation. The value of pipe is readability — it turns f3(f2(f1(df))) into df.pipe(f1).pipe(f2).pipe(f3), where reading top to bottom gives you the execution order.

Q What's the difference between replace and map?

A The biggest difference is how they handle unmatched values — replace keeps the original value unchanged, while map turns unmatched values into NaN. Use replace when you only want to swap a few specific values; use map for full mapping where every value must be transformed. replace also supports regex replacement (regex=True), which map does not.

Q What's the difference between assign and df['col']=...?

A assign returns a new DataFrame (without modifying the original), making it ideal for chaining. df['col']=... modifies in place. Inside assign, you can use lambda to reference columns created in the same step, which direct assignment cannot do. Recommendation: use assign for chained operations; for simple cases, direct assignment is fine.

Q What's the difference between transform and apply after groupby?

A transform returns a result with the same length as the original DataFrame (broadcasting within each group), while apply can return any shape. For example: groupby + transform('mean') returns the group mean for every row (length unchanged), whereas groupby + apply('mean') returns one mean per group (length = number of groups). Use transform to preserve the original shape; use apply/agg for aggregation.

Q What exactly does "vectorized" mean?

A Vectorization means using Pandas/NumPy built-in operations instead of Python loops. df['a'] + df['b'] is vectorized (C-level computation over the entire array), while df.apply(lambda r: r['a']+r['b'], axis=1) is not (Python calls row by row). Vectorized operations are 10-100x faster. Use built-in operations whenever possible instead of apply.

📖 Summary

map is for one-to-one Series mapping; dictionary mapping is fastest; unmatched values become NaN
apply handles complex per-row/per-column logic — flexible but slow; prefer vectorized operations when possible
pipe builds multi-step pipelines, turning nested calls into a readable top-to-bottom chain
replace swaps specific values while keeping unmatched ones unchanged; supports regex
assign adds columns in a chain; lambda can reference columns created in the same step
transform performs group transformations while preserving the original shape; ideal for standardization/filling/ranking
Performance ranking: vectorized > map(dictionary) > replace > map(function) > apply

📝 Exercises

Basic (Difficulty ⭐): Create a score Series (0-100). Use map + a dictionary to classify scores into High(≥70)/Medium(≥50)/Low(<50), then use replace to change Medium to Mid.
Intermediate (Difficulty ⭐⭐): Create an employee DataFrame (name/salary/department). Use pipe to build a 3-step pipeline: clean name (strip+title) → add an after-tax salary column → flag whether each employee earns above their department average.
Challenge (Difficulty ⭐⭐⭐): Create a 20-row product dataset. Use assign to add 3 columns at once (revenue/margin/tier), use groupby+transform to compute z-scores within each category, and finally use pipe to combine all steps into a single pipeline.

← Previous: Handling Duplicate Data · Next: Group Aggregation →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: Data Transformation

1. What You Will Learn

2. Alice's Grade Conversion

(1) The Problem: Converting 0-100 Scores to ABCD Grades

(2) Solution: map + Dictionary Mapping

▶ Example

3. map Element Mapping

(1) Three Ways to Use map

▶ Example

(2) When to Use map

4. apply Row/Column Functions

(1) Series apply

▶ Example

(2) DataFrame apply

▶ Example

5. pipe Chaining

(1) Chained Data Flow

▶ Example

(2) pipe vs apply Comparison

6. replace Value Replacement

(1) Exact Replacement

▶ Example

(2) replace vs map Comparison

7. assign New Columns

(1) Chaining Multiple New Columns

▶ Example

8. transform Group Transformation

(1) transform Preserves Original Shape

▶ Example

(2) transform vs apply Comparison

9. Choosing the Right Transformation Method

(1) Quick Reference for 6 Transformation Methods

(2) Performance Ranking

10. Complete Example: E-commerce Data Transformation Pipeline

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好