如何用Pandas填充DataFrame中2019年的0与NaN值：两种场景

First, let's break down why your original code didn't work:

• Slicing df['price'][df['year']==2019] often creates a copy of the data instead of modifying the original DataFrame directly, so inplace=True doesn't affect your source data.
• The fillna call uses the entire 2018 price series, which tries to fill values by index rather than matching the corresponding quantity values—so it can't correctly map prices to the right rows.

Let's fix this with targeted mapping based on quantity. First, let's set up the sample data correctly:

import pandas as pd
import numpy as np

# Sample DataFrame
df = pd.DataFrame({
    'quantity': [1,2,3,4,5,6,7,8,9]*3,
    'year': [2017]*9 + [2018]*9 + [2019]*9,
    'price': [1,2,3,4,5,6,7,8,9, 2,4,6,8,10,12,14,16,18, np.NaN,np.NaN,0,0,np.NaN,0,np.NaN,0,np.NaN]
})

Task 1: Fill 2019 missing/0 prices with 2018 corresponding quantity prices #

First, create a mapping of quantity to its 2018 price, then use it to fill the gaps:

# Create a Series where index = quantity, value = 2018 price
price_2018_map = df[df['year'] == 2018].set_index('quantity')['price']

# Define mask for 2019 rows with price 0 or NaN
mask_2019_missing = (df['year'] == 2019) & (df['price'].isna() | (df['price'] == 0))

# Make a copy of the original DataFrame to avoid modifying it
df_scenario1 = df.copy()

# Fill the missing values using the 2018 quantity-to-price map
df_scenario1.loc[mask_2019_missing, 'price'] = df_scenario1.loc[mask_2019_missing, 'quantity'].map(price_2018_map)

print("Scenario 1 Output (2019 rows):")
print(df_scenario1[df_scenario1['year'] == 2019])

This replaces all 2019 rows with 0/NaN price with the exact price from 2018 for the same quantity.

Task 2: Fill 2019 missing/0 prices with mean of 2017 and 2018 corresponding quantity prices #

First, calculate the average price per quantity across 2017 and 2018, then use that to fill the gaps:

# Group by quantity, compute mean price from 2017 and 2018
price_mean_map = df[df['year'].isin([2017, 2018])].groupby('quantity')['price'].mean()

# Make another copy of the original DataFrame
df_scenario2 = df.copy()

# Fill the missing values using the mean quantity-to-price map
df_scenario2.loc[mask_2019_missing, 'price'] = df_scenario2.loc[mask_2019_missing, 'quantity'].map(price_mean_map)

print("\nScenario 2 Output (2019 rows):")
print(df_scenario2[df_scenario2['year'] == 2019])

This replaces the missing/0 2019 prices with the average of the 2017 and 2018 prices for each matching quantity.

Key Takeaways: #

• Using .copy() ensures we don't overwrite the original data for either scenario.
• The mask targets only the relevant 2019 rows, so we don't modify other data accidentally.
• .map() with a quantity-indexed Series guarantees we match the correct price to each row's quantity, fixing the alignment issue from your original code.

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