Pandas optimization for multiple records
<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>

<p>While no data is available, consider this untested approach with a couple of left join merges of both data pieces and then run the validation steps. This would avoid any looping and run conditional logic across columns:</p>

<pre><code>import pandas as pd
import numpy as np

with open('RecordsValidate.txt') as f:
    records = f.readlines()
    print(records)

rdf = pd.DataFrame({'rcd_id': list(range(1,len(records)+1)),
                    'rcd_area_code': [int(rcd[:3]) for rcd in records],
                    'rcd_office_code': [int(rcd[3:6]) for rcd in records],
                    'rcd_subscriber_number': [rcd[6:] for rcd in records]})

filename = 'myfile.csv'
df = pd.read_csv(filename)

# VALIDATE AREA CODE
mrgdf = pd.merge(df, rdf, how='left', left_on=['AREA_CODE'], right_on=['rcd_area_code'])
mrgdf['RETURN'] = np.where(pd.isnull('rcd_id'), 'INVALID_AREA_CODE', np.nan)

mrgdf.drop([c for c in rdf.columns], inplace=True,axis=1)

# VALIDATE OFFICE CODE                         
mrgdf = pd.merge(mrgdf, rdf, how='left', left_on=['AREA_CODE', 'OFFICE_CODE'],
                 right_on=['rcd_area_code', 'rcd_office_code'])
mrgdf['RETURN'] = np.where(pd.isnull('rcd_id'), 'INVALID_OFFICE_CODE', mrgdf['RETURN'])

# VALIDATE SUBSCRIBER
mrgdf['RETURN'] = np.where((mrgdf['rcd_subscriber_number'] &lt; mrgdf['SUBSCRIBER_START']) |
                           (mrgdf['rcd_subscriber_number'] &gt; mrgdf['SUBSCRIBER_END']) |
                           (mrgdf['LABEL'].str.len() =  0),
                           'INVALID_SUBSCRIBER', mrgdf['RETURN'])
mrgdf.drop([c for c in rdf.columns], inplace=True,axis=1)
</code></pre>

python pandas