1000万行DataFrame列操作优化求助：记忆化函数仍存性能瓶颈

Great question—handling 10 million rows in Python always requires ditching Python-level loops when possible, even with memoization. Your current approach uses a list comprehension with a memoized function, but the per-iteration overhead of Python loops still adds up fast, especially with 10M elements. Let's walk through two optimized approaches tailored to your scenario (far fewer unique values than total rows):

1. Fully Vectorized Operation (Fastest Option) #

Since your foo function has simple conditional logic, we can replace it entirely with NumPy/Pandas vectorized operations. These run in C under the hood, which is orders of magnitude faster than Python loops.

Your foo logic breaks down to:

• If x > 0: return x * -1
• Else: return x * 10

We can implement this directly with np.where, then compute your index using np.maximum (a cleaner, faster alternative to your np.where for max selection):

import numpy as np
import pandas as pd

# Replace list comprehension and foo function entirely with vectorized logic
df['new'] = np.where(df['column'] > 0, df['column'] * -1, df['column'] * 10)
# Compute index by taking the max of 'new' and 'other' columns
index = np.maximum(df['new'], df['other'])
df.set_index(index, inplace=True)

This approach avoids any Python-level loops entirely—all operations are handled on entire arrays at once, which is perfect for 10M rows.

2. Precompute Mappings for Unique Values (For Complex foo Logic) #

If your foo function might grow more complex later (beyond simple conditionals), we can leverage the fact that your column has far fewer unique values (≈100k, two orders of magnitude less than 10M). Instead of running foo on every row, we only run it once per unique value, then map the results to the entire column.

def foo(x):
    if x > 0:
        bar = -1
    else:
        bar = 10
    x *= bar
    return x

# Extract all unique values from the target column
unique_vals = df['column'].unique()
# Precompute transformed values for each unique entry
value_map = {val: foo(val) for val in unique_vals}
# Map precomputed values to the full column (vectorized lookup under the hood)
df['new'] = df['column'].map(value_map)
# Compute index and set it
index = np.maximum(df['new'], df['other'])
df.set_index(index, inplace=True)

This cuts down foo calls from 10M to ~100k, and Pandas' map is optimized to handle bulk lookups quickly.

Why Your Original Approach Is Slow #

Even with memoization, the list comprehension runs a Python loop over 10M elements. Each iteration involves a function call and dictionary lookup (for memoization), which adds significant overhead. Vectorized operations or precomputed mappings eliminate this overhead by working on entire arrays or only the unique values.

内容的提问来源于stack exchange，提问作者Batman