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

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)

  • How large are the files, and how are the fields separated (space or tab)? – AdminBee Mar 5 at 15:26
  • File 1 is like 100 MB and file 2 is around 10 MB. The fields are tab-separated. The above mentioned code works perfectly, but I don't know how to change that condition to what I want to do. – kllrdr Mar 5 at 15:28
  • Are you sure that with your current approach File3 is also tab-separated? My guess is that it should now be space-separated ... – AdminBee Mar 5 at 15:31
  • You are right, file 3 is space-separated, unlike files 1 and 2. – kllrdr Mar 5 at 15:34
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.


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.