title: Need for speed: Slow nested groupbys and applys in Pandas
body: <p>I am performing a complex transformation on a DataFrame. I thought it would be quick for Pandas, but the only way I've managed to do it is with some nested groupbys and applys, using lambda functions, and it is slow. It seems like the sort of thing where there should be built-in, faster methods. At n_rows=1000 it's 2 seconds, but I'll be doing 10^7 rows, so this is far too slow. It's difficult to explain what we're doing, so here's the code and profile, then I'll explain:</p>
  
  <pre><code>n_rows = 1000
  
  d = pd.DataFrame(randint(1,10,(n_rows,8))) #Raw data
  dgs = array([3,4,1,8,9,2,3,7,10,8]) #Values we will look up, referenced by index
  grps = pd.cut(randint(1,5,n_rows),arange(1,5)) #Grouping
  
  f = lambda x: dgs[x.index].mean() #Works on a grouped Series
  g = lambda x: x.groupby(x).apply(f) #Works on a Series
  h = lambda x: x.apply(g,axis=1).mean(axis=0) #Works on a grouped DataFrame
  
  q = d.groupby(grps).apply(h) #Slow
  
  
  
  824984 function calls (816675 primitive calls) in 1.850 seconds
  Ordered by: internal time
  ncalls  tottime  percall  cumtime  percall filename:lineno(function)
  221770    0.105    0.000    0.105    0.000 {isinstance}
    7329    0.104    0.000    0.217    0.000 index.py:86(__new__)
    8309    0.089    0.000    0.423    0.000 series.py:430(__new__)
    5375    0.081    0.000    0.081    0.000 {method 'reduce' of 'numpy.ufunc' objects}
   34225    0.068    0.000    0.133    0.000 {method 'view' of 'numpy.ndarray' objects}
  36780/36779    0.067    0.000    0.067    0.000 {numpy.core.multiarray.array}
    5349    0.065    0.000    0.567    0.000 series.py:709(_get_values)
   985/1    0.063    0.000    1.847    1.847 groupby.py:608(apply)
    5349    0.056    0.000    0.198    0.000 _methods.py:42(_mean)
    5358    0.050    0.000    0.232    0.000 index.py:332(__getitem__)
    8309    0.049    0.000    0.228    0.000 series.py:3299(_sanitize_array)
    9296    0.047    0.000    0.116    0.000 index.py:1341(__new__)
     984    0.039    0.000    0.092    0.000 algorithms.py:105(factorize)
  </code></pre>
  
  <p>Group the DataFrame rows by the groupings. For each grouping, for each row, group by those values that are the same (i.e. all have the value 3 versus all have value 4). For each index in a value grouping, look up the corresponding index in <code>dgs</code>, and average. Then average for the row groupings.</p>
  
  <p>::exhale::</p>
  
  <p>Any suggestions on how to rearrange this for speed would be appreciated. </p>