4

I am trying to compare two files using awk and I would like to print data from both files as output. The files I am comparing are as follows.

File1:

gene             feature id            fc         
a                gene                 MSTRG.1.1           
b                gene                 MSTRG.1.2   
c                gene                 MSTRG.2.1 
d                gene                 MSTRG.3.1   

File2:

MSTRG.1.1       ALLMI        
MSTRG.3.1       COTJA   
MSTRG.4.1       SORCY 

I have been using the following command:

$ awk -F '\t' 'BEGIN{OFS=FS} NR==FNR {a[$1]=$1; next} $3 in a {print $1}' File2 File1

I would like the output to be:

a ALLMI
c COTJA
d SORCY,

However, currently I am only getting the following as output:

a    
c    
d

Both files are tab delimited so I am not sure why my command isn't working?

  • 1
    Are they tab-delimited or tab-aligned? If you have more than one tab between your data columns and you set FS to "\t", your field numbers will be off. Instead, try not setting FS; awk's default field separator is "a glob of whitespace" rather than "a single tab". – DopeGhoti Aug 2 '18 at 21:29
8

awk solution

How about this. Doesn't give the exact output you offer, but I'm unsure why d SORCY, would printed, as d is MSTRG3.1, which is COTJA.

Anyway, here goes. Starter-for-ten. Works fine on GNU Awk v4.0.2.

$ awk 'NR==FNR{a[$1]=$2}NR!=FNR&&FNR>1&&a[$3]{print $1,a[$3]}' file2 file1
a ALLMI
d COTJA
$

If NR is same as FNR, we're on the first file, so populate the array.

If NR isn't the same as FNR, we're on the 2nd file, so once we're past the first record of this file (header), and if field 3 exists in the array, print it.

"golfed" awk solution

Less readable, but shorter code.

awk 'NR==FNR{a[$1]=$2}a[$3]{print$1,a[$3]}' file{2,1}

join solution

Alternatively, if you're not particular about needing it achieved using awk, just use join.

$ join -1 3 -2 1 -o "1.1 2.2" file1 file2
a ALLMI
d COTJA
$

Join the files using field 3 from file 1 (-1 3), and field 1 from file 2 (-2 1). And then print field 1 from file1, and field2 from file2. Bingo.

  • 1
    d SORCY was an inital typo. What I actually wanted was d COTJA, which your code produced perfectly. I hadn't used join because I started the problem with awk and wanted to finish the problem with awk as a challenge to myself. – Josh Aug 3 '18 at 0:29
3

Try this,

 awk  'BEGIN{OFS=FS} NR==FNR {a[$1]=$1; next} $3 in a {print $1"\t"$3}'  file2 file1
  • we don't need \t as a delimiter.
  • we need to print first and third field as your requirement.
3

Your code,

awk -F '\t' 'BEGIN{OFS=FS} NR==FNR {a[$1]=$1; next} $3 in a {print $1}' File2 File1

could never output two columns as the print at the end only outputs the first column from File1.

You're almost there though. You need to make one tiny adjustment, which is to actually output the missing field:

awk -F '\t' 'BEGIN{OFS=FS} NR==FNR {a[$1]=$1; next} $3 in a {print $3, $1}' File2 File1
                                                                   ^^^
                                                                 add this

Running this on your data should produce

MSTRG.1.1 a
MSTRG.3.1 d

For large datasets, see steve's solution which is more memory efficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.