Match ids and print original file

Shell Programming and Scripting
1

Hello,

                                       I have two files

Original: ( 5000 entries)
Chr Position
chr1 879108
chr1 881918
chr1 896874 ...

and a file with allele freq ( 2000 entries)
Chr Position MAF
chr1 881918 0.007
chr1 979748 0.007
chr1 1120377 0.007
chr1 1178925 0.036

I would like the original file matched with the allele freq and print out the output file with 5000 entries.
Chr Position MAF
chr1 879108 NULL
chr1 881918 0.007
chr1 896874 NULL
...

Any help is appreciated. Thank you.

2

what's the matching point between both files? your post doesn't clear the requirement..can you please mentioned something that is actually needed?

3

The common column with both the files is the "position" which is the second column.

4

if you are looking for something like matching between both files based on 2nd columne,

awk 'FNR==NR &&  NR>2 { a[$2]=$2; next } { if( $2 in a) { print  } }' original allelefreq
chr1 881918 0.007
5

Thank you but that only prints the positions which match with the original file.
The desired output is to print all 5000 entries from the original file whether or not it has a 3rd value.

Eg:
chr1 12345 0.07
chr1 6789 NULL
chr1 13456 0.78
.....
chr22 465546 0.12
chr22 6757657 NULL

6

reverse the filename order then

awk 'FNR==NR &&  NR>2 { a[$2]=$2; next } { if( $2 in a) { print  } }' allelefreq original

and let me know if this what you wanted

7

Well, this gives me exactly all the entries common with original and allele freq file without the MAF values
chr1 979748
chr1 1120377
chr1 1178925
chr1 1222958

8 
$ awk ' NR == FNR && NR > 1 { arr[$2]=$3; next } FNR>1{ printf("%s",$0); if(arr[$2] == ""){ print " NULL"}else{print " "arr[$2]} } ' freq orig
chr1 879108 NULL
chr1 881918 0.007
chr1 896874 NULL
9

Not sure that post#4 will satify the request. Try

$ awk   'FNR==NR        {a[$2] = $3; next}
                        {$0 = $0 FS (a[$2]?a[$2]:"NULL")}
         1
        ' file2 file1
Chr Position MAF
chr1 879108 NULL
chr1 881918 0.007
chr1 896874 NULL

If you don't need the "NULL" string, replace the conditional assignment with just a[$2].

Or, even shorter,

$ awk   'FNR==NR        {a[$2] = $3; next}
                        {print $0 FS (a[$2]?a[$2]:"NULL")}
        ' file2 file1
10

Thank you very much. Both the above codes work fine.