Merging two files based on a common column; add 0 for variants not present in file 2, and keep the original value in those which are present

Question

I would like to merge two files, which share some common data. File 1 includes more than file 2. I want to merge the files based on their shared column (3) following the order of file 1 and I want to add 0 to column 5 (AN1) when the variable is not present in file 2, and if present, add the original value of AN1 (either 0 or 1).

My files look like this: File 1

CHR BP  SNP CM  base
20  61098   rs6078030   -0.00024510777  1
20  61795   rs4814683   0   1
20  63231   rs6076506   0.0005026053    1
20  63244   rs6139074   0.00050714752   1

File 2

CHR BP  SNP CM  AN1
20 9836704 rs221007 0 1
20 9817032 rs221011 0 0
20 9764069 rs2206484 0 0
20 9639395 rs4816159 0 1

I want to match them based on column 3 (SNP). I want to keep all the other columns for now.

My desired output would look like this (0 when rsX is not present, or when the original value of AN1 was 0):

File 3

CHR BP  SNP CM  base AN1
20  61098   rs6078030   -0.00024510777  1   1
20  61795   rs4814683   0   1   0
20  63231   rs6076506   0.0005026053    1   0
20  63244   rs6139074   0.00050714752   1   1

I need to modify this code according to the new conditions:

awk 'NR==FNR{ snp[$3]; next }
{ $6=($3 in snp)?(FNR==1?"AN1":"1"):"0" }1' file2 file1

Here I print 1 when rsX is present in file 1. I would like to print the original value of AN1 instead (0 or 1)

Answer 1

awk 'NR==FNR{ snp[$3]=$NF; next }
{ $6=($3 in snp)?(FNR==1?"AN1":snp[$3]):"0" }1' file2 file1

Things in awk:

NR: The total number of input records seen so far.
FNR: The input record number in the current input file and will reset to 1 for the next input file.

So condition NR==FNR will always true for the first input file only and the following block NR==FNR { ... } will execute when it's true and we do save last column value $NF into an awk array snp with column $3 as the keys.

next statement cause awk to skip executing the rest of the code and start over and if NF==FNR was still true it will repeat processing that block until all records/line of first input file read.

then in next block we add/update the value of column $6 with below conditions:

• if column#3 in file1 was set in snp array from file2 then do:
  • if it's first line, set its value to AN1
  • if not first line, set its value to the value read from the snp[$3] array.
• else set to 0.

the awk idom 1 used to print the result after all.