title: Subset a file by row and column numbers
body: <p>We want to subset a text file on rows and columns, where rows and columns numbers are read from a file. Excluding header (row 1) and rownames (col 1).</p>
  
  <p><strong>inputFile.txt</strong> Tab delimited text file    </p>
  
  <pre><code>header  62  9   3   54  6   1
  25  1   2   3   4   5   6
  96  1   1   1   1   0   1
  72  3   3   3   3   3   3
  18  0   1   0   1   1   0
  82  1   0   0   0   0   1
  77  1   0   1   0   1   1
  15  7   7   7   7   7   7
  82  0   0   1   1   1   0
  37  0   1   0   0   1   0
  18  0   1   0   0   1   0
  53  0   0   1   0   0   0
  57  1   1   1   1   1   1
  </code></pre>
  
  <p><strong>subsetCols.txt</strong> Comma separated with no spaces, one row, numbers ordered. In real data we have 500K columns, and need to subset ~10K.</p>
  
  <pre><code>1,4,6
  </code></pre>
  
  <p><strong>subsetRows.txt</strong> Comma separated with no spaces, one row, numbers ordered. In real data we have 20K rows, and need to subset about ~300.   </p>
  
  <pre><code>1,3,7
  </code></pre>
  
  <p>Current solution using <em>cut</em> and <em>awk</em> loop (<a href="https://stackoverflow.com/q/27509898">Related post: Select rows using awk</a>):     </p>
  
  <pre><code># define vars
  fileInput=inputFile.txt
  fileRows=subsetRows.txt
  fileCols=subsetCols.txt
  fileOutput=result.txt
  
  # cut columns and awk rows
  cut -f2- $fileInput | cut -f`cat $fileCols` | sed '1d' | awk -v s=`cat $fileRows` 'BEGIN{split(s, a, ","); for (i in a) b[a[i]]} NR in b' &gt; $fileOutput
  </code></pre>
  
  <p><strong>Output file: result.txt</strong>     </p>
  
  <pre><code>1   4   6
  3   3   3
  7   7   7
  </code></pre>
  
  <p><strong>Question:</strong><br>
  This solution works fine for small files, for bigger files 50K rows and 200K columns, it is taking too long, 15 minutes plus, still running. I think <strong>cut</strong>ting the columns works fine, selecting rows is the slow bit.</p>
  
  <p>Any better way?</p>
  
  <p><strong>Real input files info:</strong></p>
  
  <pre><code># $fileInput:
  #        Rows = 20127
  #        Cols = 533633
  #        Size = 31 GB
  # $fileCols: 12000 comma separated col numbers
  # $fileRows: 300 comma separated row numbers
  </code></pre>
  
  <p>More information about the file: file contains <a href="https://en.wikipedia.org/wiki/Genome-wide_association_study" rel="nofollow noreferrer">GWAS</a> genotype data. Every row represents sample (individual) and every column represents <a href="https://en.wikipedia.org/wiki/Single-nucleotide_polymorphism" rel="nofollow noreferrer">SNP</a>. For further region based analysis we need to subset samples(rows) and SNPs(columns), to make the data more manageable (small) as an input for other statistical softwares like <a href="/questions/tagged/r" class="post-tag" title="show questions tagged &#39;r&#39;" rel="tag">r</a>.</p>
  
  <p><strong>System:</strong></p>
  
  <pre><code>$ uname -a
  Linux nYYY-XXXX ZZZ Tue Dec 18 17:22:54 CST 2012 x86_64 x86_64 x86_64 GNU/Linux
  </code></pre>
  
  <hr>
  
  <p><strong>Update:</strong> Solution provided below by <a href="https://stackoverflow.com/a/40844004">@JamesBrown</a> was mixing the orders of columns in my system, as I am using different version of awk, my version is: <code>GNU Awk 3.1.7</code></p>