How to combine columns of two files, remove duplicates, and fill in missing lines

Question

I have two separate files that I would like to combine. Each have 7 columns as such:

1 10 6 0.02 A 45 0.17
1 15 6 0.03 A 67 0.17
2 33 6 0.10 A 23 0.17 

and

1 10 6 0.04 B 87 0.17
1 12 6 0.12 B 93 0.17
2 33 6 0.08 B 45 0.17

I would like to combine column 1 and 2 of each files while removing duplicates and then add column 4 5 6 from each file to the corresponding combination of column 1 and 2. Then if columns 1 and 2 don't match I would like them to be printed however for the file lacking for it to put 0 instead of column 4 5 and 6 such that the final file would be

1 10 0.02 A 45 0.04 B 87
1 12 0    0 0  0.12 B 93
1 15 0.03 A 67 0    0 0
2 33 0.10 A 23 0.08 B 45

Answer 1

A straightforward way to do this is to use a 2d array in awk

FNR==NR {a[$1FS$2][0]=$4FS$5FS$6; next}
        {a[$1FS$2][1]=$4FS$5FS$6}
END {
    empty="0"FS"0"FS"0"
    for(i in a)
        print i FS (a[i][0]? a[i][0] : empty) FS (a[i][1]? a[i][1] : empty)
}

You can save the script as a file and execute

awk -f main.awk file1 file2

Explaination

• The first line FNR==NR scans file1 and stores the desired columns $4 $5 $6 with the associated key $1 $2 in an array a[$1FS$2][0]
• The second line basically does the same thing on file2 but stores the value into the array a[$1FS$2][1], notice that these array assigning operations will automatically remove duplicates since arrays in awk are associative, meaning that each key can appear only once
• On the final step inside END, print out each row and substitute each empty values into a predefined string 0 0 0
• If you want the output to be sorted say by an ascending index value order, add a PROCINFO["sorted_in"] = "@ind_str_asc" to the first line inside END

If you have time, it would be quite helpful to go through the well-documented Gawk Programming Guide