2

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
2

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

  • When I try to use the script suggested i get the error awk: main.awk: line 2: syntax error at or near [ awk: main.awk: line 4: syntax error at or near [ awk: main.awk: line 6: syntax error at or near [ is there something wrong with use [ – Dylan Girodat Dec 7 '17 at 17:18
  • @DylanGirodat Hmm, what version of awk are you using awk --version ? – etopylight Dec 7 '17 at 17:32
  • I am using the default awk that comes with Ubuntu 16. – Dylan Girodat Dec 7 '17 at 17:36
  • @DylanGirodat I assume that means GNU Awk 4.1.3, however I couldn't reproduce the error you mentioned – etopylight Dec 7 '17 at 17:59
  • I don't think there is a [ on line 4, did you happen to alter the script? – etopylight Dec 7 '17 at 18:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.