title: Pandas - Explanation on apply function being slow
body: <p>Apply function seems to work very slow with a large dataframe (about 1~3 million rows).</p>
  
  <p>I have checked related questions here, like <a href="https://stackoverflow.com/questions/31363908/speeding-up-pandas-apply-function">Speed up Pandas apply function</a>, and <a href="https://stackoverflow.com/questions/38267210/counting-within-pandas-apply-function">Counting within pandas apply() function</a>, it seems the best way to speed it up is not to use apply function :)</p>
  
  <p>For my case, I have two kinds of tasks to do with the apply function.</p>
  
  <p>First: apply with lookup dict query</p>
  
  <pre><code>f(p_id, p_dict):
      return p_dict[p_dict['ID'] == p_id]['value']
  
  p_dict = DataFrame(...)  # it's another dict works like lookup table
  df = df.apply(f, args=(p_dict,))
  </code></pre>
  
  <p>Second: apply with groupby</p>
  
  <pre><code>f(week_id, min_week_num, p_dict):
      return p_dict[(week_id - min_week_num &lt; p_dict['WEEK']) &amp; (p_dict['WEEK'] &lt; week_id)].ix[:,2].mean()
  
  f_partial = partial(f, min_week_num=min_week_num, p_dict=p_dict)
  df = map(f, df['WEEK'])
  </code></pre>
  
  <p>I guess for the fist case, it could be done with dataframe join, while I am not sure about resource cost for such join on a large dataset.</p>
  
  <p>My question is: </p>
  
  <ol>
  <li>Is there any way to substitute apply in the two above cases?</li>
  <li>Why is apply so slow? For the dict lookup case, I think it should be O(N), it shouldn't cost that much even if N is 1 million.</li>
  </ol>