Pandas: Group by, filter rows, get the mean

debugcn Published at Dev

Makoto Miyazaki

In python I have a pandas data frame df like this:

 ID      Geo    Speed
123    False       40
123     True       90
123     True       80
123    False       50
123     True       10
456    False       10
456     True       90
456    False       40
456     True       80

I want to group df by ID, and filter out rows where Geo == False, and get the mean of Speed in the group. So the result should look like this.

 ID     Mean 
123       60  
456       85

My attempt:

df.groupby('ID')["Geo" == False].Speed.mean()
df.groupby('ID').filter(lambda g: g.Geo == False)
df[df.Geo.groupby(df.ID) == False]

Neither of them worked. Any solutions? Thank you!

jezrael

Use ~ for inverting Falses to Trues for filtering by Falses by boolean indexing:

print (df[~df["Geo"]])
    ID    Geo  Speed
0  123  False     40
3  123  False     50
5  456  False     10
7  456  False     40

df = df[~df["Geo"]].groupby('ID', as_index=False).Speed.mean()
print (df)
    ID  Speed
0  123     45
1  456     25

And for filtering by Trues:

print (df[df["Geo"]])
    ID   Geo  Speed
1  123  True     90
2  123  True     80
4  123  True     10
6  456  True     90
8  456  True     80

df = df[df["Geo"]].groupby('ID', as_index=False).Speed.mean()
print (df)
    ID  Speed
0  123     60
1  456     85

Collected from the Internet

Please contact [email protected] to delete if infringement.