在循环中合并Pandas DataFrame速度缓慢，如何优化性能？

Hey there! I totally get the frustration of slow pandas code when dealing with big datasets—your current approach using iterrows() and per-row processing is hitting exactly the bottleneck that pandas was designed to avoid. Let's fix this with vectorized operations that leverage pandas' optimized C-backed internals.

Why Your Current Code Is Slow #

• iterrows() iterates over rows one by one in pure Python, which is extremely inefficient for large DataFrames.
• Creating temporary DataFrames (df_tmp, df1) and concatenating them repeatedly (pd.concat(df_result)) causes unnecessary memory overhead and repeated processing.

Solution 1: Vectorized Merge + Boolean Filter (Fastest & Simplest) #

Instead of looping through each row, we can first merge the two DataFrames on the matching ID, then filter rows that meet your time range condition. This is all handled in bulk by pandas' optimized functions.

Here's the revised code:

def segmentMatch(self, df, df_program):
    # Step 1: Merge the DataFrames on matching ID columns
    # df's 'id' matches df_program's 'iD'
    merged_df = pd.merge(df, df_program, left_on='id', right_on='iD', how='left')
    
    # Step 2: Filter rows where the time ranges overlap
    time_match_mask = (merged_df['end_time'] >= merged_df['START_TIME']) & \
                      (merged_df['start_time'] <= merged_df['END_TIME'])
    
    # Step 3: Keep only matching rows and reset index
    result = merged_df[time_match_mask].reset_index(drop=True)
    
    return result

Solution 2: Add Indexes for Even Faster Merges #

If iD in df_program is a column you frequently match on, adding an index to it will speed up the merge operation significantly (since pandas can look up indexed values in O(1) time instead of scanning the whole column):

def segmentMatch(self, df, df_program):
    # Set index on df_program's iD column to optimize merge
    df_program_indexed = df_program.set_index('iD')
    
    # Merge using the index for faster lookups
    merged_df = pd.merge(df, df_program_indexed, left_on='id', right_index=True, how='left')
    
    # Apply the time range filter
    time_match_mask = (merged_df['end_time'] >= merged_df['START_TIME']) & \
                      (merged_df['start_time'] <= merged_df['END_TIME'])
    
    result = merged_df[time_match_mask].reset_index(drop=True)
    
    return result

Solution 3: Use query() for Readability #

If you prefer more readable code, you can use pandas' query() method to filter the time conditions after merging:

def segmentMatch(self, df, df_program):
    merged_df = pd.merge(df, df_program, left_on='id', right_on='iD', how='left')
    # Use query() for cleaner condition syntax
    result = merged_df.query('end_time >= START_TIME and start_time <= END_TIME').reset_index(drop=True)
    return result

How to Verify Correctness #

To make sure the optimized code produces the same result as your original function, test with a small subset of your data:

# Take a small sample of your data
small_df = df.sample(100)
small_program = df_program[df_program['iD'].isin(small_df['id'])]

# Compare results
original_result = your_instance.segmentMatch(small_df, small_program)
optimized_result = optimized_segmentMatch(small_df, small_program)

# Check if they're identical
print(pd.testing.assert_frame_equal(original_result, optimized_result))

This should return None if the results match, confirming the logic is correct.

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