2

I would like to compare columns from file one to two. Where column 2 of file1 should un-match from column 1 or 2 from file2 and print output from file 1.

file1.

cat test.head20.R2.fastq.tab

@0_1_2367_1112_211  ENSG00000165837 GAAATTAAGTTATAATTTTCATGGGACATTTTCATCACTGTTGACACAGTTTCAAGCATTCCATCATGTTATTTTGACTCTTTTTCTTTTTTTTTTCTTT    +   @6@CDCFFEDEIJIIJJJFBFHIIJJJJJGC?CDDDDDDFEDGBFFFFHEDFFBBBDDDDDDDDBDDD@@@@CDDDDDEHHJJJGJIIIGIJJJIIIFCH
@10000000_0_0_0_0   rupesh  TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?DDECGGIEDDDDDDHHJJJJJJIGIIIJED?CB5@CFFHHHCFF
@10000001_0_3150_2465_134   ENSG00000137860 GCCTCTCAAGTAGCTGGGATTACAGGCACCTGCCACCACGCCCAGCCAATTTTTGTATTTTTAGTAGAGACAATTTCACTATGTTGGCCAGGCTGGTCTT    +   DEDDB>HJIGHFJJJIGFFFHJJJJJJJJIIGHHFFDDCCCIIJJJJJJJJJJJJJIGIJJJHJIFHHGJJIIHEEEDDDDDC>?@DDDEEEDFFFFFFC
@10000002_0_2947_952_158    ENSG00000028203 CCCCCAGGACCAGCTGCTGTTTTGTGATGACTGCGATCGGGGTTACCACATGTACTGCCTGAGTCCCCCCATGGCGGAGCCCCCGGAAGGGAGCTGGAGC    +   JFHHEDDB;;63JJJIJJJHHFIIJIHGHHHHGHHHJJIIIEEEIJJHHHJJHHFFJIJJJJJJJJJJJJJJHDDDDDDFGBB?8BDDDEDDDDDDDDCC
@10000003_0_8902_3193_186   ENSG00000177051 CAAGGCCAGAGAGACAAATAATGCCTCATGTCCCACTGCTTTAAAATTACATTAATTTATAAAATGGCCACTATGGGCTCTTTTTGACTGTTTCTCGGAG    +   HGEDDDDDDDDDDDCDDB@<DDDD<>CDDDDB@>DDDEIJJJIGJJIIIIGIJHHHGEEIJJJJJJJJIHJJJJJJIIJIIIJJIJJJJIIIIHEDDDCC
@5000345_0_3_0_0    ENSG00000178057 TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?

file2

cat fusions.head16.R2.fastq.tab

ENSG00000137860 ENSG00000165837 1431    1598    0:0:0   0:0:0   0/2 CAGGTCATCTGCTCCTATCTCCTAAGGCCCATGGTTTTCATGATGGGTGTAGAGTGGACAGACTGTCCAATGGTGGCTGAGATGGTGGGAATCAAGTTCT    +   IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
ENSG00000177051 ENSG00000134905 277 433 0:0:0   0:0:0   451/2   CTTCACTGCACAGCCAGGGTGAGCCTCGCTGGGAAGGTGCAGGTGACTCGTGCCTGTCGGGGAGCCCGTCCTGTCCGTACAAAACATGTGCCAGGCAAGG    +   IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
ENSG00000137860 ENSG00000165837 2761    2951    0:0:0   0:0:0   2/2 AAACAATCTTACGGATTAAGAGGAGACGTGAAGCTCAAAAGTTAACAGAGATGACCAGTTTCACATTTCATTTAATGAGCAAACCAACACCTGAGAAGCC    +   IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
ENSG00000028203 ENSG00000157766 183 411 0:0:0   0:0:0   101/2   TTCTTTGTCACCAAAAACAGAAAAATGCACAACAGAGGGACAACAAAAGCCTCCTACAAGAGTCCTACCAAAATACCTGGGATATAGTAATCACTCAATG    +   IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII

desired output:

@10000000_0_0_0_0   rupesh  TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?DDECGGIEDDDDDDHHJJJJJJIGIIIJED?CB5@CFFHHHCFF
@5000345_0_3_0_0    ENSG00000178057 TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?

Tried so far to print unmatched entry from second file but don't know how to print from file1 ?

awk '{k=$2} NR==FNR{a[k]; next} !(k in a)' test.head20.R2.fastq.tab fusions.head16.R2.fastq.tab

output from above code that i don't want:

ENSG00000177051 ENSG00000134905 277 433 0:0:0   0:0:0   451/2   CTTCACTGCACAGCCAGGGTGAGCCTCGCTGGGAAGGTGCAGGTGACTCGTGCCTGTCGGGGAGCCCGTCCTGTCCGTACAAAACATGTGCCAGGCAAGG    +   IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
ENSG00000028203 ENSG00000157766 183 411 0:0:0   0:0:0   101/2   TTCTTTGTCACCAAAAACAGAAAAATGCACAACAGAGGGACAACAAAAGCCTCCTACAAGAGTCCTACCAAAATACCTGGGATATAGTAATCACTCAATG    +   IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
2
  • 1
    You say you only want the entries that are not found in either column1 or 2 from file2, but your "desired output" includes the entries ENSG00000028203 and ENSG00000177051, which are found in the first column, on the 4th and 2nd rows respectively.
    – JigglyNaga
    Aug 28, 2019 at 16:18
  • Sorry, it was manual editing and i missed that ..I corrected the desired output.
    – RKK
    Aug 28, 2019 at 17:35

2 Answers 2

0
$ awk '
   NR==FNR {a[$1]++; a[$2]++; next};
   !($2 in a)' fusions.head16.R2.fastq.tab test.head20.R2.fastq.tab 
@10000000_0_0_0_0   rupesh  TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?DDECGGIEDDDDDDHHJJJJJJIGIIIJED?CB5@CFFHHHCFF
@5000345_0_3_0_0    ENSG00000178057 TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?

This was simpler and easier than I first thought, if you read the exclusions file (fusions.head16.R2.fastq.tab) before the data file (test.head20.R2.fastq.tab).

This reads in the first file and uses array a to store the identifiers found in fields $1 and $2.

Then, for each line of the second file (and subsequent files, if any), if field $2 is not in array a, it prints the line.

2
  • there is no need to store $1 in the array and incrementing is useless, just use NR==FNR{a[$1];next}
    – oliv
    Aug 29, 2019 at 7:01
  • @oliv I'm not storing $1 in an array. I'm using it as the key. I'm also using $2 as a key in the same array. Because the identifier could be in either or both of $1 & $2. I don't really care if you think incrementing the array element's value is useless - I think it's useful because it makes it explicit that an array element is being created, and is therefore useful for helping novice awk programmers to understand - my purpose here is to teach something useful to the OP, not to conform to your ideal of cleverness.
    – cas
    Aug 29, 2019 at 8:04
0

This solution works but requires a couple of steps and an intermediate file.

Step 1: Retrieve a list of IDs for which you want the record removed from file 1:

awk -F' ' '{print $1 "\n" $2}' fusions.head16.R2.fastq.tab > remove_list

Step 2: Retrieve entries from file 1 that do not contain the IDs from remove_list.

awk -F' ' 'NR==FNR{a[$1];next} !($2 in a)'  remove_list test.head20.R.fastq.tab

Output:

@10000000_0_0_0_0   rupesh  TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?DDECGGIEDDDDDDHHJJJJJJIGIIIJED?CB5@CFFHHHCFF
@5000345_0_3_0_0    ENSG00000178057 TCCCTACTCACGTGGTGGACGCACAACCTAAGGTCAAGCTTATAGGTAAACACGCAGTGAAATATCCAGAAACGAAGCTATCACCCGGGTAGTGTCTTGG    +   =FGIIIFDCCDDDCAA5BBBBGIJIIGJIJJJJJJIIGGHHIIIJIJIIJJIEE8?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .