Bash: join 2 files

Hello !

I want to join 2 files.
They look like this:

KE340296.1    1    0/0:11
KE340296.1    2    0/0:12
KE340296.1    6    0/1:13
KE340297.1    1    0/1:14
KE340297.1    3    0/1:15
KE340297.1    4    0/1:16

and

KE340296.1    1    0/0:21
KE340296.1    2    0/0:22
KE340296.1    3    0/1:23
KE340297.1    1    0/1:24
KE340297.1    2    0/1:25
KE340297.1    3    0/1:26

I would like to obtain this: Columns 1 and 2 are the two first columns of the two files. Column 3 corresponds to file1:col3 and column 4 to file2:col3.

KE340296.1    1    0/0:11   0/0:21
KE340296.1    2    0/0:12   0/0:22
KE340296.1    3    .        0/1:23
KE340296.1    6    0/1:13   .
KE340297.1    1    0/1:14   0/1:24
KE340297.1    2    .        0/1:25
KE340297.1    3    0/1:15   0/1:26
KE340297.1    4    0/1:16   .

Let me explain:
These two files are sorted by first the colum 1 where I have this names KE... and then by the column 2 (position).
The tricky thing is that for some position, I don't have a value in one of the file. For instance in KE340296.1, position 4, I have only a value in the first file.
The file I want to obtain contains all the positions represented in the two files (there may be hole, as for KE340296.1, no position 4 and 5 in either of the two files) and if there is no value for one of the file, then I would like a dot or whatever character.

I wanted to use "join" but it seems that it won't work because positions are not perfectly matching.

Any one knows how I can do that ?

Thanks a lot !

Mu

I would suggest using awk. Here is an awk program with sort

awk '
        NR == FNR {
                A[$1 FS $2] = $NF
                next
        }
        {
                B[$1 FS $2] = $NF
        }
        END {
                for ( k in A )
                {
                        if ( k in B )
                                print k, A[k], B[k]
                        else
                                print k, A[k], "."
                }
                for ( k in B )
                {
                        if ( k in A )
                                print k, A[k], B[k]
                        else
                                print k, ".", B[k]
                }
        }
' OFS='\t' file1 file2 | sort -u

Replace file1 and file2 with your original input file names

Hello,
Another awk solution:

awk '{X=$1 OFS $2}; FNR == NR {A[X]=$3 OFS ".";next};{if( X in A) { gsub(".$",$3,A[X]);next} else {A[X]="." OFS $3}};END{for (i in A) print i, A}' OFS='\t' file1 file2 | sort

Regards.

yaa*:

awk '
  {
    i=$1 OFS $2
    A=NR==FNR ? $3 " . " : A " . " $3
  }
  END{
    for(i in A) {
      $0=A
      print i, $1, $NF
    }
  }
' OFS='\t' file1 file2 | sort

One line:

awk '{i=$1 OFS $2; A=NR==FNR?$3 " . ":A " . " $3} END{for(i in A) {$0=A; print i, $1, $NF}}' OFS='\t' file1 file2 | sort

-- edit --
May be easier to follow:

awk '
  {
    C
    if(NR==FNR) A=$3; else B=$3
  } 
  END{
    for(i in C) print i, i in A ? A : "." , i in B ? B : "."
  }
' OFS='\t' file1 file2 | sort

awk '{C; if(NR==FNR)A=$3; else B=$3} END{for(i in C) {print i,i in A?A:".", i in B?B:"."}}' OFS='\t' file1 file2 | sort

---

• yaa=yet another awk

OK thanks a lot for your responses !

I didn't know NR, FNR and OFS so I learnt a lot here !

I picked the last solution as it seems the shortest. Though, this is not the simplest to understand

Is it possible to have an explanation of the different steps ?
Thanks a lot !