1

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

1 Answer 1

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

8
  • 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 [ Dec 7, 2017 at 17:18
  • @DylanGirodat Hmm, what version of awk are you using awk --version ?
    – etopylight
    Dec 7, 2017 at 17:32
  • I am using the default awk that comes with Ubuntu 16. Dec 7, 2017 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, 2017 at 17:59
  • I don't think there is a [ on line 4, did you happen to alter the script?
    – etopylight
    Dec 7, 2017 at 18:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .