title: Python pandas groupby then filter row-wise and return a count
body: <p>I have a the following dataframe:</p>
  
  <pre><code>df = pd.DataFrame({
      'cluster': ['A','B','C','A','B','C','D','D'],
      'profit': [-1.0,1.5,1,0.5,3.0,-2,-1, -2]
      })
  </code></pre>
  
  <p>I am performing a series of groupby operations before outputting into another datafram, most of which I have got to work.</p>
  
  <pre><code>df['cluster_total_profit'] = df.groupby(['cluster'])['profit'].transform('sum')
  
  df['cluster_mean_profit'] = df.groupby(['cluster'])['profit'].transform('mean')
  
  df['occurances'] = df.groupby(['cluster'])['profit'].transform('count')
  
  df['std'] = df.groupby(['cluster'])['profit'].transform('std')
  
  clusters = df[['cluster','cluster_total_profit', 'cluster_mean_profit', 'occurances', 'std']].drop_duplicates().reset_index(drop=True)
  </code></pre>
  
  <p>The output is as follows:</p>
  
  <pre><code>  cluster  cluster_total_profit  cluster_mean_profit  occurances      std
  0       A                  -0.5                -0.25           2  1.06066
  1       B                   4.5                 2.25           2  1.06066
  2       C                  -1.0                -0.50           2  2.12132
  3       D                  -3.0                -1.50           2  0.707107
  </code></pre>
  
  <p>The last transformation I am trying to get to work is to count the number of profitable events in each group and populate the df with the number of such events. The the output could be collected in the table above which would look like the following:</p>
  
  <pre><code>  cluster  cluster_total_profit  cluster_mean_profit  occurances      std    profitable_events
  0       A                  -0.5                -0.25           2  1.06066    1
  1       B                   4.5                 2.25           2  1.06066    2
  2       C                  -1.0                -0.50           2  2.12132    1
  3       D                  -3.0                -1.50           2  0.707107   0
  </code></pre>
  
  <p>I have looked <a href="https://stackoverflow.com/questions/30244952/how-do-i-create-a-new-column-from-the-output-of-pandas-groupby-sum">here</a> and <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.DataFrameGroupBy.filter.html" rel="nofollow noreferrer">here</a> but I can't quite get the examples to tranlate to my exact usecase. Here is my code:</p>
  
  <pre><code>df['profitable_events'] = df.cluster.map(df.groupby(['cluster']).filter(lambda x: x[x['profit'] &gt; 0.0].count()))
  
  clusters = df[['cluster','cluster_total_profit', 'cluster_mean_profit', 'occurances', 'std', 'profitable_events']].drop_duplicates().reset_index(drop=True)
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>df['profitable_events'] = df.groupby(['cluster']).filter(lambda x: x[x['profit'] &gt; 0.0]).transform('count')
  </code></pre>
  
  <p>Both throw an error "TypeError: filter function returned a Series, but expected a scalar bool"</p>
  
  <p>I also tried:</p>
  
  <pre><code>df['profitable_events'] = df.cluster.map(df.groupby(['cluster']).filter(lambda x: len(x[x['profit'] &gt; 0.0].index)))
  </code></pre>
  
  <p>Which errored: "TypeError: filter function returned a int, but expected a scalar bool"</p>
  
  <p>I'm sure there is a quic fix but am not sure what it is?</p>
  
  <p>Many thanks in advance</p>