API/ENH: idxmax groupby API

[jreback]

from SO

Thinking that this is a nice general idiom that might deserve a function:

df.loc[df.groupby(level='host').idxmax()['no']]

maybe:
a) df.groupby(level='host').idxmax(loc='no') ? (e.g. have the idxmin/idxmin) functions take a 'loc' argument. or is this bending the api a big much?
b) df.groupby(level='host').loc('no').idxmax()
c) df.groupby(level='hist').loc[lambda x: x.idxmax()['no']]
d) df.groupby(level='hist').loc('no','idxmax')

df_logfile = pd.DataFrame({ 
    'host' : ['this.com', 'this.com', 'this.com', 'that.com', 'other.net', 
              'other.net', 'other.net'],
    'service' : ['mail', 'mail', 'web', 'mail', 'mail', 'web', 'web' ] })

df = df_logfile.groupby(['host','service'])['service'].agg({'no':'count'})
mask = df.groupby(level=0).agg('idxmax')
df_count = df.loc[mask['no']]
df_count = df_count.reset_index()

# yields
        host service  no
0  other.net     web   2
1   that.com    mail   1
2   this.com    mail   2

[jorisvandenbossche]

@jreback Once you have df, doing the original df.loc[df.groupby(level='host').idxmax()['no']] looks more clear (or more self-explaining) to me personally than the alternatives you propose.

But, thinking more in general, I think this would be a kind of filter method. Not the filter we have now (filter whole groups), but one that filters rows within groups (with a specific filter criterium depending on the group).
That is something I always felt confusing with groupby().filter() to remember which of the two types it was.

# accepting function applied on each group returning True/False for each row
df.groupby().filter_within_group(lambda x: x['col'] > x['col'].mean())

# accepting function applied on each group returning labels of the index to include
# (could these both types be in one function?)
df.groupby().filter_within_group(lambda x: x['col'].idxmax())

But maybe .loc() would be a good name for that, or something like .select()?

[jreback]

df.groupby(..).filterby(lambda x: x['col'].idxmax())
df.groupby(..).where(lambda x: x['col'].idxmax())

not sure we can do this w/o the lamda, though maybe

df.groupby(..).where('idxmax',column='col')

[jreback]

SQL calls this HAVING, so:

df.groupby(..).having(lambda x: x['col'].idxmax())

I think I like this the best or .where

[TomAugspurger]

Would .having or .where return a GroupBy object (a subset of the original? GroupBy) This is something I've found a bit difficult in pandas. Filtering (subselecting) a DataFrame based on a groupwide calculation, and aggregating / transformaing them. e.g. Taking the groupwise minimum of those above the groupwise mean.

df.groupby('group_col').having(lambda x: x > x.mean()).min()

would be better than the current

df.groupby('group_col').filter(lambda x: x > x.mean()).groupby('group_col').min()

which requires a second groupby. I didn't test that specific example btw.