title: Groupby Mutate Performance in Pandas
body: <p>I often find myself trying to do the R equivalent of <code>groupby</code> then <code>mutate</code>, but as many people have pointed out simply using a <code>groupby</code> and <code>apply</code> suffers from major performance issues. So my question, what is the best (highest-performance) way to group a dataframe in pandas then based on some condition in that group, add a new column based on some calculations?</p>
  
  <p>(I have searched and searched and I have not found any guides / steps on how to vectorize custom functions in pandas using numpy. All the answers to similar questions are always case-specific and don't generalize well.)</p>
  
  <p>Example data:</p>
  
  <pre><code>df
  Out[17]: 
               ID   ID2        col1        col2       col3    value
  0             1     J       333.5       333.3      333.4      cat    
  1             1     S       333.5       333.3      333.8      
  2             2     J       333.7       333.3      333.8      cat
  3             2     S       333.7       333.3      333.4      dog
  4             3     L       333.7       333.8      333.9      
  5             3     D       333.8       333.8      333.9      
  6             4     S       333.8       333.6      333.7      cat
  7             4     J       333.8       333.2      333.8      
  8             4     J       333.8       333.7      333.9      
  9             4     L       333.8       333.3      333.4      cat
  </code></pre>
  
  <p>Here are some examples, I run into constantly:</p>
  
  <h1>1) <code>apply</code> function to groups based on condition, return those results together with the original dataframe.</h1>
  
  <h1><code>df.groupby(by=['ID']).apply(myfunc)</code></h1>
  
  <pre><code>def myfunc(group):
      group['new_col'] = len(group.query('''ID2=='T' &amp; (col1&gt;=col3 | px&lt;=col2)''').unique())
      return group
  </code></pre>
  
  <h1>2) Similar to 1), but only update one existing column based on some condition, then return that result together with the original dataframe.</h1>
  
  <h1><code>df.groupby(by=['ID']).apply(update_func)</code></h1>
  
  <pre><code>def update_func(group):
       if 'S' in group['ID2'].values:
            group.loc[(group['value']=='cat'), 'other_column'] = False
       return group
  </code></pre>