title: how to optimize the time of record linkage in a database in python?
body: <p>Here I'm comparing the record to deduplicate the records.</p>
  <p>following dataframe is my dataset,</p>
  <pre><code>df = pd.DataFrame({
  'FIRSTNAME':['richard','patricia','william','patricia','dale','richard'],
  'LASTNAME':['dietzen','economide','macdonald','economide','sowders','dietzen'],
  'CONTACT': ['187067706','not available','124471426',''not available','197426502','187067706'],
  'BUSNESS_MAIL':['not available','management@gmail.com','donald@mcrtrust.com','management@gmail.com',sowders@partners.com','not available'],
  'EMAIL':['rich@gmail.com','economides@gmail.com','not available','economides@gmail.com','dale@versity.org','rich@gmail.com']
  'INFO':[['richard dietzen 187067706 not available rich@gmail.com'],
  ['patricia economide not available management@gmail.com economides@gmail.com'],
  ['william macdonald 124471426 donald@mcrtrust.com not available'],
  ['patricia economide not available management@gmail.com economides@gmail.com'],
  ['dale sowders 197426502 sowders@partners.com dale@versity.org'],
  ['richard dietzen 187067706 not available rich@gmail.com']]})
  </code></pre>
  <p>Here the above dataframe i convert the INFO column to series and compute the score. but it's take longer time to execute the code, the following code is i used to calculate the confidence score for custom function.</p>
  <pre><code>class CustomFeatureContacts(BaseCompareFeature):
  
      def _compute_vectorized(self, s1, s2):
      # algorithm that compares s1 and s2
      # This combines the columns you are comparing into a single DataFrame
         concat = pd.concat([s1, s2], axis=1, ignore_index=True)
  
         def inner_apply(x):
  
          &quot;&quot;&quot;
          This is where your custom algorithm is housed.
          Create a function to be applied to each pair in the DataFrame.
          &quot;&quot;&quot;
  
            info_1 = x[0]
            info_2 = x[1]
  
            info_1 = ast.literal_eval(info_1)
            info_2 = ast.literal_eval(info_2)
            idx_list_1 = []
            idx_list_2 = []
  
            for idx_1,item_1 in enumerate(info_1):
                if item_1 != 'not available':
                    idx_list_1.append(idx_1)
  
            for idx_2,item_2 in enumerate(info_2):
                 if item_2 != 'not available':
                     idx_list_2.append(idx_2)
  
            elements_in_all = list(set(idx_list_1).intersection(idx_list_2))
  
            num_fields = len(elements_in_all)
  
            for idx in elements_in_all:
                if idx == 0:
                   full_name_1 = info_1[0]
                   full_name_2 = info_2[0]
  
                   full_name_score = fuzz.ratio(full_name_1, full_name_2) / 100
  
               elif idx == 3:
                   mobile_num_1 = contact_info_1[3]
                   mobile_num_2 = contact_info_2[3]
  
                   if mobile_num_1 == mobile_num_2:
                      mobile_num_score = 1.0
  
               elif idx == 1:
                   business_email_1 = info_1[1]
                   business_email_2 = info_2[1]
                   business_email_score = SequenceMatcher(None, business_email_1, business_email_2).ratio()
  
                elif idx == 2:
                  personal_email_1 = info_1[2]
                  personal_email_2 = info_2[2]
  
                  personal_email_score = SequenceMatcher(None, personal_email_1, personal_email_2).ratio()
  
          overall_score = (full_name_score + mobile_num_score+ personal_email_score + business_email_score )/num_fields
  
        return overall_score
  
  return concat.apply(inner_apply, axis=1)
  </code></pre>
  <p>this above function is getting the score of each field(column) matching score, to get score i have split the info column (pd.series) details and get the indexes of available data (exclude the not available data indexes) columns and intersect with each record for get score. but i need to optimize this code, i tried several ways, but the data size is very large it's taking more than 45 minutes. please anyone help me to optimize and speed up the code.</p>