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)