Fill na by applying condition on another column

Question

There is a df which contains two columns. First column has monthly values but the second one only contains quarterly values. I want to fill the NA values of second column by the same percentage change on the first column. For example, the original df looks like this:

            ColA   ColB 
2019-12-31   100    5
2020-01-31   200    NA
2020-02-28   300    NA
2020-03-31   200    20
2020-04-30   300    NA

And I want to calculate the percentage change of colA and then fill the colB as per the percentage change. The result should look like:

            ColA   ColB 
2019-12-31   100    5
2020-01-31   200    10
2020-02-28   300    15
2020-03-31   200    20
2020-04-30   300    30

I can calculate the percentage changes of colA by pct_change() but not getting the point how to apply only on the na values of colB. Is there any solution for it please?

Answer 1

You could simply compute a ratio, ffill it, and use combine_first to update missing values:

ratio = (df['ColB'] / df['ColA']).ffill()
df['ColB'] = df['ColB'].combine_first(df['ColA'] * ratio)

It is enough to get the expected result:

            ColA  ColB
2019-12-31   100   5.0
2020-01-31   200  10.0
2020-02-28   300  15.0
2020-03-31   200  20.0
2020-04-30   300  30.0