Pandas - Fillna based on last non-blank value and next column-CodePudding

I have the following pandas dataframe:

       A      B    C
0  100.0  110.0  100
1   90.0  120.0  110
2    NaN  105.0  105
3    NaN  100.0  103
4    NaN    NaN  107
5    NaN    NaN  110

I need to fill NaNs in all columns in a particular way. Let's take column "A" as an example: the last non-NaN value is row #1 (90.0). So for column "A" I need to fill NaNs with the following formula:

Column_A-Row_1   *   Column_B-CurrentRow   /   Column_B-Row_1

For example, the first NaN of column A (row #2) should be filled with: 90 * 105 / 120. The following NaN of column A should be filled with: 90 * 100 / 120.

Please note that column names can change, so I can't reference columns by name.

This is the expected output:

        A       B      C
0  100.00  110.00  100.0
1   90.00  120.00  110.0
2   78.75  105.00  105.0
3   75.00  100.00  103.0
4     NaN  103.88  107.0
5     NaN  106.80  110.0

Any ideas? Thanks

CodePudding user response：

You can fill the first NaN that follows a number using shift on both axis:

df2 = df.combine_first(df.shift().mul(df.div(df.shift()).shift(-1,axis=1)))

output:

        A           B    C
0  100.00  110.000000  100
1   90.00  120.000000  110
2   78.75  105.000000  105
3     NaN  100.000000  103
4     NaN  103.883495  107
5     NaN         NaN  110

It is unclear how you get the 75 though, do you want to iterate the process?