如何用Pandas对含非均匀timestamp的分类DataFrame做指定窗口Backfill？

Hey there! Let's break down how to tackle this problem efficiently, especially since you've got a large dataset with 100+ columns and non-uniform timestamps. Here's a step-by-step plan:

1. Prep Your Timestamps & Sort the Data #

First, ensure your timestamp column is parsed as a datetime type (critical for calculating time differences) and sort the DataFrame by timestamp. Non-uniform timestamps won't play nice if your data is out of order:

import pandas as pd

# Convert timestamp column to datetime
df['timestamp'] = pd.to_datetime(df['timestamp'])
# Sort the DataFrame to ensure chronological order
df = df.sort_values('timestamp').reset_index(drop=True)

2. Identify Error Windows #

Next, pinpoint all the timestamps where the error flag appears, then calculate the 1-day window before each error (from error_timestamp - 1 day to error_timestamp):

# Extract timestamps where error is marked
error_timestamps = df[df['error'] == 'error']['timestamp']

# Create a DataFrame to represent each error's 1-day pre-error window
window_df = pd.DataFrame({
    'window_end': error_timestamps,
    'window_start': error_timestamps - pd.Timedelta(days=1),
    'window_id': range(len(error_timestamps))  # Unique ID for each window
})

3. Mark Rows That Fall Within Any Error Window #

Use merge_asof (perfect for non-uniform time matching) to map each row in your main DataFrame to the nearest error window, then check if the row's timestamp falls within the 1-day pre-error range. This is way more efficient than looping through rows:

# Merge main DataFrame with window info (matches nearest upcoming error window)
df = pd.merge_asof(
    df,
    window_df,
    left_on='timestamp',
    right_on='window_end',
    direction='backward'  # Finds the latest window_end <= current timestamp
)

# Flag rows that are within the 1-day pre-error window
df['in_pre_error_window'] = df['timestamp'] >= df['window_start']

4. Backfill NaNs Only Within the Target Windows #

Now, we'll only backfill NaNs in rows marked as in_pre_error_window=True. We'll group rows by their window_id to ensure backfilling stops at the start of each 1-day window, and leave NaNs outside these windows untouched:

# Create a copy to avoid modifying the original data
cleaned_df = df.copy()

# Define which columns need backfilling (exclude timestamp/error/window columns)
fill_columns = [col for col in cleaned_df.columns if col not in ['timestamp', 'error', 'window_start', 'window_end', 'window_id', 'in_pre_error_window']]

# Backfill within each pre-error window group
cleaned_df.loc[cleaned_df['in_pre_error_window'], fill_columns] = (
    cleaned_df[cleaned_df['in_pre_error_window']]
    .groupby('window_id')[fill_columns]
    .bfill()
)

Key Notes for Your Use Case #

• Handling Overlapping Windows: If two errors are less than 1 day apart, merge_asof will map rows to the nearest upcoming error. This means overlapping rows will use the later error's window for backfilling, which is likely what you want (prioritizing the most recent pre-error period).
• Efficiency: All operations here are vectorized (no slow apply loops), so they'll handle your 100+ columns without major performance hits.
• Categorical Data: Pandas supports backfilling for categorical columns natively, so you don't need special handling for those—just make sure your columns are properly tagged as category type if needed.

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