title: How to create a Pandas Groupby object where each column has a filter on the original data?
body: <p>I'm trying to replicate the following SQL with Pandas, but it's surprisingly more complex than I expected:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
      id
      , count(*)
      , count(case when some_condition = True then 1 end)
      , count(case when some_other_condition = False then 1 end)
  FROM table
  GROUP BY id
  </code></pre>
  <p>The only thing I can think of is something like this:</p>
  <pre class="lang-py prettyprint-override"><code>grouped = df.groupby('id')
  df_total = grouped.count()
  df_some_condition = grouped.filter(...).count()
  df_some_other_condition = grouped.filter(...).count()
  df_total.join(df_some_condition, on='id').join(df_some_other_condition, on='id')
  </code></pre>
  <p>I'm just surprised that I can't make filtered columns with groupby().count(), and that I have to create 3 separate dataframes and then join them. Is there a simpler way to do this that I'm overlooking?</p>
  <p>Note: the syntax may not be exactly correct here, just wrote up something quickly to illustrate my issue.</p>