如何向量化/提升Pandas fillna()等填充函数及滚动填充的大数据集性能？

Great question—dealing with missing data on 250M rows is no joke, and Pandas' built-in fill methods can definitely choke on that scale, especially when combined with rolling operations. Let's break down the most effective optimizations, split into general fill fixes and rolling-specific solutions.

These tweaks apply to basic fillna(), ffill(), and bfill() calls before even getting to rolling scenarios:

• Switch to PyArrow-backed DataFrames
  Pandas 2.0+ introduced PyArrow as a dtype backend, which drastically speeds up many string and nullable-type operations (including fills). Converting your DataFrame to use PyArrow can cut fill operation time by 50%+ in many cases:

  # Convert to PyArrow-backed dtypes
  df = df.convert_dtypes(dtype_backend="pyarrow")
  # Run your fill operation
  df['col'] = df['col'].ffill()
  

• Use NumPy vectorization instead of Pandas methods
  For simple forward/backward fills, pure NumPy operations avoid Pandas' overhead. Here's a vectorized ffill implementation that outperforms df.ffill() on large datasets:

  import numpy as np
  
  mask = df['col'].isna().to_numpy()
  idx = np.where(~mask, np.arange(len(df)), 0)
  idx = np.maximum.accumulate(idx)  # Track last non-NA index
  df['col_filled'] = df['col'].to_numpy()[idx]
  

• Minimize memory copies with inplace=True
  By default, Pandas creates a new DataFrame when running fills. Using inplace=True modifies the original data directly, reducing memory overhead (just make sure you have a backup of your raw data first):

  df['col'].ffill(inplace=True)
  

This is where most people hit bottlenecks—rolling().{func}.fillna() is slow because it iterates over windows sequentially. Try these alternatives:

• Replace rolling fills with vectorized column shifts
  For simple rolling forward/backward fills (e.g., fill missing values with the last non-NA in a 3-row window), you can stack shifted columns and use bfill()/ffill() across columns instead of rolling:

  # Create shifted versions of the column for the window size
  window_size = 3
  shifted_cols = [df['col'].shift(i) for i in range(window_size)]
  # Combine columns and take the first non-NA value from the right (ffill logic)
  df['rolling_ffill'] = pd.concat(shifted_cols, axis=1).bfill(axis=1).iloc[:, 0]
  

  This is fully vectorized and avoids the per-window loop overhead.

• Use Numba for JIT-compiled custom rolling functions
  For complex rolling fill logic, Numba compiles Python code to machine code, matching C-level speeds. Here's an example of a JIT-compiled rolling forward fill:

  import numba
  
  @numba.jit(nopython=True)
  def rolling_ffill_numba(arr, window):
      result = arr.copy()
      for i in range(1, len(arr)):
          if np.isnan(result[i]):
              # Look back up to window rows for the last non-NA value
              for j in range(1, window + 1):
                  if i - j >= 0 and not np.isnan(result[i - j]):
                      result[i] = result[i - j]
                      break
      return result
  
  # Apply to your column
  df['rolling_ffill'] = rolling_ffill_numba(df['col'].to_numpy(), window=3)
  

• Go out-of-core with Dask
  If your dataset is too big to fit in memory, Dask splits data into partitions and processes them in parallel. Its rolling and fill operations are optimized for scale:

  import dask.dataframe as dd
  
  # Convert Pandas DataFrame to Dask (adjust partitions based on your CPU cores)
  dask_df = dd.from_pandas(df, npartitions=8)
  # Run rolling mean + backward fill in parallel
  filled_col = dask_df['col'].rolling(7).mean().bfill()
  # Bring results back to Pandas if needed
  result = filled_col.compute()
  

• Downcast data types：Reduce memory usage by using smaller dtypes (e.g., float32 instead of float64 if precision allows, or nullable integer types). Smaller memory footprints speed up all operations.
• Filter irrelevant rows first：If you only need to process a subset of rows, filter them before running fills to reduce data volume.
• Use swifter for automatic parallelization：The swifter library automatically chooses the fastest execution method (Pandas, Dask, or Numba) for your fill operations:

  import swifter
  df['col'] = df['col'].swifter.rolling(5).bfill()
  

Hope these methods help you cut down runtime significantly—250M rows is a massive dataset, so combining a few of these (like PyArrow + Numba for rolling fills) should give you the biggest gains.

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