title: Improving the performance of pandas groupby
body: <p>I have a machine learning application written in Python which includes a data processing step. When I wrote it, I initially did the data processing on Pandas DataFrames, but when this lead to abysmal performance I eventually rewrote it using vanilla Python, with for loops instead of vectorized operations and lists and dicts instead of DataFrames and Series. To my surprise, the performance of the code written in vanilla Python ended up being <em>far</em> higher than that of the code written using Pandas.</p>
  
  <p>As my handcoded data processing code is substantially bigger and messier than the original Pandas code, I haven't quite given up on using Pandas, and I'm currently trying to optimize the Pandas code without much success.</p>
  
  <p>The core of the data processing step consists of the following: I first divide the rows into several groups, as the data consists of several thousand time series (one for each "individual"), and I then do the same data processing on each group: a lot of summarization, combining different columns into new ones, etc.</p>
  
  <p>I profiled my code using Jupyter Notebook's <code>lprun</code>, and the bulk of the time is spent on the following and other similar lines:</p>
  
  <pre><code>grouped_data = data.groupby('pk')
  data[[v + 'Diff' for v in val_cols]] = grouped_data[val_cols].transform(lambda x: x - x.shift(1)).fillna(0)
  data[[v + 'Mean' for v in val_cols]] = grouped_data[val_cols].rolling(4).mean().shift(1).reset_index()[val_cols]
  (...)
  </code></pre>
  
  <p>...a mix of vectorized and non-vectorized processing. I understand that the non-vectorized operations won't be faster than my handwritten for loops, since that's basically what they are under the hood, but how can they be <em>so much slower</em>? We're talking about a performance degradation of 10-20x between my handwritten code and the Pandas code.</p>
  
  <p>Am I doing something very, very wrong?</p>