Fill Pandas dataframe rows, whose value is a 0 or NaN, with a formula that have to be calculated on specific rows of another column

I have a dateframe where values in the "price" column are different depending on both the values in the "quantity" and "year" columns. For example for a quantity equal to 2 I have a price equal to 2 in the 2017 and equal to 4 in the 2018. I would like to fill the rows for 2019, that have a 0 and NaN value, with values from 2018.

df = pd.DataFrame({
    'quantity': pd.Series([1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9]),
    'year': pd.Series([2017,2017,2017,2017,2017,2017,2017,2017,2017,2018,2018,2018,2018,2018,2018,2018,2018,2018,2019,2019,2019,2019,2019,2019,2019,2019,2019,]),
    'price': pd.Series([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])
})

And what if, instead of taking values from 2018, I should calculate a mean between 2017 and 2018?

I tried to readapt this question applying it to the first case (to apply data from 2018), but it doesn't work:

df['price'][df['year']==2019].fillna(df['price'][df['year'] == 2018], inplace = True)

Could you please help me?

The expected output should be a dataframe like the followings:

Df with values from 2018

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

Df with values that are a mean between 2017 and 2018

df = pd.DataFrame({
    'quantity': pd.Series([1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9]),
    'year': pd.Series([2017,2017,2017,2017,2017,2017,2017,2017,2017,2018,2018,2018,2018,2018,2018,2018,2018,2018,2019,2019,2019,2019,2019,2019,2019,2019,2019,]),
    'price': pd.Series([1,2,3,4,5,6,7,8,9,2,4,6,8,10,12,14,16,18,1.5,3,4.5,6,7.5,9,10.5,12,13.5])
})

Solution

Here's one way filling with the mean of 2017 and 2018. Start by grouping the previous year's data by the quantity and aggregating with the mean:

m = df[df.year.isin([2017, 2018])].groupby('quantity').price.mean()

Use set_index to set the quantity column as index, replace 0s by NaNs and use fillna which also accepts dictionaries to map the values according to the index:

ix = df[df.year.eq(2019)].index
df.loc[ix, 'price'] = (df.loc[ix].set_index('quantity').price
                        .replace(0, np.nan).fillna(m).values)

    quantity  year  price
0          1  2017    1.0
1          2  2017    2.0
2          3  2017    3.0
3          4  2017    4.0
4          5  2017    5.0
5          6  2017    6.0
6          7  2017    7.0
7          8  2017    8.0
8          9  2017    9.0
9          1  2018    2.0
10         2  2018    4.0
11         3  2018    6.0
12         4  2018    8.0
13         5  2018   10.0
14         6  2018   12.0
15         7  2018   14.0
16         8  2018   16.0
17         9  2018   18.0
18         1  2019    1.5
19         2  2019    3.0
20         3  2019    4.5
21         4  2019    6.0
22         5  2019    7.5
23         6  2019    9.0
24         7  2019   10.5
25         8  2019   12.0
26         9  2019   13.5