Deconstructing one line into two lines based on specific columns

Question

I have a .tsv file (batch_1.catalog.tags.tsv) consisting of 1,965,056 lines of 14 columns. I want to break some of these into two lines.

The first line: starts with a greater than sign (>) followed by 8 of the 14 columns
The second line: only column 10

Eg.

>column3(a number) column4(numbers and letters) column5(a number) column6(- or +) column11(0 or 1) column12(0 or 1) column13(0 or 1) column14(0 or 1)       
column10(string with As,Ts,Gs,Cs, and sometimes Ns)    

Here is an example of the sixth line of the .tsv file, as specified by the third column:

0   1   6   gi|586799556|ref|NW_006530744.1|    141 +   consensus   0   1_33,14_43  CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC    0   0   0   0    

This is what I would like:

>6 gi|586799556|ref|NW_006530744.1| 141 +  0 0 0 0        
CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC    

However, I only want to do this to lines in the tsv file (batch_1.catalog.tags.tsv) that have a third-column number that matches the numbers in a different text file (whitelist.txt).

In the above example, the whitelist.txt file would contain the number 6, although there are 8000+ more lines with different third-column numbers (i.e. IDs). The whitelist.txt includes numbers of up to 6 digits.

I have been trying an alternative approach. I was given the code below for using the whitelist to pull out column 10 from the .tsv file. However, grep went on for 10 hours and didn't do anything (empty cat.fa file).

cat whitelist.txt | while read line; do zgrep "^0    1       $line   " batch_1.catalog.tags.tsv.gz; done | cut -f 3,10 | sed -E -e's/^([0-9]+)       ([ACGTN]+)$/>\1Z\2/' | tr "Z" "\n" > cat.fa    

Both solutions below using awk or perl work perfectly. The IDs are also printed out in order although they were not in order in the whitelist. The perl solution prints the lines tab-delimited while awk prints them space-delimited.

Answer 1

perl -F'\t+' -lane '
   @ARGV and $h{$F[0]}++,next;
   print ">", join("\t", @F[2..5,-4..-1]), $\, $F[9] if exists $h{$F[2]};
' whitelist.txt batch_1.catalog.tags.tsv

Assuming your file is TAB-separated.

Note that if your file might have windows or mac line endings then it is prudent to first convert them to unix line endings ("\n") via the utilities dos2unix, etc. Coz many a time it's been seen that the code provided does not work at OP's end due to reasons like these.

Workings

• Note when Perl is processing the first argument (in this case, whitelight.txt, then @ARGV holds batch_1.catalog.tsv file, i.e., @ARGV = 1 => @ARGV is evaluated as TRUE in boolean context.
• @ARGV and $h{$F[0]}++,next is to be interpreted as: when we are processing the whitelight file, then, add the first field ($F[0]) of this file to the hash %h then immediately go to the next line.
• Any lines below these, will process the TSV file, as at that time @ARGV holds nothing, so count is zero.
• Only those TSV file records shall goto stdout that have their 3rd field $F[2] happen to be a key in the %h hash.
• Once the decision has been taken to print a TSV record, then the format for its printing is: (Note: The default OFS for a print is NULL)
• ">", $F[2] meaning 3rd field is preceded by a >
• fields 4,5,6 => @F[3..5] will be TAB separated and joined.
• last 4 fields => @F[-4..-1] will be TAB separated and joined.
• The 10th field $F[9] will be preceded by a newline, which is provided by the $\ = ORS = \n due to the Perl option -l.

Answer 2

awk solution:

Assuming a test fragment from batch_1.catalog.tags.tsv file:

0   1   6   gi|586799556|ref|NW_006530744.1|    141 +   consensus   0   1_33,14_43  CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC    0   0   0   0
1   2   7   hi|686711556|ref|NW_006530744.2|    141 +   consensus   0   1_33,14_43  CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC    1   1   0   1
2   2   8   hi|686711556|ref|NW_006530744.2|    141 +   consensus   0   1_33,14_43  CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC    1   1   1   1
3   3   9   th|776711556|ref|NW_006530744.2|    141 +   consensus   1   1_33,14_43  CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC    1   0   1   1

And a test fragment from whitelight.txt file:

6
7
9

The command:

awk 'NR==FNR{ a[$0]++; next }{ if ($3 in a) { 
     $0=">"$3 FS $4 FS $5 FS $6 FS $11 FS $12 FS $13 FS $14 RS $10; print}}' whitelist.txt batch_1.catalog.tags.tsv > cat.fa

Final cat.fa contents:

>6 gi|586799556|ref|NW_006530744.1| 141 + 0 0 0 0
CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC
>7 hi|686711556|ref|NW_006530744.2| 141 + 1 1 0 1
CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC
>9 th|776711556|ref|NW_006530744.2| 141 + 1 0 1 1
CGGGCGGTGGTGGCGCACGCCTTTAATCCCAGCACTTGGGAGGCAGAGGCAGGTGGATCTTTGTGAGTTCGAGGCCAGCCTGGGCTACCAAGTGAGCTCC

---

Details:

NR==FNR - perform action for the first file i.e. whitelight.txt

a[$0]++; - accumulating numbers from the whitelight.txt file

if ($3 in a) - allows action if a 3rd column value from the 2nd file matches any of the accumulated numbers

RS - awk's record separator, defaults to the newline character