title: Pandas, groupby and finding maximum in groups, returning value and count
body: <p>I have a pandas DataFrame with log data:</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>And I want to find the service on every host that gives the most errors:</p>
  
  <pre><code>        host service  no
  0   this.com    mail   2
  1   that.com    mail   1
  2  other.net     web   2
  </code></pre>
  
  <p>The only solution I found was grouping by host and service, and then iterating
  over the level 0 of the index.  </p>
  
  <p>Can anyone suggest a better, shorter version? without the Iteration?</p>
  
  <pre><code>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) &amp; (df_count['service'] == service), 'no']   = no
  </code></pre>
  
  <p>full code <a href="https://gist.github.com/bjelline/d8066de66e305887b714" rel="noreferrer">https://gist.github.com/bjelline/d8066de66e305887b714</a></p>