CODEWITHSUNDEEP
pythonpandasgroup-byfillna
user15212564
user15212564

Reputation:

Fill NaN value after grouping twice

Dummy data :

code = ['a','a','a','a','b','b']
serial  =  ['x','y','x','y','x','y']
result = [123,  np.nan, 453, 675, 786, 332]

  code serial  result
0  a    x      123.0 
1  a    y     NaN    
2  a    x      453.0 
3  a    y      675.0 
4  b    x      786.0 
5  b    y      332.0

I want to fill NaN with 675.0, first group by code then by serial and fill the NaN value

Code:

df['result'] = df['result'].fillna(df.groupby('code')['result'].ffill())

In the code above; I want to integrate .groupby('serial')

Upvotes: 1

Views: 48

Answers (2)

Vivek Kalyanarangan
Vivek Kalyanarangan

Reputation: 9081

Use -

df['result'].fillna(df.groupby(['code', 'serial'])['result'].transform('first'))

Output

0    123
1    675
2    453
3    675
4    786
5    332
Name: result, dtype: int64

Upvotes: 2

perl
perl

Reputation: 9941

You can groupby both columns at the same time:

df['result'] = df.groupby(['code', 'serial'])['result'].bfill()
df

Output:

  code serial  result
0    a      x   123.0
1    a      y   675.0
2    a      x   453.0
3    a      y   675.0
4    b      x   786.0
5    b      y   332.0

P.S. You would need to bfill instead of ffill it though, since that NaN comes before the first value in the group

Upvotes: 1

Related Questions