I need to fill in the Null values with values that are already populated with the ColA group
original df:
ColA ColB ColC
cat 20 blue
cat 28 yellow
fish 32 red
fish NaN NaN
fish NaN NaN
dog 3 black
dog 311 tan
dog 755 white
frog NaN NaN
frog NaN NaN
frog 400 green
frog NaN NaN
Leave alone the rows that have populated cells in each column, but where there are null values, I want to fill those cells with the associated value that the ColA group has populated, for example:
desired df:
ColA ColB ColC
cat 20 blue
cat 28 yellow
fish 32 red
fish 32 red
fish 32 red
dog 3 black
dog 311 tan
dog 755 white
frog 400 green
frog 400 green
frog 400 green
frog 400 green
Any ideas is appreciated. Thanks!
CodePudding user response:
Try goupby
with forward fill (ffill
) and back fill(bfill
)
df[['ColB', 'ColC']] = df.groupby('ColA').ffill().bfill()
ColA ColB ColC
0 cat 20.0 blue
1 cat 20.0 yellow
2 fish 32.0 red
3 fish 32.0 red
4 fish 32.0 red
5 dog 3.0 black
6 dog 3.0 tan
7 dog 3.0 white
8 frog 400.0 green
9 frog 400.0 green
10 frog 400.0 green
11 frog 400.0 green