Pandas groupby object filtering

Question

i have a pandas dataframe

df.columns
Index([u’car_id’,u’color’,u’make’,u’year’)]

I would like to create a new FILTERABLE object that has the count of each group (color,make,year);

grp = df[[‘color’,’make’,’year’]].groupby([‘color’,’make’,’year’]).size()

which will return something like this

color   make   year     count
black   honda  2011   416

I would like to be able to filter it, however when I try this:

grp.filter(lambda x: x[‘color’]==‘black’)

I receive this error

TypeError: 'function' object is not iterable

How do I leverage a 'groupby' object in order to filter the rows out?

Answer 1

I think you need add reset_index and then output is DataFrame. Last use boolean indexing:

df = df[['color','make','year']].groupby(['color','make','year'])
                                .size()
                                .reset_index(name='count')


df1 = df[df.color == 'black']

Answer 2

Option 1
Filter ahead of time

cols = ['color','make','year']
df[df.color == 'black', cols].grouby(cols).size()

Option 2 Use xs for index cross sections

cols = ['color','make','year']
grp = df[cols].groupby(cols).size()

df.xs('black', level='color', drop_level=False)

or

df.xs('honda', level='make', drop_level=False)

or

df.xs(2011, level='year', drop_level=False)