-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, 2021 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 Mar 8, 2021 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, 2021 at 20:13
  • no, there's not, it gives you the value from file2 if exist else 0 if not exist Mar 8, 2021 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, 2021 at 20:15

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .