CODEWITHSUNDEEP
unixawk
Lala
Lala

Reputation: 65

Merge two files based on two common columns, and replace the blank to 0

I really need your help to merge two files (with 3 columns) based on $1$2 and get all values of $3 and replace the empty/blank with 0. Thank very much in advance

file1:

chr1 1000001 135       
chr2 1000002 57
chr2 4400002 117    
chr6 1000003 172

file2:

chr1 1000001 377       
chr1 5500002 320       
chr6 1000003 432

Desired output:

chr1 1000001 135    377    
chr1 5500002  0    320     
chr2 1000002 57     0
chr2 4400002 117      0
chr6 1000003 172   432

I have tried

awk -F'\t' 'NR==FNR{A[$1$2]=$3; next} {A[$1$2]; B[$1$2]=$3} END{for (id in A) print id,A[id],B[id], "0"}' OFS='\t' File1 File2

Output

chr1 1000001 135       
chr1 1000001 377       
chr1 5500002 320       
chr2 1000002 57
chr2 4400002 117    
chr6 1000003 172
chr6 1000003 432

Upvotes: 0

Views: 158

Answers (1)

RavinderSingh13
RavinderSingh13

Reputation: 133700

Could you please try following, written and tested with shown samples only in GNU awk.

awk '
FNR==NR{
  a[$1 OFS $2]=$NF
  next
}
{
  if(($1 OFS $2) in a){
    d[$1 OFS $2]
    $(NF+1)=a[$1 OFS $2]
  }
  else{
    $(NF+1)=0
  }
  print
}
END{
  for(i in a){
    if(!(i in d)){
      print i,"0",a[i]
    }
  }
}
' Input_file2  Input_file1 | sort -k1

Output will be as follows.

chr1 1000001 135 377
chr1 5500002 0 320
chr2 1000002 57 0
chr2 4400002 117 0
chr6 1000003 172 432

Upvotes: 2

Related Questions