Cfrtjryk

I need to combine two files into a single file with all columns from both files.

I am providing my example files.
File 1

chr loc T1  C1

chr1    100 2   3

chr1    200 3   4

chr2    100 1   4

chr2    400 3   1

File 2

chr loc T2  C2

chr1    100 1   2

chr1    300 4   1

chr2    100 7   5

chr2    500 1   9

and output file should be like this

output file

chr loc T1  C1  T2  C2

chr1    100 2   3   1   2

chr1    200 3   4   0   0

chr1    300 0   0   4   1

chr2    100 1   4   7   5

chr2    400 3   1   0   0

chr2    500 0   0   1   9

join -a1 -a2 -e 0 -o 0,1.2,1.3,2.2,2.3 

    <(sed 's/ +/_/' file1 | sort) 

    <(sed 's/ +/_/' file2 | sort) | 

sed 's/_/ /' | 

column  -t | 

sort

chr   loc  T1  C1  T2  C2

chr1  100  2   3   1   2

chr1  200  3   4   0   0

chr1  300  0   0   4   1

chr2  100  1   4   7   5

chr2  400  3   1   0   0

chr2  500  0   0   1   9

The trickiest part here are the reasons for sed -- join will only join on a single field, and here the join criteria is the first 2 fields. So, we have to combine those fields into a single word: I replace the first sequence of whitespace with an underscore so join will see chr1_100, chr1_200, etc.

join requires its input files to be sorted.

I use process substitution so that join can work with the sed|sort pipelines like files.

Then another sed call to undo the combined field, and then column to make it pretty.

By default, join uses the first field of each file as the key field.

By default, join does an inner join: only keys present in both files are printed. The -a1 and -a2 option enable the full outer join we want. The -e option provides the default value for null fields, and we need the -o option to specify that we want all the fields.

Can also use awk:

awk '

    {key = $1 OFS $2} 

    NR == FNR {f1[key] = $3; f2[key] = $4; next} 

    !(key in f1) {print $1, $2, 0, 0, $3, $4; next} 

    {print key, f1[key], f2[key], $3, $4; delete f1[key]} 

    END {for (key in f1) print key, f1[key], f2[key], 0, 0}

' file1 file2 | sort

chr loc T1 C1 T2 C2

chr1 100 2 3 1 2

chr1 200 3 4 0 0

chr1 300 0 0 4 1

chr2 100 1 4 7 5

chr2 400 3 1 0 0

chr2 500 0 0 1 9