Search code examples
pandasgroup-byfillna

pandas: fillna whole df with groupby

I have the following df with a lot more number columns. I now want to make a forward filling for all the columns in the dataframe but grouped by id. 

id  date  number    number2
1   2001    4         11
1   2002    4         45
1   2003    NaN       13
2   2001    7         NaN
2   2002    8         2

The result should look like this:

id  date  number    number2
1   2001    4         11
1   2002    4         45
1   2003    4         13
2   2001    7         NaN
2   2002    8         2

I tried the following command:

df= df.groupby("id").fillna(method="ffill", limit=2)

However, this raises a KeyError "isin". Filling just one column with the following command works just fine, but how can I efficiently forward fill the whole df grouped by isin?

df["number"]= df.groupby("id")["number"].fillna(method="ffill", limit=2)
Solution

  • You can use:

    df = df.groupby("id").apply(lambda x: x.ffill(limit=2))
print (df)
   id  date  number  number2
0   1  2001     4.0     11.0
1   1  2002     4.0     45.0
2   1  2003     4.0     13.0
3   2  2001     7.0      NaN
4   2  2002     8.0      2.0

    Also for me working:

    df.groupby("id").fillna(method="ffill", limit=2)

    so I think is necessary upgrade pandas.