title: The fastest way to find primary key candidates in CSV file?
body: <p><strong>Find the primary key candidates in CSV file,</strong> the test requires the nums of column &lt;= 4 and any subset of these columns cannot be a primary key.
  There are some example:</p>
  
  <ul>
  <li>key1: [column18] ==>correct</li>
  <li>key2: [column15,column18] ==>wrong, contains key1</li>
  <li>key3: [column1,column2,column3,column5,column6] ==> wrong, more than 4</li>
  </ul>
  
  <p>This is a test for me! I have finished it in <strong>35 seconds</strong>, but not reach the efficient requirement: find primary keys in about <strong>1 second</strong> in a csv file about 10000 lines.</p>
  
  <p><strong>Test Data:</strong></p>
  
  <ul>
  <li><p><a href="https://drive.google.com/open?id=1j-u5IGGPb090gSa2SxgYwD8Af6WTEtGq" rel="nofollow noreferrer">you can get the example csv file here</a>.</p></li>
  <li><p>The right answer of this file is:
  [['column8', 'column11', 'column15', 'column18']]</p></li>
  </ul>
  
  <p>So, my question is <strong>"Is there any more efficient way to find primary keys?"</strong></p>
  
  <p><strong>Here is my code:</strong></p>
  
  <pre><code># coding: utf-8
  import pandas as pd
  import numpy as np
  import os
  import itertools
  import time
  
  def is_subkey(newkey,keys):
  #     test wheather newkey is sub set of any keys
      for key in keys:
          if set(key).issubset(newkey):
              return True
      return False
  
  def primarykey_recognition(file,max_num=4):
  #     file is an file object, returnd by function open()
  #   doc is a pandas DF
      doc = pd.read_csv(file,sep=',')
      num = 1
      result = []
      table_length = len(doc.values)
      while num &lt;= max_num:
          keys = list(itertools.combinations(doc.columns,num))
  #         print(keys)
          for key in keys:
              if is_subkey(key,result):
  #           if key belong to any sub set of keys in result ,continue
                  continue
  #           
              bools = np.array(doc.duplicated(subset=list(key)))
              if np.sum(bools) &gt; 0:
  #           sum(bools) means bools has duplicated lines 
                  continue
              else:
                  result.append(list(key))
          num += 1
      return result
  
  if __name__=="__main__":
  
      with open(r"..\data\Table_C.csv") as file:
          tic = time.clock()
          keys = primarykey_recognition(file)
          toc = time.clock()
  
          print("File {} has primary keys: ".format(filename))
          print(keys)
          print("Elapsed: {} s".format(round(toc - tic,4)))
  </code></pre>
  
  <p>I find there is a similar question ,<a href="https://stackoverflow.com/questions/49998457/how-to-find-a-columns-set-for-a-primary-key-candidate-in-csv-file">How to find a columns set for a primary key candidate in CSV file?</a>, but the code is <strong>not efficient</strong> and find wrong keys like example key2.</p>
  
  <p><strong>Here is the code of that question:</strong></p>
  
  <pre><code># coding: utf-8
  import pandas
  from itertools import chain, combinations
  import time
  
  def key_options(items):
      return chain.from_iterable(combinations(items, r) for r in range(1, len(items)+1) )
  
  tic = time.clock()    
  df = pandas.read_csv(r"..\data\Table_C.csv");
  
  # iterate over all combos of headings, excluding ID for brevity
  for candidate in key_options(list(df)[1:]):
      deduped = df.drop_duplicates(candidate)
  
      if len(deduped.index) == len(df.index):
          print(','.join(candidate))
  
  toc = time.clock()
  print("Elapsed: {} s".format(round(toc - tic,4)))
  </code></pre>