Joining entries based off of column using awk/join

Question

I have two files which are pipe-delimited and may have column 1+column2 matches in both, or one file may have the entry while the other does not. Assume my match-key I am going off of equals $1"-"$2 using a pipe '|' as the FS.

file1

1111|AAA|foo|50
1111|BBB|foo|30
2222|BBB|foo|10

file2

1111|AAA|bar|10
1111|CCC|bar|20
3333|AAA|bar|40

The desired output would be the following for the first entry (I have this working)

1111|AAA|50|10

For the second entry file1 (If there is no matching column1+column2 in both files, replace the entry which is missing for foo as 0. And the other way around)

1111|BBB|30|0

And for an entry key (column1+column2) in file2, but not in file1 (This is entry 3 of file 2 expected output)

3333|AAA|0|40

So, desired output overall format is listing ALL unique keys which are represented by column1+column2 in BOTH files. With the 3rd column entries being those values from file1 column 4 (or 0 if value doesn't exist in file1) and the 4th column in output as those values in column 4 of file 2 (or 0 if value doesn't exist in file2).

I have done a lot of research and tried many things but I have values not outputting if the column1+column2 pair exists in file2 but not file1 by using the following:

join -t"|" -e0 -a1 -a2 -o 1.2,1.3,1.5,2.5 <(<file1 awk -F"|" '{print $1"-"$2"|"$0}' | sort -k1,1) <(<file2 awk -F"|" '{print $1"-"$2"|"$0}' | sort -k1,1)

The above case gives me expected output if there is a column1+column2 match in file1 but not file2, and appends a 0 for the match not existing... How can I get this to work for ALL scenarios?

Above command will do some process substitution by adding a key in column 1 in both files which is column1+column2, and then join based off of that new key. -e0 will add a 0 if this key exists in file1 but not file2. How can I get it to cover the case of: New key (column1-column2) exists in file 2 but NOT file 1?

Answer 1

With your approach you have to use join twice (or change your approach to do it with a single join invocation) :

• print the common lines and the unpairable lines from file1 with join -t'|' -e0 -a1 -o 1.2,1.3,1.5,2.5 <(<file1 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1) <(<file2 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1)
• print the unpairable lines from file2 with join -t'|' -e0 -v2 -o 2.2,2.3,1.5,2.5 <(<file1 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1) <(<file2 awk -F'|' '{print $1"-"$2"|"$0}' | sort -t'|' -k1,1)

You can do the same with a single awk invocation, storing $4 in two arrays indexed by e.g. $1|$2 and then in the END block iterating over each array indices, comparing them and printing accordingly:

awk -F'|' 'NR==FNR{z[$1"|"$2]=$4;next}{x[$1"|"$2]=$4}
END{for (j in x){if (!(j in z)){print j, "0", x[j]}};
for (i in z){if (i in x){print i, z[i], x[i]} else {print i, z[i], "0"}}
}' OFS="|"  file1 file2

Answer 2

The following replaces the first | in both files with @ (use a character that does not occur elsewhere in the file), performs the join and then changes the @ back to the original |. This way, we create a new |-delimited join field consisting of columns 1 and 2 from the original files.

join -t'|' -e0 -a1 -a2 -o0,1.3,2.3 \
    <( sed 's/|/@/' file1 | sort )  \
    <( sed 's/|/@/' file2 | sort ) |
tr '@' '|'

In the output field specification (-o), a zero represents the join field and column 3 in either file is actually column 4 from the original data.

For the given input files, this generates

1111|AAA|50|10
1111|BBB|30|0
1111|CCC|0|20
2222|BBB|10|0
3333|AAA|0|40

Answer 3

Another awk approach:

awk -F'|' 'NR==FNR{f1[$1FS$2]=$NF;next} {f2[$1FS$2]=$NF} 
    END{for (x in f1){print x,f1[x],f2[x]?f2[x]:0; delete f2[x]};
        for (y in f2) print y, 0, f2[y]
}' file[12] OFS='|'

Explanation:

• NR==FNR{f1[$1FS$2]=$NF;next}, this will run only for file1 and with the key combination of $1FS$2 will store last column value $NF in array called f1 (FS will substitute with | as awk's Field Seperator).
• {f2[$1FS$2]=$NF}, same as above but this will run only for file2
• for (x in f1){print x,f1[x],f2[x]?f2[x]:0; delete f2[x]}, loop within array f1 and print key (x), its value in file1 f1[x] and if there same file1 key in file2, then print it as well, else print 0 (Used ternary condition f2[x]?f2[x]:0), after that we are also deleting record of same key from file2 with delete f2[x].
• for (y in f2) print y, 0, f2[y], now array f2 has records which exist in file2 only, so we are printing their key (y), 0 because doesn't not exist in file1 and their value in file2 f2[y].