循环生成过滤Pandas DataFrame性能不佳，求优化方案

Your core issue here is that looping through every single party_id and querying the DataFrames repeatedly is extremely inefficient—Pandas wasn't designed for row-by-row operations, especially with large datasets like 11M rows. Let's fix this with vectorized operations that leverage Pandas' optimized internals.

Step 1: Preprocess & Index for Speed #

First, let's make sure our data is properly typed and indexed to speed up merges and filters:

import pandas as pd
from datetime import timedelta

# Convert date columns to datetime (do this once, not in the loop!)
app['date_st'] = pd.to_datetime(app['date_st'])
facts['fact_date'] = pd.to_datetime(facts['fact_date'])

# Add computed columns to app: the earliest allowed fact date for each party
window_days = 180
app['fact_start_date'] = app['date_st'] - timedelta(days=window_days)

# Add indexes to speed up merges and filters
app = app.set_index('client_id')
facts = facts.set_index('client_id')

Step 2: Vectorized Merge & Filter (No Loops!) #

Instead of looping, we'll merge the two DataFrames on client_id first, then filter the rows that meet our time window criteria. This is way faster because Pandas handles the join in optimized C-level code.

# Merge app and facts on client_id (this aligns all facts with their client's parties)
merged = app.merge(facts, left_index=True, right_index=True, how='inner')

# Apply the time window filters
filtered = merged[
    (merged['fact_date'] < merged['date_st']) &
    (merged['fact_date'] >= merged['fact_start_date'])
]

# Keep only the columns we need, and reset index if needed
new_facts = filtered[['party_id', 'client_id', 'fact_date', 'fact_sum']].reset_index(drop=True)

Why This is Way Faster #

• No row-by-row loops: The merge and filter operations are vectorized, meaning they operate on entire columns at once instead of individual rows.
• Indexing: By indexing on client_id, we speed up the merge operation significantly (Pandas uses hash tables for indexed joins).
• Single pass over data: We only process the facts table once, instead of 50k times.

Optional: Memory Optimization (If Needed) #

If even the merged DataFrame is too large for memory, you can process the data in chunks by client_id:

new_facts_list = []

# Iterate over unique client IDs (fewer iterations than party IDs!)
for clid in app.index.unique():
    # Get all parties for this client
    client_app = app.loc[clid]
    # Get all facts for this client
    client_facts = facts.loc[clid]
    # Merge and filter for this client
    merged = client_app.merge(client_facts, on='client_id', how='inner')
    filtered = merged[
        (merged['fact_date'] < merged['date_st']) &
        (merged['fact_date'] >= merged['fact_start_date'])
    ]
    new_facts_list.append(filtered)

# Combine all chunks
new_facts = pd.concat(new_facts_list, ignore_index=True)

This reduces the peak memory usage since we only process one client's data at a time.

Verify the Result #

You can check if the output matches your expected result by comparing a sample of rows:

print(new_facts[new_facts['party_id'] == 'pid1'])

This should match the rows you listed for pid1 in your expected output.

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