left join using awk

Shell Programming and Scripting
1

Hi guys,

I need AWK to merge the following 2 files:

file1

1 a 1 1
2 b 2 2
3 c 3 3
4 d 4 4

file2

a a/a c/c a/c c/c a/a c/t
c c/t c/c a/t g/g c/c c/t

desired output:

1 a 1 1 a/a c/c a/c c/c a/a c/t
2 b 2 2 x x x x x x
3 c 3 3 c/t c/c a/t g/g c/c c/t
4 d 4 4 x x x x x x

The awk command should merge the files and put many "x" as the number of columns - 1 from file 2

Thanks!!

2 
awk 'NR == FNR {
  r = null
  for (i = 1; ++i <= NF;) 
    r = r ? r OFS $i : $i
  f2[$1] = r; nf = NF; next
  }
FNR == 1 {
  for (i = 1; ++i <= nf;) 
    x = x ? x OFS c : c
  }
{ 
  print $0, $2 in f2 ? f2[$2] : x 
  }' c=x file2 file1

If you want to be able to use an arbitrary OFS:

awk 'NR == FNR {
  r = null
  for (i = 1; ++i <= NF;) 
    r = r ? r OFS $i : $i
  f2[$1] = r; nf = NF; next
  }
FNR == 1 {
  for (i = 1; ++i <= nf;) 
    x = x ? x OFS c : c
  }
{ 
  $1 = $1
  print $0, $2 in f2 ? f2[$2] : x 
  }' OFS=, c=x file2 file1
3

If the files are already sorted on the join field, and if you don't actually have an awk restriction (but merely mentioned it because you thought it was the best tool for the task):

join -1 2 -2 1 -a 1 -e x -o 1.1,1.2,1.3,1.4,2.2,2.3,2.4,2.5,2.6,2.7 file1 file2

Regards,
Alister