Pandas分组计算百分位性能优化求助：1800万行数据处理过慢

Let's tackle this performance issue head-on. Your original groupby.apply approach is slow because it creates a full DataFrame copy for each of your 3 million groups—this overhead adds up massively for large datasets. Here are three actionable, easy-to-implement optimizations that don't require deep knowledge of multiprocessing:

1. Use Pandas' Native groupby.agg (Fastest for In-Memory Data) #

Skip the custom apply function entirely and use Pandas' built-in aggregation with quantile. This leverages vectorized operations instead of per-group DataFrame copies, which will cut down runtime drastically.

First compute the grouped percentiles, then merge the result back to your original DataFrame:

import pandas as pd
import numpy as np

# Calculate grouped 20th percentile
grouped_quantiles = (
    sales.groupby(['Barcode', 'ShopCode'])['SaleQTY']
    .agg(lambda x: x.quantile(0.2))
    .reset_index(name='Quantile')
)

# Merge back to original data to get the quantile for every row
sales_with_quant = sales.merge(grouped_quantiles, on=['Barcode', 'ShopCode'], how='left')

This approach avoids the overhead of copying entire DataFrames per group and uses Pandas' optimized aggregation pathways. For most in-memory datasets (even 18M rows), this should be 10-100x faster than your original apply method.

2. Use Dask for Automatic Parallel Processing (No Multiprocessing Code Needed) #

If the data is pushing the limits of your memory or you want to fully utilize your 32 cores, Dask DataFrame is perfect. It splits your data into chunks, processes them in parallel across cores, and handles merging results automatically—no manual multiprocessing setup required.

Here's how to implement it:

import dask.dataframe as dd

# Convert your Pandas DataFrame to Dask (split into 32 chunks, matching your core count)
dask_sales = dd.from_pandas(sales, npartitions=32)

# Compute grouped 20th percentile across parallel chunks
grouped_quantiles = (
    dask_sales.groupby(['Barcode', 'ShopCode'])['SaleQTY']
    .quantile(0.2)
    .reset_index(name='Quantile')
)

# Merge back and convert to Pandas DataFrame
sales_with_quant = dask_sales.merge(grouped_quantiles, on=['Barcode', 'ShopCode'], how='left').compute()

Dask will handle all the parallelization under the hood. If your data is stored in a file (like CSV/Parquet), reading it directly with dd.read_csv() or dd.read_parquet() will be even more efficient than converting from Pandas.

3. Use Swifter for Auto-Optimized apply #

If you prefer to keep a similar structure to your original code, Swifter automatically optimizes apply operations by choosing between Pandas, Dask, or Numba depending on what's fastest for your data.

Install it first with pip install swifter, then use it like this:

import swifter

def calculate_quantile(group):
    return group['SaleQTY'].quantile(0.2)

# Swifter will auto-optimize the apply call
grouped_quantiles = (
    sales.groupby(['Barcode', 'ShopCode'])
    .swifter.apply(calculate_quantile)
    .reset_index(name='Quantile')
)

sales_with_quant = sales.merge(grouped_quantiles, on=['Barcode', 'ShopCode'], how='left')

Swifter takes the guesswork out of optimization and will likely be much faster than your original apply without requiring major code changes.

Why Your Numba Attempt Failed #

Numba's nopython=True mode can't interact with Pandas objects (like DataFrames or Series) directly. To use Numba here, you'd need to convert your data to NumPy arrays and implement manual grouping logic, which is more complex than the above approaches. For your use case, the native Pandas/Dask methods are better options unless you're comfortable writing low-level array code.

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