如何加速800万行多维度销售DataFrame的分组移位列生成循环？

Hey Jordan, that's a super common pain point when working with large time-series datasets—your current loop-and-concat approach is slow because every pd.concat operation creates a copy of your data, which adds up exponentially when you're dealing with nearly 10k product-location groups. Let's switch to pandas' optimized grouping tools to cut down runtime drastically.

Why Your Current Code Is Slow #

• Repeated Data Copies: Each pd.concat(df1, df_) duplicates existing data in df1 plus the new group, leading to massive memory overhead and slowdowns as df1 grows.
• Python-Level Loop: Iterating over each group with a Python loop misses out on pandas' vectorized, C-optimized operations, which are designed for exactly this kind of grouped transformation.

Faster, Vectorized Solution #

Instead of manually looping through groups, use groupby directly on your product and location columns to compute all 7 shift columns in a vectorized way. This avoids all the repeated copying and leverages pandas' optimized backend.

Here's the revised code:

import pandas as pd

# Ensure your DataFrame is sorted correctly (group内日期顺序必须正确)
df = df.sort_values(['product', 'location', 'date'])

# Generate all 7 shifted columns in a loop using groupby
for offset in range(1, 8):
    df[f'eaches_{offset}'] = df.groupby(['product', 'location'])['eaches'].shift(-offset)

Even More Efficient: Batch Column Creation

If you want to avoid looping through offsets (though the above is already way faster), you can generate all columns in one groupby.agg call:

# Create a dictionary mapping new column names to shift operations
shift_ops = {f'eaches_{n}': lambda x: x.shift(-n) for n in range(1, 8)}

# Compute all shifted columns at once and join back to the original DataFrame
shifted_df = df.groupby(['product', 'location'])['eaches'].agg(**shift_ops)
df = df.join(shifted_df)

Key Improvements #

1. No More Concat: We modify the original DataFrame in-place (or join a precomputed shifted DataFrame) instead of building a new one incrementally.
2. Vectorized Group Operations: groupby uses pandas' optimized C extensions under the hood, which are orders of magnitude faster than Python loops for large datasets.
3. Cleaner Code: We eliminate the need for the sort_values helper column by grouping directly on the original product and location columns.

Quick Notes #

• Make sure your date column is a datetime type (not a string) to ensure proper chronological sorting. If it's not, convert it first with df['date'] = pd.to_datetime(df['date']).
• For 800k rows, this approach should reduce runtime from hours (or more) to just a few minutes, depending on your hardware.
• If memory is a concern, you can drop intermediate columns or use df.groupby(...).shift() with dropna=False to avoid extra memory overhead.

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