title: Pandas dataframe merge doesn't perform better than appending to a new list
body: <p>Basically I have two lists and I have to check each value's existance in both lists</p>
  <pre><code>list A = [1,2,3,4]
  list B= [3,4,5]
  
  ## just an example. The result would be convert into csv
  result = [
  [1, true, false],
  [2, true, false],   ## 2 only exist in the first list
  [3, true, true],    ## 3 exist in both lists
  [4, true, true],
  [5, false, true]
  ]
  </code></pre>
  <p>I was using list and set to appending each row into a list. As <a href="https://stackoverflow.com/questions/42847053/pandas-dataframe-check-intersection-and-fill-in-a-new-dataframe">this question</a> suggests, I used Dataframe merge to test the performance.</p>
  <p>Amazingly, the dataframe <code>merge</code> with <code>indication</code> method doesn't outperform the very stupid list solution.</p>
  <p>The two solutions are here</p>
  <p>Stupid one: should be a O(n) solution since check in set is O(1)</p>
  <pre><code>def FindDifferences():    
      df1 = pd.read_csv('Gmax_v6_annotation_info.txt', names=['name'], usecols=[0], delimiter='\t')
      df2 = pd.read_csv('Gmax_v9_annotation_info.txt', names=['name'], usecols=[2], delimiter='\t')
      v6_set = set(df1['name'])
      v9_set = set(df2['name'])
      result = []
      for val in v6_set:
          if val in v9_set:
              result.append([val, True, True])
          else:
              result.append([val, True, False])
      for val in v9_set:
          if val not in v6_set:
              result.append([val, False, True])
      result_df = pd.DataFrame(result, columns=['name', 'inv6', 'inv9'])
      result_df.to_csv('result_csv.csv', index=False, header=False)
      return
  </code></pre>
  <p>Dataframe one, credit to Psidom</p>
  <pre><code>In [13]: def pandas_solution():
      ...:     df1 = pd.read_csv('Gmax_v6_annotation_info.txt', names=['name'], usecols=[0], delimiter='\t')
      ...:     df2 = pd.read_csv('Gmax_v9_annotation_info.txt', names=['name'], usecols=[2], delimiter='\t')
      ...:     df1.merge(df2, how='outer', indicator=True)
                  .assign(inv6 = lambda x:x._merge != &quot;right_only&quot;, inv9 = lambda x:x._merge != &quot;left_only&quot;)
                  .drop(&quot;_merge&quot;, 1).to_csv('resultcsv.csv')
  </code></pre>
  <p>Result</p>
  <pre><code>In [7]: %timeit FindDifferences()
  1 loop, best of 3: 386 ms per loop
  
  In [16]: %timeit pandas_solution()
  1 loop, best of 3: 389 ms per loop
  </code></pre>
  <p><a href="https://github.com/shaoqin2/AlgorithmSpeedTests/tree/master/check_existance_in_two_lists" rel="nofollow noreferrer">https://github.com/shaoqin2/AlgorithmSpeedTests/tree/master/check_existance_in_two_lists</a></p>
  <h3>Question</h3>
  <p>I would like to know what happened under the hood and what's the takeaway from this behavior. the merge solution intuitively seems so much better.</p>