Geek Answers Handbook

Pandas, groupby and finding the maximum in groups, returning the value and quantity

I have a pandas DataFrame with log data:

host service 0 this.com mail 1 this.com mail 2 this.com web 3 that.com mail 4 other.net mail 5 other.net web 6 other.net web

And I want to find a service on each host that gives the most errors:

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

The only solution I found was grouping by host and service, and then iterating over index level 0.

Can anyone suggest a better, shorter version? without iteration?

 df = df_logfile.groupby(['host','service']).agg({'service':np.size}) df_count = pd.DataFrame() df_count['host'] = df_logfile['host'].unique() df_count['service'] = np.nan df_count['no'] = np.nan for h,data in df.groupby(level=0): i = data.idxmax()[0] service = i[1] no = data.xs(i)[0] df_count.loc[df_count['host'] == h, 'service'] = service df_count.loc[(df_count['host'] == h) & (df_count['service'] == service), 'no'] = no

full code https://gist.github.com/bjelline/d8066de66e305887b714

+7
python numpy pandas
source share
1 answer

Given df , the next step is to group only by host value and
aggregate on idxmax . This gives you the index that corresponds to the highest value of the service. Then you can use df.loc[...] to select the lines in df that match the largest utility values:

 import numpy as np import pandas as pd 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() print("\nOutput\n{}".format(df_count))

returns a dataframe

  host service no 0 other.net web 2 1 that.com mail 1 2 this.com mail 2
+4
source share

More articles:


All Articles