如何优化两个DataFrame间时间匹配算法的执行效率？

Hey there! Let's fix that slow loop you're using for matching time-series data. Traversing every row and filtering each time is a total performance killer—especially with large datasets. Here's how to optimize this with vectorized pandas operations, which are way faster and cleaner.

Step 1: Prep Your Data First #

First, make sure both DataFrames have datetime-indexed time columns—this is non-negotiable for efficient time-series work. Let's start by converting and setting the indices:

import pandas as pd

# Load and clean short granularity (4h) data
df_4h = pd.DataFrame({
    'Time': ['1/1/01 00:00', '1/1/01 06:00', '1/1/01 12:00', '1/1/01 18:00',
             '2/1/01 00:00', '2/1/01 06:00', '2/1/01 12:00', '2/1/01 18:00',
             '3/1/01 00:00', '3/1/01 06:00', '3/1/01 12:00', '3/1/01 18:00'],
    'Data_4h': [1.1, 1.2, 1.3, 1.1, 1.1, 1.2, 1.3, 1.1, 1.1, 1.2, 1.3, 1.1]
})
df_4h['Time'] = pd.to_datetime(df_4h['Time'], format='%d/%m/%y %H:%M')
df_4h = df_4h.set_index('Time')

# Load and clean long granularity (1d) data
df_1d = pd.DataFrame({
    'Time': ['1/1/01 00:00', '2/1/01 00:00', '3/1/01 00:00'],
    'Data_1d': [1.1, 1.6, 1.0]
})
df_1d['Time'] = pd.to_datetime(df_1d['Time'], format='%d/%m/%y %H:%M')
df_1d = df_1d.set_index('Time')

Option 1: Exact Date Matching (Best for Daily Long Granularity) #

If your long-granularity data is strictly daily (like your example, with entries at midnight), we can map dates directly. The trick is to shift the long-granularity dates forward by one day, so they align with the short-granularity dates that need to reference them:

# Shift long-granularity dates forward by 1 day to match the next day's short data
prev_day_series = df_1d['Data_1d'].rename(lambda dt: dt + pd.Timedelta(days=1))

# Extract the date part from each short-granularity timestamp and map to the shifted series
df_4h['Data_1d'] = df_4h.index.floor('D').map(prev_day_series)

This gives you exactly the output you want, with NaN for days where no prior long-granularity data exists.

Option 2: Flexible Time Matching (For Irregular Long Granularity) #

If your long-granularity data has gaps or isn't strictly at midnight, use pd.merge_asof—this finds the nearest prior long-granularity entry for each short-granularity timestamp (after subtracting a day):

# Create a column for the timestamp we want to match (current time minus 1 day)
df_4h['target_time'] = df_4h.index - pd.Timedelta(days=1)

# Sort both datasets (required for merge_asof)
df_4h_sorted = df_4h.sort_values('target_time')
df_1d_sorted = df_1d.sort_index()

# Merge to find the nearest long-granularity entry <= target_time
result = pd.merge_asof(
    df_4h_sorted,
    df_1d_sorted,
    left_on='target_time',
    right_index=True,
    direction='backward'  # Find the latest entry <= target_time
)

# Clean up and restore original order
result = result.sort_index().drop('target_time', axis=1)

Why This Is Way Faster #

Your original loop uses O(n*m) time complexity (n rows in short data, m rows in long data) because it filters the entire long dataset for every row. Vectorized operations like map or merge_asof use optimized pandas/C-based code, with time complexity closer to O(n log n) (from sorting), which is exponentially faster for large datasets.

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