How to do forward filling for each group in pandas

Question

I have a dataframe similar to below

id A   B   C   D E
1  2   3   4   5 5
1  NaN 4   NaN 6 7
2  3   4   5   6 6
2  NaN NaN 5   4 1

I want to do a null value imputation for columns A, B, C in a forward filling but for each group. That means, I want the forward filling be applied on each id. How can I do that?

Answer 1

Use GroupBy.ffill for forward filling per groups for all columns, but if first values per groups are NaNs there is no replace, so is possible use fillna and last casting to integers:

print (df)
   id    A    B    C  D    E
0   1  2.0  3.0  4.0  5  NaN
1   1  NaN  4.0  NaN  6  NaN
2   2  3.0  4.0  5.0  6  6.0
3   2  NaN  NaN  5.0  4  1.0

cols = ['A','B','C']
df[['id'] + cols] = df.groupby('id')[cols].ffill().fillna(0).astype(int)
print (df)
   id  A  B  C  D    E
0   1  2  3  4  5  NaN
1   1  2  4  4  6  NaN
2   2  3  4  5  6  6.0
3   2  3  4  5  4  1.0

Detail:

print (df.groupby('id')[cols].ffill().fillna(0).astype(int))
   id  A  B  C
0   1  2  3  4
1   1  2  4  4
2   2  3  4  5
3   2  3  4  5

Or:

cols = ['A','B','C']
df.update(df.groupby('id')[cols].ffill().fillna(0))
print (df)
   id    A    B    C  D    E
0   1  2.0  3.0  4.0  5  NaN
1   1  2.0  4.0  4.0  6  NaN
2   2  3.0  4.0  5.0  6  6.0
3   2  3.0  4.0  5.0  4  1.0