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 (which is a continuous variable).
My files look like this: File 1
CHR BP SNP CM base
10 99969507 rs35122894 0 0.23
10 99966455 rs17451447 0 1
10 99964768 rs72826281 0 0.43
10 9996313 rs111848993 0 0.01
File 2
CHR BP SNP CM AN1
10 99969507 rs35122894 0 1.000000000000000
10 99966455 rs17451447 0 0.678999997353345
10 99964768 rs72826281 0 0.876534244415788
10 9996313 rs111848993 0 0.003456777789882
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: File 3
CHR BP SNP CM base AN1
10 99969507 rs35122894 0 0.23 1.000000000000000
10 99966455 rs17451447 0 1 0.678999997353345
10 99964768 rs72826281 0 0.43 0.876534244415788
10 9996313 rs111848993 0 0.01 0.003456777789882
I tried to do it with awk:
awk 'NR==FNR{ snp[$3]; next }
{ $6=($3 in snp)?(FNR==?"AN1[$5]):"0" }1' file2 file1 > file3
awk 'NR==FNR{a[$3]=$5;next} {print $1,$2,a[$3],$4,$5,$6}' File2 File1 > file3
If the variant is present in file 2, I would like to print its value, so the random number. If it does not, I want to print 0
- is that use case covered by your posted sample input/output? If not then please add it to the example and add that text to your question, don't spread your requirements out across comments, put them all in your question.