python - Fill columns of nan values with previous value multiplied by constant

Question

I have a df with some columns ending in multiple nan values. I'd like to use fillna(method='ffill') or something similar to multiply each nan col value with the previous value * some constant from another df. However, just using the following doesn't work. It would need to calculate each col value before moving onto the next, is this the problem?

df_new = df.fillna(method='ffill') * constant 

question from:https://stackoverflow.com/questions/65906808/fill-columns-of-nan-values-with-previous-value-multiplied-by-constant

Answer 1

Interesting question! This is not a vectorized solution but it will work on a column by column basis.

First setting up the data to give the tests - setting the index to date so we are not reliant on the index number to help

A = [np.nan, np.nan, 5.5, 5.7, 5.9, 6.1, 6.0, 5.9, np.nan, np.nan, np.nan,15.0]

df = pd.DataFrame({'A': A}, index=pd.date_range(start='2010', periods=len(A), freq="QS"))

gives

            A
2010-01-01  NaN
2010-04-01  NaN
2010-07-01  5.5
2010-10-01  5.7
2011-01-01  5.9
2011-04-01  6.1
2011-07-01  6.0
2011-10-01  5.9
2012-01-01  NaN
2012-04-01  NaN
2012-07-01  NaN
2012-10-01  15.0

and then

for id in df[df.A.isnull() == True].index:
    df.loc[id, 'A'] = 1.025 * df.A.shift().loc[id] 

gives

            A
2010-01-01  NaN
2010-04-01  NaN
2010-07-01  5.500000
2010-10-01  5.700000
2011-01-01  5.900000
2011-04-01  6.100000
2011-07-01  6.000000
2011-10-01  5.900000
2012-01-01  6.047500
2012-04-01  6.198687
2012-07-01  6.353655
2012-10-01  15.000000

now you need to loop through the columns that you want to fill forward