Python Pandas：基于枚举日期堆叠生成记录的向量化实现

Hey there! I totally get it—when dealing with massive datasets, loop-based or apply-heavy approaches can grind to a halt. Let’s build a fully vectorized solution that’ll handle your date expansion task blazingly fast, no slow Python loops required.

The Core Idea #

We’ll lean into NumPy’s optimized C-backed vectorized operations to:

• Repeat each row exactly units times
• Generate sequential date offsets for each repeated entry
• Add those offsets to the original transaction_dt to create our enumerated dates

Step-by-Step Implementation #

First, let’s use sample data to demonstrate the workflow (imagine this has millions of rows):

import pandas as pd
import numpy as np

# Sample large-scale DataFrame
df = pd.DataFrame({
    'transaction_id': [1001, 1002, 1003],
    'transaction_dt': pd.to_datetime(['2024-03-15', '2024-04-20', '2024-05-05']),
    'units': [5, 3, 7],
    'total_amount': [450, 210, 630]
})

Now the vectorized magic:

# 1. Create repeated indices to duplicate rows by their `units` count
repeated_rows = np.repeat(df.index, df['units'])

# 2. Generate date offsets (0, 1, ..., units-1) for each original row
date_offsets = np.concatenate([np.arange(unit_count) for unit_count in df['units']])

# 3. Build the expanded DataFrame
expanded_df = df.loc[repeated_rows].copy()
# Add offsets to the original date (use 'h' for hours, 'm' for minutes if needed)
expanded_df['transaction_dt'] += pd.to_timedelta(date_offsets, unit='D')

# Optional: Reset index for clean output
expanded_df = expanded_df.reset_index(drop=True)

Why This Is So Fast #

• np.repeat and np.concatenate are pure vectorized operations—they skip Python-level loops entirely, which is where most big-data slowdowns originate.
• Pandas’ .loc[repeated_rows] is optimized to handle large-scale row duplication efficiently, with minimal overhead.
• Memory usage stays lean compared to row-by-row list building, since NumPy arrays store data in contiguous, low-overhead blocks.

An Approach to Avoid for Large Data #

You might see suggestions to use apply + explode, like this:

# NOT recommended for massive datasets!
df['transaction_dt'] = df.apply(
    lambda x: pd.date_range(x['transaction_dt'], periods=x['units'], freq='D'),
    axis=1
)
expanded_df = df.explode('transaction_dt')

This works for small datasets, but the row-wise apply will be drastically slower on millions of rows compared to our vectorized method.

Quick Adjustments #

• Tweak the unit parameter in pd.to_timedelta if you need offsets in hours, minutes, or other intervals.
• Filter out rows where units = 0 first to avoid empty expansions.

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