Using ffill() in a conditional np.where

Question

Here is a sample of my dataframe:

       id    Apples     Values        1Y 
0       6  21973805     455215  21889611
1       4  36403870    5661698       nan
2       5    277500      98846       nan  
3       1        19        nan       nan
4       2       120       6466    568912
5       1      3210      86565       nan

And I want to fill the nan of [1Y] column with the previous known value. But I want to fill it ONLY if the corresponding [Values] value is not nan.

Thus getting the output:

       id    Apples     Values        1Y 
0       6  21973805     455215  21889611
1       4  36403870    5661698  21889611
2       5    277500      98846  21889611       
3       1        19        nan       nan
4       2       120       6466    568912
5       1      3210      86565    568912

I tried the following code:

df['1Y'] = np.where((df['Values'] != np.nan) & (df['1Y'] == np.nan), df['1Y'].ffill(), df['1Y'])

But this changes nothing in the dataframe.

score 1 · Accepted Answer · answered Aug 05 '20 at 11:43

You should use pd.Series.isna to check for nan entries, if you do so:

df['1Y'] = np.where((~df['values'].isna()) & (df['1Y'].isna()), 
                    df['1Y'].ffill(), df['1Y'])

df['1Y']
0    21889611.0
1    21889611.0
2    21889611.0
3           NaN
4      568912.0
5      568912.0
Name: 1Y, dtype: float64

If fact np.nan==np.nan return False, check more here: why np.nan != np.nan.

Using ffill() in a conditional np.where

1 Answers1