fillna by avoiding row wise operation in pandas

Question

I have a data frame in which there is a column containing several NaN values. The dataframe looks like this:

            col_1   col_2
2022-10-31  99.094  102.498
2022-11-30  99.001  101.880
2022-12-31     NaN  108.498
2023-01-31     NaN  100.500

I want to fill those NaN based on the simple calculation below: desired_val = (previous value in col_1 * current value in col_2) / previous value in col_2

which means,

df.loc['2022-12-31', 'col_1'] should be = (99.001 * 108.498) / 101.880 = 105.432

and df.loc['2023-01-31', 'col_1'] should be = (105.432 * 100.500) / 108.498 = 97.660

I found solution by using row by row operation but it is slow when the dataset is big. I tried column wise operation by using this:

df['col_1'] = df['col_1'].fillna(
        (df[col_1].shift(1) * df[col_2])
        / df[col_2].shift(1)
       )

But it does work only for one row and then it does not go further. Is there any column wise pandas solution for that?

Answer 1

You can think of your operation and see that you multiply by x in one row and divide by x in the next row.

Thus you can simplify the result to:

col1_value = (last_valid_col1_value * current_col2_value)
             / col2_value_at_last_valid_col1_position

Which can be translated as:

# is the row a NA?
m1 = df['col_1'].isna()
# is the next row a NA?
m2 = df['col_1'].shift(-1).isna()

df.loc[m1, 'col_1'] = (df['col_1'].div(df['col_2'])
                       .where(m2 & ~m1).ffill()
                       .mul(df['col_2'])[m1]
                      )

Output:

                 col_1    col_2
2022-10-31   99.094000  102.498
2022-11-30   99.001000  101.880
2022-12-31  105.431984  108.498
2023-01-31   97.659997  100.500

Intermediates:

             col_1    col_2     m1     m2  m2&~m1  ffilled(col1/col2)      result
2022-10-31  99.094  102.498  False  False   False                 NaN         NaN
2022-11-30  99.001  101.880  False   True    True            0.971741         NaN
2022-12-31     NaN  108.498   True   True   False            0.971741  105.431984
2023-01-31     NaN  100.500   True   True   False            0.971741   97.659997

Answer 2

Another possible solution, which uses a mathematical formula (under the assumption that col_1 is only NaN after the first NaN):

col1_NaN = last_not_NaN_of_col1 * 
           cumprod(col2_after_last_not_NaN_col1/col2.shift)

---

df.assign(col_1 = df['col_1'].combine_first(
    df['col_1'][df['col_1'].last_valid_index()] * 
    df['col_2'].div(df['col_2'].shift())[df['col_1'].isna()].cumprod()))

---

Output:

                 col_1    col_2
2022-10-31   99.094000  102.498
2022-11-30   99.001000  101.880
2022-12-31  105.431984  108.498
2023-01-31   97.659997  100.500