title: Searching millions of strings between two textfile for exact matches (all-vs-all)
body: <p>I have two text files. Both contain hundreds of millions of lines (rows). The second one is about four times larger.</p>
  <p>Both text files have two columns each. The first one is an <code>ID</code> (key), the second one is the <code>Value</code> string which has to be compared between both files.</p>
  <p>EDIT2: There might be duplicates in <code>Value</code> for both files.</p>
  <p>Structure of both text files:</p>
  <pre><code>ID                      Value
  B00CC0:2610:20880:13730 cd99AABABBABABABABABABABABA
  B00CC0:2549:10230:33301 cd99BABABBABBBABBBBBBAAABBB
  B00CC0:1272:8504:27179  cd99BBBBBBBBAAAAAAAAABBBBBB
  B00CC0:1556:10628:35055 cd99AAAABBBBABABAAAAAAAAAAB
  ...                     ...
  </code></pre>
  <p>Now I want to output every line in the second file which contains a <code>Value</code> occurring in the first file (exact match, not a substring!).</p>
  <p>I was trying a naive implementation in in Python via just loading both files to dataframes and then perform filtering:</p>
  <pre><code>import sys
  import modin.pandas as pd
  
  import ray
  ray.init()
  
  # load 1st file
  data_one = pd.read_csv(filename1, compression='gzip', header=0, sep='\t', usecols=[1], names=['Value'])
  data_one_list = data_tso['Value'].tolist()
  
  # load 2nd file
  data_two = pd.read_csv(filename2, compression='gzip', header=0, sep='\t', usecols=[0,1], names=['ID','alue'])
  
  # filter
  data_two_filtered = data_two[data_two['Value'].isin(data_one_list)]
  </code></pre>
  <p>However, this works only if I subset the first file otherwise it is too big and the Python script crashes (Eating up all the RAM). And it is too slow anyway. I was trying to use <code>modin.pandas</code> to speed up the entire process, but does not solve my problem.</p>
  <p>Now I have questions going into two directions:</p>
  <p>First direction:</p>
  <ol>
  <li>Do you think it is possible to develop a solution with &quot;decent&quot; performance in Python? Or do you think C/C++ is needed (mentioning C/C++ since those are the only compiled languages I master at least enough to solve this problem)?</li>
  </ol>
  <p>Second direction:</p>
  <ol>
  <li>Do you think I have to use an approach such as a hash table or a trie for lookup or do you think a simple table lookup as tested is sufficient if done correctly?</li>
  <li>If you suggest a specific approach, what would it be (data structure, approach)?</li>
  </ol>
  <p>EDIT:</p>
  <ol>
  <li>I have a machine with 256 GB RAM and 64 threads.</li>
  <li>A decent speed would be to have this filtering performed within about 1-2 minutes max.</li>
  </ol>