Pandas: Performance Optimization

Q: How do you use memory_usage?

df.memory_usage(deep=True) returns the byte count for each column. deep=True calculates actual string memory for object columns (otherwise it only counts pointer sizes). Total: df.memory_usage(deep=True).sum() . Use this function to compare before and after optimization. category columns store only integer codes + a dictionary, using far less memory than object.

Last updated: 2026-08-26

Bob's apply script took 10 minutes; Alice switched to vectorization and finished in 30 seconds — a 20x speedup. This is not an isolated case: 90% of Pandas performance problems come from "using loops instead of vectorization." This section covers the complete performance toolkit, from coding habits to engine-level optimization, helping you write Pandas code that is both fast and memory-efficient.

⚠️ Note: The code below requires a local Python environment to run.

1. What You'll Learn

❶ Vectorization vs apply vs loops
❷ eval / query expression engine
❸ category memory optimization
❹ downcast numeric compression
❺ Copy-on-Write and big data strategies

2. Bob's 10-Minute apply

(1) The Pain: Row-by-Row Loops Are Too Slow

Bob used iterrows to process 100K rows, and it took 10 minutes:

PYTHON

import pandas as pd
import numpy as np

# DON'T DO THIS — very slow!
# for i, row in df.iterrows():
#     df.loc[i, 'new_col'] = row['a'] * row['b'] + row['c']

TEXT 📖 Display only

> **Output:** Run this 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) The Fix: Vectorization in 30 Seconds

▶ Example: Vectorization vs Loops (Difficulty ⭐)

PYTHON

import pandas as pd
import numpy as np

np.random.seed(42)
df = pd.DataFrame({
    'a': np.random.randn(100000),
    'b': np.random.randn(100000),
    'c': np.random.randn(100000)
})

# ❌ Slow: iterrows (~300s for 100K rows)
# for i, row in df.iterrows():
#     df.loc[i, 'result'] = row['a'] * row['b'] + row['c']

# ✅ Fast: vectorized (~0.03s)
df['result'] = df['a'] * df['b'] + df['c']

print(f"Result mean: {df['result'].mean():.4f}")
# ~30 seconds vs ~0.03 seconds — 1000x faster!

TEXT 📖 Display only

> **Output:** Run this 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. Performance Ranking: Loops → apply → Vectorization

(1) Comparing 4 Methods

▶ Example

TEXT 📖 Display only

> **Output:** Run this 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.

: Speed comparison of 4 methods (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

np.random.seed(42)
df = pd.DataFrame({
    'a': np.random.randn(50000),
    'b': np.random.randn(50000)
})

# Method 1: iterrows (slowest — NEVER use for computation)
# ~60 seconds for 50K rows

# Method 2: apply (slow)
result2 = df.apply(lambda row: row['a'] + row['b'], axis=1)

# Method 3: vectorized (fast)
result3 = df['a'] + df['b']

# Method 4: numpy (fastest)
result4 = (df['a'].values + df['b'].values)

# Verify same result
print(np.allclose(result2.values, result3.values))  # True
print(np.allclose(result3.values, result4))          # True

TEXT 📖 Display only

> **Output:** Run this 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 Table

Rank	Method	Relative Speed	Best For
1	NumPy operations	Fastest (1x)	Pure numeric
2	Pandas vectorization	Fast (1-2x)	Labels + numeric
3	map (dictionary)	Medium (5-10x)	One-to-one mapping
4	apply	Slow (50-100x)	Complex logic
5	itertuples	Very slow (200x)	Must iterate rows
6	iterrows	Slowest (500x)	Never use

🔥 Golden Rule: Use vectorization whenever possible. If you can't, use apply. Loops are the last resort. iterrows should always be the last choice (or no choice at all).

4. eval / query Expression Engine

(1) eval Speeds Up Complex Operations

▶ Example

TEXT 📖 Display only

> **Output:** Run this 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.

: eval expression engine (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

np.random.seed(42)
df = pd.DataFrame({
    'a': np.random.randn(100000),
    'b': np.random.randn(100000),
    'c': np.random.randn(100000),
    'd': np.random.randn(100000)
})

# Complex expression — eval avoids intermediate DataFrames
result1 = df['a'] * df['b'] + df['c'] / df['d'] - df['a'] ** 2
result2 = df.eval('a * b + c / d - a ** 2')

print(np.allclose(result1, result2))  # True
# eval is faster when:
# 1. Many columns in the expression (>4)
# 2. DataFrame is large (>10K rows)
# 3. Memory is limited (avoids intermediates)

# query for filtering
filtered = df.query('a > 0 and b < 0')
# Equivalent: df[(df['a'] > 0) & (df['b'] < 0)]

TEXT 📖 Display only

> **Output:** Run this 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) eval vs Regular Operations

Scenario	eval Advantage	eval Disadvantage
Multi-column complex expressions	Fewer intermediate variables	Limited syntax
Large DataFrame	Saves memory	No benefit for small data
Simple operations	No advantage	Poor readability
Conditional filtering (query)	Better readability	Limited flexibility

5. category Memory Optimization

▶ Example

TEXT 📖 Display only

> **Output:** Run this 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.

: category type compression (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

np.random.seed(42)
# Simulate: 100K rows, 10 unique cities
df = pd.DataFrame({
    'city': np.random.choice(['NYC', 'LA', 'Chicago', 'Houston', 'Phoenix',
                               'Philly', 'San Antonio', 'San Diego', 'Dallas', 'Austin'], 100000),
    'status': np.random.choice(['Active', 'Inactive', 'Pending', 'Churned'], 100000)
})

# Before: object type
mem_before = df.memory_usage(deep=True).sum()
print(f"Before (object): {mem_before / 1024:.1f} KB")

# After: category type
df['city'] = df['city'].astype('category')
df['status'] = df['status'].astype('category')
mem_after = df.memory_usage(deep=True).sum()
print(f"After (category): {mem_after / 1024:.1f} KB")
print(f"Savings: {(1 - mem_after / mem_before) * 100:.1f}%")
# Typically 80-95% memory reduction for low-cardinality strings!

TEXT 📖 Display only

> **Output:** Run this 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.

(1) When to Use category

Condition	Suitable	Not Suitable
Unique values < 50% of total rows	✅	❌
String columns	✅	—
Ordering is meaningful	✅	Random unordered strings
Need string methods	❌ (.str limited)	✅ (use object)
Frequent groupby	✅ (faster)	—

6. downcast Numeric Compression

▶ Example

TEXT 📖 Display only

> **Output:** Run this 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.

: downcast numeric types (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

np.random.seed(42)
df = pd.DataFrame({
    'id': np.arange(100000),
    'age': np.random.randint(0, 120, 100000),
    'score': np.random.randint(0, 100, 100000),
    'price': np.round(np.random.uniform(0, 1000, 100000), 2),
    'flag': np.random.choice([0, 1], 100000)
})

# Check current types and memory
print("Before:")
print(df.dtypes)
print(f"Memory: {df.memory_usage(deep=True).sum() / 1024:.1f} KB")

# Downcast integers
df['id'] = pd.to_numeric(df['id'], downcast='unsigned')   # uint32 → uint32
df['age'] = pd.to_numeric(df['age'], downcast='unsigned')  # int64 → uint8 (0-255)
df['score'] = pd.to_numeric(df['score'], downcast='unsigned')
df['flag'] = pd.to_numeric(df['flag'], downcast='unsigned')  # int64 → uint8

# Downcast floats
df['price'] = pd.to_numeric(df['price'], downcast='float')  # float64 → float32

print("\nAfter:")
print(df.dtypes)
print(f"Memory: {df.memory_usage(deep=True).sum() / 1024:.1f} KB")
# Typical savings: 50-75%

TEXT 📖 Display only

> **Output:** Run this 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.

7. Copy-on-Write and Big Data Strategies

(1) Copy-on-Write (CoW)

▶ Example

TEXT 📖 Display only

> **Output:** Run this 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.

: CoW behavior (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# Pandas 3.x: CoW is default
pd.options.mode.copy_on_write = True

df = pd.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 6]})

# With CoW: slice returns a view, modification creates a copy
subset = df[['a']]  # no copy until modified
df.loc[0, 'a'] = 99  # modifies df, subset unchanged
print(subset)  # still [1, 2, 3] — CoW protects

# Safe patterns under CoW:
df['c'] = df['a'] + df['b']  # ✅ assign new column
df = df.drop(columns='c')     # ✅ reassign

# Avoid:
# subset['a'] = 10  # ❌ SettingWithCopyWarning (pre-CoW issue, fixed by CoW)

TEXT 📖 Display only

> **Output:** Run this 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) Big Data Strategies

Data Size	Strategy
< 1GB	Standard Pandas
1-5GB	downcast + category + eval
5-50GB	chunksize chunked processing
> 50GB	Consider Dask / Polars / PySpark

▶ Example

TEXT 📖 Display only

> **Output:** Run this 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.

: chunksize chunking (Difficulty ⭐)

PYTHON

import pandas as pd
from io import StringIO

# Simulate large CSV
csv_data = "id,value\n" + "\n".join([f"{i},{i*10}" for i in range(1000)])

# Process in chunks
total = 0
for chunk in pd.read_csv(StringIO(csv_data), chunksize=200):
    total += chunk['value'].sum()

print(f"Total: {total}")

TEXT 📖 Display only

> **Output:** Run this 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.

8. Full Example: End-to-End Optimization for One Million Rows

(6) ▶ Performance Optimization Checklist

graph TB
    A[Performance Optimization] --> B[1. Vectorization over loops]
    A --> C[2. eval / query acceleration]
    A --> D[3. category string compression]
    A --> E[4. downcast numeric compression]
    A --> F[5. chunksize chunking]
    A --> G[6. Copy-on-Write]
    B --> H[100-500x speedup]
    C --> I[Save memory, avoid intermediates]
    D --> J[80-95% memory savings]
    E --> K[50-75% memory savings]
    F --> L[Process data beyond memory]
    G --> M[Zero-copy slicing]

TEXT 📖 Display only

> **Output:** Run this 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.

▶ Example

TEXT 📖 Display only

> **Output:** Run this 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.

: From slow to fast, full workflow (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# ============================================
# Comprehensive example: 1M rows optimization
# iterrows → apply → vectorized → eval → category
# ============================================

np.random.seed(42)
df = pd.DataFrame({
    'city': np.random.choice([f'City_{i}' for i in range(20)], 1000000),
    'category': np.random.choice(['Electronics', 'Clothing', 'Home', 'Sports'], 1000000),
    'price': np.round(np.random.uniform(10, 500, 1000000), 2),
    'quantity': np.random.randint(1, 100, 1000000),
    'discount': np.random.choice([0, 0.1, 0.2, 0.3], 1000000)
})

# Step 1: Baseline memory
mem1 = df.memory_usage(deep=True).sum() / (1024**2)
print(f"Step 1 — Raw memory: {mem1:.1f} MB")

# Step 2: Downcast numerics
df['quantity'] = pd.to_numeric(df['quantity'], downcast='unsigned')
df['price'] = pd.to_numeric(df['price'], downcast='float')
df['discount'] = pd.to_numeric(df['discount'], downcast='float')
mem2 = df.memory_usage(deep=True).sum() / (1024**2)
print(f"Step 2 — After downcast: {mem2:.1f} MB ({(1-mem2/mem1)*100:.0f}% saved)")

# Step 3: Category for strings
df['city'] = df['city'].astype('category')
df['category'] = df['category'].astype('category')
mem3 = df.memory_usage(deep=True).sum() / (1024**2)
print(f"Step 3 — After category: {mem3:.1f} MB ({(1-mem3/mem1)*100:.0f}% saved total)")

# Step 4: Vectorized calculation (fast)
df['revenue'] = df['price'] * df['quantity'] * (1 - df['discount'])
print(f"Step 4 — Revenue calculated (vectorized, ~0.01s)")

# Step 5: eval alternative
df['revenue2'] = df.eval('price * quantity * (1 - discount)')
print(f"Step 5 — Revenue via eval")

# Verify
print(f"Results match: {np.allclose(df['revenue'], df['revenue2'])}")

TEXT 📖 Display only

> **Output:** Run this 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 iterrows and itertuples?

A iterrows returns (index, Series) pairs, creating a new Series each time — extremely slow (~500x slower than vectorization). itertuples returns (index, namedtuple) pairs without creating a Series — 5-10x faster than iterrows but still 50x slower than vectorization. Use itertuples when you must iterate row by row, but first consider whether vectorization is possible.

Q When is eval faster?

A eval is faster with large data (>10K rows) and complex multi-column expressions — it avoids creating intermediate DataFrames and uses the numexpr engine for acceleration. For small data or simple expressions, eval is actually slower (engine startup overhead). Rule of thumb: eval is worthwhile only for compound operations with 4+ columns on large datasets.

Q Does inplace really save memory?

A In Pandas 2.x, inplace usually does not save memory — internally it may still create a temporary copy before assigning. Under Pandas 3.x Copy-on-Write mode, inplace and non-inplace operations have essentially the same memory overhead. Recommendation: avoid inplace; use the df = df.method() assignment pattern for clearer semantics.

Q What is Copy-on-Write?

A CoW is the default behavior in Pandas 3.x — data shares underlying memory, and a copy is only created when modification occurs. Benefits: slicing doesn't copy (saves memory), chained assignment is safe (no SettingWithCopyWarning). Impact: some inplace operations behave differently and require reassignment.

Q What about extremely large files?

A Three-tier strategy: ① chunksize for chunked reading + aggregation (suitable for divisible statistics); ② downcast/category to reduce per-row memory (fit more rows in memory); ③ switch tools — Dask (distributed Pandas API), Polars (ultra-fast DataFrame written in Rust), PySpark (cluster-level). A single machine's memory limit is roughly 3-5x the data size.

Q Is apply always slower than vectorization?

A In most cases, yes — apply calls a Python function row by row, while vectorization executes at the C level. Rare exception: when vectorization requires creating multiple huge intermediate arrays, apply may save memory (but not time). Strategy: prefer vectorization first, try eval if performance is insufficient, and use apply only as a last resort.

Q How do you use memory_usage?

A df.memory_usage(deep=True) returns the byte count for each column. deep=True calculates actual string memory for object columns (otherwise it only counts pointer sizes). Total: df.memory_usage(deep=True).sum(). Use this function to compare before and after optimization. category columns store only integer codes + a dictionary, using far less memory than object.

📖 Summary

Golden rule: vectorization > map > apply > itertuples > iterrows
eval/query uses the numexpr engine to accelerate complex expressions — worthwhile for large data with many columns
category type saves 80-95% memory for low-cardinality string columns
downcast converts int64 → uint8/uint16/uint32, float64 → float32, saving 50-75%
Copy-on-Write enables zero-copy slicing and safe assignment
Big data strategy: downcast + category → chunksize → Dask/Polars

📝 Exercises

Basic (Difficulty ⭐): Create a 10K-row DataFrame, compute a new column using both apply and vectorization, and compare memory_usage.
Intermediate (Difficulty ⭐⭐): Create a 100K-row DataFrame with object columns, optimize memory using category + downcast, and calculate the percentage saved.
Challenge (Difficulty ⭐⭐⭐): Create a 1-million-row DataFrame (with category/float/int columns), then complete: vectorized calculation → eval calculation → compare results → track memory_usage throughout → produce a full optimization report.

← Previous: Visualization · Next: Styled Output →

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: Performance Optimization

1. What You'll Learn

2. Bob's 10-Minute apply

(1) The Pain: Row-by-Row Loops Are Too Slow

(2) The Fix: Vectorization in 30 Seconds

▶ Example: Vectorization vs Loops (Difficulty ⭐)

3. Performance Ranking: Loops → apply → Vectorization

(1) Comparing 4 Methods

▶ Example

(2) Performance Ranking Table

4. eval / query Expression Engine

(1) eval Speeds Up Complex Operations

▶ Example

(2) eval vs Regular Operations

5. category Memory Optimization

▶ Example

(1) When to Use category

6. downcast Numeric Compression

▶ Example

7. Copy-on-Write and Big Data Strategies

(1) Copy-on-Write (CoW)

▶ Example

(2) Big Data Strategies

▶ Example

8. Full Example: End-to-End Optimization for One Million Rows

(6) ▶ Performance Optimization Checklist

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好