CODEWITHSUNDEEP
pythonpython-3.xpandas
Jim Eisenberg
Jim Eisenberg

Reputation: 1500

Pandas groupby give any non nan values

I'm trying to perform a groupby on a table where given this groupby index, all values are either correct or Nan. EG:

    id country    name
0    1  France    None
1    1  France  Pierre
2    2    None   Marge
3    1    None  Pierre
4    3     USA     Jim
5    3    None     Jim
6    2      UK    None
7    4   Spain  Alvaro
8    2    None   Marge
9    3    None     Jim
10   4   Spain    None
11   3    None     Jim

I just want to get the values for each of the 4 people, which should never clash, eg:

   country    name
id                
1   France  Pierre
2       UK   Marge
3      USA     Jim
4    Spain  Alvaro

I've tried:

groupby().first()
groupby.nth(0,dropna='any'/'all')

and even

groupby().apply(lambda x: x.loc[x.first_valid_index()])

All to no avail. What am I missing?

EDIT: to help you making the example dataframe for testing:

df = pd.DataFrame({'id':[1,1,2,1,3,3,2,4,2,3,4,3],'country':['France','France',None,None,'USA',None,'UK','Spain',None,None,'Spain',None],'name':[None,'Pierre','Marge','Pierre','Jim','Jim',None,'Alvaro','Marge','Jim',None,'Jim']})

Upvotes: 6

Views: 3538

Answers (2)

Vaishali
Vaishali

Reputation: 38415

Pandas groupby.first returns first not-null value but does not support None, try

df.fillna(np.nan).groupby('id').first()

    country name
id      
1   France  Pierre
2   UK      Marge
3   USA     Jim
4   Spain   Alvaro

Upvotes: 8

ALollz
ALollz

Reputation: 59519

Possible specifying to dropna when values are None

df.groupby('id').first(dropna=True)

   country    name
id                
1   France  Pierre
2       UK   Marge
3      USA     Jim
4    Spain  Alvaro

Upvotes: 2

Related Questions