如何在Python中用NA前三个值的均值填充缺失值（附数据集）

Got it, let's walk through how to solve this problem—filling NA values in your dataset using the mean of the last three valid values in the NET_SALES column. We'll use pandas, the standard library for data manipulation in Python.

Step 1: Set Up the Data #

First, let's convert your dataset into a pandas DataFrame. We'll use numpy.nan to represent the NA values:

import pandas as pd
import numpy as np

# Create the dataset as a DataFrame
data = {
    'RECEIPT_MONTH_YEAR': ['2014-01-01', '2014-02-01', '2014-03-01', '2014-04-01', '2014-05-01', '2014-06-01', '2014-07-01', '2014-08-01', '2014-09-01', '2014-10-01', '2014-10-01'],
    'NET_SALES': [818817.20, 362377.20, 374644.60, np.nan, np.nan, np.nan, np.nan, 46382.50, 55933.70, 292303.40, 382928.60]
}
df = pd.DataFrame(data)

Step 2: Fill NA Values with Mean of Last Three Valid Values #

We'll iterate through the NET_SALES column, keep track of all valid values we've seen so far, and for each NA, calculate the mean of the most recent three valid values:

# Initialize lists to track valid values and store filled values
valid_sales = []
filled_sales = []

for value in df['NET_SALES']:
    if not pd.isna(value):
        # Add valid value to our tracking list and keep it in the filled list
        valid_sales.append(value)
        filled_sales.append(value)
    else:
        # Calculate mean of last three valid values (if available)
        if len(valid_sales) >= 3:
            mean_val = sum(valid_sales[-3:]) / 3
            filled_sales.append(mean_val)
        else:
            # Handle cases where there aren't 3 prior valid values (optional)
            filled_sales.append(np.nan)

# Add the filled column to the DataFrame
df['NET_SALES_FILLED'] = filled_sales

Step 3: View the Result #

If you print the DataFrame, you'll see the NA values filled with the mean of the first three valid entries (818817.20, 362377.20, 374644.60), which equals 518613.0:

print(df)

Output:

RECEIPT_MONTH_YEAR  NET_SALES  NET_SALES_FILLED
0          2014-01-01   818817.2        818817.200
1          2014-02-01   362377.2        362377.200
2          2014-03-01   374644.6        374644.600
3          2014-04-01        NaN        518613.000
4          2014-05-01        NaN        518613.000
5          2014-06-01        NaN        518613.000
6          2014-07-01        NaN        518613.000
7          2014-08-01    46382.5         46382.500
8          2014-09-01    55933.7         55933.700
9          2014-10-01   292303.4        292303.400
10         2014-10-01   382928.6        382928.600

Key Notes #

• This approach works for consecutive NA values—all NAs will use the most recent three valid values available before them.
• If your dataset had NAs at the start (with fewer than three prior valid values), the code leaves them as NA, but you can adjust this logic (e.g., use the mean of available valid values) if needed.

内容的提问来源于stack exchange，提问作者Naveen Raj B