多维度时序数据中基于邻域mean值的NaN值简洁填充最优方案咨询

Hey there! Let's tackle this missing value problem for your country-model-year time-series data. Your initial idea of using neighboring values makes total sense, and we can adapt it to handle leading, trailing, and consecutive NaNs without overcomplicating the code. Here are my go-to, low-effort solutions:

1. Rolling Window Mean + Boundary Filling (Best for Static Trends) #

This builds on your original "3 neighbors" idea but uses pandas' built-in rolling functions to automatically handle edge cases. We'll use a window of 7 (3 before, 3 after, plus the current position) with flexible minimum valid values, then fill any remaining edge NaNs with forward/backward fills.

Code Example: #

import pandas as pd

# First, sort your data to ensure chronological order per country-model pair
df = df.sort_values(['country', 'model', 'year'])

# Group by country and model to calculate means within each independent group
df['filled_value'] = df.groupby(['country', 'model'])['target_column'].transform(
    lambda x: x.rolling(
        window=7,  # Covers 3 prior values + current + 3 next values
        center=True,  # Centers the window on the missing value
        min_periods=1  # Calculates mean even if only 1 valid value exists in the window
    ).mean()
    .bfill()  # Fills remaining trailing NaNs with the last valid window mean
    .ffill()  # Fills remaining leading NaNs with the first valid window mean
)

Why this works: #

• Leading NaNs: The first few missing values will use the mean of available subsequent values, then ffill propagates that valid data forward.
• Trailing NaNs: bfill takes the last valid window mean and fills the end gaps seamlessly.
• Consecutive NaNs: The rolling window pulls in valid values from either side, and the min_periods flag ensures we never get stuck on empty windows.

2. Exponential Weighted Moving Average (EWMA) + Boundary Filling (Best for Trending Data) #

If your time-series has a clear upward/downward trend, EWMA gives more weight to recent values (more realistic for trends) while keeping the code just as simple.

Code Example: #

df['filled_value'] = df.groupby(['country', 'model'])['target_column'].transform(
    lambda x: x.ewm(
        span=7,  # Roughly matches your 3-neighbor window in terms of weight distribution
        adjust=False  # Uses recursive calculation for better trend tracking
    ).mean()
    .bfill()
    .ffill()
)

Why this works: #

• It naturally smooths trends without manual logic, and the boundary fills handle edge cases exactly like the rolling method.

Quick Notes to Avoid Mistakes: #

• Always sort your data first: Window functions rely on sequential chronological order—make sure years are ordered correctly for each country-model pair.
• Group strictly by country and model: Never calculate means across different groups; this ensures you only use relevant neighboring data for fills.
• Test edge cases manually: Spot-check rows with leading NaNs, trailing NaNs, and consecutive gaps to confirm the fill behaves as expected.

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