Search code examples
pythonpandasfillna

How can I ffill a datframe nan values from a diffrent dataframe with limit?

I am trying to ffill a dataframe with a limit of 2 and values from another dataframe. However when running the following code DF1.fillna(value=DF2,method='ffill',axis=0,limit=2), I get this error ValueError: Cannot specify both 'value' and 'method'. DF1 and DF2 are identical in shape, columns and index.

DF1

CompanyId 1/1/2018 1/1/2019 1/1/2020 1/1/2021
1 10 15 nan nan
2 12 16 nan 12
3 14 17 nan 13
4 nan 18 12 14
5 nan 18 13 nan
6 nan nan 14 nan

DF2

CompanyId 1/1/2018 1/1/2019 1/1/2020 1/1/2021
1 21 31 41 51
2 22 32 42 52
3 23 33 43 53
4 24 34 44 54
5 25 35 45 55
6 26 36 46 56

Expected result

CompanyId 1/1/2018 1/1/2019 1/1/2020 1/1/2021
1 10 15 nan nan
2 12 16 nan 12
3 14 17 nan 13
4 24 18 12 14
5 25 18 13 55
6 nan 36 14 56

Thanks in advance!

Solution

  • Assuming df1 and df2 have the identical index and columns, here is one way to approach the problem:

    Forward fill df1 with limit=2, then use isna to create a boolean mask. Now, use this boolean mask to mask the values in df2, finally fill the nan values in df1 using the masked dataframe df2

    df1.fillna(df2.mask(df1.ffill(limit=2).isna()))

       CompanyId  1/1/2018  1/1/2019  1/1/2020  1/1/2021
0          1      10.0      15.0       NaN       NaN
1          2      12.0      16.0       NaN      12.0
2          3      14.0      17.0       NaN      13.0
3          4      24.0      18.0      12.0      14.0
4          5      25.0      18.0      13.0      55.0
5          6       NaN      36.0      14.0      56.0