Pandas: Compare a column to all other columns of a dataframe
<p>I have a scenario where I have new subjects being tested for a series of characteristics where the results are all string categorical values.  Once the testing is done I needs to compare the new dataset to a master dataset of all subjects and look for similarities (matches) of a given thresh hold (say 90%).  </p>

<p>Therefore, I need to be able to do a columnar (subject-wise) comparison of each one of the new subjects in the new data set to each column in the master data set plus the others in the new data set in the best performance possible because production data set has about half million columns (and growing) and 10,000 rows.</p>

<p>Here is some example code:</p>

<pre><code>master = pd.DataFrame({'Characteristic':['C1', 'C2', 'C3'], 
                                   'S1':['AA','BB','AB'],
                                   'S2':['AB','-','BB'],
                                   'S3':['AA','AB','--']})
new = pd.DataFrame({'Characteristic':['C1', 'C2', 'C3'], 
                                'S4':['AA','BB','AA'],
                                'S5':['AB','-','BB']})
new_master = pd.merge(master, new, on='Characteristic', how='inner')  

def doComparison(comparison_df, new_columns, master_columns):
  summary_dict = {}
  row_cnt = comparison_df.shape[0]

  for new_col_idx, new_col in enumerate(new_columns):
      # don't compare the Characteristic column
      if new_col != 'Characteristic':
        print 'Evalating subject ' + new_col + ' for matches'
        summary_dict[new_col] = []
        new_data = comparison_df.ix[:, new_col]
        for master_col_idx, master_col in enumerate(master_columns):
            # don't compare same subject or Characteristic column
            if new_col != master_col and master_col != 'Characteristic':
                master_data = comparison_df.ix[:, master_col]
                is_same = (new_data == master_data) &amp; (new_data != '--') &amp; (master_data != '--')
                pct_same = sum(is_same) * 100 / row_cnt
                if pct_same &gt; 90:
                    print '  Found potential match ' + master_col + ' ' + str(pct_same) + ' pct'
                    summary_dict[new_col].append({'match' : master_col, 'pct' : pct_same})
  return summary_dict

result = doComparison(new_master, new.columns, master.columns)
</code></pre>

<p>This way works but I would like to increase the efficiency and performance and don't exactly know how.</p>

<p>Consider the following adjustment that runs a list comprehension to build all combinations of both dataframe column names that is then iterated on to  <code>&gt; 90%</code> threshold matches.</p>

<pre><code># LIST COMPREHENSION (TUPLE PAIRS) LEAVES OUT CHARACTERISTIC (FIRST COL) AND SAME NAMED COLS
columnpairs = [(i,j) for i in new.columns[1:] for j in master.columns[1:] if i != j]

# DICTIONARY COMPREHENSION TO INITIALIZE DICT OBJ
summary_dict = {col:[] for col in new.columns[1:]}

for p in columnpairs:
    i, j = p

    is_same = (new['Characteristic'] == master['Characteristic']) &amp; \
              (new[i] == master[j]) &amp; (new[i] != '--') &amp; (master[j] != '--')
    pct_same = sum(is_same) * 100 / len(master)

    if pct_same &gt; 90:        
        summary_dict[i].append({'match' : j, 'pct': pct_same})

print(summary_dict)
# {'S4': [], 'S5': [{'match': 'S2', 'pct': 100.0}]}
</code></pre>

python pandas dataframe analytics data-science