title: Fastest way to iterate over two panda data frames
body: <p>I have two data frames:</p>
  
  <p><code>dt1</code> where I store millions of registry key entries. Defined with columns: <code>Index([u'count', u'id', u'is_malicious', u'key', u'name', u'value'], dtype='object')</code></p>
  
  <p>And <code>dt2</code> where I match those registry entries to machines across our network. Defined with columns: <code>Index([u'id', u'machine_id', 'registry_key_id'], dtype='object')</code></p>
  
  <p><strong>What is the fastest way to iterate over all the rows in <code>dt1</code>, and for each row, count how many times <code>row['id']</code> is found in <code>dt2</code> column <code>row['registry_key_id']</code>?</strong></p>
  
  <p>The pseudocode can be thought as:</p>
  
  <pre><code>for row in dt1:
    row['count'] = count(dt2[dt2['registry_key_id'] == row['id']]
  </code></pre>
  
  <p>Right now we are using <code>for index, row in panda.iterrows():</code> but it's quite slow when it comes to processing hundreds of thousands of rows. We are looking for method to considerably accelerate that process.</p>
  
  <p>Thank you for your assistance.</p>
  
  <p><strong>Update 1:</strong></p>
  
  <p>See the code below:</p>
  
  <pre><code>count = count.groupby('registry_key_id').count()
  res = hunter.registry_keys().copy(deep=True)
  res['count'] = res['id'].map(count['id'])
  </code></pre>
  
  <p>We have ascertained that <code>len(count) == len(res)</code> and that <code>count['id']</code> returns the total number of times <code>'registry_key_id'</code> is seen.</p>
  
  <p>But all the values in <code>res['count']</code> are <code>NaN</code>.</p>
  
  <p>Could you please assist in fixing that?</p>
  
  <p><strong>Answer:</strong></p>
  
  <p>Using a combination of Flab's and Yarnspinner's responses, I was able to cut down the time of counting and mapping the pandas from 1 hour to 12 seconds. Thanks!</p>