基于Pandas实现同年内缺失月份值的向前填充及特殊场景处理

Alright, let's work through this Pandas data filling task. I'll show you how to integrate the three required rules into your existing code, with clear explanations for each part.

• When a month's cell value is NaN, fill it with the most recent non-NaN value from the previous months of the same year.
• If a year has only one valid value, fill all 12 months of that year with this single value.
• If all 12 months of a year are NaN, leave them unchanged as NaN.

data_list = []

for f in excel_files:
    df = pd.read_excel(f, header = None)

    new_col_coords = [[6,1], [7,1], [3,1], [1,0], [0,0]]
    new_columns = [x for x in [df.iloc[i, j] for i, j in new_col_coords]]
    header_row = 11
    df = df.T.set_index(header_row).T
    df = df.reset_index(drop = True).iloc[header_row:].reset_index(drop = True)
    df.columns.names = [None]
    df[['Region-INF', 'Series Name', 'Series ID']] = new_columns
    df = df[['Region-INF', 'Series Name', 'Series ID', 'View Description', 'Index Type', 'Year',
             'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']]

Region-INF  Series Name  Series ID     Jan  Feb  Mar  Apr  May   Jun
Pacific     All Items    CUUR0490SDD   2.9  2.8  NaN  NaN  2.52  2.04
Pacific     All Items    CUE07890SDF   NaN  2.64 NaN  2.44 2.59  3
Pacific     All Items    CUE073310SAF  2.1  2.4  NaN  2.21 3.45  NaN

Region-INF  Series Name  Series ID     Jan  Feb  Mar  Apr  May   Jun
Pacific     All Items    CUUR0490SDD   2.9  2.8  2.8  2.8  2.52  2.04
Pacific     All Items    CUE07890SDF   3    2.64 2.64 2.44 2.59  3
Pacific     All Items    CUE073310SAF  2.1  2.4  2.4  2.21 3.45  3.45  

Here's the updated code with all three filling rules implemented:

import pandas as pd

data_list = []

# Define month columns once for reusability
month_cols = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

def fill_month_values(row):
    """Custom function to apply the three filling rules to a single year's data"""
    month_vals = row[month_cols]
    
    # Rule 1: Forward fill missing values with the latest non-NaN previous month
    filled_ffill = month_vals.ffill()
    
    # Count valid non-NaN values in the year
    valid_count = month_vals.notna().sum()
    
    if valid_count == 1:
        # Rule 2: Fill all months with the only valid value if only one exists
        unique_val = month_vals.dropna().iloc[0]
        return pd.Series([unique_val] * 12, index=month_cols)
    elif valid_count == 0:
        # Rule 3: Leave all NaN if no valid values exist
        return month_vals
    else:
        # Return forward-filled result for all other cases
        return filled_ffill

for f in excel_files:
    df = pd.read_excel(f, header = None)

    new_col_coords = [[6,1], [7,1], [3,1], [1,0], [0,0]]
    new_columns = [x for x in [df.iloc[i, j] for i, j in new_col_coords]]
    header_row = 11
    df = df.T.set_index(header_row).T
    df = df.reset_index(drop = True).iloc[header_row:].reset_index(drop = True)
    df.columns.names = [None]
    df[['Region-INF', 'Series Name', 'Series ID']] = new_columns
    df = df[['Region-INF', 'Series Name', 'Series ID', 'View Description', 'Index Type', 'Year',
             *month_cols]]  # Use pre-defined month_cols for clarity
    
    # Apply the filling logic to each row
    df[month_cols] = df.apply(fill_month_values, axis=1)
    
    data_list.append(df)

• month_cols Definition: This centralizes the month column names, making the code easier to update if needed.
• fill_month_values Function:
  • ffill() handles rule 1 by propagating the last valid value forward to fill gaps.
  • We count valid values to trigger rules 2 and 3:
    • If only one valid value exists, we create a series filled with that value for all months.
    • If no valid values exist, we return the original NaN values.
  • For rows with multiple valid values, we stick with the forward-filled result.
• Row-wise Application: df.apply(..., axis=1) runs our custom function on each row, ensuring the rules are applied per year/series combination.

This code will produce exactly the expected output when tested with your sample data.

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