CODEWITHSUNDEEP
unixjoinawkmerge
slarmer1
slarmer1

Reputation: 55

Join 2 files on 2 columns returning 0 for non-matches

I have 2 files, that are sorted, with 3 columns each. I need to merge them based on the first 2 columsn and return 4 columns, being the 2 that are matching and then the 3rd column from each file. I have tried to do in awk using 

awk 'NR==FNR{a[$1FS$2]=$3;NEXT}   
  {k=$1FS$2;print k,(a[k]?a[k]:a[k]*1),$3;delete a[k]}        
  END{for(k in a)print k,a[k],"0"}'

but it is not working how I hoped.

my input files look like this:

File 1:

Chr BPPos Chip_1

1 1234 1

1 1765 2

1 2043 3

2 1231 4

File 2:

Chr BPPos Chip_2

1 1765 1

1 2074 2

2 1231 3

2 2141 4

Desired Output:

Chr BPPos Chip_1 Chip_2

1 1234 1 0

1 1765 2 1

1 2043 3 0

1 2073 0 2

2 1231 4 3

2 2141 0 4

Any help would be hugely appreciated!

Steve

Upvotes: 1

Views: 60

Answers (2)

konsolebox
konsolebox

Reputation: 75568

awk '{ k = $1 FS $2 } !a[k]++ { keys[i++] = k } FNR == NR { b[k] = $3; next } { c[k] = $3 } END { for (i = 0; i in keys; ++i) { k = keys[i]; print k, k in b ? b[k] : "0", k in c ? c[k] : "0" } }' file1 file2

Output:

Chr BPPos Chip_1 Chip_2
1 1234 1 0
1 1765 2 1
1 2043 3 0
2 1231 4 3
1 2074 0 2
2 2141 0 4

Upvotes: 0

Kent
Kent

Reputation: 195219

try this one-liner:

awk '{k=$1 FS $2}
     NR==FNR{a[k]=$3;next}
     k in a{print $0,a[k];delete a[k];next}{print $0,"0"}
     END{for(x in a)print x,"0",a[x]}' file2 file1

with your data, output is:

kent$  awk '{k=$1 FS $2}NR==FNR{a[k]=$3;next}k in a{print $0,a[k];delete a[k];next}{print $0,"0"}END{for(x in a)print x,"0",a[x]}' f2 f1
Chr BPPos Chip_1 Chip_2
1 1234 1 0
1 1765 2 1
1 2043 3 0
2 1231 4 3
1 2074 0 2
2 2141 0 4

Upvotes: 2

Related Questions