求助：基于不同时间窗口（1天/2天）为各列带标签回填缺失数据

Got it, let's work through this problem together. The tricky part here is that pandas' built-in fillna() or bfill() don't support combining time-based windows with per-column custom backfill rules—especially when dealing with non-uniform timestamps. Here's a solid, practical solution that gets exactly what you need:

Step 1: Setup Sample Data (Matching Your Non-Uniform Timestamp Scenario) #

First, let's replicate a dataset similar to yours with non-uniform timestamps and missing values:

import pandas as pd
import numpy as np

# Generate non-uniform timestamps
timestamps = pd.to_datetime([
    '2023-01-01 00:00', 
    '2023-01-01 08:00', 
    '2023-01-02 12:00', 
    '2023-01-04 09:00', 
    '2023-01-05 14:00'
])

# Create sample dataframe with missing values
df = pd.DataFrame({
    'timestamp': timestamps,
    'col1': [10, np.nan, 30, np.nan, 50],  # Needs 1-day backfill window
    'col2': [np.nan, 20, np.nan, 40, np.nan]  # Needs 2-day backfill window
})

Step 2: Define Per-Column Time Windows #

Map each column to its desired backfill time window using pd.Timedelta:

# Define custom time windows for each column
column_time_windows = {
    'col1': pd.Timedelta(days=1),
    'col2': pd.Timedelta(days=2)
}

Step 3: Time-Windowed Backfill with merge_asof #

We'll use pandas' merge_asof function—it's designed for non-uniform time series and lets us match values within a strict time window, which is perfect for your requirement:

# Sort dataframe by timestamp (required for merge_asof) and set index
df_sorted = df.sort_values('timestamp').set_index('timestamp')
filled_df = df_sorted.copy()

for col, window in column_time_windows.items():
    # Extract rows where the column has no missing values (our "source" of valid data)
    valid_values = df_sorted[[col]].dropna().reset_index()
    # Convert our target dataframe back to a flat structure for merging
    temp_df = df_sorted[[col]].reset_index()
    
    # Use merge_asof to find the NEXT valid value within the time window
    merged = pd.merge_asof(
        temp_df,
        valid_values,
        on='timestamp',
        direction='forward',  # Look for the first value AFTER the missing point
        tolerance=window,     # Only match values within the defined time window
        suffixes=('', '_filled')
    )
    
    # Replace missing values with the matched values (keep original if no match in window)
    filled_df[col] = merged[f'{col}_filled'].fillna(filled_df[col])

# Reset index to get timestamp back as a column (optional)
filled_df = filled_df.reset_index()

How This Works #

• merge_asof with direction='forward' finds the first valid value after each missing point (exactly what backfill does).
• The tolerance parameter ensures we only use values within your specified time window (1 day for col1, 2 days for col2).
• Any missing value that has no valid data within its time window stays as NaN—no unwanted over-filling.

Key Notes #

• Always sort your dataframe by timestamp first—merge_asof requires sorted input to work correctly.
• You can adjust the time window to any granularity (e.g., pd.Timedelta(hours=6) for 6-hour windows) depending on your needs.
• This method handles non-uniform timestamps seamlessly, which is a huge advantage over row-based limit parameters in bfill().

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