title: Pandas optimization for multiple records
body: <p>I have a file with around 500K records.
  Each record needs to be validated.
  Records are de duplicated and store in a list:</p>
  
  <pre><code>with open(filename) as f:
      records = f.readlines()
  </code></pre>
  
  <p>The validation file I used is stored in a Pandas Dataframe
  This DataFrame contains around 80K records and 9 columns (myfile.csv).</p>
  
  <pre><code>filename = 'myfile.csv'
  df = pd.read_csv(filename)
  
  def check(df, destination):
      try:
          area_code = destination[:3]
          office_code = destination[3:6]
          subscriber_number = destination[6:]
  
  
          if any(df['AREA_CODE'].astype(int) == area_code):
              area_code_numbers = df[df['AREA_CODE'] == area_code]
              if any(area_code_numbers['OFFICE_CODE'].astype(int) == office_code):
                  matching_records = area_code_numbers[area_code_numbers['OFFICE_CODE'].astype(int) == office_code]
  
                  start = subscriber_number &gt;= matching_records['SUBSCRIBER_START']
                  end = subscriber_number &lt;= matching_records['SUBSCRIBER_END']
                  # Perform intersection
                  record_found = matching_records[start &amp; end]['LABEL'].to_string(index=False)
                  # We should return only 1 value
                  if len(record_found) &gt; 0:
                      return record_found
                  else:
                      return 'INVALID_SUBSCRIBER'
              else:                   
                  return 'INVALID_OFFICE_CODE'
          else:               
              return 'INVALID_AREA_CODE'
      except KeyError:
          pass
      except Exception:
          pass
  </code></pre>
  
  <p>I'm looking for a way to improve the comparisons, as when I run it, it just hangs. If I run it with an small subset (10K) it works fine.
  Not sure if there is a more efficient notation/recommendation.</p>
  
  <pre><code>for record in records:
      check(df, record)
  </code></pre>
  
  <p>Using MacOS 8GB/2.3 GHz Intel Core i7.</p>
  
  <p>With Cprofile.run in <strong>check</strong> function alone shows:  </p>
  
  <pre><code>4253 function calls (4199 primitive calls) in 0.017 seconds.
  </code></pre>
  
  <p>Hence I assume 500K will take around 2 1/2 hours</p>