-2

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
10
  • Sorry about it, it is a long file, I did not cut the same variants. I modified the question now. So AN1 is a continuous variable, which is not 0 or 1 but a random number. 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. – kllrdr Mar 8 at 20:09
  • 1
    now I see there is no difference of your this question with the one previously you were asked, can you say what is the differences? I mean answer is already given for that here unix.stackexchange.com/a/637766/72456 – αғsнιη Mar 8 at 20:10
  • The difference is that the value is not 0 or 1, it is a continuous variable. You gave me an answer to how to the case when only 0 or 1 could be the numbers. – kllrdr Mar 8 at 20:13
  • no, there's not, it gives you the value from file2 if exist else 0 if not exist – αғsнιη Mar 8 at 20:15
  • 1
    Your comment says 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. – Ed Morton Mar 8 at 20:15
2
$ awk 'NR==FNR{an1[$3]=$5; next} {print $0, an1[$3]}' file2 file1
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

If, per your comment under the question, you want to print 0 when the SNP values isn't in the array then:

awk 'NR==FNR{an1[$3]=$5; next} {print $0, ($3 in an1 ? an1[$3] : 0)}' file2 file1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.