基于前后周同期均值的DataFrame缺失值插补技术咨询

Alright, let's work through this time series imputation challenge you've got. Your DataFrame tracks 15-minute interval usage data (96 entries per day) with some missing values, and you want to fill those gaps using the average of the same time slot from the previous and next week—expanding to earlier/later weeks if those are missing too, plus a special case for the first row. Here's a practical pandas-based solution tailored to your needs:

First, make sure your date and time stamp columns are combined into a single datetime index. This makes it way easier to align weekly time slots accurately:

import pandas as pd
import numpy as np

# Combine date + time stamp into a datetime column, then set as index
df['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time stamp'])
df = df.set_index('datetime').sort_index()  # Ensure data is in chronological order

Pro tip: Double-check that your index is sorted—if it's out of order, the weekly shifts we'll use later will be wrong.

We'll create shifted versions of your usage column to pull values from 1, 2, 3, and 4 weeks before/after each time slot. For each missing value, we'll collect all valid non-null values from these shifted columns and compute their mean. For the first row (which has no prior weeks), we'll only look at future weeks automatically.

Here's the function:

def impute_weekly_usage(df):
    # Calculate number of rows per week (96 entries/day * 7 days = 672 rows)
    rows_per_week = 96 * 7

    # Create temporary columns for shifted weekly values (1-4 weeks back/forward)
    for week_offset in range(1, 5):
        # Shift backward = get values from X weeks prior
        df[f'usage_past_{week_offset}w'] = df['usage'].shift(week_offset * rows_per_week)
        # Shift forward = get values from X weeks ahead
        df[f'usage_future_{week_offset}w'] = df['usage'].shift(-week_offset * rows_per_week)

    # Define a helper to compute the mean of valid weekly values for each row
    def fill_missing(row):
        # If the value isn't missing, return it as-is
        if not pd.isna(row['usage']):
            return row['usage']
        
        # Collect all non-null shifted values
        weekly_values = []
        for week_offset in range(1, 5):
            weekly_values.append(row[f'usage_past_{week_offset}w'])
            weekly_values.append(row[f'usage_future_{week_offset}w'])
        
        # Filter out NaNs and calculate mean if we have valid data
        valid_values = [val for val in weekly_values if not pd.isna(val)]
        if valid_values:
            return np.mean(valid_values)
        
        # Fallback: if no weekly data exists, use forward fill (adjust this if needed)
        return df['usage'].iloc[:df.index.get_loc(row.name)].ffill().iloc[-1]

    # Apply the helper to create the imputed usage column
    df['usage_imputed'] = df.apply(fill_missing, axis=1)

    # Clean up temporary columns
    df = df.drop([col for col in df.columns if 'usage_past' in col or 'usage_future' in col], axis=1)

    return df

Just pass your prepped DataFrame to the function:

df_imputed = impute_weekly_usage(df)

• Adjust the number of weeks checked: Change range(1,5) to range(1,8) if you want to check up to 7 weeks back/forward instead of 4.
• Fallback behavior: The final fallback uses forward fill if no weekly values are available. You could replace this with linear interpolation (df['usage'].interpolate(method='linear')) or the global mean of usage if that makes more sense for your data.
• First row handling: Since the first row has no prior weeks, the usage_past_Xw columns will be NaN, so the function automatically only uses future week values to compute the mean.

If you want to test this with sample data, here's a quick setup:

# Create 2 weeks of 15-minute interval data
dates = pd.date_range(start='2023-01-01', end='2023-01-14', freq='15min')
usage = [i % 100 for i in range(len(dates))]  # Dummy cyclic usage data

# Add missing values (first row + a random mid-week value)
usage[0] = np.nan
usage[96 * 3 + 12] = np.nan  # Missing value on 2023-01-04 03:00:00

# Build sample DataFrame
sample_df = pd.DataFrame({'datetime': dates, 'usage': usage}).set_index('datetime')

# Run imputation
imputed_sample = impute_weekly_usage(sample_df)

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