repeat non-null value in Pandas DataFrame with Groupby

user_9339958 0
Viewed 35

I'd like to fill all NaN values with a non-NaN value using groupby. I know I can do this using fillna(method='ffill') and then fillna(method='bfill') since my non-NaN value is not always a the beginning or end, or I can create a new DataFrame with the non-NaN value and then merge them, but I need to do this a bunch of times and I was wondering if there is a more efficient method to reduce the process time.

I was thinking something like a .transform(fillna) function.

This is my input:

col1 col2
A Nan
A Nan
A 'apples'
B Nan
B 'bananas'
B Nan

and this is what I'd like to get

col1 col2
A 'apples'
A 'apples
A 'apples'
B 'bananas
B 'bananas'
B 'bananas'
pandas dataframe python
2 Answers

As envisioned, you can do:

df.groupby('col1')['col2'].ffill().bfill()

You can also do:

df.groupby('col1')['col2'].transform('first')

which should be faster on large dataframes.

Note that it is doing the same thing in this case but it would behave differently if you have non unique non-NaN values in each group.

user_163434640 
df1.col2 = df1.col2.mask((df1.col2.isnull()) & (df1.col1 == 'A'), 'apples')
df1.col2 = df1.col2.mask((df1.col2.isnull()) & (df1.col1 == 'B'), 'bananas')
user_143516990
Related