AWK: Comparing two columns from two different files

Hi - I have two files as follows:

File 1:

chr5    118464905    118465027    ENST00000514151    utr5    0    +
chr5    118464903    118465118    ENST00000504031    utr5    0    +
chr5    118468826    118469180    ENST00000504031    utr5    0    +
chr5    118469920    118470084    ENST00000512281    utr5    0    +
chr5    118479535    118479625    ENST00000512281    utr5    0    +
chr5    118480230    118480333    ENST00000512281    utr5    0    +

File 2:

ENST00000512281    Xkr4
ENST00000504031    Nku23
ENST00000776348    KKM2

I want to compare the field 1 of file 2 to field 4 of file1 and add the corresponding field 2 of file 2 to the line on file 1.

Example output

chr5    118464903    118465118    ENST00000504031    utr5    0    +   Nku23
chr5    118468826    118469180    ENST00000504031    utr5    0    +   Nku23
chr5    118469920    118470084    ENST00000512281    utr5    0    +   Xkr4
chr5    118479535    118479625    ENST00000512281    utr5    0    +   Xkr4
chr5    118480230    118480333    ENST00000512281    utr5    0    +   Xkr4

I tried using NR==FNR based code, but did not work.

THanks a lot in advance

awk 'NR==FNR{a[$1]=$2;next}a[$4]{print $0"\t"a[$4]}' file2 file1

awk 'NR==FNR{a[$1]=$2;next}a[$4]{$0=$0"\t"a[$4]}1' file2 file1 

danmero - You are god!

Be welcome but please learn to use [code] tags when you post code or data sample.

PS. I update my one-liner to fit your required output.

Sure - I will, THanks again

while read key value
do
  sed -i "/${key}/ s/$/ ${value}/" file1
done < file2

awk 'NF==8' file1

nawk 'NR==FNR { 
_[$1]=$2 
}
NR!=FNR {
  if(_[$4] != "")
    print $0"  "_[$4]
}' f2 f1

So no perl any more?

Be nice &

hi polsum,
Can i know ur this code write in which shell script?

awk 'NR==FNR{a[$1]=$2;next}a[$4]{print $0"\t"a[$4]}' file2 file1

---------- Post updated at 10:33 AM ---------- Previous update was at 10:13 AM ----------

hi danmero,
can u explain about ur code :
wht is $1 ,$2 and $4?

awk 'NR==FNR{a[$1]=$2;next}a[$4]{print $0"\t"a[$4]}' file2 file1

$ ruby -ane 'BEGIN{h={};File.readlines("file1").each{|x|k,v=x.split;h[k]=v.chomp} };puts "#{$_.chomp} #{h[$F[3]]}" if h[$F[3]] ;END{}' file1

deleted as I have moved it to a new post

CSN

Hi cs_novice,

Please open new post, don't post your problem in between another post.

Try this one:

awk 'NR==FNR{a[$1]=$2;next}a[$1]{print $0"\t"a[$1]}' Freq.txt Pval.txt

A solution for cs_novice problem using shell(bash) only
while read a b;do while read c d;do if [[ $a = $c ]]; then printf "%s\t%s\t%s\n" $a $b $d;fi;done < Freq.txt ;done < Pval.txt

Hi Pravin27
I have asked the same query in a new post. danmero's awk and shell scripts are not giving me the right output. Please check the new post.

thanks
CSN