How to apply numpy.where() or fillna() row by row to return elements from newly-filled rows

Question

I am trying to fill NaN rows based on previous rows AND different columns. I have the following code:

import pandas as pd
import numpy as np

data = {'value':[55,58,60,62,64,np.nan,np.nan],
        'growth_rate': [np.nan,1.0545,1.034483,1.033333,1.032258,1.02,1.03]}

df = pd.DataFrame(data)  

print(df)

Which gives the following dataframe:

   value  growth_rate
0   55.0          NaN
1   58.0     1.054500
2   60.0     1.034483
3   62.0     1.033333
4   64.0     1.032258
5    NaN     1.020000
6    NaN     1.030000

I do have the growth rates to fill the gaps in rows 5 and 6. I've tried the following code:

df['value'] = np.where(df['value'].isnull(), df['value'].shift(1) * df['growth_rate'], df['value'])
print(df)

Which gives me the following output:

   value  growth_rate
0  55.00          NaN
1  58.00     1.054500
2  60.00     1.034483
3  62.00     1.033333
4  64.00     1.032258
5  65.28     1.020000
6    NaN     1.030000

As you can see, only row 5 was filled using np.where(). I have to rerun this line to get the expected result:

     value  growth_rate
0  55.0000          NaN
1  58.0000     1.054500
2  60.0000     1.034483
3  62.0000     1.033333
4  64.0000     1.032258
5  65.2800     1.020000
6  67.2384     1.030000

However, this approach is not efficient. There must be a way to make this operation in one line! I've tried with fillna() as well, but I get the same results:

df['value'] = df['value'].fillna(df['value'].shift(1) * df['growth_rate'])
print(df)

   value  growth_rate
0  55.00          NaN
1  58.00     1.054500
2  60.00     1.034483
3  62.00     1.033333
4  64.00     1.032258
5  65.28     1.020000
6    NaN     1.030000

I wish I could find some sort of ffill() or np.where() that fills gaps based newly-filled rows and another column (growth_rate) at the same time, all in one step.

user_15497888 · Answer

Assuming all missing values are in a single group, we can ffill the missing values in value to bring down the last valid value, then take the cumulative product (cumprod) of growth_rate where value isna:

m = df['value'].isna()
df.loc[m, 'value'] = df['value'].ffill() * df.loc[m, 'growth_rate'].cumprod()

df:

     value  growth_rate
0  55.0000          NaN
1  58.0000     1.054500
2  60.0000     1.034483
3  62.0000     1.033333
4  64.0000     1.032258
5  65.2800     1.020000
6  67.2384     1.030000

Setup and imports:

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'value': [55.0, 58.0, 60.0, 62.0, 64.0, np.nan, np.nan],
    'growth_rate': [np.nan, 1.0545, 1.034483, 1.033333, 1.032258, 1.02, 1.03]
})

Assuming we want separate interspersed nan groups to be calculated independently we can create groups with cumsum and use groupby cumprod instead:

m = df['value'].isna()
df.loc[m, 'value'] = (
        df['value'].ffill() *
        df.loc[m, 'growth_rate'].groupby((~m).cumsum()).cumprod()
)

df:

       value  growth_rate
0  55.000000          NaN
1  58.000000     1.054500
2  60.000014     1.034483  # (group 1) cumprod 
3  62.000000     1.033333
4  64.000000     1.032258
5  65.280000     1.020000  # (group 2) values same as without groupby
6  67.238400     1.030000  # since these are in a group together

Modified setup and imports:

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'value': [55.0, 58.0, np.nan, 62.0, 64.0, np.nan, np.nan],
    'growth_rate': [np.nan, 1.0545, 1.034483, 1.033333, 1.032258, 1.02, 1.03]
})

modified df:

   value  growth_rate
0   55.0          NaN
1   58.0     1.054500
2    NaN     1.034483
3   62.0     1.033333
4   64.0     1.032258
5    NaN     1.020000
6    NaN     1.030000

How to apply numpy.where() or fillna() row by row to return elements from newly-filled rows

1 Answers