compare columns from two different files and PRINT RECORDS FROM FIRST FILE those that DO NOT match from second file

Question

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

Answer 1

$ 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.

Answer 2

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?