title: Merging large TSV files on select columns
body: <p>Is there a memory-efficient way to merge many large TSV files, only keeping select columns of each TSV file?</p>
  <p>Background:</p>
  <p>I have many  (~100) large TSV files (~800MB) of the form:</p>
  <ul>
  <li>filename: 1.txt</li>
  </ul>
  <p>ID    Char1    Char2     ...      P  <br>
  1     Green       Red          .      0.1 <br>
  2       Red      Orange     .      0.2 <br>
  3    Purple     Blue        .    0.13 <br>
  (Format A)</p>
  <p>The ID column is the same between all TSV files. I only care about the ID and P columns for each and would like to condense them into the following form to make them take up less memory (column names are pulled from the Format A file name):</p>
  <p>ID     1        2        ...        N <br>
  1     P_11   P_12      .       P_1N <br>
  2    P_21  P_22     .       P_2N <br>
  3    P_31  P_32     .       P_3N <br>
  (Format B)</p>
  <p>I need to do this in a memory-efficient manner as I will be processing as many of these blocks of 100 files in parallel. Without parallelization, the runtime would be extremely long as there are 170 blocks of 100 files to process. My workflow is as follows:</p>
  <ul>
  <li>Step 1: Generate M groups of these 100 files in format A</li>
  <li>Step 2: Process these 100 files into the one file of format B</li>
  <li>Step 3: Repeat the above two steps until all 170 groups of 100 files are processed</li>
  </ul>
  <p>I've tried many different approaches, and none is both time and memory efficient.</p>
  <p>My first approach involved storing everything in memory. I kept one file's ID and P columns in a list of lists. Then, I iterated through every other file and appended its P value to the end of the appropriate array. This approach had a satisfactory runtime (~30 minutes per block of 100 files). But, this failed as my VM does not have the memory to parallelize storing all of this data at once.</p>
  <p>I've gone through a number of iterations over the last few days. In my current iteration, I use one file and only keep its ID and P column. Then, I loop through each file and use fileinput to append the file's P column in place to the end of line. After testing, this method passes on the memory/parallelization side but too slow in terms of runtime. I've also thought of using pandas to merge, but this runs into both a time and memory constraint.</p>
  <p>I have scoured every corner of my brain and the internet, but have not produced a satisfactory solution.</p>